X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=t%2Fprefetch%2Fcorrelated.t;h=5196620dbbdbe917905cf32dbb471e9220328404;hb=ee573365;hp=694cf0b776f5dd7e1cc72294dc672aacbcfd97f9;hpb=65ad59ab00bf973e5fd7a375927fd831e6b6e6dc;p=dbsrgits%2FDBIx-Class.git

diff --git a/t/prefetch/correlated.t b/t/prefetch/correlated.t
index 694cf0b..5196620 100644
--- a/t/prefetch/correlated.t
+++ b/t/prefetch/correlated.t
@@ -2,19 +2,18 @@ use strict;
 use warnings;
 
 use Test::More;
+use Test::Deep;
 use lib qw(t/lib);
-use DBICTest;
-use DBIC::SqlMakerTest;
+use DBICTest ':DiffSQL';
 
 my $schema = DBICTest->init_schema();
-my $orig_debug = $schema->storage->debug;
 
 my $cdrs = $schema->resultset('CD')->search({ 'me.artist' => { '!=', 2 }});
 
 my $cd_data = { map {
   $_->cdid => {
     siblings => $cdrs->search ({ artist => $_->get_column('artist') })->count - 1,
-    track_titles => [ map { $_->title } ($_->tracks->all) ],
+    track_titles => [ sort $_->tracks->get_column('title')->all ],
   },
 } ( $cdrs->all ) };
 
@@ -35,10 +34,10 @@ is_same_sql_bind(
     SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track,
            (SELECT COUNT( * )
               FROM cd siblings
-            WHERE siblings.artist = me.artist
+            WHERE me.artist != ?
+              AND siblings.artist = me.artist
               AND siblings.cdid != me.cdid
               AND siblings.cdid != ?
-              AND me.artist != ?
            ),
            tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at
       FROM cd me
@@ -49,12 +48,12 @@ is_same_sql_bind(
   [
 
     # subselect
-    [ { sqlt_datatype => 'integer', dbic_colname => 'siblings.cdid' }
-      => 23414 ],
-
     [ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' }
       => 2 ],
 
+    [ { sqlt_datatype => 'integer', dbic_colname => 'siblings.cdid' }
+      => 23414 ],
+
     # outher WHERE
     [ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' }
       => 2 ],
@@ -62,26 +61,19 @@ is_same_sql_bind(
   'Expected SQL on correlated realiased subquery'
 );
 
-my $queries = 0;
-$schema->storage->debugcb(sub { $queries++; });
-$schema->storage->debug(1);
-
-is_deeply (
-  { map
-    { $_->cdid => {
-      track_titles => [ map { $_->title } ($_->tracks->all) ],
-      siblings => $_->get_column ('sibling_count'),
-    } }
-    $c_rs->all
-  },
-  $cd_data,
-  'Proper information retrieved from correlated subquery'
-);
-
-is ($queries, 1, 'Only 1 query fired to retrieve everything');
-
-$schema->storage->debug($orig_debug);
-$schema->storage->debugcb(undef);
+$schema->is_executed_querycount( sub {
+  cmp_deeply (
+    { map
+      { $_->cdid => {
+        track_titles => [ sort map { $_->title } ($_->tracks->all) ],
+        siblings => $_->get_column ('sibling_count'),
+      } }
+      $c_rs->all
+    },
+    $cd_data,
+    'Proper information retrieved from correlated subquery'
+  );
+}, 1, 'Only 1 query fired to retrieve everything');
 
 # now add an unbalanced select/as pair
 $c_rs = $c_rs->search ({}, {
@@ -101,15 +93,15 @@ is_same_sql_bind(
     SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track,
            (SELECT COUNT( * )
               FROM cd siblings
-            WHERE siblings.artist = me.artist
+            WHERE me.artist != ?
+              AND siblings.artist = me.artist
               AND siblings.cdid != me.cdid
               AND siblings.cdid != ?
-              AND me.artist != ?
            ),
            (SELECT MIN( year ), MAX( year )
               FROM cd siblings
-            WHERE siblings.artist = me.artist
-              AND me.artist != ?
+            WHERE me.artist != ?
+              AND siblings.artist = me.artist
            ),
            tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at
       FROM cd me
@@ -120,12 +112,12 @@ is_same_sql_bind(
   [
 
     # first subselect
-    [ { sqlt_datatype => 'integer', dbic_colname => 'siblings.cdid' }
-      => 23414 ],
-
     [ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' }
       => 2 ],
 
+    [ { sqlt_datatype => 'integer', dbic_colname => 'siblings.cdid' }
+      => 23414 ],
+
     # second subselect
     [ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' }
       => 2 ],
@@ -137,4 +129,105 @@ is_same_sql_bind(
   'Expected SQL on correlated realiased subquery'
 );
 
+$schema->storage->disconnect;
+
+# test for subselect identifier leakage
+# NOTE - the hodge-podge mix of literal and regular identifuers is *deliberate*
+for my $quote_names (0,1) {
+  my $schema = DBICTest->init_schema( quote_names => $quote_names );
+
+  my ($ql, $qr) = $schema->storage->sql_maker->_quote_chars;
+
+  my $art_rs = $schema->resultset('Artist')->search ({}, {
+    order_by => 'me.artistid',
+    prefetch => 'cds',
+    rows => 2,
+  });
+
+  my $inner_lim_bindtype = { sqlt_datatype => 'integer' };
+
+  for my $inner_relchain (qw( cds_unordered cds ) ) {
+
+    my $stupid_latest_competition_release_query = $schema->resultset('Artist')->search(
+      { 'competition.artistid' => { '!=', { -ident => 'me.artistid' } } },
+      { alias => 'competition' },
+    )->search_related( $inner_relchain, {}, {
+      rows => 1, order_by => 'year', columns => { year => \'year' }, distinct => 1
+    })->get_column(\'year')->max_rs;
+
+    my $final_query = $art_rs->search( {}, {
+      '+columns' => { max_competition_release => \[
+        @${ $stupid_latest_competition_release_query->as_query }
+      ]},
+    });
+
+    # we are using cds_unordered explicitly above - do the sorting manually
+    my @results = sort { $a->{artistid} <=> $b->{artistid} } @{$final_query->all_hri};
+    @$_ = sort { $a->{cdid} <=> $b->{cdid} } @$_ for map { $_->{cds} } @results;
+
+    is_deeply (
+      \@results,
+      [
+        { artistid => 1, charfield => undef, max_competition_release => 1998, name => "Caterwauler McCrae", rank => 13, cds => [
+          { artist => 1, cdid => 1, genreid => 1, single_track => undef, title => "Spoonful of bees", year => 1999 },
+          { artist => 1, cdid => 2, genreid => undef, single_track => undef, title => "Forkful of bees", year => 2001 },
+          { artist => 1, cdid => 3, genreid => undef, single_track => undef, title => "Caterwaulin' Blues", year => 1997 },
+        ] },
+        { artistid => 2, charfield => undef, max_competition_release => 1997, name => "Random Boy Band", rank => 13, cds => [
+          { artist => 2, cdid => 4, genreid => undef, single_track => undef, title => "Generic Manufactured Singles", year => 2001 },
+        ] },
+      ],
+      "Expected result from weird query",
+    );
+
+    # the decomposition to sql/bind is *deliberate* in both instances
+    # we want to ensure this keeps working for lietral sql, even when
+    # as_query switches to return an overloaded dq node
+    my ($sql, @bind) = @${ $final_query->as_query };
+
+    my $correlated_sql = qq{ (
+      SELECT MAX( year )
+        FROM (
+          SELECT year
+            FROM ${ql}artist${qr} ${ql}competition${qr}
+            JOIN cd ${ql}${inner_relchain}${qr}
+              ON ${ql}${inner_relchain}${qr}.${ql}artist${qr} = ${ql}competition${qr}.${ql}artistid${qr}
+          WHERE ${ql}competition${qr}.${ql}artistid${qr} != ${ql}me${qr}.${ql}artistid${qr}
+          GROUP BY year
+          ORDER BY MIN( ${ql}year${qr} )
+          LIMIT ?
+        ) ${ql}${inner_relchain}${qr}
+    )};
+
+    is_same_sql_bind(
+      $sql,
+      \@bind,
+      qq{ (
+        SELECT  ${ql}me${qr}.${ql}artistid${qr}, ${ql}me${qr}.${ql}name${qr}, ${ql}me${qr}.${ql}rank${qr}, ${ql}me${qr}.${ql}charfield${qr},
+                $correlated_sql,
+                ${ql}cds${qr}.${ql}cdid${qr}, ${ql}cds${qr}.${ql}artist${qr}, ${ql}cds${qr}.${ql}title${qr}, ${ql}cds${qr}.${ql}year${qr}, ${ql}cds${qr}.${ql}genreid${qr}, ${ql}cds${qr}.${ql}single_track${qr}
+          FROM (
+            SELECT  ${ql}me${qr}.${ql}artistid${qr}, ${ql}me${qr}.${ql}name${qr}, ${ql}me${qr}.${ql}rank${qr}, ${ql}me${qr}.${ql}charfield${qr},
+                    $correlated_sql
+              FROM ${ql}artist${qr} ${ql}me${qr}
+              ORDER BY ${ql}me${qr}.${ql}artistid${qr}
+              LIMIT ?
+          ) ${ql}me${qr}
+          LEFT JOIN cd ${ql}cds${qr}
+            ON ${ql}cds${qr}.${ql}artist${qr} = ${ql}me${qr}.${ql}artistid${qr}
+        ORDER BY ${ql}me${qr}.${ql}artistid${qr}
+      ) },
+      [
+        [ $inner_lim_bindtype
+          => 1 ],
+        [ $inner_lim_bindtype
+          => 1 ],
+        [ { sqlt_datatype => 'integer' }
+          => 2 ],
+      ],
+      "No leakage of correlated subquery identifiers (quote_names => $quote_names, inner alias '$inner_relchain')"
+    );
+  }
+}
+
 done_testing;