X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=t%2Fsqlmaker%2Flimit_dialects%2Frownum.t;h=522b4d476ab993c60a352fcb3aeeced4c30b3623;hb=7cec43565df63cdbf6320721c7d7c33cb6ce6e96;hp=f263166e7b2b84b4d96805b2de45a4ed7008d275;hpb=69d3c2708f5564ce38d5878fa694b04f6740cde0;p=dbsrgits%2FDBIx-Class.git

diff --git a/t/sqlmaker/limit_dialects/rownum.t b/t/sqlmaker/limit_dialects/rownum.t
index f263166..522b4d4 100644
--- a/t/sqlmaker/limit_dialects/rownum.t
+++ b/t/sqlmaker/limit_dialects/rownum.t
@@ -16,55 +16,164 @@ my ($TOTAL, $OFFSET) = (
 my $s = DBICTest->init_schema (no_deploy => 1, );
 $s->storage->sql_maker->limit_dialect ('RowNum');
 
-my $rs = $s->resultset ('CD');
-
-is_same_sql_bind (
-  $rs->search ({}, { rows => 1, offset => 3,columns => [
-      { id => 'foo.id' },
-      { 'bar.id' => 'bar.id' },
-      { bleh => \ 'TO_CHAR (foo.womble, "blah")' },
-    ]})->as_query,
-  '(
-    SELECT id, bar__id, bleh
+my $rs = $s->resultset ('CD')->search({ id => 1 });
+
+my $where_bind = [ { dbic_colname => 'id' }, 1 ];
+
+for my $test_set (
+  {
+    name => 'Rownum subsel aliasing works correctly',
+    rs => $rs->search_rs(undef, {
+      rows => 1,
+      offset => 3,
+      columns => [
+        { id => 'foo.id' },
+        { 'bar.id' => 'bar.id' },
+        { bleh => \'TO_CHAR (foo.womble, "blah")' },
+      ]
+    }),
+    sql => '(
+      SELECT id, bar__id, bleh
       FROM (
         SELECT id, bar__id, bleh, ROWNUM rownum__index
-          FROM (
-            SELECT foo.id AS id, bar.id AS bar__id, TO_CHAR(foo.womble, "blah") AS bleh
-              FROM cd me
-          ) me
+        FROM (
+          SELECT foo.id AS id, bar.id AS bar__id, TO_CHAR (foo.womble, "blah") AS bleh
+            FROM cd me
+          WHERE id = ?
+        ) me
+      ) me WHERE rownum__index BETWEEN ? AND ?
+    )',
+    binds => [
+      $where_bind,
+      [ $OFFSET => 4 ],
+      [ $TOTAL => 4 ],
+    ],
+  }, {
+    name => 'Rownum subsel aliasing works correctly with unique order_by',
+    rs => $rs->search_rs(undef, {
+      rows => 1,
+      offset => 3,
+      columns => [
+        { id => 'foo.id' },
+        { 'bar.id' => 'bar.id' },
+        { bleh => \'TO_CHAR (foo.womble, "blah")' },
+      ],
+      order_by => [qw( artist title )],
+    }),
+    sql => '(
+      SELECT id, bar__id, bleh
+      FROM (
+        SELECT id, bar__id, bleh, ROWNUM rownum__index
+        FROM (
+          SELECT foo.id AS id, bar.id AS bar__id, TO_CHAR(foo.womble, "blah") AS bleh
+            FROM cd me
+          WHERE id = ?
+          ORDER BY artist, title
+        ) me
         WHERE ROWNUM <= ?
       ) me
-    WHERE rownum__index >= ?
-  )',
-  [
-    [ $TOTAL => 4 ],
-    [ $OFFSET => 4 ],
-  ],
-  'Rownum subsel aliasing works correctly'
-);
-
-is_same_sql_bind (
-  $rs->search ({}, { rows => 2, offset => 3,columns => [
-      { id => 'foo.id' },
-      { 'ends_with_me.id' => 'ends_with_me.id' },
-    ]})->as_query,
-  '(SELECT id, ends_with_me__id
+      WHERE rownum__index >= ?
+    )',
+    binds => [
+      $where_bind,
+      [ $TOTAL => 4 ],
+      [ $OFFSET => 4 ],
+    ],
+  },
+ {
+    name => 'Rownum subsel aliasing works correctly with non-unique order_by',
+    rs => $rs->search_rs(undef, {
+      rows => 1,
+      offset => 3,
+      columns => [
+        { id => 'foo.id' },
+        { 'bar.id' => 'bar.id' },
+        { bleh => \'TO_CHAR (foo.womble, "blah")' },
+      ],
+      order_by => 'artist',
+    }),
+    sql => '(
+      SELECT id, bar__id, bleh
+      FROM (
+        SELECT id, bar__id, bleh, ROWNUM rownum__index
+        FROM (
+          SELECT foo.id AS id, bar.id AS bar__id, TO_CHAR(foo.womble, "blah") AS bleh
+            FROM cd me
+          WHERE id = ?
+          ORDER BY artist
+        ) me
+      ) me
+      WHERE rownum__index BETWEEN ? and ?
+    )',
+    binds => [
+      $where_bind,
+      [ $OFFSET => 4 ],
+      [ $TOTAL => 4 ],
+    ],
+  }, {
+    name => 'Rownum subsel aliasing #2 works correctly',
+    rs => $rs->search_rs(undef, {
+      rows => 2,
+      offset => 3,
+      columns => [
+        { id => 'foo.id' },
+        { 'ends_with_me.id' => 'ends_with_me.id' },
+      ]
+    }),
+    sql => '(
+      SELECT id, ends_with_me__id
       FROM (
         SELECT id, ends_with_me__id, ROWNUM rownum__index
-          FROM (
-            SELECT foo.id AS id, ends_with_me.id AS ends_with_me__id
-              FROM cd me
-          ) me
+        FROM (
+          SELECT foo.id AS id, ends_with_me.id AS ends_with_me__id
+            FROM cd me
+          WHERE id = ?
+        ) me
+      ) me WHERE rownum__index BETWEEN ? AND ?
+    )',
+    binds => [
+      $where_bind,
+      [ $OFFSET => 4 ],
+      [ $TOTAL => 5 ],
+    ],
+  }, {
+    name => 'Rownum subsel aliasing #2 works correctly with unique order_by',
+    rs => $rs->search_rs(undef, {
+      rows => 2,
+      offset => 3,
+      columns => [
+        { id => 'foo.id' },
+        { 'ends_with_me.id' => 'ends_with_me.id' },
+      ],
+      order_by => [qw( artist title )],
+    }),
+    sql => '(
+      SELECT id, ends_with_me__id
+      FROM (
+        SELECT id, ends_with_me__id, ROWNUM rownum__index
+        FROM (
+          SELECT foo.id AS id, ends_with_me.id AS ends_with_me__id
+            FROM cd me
+          WHERE id = ?
+          ORDER BY artist, title
+        ) me
         WHERE ROWNUM <= ?
       ) me
-    WHERE rownum__index >= ?
-  )',
-  [
-    [ $TOTAL => 5 ],
-    [ $OFFSET => 4 ],
-  ],
-  'Rownum subsel aliasing works correctly'
-);
+      WHERE rownum__index >= ?
+    )',
+    binds => [
+      $where_bind,
+      [ $TOTAL => 5 ],
+      [ $OFFSET => 4 ],
+    ],
+  }
+) {
+  is_same_sql_bind(
+    $test_set->{rs}->as_query,
+    $test_set->{sql},
+    $test_set->{binds},
+    $test_set->{name});
+}
 
 {
 my $subq = $s->resultset('Owners')->search({
@@ -94,15 +203,14 @@ is_same_sql_bind(
               JOIN owners owner ON owner.id = me.owner
             WHERE ( source = ? )
           ) me
-        WHERE ROWNUM <= ?
       ) me
-    WHERE rownum__index >= ?
+    WHERE rownum__index BETWEEN ? AND ?
   )',
   [
     [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
       => 'Library' ],
-    [ $TOTAL => 5 ],
     [ $OFFSET => 4 ],
+    [ $TOTAL => 5 ],
   ],
 
   'pagination with subquery works'
@@ -125,5 +233,31 @@ is_same_sql_bind(
   );
 }
 
+{
+my $subq = $s->resultset('Owners')->search({
+   'books.owner' => { -ident => 'owner.id' },
+}, { alias => 'owner', select => ['id'] } )->count_rs;
+
+my $rs_selectas_rel = $s->resultset('BooksInLibrary')->search( { -exists => $subq->as_query }, { select => ['id','owner'], rows => 1 } );
+
+is_same_sql_bind(
+  $rs_selectas_rel->as_query,
+  '(
+    SELECT id, owner FROM (
+      SELECT id, owner, ROWNUM rownum__index FROM (
+        SELECT me.id, me.owner  FROM books me WHERE ( ( (EXISTS (SELECT COUNT( * ) FROM owners owner WHERE ( books.owner = owner.id ))) AND source = ? ) )
+      ) me
+    ) me WHERE rownum__index BETWEEN ? AND ?
+  )',
+  [
+    [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
+    [ $OFFSET => 1 ],
+    [ $TOTAL => 1 ],
+  ],
+  'Pagination with sub-query in WHERE works'
+);
+
+}
+
 
 done_testing;