X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=t%2Fsqlmaker%2Flimit_dialects%2Ftorture.t;h=9d8d23db8098506e4a815df000341d9e12888130;hb=d16df2398243321f1bd43fcc625d2e14852af0c9;hp=44df4407a5172290851bb5fffa46bbb0e265cf67;hpb=c97338007ede15e7c62095a642b3de382a3508bd;p=dbsrgits%2FDBIx-Class.git

diff --git a/t/sqlmaker/limit_dialects/torture.t b/t/sqlmaker/limit_dialects/torture.t
index 44df440..9d8d23d 100644
--- a/t/sqlmaker/limit_dialects/torture.t
+++ b/t/sqlmaker/limit_dialects/torture.t
@@ -3,48 +3,120 @@ use warnings;
 
 use Test::More;
 use Test::Exception;
+use Storable 'dclone';
 use lib qw(t/lib);
-use DBICTest;
-use DBIC::SqlMakerTest;
+use DBICTest ':DiffSQL';
 
 my $schema = DBICTest->init_schema;
 my $native_limit_dialect = $schema->storage->sql_maker->{limit_dialect};
 
-my $attr = {};
+my $where_string = 'me.title = ? AND source != ? AND source = ?';
+
 my @where_bind = (
-  [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Study' ],
-  [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.title' } => 'kama sutra' ],
+  [ {} => 'kama sutra' ],
+  [ {} => 'Study' ],
   [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
 );
 my @select_bind = (
-  [ $attr => 11 ], [ $attr => 12 ], [ $attr => 13 ],
+  [ { sqlt_datatype => 'numeric' } => 11 ],
+  [ {} => 12 ],
+  [ { sqlt_datatype => 'integer', dbic_colname => 'me.id' } => 13 ],
 );
 my @group_bind = (
-  [ $attr => 21 ],
+  [ {} => 21 ],
 );
 my @having_bind = (
-  [ $attr => 31 ],
+  [ {} => 31 ],
 );
 my @order_bind = (
-  [ $attr => 1 ], [ $attr => 2 ], [ $attr => 3 ],
+  [ { sqlt_datatype => 'int' } => 1 ],
+  [ { sqlt_datatype => 'varchar', dbic_colname => 'name', sqlt_size => 100 } => 2 ],
+  [ {} => 3 ],
 );
 
 my $tests = {
 
   LimitOffset => {
+    limit_plain => [
+      "( SELECT me.artistid FROM artist me LIMIT ? )",
+      [
+        [ { sqlt_datatype => 'integer' } => 5 ]
+      ],
+    ],
+    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 source != ? AND me.title = ? AND source = ?
-        GROUP BY avg(me.id / ?)
+        WHERE $where_string
+        GROUP BY (me.id / ?), owner.id
         HAVING ?
         ORDER BY ? / ?, ?
         LIMIT ?
         OFFSET ?
-      )',
+      )",
       [
         @select_bind,
         @where_bind,
@@ -56,7 +128,7 @@ my $tests = {
       ],
     ],
     limit_offset_prefetch => [
-      '(
+      "(
         SELECT me.name, books.id, books.source, books.owner, books.title, books.price
           FROM (
             SELECT me.name, me.id
@@ -65,8 +137,7 @@ my $tests = {
           ) me
           LEFT JOIN books books
             ON books.owner = me.id
-        ORDER BY books.owner
-      )',
+      )",
       [
         [ { sqlt_datatype => 'integer' } => 3 ],
         [ { sqlt_datatype => 'integer' } => 1 ],
@@ -75,18 +146,24 @@ my $tests = {
   },
 
   LimitXY => {
+    limit_plain => [
+      "( SELECT me.artistid FROM artist me LIMIT ? )",
+      [
+        [ { sqlt_datatype => 'integer' } => 5 ]
+      ],
+    ],
     ordered_limit_offset => [
-      '(
+      "(
         SELECT me.id, owner.id, owner.name, ? * ?, ?
           FROM books me
           JOIN owners owner
             ON owner.id = me.owner
-        WHERE source != ? AND me.title = ? AND source = ?
-        GROUP BY avg(me.id / ?)
+        WHERE $where_string
+        GROUP BY (me.id / ?), owner.id
         HAVING ?
         ORDER BY ? / ?, ?
         LIMIT ?, ?
-      )',
+      )",
       [
         @select_bind,
         @where_bind,
@@ -98,7 +175,7 @@ my $tests = {
       ],
     ],
     limit_offset_prefetch => [
-      '(
+      "(
         SELECT me.name, books.id, books.source, books.owner, books.title, books.price
           FROM (
             SELECT me.name, me.id
@@ -107,8 +184,7 @@ my $tests = {
           ) me
           LEFT JOIN books books
             ON books.owner = me.id
-        ORDER BY books.owner
-      )',
+      )",
       [
         [ { sqlt_datatype => 'integer' } => 1 ],
         [ { sqlt_datatype => 'integer' } => 3 ],
@@ -117,17 +193,23 @@ my $tests = {
   },
 
   SkipFirst => {
+    limit_plain => [
+      "( SELECT FIRST ? me.artistid FROM artist me )",
+      [
+        [ { sqlt_datatype => 'integer' } => 5 ]
+      ],
+    ],
     ordered_limit_offset => [
-      '(
+      "(
         SELECT SKIP ? FIRST ? me.id, owner.id, owner.name, ? * ?, ?
           FROM books me
           JOIN owners owner
             ON owner.id = me.owner
-        WHERE source != ? AND me.title = ? AND source = ?
-        GROUP BY avg(me.id / ?)
+        WHERE $where_string
+        GROUP BY (me.id / ?), owner.id
         HAVING ?
         ORDER BY ? / ?, ?
-      )',
+      )",
       [
         [ { sqlt_datatype => 'integer' } => 3 ],
         [ { sqlt_datatype => 'integer' } => 4 ],
@@ -139,7 +221,7 @@ my $tests = {
       ],
     ],
     limit_offset_prefetch => [
-      '(
+      "(
         SELECT me.name, books.id, books.source, books.owner, books.title, books.price
           FROM (
             SELECT SKIP ? FIRST ? me.name, me.id
@@ -147,8 +229,7 @@ my $tests = {
           ) me
           LEFT JOIN books books
             ON books.owner = me.id
-        ORDER BY books.owner
-      )',
+      )",
       [
         [ { sqlt_datatype => 'integer' } => 1 ],
         [ { sqlt_datatype => 'integer' } => 3 ],
@@ -157,17 +238,23 @@ my $tests = {
   },
 
   FirstSkip => {
+    limit_plain => [
+      "( SELECT FIRST ? me.artistid FROM artist me )",
+      [
+        [ { sqlt_datatype => 'integer' } => 5 ]
+      ],
+    ],
     ordered_limit_offset => [
-      '(
+      "(
         SELECT FIRST ? SKIP ? me.id, owner.id, owner.name, ? * ?, ?
           FROM books me
           JOIN owners owner
             ON owner.id = me.owner
-        WHERE source != ? AND me.title = ? AND source = ?
-        GROUP BY avg(me.id / ?)
+        WHERE $where_string
+        GROUP BY (me.id / ?), owner.id
         HAVING ?
         ORDER BY ? / ?, ?
-      )',
+      )",
       [
         [ { sqlt_datatype => 'integer' } => 4 ],
         [ { sqlt_datatype => 'integer' } => 3 ],
@@ -179,7 +266,7 @@ my $tests = {
       ],
     ],
     limit_offset_prefetch => [
-      '(
+      "(
         SELECT me.name, books.id, books.source, books.owner, books.title, books.price
           FROM (
             SELECT FIRST ? SKIP ? me.name, me.id
@@ -187,8 +274,7 @@ my $tests = {
           ) me
           LEFT JOIN books books
             ON books.owner = me.id
-        ORDER BY books.owner
-      )',
+      )",
       [
         [ { sqlt_datatype => 'integer' } => 3 ],
         [ { sqlt_datatype => 'integer' } => 1 ],
@@ -197,7 +283,7 @@ my $tests = {
   },
 
   RowNumberOver => do {
-    my $unordered_sql = '(
+    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
@@ -206,15 +292,15 @@ my $tests = {
                 FROM books me
                 JOIN owners owner
                   ON owner.id = me.owner
-              WHERE source != ? AND me.title = ? AND source = ?
-              GROUP BY avg(me.id / ?)
+              WHERE $where_string
+              GROUP BY (me.id / ?), owner.id
               HAVING ?
             ) me
       ) me
       WHERE rno__row__index >= ? AND rno__row__index <= ?
-    )';
+    )";
 
-    my $ordered_sql = '(
+    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
@@ -224,15 +310,32 @@ my $tests = {
                 FROM books me
                 JOIN owners owner
                   ON owner.id = me.owner
-              WHERE source != ? AND me.title = ? AND source = ?
-              GROUP BY avg(me.id / ?)
+              WHERE $where_string
+              GROUP BY (me.id / ?), owner.id
               HAVING ?
             ) me
       ) me
       WHERE rno__row__index >= ? AND rno__row__index <= ?
-    )';
+    )";
 
     {
+      limit_plain => [
+        "(
+          SELECT me.artistid
+            FROM (
+              SELECT me.artistid, ROW_NUMBER() OVER(  ) AS rno__row__index
+                FROM (
+                  SELECT me.artistid
+                    FROM artist me
+                ) me
+            ) me
+          WHERE rno__row__index >= ? AND rno__row__index <= ?
+        )",
+        [
+          [ { sqlt_datatype => 'integer' } => 1 ],
+          [ { sqlt_datatype => 'integer' } => 5 ],
+        ],
+      ],
       limit => [$unordered_sql,
         [
           @select_bind,
@@ -276,7 +379,7 @@ my $tests = {
         ],
       ],
       limit_offset_prefetch => [
-        '(
+        "(
           SELECT me.name, books.id, books.source, books.owner, books.title, books.price
             FROM (
               SELECT me.name, me.id
@@ -290,8 +393,7 @@ my $tests = {
             ) me
             LEFT JOIN books books
               ON books.owner = me.id
-          ORDER BY books.owner
-        )',
+        )",
         [
           [ { sqlt_datatype => 'integer' } => 2 ],
           [ { sqlt_datatype => 'integer' } => 4 ],
@@ -302,23 +404,36 @@ my $tests = {
 
   RowNum => do {
     my $limit_sql = sub {
-      sprintf '(
+      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 source != ? AND me.title = ? AND source = ?
-            GROUP BY avg(me.id / ?)
+            WHERE $where_string
+            GROUP BY (me.id / ?), owner.id
             HAVING ?
             %s
           ) me
         WHERE ROWNUM <= ?
-      )', $_[0] || '';
+      )", $_[0] || '';
     };
 
     {
+      limit_plain => [
+        "(
+          SELECT me.artistid
+            FROM (
+              SELECT me.artistid
+                FROM artist me
+            ) me
+          WHERE ROWNUM <= ?
+        )",
+        [
+          [ { sqlt_datatype => 'integer' } => 5 ],
+        ],
+      ],
       limit => [ $limit_sql->(),
         [
           @select_bind,
@@ -329,22 +444,22 @@ my $tests = {
         ],
       ],
       limit_offset => [
-        '(
+        "(
           SELECT me.id, owner__id, owner__name, bar, baz
             FROM (
-              SELECT me.id, owner__id, owner__name, bar, baz, ROWNUM rownum__index
+              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 source != ? AND me.title = ? AND source = ?
-                  GROUP BY avg(me.id / ?)
+                  WHERE $where_string
+                  GROUP BY (me.id / ?), owner.id
                   HAVING ?
                 ) me
             ) me
           WHERE rownum__index BETWEEN ? AND ?
-        )',
+        )",
         [
           @select_bind,
           @where_bind,
@@ -365,24 +480,24 @@ my $tests = {
         ],
       ],
       ordered_limit_offset => [
-        '(
+        "(
           SELECT me.id, owner__id, owner__name, bar, baz
             FROM (
-              SELECT me.id, owner__id, owner__name, bar, baz, ROWNUM rownum__index
+              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 source != ? AND me.title = ? AND source = ?
-                  GROUP BY avg(me.id / ?)
+                  WHERE $where_string
+                  GROUP BY (me.id / ?), owner.id
                   HAVING ?
                   ORDER BY ? / ?, ?
                 ) me
               WHERE ROWNUM <= ?
             ) me
           WHERE rownum__index >= ?
-        )',
+        )",
         [
           @select_bind,
           @where_bind,
@@ -394,12 +509,12 @@ my $tests = {
         ],
       ],
       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 rownum__index
+                  SELECT me.name, me.id, ROWNUM AS rownum__index
                     FROM (
                       SELECT me.name, me.id
                         FROM owners me
@@ -408,8 +523,7 @@ my $tests = {
             ) me
             LEFT JOIN books books
               ON books.owner = me.id
-          ORDER BY books.owner
-        )',
+        )",
         [
           [ { sqlt_datatype => 'integer' } => 2 ],
           [ { sqlt_datatype => 'integer' } => 4 ],
@@ -419,17 +533,21 @@ my $tests = {
   },
 
   FetchFirst => {
+    limit_plain => [
+      "( SELECT me.artistid FROM artist me FETCH FIRST 5 ROWS ONLY )",
+      [],
+    ],
     limit => [
-      '(
+      "(
         SELECT me.id, owner.id, owner.name, ? * ?, ?
           FROM books me
           JOIN owners owner
             ON owner.id = me.owner
-        WHERE source != ? AND me.title = ? AND source = ?
-        GROUP BY avg(me.id / ?)
+        WHERE $where_string
+        GROUP BY (me.id / ?), owner.id
         HAVING ?
         FETCH FIRST 4 ROWS ONLY
-      )',
+      )",
       [
         @select_bind,
         @where_bind,
@@ -438,22 +556,22 @@ my $tests = {
       ],
     ],
     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 source != ? AND me.title = ? AND source = ?
-            GROUP BY avg(me.id / ?)
+            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,
@@ -462,17 +580,17 @@ my $tests = {
       ],
     ],
     ordered_limit => [
-      '(
+      "(
         SELECT me.id, owner.id, owner.name, ? * ?, ?
           FROM books me
           JOIN owners owner
             ON owner.id = me.owner
-        WHERE source != ? AND me.title = ? AND source = ?
-        GROUP BY avg(me.id / ?)
+        WHERE $where_string
+        GROUP BY (me.id / ?), owner.id
         HAVING ?
         ORDER BY ? / ?, ?
         FETCH FIRST 4 ROWS ONLY
-      )',
+      )",
       [
         @select_bind,
         @where_bind,
@@ -482,7 +600,7 @@ my $tests = {
       ],
     ],
     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
@@ -491,8 +609,8 @@ my $tests = {
                   FROM books me
                   JOIN owners owner
                     ON owner.id = me.owner
-                WHERE source != ? AND me.title = ? AND source = ?
-                GROUP BY avg(me.id / ?)
+                WHERE $where_string
+                GROUP BY (me.id / ?), owner.id
                 HAVING ?
                 ORDER BY ? / ?, ?
                 FETCH FIRST 7 ROWS ONLY
@@ -501,18 +619,18 @@ my $tests = {
             FETCH FIRST 4 ROWS ONLY
           ) me
         ORDER BY ORDER__BY__001, ORDER__BY__002
-      )',
+      )",
       [
         @select_bind,
         @order_bind,
         @where_bind,
         @group_bind,
         @having_bind,
-        (map { [ @$_ ] } @order_bind),  # without this is_deeply throws a fit
+        @{ 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
@@ -527,23 +645,26 @@ my $tests = {
           ) me
           LEFT JOIN books books
             ON books.owner = me.id
-        ORDER BY books.owner
-      )',
+      )",
       [],
     ],
   },
 
   Top => {
+    limit_plain => [
+      "( SELECT TOP 5 me.artistid FROM artist me )",
+      [],
+    ],
     limit => [
-      '(
+      "(
         SELECT TOP 4 me.id, owner.id, owner.name, ? * ?, ?
           FROM books me
           JOIN owners owner
             ON owner.id = me.owner
-        WHERE source != ? AND me.title = ? AND source = ?
-        GROUP BY avg(me.id / ?)
+        WHERE $where_string
+        GROUP BY (me.id / ?), owner.id
         HAVING ?
-      )',
+      )",
       [
         @select_bind,
         @where_bind,
@@ -552,20 +673,20 @@ my $tests = {
       ],
     ],
     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 source != ? AND me.title = ? AND source = ?
-            GROUP BY avg(me.id / ?)
+            WHERE $where_string
+            GROUP BY (me.id / ?), owner.id
             HAVING ?
             ORDER BY me.id
           ) me
         ORDER BY me.id DESC
-      )',
+      )",
       [
         @select_bind,
         @where_bind,
@@ -574,16 +695,16 @@ my $tests = {
       ],
     ],
     ordered_limit => [
-      '(
+      "(
         SELECT TOP 4 me.id, owner.id, owner.name, ? * ?, ?
           FROM books me
           JOIN owners owner
             ON owner.id = me.owner
-        WHERE source != ? AND me.title = ? AND source = ?
-        GROUP BY avg(me.id / ?)
+        WHERE $where_string
+        GROUP BY (me.id / ?), owner.id
         HAVING ?
         ORDER BY ? / ?, ?
-      )',
+      )",
       [
         @select_bind,
         @where_bind,
@@ -593,7 +714,7 @@ my $tests = {
       ],
     ],
     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
@@ -602,26 +723,26 @@ my $tests = {
                   FROM books me
                   JOIN owners owner
                     ON owner.id = me.owner
-                WHERE source != ? AND me.title = ? AND source = ?
-                GROUP BY avg(me.id / ?)
+                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,
-        (map { [ @$_ ] } @order_bind),  # without this is_deeply throws a fit
+        @{ 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
@@ -634,83 +755,69 @@ my $tests = {
           ) me
           LEFT JOIN books books
             ON books.owner = me.id
-        ORDER BY books.owner
-      )',
+      )",
       [],
     ],
   },
 
-  RowCountOrGenericSubQ => {
-    limit => [
-      '(
-        SET ROWCOUNT 4
-        SELECT me.id, owner.id, owner.name, ? * ?, ?
-          FROM books me
-          JOIN owners owner
-            ON owner.id = me.owner
-        WHERE source != ? AND me.title = ? AND source = ?
-        GROUP BY avg(me.id / ?)
-        HAVING ?
-        ORDER BY me.id
-        SET ROWCOUNT 0
-      )',
-      [
-        @select_bind,
-        @where_bind,
-        @group_bind,
-        @having_bind,
-      ],
-    ],
-    limit_offset => [
-      '(
-        SELECT me.id, owner__id, owner__name, bar, baz
+  GenericSubQ => {
+    limit_plain => [
+      "(
+        SELECT me.artistid
           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 source != ? AND me.title = ? AND source = ?
-            GROUP BY avg( me.id / ? )
-            HAVING ?
+            SELECT me.artistid
+              FROM artist me
           ) me
-        WHERE (
-          SELECT COUNT( * )
-            FROM books rownum__emulation
-          WHERE rownum__emulation.id < me.id
-        ) BETWEEN ? AND ?
-        ORDER BY me.id
-      )',
+        WHERE
+          (
+            SELECT COUNT(*)
+              FROM artist rownum__emulation
+            WHERE rownum__emulation.artistid < me.artistid
+          ) < ?
+        ORDER BY me.artistid ASC
+      )",
       [
-        @select_bind,
-        @where_bind,
-        @group_bind,
-        @having_bind,
-        [ { sqlt_datatype => 'integer' } => 3 ],
-        [ { sqlt_datatype => 'integer' } => 6 ],
+        [ { sqlt_datatype => 'integer' } => 5 ]
       ],
     ],
-  },
-
-  GenericSubQ => {
-    limit => [
-      '(
+    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
+            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 source != ? AND me.title = ? AND source = ?
-            GROUP BY avg( me.id / ? )
+            WHERE $where_string
+            GROUP BY (me.id / ?), owner.id
             HAVING ?
           ) me
         WHERE (
           SELECT COUNT( * )
             FROM books rownum__emulation
-          WHERE rownum__emulation.id < me.id
-        ) < ?
-        ORDER BY me.id
-      )',
+          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,
@@ -719,25 +826,44 @@ my $tests = {
         [ { sqlt_datatype => 'integer' } => 4 ],
       ],
     ],
-    limit_offset => [
-      '(
+    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
+            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 source != ? AND me.title = ? AND source = ?
-            GROUP BY avg( me.id / ? )
+            WHERE $where_string
+            GROUP BY (me.id / ?), owner.id
             HAVING ?
           ) me
         WHERE (
           SELECT COUNT( * )
             FROM books rownum__emulation
-          WHERE rownum__emulation.id < me.id
-        ) BETWEEN ? AND ?
-        ORDER BY me.id
-      )',
+          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,
@@ -748,24 +874,34 @@ my $tests = {
       ],
     ],
     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
+                SELECT me.name, me.id
+                  FROM owners me
               ) me
-            WHERE (
-              SELECT COUNT(*)
-                FROM owners rownum__emulation
-              WHERE rownum__emulation.id < me.id
-            ) BETWEEN ? AND ?
-            ORDER BY me.id
+            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.id, books.owner
-      )',
+        ORDER BY me.name ASC, me.id DESC
+      )",
       [
         [ { sqlt_datatype => 'integer' } => 1 ],
         [ { sqlt_datatype => 'integer' } => 3 ],
@@ -781,52 +917,110 @@ for my $limtype (sort keys %$tests) {
   delete $schema->storage->_sql_maker->{_cached_syntax};
   $schema->storage->_sql_maker->limit_dialect ($limtype);
 
+  # do the simplest thing possible first
+  if ($tests->{$limtype}{limit_plain}) {
+    is_same_sql_bind(
+      $schema->resultset('Artist')->search(
+        [ -and => [ {}, [] ], -or => [ {}, [] ] ],
+        {
+          columns => 'artistid',
+          join => [ {}, [ [ {}, {} ] ], {} ],
+          prefetch => [ [ [ {}, [] ], {} ], {}, [ {} ] ],
+          order_by => ( $limtype eq 'GenericSubQ' ? 'artistid' : [] ),
+          group_by => [],
+          rows => 5,
+          offset => 0,
+        }
+      )->as_query,
+      @{$tests->{$limtype}{limit_plain}},
+      "$limtype: Plain unordered ungrouped select with limit and no offset",
+    )
+  }
+
   # 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 => \['? * ?', [ $attr => 11 ], [ $attr => 12 ]], baz => \[ '?', [ $attr => 13 ]] },
-    group_by => \[ 'avg(me.id / ?)', [ $attr => 21 ] ],
-    having => \[ '?', [ $attr => 31 ] ],
-    ($limtype =~ /GenericSubQ/ ? ( order_by => 'me.id' ) : () ),  # needs a simple-column stable order to be happy
-  });
+  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)
   #
+  my $can_run = ($limtype eq $native_limit_dialect or $limtype eq 'GenericSubQ');
 
   # only limit, no offset, no order
-  is_same_sql_bind(
-    $rs->as_query,
-    @{$tests->{$limtype}{limit}},
-    "$limtype: Unordered limit with select/group/having",
-  ) if $tests->{$limtype}{limit};
+  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
-  is_same_sql_bind(
-    $rs->search({}, { offset => 3 })->as_query,
-    @{$tests->{$limtype}{limit_offset}},
-    "$limtype: Unordered limit+offset with select/group/having",
-  ) if $tests->{$limtype}{limit_offset};
+  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 => [ \['? / ?', [ $attr => 1 ], [ $attr => 2 ]], \[ '?', [ $attr => 3 ]] ],
+    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 ] ]
+    ),
   });
 
-  is_same_sql_bind(
-    $rs->as_query,
-    @{$tests->{$limtype}{ordered_limit}},
-    "$limtype: Ordered limit with select/group/having",
-  ) if $tests->{$limtype}{ordered_limit};
+  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
-  is_same_sql_bind(
-    $rs->search({}, { offset => 3 })->as_query,
-    @{$tests->{$limtype}{ordered_limit_offset}},
-    "$limtype: Ordered limit+offset with select/group/having",
-  ) if $tests->{$limtype}{ordered_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({}, {
@@ -834,20 +1028,22 @@ for my $limtype (sort keys %$tests) {
     offset => 1,
     columns => 'name',  # only the owner name, still prefetch all the books
     prefetch => 'books',
-    ($limtype =~ /GenericSubQ/ ? ( order_by => 'me.id' ) : () ),  # needs a simple-column stable order to be happy
+    ($limtype !~ /GenericSubQ/ ? () : (
+      # needs a same-table stable order to be happy
+      order_by => [ { -asc => 'me.name' }, \ 'me.id DESC' ]
+    )),
   });
 
-  is_same_sql_bind (
-    $pref_rs->as_query,
-    @{$tests->{$limtype}{limit_offset_prefetch}},
-    "$limtype: Prefetch with limit+offset",
-  ) if $tests->{$limtype}{limit_offset_prefetch};
+  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};
 
-  # we can actually run the query
-  if ($limtype eq $native_limit_dialect or $limtype eq 'GenericSubQ') {
-    lives_ok { is ($pref_rs->all, 1, 'Expected count of objects on limtied prefetch') }
-      "Complex limited prefetch works with supported limit $limtype"
-  }
+    is ($pref_rs->all, 1, 'Expected count of objects on limited prefetch')
+      if $can_run;
+  } "Complex limited prefetch runs under $limtype";
 }
 
 done_testing;