X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=t%2Fsqlmaker%2Flimit_dialects%2Ftorture_quoted.t;fp=t%2Fsqlmaker%2Flimit_dialects%2Ftorture_quoted.t;h=77439a0d623b595d4ee2c279e5008b8fb4a0664b;hb=d1f4d28df1b9f62c53e87ed0f93a9f6dadfbaf97;hp=0000000000000000000000000000000000000000;hpb=dc297536f438c5bba86229471b94bd489fe06c12;p=dbsrgits%2FDBIx-Class.git

diff --git a/t/sqlmaker/limit_dialects/torture_quoted.t b/t/sqlmaker/limit_dialects/torture_quoted.t
new file mode 100644
index 0000000..77439a0
--- /dev/null
+++ b/t/sqlmaker/limit_dialects/torture_quoted.t
@@ -0,0 +1,955 @@
+use strict;
+use warnings;
+
+use Test::More;
+use Test::Exception;
+use Storable 'dclone';
+use lib qw(t/lib);
+use DBICTest ':DiffSQL';
+
+my $schema = DBICTest->init_schema(
+  no_deploy => 1,
+  quote_char => '`',
+  name_sep => '.',
+);
+
+my $native_limit_dialect = $schema->storage->sql_maker->{limit_dialect};
+
+my $where_string = '`me`.`title` = ? AND `source` != ? AND `source` = ?';
+
+my @where_bind = (
+  [ {} => 'kama sutra' ],
+  [ {} => 'Study' ],
+  [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
+);
+my @select_bind = (
+  [ { sqlt_datatype => 'numeric' } => 11 ],
+  [ {} => 12 ],
+  [ { sqlt_datatype => 'integer', dbic_colname => 'me.id' } => 13 ],
+);
+my @group_bind = (
+  [ {} => 21 ],
+);
+my @having_bind = (
+  [ {} => 31 ],
+);
+my @order_bind = (
+  [ { sqlt_datatype => 'int' } => 1 ],
+  [ { sqlt_datatype => 'varchar', dbic_colname => 'name', sqlt_size => 100 } => 2 ],
+  [ {} => 3 ],
+);
+
+my $tests = {
+
+  LimitOffset => {
+    limit => [
+      "(
+        SELECT `me`.`id`, `owner`.`id`, `owner`.`name`, ? * ?, ?
+          FROM `books` `me`
+          JOIN `owners` `owner`
+            ON `owner`.`id` = `me`.`owner`
+        WHERE $where_string
+        GROUP BY (`me`.`id` / ?), `owner`.`id`
+        HAVING ?
+        LIMIT ?
+      )",
+      [
+        @select_bind,
+        @where_bind,
+        @group_bind,
+        @having_bind,
+        [ { sqlt_datatype => 'integer' } => 4 ],
+      ],
+    ],
+    limit_offset => [
+      "(
+        SELECT `me`.`id`, `owner`.`id`, `owner`.`name`, ? * ?, ?
+          FROM `books` `me`
+          JOIN `owners` `owner`
+            ON `owner`.`id` = `me`.`owner`
+        WHERE $where_string
+        GROUP BY (`me`.`id` / ?), `owner`.`id`
+        HAVING ?
+        LIMIT ?
+        OFFSET ?
+      )",
+      [
+        @select_bind,
+        @where_bind,
+        @group_bind,
+        @having_bind,
+        [ { sqlt_datatype => 'integer' } => 4 ],
+        [ { sqlt_datatype => 'integer' } => 3 ],
+      ],
+    ],
+    ordered_limit => [
+      "(
+        SELECT `me`.`id`, `owner`.`id`, `owner`.`name`, ? * ?, ?
+          FROM `books` `me`
+          JOIN `owners` `owner`
+            ON `owner`.`id` = `me`.`owner`
+        WHERE $where_string
+        GROUP BY (`me`.`id` / ?), `owner`.`id`
+        HAVING ?
+        ORDER BY ? / ?, ?
+        LIMIT ?
+      )",
+      [
+        @select_bind,
+        @where_bind,
+        @group_bind,
+        @having_bind,
+        @order_bind,
+        [ { sqlt_datatype => 'integer' } => 4 ],
+      ]
+    ],
+    ordered_limit_offset => [
+      "(
+        SELECT `me`.`id`, `owner`.`id`, `owner`.`name`, ? * ?, ?
+          FROM `books` `me`
+          JOIN `owners` `owner`
+            ON `owner`.`id` = `me`.`owner`
+        WHERE $where_string
+        GROUP BY (`me`.`id` / ?), `owner`.`id`
+        HAVING ?
+        ORDER BY ? / ?, ?
+        LIMIT ?
+        OFFSET ?
+      )",
+      [
+        @select_bind,
+        @where_bind,
+        @group_bind,
+        @having_bind,
+        @order_bind,
+        [ { sqlt_datatype => 'integer' } => 4 ],
+        [ { sqlt_datatype => 'integer' } => 3 ],
+      ],
+    ],
+    limit_offset_prefetch => [
+      "(
+        SELECT `me`.`name`, `books`.`id`, `books`.`source`, `books`.`owner`, `books`.`title`, `books`.`price`
+          FROM (
+            SELECT `me`.`name`, `me`.`id`
+              FROM `owners` `me`
+            LIMIT ? OFFSET ?
+          ) `me`
+          LEFT JOIN `books` `books`
+            ON `books`.`owner` = `me`.`id`
+      )",
+      [
+        [ { sqlt_datatype => 'integer' } => 3 ],
+        [ { sqlt_datatype => 'integer' } => 1 ],
+      ]
+    ],
+  },
+
+  LimitXY => {
+    ordered_limit_offset => [
+      "(
+        SELECT `me`.`id`, `owner`.`id`, `owner`.`name`, ? * ?, ?
+          FROM `books` `me`
+          JOIN `owners` `owner`
+            ON `owner`.`id` = `me`.`owner`
+        WHERE $where_string
+        GROUP BY (`me`.`id` / ?), `owner`.`id`
+        HAVING ?
+        ORDER BY ? / ?, ?
+        LIMIT ?, ?
+      )",
+      [
+        @select_bind,
+        @where_bind,
+        @group_bind,
+        @having_bind,
+        @order_bind,
+        [ { sqlt_datatype => 'integer' } => 3 ],
+        [ { sqlt_datatype => 'integer' } => 4 ],
+      ],
+    ],
+    limit_offset_prefetch => [
+      "(
+        SELECT `me`.`name`, `books`.`id`, `books`.`source`, `books`.`owner`, `books`.`title`, `books`.`price`
+          FROM (
+            SELECT `me`.`name`, `me`.`id`
+              FROM `owners` `me`
+            LIMIT ?,?
+          ) `me`
+          LEFT JOIN `books` `books`
+            ON `books`.`owner` = `me`.`id`
+      )",
+      [
+        [ { sqlt_datatype => 'integer' } => 1 ],
+        [ { sqlt_datatype => 'integer' } => 3 ],
+      ]
+    ],
+  },
+
+  SkipFirst => {
+    ordered_limit_offset => [
+      "(
+        SELECT SKIP ? FIRST ? `me`.`id`, `owner`.`id`, `owner`.`name`, ? * ?, ?
+          FROM `books` `me`
+          JOIN `owners` `owner`
+            ON `owner`.`id` = `me`.`owner`
+        WHERE $where_string
+        GROUP BY (`me`.`id` / ?), `owner`.`id`
+        HAVING ?
+        ORDER BY ? / ?, ?
+      )",
+      [
+        [ { sqlt_datatype => 'integer' } => 3 ],
+        [ { sqlt_datatype => 'integer' } => 4 ],
+        @select_bind,
+        @where_bind,
+        @group_bind,
+        @having_bind,
+        @order_bind,
+      ],
+    ],
+    limit_offset_prefetch => [
+      "(
+        SELECT `me`.`name`, `books`.`id`, `books`.`source`, `books`.`owner`, `books`.`title`, `books`.`price`
+          FROM (
+            SELECT SKIP ? FIRST ? `me`.`name`, `me`.`id`
+              FROM `owners` `me`
+          ) `me`
+          LEFT JOIN `books` `books`
+            ON `books`.`owner` = `me`.`id`
+      )",
+      [
+        [ { sqlt_datatype => 'integer' } => 1 ],
+        [ { sqlt_datatype => 'integer' } => 3 ],
+      ]
+    ],
+  },
+
+  FirstSkip => {
+    ordered_limit_offset => [
+      "(
+        SELECT FIRST ? SKIP ? `me`.`id`, `owner`.`id`, `owner`.`name`, ? * ?, ?
+          FROM `books` `me`
+          JOIN `owners` `owner`
+            ON `owner`.`id` = `me`.`owner`
+        WHERE $where_string
+        GROUP BY (`me`.`id` / ?), `owner`.`id`
+        HAVING ?
+        ORDER BY ? / ?, ?
+      )",
+      [
+        [ { sqlt_datatype => 'integer' } => 4 ],
+        [ { sqlt_datatype => 'integer' } => 3 ],
+        @select_bind,
+        @where_bind,
+        @group_bind,
+        @having_bind,
+        @order_bind,
+      ],
+    ],
+    limit_offset_prefetch => [
+      "(
+        SELECT `me`.`name`, `books`.`id`, `books`.`source`, `books`.`owner`, `books`.`title`, `books`.`price`
+          FROM (
+            SELECT FIRST ? SKIP ? `me`.`name`, `me`.`id`
+              FROM `owners` `me`
+          ) `me`
+          LEFT JOIN `books` `books`
+            ON `books`.`owner` = `me`.`id`
+      )",
+      [
+        [ { sqlt_datatype => 'integer' } => 3 ],
+        [ { sqlt_datatype => 'integer' } => 1 ],
+      ]
+    ],
+  },
+
+  RowNumberOver => do {
+    my $unordered_sql = "(
+      SELECT `me`.`id`, `owner__id`, `owner__name`, `bar`, `baz`
+        FROM (
+          SELECT `me`.`id`, `owner__id`, `owner__name`, `bar`, `baz`, ROW_NUMBER() OVER() AS `rno__row__index`
+            FROM (
+              SELECT `me`.`id`, `owner`.`id` AS `owner__id`, `owner`.`name` AS `owner__name`, ? * ? AS `bar`, ? AS `baz`
+                FROM `books` `me`
+                JOIN `owners` `owner`
+                  ON `owner`.`id` = `me`.`owner`
+              WHERE $where_string
+              GROUP BY (`me`.`id` / ?), `owner`.`id`
+              HAVING ?
+            ) `me`
+      ) `me`
+      WHERE `rno__row__index` >= ? AND `rno__row__index` <= ?
+    )";
+
+    my $ordered_sql = "(
+      SELECT `me`.`id`, `owner__id`, `owner__name`, `bar`, `baz`
+        FROM (
+          SELECT `me`.`id`, `owner__id`, `owner__name`, `bar`, `baz`, ROW_NUMBER() OVER( ORDER BY `ORDER__BY__001`, `ORDER__BY__002` ) AS `rno__row__index`
+            FROM (
+              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`
+                FROM `books` `me`
+                JOIN `owners` `owner`
+                  ON `owner`.`id` = `me`.`owner`
+              WHERE $where_string
+              GROUP BY (`me`.`id` / ?), `owner`.`id`
+              HAVING ?
+            ) `me`
+      ) `me`
+      WHERE `rno__row__index` >= ? AND `rno__row__index` <= ?
+    )";
+
+    {
+      limit => [$unordered_sql,
+        [
+          @select_bind,
+          @where_bind,
+          @group_bind,
+          @having_bind,
+          [ { sqlt_datatype => 'integer' } => 1 ],
+          [ { sqlt_datatype => 'integer' } => 4 ],
+        ],
+      ],
+      limit_offset => [$unordered_sql,
+        [
+          @select_bind,
+          @where_bind,
+          @group_bind,
+          @having_bind,
+          [ { sqlt_datatype => 'integer' } => 4 ],
+          [ { sqlt_datatype => 'integer' } => 7 ],
+        ],
+      ],
+      ordered_limit => [$ordered_sql,
+        [
+          @select_bind,
+          @order_bind,
+          @where_bind,
+          @group_bind,
+          @having_bind,
+          [ { sqlt_datatype => 'integer' } => 1 ],
+          [ { sqlt_datatype => 'integer' } => 4 ],
+        ],
+      ],
+      ordered_limit_offset => [$ordered_sql,
+        [
+          @select_bind,
+          @order_bind,
+          @where_bind,
+          @group_bind,
+          @having_bind,
+          [ { sqlt_datatype => 'integer' } => 4 ],
+          [ { sqlt_datatype => 'integer' } => 7 ],
+        ],
+      ],
+      limit_offset_prefetch => [
+        "(
+          SELECT `me`.`name`, `books`.`id`, `books`.`source`, `books`.`owner`, `books`.`title`, `books`.`price`
+            FROM (
+              SELECT `me`.`name`, `me`.`id`
+                FROM (
+                  SELECT `me`.`name`, `me`.`id`, ROW_NUMBER() OVER() AS `rno__row__index`
+                  FROM (
+                    SELECT `me`.`name`, `me`.`id`  FROM `owners` `me`
+                  ) `me`
+                ) `me`
+              WHERE `rno__row__index` >= ? AND `rno__row__index` <= ?
+            ) `me`
+            LEFT JOIN `books` `books`
+              ON `books`.`owner` = `me`.`id`
+        )",
+        [
+          [ { sqlt_datatype => 'integer' } => 2 ],
+          [ { sqlt_datatype => 'integer' } => 4 ],
+        ]
+      ],
+    };
+  },
+
+  RowNum => do {
+    my $limit_sql = sub {
+      sprintf "(
+        SELECT `me`.`id`, `owner__id`, `owner__name`, `bar`, `baz`
+          FROM (
+            SELECT `me`.`id`, `owner`.`id` AS `owner__id`, `owner`.`name` AS `owner__name`, ? * ? AS `bar`, ? AS `baz`
+              FROM `books` `me`
+              JOIN `owners` `owner`
+                ON `owner`.`id` = `me`.`owner`
+            WHERE $where_string
+            GROUP BY (`me`.`id` / ?), `owner`.`id`
+            HAVING ?
+            %s
+          ) `me`
+        WHERE ROWNUM <= ?
+      )", $_[0] || '';
+    };
+
+    {
+      limit => [ $limit_sql->(),
+        [
+          @select_bind,
+          @where_bind,
+          @group_bind,
+          @having_bind,
+          [ { sqlt_datatype => 'integer' } => 4 ],
+        ],
+      ],
+      limit_offset => [
+        "(
+          SELECT `me`.`id`, `owner__id`, `owner__name`, `bar`, `baz`
+            FROM (
+              SELECT `me`.`id`, `owner__id`, `owner__name`, `bar`, `baz`, ROWNUM AS `rownum__index`
+                FROM (
+                  SELECT `me`.`id`, `owner`.`id` AS `owner__id`, `owner`.`name` AS `owner__name`, ? * ? AS `bar`, ? AS `baz`
+                    FROM `books` `me`
+                    JOIN `owners` `owner`
+                      ON `owner`.`id` = `me`.`owner`
+                  WHERE $where_string
+                  GROUP BY (`me`.`id` / ?), `owner`.`id`
+                  HAVING ?
+                ) `me`
+            ) `me`
+          WHERE `rownum__index` BETWEEN ? AND ?
+        )",
+        [
+          @select_bind,
+          @where_bind,
+          @group_bind,
+          @having_bind,
+          [ { sqlt_datatype => 'integer' } => 4 ],
+          [ { sqlt_datatype => 'integer' } => 7 ],
+        ],
+      ],
+      ordered_limit => [ $limit_sql->('ORDER BY ? / ?, ?'),
+        [
+          @select_bind,
+          @where_bind,
+          @group_bind,
+          @having_bind,
+          @order_bind,
+          [ { sqlt_datatype => 'integer' } => 4 ],
+        ],
+      ],
+      ordered_limit_offset => [
+        "(
+          SELECT `me`.`id`, `owner__id`, `owner__name`, `bar`, `baz`
+            FROM (
+              SELECT `me`.`id`, `owner__id`, `owner__name`, `bar`, `baz`, ROWNUM AS `rownum__index`
+                FROM (
+                  SELECT `me`.`id`, `owner`.`id` AS `owner__id`, `owner`.`name` AS `owner__name`, ? * ? AS `bar`, ? AS `baz`
+                    FROM `books` `me`
+                    JOIN `owners` `owner`
+                      ON `owner`.`id` = `me`.`owner`
+                  WHERE $where_string
+                  GROUP BY (`me`.`id` / ?), `owner`.`id`
+                  HAVING ?
+                  ORDER BY ? / ?, ?
+                ) `me`
+              WHERE ROWNUM <= ?
+            ) `me`
+          WHERE `rownum__index` >= ?
+        )",
+        [
+          @select_bind,
+          @where_bind,
+          @group_bind,
+          @having_bind,
+          @order_bind,
+          [ { sqlt_datatype => 'integer' } => 7 ],
+          [ { sqlt_datatype => 'integer' } => 4 ],
+        ],
+      ],
+      limit_offset_prefetch => [
+        "(
+          SELECT `me`.`name`, `books`.`id`, `books`.`source`, `books`.`owner`, `books`.`title`, `books`.`price`
+            FROM (
+              SELECT `me`.`name`, `me`.`id`
+                FROM (
+                  SELECT `me`.`name`, `me`.`id`, ROWNUM AS `rownum__index`
+                    FROM (
+                      SELECT `me`.`name`, `me`.`id`
+                        FROM `owners` `me`
+                    ) `me`
+                ) `me` WHERE `rownum__index` BETWEEN ? AND ?
+            ) `me`
+            LEFT JOIN `books` `books`
+              ON `books`.`owner` = `me`.`id`
+        )",
+        [
+          [ { sqlt_datatype => 'integer' } => 2 ],
+          [ { sqlt_datatype => 'integer' } => 4 ],
+        ]
+      ],
+    };
+  },
+
+  FetchFirst => {
+    limit => [
+      "(
+        SELECT `me`.`id`, `owner`.`id`, `owner`.`name`, ? * ?, ?
+          FROM `books` `me`
+          JOIN `owners` `owner`
+            ON `owner`.`id` = `me`.`owner`
+        WHERE $where_string
+        GROUP BY (`me`.`id` / ?), `owner`.`id`
+        HAVING ?
+        FETCH FIRST 4 ROWS ONLY
+      )",
+      [
+        @select_bind,
+        @where_bind,
+        @group_bind,
+        @having_bind,
+      ],
+    ],
+    limit_offset => [
+      "(
+        SELECT `me`.`id`, `owner__id`, `owner__name`, `bar`, `baz`
+          FROM (
+            SELECT `me`.`id`, `owner`.`id` AS `owner__id`, `owner`.`name` AS `owner__name`, ? * ? AS `bar`, ? AS `baz`
+              FROM `books` `me`
+              JOIN `owners` `owner`
+                ON `owner`.`id` = `me`.`owner`
+            WHERE $where_string
+            GROUP BY (`me`.`id` / ?), `owner`.`id`
+            HAVING ?
+            ORDER BY `me`.`id`
+            FETCH FIRST 7 ROWS ONLY
+          ) `me`
+        ORDER BY `me`.`id` DESC
+        FETCH FIRST 4 ROWS ONLY
+      )",
+      [
+        @select_bind,
+        @where_bind,
+        @group_bind,
+        @having_bind,
+      ],
+    ],
+    ordered_limit => [
+      "(
+        SELECT `me`.`id`, `owner`.`id`, `owner`.`name`, ? * ?, ?
+          FROM `books` `me`
+          JOIN `owners` `owner`
+            ON `owner`.`id` = `me`.`owner`
+        WHERE $where_string
+        GROUP BY (`me`.`id` / ?), `owner`.`id`
+        HAVING ?
+        ORDER BY ? / ?, ?
+        FETCH FIRST 4 ROWS ONLY
+      )",
+      [
+        @select_bind,
+        @where_bind,
+        @group_bind,
+        @having_bind,
+        @order_bind,
+      ],
+    ],
+    ordered_limit_offset => [
+      "(
+        SELECT `me`.`id`, `owner__id`, `owner__name`, `bar`, `baz`
+          FROM (
+            SELECT `me`.`id`, `owner__id`, `owner__name`, `bar`, `baz`, `ORDER__BY__001`, `ORDER__BY__002`
+              FROM (
+                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`
+                  FROM `books` `me`
+                  JOIN `owners` `owner`
+                    ON `owner`.`id` = `me`.`owner`
+                WHERE $where_string
+                GROUP BY (`me`.`id` / ?), `owner`.`id`
+                HAVING ?
+                ORDER BY ? / ?, ?
+                FETCH FIRST 7 ROWS ONLY
+              ) `me`
+            ORDER BY `ORDER__BY__001` DESC, `ORDER__BY__002` DESC
+            FETCH FIRST 4 ROWS ONLY
+          ) `me`
+        ORDER BY `ORDER__BY__001`, `ORDER__BY__002`
+      )",
+      [
+        @select_bind,
+        @order_bind,
+        @where_bind,
+        @group_bind,
+        @having_bind,
+        @{ dclone \@order_bind },  # without this is_deeply throws a fit
+      ],
+    ],
+    limit_offset_prefetch => [
+      "(
+        SELECT `me`.`name`, `books`.`id`, `books`.`source`, `books`.`owner`, `books`.`title`, `books`.`price`
+          FROM (
+            SELECT `me`.`name`, `me`.`id`
+              FROM (
+                SELECT `me`.`name`, `me`.`id`
+                  FROM `owners` `me`
+                ORDER BY `me`.`id`
+                FETCH FIRST 4 ROWS ONLY
+              ) `me`
+              ORDER BY `me`.`id` DESC
+            FETCH FIRST 3 ROWS ONLY
+          ) `me`
+          LEFT JOIN `books` `books`
+            ON `books`.`owner` = `me`.`id`
+      )",
+      [],
+    ],
+  },
+
+  Top => {
+    limit => [
+      "(
+        SELECT TOP 4 `me`.`id`, `owner`.`id`, `owner`.`name`, ? * ?, ?
+          FROM `books` `me`
+          JOIN `owners` `owner`
+            ON `owner`.`id` = `me`.`owner`
+        WHERE $where_string
+        GROUP BY (`me`.`id` / ?), `owner`.`id`
+        HAVING ?
+      )",
+      [
+        @select_bind,
+        @where_bind,
+        @group_bind,
+        @having_bind,
+      ],
+    ],
+    limit_offset => [
+      "(
+        SELECT TOP 4 `me`.`id`, `owner__id`, `owner__name`, `bar`, `baz`
+          FROM (
+            SELECT TOP 7 `me`.`id`, `owner`.`id` AS `owner__id`, `owner`.`name` AS `owner__name`, ? * ? AS `bar`, ? AS `baz`
+              FROM `books` `me`
+              JOIN `owners` `owner`
+                ON `owner`.`id` = `me`.`owner`
+            WHERE $where_string
+            GROUP BY (`me`.`id` / ?), `owner`.`id`
+            HAVING ?
+            ORDER BY `me`.`id`
+          ) `me`
+        ORDER BY `me`.`id` DESC
+      )",
+      [
+        @select_bind,
+        @where_bind,
+        @group_bind,
+        @having_bind,
+      ],
+    ],
+    ordered_limit => [
+      "(
+        SELECT TOP 4 `me`.`id`, `owner`.`id`, `owner`.`name`, ? * ?, ?
+          FROM `books` `me`
+          JOIN `owners` `owner`
+            ON `owner`.`id` = `me`.`owner`
+        WHERE $where_string
+        GROUP BY (`me`.`id` / ?), `owner`.`id`
+        HAVING ?
+        ORDER BY ? / ?, ?
+      )",
+      [
+        @select_bind,
+        @where_bind,
+        @group_bind,
+        @having_bind,
+        @order_bind,
+      ],
+    ],
+    ordered_limit_offset => [
+      "(
+        SELECT `me`.`id`, `owner__id`, `owner__name`, `bar`, `baz`
+          FROM (
+            SELECT TOP 4 `me`.`id`, `owner__id`, `owner__name`, `bar`, `baz`, `ORDER__BY__001`, `ORDER__BY__002`
+              FROM (
+                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`
+                  FROM `books` `me`
+                  JOIN `owners` `owner`
+                    ON `owner`.`id` = `me`.`owner`
+                WHERE $where_string
+                GROUP BY (`me`.`id` / ?), `owner`.`id`
+                HAVING ?
+                ORDER BY ? / ?, ?
+              ) `me`
+            ORDER BY `ORDER__BY__001` DESC, `ORDER__BY__002` DESC
+          ) `me`
+        ORDER BY `ORDER__BY__001`, `ORDER__BY__002`
+      )",
+      [
+        @select_bind,
+        @order_bind,
+        @where_bind,
+        @group_bind,
+        @having_bind,
+        @{ dclone \@order_bind },  # without this is_deeply throws a fit
+      ],
+    ],
+    limit_offset_prefetch => [
+      "(
+        SELECT `me`.`name`, `books`.`id`, `books`.`source`, `books`.`owner`, `books`.`title`, `books`.`price`
+          FROM (
+            SELECT TOP 3 `me`.`name`, `me`.`id`
+              FROM (
+                SELECT TOP 4 `me`.`name`, `me`.`id`
+                  FROM `owners` `me`
+                ORDER BY `me`.`id`
+              ) `me`
+              ORDER BY `me`.`id` DESC
+          ) `me`
+          LEFT JOIN `books` `books`
+            ON `books`.`owner` = `me`.`id`
+      )",
+      [],
+    ],
+  },
+
+  GenericSubQ => {
+    ordered_limit => [
+      "(
+        SELECT `me`.`id`, `owner__id`, `owner__name`, `bar`, `baz`
+          FROM (
+            SELECT `me`.`id`, `owner`.`id` AS `owner__id`, `owner`.`name` AS `owner__name`, ? * ? AS `bar`, ? AS `baz`, `me`.`price`
+              FROM `books` `me`
+              JOIN `owners` `owner`
+                ON `owner`.`id` = `me`.`owner`
+            WHERE $where_string
+            GROUP BY (`me`.`id` / ?), `owner`.`id`
+            HAVING ?
+          ) `me`
+        WHERE (
+          SELECT COUNT( * )
+            FROM `books` `rownum__emulation`
+          WHERE
+            ( `me`.`price` IS NULL AND `rownum__emulation`.`price` IS NOT NULL )
+              OR
+            (
+              `rownum__emulation`.`price` > `me`.`price`
+                AND
+              `me`.`price` IS NOT NULL
+                AND
+              `rownum__emulation`.`price` IS NOT NULL
+            )
+              OR
+            (
+              (
+                `me`.`price` = `rownum__emulation`.`price`
+                 OR
+                ( `me`.`price` IS NULL AND `rownum__emulation`.`price` IS NULL )
+              )
+                AND
+              `rownum__emulation`.`id` < `me`.`id`
+            )
+          ) < ?
+        ORDER BY `me`.`price` DESC, `me`.`id` ASC
+      )",
+      [
+        @select_bind,
+        @where_bind,
+        @group_bind,
+        @having_bind,
+        [ { sqlt_datatype => 'integer' } => 4 ],
+      ],
+    ],
+    ordered_limit_offset => [
+      "(
+        SELECT `me`.`id`, `owner__id`, `owner__name`, `bar`, `baz`
+          FROM (
+            SELECT `me`.`id`, `owner`.`id` AS `owner__id`, `owner`.`name` AS `owner__name`, ? * ? AS `bar`, ? AS `baz`, `me`.`price`
+              FROM `books` `me`
+              JOIN `owners` `owner`
+                ON `owner`.`id` = `me`.`owner`
+            WHERE $where_string
+            GROUP BY (`me`.`id` / ?), `owner`.`id`
+            HAVING ?
+          ) `me`
+        WHERE (
+          SELECT COUNT( * )
+            FROM `books` `rownum__emulation`
+          WHERE
+            ( `me`.`price` IS NULL AND `rownum__emulation`.`price` IS NOT NULL )
+              OR
+            (
+              `rownum__emulation`.`price` > `me`.`price`
+                AND
+              `me`.`price` IS NOT NULL
+                AND
+              `rownum__emulation`.`price` IS NOT NULL
+            )
+              OR
+            (
+              (
+                `me`.`price` = `rownum__emulation`.`price`
+                 OR
+                ( `me`.`price` IS NULL AND `rownum__emulation`.`price` IS NULL )
+              )
+                AND
+              `rownum__emulation`.`id` < `me`.`id`
+            )
+          ) BETWEEN ? AND ?
+        ORDER BY `me`.`price` DESC, `me`.`id` ASC
+      )",
+      [
+        @select_bind,
+        @where_bind,
+        @group_bind,
+        @having_bind,
+        [ { sqlt_datatype => 'integer' } => 3 ],
+        [ { sqlt_datatype => 'integer' } => 6 ],
+      ],
+    ],
+    limit_offset_prefetch => [
+      "(
+        SELECT `me`.`name`, `books`.`id`, `books`.`source`, `books`.`owner`, `books`.`title`, `books`.`price`
+          FROM (
+            SELECT `me`.`name`, `me`.`id`
+              FROM (
+                SELECT `me`.`name`, `me`.`id`
+                  FROM `owners` `me`
+              ) `me`
+            WHERE
+              (
+                SELECT COUNT(*)
+                  FROM `owners` `rownum__emulation`
+                WHERE (
+                  `rownum__emulation`.`name` < `me`.`name`
+                    OR
+                  (
+                    `me`.`name` = `rownum__emulation`.`name`
+                      AND
+                    `rownum__emulation`.`id` > `me`.`id`
+                  )
+                )
+              ) BETWEEN ? AND ?
+            ORDER BY `me`.`name` ASC, `me`.`id` DESC
+          ) `me`
+          LEFT JOIN `books` `books`
+            ON `books`.`owner` = `me`.`id`
+        ORDER BY `me`.`name` ASC, `me`.`id` DESC
+      )",
+      [
+        [ { sqlt_datatype => 'integer' } => 1 ],
+        [ { sqlt_datatype => 'integer' } => 3 ],
+      ],
+    ],
+  }
+};
+
+for my $limtype (sort keys %$tests) {
+
+  Test::Builder->new->is_passing or exit;
+
+  delete $schema->storage->_sql_maker->{_cached_syntax};
+  $schema->storage->_sql_maker->limit_dialect ($limtype);
+
+  # not deploying, so can't run
+  my $can_run = 0 && ($limtype eq $native_limit_dialect or $limtype eq 'GenericSubQ');
+
+  # chained search is necessary to exercise the recursive {where} parser
+  my $rs = $schema->resultset('BooksInLibrary')->search(
+    { 'me.title' => { '=' => \[ '?', 'kama sutra' ] } }
+  )->search(
+    { source => { '!=', \[ '?', [ {} => 'Study' ] ] } },
+    {
+      columns => [ { identifier => 'me.id' }, 'owner.id', 'owner.name' ], # people actually do that. BLEH!!! :)
+      join => 'owner',  # single-rel manual prefetch
+      rows => 4,
+      '+columns' => { bar => \['? * ?', [ \ 'numeric' => 11 ], 12 ], baz => \[ '?', [ 'me.id' => 13 ] ] },
+      group_by => \[ '(`me`.`id` / ?), `owner`.`id`', 21 ],
+      having => \[ '?', 31 ],
+    }
+  );
+
+  #
+  # not all tests run on all dialects (somewhere impossible, somewhere makes no sense)
+  #
+
+  # only limit, no offset, no order
+  if ($tests->{$limtype}{limit}) {
+    lives_ok {
+      is_same_sql_bind(
+        $rs->as_query,
+        @{$tests->{$limtype}{limit}},
+        "$limtype: Unordered limit with select/group/having",
+      );
+
+      $rs->all if $can_run;
+    } "Grouped limit under $limtype";
+  }
+
+  # limit + offset, no order
+  if ($tests->{$limtype}{limit_offset}) {
+
+    lives_ok {
+      my $subrs = $rs->search({}, { offset => 3 });
+
+      is_same_sql_bind(
+        $subrs->as_query,
+        @{$tests->{$limtype}{limit_offset}},
+        "$limtype: Unordered limit+offset with select/group/having",
+      );
+
+      $subrs->all if $can_run;
+    } "Grouped limit+offset runs under $limtype";
+  }
+
+  # order + limit, no offset
+  $rs = $rs->search(undef, {
+    order_by => ( $limtype =~ /GenericSubQ/
+      ? [ { -desc => 'price' }, 'me.id', \[ '`owner`.`name` + ?', 'bah' ] ] # needs a same-table stable order to be happy
+      : [ \['? / ?', [ \ 'int' => 1 ], [ name => 2 ]], \[ '?', 3 ] ]
+    ),
+  });
+
+  if ($tests->{$limtype}{ordered_limit}) {
+
+    lives_ok {
+      is_same_sql_bind(
+        $rs->as_query,
+        @{$tests->{$limtype}{ordered_limit}},
+        "$limtype: Ordered limit with select/group/having",
+      );
+
+      $rs->all if $can_run;
+    } "Grouped ordered limit runs under $limtype"
+  }
+
+  # order + limit + offset
+  if ($tests->{$limtype}{ordered_limit_offset}) {
+    lives_ok {
+      my $subrs = $rs->search({}, { offset => 3 });
+
+      is_same_sql_bind(
+        $subrs->as_query,
+        @{$tests->{$limtype}{ordered_limit_offset}},
+        "$limtype: Ordered limit+offset with select/group/having",
+      );
+
+      $subrs->all if $can_run;
+    } "Grouped ordered limit+offset runs under $limtype";
+  }
+
+  # complex prefetch on partial-fetch root with limit
+  my $pref_rs = $schema->resultset('Owners')->search({}, {
+    rows => 3,
+    offset => 1,
+    columns => 'name',  # only the owner name, still prefetch all the books
+    prefetch => 'books',
+    ($limtype !~ /GenericSubQ/ ? () : (
+      # needs a same-table stable order to be happy
+      order_by => [ { -asc => 'me.name' }, \ '`me`.`id` DESC' ]
+    )),
+  });
+
+  lives_ok {
+    is_same_sql_bind (
+      $pref_rs->as_query,
+      @{$tests->{$limtype}{limit_offset_prefetch}},
+      "$limtype: Prefetch with limit+offset",
+    ) if $tests->{$limtype}{limit_offset_prefetch};
+
+    is ($pref_rs->all, 1, 'Expected count of objects on limited prefetch')
+      if $can_run;
+  } "Complex limited prefetch runs under $limtype";
+}
+
+done_testing;