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