X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=t%2Fsqlmaker%2Flimit_dialects%2Ftorture.t;h=072e9c658a1fa70c1992d97cf88d398ab291d362;hb=65ad59ab00bf973e5fd7a375927fd831e6b6e6dc;hp=7bb116b10c13cab3e05258409cc3d02a978b8259;hpb=4b8da207a3460216711d73987feaa13f7107ecc3;p=dbsrgits%2FDBIx-Class.git

diff --git a/t/sqlmaker/limit_dialects/torture.t b/t/sqlmaker/limit_dialects/torture.t
index 7bb116b..072e9c6 100644
--- a/t/sqlmaker/limit_dialects/torture.t
+++ b/t/sqlmaker/limit_dialects/torture.t
@@ -2,11 +2,13 @@ use strict;
 use warnings;
 
 use Test::More;
+use Test::Exception;
 use lib qw(t/lib);
 use DBICTest;
 use DBIC::SqlMakerTest;
 
 my $schema = DBICTest->init_schema;
+my $native_limit_dialect = $schema->storage->sql_maker->{limit_dialect};
 
 my $attr = {};
 my @where_bind = (
@@ -53,6 +55,22 @@ my $tests = {
         [ { 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 => {
@@ -78,6 +96,22 @@ my $tests = {
         [ { 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 => {
@@ -102,6 +136,21 @@ my $tests = {
         @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 => {
@@ -126,6 +175,21 @@ my $tests = {
         @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 {
@@ -149,10 +213,10 @@ my $tests = {
     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__1, ORDER__BY__2 ) AS rno__row__index
+          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__1, ? AS ORDER__BY__2
+                     ? / ? AS ORDER__BY__001, ? AS ORDER__BY__002
                 FROM books me
                 JOIN owners owner
                   ON owner.id = me.owner
@@ -207,6 +271,27 @@ my $tests = {
           [ { 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 ],
+        ]
+      ],
     };
   },
 
@@ -303,6 +388,27 @@ my $tests = {
           [ { 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 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 ],
+        ]
+      ],
     };
   },
 
@@ -373,9 +479,9 @@ my $tests = {
       '(
         SELECT me.id, owner__id, owner__name, bar, baz
           FROM (
-            SELECT me.id, owner__id, owner__name, bar, baz, ORDER__BY__1, ORDER__BY__2
+            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__1, ? AS ORDER__BY__2
+                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
@@ -385,10 +491,10 @@ my $tests = {
                 ORDER BY ? / ?, ?
                 FETCH FIRST 7 ROWS ONLY
               ) me
-            ORDER BY ORDER__BY__1 DESC, ORDER__BY__2 DESC
+            ORDER BY ORDER__BY__001 DESC, ORDER__BY__002 DESC
             FETCH FIRST 4 ROWS ONLY
           ) me
-        ORDER BY ORDER__BY__1, ORDER__BY__2
+        ORDER BY ORDER__BY__001, ORDER__BY__002
       )',
       [
         @select_bind,
@@ -399,6 +505,25 @@ my $tests = {
         (map { [ @$_ ] } @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 => {
@@ -464,9 +589,9 @@ my $tests = {
       '(
         SELECT me.id, owner__id, owner__name, bar, baz
           FROM (
-            SELECT TOP 4 me.id, owner__id, owner__name, bar, baz, ORDER__BY__1, ORDER__BY__2
+            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__1, ? AS ORDER__BY__2
+                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
@@ -475,9 +600,9 @@ my $tests = {
                 HAVING ?
                 ORDER BY ? / ?, ?
               ) me
-            ORDER BY ORDER__BY__1 DESC, ORDER__BY__2 DESC
+            ORDER BY ORDER__BY__001 DESC, ORDER__BY__002 DESC
           ) me
-        ORDER BY ORDER__BY__1, ORDER__BY__2
+        ORDER BY ORDER__BY__001, ORDER__BY__002
       )',
       [
         @select_bind,
@@ -488,6 +613,23 @@ my $tests = {
         (map { [ @$_ ] } @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
+      )',
+      [],
+    ],
   },
 
   RowCountOrGenericSubQ => {
@@ -597,6 +739,30 @@ my $tests = {
         [ { 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.id < me.id
+            ) BETWEEN ? AND ?
+            ORDER BY me.id
+          ) me
+          LEFT JOIN books books
+            ON books.owner = me.id
+        ORDER BY me.id
+      )',
+      [
+        [ { sqlt_datatype => 'integer' } => 1 ],
+        [ { sqlt_datatype => 'integer' } => 3 ],
+      ],
+    ],
   }
 };
 
@@ -653,6 +819,27 @@ for my $limtype (sort keys %$tests) {
     @{$tests->{$limtype}{ordered_limit_offset}},
     "$limtype: Ordered limit+offset with select/group/having",
   ) if $tests->{$limtype}{ordered_limit_offset};
+
+  # 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/ ? ( order_by => 'me.id' ) : () ),  # needs a simple-column stable order to be happy
+  });
+
+  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"
+  }
 }
 
 done_testing;