Got a failing test

[dbsrgits/DBIx-Class.git] / t / 746mssql.t

diff --git a/t/746mssql.t b/t/746mssql.t
index ab9f09f..0d53068 100644 (file)
--- a/t/746mssql.t
+++ b/t/746mssql.t
@@ -205,10 +205,10 @@ lives_ok ( sub {
     [qw/1   wiggle/],
     [qw/2   woggle/],
     [qw/3   boggle/],
-    [qw/4   fREW/],
-    [qw/5   fRIOUX/],
-    [qw/6   fROOH/],
-    [qw/7   fRUE/],
+    [qw/4   fRIOUX/],
+    [qw/5   fRUE/],
+    [qw/6   fREW/],
+    [qw/7   fROOH/],
     [qw/8   fISMBoC/],
     [qw/9   station/],
     [qw/10   mirror/],
@@ -238,18 +238,18 @@ lives_ok ( sub {
   my $schema = DBICTest::Schema->connect($dsn, $user, $pass);
   $schema->populate ('BooksInLibrary', [
     [qw/source  owner title   /],
-    [qw/Library 1     secrets0/],
-    [qw/Library 1     secrets1/],
+    [qw/Library 7     secrets10/],
     [qw/Eatery  1     secrets2/],
-    [qw/Library 2     secrets3/],
     [qw/Library 3     secrets4/],
-    [qw/Eatery  3     secrets5/],
-    [qw/Library 4     secrets6/],
-    [qw/Library 5     secrets7/],
     [qw/Eatery  5     secrets8/],
-    [qw/Library 6     secrets9/],
-    [qw/Library 7     secrets10/],
+    [qw/Library 5     secrets7/],
+    [qw/Library 2     secrets3/],
     [qw/Eatery  7     secrets11/],
+    [qw/Library 4     secrets6/],
+    [qw/Library 1     secrets0/],
+    [qw/Eatery  3     secrets5/],
+    [qw/Library 6     secrets9/],
+    [qw/Library 1     secrets1/],
     [qw/Library 8     secrets12/],
   ]);
 }, 'populate without PKs supplied ok' );
@@ -345,14 +345,16 @@ $schema->storage->_sql_maker->{name_sep} = '.';
     },
     {
       prefetch => 'owner',
-      order_by => { -desc => 'owner.name' },
+      order_by => 'owner.name',
     }
   );
+  # this is the order in which they should come from the above query
+  my @owner_names = qw/boggle fISMBoC fREW fRIOUX fROOH fRUE wiggle wiggle/;
 
   is ($rs->all, 8, 'Correct amount of objects from right-sorted joined resultset');
   is_deeply (
     [map { $_->owner->name } ($rs->all) ],
-    [qw/wiggle wiggle fRUE fROOH fRIOUX fREW fISMBoC boggle /],
+    \@owner_names,
     'Rows were properly ordered'
   );
 
@@ -366,7 +368,7 @@ $schema->storage->_sql_maker->{name_sep} = '.';
 
   is_deeply (
     [map { $_->owner->name } ($limited_rs->all) ],
-    [qw/fRUE fROOH fRIOUX fREW fISMBoC boggle /],
+    [@owner_names[2 .. 7]],
     'Limited rows were properly ordered'
   );
   is ($queries, 1, 'Only one query with prefetch');
@@ -377,9 +379,69 @@ $schema->storage->_sql_maker->{name_sep} = '.';
 
   is_deeply (
     [map { $_->name } ($limited_rs->search_related ('owner')->all) ],
-    [qw/fRUE fROOH fRIOUX fREW fISMBoC boggle /],
+    [@owner_names[2 .. 7]],
     'Rows are still properly ordered after search_related'
   );
+
+
+# Just to aid bug-hunting, delete block before merging
+{
+
+=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;