t/sqlmaker/limit_dialects/torture.t

   1 use strict;
   2 use warnings;
   3
   4 use Test::More;
   5 use Test::Exception;
   6 use lib qw(t/lib);
   7 use DBICTest;
   8 use DBIC::SqlMakerTest;
   9
  10 my $schema = DBICTest->init_schema;
  11 my $native_limit_dialect = $schema->storage->sql_maker->{limit_dialect};
  12
  13 my $attr = {};
  14 my @where_bind = (
  15   [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Study' ],
  16   [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.title' } => 'kama sutra' ],
  17   [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
  18 );
  19 my @select_bind = (
  20   [ $attr => 11 ], [ $attr => 12 ], [ $attr => 13 ],
  21 );
  22 my @group_bind = (
  23   [ $attr => 21 ],
  24 );
  25 my @having_bind = (
  26   [ $attr => 31 ],
  27 );
  28 my @order_bind = (
  29   [ $attr => 1 ], [ $attr => 2 ], [ $attr => 3 ],
  30 );
  31
  32 my $tests = {
  33
  34   LimitOffset => {
  35     limit => [
  36       '(
  37         SELECT me.id, owner.id, owner.name, ? * ?, ?
  38           FROM books me
  39           JOIN owners owner
  40             ON owner.id = me.owner
  41         WHERE source != ? AND me.title = ? AND source = ?
  42         GROUP BY (me.id / ?), owner.id
  43         HAVING ?
  44         LIMIT ?
  45       )',
  46       [
  47         @select_bind,
  48         @where_bind,
  49         @group_bind,
  50         @having_bind,
  51         [ { sqlt_datatype => 'integer' } => 4 ],
  52       ],
  53     ],
  54     limit_offset => [
  55       '(
  56         SELECT me.id, owner.id, owner.name, ? * ?, ?
  57           FROM books me
  58           JOIN owners owner
  59             ON owner.id = me.owner
  60         WHERE source != ? AND me.title = ? AND source = ?
  61         GROUP BY (me.id / ?), owner.id
  62         HAVING ?
  63         LIMIT ?
  64         OFFSET ?
  65       )',
  66       [
  67         @select_bind,
  68         @where_bind,
  69         @group_bind,
  70         @having_bind,
  71         [ { sqlt_datatype => 'integer' } => 4 ],
  72         [ { sqlt_datatype => 'integer' } => 3 ],
  73       ],
  74     ],
  75     ordered_limit => [
  76       '(
  77         SELECT me.id, owner.id, owner.name, ? * ?, ?
  78           FROM books me
  79           JOIN owners owner
  80             ON owner.id = me.owner
  81         WHERE source != ? AND me.title = ? AND source = ?
  82         GROUP BY (me.id / ?), owner.id
  83         HAVING ?
  84         ORDER BY ? / ?, ?
  85         LIMIT ?
  86       )',
  87       [
  88         @select_bind,
  89         @where_bind,
  90         @group_bind,
  91         @having_bind,
  92         @order_bind,
  93         [ { sqlt_datatype => 'integer' } => 4 ],
  94       ]
  95     ],
  96     ordered_limit_offset => [
  97       '(
  98         SELECT me.id, owner.id, owner.name, ? * ?, ?
  99           FROM books me
 100           JOIN owners owner
 101             ON owner.id = me.owner
 102         WHERE source != ? AND me.title = ? AND source = ?
 103         GROUP BY (me.id / ?), owner.id
 104         HAVING ?
 105         ORDER BY ? / ?, ?
 106         LIMIT ?
 107         OFFSET ?
 108       )',
 109       [
 110         @select_bind,
 111         @where_bind,
 112         @group_bind,
 113         @having_bind,
 114         @order_bind,
 115         [ { sqlt_datatype => 'integer' } => 4 ],
 116         [ { sqlt_datatype => 'integer' } => 3 ],
 117       ],
 118     ],
 119     limit_offset_prefetch => [
 120       '(
 121         SELECT me.name, books.id, books.source, books.owner, books.title, books.price
 122           FROM (
 123             SELECT me.name, me.id
 124               FROM owners me
 125             LIMIT ? OFFSET ?
 126           ) me
 127           LEFT JOIN books books
 128             ON books.owner = me.id
 129       )',
 130       [
 131         [ { sqlt_datatype => 'integer' } => 3 ],
 132         [ { sqlt_datatype => 'integer' } => 1 ],
 133       ]
 134     ],
 135   },
 136
 137   LimitXY => {
 138     ordered_limit_offset => [
 139       '(
 140         SELECT me.id, owner.id, owner.name, ? * ?, ?
 141           FROM books me
 142           JOIN owners owner
 143             ON owner.id = me.owner
 144         WHERE source != ? AND me.title = ? AND source = ?
 145         GROUP BY (me.id / ?), owner.id
 146         HAVING ?
 147         ORDER BY ? / ?, ?
 148         LIMIT ?, ?
 149       )',
 150       [
 151         @select_bind,
 152         @where_bind,
 153         @group_bind,
 154         @having_bind,
 155         @order_bind,
 156         [ { sqlt_datatype => 'integer' } => 3 ],
 157         [ { sqlt_datatype => 'integer' } => 4 ],
 158       ],
 159     ],
 160     limit_offset_prefetch => [
 161       '(
 162         SELECT me.name, books.id, books.source, books.owner, books.title, books.price
 163           FROM (
 164             SELECT me.name, me.id
 165               FROM owners me
 166             LIMIT ?,?
 167           ) me
 168           LEFT JOIN books books
 169             ON books.owner = me.id
 170       )',
 171       [
 172         [ { sqlt_datatype => 'integer' } => 1 ],
 173         [ { sqlt_datatype => 'integer' } => 3 ],
 174       ]
 175     ],
 176   },
 177
 178   SkipFirst => {
 179     ordered_limit_offset => [
 180       '(
 181         SELECT SKIP ? FIRST ? me.id, owner.id, owner.name, ? * ?, ?
 182           FROM books me
 183           JOIN owners owner
 184             ON owner.id = me.owner
 185         WHERE source != ? AND me.title = ? AND source = ?
 186         GROUP BY (me.id / ?), owner.id
 187         HAVING ?
 188         ORDER BY ? / ?, ?
 189       )',
 190       [
 191         [ { sqlt_datatype => 'integer' } => 3 ],
 192         [ { sqlt_datatype => 'integer' } => 4 ],
 193         @select_bind,
 194         @where_bind,
 195         @group_bind,
 196         @having_bind,
 197         @order_bind,
 198       ],
 199     ],
 200     limit_offset_prefetch => [
 201       '(
 202         SELECT me.name, books.id, books.source, books.owner, books.title, books.price
 203           FROM (
 204             SELECT SKIP ? FIRST ? me.name, me.id
 205               FROM owners me
 206           ) me
 207           LEFT JOIN books books
 208             ON books.owner = me.id
 209       )',
 210       [
 211         [ { sqlt_datatype => 'integer' } => 1 ],
 212         [ { sqlt_datatype => 'integer' } => 3 ],
 213       ]
 214     ],
 215   },
 216
 217   FirstSkip => {
 218     ordered_limit_offset => [
 219       '(
 220         SELECT FIRST ? SKIP ? me.id, owner.id, owner.name, ? * ?, ?
 221           FROM books me
 222           JOIN owners owner
 223             ON owner.id = me.owner
 224         WHERE source != ? AND me.title = ? AND source = ?
 225         GROUP BY (me.id / ?), owner.id
 226         HAVING ?
 227         ORDER BY ? / ?, ?
 228       )',
 229       [
 230         [ { sqlt_datatype => 'integer' } => 4 ],
 231         [ { sqlt_datatype => 'integer' } => 3 ],
 232         @select_bind,
 233         @where_bind,
 234         @group_bind,
 235         @having_bind,
 236         @order_bind,
 237       ],
 238     ],
 239     limit_offset_prefetch => [
 240       '(
 241         SELECT me.name, books.id, books.source, books.owner, books.title, books.price
 242           FROM (
 243             SELECT FIRST ? SKIP ? me.name, me.id
 244               FROM owners me
 245           ) me
 246           LEFT JOIN books books
 247             ON books.owner = me.id
 248       )',
 249       [
 250         [ { sqlt_datatype => 'integer' } => 3 ],
 251         [ { sqlt_datatype => 'integer' } => 1 ],
 252       ]
 253     ],
 254   },
 255
 256   RowNumberOver => do {
 257     my $unordered_sql = '(
 258       SELECT me.id, owner__id, owner__name, bar, baz
 259         FROM (
 260           SELECT me.id, owner__id, owner__name, bar, baz, ROW_NUMBER() OVER() AS rno__row__index
 261             FROM (
 262               SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
 263                 FROM books me
 264                 JOIN owners owner
 265                   ON owner.id = me.owner
 266               WHERE source != ? AND me.title = ? AND source = ?
 267               GROUP BY (me.id / ?), owner.id
 268               HAVING ?
 269             ) me
 270       ) me
 271       WHERE rno__row__index >= ? AND rno__row__index <= ?
 272     )';
 273
 274     my $ordered_sql = '(
 275       SELECT me.id, owner__id, owner__name, bar, baz
 276         FROM (
 277           SELECT me.id, owner__id, owner__name, bar, baz, ROW_NUMBER() OVER( ORDER BY ORDER__BY__001, ORDER__BY__002 ) AS rno__row__index
 278             FROM (
 279               SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz,
 280                      ? / ? AS ORDER__BY__001, ? AS ORDER__BY__002
 281                 FROM books me
 282                 JOIN owners owner
 283                   ON owner.id = me.owner
 284               WHERE source != ? AND me.title = ? AND source = ?
 285               GROUP BY (me.id / ?), owner.id
 286               HAVING ?
 287             ) me
 288       ) me
 289       WHERE rno__row__index >= ? AND rno__row__index <= ?
 290     )';
 291
 292     {
 293       limit => [$unordered_sql,
 294         [
 295           @select_bind,
 296           @where_bind,
 297           @group_bind,
 298           @having_bind,
 299           [ { sqlt_datatype => 'integer' } => 1 ],
 300           [ { sqlt_datatype => 'integer' } => 4 ],
 301         ],
 302       ],
 303       limit_offset => [$unordered_sql,
 304         [
 305           @select_bind,
 306           @where_bind,
 307           @group_bind,
 308           @having_bind,
 309           [ { sqlt_datatype => 'integer' } => 4 ],
 310           [ { sqlt_datatype => 'integer' } => 7 ],
 311         ],
 312       ],
 313       ordered_limit => [$ordered_sql,
 314         [
 315           @select_bind,
 316           @order_bind,
 317           @where_bind,
 318           @group_bind,
 319           @having_bind,
 320           [ { sqlt_datatype => 'integer' } => 1 ],
 321           [ { sqlt_datatype => 'integer' } => 4 ],
 322         ],
 323       ],
 324       ordered_limit_offset => [$ordered_sql,
 325         [
 326           @select_bind,
 327           @order_bind,
 328           @where_bind,
 329           @group_bind,
 330           @having_bind,
 331           [ { sqlt_datatype => 'integer' } => 4 ],
 332           [ { sqlt_datatype => 'integer' } => 7 ],
 333         ],
 334       ],
 335       limit_offset_prefetch => [
 336         '(
 337           SELECT me.name, books.id, books.source, books.owner, books.title, books.price
 338             FROM (
 339               SELECT me.name, me.id
 340                 FROM (
 341                   SELECT me.name, me.id, ROW_NUMBER() OVER() AS rno__row__index
 342                   FROM (
 343                     SELECT me.name, me.id  FROM owners me
 344                   ) me
 345                 ) me
 346               WHERE rno__row__index >= ? AND rno__row__index <= ?
 347             ) me
 348             LEFT JOIN books books
 349               ON books.owner = me.id
 350         )',
 351         [
 352           [ { sqlt_datatype => 'integer' } => 2 ],
 353           [ { sqlt_datatype => 'integer' } => 4 ],
 354         ]
 355       ],
 356     };
 357   },
 358
 359   RowNum => do {
 360     my $limit_sql = sub {
 361       sprintf '(
 362         SELECT me.id, owner__id, owner__name, bar, baz
 363           FROM (
 364             SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
 365               FROM books me
 366               JOIN owners owner
 367                 ON owner.id = me.owner
 368             WHERE source != ? AND me.title = ? AND source = ?
 369             GROUP BY (me.id / ?), owner.id
 370             HAVING ?
 371             %s
 372           ) me
 373         WHERE ROWNUM <= ?
 374       )', $_[0] || '';
 375     };
 376
 377     {
 378       limit => [ $limit_sql->(),
 379         [
 380           @select_bind,
 381           @where_bind,
 382           @group_bind,
 383           @having_bind,
 384           [ { sqlt_datatype => 'integer' } => 4 ],
 385         ],
 386       ],
 387       limit_offset => [
 388         '(
 389           SELECT me.id, owner__id, owner__name, bar, baz
 390             FROM (
 391               SELECT me.id, owner__id, owner__name, bar, baz, ROWNUM rownum__index
 392                 FROM (
 393                   SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
 394                     FROM books me
 395                     JOIN owners owner
 396                       ON owner.id = me.owner
 397                   WHERE source != ? AND me.title = ? AND source = ?
 398                   GROUP BY (me.id / ?), owner.id
 399                   HAVING ?
 400                 ) me
 401             ) me
 402           WHERE rownum__index BETWEEN ? AND ?
 403         )',
 404         [
 405           @select_bind,
 406           @where_bind,
 407           @group_bind,
 408           @having_bind,
 409           [ { sqlt_datatype => 'integer' } => 4 ],
 410           [ { sqlt_datatype => 'integer' } => 7 ],
 411         ],
 412       ],
 413       ordered_limit => [ $limit_sql->('ORDER BY ? / ?, ?'),
 414         [
 415           @select_bind,
 416           @where_bind,
 417           @group_bind,
 418           @having_bind,
 419           @order_bind,
 420           [ { sqlt_datatype => 'integer' } => 4 ],
 421         ],
 422       ],
 423       ordered_limit_offset => [
 424         '(
 425           SELECT me.id, owner__id, owner__name, bar, baz
 426             FROM (
 427               SELECT me.id, owner__id, owner__name, bar, baz, ROWNUM rownum__index
 428                 FROM (
 429                   SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
 430                     FROM books me
 431                     JOIN owners owner
 432                       ON owner.id = me.owner
 433                   WHERE source != ? AND me.title = ? AND source = ?
 434                   GROUP BY (me.id / ?), owner.id
 435                   HAVING ?
 436                   ORDER BY ? / ?, ?
 437                 ) me
 438               WHERE ROWNUM <= ?
 439             ) me
 440           WHERE rownum__index >= ?
 441         )',
 442         [
 443           @select_bind,
 444           @where_bind,
 445           @group_bind,
 446           @having_bind,
 447           @order_bind,
 448           [ { sqlt_datatype => 'integer' } => 7 ],
 449           [ { sqlt_datatype => 'integer' } => 4 ],
 450         ],
 451       ],
 452       limit_offset_prefetch => [
 453         '(
 454           SELECT me.name, books.id, books.source, books.owner, books.title, books.price
 455             FROM (
 456               SELECT me.name, me.id
 457                 FROM (
 458                   SELECT me.name, me.id, ROWNUM rownum__index
 459                     FROM (
 460                       SELECT me.name, me.id
 461                         FROM owners me
 462                     ) me
 463                 ) me WHERE rownum__index BETWEEN ? AND ?
 464             ) me
 465             LEFT JOIN books books
 466               ON books.owner = me.id
 467         )',
 468         [
 469           [ { sqlt_datatype => 'integer' } => 2 ],
 470           [ { sqlt_datatype => 'integer' } => 4 ],
 471         ]
 472       ],
 473     };
 474   },
 475
 476   FetchFirst => {
 477     limit => [
 478       '(
 479         SELECT me.id, owner.id, owner.name, ? * ?, ?
 480           FROM books me
 481           JOIN owners owner
 482             ON owner.id = me.owner
 483         WHERE source != ? AND me.title = ? AND source = ?
 484         GROUP BY (me.id / ?), owner.id
 485         HAVING ?
 486         FETCH FIRST 4 ROWS ONLY
 487       )',
 488       [
 489         @select_bind,
 490         @where_bind,
 491         @group_bind,
 492         @having_bind,
 493       ],
 494     ],
 495     limit_offset => [
 496       '(
 497         SELECT me.id, owner__id, owner__name, bar, baz
 498           FROM (
 499             SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
 500               FROM books me
 501               JOIN owners owner
 502                 ON owner.id = me.owner
 503             WHERE source != ? AND me.title = ? AND source = ?
 504             GROUP BY (me.id / ?), owner.id
 505             HAVING ?
 506             ORDER BY me.id
 507             FETCH FIRST 7 ROWS ONLY
 508           ) me
 509         ORDER BY me.id DESC
 510         FETCH FIRST 4 ROWS ONLY
 511       )',
 512       [
 513         @select_bind,
 514         @where_bind,
 515         @group_bind,
 516         @having_bind,
 517       ],
 518     ],
 519     ordered_limit => [
 520       '(
 521         SELECT me.id, owner.id, owner.name, ? * ?, ?
 522           FROM books me
 523           JOIN owners owner
 524             ON owner.id = me.owner
 525         WHERE source != ? AND me.title = ? AND source = ?
 526         GROUP BY (me.id / ?), owner.id
 527         HAVING ?
 528         ORDER BY ? / ?, ?
 529         FETCH FIRST 4 ROWS ONLY
 530       )',
 531       [
 532         @select_bind,
 533         @where_bind,
 534         @group_bind,
 535         @having_bind,
 536         @order_bind,
 537       ],
 538     ],
 539     ordered_limit_offset => [
 540       '(
 541         SELECT me.id, owner__id, owner__name, bar, baz
 542           FROM (
 543             SELECT me.id, owner__id, owner__name, bar, baz, ORDER__BY__001, ORDER__BY__002
 544               FROM (
 545                 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
 546                   FROM books me
 547                   JOIN owners owner
 548                     ON owner.id = me.owner
 549                 WHERE source != ? AND me.title = ? AND source = ?
 550                 GROUP BY (me.id / ?), owner.id
 551                 HAVING ?
 552                 ORDER BY ? / ?, ?
 553                 FETCH FIRST 7 ROWS ONLY
 554               ) me
 555             ORDER BY ORDER__BY__001 DESC, ORDER__BY__002 DESC
 556             FETCH FIRST 4 ROWS ONLY
 557           ) me
 558         ORDER BY ORDER__BY__001, ORDER__BY__002
 559       )',
 560       [
 561         @select_bind,
 562         @order_bind,
 563         @where_bind,
 564         @group_bind,
 565         @having_bind,
 566         (map { [ @$_ ] } @order_bind),  # without this is_deeply throws a fit
 567       ],
 568     ],
 569     limit_offset_prefetch => [
 570       '(
 571         SELECT me.name, books.id, books.source, books.owner, books.title, books.price
 572           FROM (
 573             SELECT me.name, me.id
 574               FROM (
 575                 SELECT me.name, me.id
 576                   FROM owners me
 577                 ORDER BY me.id
 578                 FETCH FIRST 4 ROWS ONLY
 579               ) me
 580               ORDER BY me.id DESC
 581             FETCH FIRST 3 ROWS ONLY
 582           ) me
 583           LEFT JOIN books books
 584             ON books.owner = me.id
 585       )',
 586       [],
 587     ],
 588   },
 589
 590   Top => {
 591     limit => [
 592       '(
 593         SELECT TOP 4 me.id, owner.id, owner.name, ? * ?, ?
 594           FROM books me
 595           JOIN owners owner
 596             ON owner.id = me.owner
 597         WHERE source != ? AND me.title = ? AND source = ?
 598         GROUP BY (me.id / ?), owner.id
 599         HAVING ?
 600       )',
 601       [
 602         @select_bind,
 603         @where_bind,
 604         @group_bind,
 605         @having_bind,
 606       ],
 607     ],
 608     limit_offset => [
 609       '(
 610         SELECT TOP 4 me.id, owner__id, owner__name, bar, baz
 611           FROM (
 612             SELECT TOP 7 me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
 613               FROM books me
 614               JOIN owners owner
 615                 ON owner.id = me.owner
 616             WHERE source != ? AND me.title = ? AND source = ?
 617             GROUP BY (me.id / ?), owner.id
 618             HAVING ?
 619             ORDER BY me.id
 620           ) me
 621         ORDER BY me.id DESC
 622       )',
 623       [
 624         @select_bind,
 625         @where_bind,
 626         @group_bind,
 627         @having_bind,
 628       ],
 629     ],
 630     ordered_limit => [
 631       '(
 632         SELECT TOP 4 me.id, owner.id, owner.name, ? * ?, ?
 633           FROM books me
 634           JOIN owners owner
 635             ON owner.id = me.owner
 636         WHERE source != ? AND me.title = ? AND source = ?
 637         GROUP BY (me.id / ?), owner.id
 638         HAVING ?
 639         ORDER BY ? / ?, ?
 640       )',
 641       [
 642         @select_bind,
 643         @where_bind,
 644         @group_bind,
 645         @having_bind,
 646         @order_bind,
 647       ],
 648     ],
 649     ordered_limit_offset => [
 650       '(
 651         SELECT me.id, owner__id, owner__name, bar, baz
 652           FROM (
 653             SELECT TOP 4 me.id, owner__id, owner__name, bar, baz, ORDER__BY__001, ORDER__BY__002
 654               FROM (
 655                 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
 656                   FROM books me
 657                   JOIN owners owner
 658                     ON owner.id = me.owner
 659                 WHERE source != ? AND me.title = ? AND source = ?
 660                 GROUP BY (me.id / ?), owner.id
 661                 HAVING ?
 662                 ORDER BY ? / ?, ?
 663               ) me
 664             ORDER BY ORDER__BY__001 DESC, ORDER__BY__002 DESC
 665           ) me
 666         ORDER BY ORDER__BY__001, ORDER__BY__002
 667       )',
 668       [
 669         @select_bind,
 670         @order_bind,
 671         @where_bind,
 672         @group_bind,
 673         @having_bind,
 674         (map { [ @$_ ] } @order_bind),  # without this is_deeply throws a fit
 675       ],
 676     ],
 677     limit_offset_prefetch => [
 678       '(
 679         SELECT me.name, books.id, books.source, books.owner, books.title, books.price
 680           FROM (
 681             SELECT TOP 3 me.name, me.id
 682               FROM (
 683                 SELECT TOP 4 me.name, me.id
 684                   FROM owners me
 685                 ORDER BY me.id
 686               ) me
 687               ORDER BY me.id DESC
 688           ) me
 689           LEFT JOIN books books
 690             ON books.owner = me.id
 691       )',
 692       [],
 693     ],
 694   },
 695
 696   GenericSubQ => {
 697     limit => [
 698       '(
 699         SELECT me.id, owner__id, owner__name, bar, baz
 700           FROM (
 701             SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
 702               FROM books me
 703               JOIN owners owner
 704                 ON owner.id = me.owner
 705             WHERE source != ? AND me.title = ? AND source = ?
 706             GROUP BY (me.id / ?), owner.id
 707             HAVING ?
 708           ) me
 709         WHERE (
 710           SELECT COUNT( * )
 711             FROM books rownum__emulation
 712           WHERE rownum__emulation.id < me.id
 713         ) < ?
 714         ORDER BY me.id
 715       )',
 716       [
 717         @select_bind,
 718         @where_bind,
 719         @group_bind,
 720         @having_bind,
 721         [ { sqlt_datatype => 'integer' } => 4 ],
 722       ],
 723     ],
 724     limit_offset => [
 725       '(
 726         SELECT me.id, owner__id, owner__name, bar, baz
 727           FROM (
 728             SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
 729               FROM books me
 730               JOIN owners owner
 731                 ON owner.id = me.owner
 732             WHERE source != ? AND me.title = ? AND source = ?
 733             GROUP BY (me.id / ?), owner.id
 734             HAVING ?
 735           ) me
 736         WHERE (
 737           SELECT COUNT( * )
 738             FROM books rownum__emulation
 739           WHERE rownum__emulation.id < me.id
 740         ) BETWEEN ? AND ?
 741         ORDER BY me.id
 742       )',
 743       [
 744         @select_bind,
 745         @where_bind,
 746         @group_bind,
 747         @having_bind,
 748         [ { sqlt_datatype => 'integer' } => 3 ],
 749         [ { sqlt_datatype => 'integer' } => 6 ],
 750       ],
 751     ],
 752     limit_offset_prefetch => [
 753       '(
 754         SELECT me.name, books.id, books.source, books.owner, books.title, books.price
 755           FROM (
 756             SELECT me.name, me.id
 757               FROM (
 758                 SELECT me.name, me.id  FROM owners me
 759               ) me
 760             WHERE (
 761               SELECT COUNT(*)
 762                 FROM owners rownum__emulation
 763               WHERE rownum__emulation.id < me.id
 764             ) BETWEEN ? AND ?
 765             ORDER BY me.id
 766           ) me
 767           LEFT JOIN books books
 768             ON books.owner = me.id
 769         ORDER BY me.id
 770       )',
 771       [
 772         [ { sqlt_datatype => 'integer' } => 1 ],
 773         [ { sqlt_datatype => 'integer' } => 3 ],
 774       ],
 775     ],
 776   }
 777 };
 778
 779 for my $limtype (sort keys %$tests) {
 780
 781   Test::Builder->new->is_passing or exit;
 782
 783   delete $schema->storage->_sql_maker->{_cached_syntax};
 784   $schema->storage->_sql_maker->limit_dialect ($limtype);
 785
 786   my $can_run = ($limtype eq $native_limit_dialect or $limtype eq 'GenericSubQ');
 787
 788   # chained search is necessary to exercise the recursive {where} parser
 789   my $rs = $schema->resultset('BooksInLibrary')->search({ 'me.title' => { '=' => 'kama sutra' } })->search({ source => { '!=', 'Study' } }, {
 790     columns => [ { identifier => 'me.id' }, 'owner.id', 'owner.name' ], # people actually do that. BLEH!!! :)
 791     join => 'owner',  # single-rel manual prefetch
 792     rows => 4,
 793     '+columns' => { bar => \['? * ?', [ $attr => 11 ], [ $attr => 12 ]], baz => \[ '?', [ $attr => 13 ]] },
 794     group_by => \[ '(me.id / ?), owner.id', [ $attr => 21 ] ],
 795     having => \[ '?', [ $attr => 31 ] ],
 796     ($limtype =~ /GenericSubQ/ ? ( order_by => 'me.id' ) : () ),  # needs a simple-column stable order to be happy
 797   });
 798
 799   #
 800   # not all tests run on all dialects (somewhere impossible, somewhere makes no sense)
 801   #
 802
 803   # only limit, no offset, no order
 804   if ($tests->{$limtype}{limit}) {
 805     is_same_sql_bind(
 806       $rs->as_query,
 807       @{$tests->{$limtype}{limit}},
 808       "$limtype: Unordered limit with select/group/having",
 809     );
 810
 811     lives_ok { $rs->all } "Grouped limit runs under $limtype"
 812       if $can_run;
 813   }
 814
 815   # limit + offset, no order
 816   if ($tests->{$limtype}{limit_offset}) {
 817     my $subrs = $rs->search({}, { offset => 3 });
 818     is_same_sql_bind(
 819       $subrs->as_query,
 820       @{$tests->{$limtype}{limit_offset}},
 821       "$limtype: Unordered limit+offset with select/group/having",
 822     );
 823
 824     lives_ok { $subrs->all } "Grouped limit+offset runs under $limtype"
 825       if $can_run;
 826   }
 827
 828   # order + limit, no offset
 829   $rs = $rs->search(undef, {
 830     order_by => [ \['? / ?', [ $attr => 1 ], [ $attr => 2 ]], \[ '?', [ $attr => 3 ]] ],
 831   });
 832
 833   if ($tests->{$limtype}{ordered_limit}) {
 834     is_same_sql_bind(
 835       $rs->as_query,
 836       @{$tests->{$limtype}{ordered_limit}},
 837       "$limtype: Ordered limit with select/group/having",
 838     );
 839
 840     lives_ok { $rs->all } "Grouped ordered limit runs under $limtype"
 841       if $can_run;
 842   }
 843
 844   # order + limit + offset
 845   if ($tests->{$limtype}{ordered_limit_offset}) {
 846     my $subrs = $rs->search({}, { offset => 3 });
 847     is_same_sql_bind(
 848       $subrs->as_query,
 849       @{$tests->{$limtype}{ordered_limit_offset}},
 850       "$limtype: Ordered limit+offset with select/group/having",
 851     );
 852
 853     lives_ok { $subrs->all } "Grouped ordered limit+offset runs under $limtype"
 854       if $can_run;
 855   }
 856
 857   # complex prefetch on partial-fetch root with limit
 858   my $pref_rs = $schema->resultset('Owners')->search({}, {
 859     rows => 3,
 860     offset => 1,
 861     columns => 'name',  # only the owner name, still prefetch all the books
 862     prefetch => 'books',
 863     ($limtype =~ /GenericSubQ/ ? ( order_by => 'me.id' ) : () ),  # needs a simple-column stable order to be happy
 864   });
 865
 866   is_same_sql_bind (
 867     $pref_rs->as_query,
 868     @{$tests->{$limtype}{limit_offset_prefetch}},
 869     "$limtype: Prefetch with limit+offset",
 870   ) if $tests->{$limtype}{limit_offset_prefetch};
 871
 872   if ($can_run) {
 873     lives_ok { is ($pref_rs->all, 1, 'Expected count of objects on limited prefetch') }
 874       "Complex limited prefetch runs under $limtype"
 875   }
 876 }
 877
 878 done_testing;