Do not artificially order the internals of a has_many prefetch subquery

[dbsrgits/DBIx-Class.git] / t / prefetch / with_limit.t

diff --git a/t/prefetch/with_limit.t b/t/prefetch/with_limit.t
index 9c1f067..1d2aa84 100644 (file)
--- a/t/prefetch/with_limit.t
+++ b/t/prefetch/with_limit.t
@@ -8,6 +8,9 @@ use Test::Exception;
 use lib qw(t/lib);
 use DBICTest;
 use DBIC::SqlMakerTest;
+use DBIx::Class::SQLMaker::LimitDialects;
+
+my $ROWS = DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype;
 
 my $schema = DBICTest->init_schema();
 
@@ -31,6 +34,67 @@ my $use_prefetch = $no_prefetch->search(
   }
 );
 
+# add an extra +select to make sure it does not throw things off
+# we also expect it to appear in both selectors, as we can not know
+# for sure which part of the query it applies to (may be order_by,
+# maybe something else)
+#
+# we use a reference to the same array in bind vals, because
+# is_deeply picks up this difference too (not sure if bug or
+# feature)
+$use_prefetch = $use_prefetch->search({}, {
+  '+columns' => { monkeywrench => \[ 'me.artistid + ?', [ \ 'inTEger' => 1 ] ] },
+});
+
+my $bind_int_resolved = sub { [ { sqlt_datatype => 'inTEger' } => 1 ] };
+my $bind_vc_resolved = sub { [
+  { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'tracks.title' }
+    => 'blah-blah-1234568'
+] };
+is_same_sql_bind (
+  $use_prefetch->as_query,
+  '(
+    SELECT  me.artistid + ?,
+            me.artistid, me.name,
+            cds.cdid, cds.artist, cds.title, cds.year, cds.genreid, cds.single_track
+      FROM (
+        SELECT me.artistid + ?,
+               me.artistid, me.name
+          FROM artist me
+          LEFT JOIN cd cds
+            ON cds.artist = me.artistid
+          LEFT JOIN cd_artwork artwork
+            ON artwork.cd_id = cds.cdid
+          LEFT JOIN track tracks
+            ON tracks.cd = cds.cdid
+        WHERE   artwork.cd_id IS NULL
+             OR tracks.title != ?
+        GROUP BY me.artistid + ?, me.artistid, me.name
+        ORDER BY name DESC LIMIT ?
+      ) me
+      LEFT JOIN cd cds
+        ON cds.artist = me.artistid
+      LEFT JOIN cd_artwork artwork
+        ON artwork.cd_id = cds.cdid
+      LEFT JOIN track tracks
+        ON tracks.cd = cds.cdid
+    WHERE artwork.cd_id IS NULL
+       OR tracks.title != ?
+    GROUP BY me.artistid + ?, me.artistid, me.name, cds.cdid, cds.artist, cds.title, cds.year, cds.genreid, cds.single_track
+    ORDER BY name DESC
+  )',
+  [
+    $bind_int_resolved->(),  # outer select
+    $bind_int_resolved->(),  # inner select
+    $bind_vc_resolved->(), # inner where
+    $bind_int_resolved->(),  # inner group_by
+    [ $ROWS => 3 ],
+    $bind_vc_resolved->(), # outer where
+    $bind_int_resolved->(),  # outer group_by
+  ],
+  'Expected SQL on complex limited prefetch'
+);
+
 is($no_prefetch->count, $use_prefetch->count, '$no_prefetch->count == $use_prefetch->count');
 is(
   scalar ($no_prefetch->all),
@@ -84,6 +148,15 @@ throws_ok (
   'single() with multiprefetch is illegal',
 );
 
+throws_ok (
+  sub {
+    $use_prefetch->search(
+      {'tracks.title' => { '!=' => 'foo' }},
+      { order_by => \ 'some oddball literal sql', join => { cds => 'tracks' } }
+    )->next
+  }, qr/A required group_by clause could not be constructed automatically/,
+);
+
 my $artist = $use_prefetch->search({'cds.title' => $artist_many_cds->cds->first->title })->next;
 is($artist->cds->count, 1, "count on search limiting prefetched has_many");
 
@@ -102,7 +175,7 @@ is_same_sql_bind (
   '(
     SELECT
         me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track,
-        tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at, tracks.small_dt,
+        tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at,
         artist.artistid, artist.name, artist.rank, artist.charfield
       FROM (
         SELECT
@@ -110,20 +183,21 @@ is_same_sql_bind (
           FROM cd me
           JOIN artist artist ON artist.artistid = me.artist
         WHERE ( ( artist.name = ? AND me.year = ? ) )
-        LIMIT 15
+        LIMIT ?
       ) me
       LEFT JOIN track tracks
         ON tracks.cd = me.cdid
       JOIN artist artist
         ON artist.artistid = me.artist
     WHERE ( ( artist.name = ? AND me.year = ? ) )
-    ORDER BY tracks.cd
+    ORDER BY me.cdid
   )',
   [
-    [ 'artist.name' => 'foo' ],
-    [ 'me.year'     => 2010  ],
-    [ 'artist.name' => 'foo' ],
-    [ 'me.year'     => 2010  ],
+    [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'artist.name' } => 'foo' ],
+    [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.year' } => 2010 ],
+    [ $ROWS         => 15    ],
+    [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'artist.name' } => 'foo' ],
+    [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.year' } => 2010 ],
   ],
   'No grouping of non-multiplying resultsets',
 );