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