Fail early on literal-ordered complex prefetch without explicit group_by clause

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

diff --git a/t/prefetch/with_limit.t b/t/prefetch/with_limit.t
index 1dd0829..4acdcab 100644 (file)
--- a/t/prefetch/with_limit.t
+++ b/t/prefetch/with_limit.t
@@ -7,8 +7,7 @@ use Test::More;
 use Test::Exception;
 use lib qw(t/lib);
 use DBICTest;
-
-plan tests => 9;
+use DBIC::SqlMakerTest;
 
 my $schema = DBICTest->init_schema();
 
@@ -25,6 +24,8 @@ my $no_prefetch = $schema->resultset('Artist')->search(
 my $use_prefetch = $no_prefetch->search(
   {},
   {
+    select => ['me.artistid', 'me.name'],
+    as => ['artistid', 'name'],
     prefetch => 'cds',
     order_by => { -desc => 'name' },
   }
@@ -83,6 +84,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");
 
@@ -90,3 +100,41 @@ is($artist->cds->count, 1, "count on search limiting prefetched has_many");
 my $artist2 = $use_prefetch->search({'cds.title' => { '!=' => $artist_many_cds->cds->first->title } })->slice (0,0)->next;
 is($artist2->cds->count, 2, "count on search limiting prefetched has_many");
 
+# make sure 1:1 joins do not force a subquery (no point to exercise the optimizer, if at all available)
+# get cd's that have any tracks and their artists
+my $single_prefetch_rs = $schema->resultset ('CD')->search (
+  { 'me.year' => 2010, 'artist.name' => 'foo' },
+  { prefetch => ['tracks', 'artist'], rows => 15 },
+);
+is_same_sql_bind (
+  $single_prefetch_rs->as_query,
+  '(
+    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,
+        artist.artistid, artist.name, artist.rank, artist.charfield
+      FROM (
+        SELECT
+            me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
+          FROM cd me
+          JOIN artist artist ON artist.artistid = me.artist
+        WHERE ( ( artist.name = ? AND me.year = ? ) )
+        LIMIT 15
+      ) 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
+  )',
+  [
+    [ 'artist.name' => 'foo' ],
+    [ 'me.year'     => 2010  ],
+    [ 'artist.name' => 'foo' ],
+    [ 'me.year'     => 2010  ],
+  ],
+  'No grouping of non-multiplying resultsets',
+);
+
+done_testing;