X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=t%2F746mssql.t;h=a73bfea0934bd2fc8dbabfb85ded156f7fb7fd9b;hb=257a1d3b8fb3d5509b132a9497bfe34662e8d29e;hp=8a97776e7868df3d3c9087137c82a4ffb5c3704f;hpb=8ff609183688d3edffe7190a664d0e525b2c188d;p=dbsrgits%2FDBIx-Class.git

diff --git a/t/746mssql.t b/t/746mssql.t
index 8a97776..a73bfea 100644
--- a/t/746mssql.t
+++ b/t/746mssql.t
@@ -220,11 +220,12 @@ lives_ok ( sub {
   ]);
 }, 'populate with PKs supplied ok' );
 
+
 lives_ok (sub {
   # start a new connection, make sure rebless works
   # test an insert with a supplied identity, followed by one without
   my $schema = DBICTest::Schema->connect($dsn, $user, $pass);
-  for (1..2) {
+  for (2, 1) {
     my $id = $_ * 20 ;
     $schema->resultset ('Owners')->create ({ id => $id, name => "troglodoogle $id" });
     $schema->resultset ('Owners')->create ({ name => "troglodoogle " . ($id + 1) });
@@ -254,19 +255,81 @@ lives_ok ( sub {
   ]);
 }, 'populate without PKs supplied ok' );
 
-# make sure ordered subselects work
+# plain ordered subqueries throw
+throws_ok (sub {
+  $schema->resultset('Owners')->search ({}, { order_by => 'name' })->as_query
+}, qr/ordered subselect encountered/, 'Ordered Subselect detection throws ok');
+
+# make sure ordered subselects *somewhat* work
 {
+  my $owners = $schema->resultset ('Owners')->search ({}, { order_by => 'name', offset => 2, rows => 3, unsafe_subselect_ok => 1 });
+
+  my $al = $owners->current_source_alias;
+  my $sealed_owners = $owners->result_source->resultset->search (
+    {},
+    {
+      alias => $al,
+      from => [{
+        -alias => $al,
+        -source_handle => $owners->result_source->handle,
+        $al => $owners->as_query,
+      }],
+    },
+  );
+
+  is_deeply (
+    [ map { $_->name } ($sealed_owners->all) ],
+    [ map { $_->name } ($owners->all) ],
+    'Sort preserved from within a subquery',
+  );
+}
+
+TODO: {
+  local $TODO = "This porbably will never work, but it isn't critical either afaik";
+
   my $book_owner_ids = $schema->resultset ('BooksInLibrary')
-                               ->search ({}, { join => 'owner', distinct => 1, order_by => { -desc => 'owner'} })
+                               ->search ({}, { join => 'owner', distinct => 1, order_by => 'owner.name', unsafe_subselect_ok => 1 })
                                 ->get_column ('owner');
 
-  my $owners = $schema->resultset ('Owners')->search ({
+  my $book_owners = $schema->resultset ('Owners')->search ({
     id => { -in => $book_owner_ids->as_query }
   });
 
-  is ($owners->count, 8, 'Correct amount of book owners');
-  is ($owners->all, 8, 'Correct amount of book owner objects');
+  is_deeply (
+    [ map { $_->id } ($book_owners->all) ],
+    [ $book_owner_ids->all ],
+    'Sort is preserved across IN subqueries',
+  );
 }
+
+# This is known not to work - thus the negative test
+{
+  my $owners = $schema->resultset ('Owners')->search ({}, { order_by => 'name', offset => 2, rows => 3, unsafe_subselect_ok => 1 });
+  my $corelated_owners = $owners->result_source->resultset->search (
+    {
+      id => { -in => $owners->get_column('id')->as_query },
+    },
+    {
+      order_by => 'name' #reorder because of what is shown above
+    },
+  );
+
+  cmp_ok (
+    join ("\x00", map { $_->name } ($corelated_owners->all) ),
+      'ne',
+    join ("\x00", map { $_->name } ($owners->all) ),
+    'Sadly sort not preserved from within a corelated subquery',
+  );
+
+  cmp_ok (
+    join ("\x00", sort map { $_->name } ($corelated_owners->all) ),
+      'ne',
+    join ("\x00", sort map { $_->name } ($owners->all) ),
+    'Which in fact gives a completely wrong dataset',
+  );
+}
+
+
 # make sure right-join-side single-prefetch ordering limit works
 {
   my $rs = $schema->resultset ('BooksInLibrary')->search (
@@ -288,7 +351,7 @@ lives_ok ( sub {
     'Rows were properly ordered'
   );
 
-  my $limited_rs = $rs->search ({}, {rows => 7, offset => 2});
+  my $limited_rs = $rs->search ({}, {rows => 7, offset => 2, unsafe_subselect_ok => 1});
   is ($limited_rs->count, 6, 'Correct count of limited right-sorted joined resultset');
   is ($limited_rs->count_rs->next, 6, 'Correct count_rs of limited right-sorted joined resultset');
 
@@ -334,6 +397,7 @@ $schema->storage->_sql_maker->{name_sep} = '.';
       prefetch => 'books',
       order_by => { -asc => \['name + ?', [ test => 'xxx' ]] }, # test bindvar propagation
       rows     => 3,  # 8 results total
+      unsafe_subselect_ok => 1,
     },
   );
 
@@ -362,6 +426,7 @@ $schema->storage->_sql_maker->{name_sep} = '.';
       prefetch => 'owner',
       rows     => 2,  # 3 results total
       order_by => { -desc => 'owner' },
+      unsafe_subselect_ok => 1,
     },
   );
 
@@ -384,79 +449,6 @@ $schema->storage->_sql_maker->{name_sep} = '.';
   is ($books->page(2)->count_rs->next, 1, 'Prefetched grouped search returns correct count_rs');
 }
 
-
-
-# Just to aid bug-hunting, delete block before merging
-{
-
-  my $limited_rs = $schema->resultset ('BooksInLibrary')->search (
-    {
-      'owner.name' => { '!=', 'woggle' },
-    },
-    {
-      prefetch => 'owner',
-      order_by => 'owner.name',
-      rows => 7,
-      offset => 2,
-    }
-  );
-
-
-=begin
-
-Alan's SQL:
-
-      SELECT me.id, me.surveyor_id, me.survey_site_id, me.year, surveyor.id, surveyor.name, surveyor.email, surveyor.phone, surveyor.login, surveyor.password, surveyor.is_active, surveyor.is_verifier, surveyor.arm_length, surveyor.eye_height, surveyor.year_joined
-        FROM (
-          SELECT *
-            FROM (
-              SELECT orig_query.*, ROW_NUMBER() OVER( ORDER BY (SELECT(1)) ) AS rno__row__index
-                FROM (
-                  SELECT me.id, me.surveyor_id, me.survey_site_id, me.year
-                    FROM (
-                      SELECT TOP 100 PERCENT me.id, me.surveyor_id, me.survey_site_id, me.year
-                        FROM surveyors_survey_sites me
-                        JOIN surveyors surveyor ON surveyor.id = me.surveyor_id
-                      ORDER BY surveyor.name
-                    ) me
-                ) orig_query
-            ) rno_subq
-          WHERE rno__row__index BETWEEN 136 AND 150 
-        ) me
-        JOIN surveyors surveyor ON surveyor.id = me.surveyor_id
-      ORDER BY surveyor.name
-=cut
-
-  is_same_sql_bind (
-    $limited_rs->as_query,
-    '(
-      SELECT TOP 100 PERCENT [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price], [owner].[id], [owner].[name]
-        FROM (
-          SELECT *
-            FROM (
-              SELECT [me].*, ROW_NUMBER() OVER( ORDER BY (SELECT(1)) ) AS rno__row__index
-                FROM (
-                  SELECT [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price]
-                    FROM (
-                      SELECT TOP 100 PERCENT [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price]
-                        FROM [books] [me]
-                        JOIN [owners] [owner] ON [owner].[id] = [me].[owner]
-                      WHERE ( ( [owner].[name] != ? AND [source] = ? ) )
-                      ORDER BY [owner].[name]
-                    ) [me]
-                ) [me]
-            ) rno_subq
-          WHERE rno__row__index BETWEEN 3 AND 9
-        ) [me]
-        JOIN [owners] [owner] ON [owner].[id] = [me].[owner]
-      WHERE ( ( [owner].[name] != ? AND [source] = ? ) )
-      ORDER BY [owner].[name]
-    )',
-    [ ([ 'owner.name' => 'woggle' ], [ source => 'Library' ]) x 2 ],
-    'Expected SQL executed',
-  );
-}
-
 done_testing;
 
 # clean up our mess