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