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