t/sqlmaker/limit_dialects/torture.t

   1 use strict;
   2 use warnings;
   3
   4 use Test::More;
   5 use lib qw(t/lib);
   6 use DBICTest;
   7 use DBIC::SqlMakerTest;
   8
   9 my $schema = DBICTest->init_schema;
  10
  11 my $attr = {};
  12 my @where_bind = (
  13   [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Study' ],
  14   [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.title' } => 'kama sutra' ],
  15   [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
  16 );
  17 my @select_bind = (
  18   [ $attr => 11 ], [ $attr => 12 ], [ $attr => 13 ],
  19 );
  20 my @group_bind = (
  21   [ $attr => 21 ],
  22 );
  23 my @having_bind = (
  24   [ $attr => 31 ],
  25 );
  26 my @order_bind = (
  27   [ $attr => 1 ], [ $attr => 2 ], [ $attr => 3 ],
  28 );
  29
  30 my $tests = {
  31
  32   LimitOffset => {
  33     ordered_limit_offset => [
  34       '(
  35         SELECT me.id, owner.id, owner.name, ? * ?, ?
  36           FROM books me
  37           JOIN owners owner
  38             ON owner.id = me.owner
  39         WHERE source != ? AND me.title = ? AND source = ?
  40         GROUP BY avg(me.id / ?)
  41         HAVING ?
  42         ORDER BY ? / ?, ?
  43         LIMIT ?
  44         OFFSET ?
  45       )',
  46       [
  47         @select_bind,
  48         @where_bind,
  49         @group_bind,
  50         @having_bind,
  51         @order_bind,
  52         [ { sqlt_datatype => 'integer' } => 4 ],
  53         [ { sqlt_datatype => 'integer' } => 3 ],
  54       ],
  55     ],
  56   },
  57
  58   LimitXY => {
  59     ordered_limit_offset => [
  60       '(
  61         SELECT me.id, owner.id, owner.name, ? * ?, ?
  62           FROM books me
  63           JOIN owners owner
  64             ON owner.id = me.owner
  65         WHERE source != ? AND me.title = ? AND source = ?
  66         GROUP BY avg(me.id / ?)
  67         HAVING ?
  68         ORDER BY ? / ?, ?
  69         LIMIT ?, ?
  70       )',
  71       [
  72         @select_bind,
  73         @where_bind,
  74         @group_bind,
  75         @having_bind,
  76         @order_bind,
  77         [ { sqlt_datatype => 'integer' } => 3 ],
  78         [ { sqlt_datatype => 'integer' } => 4 ],
  79       ],
  80     ],
  81   },
  82
  83   SkipFirst => {
  84     ordered_limit_offset => [
  85       '(
  86         SELECT SKIP ? FIRST ? me.id, owner.id, owner.name, ? * ?, ?
  87           FROM books me
  88           JOIN owners owner
  89             ON owner.id = me.owner
  90         WHERE source != ? AND me.title = ? AND source = ?
  91         GROUP BY avg(me.id / ?)
  92         HAVING ?
  93         ORDER BY ? / ?, ?
  94       )',
  95       [
  96         [ { sqlt_datatype => 'integer' } => 3 ],
  97         [ { sqlt_datatype => 'integer' } => 4 ],
  98         @select_bind,
  99         @where_bind,
 100         @group_bind,
 101         @having_bind,
 102         @order_bind,
 103       ],
 104     ],
 105   },
 106
 107   FirstSkip => {
 108     ordered_limit_offset => [
 109       '(
 110         SELECT FIRST ? SKIP ? me.id, owner.id, owner.name, ? * ?, ?
 111           FROM books me
 112           JOIN owners owner
 113             ON owner.id = me.owner
 114         WHERE source != ? AND me.title = ? AND source = ?
 115         GROUP BY avg(me.id / ?)
 116         HAVING ?
 117         ORDER BY ? / ?, ?
 118       )',
 119       [
 120         [ { sqlt_datatype => 'integer' } => 4 ],
 121         [ { sqlt_datatype => 'integer' } => 3 ],
 122         @select_bind,
 123         @where_bind,
 124         @group_bind,
 125         @having_bind,
 126         @order_bind,
 127       ],
 128     ],
 129   },
 130
 131   RowNumberOver => do {
 132     my $unordered_sql = '(
 133       SELECT me.id, owner__id, owner__name, bar, baz
 134         FROM (
 135           SELECT me.id, owner__id, owner__name, bar, baz, ROW_NUMBER() OVER() AS rno__row__index
 136             FROM (
 137               SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
 138                 FROM books me
 139                 JOIN owners owner
 140                   ON owner.id = me.owner
 141               WHERE source != ? AND me.title = ? AND source = ?
 142               GROUP BY avg(me.id / ?)
 143               HAVING ?
 144             ) me
 145       ) me
 146       WHERE rno__row__index >= ? AND rno__row__index <= ?
 147     )';
 148
 149     my $ordered_sql = '(
 150       SELECT me.id, owner__id, owner__name, bar, baz
 151         FROM (
 152           SELECT me.id, owner__id, owner__name, bar, baz, ROW_NUMBER() OVER( ORDER BY ORDER__BY__001, ORDER__BY__002 ) AS rno__row__index
 153             FROM (
 154               SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz,
 155                      ? / ? AS ORDER__BY__001, ? AS ORDER__BY__002
 156                 FROM books me
 157                 JOIN owners owner
 158                   ON owner.id = me.owner
 159               WHERE source != ? AND me.title = ? AND source = ?
 160               GROUP BY avg(me.id / ?)
 161               HAVING ?
 162             ) me
 163       ) me
 164       WHERE rno__row__index >= ? AND rno__row__index <= ?
 165     )';
 166
 167     {
 168       limit => [$unordered_sql,
 169         [
 170           @select_bind,
 171           @where_bind,
 172           @group_bind,
 173           @having_bind,
 174           [ { sqlt_datatype => 'integer' } => 1 ],
 175           [ { sqlt_datatype => 'integer' } => 4 ],
 176         ],
 177       ],
 178       limit_offset => [$unordered_sql,
 179         [
 180           @select_bind,
 181           @where_bind,
 182           @group_bind,
 183           @having_bind,
 184           [ { sqlt_datatype => 'integer' } => 4 ],
 185           [ { sqlt_datatype => 'integer' } => 7 ],
 186         ],
 187       ],
 188       ordered_limit => [$ordered_sql,
 189         [
 190           @select_bind,
 191           @order_bind,
 192           @where_bind,
 193           @group_bind,
 194           @having_bind,
 195           [ { sqlt_datatype => 'integer' } => 1 ],
 196           [ { sqlt_datatype => 'integer' } => 4 ],
 197         ],
 198       ],
 199       ordered_limit_offset => [$ordered_sql,
 200         [
 201           @select_bind,
 202           @order_bind,
 203           @where_bind,
 204           @group_bind,
 205           @having_bind,
 206           [ { sqlt_datatype => 'integer' } => 4 ],
 207           [ { sqlt_datatype => 'integer' } => 7 ],
 208         ],
 209       ],
 210     };
 211   },
 212
 213   RowNum => do {
 214     my $limit_sql = sub {
 215       sprintf '(
 216         SELECT me.id, owner__id, owner__name, bar, baz
 217           FROM (
 218             SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
 219               FROM books me
 220               JOIN owners owner
 221                 ON owner.id = me.owner
 222             WHERE source != ? AND me.title = ? AND source = ?
 223             GROUP BY avg(me.id / ?)
 224             HAVING ?
 225             %s
 226           ) me
 227         WHERE ROWNUM <= ?
 228       )', $_[0] || '';
 229     };
 230
 231     {
 232       limit => [ $limit_sql->(),
 233         [
 234           @select_bind,
 235           @where_bind,
 236           @group_bind,
 237           @having_bind,
 238           [ { sqlt_datatype => 'integer' } => 4 ],
 239         ],
 240       ],
 241       limit_offset => [
 242         '(
 243           SELECT me.id, owner__id, owner__name, bar, baz
 244             FROM (
 245               SELECT me.id, owner__id, owner__name, bar, baz, ROWNUM rownum__index
 246                 FROM (
 247                   SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
 248                     FROM books me
 249                     JOIN owners owner
 250                       ON owner.id = me.owner
 251                   WHERE source != ? AND me.title = ? AND source = ?
 252                   GROUP BY avg(me.id / ?)
 253                   HAVING ?
 254                 ) me
 255             ) me
 256           WHERE rownum__index BETWEEN ? AND ?
 257         )',
 258         [
 259           @select_bind,
 260           @where_bind,
 261           @group_bind,
 262           @having_bind,
 263           [ { sqlt_datatype => 'integer' } => 4 ],
 264           [ { sqlt_datatype => 'integer' } => 7 ],
 265         ],
 266       ],
 267       ordered_limit => [ $limit_sql->('ORDER BY ? / ?, ?'),
 268         [
 269           @select_bind,
 270           @where_bind,
 271           @group_bind,
 272           @having_bind,
 273           @order_bind,
 274           [ { sqlt_datatype => 'integer' } => 4 ],
 275         ],
 276       ],
 277       ordered_limit_offset => [
 278         '(
 279           SELECT me.id, owner__id, owner__name, bar, baz
 280             FROM (
 281               SELECT me.id, owner__id, owner__name, bar, baz, ROWNUM rownum__index
 282                 FROM (
 283                   SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
 284                     FROM books me
 285                     JOIN owners owner
 286                       ON owner.id = me.owner
 287                   WHERE source != ? AND me.title = ? AND source = ?
 288                   GROUP BY avg(me.id / ?)
 289                   HAVING ?
 290                   ORDER BY ? / ?, ?
 291                 ) me
 292               WHERE ROWNUM <= ?
 293             ) me
 294           WHERE rownum__index >= ?
 295         )',
 296         [
 297           @select_bind,
 298           @where_bind,
 299           @group_bind,
 300           @having_bind,
 301           @order_bind,
 302           [ { sqlt_datatype => 'integer' } => 7 ],
 303           [ { sqlt_datatype => 'integer' } => 4 ],
 304         ],
 305       ],
 306     };
 307   },
 308
 309   FetchFirst => {
 310     limit => [
 311       '(
 312         SELECT me.id, owner.id, owner.name, ? * ?, ?
 313           FROM books me
 314           JOIN owners owner
 315             ON owner.id = me.owner
 316         WHERE source != ? AND me.title = ? AND source = ?
 317         GROUP BY avg(me.id / ?)
 318         HAVING ?
 319         FETCH FIRST 4 ROWS ONLY
 320       )',
 321       [
 322         @select_bind,
 323         @where_bind,
 324         @group_bind,
 325         @having_bind,
 326       ],
 327     ],
 328     limit_offset => [
 329       '(
 330         SELECT me.id, owner__id, owner__name, bar, baz
 331           FROM (
 332             SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
 333               FROM books me
 334               JOIN owners owner
 335                 ON owner.id = me.owner
 336             WHERE source != ? AND me.title = ? AND source = ?
 337             GROUP BY avg(me.id / ?)
 338             HAVING ?
 339             ORDER BY me.id
 340             FETCH FIRST 7 ROWS ONLY
 341           ) me
 342         ORDER BY me.id DESC
 343         FETCH FIRST 4 ROWS ONLY
 344       )',
 345       [
 346         @select_bind,
 347         @where_bind,
 348         @group_bind,
 349         @having_bind,
 350       ],
 351     ],
 352     ordered_limit => [
 353       '(
 354         SELECT me.id, owner.id, owner.name, ? * ?, ?
 355           FROM books me
 356           JOIN owners owner
 357             ON owner.id = me.owner
 358         WHERE source != ? AND me.title = ? AND source = ?
 359         GROUP BY avg(me.id / ?)
 360         HAVING ?
 361         ORDER BY ? / ?, ?
 362         FETCH FIRST 4 ROWS ONLY
 363       )',
 364       [
 365         @select_bind,
 366         @where_bind,
 367         @group_bind,
 368         @having_bind,
 369         @order_bind,
 370       ],
 371     ],
 372     ordered_limit_offset => [
 373       '(
 374         SELECT me.id, owner__id, owner__name, bar, baz
 375           FROM (
 376             SELECT me.id, owner__id, owner__name, bar, baz, ORDER__BY__001, ORDER__BY__002
 377               FROM (
 378                 SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz, ? / ? AS ORDER__BY__001, ? AS ORDER__BY__002
 379                   FROM books me
 380                   JOIN owners owner
 381                     ON owner.id = me.owner
 382                 WHERE source != ? AND me.title = ? AND source = ?
 383                 GROUP BY avg(me.id / ?)
 384                 HAVING ?
 385                 ORDER BY ? / ?, ?
 386                 FETCH FIRST 7 ROWS ONLY
 387               ) me
 388             ORDER BY ORDER__BY__001 DESC, ORDER__BY__002 DESC
 389             FETCH FIRST 4 ROWS ONLY
 390           ) me
 391         ORDER BY ORDER__BY__001, ORDER__BY__002
 392       )',
 393       [
 394         @select_bind,
 395         @order_bind,
 396         @where_bind,
 397         @group_bind,
 398         @having_bind,
 399         (map { [ @$_ ] } @order_bind),  # without this is_deeply throws a fit
 400       ],
 401     ],
 402   },
 403
 404   Top => {
 405     limit => [
 406       '(
 407         SELECT TOP 4 me.id, owner.id, owner.name, ? * ?, ?
 408           FROM books me
 409           JOIN owners owner
 410             ON owner.id = me.owner
 411         WHERE source != ? AND me.title = ? AND source = ?
 412         GROUP BY avg(me.id / ?)
 413         HAVING ?
 414       )',
 415       [
 416         @select_bind,
 417         @where_bind,
 418         @group_bind,
 419         @having_bind,
 420       ],
 421     ],
 422     limit_offset => [
 423       '(
 424         SELECT TOP 4 me.id, owner__id, owner__name, bar, baz
 425           FROM (
 426             SELECT TOP 7 me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
 427               FROM books me
 428               JOIN owners owner
 429                 ON owner.id = me.owner
 430             WHERE source != ? AND me.title = ? AND source = ?
 431             GROUP BY avg(me.id / ?)
 432             HAVING ?
 433             ORDER BY me.id
 434           ) me
 435         ORDER BY me.id DESC
 436       )',
 437       [
 438         @select_bind,
 439         @where_bind,
 440         @group_bind,
 441         @having_bind,
 442       ],
 443     ],
 444     ordered_limit => [
 445       '(
 446         SELECT TOP 4 me.id, owner.id, owner.name, ? * ?, ?
 447           FROM books me
 448           JOIN owners owner
 449             ON owner.id = me.owner
 450         WHERE source != ? AND me.title = ? AND source = ?
 451         GROUP BY avg(me.id / ?)
 452         HAVING ?
 453         ORDER BY ? / ?, ?
 454       )',
 455       [
 456         @select_bind,
 457         @where_bind,
 458         @group_bind,
 459         @having_bind,
 460         @order_bind,
 461       ],
 462     ],
 463     ordered_limit_offset => [
 464       '(
 465         SELECT me.id, owner__id, owner__name, bar, baz
 466           FROM (
 467             SELECT TOP 4 me.id, owner__id, owner__name, bar, baz, ORDER__BY__001, ORDER__BY__002
 468               FROM (
 469                 SELECT TOP 7 me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz, ? / ? AS ORDER__BY__001, ? AS ORDER__BY__002
 470                   FROM books me
 471                   JOIN owners owner
 472                     ON owner.id = me.owner
 473                 WHERE source != ? AND me.title = ? AND source = ?
 474                 GROUP BY avg(me.id / ?)
 475                 HAVING ?
 476                 ORDER BY ? / ?, ?
 477               ) me
 478             ORDER BY ORDER__BY__001 DESC, ORDER__BY__002 DESC
 479           ) me
 480         ORDER BY ORDER__BY__001, ORDER__BY__002
 481       )',
 482       [
 483         @select_bind,
 484         @order_bind,
 485         @where_bind,
 486         @group_bind,
 487         @having_bind,
 488         (map { [ @$_ ] } @order_bind),  # without this is_deeply throws a fit
 489       ],
 490     ],
 491   },
 492
 493   RowCountOrGenericSubQ => {
 494     limit => [
 495       '(
 496         SET ROWCOUNT 4
 497         SELECT me.id, owner.id, owner.name, ? * ?, ?
 498           FROM books me
 499           JOIN owners owner
 500             ON owner.id = me.owner
 501         WHERE source != ? AND me.title = ? AND source = ?
 502         GROUP BY avg(me.id / ?)
 503         HAVING ?
 504         ORDER BY me.id
 505         SET ROWCOUNT 0
 506       )',
 507       [
 508         @select_bind,
 509         @where_bind,
 510         @group_bind,
 511         @having_bind,
 512       ],
 513     ],
 514     limit_offset => [
 515       '(
 516         SELECT me.id, owner__id, owner__name, bar, baz
 517           FROM (
 518             SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
 519               FROM books me
 520               JOIN owners owner
 521                 ON owner.id = me.owner
 522             WHERE source != ? AND me.title = ? AND source = ?
 523             GROUP BY avg( me.id / ? )
 524             HAVING ?
 525           ) me
 526         WHERE (
 527           SELECT COUNT( * )
 528             FROM books rownum__emulation
 529           WHERE rownum__emulation.id < me.id
 530         ) BETWEEN ? AND ?
 531         ORDER BY me.id
 532       )',
 533       [
 534         @select_bind,
 535         @where_bind,
 536         @group_bind,
 537         @having_bind,
 538         [ { sqlt_datatype => 'integer' } => 3 ],
 539         [ { sqlt_datatype => 'integer' } => 6 ],
 540       ],
 541     ],
 542   },
 543
 544   GenericSubQ => {
 545     limit => [
 546       '(
 547         SELECT me.id, owner__id, owner__name, bar, baz
 548           FROM (
 549             SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
 550               FROM books me
 551               JOIN owners owner
 552                 ON owner.id = me.owner
 553             WHERE source != ? AND me.title = ? AND source = ?
 554             GROUP BY avg( me.id / ? )
 555             HAVING ?
 556           ) me
 557         WHERE (
 558           SELECT COUNT( * )
 559             FROM books rownum__emulation
 560           WHERE rownum__emulation.id < me.id
 561         ) < ?
 562         ORDER BY me.id
 563       )',
 564       [
 565         @select_bind,
 566         @where_bind,
 567         @group_bind,
 568         @having_bind,
 569         [ { sqlt_datatype => 'integer' } => 4 ],
 570       ],
 571     ],
 572     limit_offset => [
 573       '(
 574         SELECT me.id, owner__id, owner__name, bar, baz
 575           FROM (
 576             SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
 577               FROM books me
 578               JOIN owners owner
 579                 ON owner.id = me.owner
 580             WHERE source != ? AND me.title = ? AND source = ?
 581             GROUP BY avg( me.id / ? )
 582             HAVING ?
 583           ) me
 584         WHERE (
 585           SELECT COUNT( * )
 586             FROM books rownum__emulation
 587           WHERE rownum__emulation.id < me.id
 588         ) BETWEEN ? AND ?
 589         ORDER BY me.id
 590       )',
 591       [
 592         @select_bind,
 593         @where_bind,
 594         @group_bind,
 595         @having_bind,
 596         [ { sqlt_datatype => 'integer' } => 3 ],
 597         [ { sqlt_datatype => 'integer' } => 6 ],
 598       ],
 599     ],
 600   }
 601 };
 602
 603 for my $limtype (sort keys %$tests) {
 604
 605   Test::Builder->new->is_passing or exit;
 606
 607   delete $schema->storage->_sql_maker->{_cached_syntax};
 608   $schema->storage->_sql_maker->limit_dialect ($limtype);
 609
 610   # chained search is necessary to exercise the recursive {where} parser
 611   my $rs = $schema->resultset('BooksInLibrary')->search({ 'me.title' => { '=' => 'kama sutra' } })->search({ source => { '!=', 'Study' } }, {
 612     columns => [ { identifier => 'me.id' }, 'owner.id', 'owner.name' ], # people actually do that. BLEH!!! :)
 613     join => 'owner',  # single-rel manual prefetch
 614     rows => 4,
 615     '+columns' => { bar => \['? * ?', [ $attr => 11 ], [ $attr => 12 ]], baz => \[ '?', [ $attr => 13 ]] },
 616     group_by => \[ 'avg(me.id / ?)', [ $attr => 21 ] ],
 617     having => \[ '?', [ $attr => 31 ] ],
 618     ($limtype =~ /GenericSubQ/ ? ( order_by => 'me.id' ) : () ),  # needs a simple-column stable order to be happy
 619   });
 620
 621   #
 622   # not all tests run on all dialects (somewhere impossible, somewhere makes no sense)
 623   #
 624
 625   # only limit, no offset, no order
 626   is_same_sql_bind(
 627     $rs->as_query,
 628     @{$tests->{$limtype}{limit}},
 629     "$limtype: Unordered limit with select/group/having",
 630   ) if $tests->{$limtype}{limit};
 631
 632   # limit + offset, no order
 633   is_same_sql_bind(
 634     $rs->search({}, { offset => 3 })->as_query,
 635     @{$tests->{$limtype}{limit_offset}},
 636     "$limtype: Unordered limit+offset with select/group/having",
 637   ) if $tests->{$limtype}{limit_offset};
 638
 639   # order + limit, no offset
 640   $rs = $rs->search(undef, {
 641     order_by => [ \['? / ?', [ $attr => 1 ], [ $attr => 2 ]], \[ '?', [ $attr => 3 ]] ],
 642   });
 643
 644   is_same_sql_bind(
 645     $rs->as_query,
 646     @{$tests->{$limtype}{ordered_limit}},
 647     "$limtype: Ordered limit with select/group/having",
 648   ) if $tests->{$limtype}{ordered_limit};
 649
 650   # order + limit + offset
 651   is_same_sql_bind(
 652     $rs->search({}, { offset => 3 })->as_query,
 653     @{$tests->{$limtype}{ordered_limit_offset}},
 654     "$limtype: Ordered limit+offset with select/group/having",
 655   ) if $tests->{$limtype}{ordered_limit_offset};
 656 }
 657
 658 done_testing;