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     ordered_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, me.price
 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
 713             ( me.price IS NULL AND rownum__emulation.price IS NOT NULL )
 714               OR
 715             (
 716               rownum__emulation.price > me.price
 717                 AND
 718               me.price IS NOT NULL
 719                 AND
 720               rownum__emulation.price IS NOT NULL
 721             )
 722               OR
 723             (
 724               (
 725                 me.price = rownum__emulation.price
 726                  OR
 727                 ( me.price IS NULL AND rownum__emulation.price IS NULL )
 728               )
 729                 AND
 730               rownum__emulation.id < me.id
 731             )
 732           ) < ?
 733         ORDER BY me.price DESC, me.id ASC
 734       )',
 735       [
 736         @select_bind,
 737         @where_bind,
 738         @group_bind,
 739         @having_bind,
 740         [ { sqlt_datatype => 'integer' } => 4 ],
 741       ],
 742     ],
 743     ordered_limit_offset => [
 744       '(
 745         SELECT me.id, owner__id, owner__name, bar, baz
 746           FROM (
 747             SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz, me.price
 748               FROM books me
 749               JOIN owners owner
 750                 ON owner.id = me.owner
 751             WHERE source != ? AND me.title = ? AND source = ?
 752             GROUP BY (me.id / ?), owner.id
 753             HAVING ?
 754           ) me
 755         WHERE (
 756           SELECT COUNT( * )
 757             FROM books rownum__emulation
 758           WHERE
 759             ( me.price IS NULL AND rownum__emulation.price IS NOT NULL )
 760               OR
 761             (
 762               rownum__emulation.price > me.price
 763                 AND
 764               me.price IS NOT NULL
 765                 AND
 766               rownum__emulation.price IS NOT NULL
 767             )
 768               OR
 769             (
 770               (
 771                 me.price = rownum__emulation.price
 772                  OR
 773                 ( me.price IS NULL AND rownum__emulation.price IS NULL )
 774               )
 775                 AND
 776               rownum__emulation.id < me.id
 777             )
 778           ) BETWEEN ? AND ?
 779         ORDER BY me.price DESC, me.id ASC
 780       )',
 781       [
 782         @select_bind,
 783         @where_bind,
 784         @group_bind,
 785         @having_bind,
 786         [ { sqlt_datatype => 'integer' } => 3 ],
 787         [ { sqlt_datatype => 'integer' } => 6 ],
 788       ],
 789     ],
 790     limit_offset_prefetch => [
 791       '(
 792         SELECT me.name, books.id, books.source, books.owner, books.title, books.price
 793           FROM (
 794             SELECT me.name, me.id
 795               FROM (
 796                 SELECT me.name, me.id
 797                   FROM owners me
 798               ) me
 799             WHERE
 800               (
 801                 SELECT COUNT(*)
 802                   FROM owners rownum__emulation
 803                 WHERE (
 804                   rownum__emulation.name < me.name
 805                     OR
 806                   (
 807                     me.name = rownum__emulation.name
 808                       AND
 809                     rownum__emulation.id > me.id
 810                   )
 811                 )
 812               ) BETWEEN ? AND ?
 813             ORDER BY me.name ASC, me.id DESC
 814           ) me
 815           LEFT JOIN books books
 816             ON books.owner = me.id
 817         ORDER BY me.name ASC, me.id DESC
 818       )',
 819       [
 820         [ { sqlt_datatype => 'integer' } => 1 ],
 821         [ { sqlt_datatype => 'integer' } => 3 ],
 822       ],
 823     ],
 824   }
 825 };
 826
 827 for my $limtype (sort keys %$tests) {
 828
 829   Test::Builder->new->is_passing or exit;
 830
 831   delete $schema->storage->_sql_maker->{_cached_syntax};
 832   $schema->storage->_sql_maker->limit_dialect ($limtype);
 833
 834   my $can_run = ($limtype eq $native_limit_dialect or $limtype eq 'GenericSubQ');
 835
 836   # chained search is necessary to exercise the recursive {where} parser
 837   my $rs = $schema->resultset('BooksInLibrary')->search({ 'me.title' => { '=' => 'kama sutra' } })->search({ source => { '!=', 'Study' } }, {
 838     columns => [ { identifier => 'me.id' }, 'owner.id', 'owner.name' ], # people actually do that. BLEH!!! :)
 839     join => 'owner',  # single-rel manual prefetch
 840     rows => 4,
 841     '+columns' => { bar => \['? * ?', [ $attr => 11 ], [ $attr => 12 ]], baz => \[ '?', [ $attr => 13 ]] },
 842     group_by => \[ '(me.id / ?), owner.id', [ $attr => 21 ] ],
 843     having => \[ '?', [ $attr => 31 ] ],
 844   });
 845
 846   #
 847   # not all tests run on all dialects (somewhere impossible, somewhere makes no sense)
 848   #
 849
 850   # only limit, no offset, no order
 851   if ($tests->{$limtype}{limit}) {
 852     is_same_sql_bind(
 853       $rs->as_query,
 854       @{$tests->{$limtype}{limit}},
 855       "$limtype: Unordered limit with select/group/having",
 856     );
 857
 858     lives_ok { $rs->all } "Grouped limit runs under $limtype"
 859       if $can_run;
 860   }
 861
 862   # limit + offset, no order
 863   if ($tests->{$limtype}{limit_offset}) {
 864     my $subrs = $rs->search({}, { offset => 3 });
 865     is_same_sql_bind(
 866       $subrs->as_query,
 867       @{$tests->{$limtype}{limit_offset}},
 868       "$limtype: Unordered limit+offset with select/group/having",
 869     );
 870
 871     lives_ok { $subrs->all } "Grouped limit+offset runs under $limtype"
 872       if $can_run;
 873   }
 874
 875   # order + limit, no offset
 876   $rs = $rs->search(undef, {
 877     order_by => ( $limtype =~ /GenericSubQ/
 878       ? [ { -desc => 'price' }, 'me.id', \[ 'owner.name + ?', [ {} => 'bah' ] ] ] # needs a same-table stable order to be happy
 879       : [ \['? / ?', [ $attr => 1 ], [ $attr => 2 ]], \[ '?', [ $attr => 3 ]] ]
 880     ),
 881   });
 882
 883   if ($tests->{$limtype}{ordered_limit}) {
 884     is_same_sql_bind(
 885       $rs->as_query,
 886       @{$tests->{$limtype}{ordered_limit}},
 887       "$limtype: Ordered limit with select/group/having",
 888     );
 889
 890     lives_ok { $rs->all } "Grouped ordered limit runs under $limtype"
 891       if $can_run;
 892   }
 893
 894   # order + limit + offset
 895   if ($tests->{$limtype}{ordered_limit_offset}) {
 896     my $subrs = $rs->search({}, { offset => 3 });
 897     is_same_sql_bind(
 898       $subrs->as_query,
 899       @{$tests->{$limtype}{ordered_limit_offset}},
 900       "$limtype: Ordered limit+offset with select/group/having",
 901     );
 902
 903     lives_ok { $subrs->all } "Grouped ordered limit+offset runs under $limtype"
 904       if $can_run;
 905   }
 906
 907   # complex prefetch on partial-fetch root with limit
 908   my $pref_rs = $schema->resultset('Owners')->search({}, {
 909     rows => 3,
 910     offset => 1,
 911     columns => 'name',  # only the owner name, still prefetch all the books
 912     prefetch => 'books',
 913     ($limtype !~ /GenericSubQ/ ? () : (
 914       # needs a same-table stable order to be happy
 915       order_by => [ { -asc => 'me.name' }, \'me.id DESC' ]
 916     )),
 917   });
 918
 919   is_same_sql_bind (
 920     $pref_rs->as_query,
 921     @{$tests->{$limtype}{limit_offset_prefetch}},
 922     "$limtype: Prefetch with limit+offset",
 923   ) if $tests->{$limtype}{limit_offset_prefetch};
 924
 925   if ($can_run) {
 926     lives_ok { is ($pref_rs->all, 1, 'Expected count of objects on limited prefetch') }
 927       "Complex limited prefetch runs under $limtype"
 928   }
 929 }
 930
 931 done_testing;