t/sqlmaker/limit_dialects/torture.t

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