Properly handle empty group_by/order_by

[dbsrgits/DBIx-Class.git] / t / sqlmaker / limit_dialects / torture.t

diff --git a/t/sqlmaker/limit_dialects/torture.t b/t/sqlmaker/limit_dialects/torture.t
index f4e7d1d..9d8d23d 100644 (file)
--- a/t/sqlmaker/limit_dialects/torture.t
+++ b/t/sqlmaker/limit_dialects/torture.t
@@ -5,15 +5,16 @@ 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 $where_string = 'me.title = ? AND source != ? AND source = ?';
+
 my @where_bind = (
-  [ {} => 'Study' ],
   [ {} => 'kama sutra' ],
+  [ {} => 'Study' ],
   [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
 );
 my @select_bind = (
@@ -36,17 +37,23 @@ my @order_bind = (
 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 source != ? AND me.title = ? AND source = ?
+        WHERE $where_string
         GROUP BY (me.id / ?), owner.id
         HAVING ?
         LIMIT ?
-      )',
+      )",
       [
         @select_bind,
         @where_bind,
@@ -56,17 +63,17 @@ my $tests = {
       ],
     ],
     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 = ?
+        WHERE $where_string
         GROUP BY (me.id / ?), owner.id
         HAVING ?
         LIMIT ?
         OFFSET ?
-      )',
+      )",
       [
         @select_bind,
         @where_bind,
@@ -77,17 +84,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 = ?
+        WHERE $where_string
         GROUP BY (me.id / ?), owner.id
         HAVING ?
         ORDER BY ? / ?, ?
         LIMIT ?
-      )',
+      )",
       [
         @select_bind,
         @where_bind,
@@ -98,18 +105,18 @@ my $tests = {
       ]
     ],
     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 = ?
+        WHERE $where_string
         GROUP BY (me.id / ?), owner.id
         HAVING ?
         ORDER BY ? / ?, ?
         LIMIT ?
         OFFSET ?
-      )',
+      )",
       [
         @select_bind,
         @where_bind,
@@ -121,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
@@ -130,7 +137,7 @@ my $tests = {
           ) me
           LEFT JOIN books books
             ON books.owner = me.id
-      )',
+      )",
       [
         [ { sqlt_datatype => 'integer' } => 3 ],
         [ { sqlt_datatype => 'integer' } => 1 ],
@@ -139,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 = ?
+        WHERE $where_string
         GROUP BY (me.id / ?), owner.id
         HAVING ?
         ORDER BY ? / ?, ?
         LIMIT ?, ?
-      )',
+      )",
       [
         @select_bind,
         @where_bind,
@@ -162,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
@@ -171,7 +184,7 @@ my $tests = {
           ) me
           LEFT JOIN books books
             ON books.owner = me.id
-      )',
+      )",
       [
         [ { sqlt_datatype => 'integer' } => 1 ],
         [ { sqlt_datatype => 'integer' } => 3 ],
@@ -180,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 = ?
+        WHERE $where_string
         GROUP BY (me.id / ?), owner.id
         HAVING ?
         ORDER BY ? / ?, ?
-      )',
+      )",
       [
         [ { sqlt_datatype => 'integer' } => 3 ],
         [ { sqlt_datatype => 'integer' } => 4 ],
@@ -202,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
@@ -210,7 +229,7 @@ my $tests = {
           ) me
           LEFT JOIN books books
             ON books.owner = me.id
-      )',
+      )",
       [
         [ { sqlt_datatype => 'integer' } => 1 ],
         [ { sqlt_datatype => 'integer' } => 3 ],
@@ -219,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 = ?
+        WHERE $where_string
         GROUP BY (me.id / ?), owner.id
         HAVING ?
         ORDER BY ? / ?, ?
-      )',
+      )",
       [
         [ { sqlt_datatype => 'integer' } => 4 ],
         [ { sqlt_datatype => 'integer' } => 3 ],
@@ -241,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
@@ -249,7 +274,7 @@ my $tests = {
           ) me
           LEFT JOIN books books
             ON books.owner = me.id
-      )',
+      )",
       [
         [ { sqlt_datatype => 'integer' } => 3 ],
         [ { sqlt_datatype => 'integer' } => 1 ],
@@ -258,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
@@ -267,15 +292,15 @@ my $tests = {
                 FROM books me
                 JOIN owners owner
                   ON owner.id = me.owner
-              WHERE source != ? AND me.title = ? AND source = ?
+              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
@@ -285,15 +310,32 @@ my $tests = {
                 FROM books me
                 JOIN owners owner
                   ON owner.id = me.owner
-              WHERE source != ? AND me.title = ? AND source = ?
+              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,
@@ -337,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
@@ -351,7 +393,7 @@ my $tests = {
             ) me
             LEFT JOIN books books
               ON books.owner = me.id
-        )',
+        )",
         [
           [ { sqlt_datatype => 'integer' } => 2 ],
           [ { sqlt_datatype => 'integer' } => 4 ],
@@ -362,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 = ?
+            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,
@@ -389,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 = ?
+                  WHERE $where_string
                   GROUP BY (me.id / ?), owner.id
                   HAVING ?
                 ) me
             ) me
           WHERE rownum__index BETWEEN ? AND ?
-        )',
+        )",
         [
           @select_bind,
           @where_bind,
@@ -425,16 +480,16 @@ 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 = ?
+                  WHERE $where_string
                   GROUP BY (me.id / ?), owner.id
                   HAVING ?
                   ORDER BY ? / ?, ?
@@ -442,7 +497,7 @@ my $tests = {
               WHERE ROWNUM <= ?
             ) me
           WHERE rownum__index >= ?
-        )',
+        )",
         [
           @select_bind,
           @where_bind,
@@ -454,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
@@ -468,7 +523,7 @@ my $tests = {
             ) me
             LEFT JOIN books books
               ON books.owner = me.id
-        )',
+        )",
         [
           [ { sqlt_datatype => 'integer' } => 2 ],
           [ { sqlt_datatype => 'integer' } => 4 ],
@@ -478,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 = ?
+        WHERE $where_string
         GROUP BY (me.id / ?), owner.id
         HAVING ?
         FETCH FIRST 4 ROWS ONLY
-      )',
+      )",
       [
         @select_bind,
         @where_bind,
@@ -497,14 +556,14 @@ 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 = ?
+            WHERE $where_string
             GROUP BY (me.id / ?), owner.id
             HAVING ?
             ORDER BY me.id
@@ -512,7 +571,7 @@ my $tests = {
           ) me
         ORDER BY me.id DESC
         FETCH FIRST 4 ROWS ONLY
-      )',
+      )",
       [
         @select_bind,
         @where_bind,
@@ -521,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 = ?
+        WHERE $where_string
         GROUP BY (me.id / ?), owner.id
         HAVING ?
         ORDER BY ? / ?, ?
         FETCH FIRST 4 ROWS ONLY
-      )',
+      )",
       [
         @select_bind,
         @where_bind,
@@ -541,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
@@ -550,7 +609,7 @@ my $tests = {
                   FROM books me
                   JOIN owners owner
                     ON owner.id = me.owner
-                WHERE source != ? AND me.title = ? AND source = ?
+                WHERE $where_string
                 GROUP BY (me.id / ?), owner.id
                 HAVING ?
                 ORDER BY ? / ?, ?
@@ -560,7 +619,7 @@ my $tests = {
             FETCH FIRST 4 ROWS ONLY
           ) me
         ORDER BY ORDER__BY__001, ORDER__BY__002
-      )',
+      )",
       [
         @select_bind,
         @order_bind,
@@ -571,7 +630,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
@@ -586,22 +645,26 @@ my $tests = {
           ) me
           LEFT JOIN books books
             ON books.owner = me.id
-      )',
+      )",
       [],
     ],
   },
 
   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 = ?
+        WHERE $where_string
         GROUP BY (me.id / ?), owner.id
         HAVING ?
-      )',
+      )",
       [
         @select_bind,
         @where_bind,
@@ -610,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 = ?
+            WHERE $where_string
             GROUP BY (me.id / ?), owner.id
             HAVING ?
             ORDER BY me.id
           ) me
         ORDER BY me.id DESC
-      )',
+      )",
       [
         @select_bind,
         @where_bind,
@@ -632,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 = ?
+        WHERE $where_string
         GROUP BY (me.id / ?), owner.id
         HAVING ?
         ORDER BY ? / ?, ?
-      )',
+      )",
       [
         @select_bind,
         @where_bind,
@@ -651,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
@@ -660,7 +723,7 @@ my $tests = {
                   FROM books me
                   JOIN owners owner
                     ON owner.id = me.owner
-                WHERE source != ? AND me.title = ? AND source = ?
+                WHERE $where_string
                 GROUP BY (me.id / ?), owner.id
                 HAVING ?
                 ORDER BY ? / ?, ?
@@ -668,7 +731,7 @@ my $tests = {
             ORDER BY ORDER__BY__001 DESC, ORDER__BY__002 DESC
           ) me
         ORDER BY ORDER__BY__001, ORDER__BY__002
-      )',
+      )",
       [
         @select_bind,
         @order_bind,
@@ -679,7 +742,7 @@ my $tests = {
       ],
     ],
     limit_offset_prefetch => [
-      '(
+      "(
         SELECT me.name, books.id, books.source, books.owner, books.title, books.price
           FROM (
             SELECT TOP 3 me.name, me.id
@@ -692,21 +755,40 @@ my $tests = {
           ) me
           LEFT JOIN books books
             ON books.owner = me.id
-      )',
+      )",
       [],
     ],
   },
 
   GenericSubQ => {
+    limit_plain => [
+      "(
+        SELECT me.artistid
+          FROM (
+            SELECT me.artistid
+              FROM artist me
+          ) me
+        WHERE
+          (
+            SELECT COUNT(*)
+              FROM artist rownum__emulation
+            WHERE rownum__emulation.artistid < me.artistid
+          ) < ?
+        ORDER BY me.artistid ASC
+      )",
+      [
+        [ { sqlt_datatype => 'integer' } => 5 ]
+      ],
+    ],
     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 source != ? AND me.title = ? AND source = ?
+            WHERE $where_string
             GROUP BY (me.id / ?), owner.id
             HAVING ?
           ) me
@@ -735,7 +817,7 @@ my $tests = {
             )
           ) < ?
         ORDER BY me.price DESC, me.id ASC
-      )',
+      )",
       [
         @select_bind,
         @where_bind,
@@ -745,14 +827,14 @@ my $tests = {
       ],
     ],
     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 source != ? AND me.title = ? AND source = ?
+            WHERE $where_string
             GROUP BY (me.id / ?), owner.id
             HAVING ?
           ) me
@@ -781,7 +863,7 @@ my $tests = {
             )
           ) BETWEEN ? AND ?
         ORDER BY me.price DESC, me.id ASC
-      )',
+      )",
       [
         @select_bind,
         @where_bind,
@@ -792,7 +874,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
@@ -819,7 +901,7 @@ my $tests = {
           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 ],
@@ -835,7 +917,25 @@ for my $limtype (sort keys %$tests) {
   delete $schema->storage->_sql_maker->{_cached_syntax};
   $schema->storage->_sql_maker->limit_dialect ($limtype);
 
-  my $can_run = ($limtype eq $native_limit_dialect or $limtype eq 'GenericSubQ');
+  # 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(
@@ -855,6 +955,7 @@ for my $limtype (sort keys %$tests) {
   #
   # 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
   if ($tests->{$limtype}{limit}) {