t/prefetch/correlated.t

   1 use strict;
   2 use warnings;
   3
   4 use Test::More;
   5 use Test::Deep;
   6 use lib qw(t/lib);
   7 use DBICTest;
   8 use DBIC::SqlMakerTest;
   9
  10 my $schema = DBICTest->init_schema();
  11
  12 my $cdrs = $schema->resultset('CD')->search({ 'me.artist' => { '!=', 2 }});
  13
  14 my $cd_data = { map {
  15   $_->cdid => {
  16     siblings => $cdrs->search ({ artist => $_->get_column('artist') })->count - 1,
  17     track_titles => [ map { $_->title } ($_->tracks->all) ],
  18   },
  19 } ( $cdrs->all ) };
  20
  21 my $c_rs = $cdrs->search ({}, {
  22   prefetch => 'tracks',
  23   '+columns' => { sibling_count => $cdrs->search(
  24       {
  25         'siblings.artist' => { -ident => 'me.artist' },
  26         'siblings.cdid' => { '!=' => ['-and', { -ident => 'me.cdid' }, 23414] },
  27       }, { alias => 'siblings' },
  28     )->count_rs->as_query,
  29   },
  30 });
  31
  32 is_same_sql_bind(
  33   $c_rs->as_query,
  34   '(
  35     SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track,
  36            (SELECT COUNT( * )
  37               FROM cd siblings
  38             WHERE me.artist != ?
  39               AND siblings.artist = me.artist
  40               AND siblings.cdid != me.cdid
  41               AND siblings.cdid != ?
  42            ),
  43            tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at
  44       FROM cd me
  45       LEFT JOIN track tracks
  46         ON tracks.cd = me.cdid
  47     WHERE me.artist != ?
  48   )',
  49   [
  50
  51     # subselect
  52     [ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' }
  53       => 2 ],
  54
  55     [ { sqlt_datatype => 'integer', dbic_colname => 'siblings.cdid' }
  56       => 23414 ],
  57
  58     # outher WHERE
  59     [ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' }
  60       => 2 ],
  61   ],
  62   'Expected SQL on correlated realiased subquery'
  63 );
  64
  65 $schema->is_executed_querycount( sub {
  66   cmp_deeply (
  67     { map
  68       { $_->cdid => {
  69         track_titles => [ map { $_->title } ($_->tracks->all) ],
  70         siblings => $_->get_column ('sibling_count'),
  71       } }
  72       $c_rs->all
  73     },
  74     $cd_data,
  75     'Proper information retrieved from correlated subquery'
  76   );
  77 }, 1, 'Only 1 query fired to retrieve everything');
  78
  79 # now add an unbalanced select/as pair
  80 $c_rs = $c_rs->search ({}, {
  81   '+select' => $cdrs->search(
  82     { 'siblings.artist' => { -ident => 'me.artist' } },
  83     { alias => 'siblings', columns => [
  84       { first_year => { min => 'year' }},
  85       { last_year => { max => 'year' }},
  86     ]},
  87   )->as_query,
  88   '+as' => [qw/active_from active_to/],
  89 });
  90
  91 is_same_sql_bind(
  92   $c_rs->as_query,
  93   '(
  94     SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track,
  95            (SELECT COUNT( * )
  96               FROM cd siblings
  97             WHERE me.artist != ?
  98               AND siblings.artist = me.artist
  99               AND siblings.cdid != me.cdid
 100               AND siblings.cdid != ?
 101            ),
 102            (SELECT MIN( year ), MAX( year )
 103               FROM cd siblings
 104             WHERE me.artist != ?
 105               AND siblings.artist = me.artist
 106            ),
 107            tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at
 108       FROM cd me
 109       LEFT JOIN track tracks
 110         ON tracks.cd = me.cdid
 111     WHERE me.artist != ?
 112   )',
 113   [
 114
 115     # first subselect
 116     [ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' }
 117       => 2 ],
 118
 119     [ { sqlt_datatype => 'integer', dbic_colname => 'siblings.cdid' }
 120       => 23414 ],
 121
 122     # second subselect
 123     [ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' }
 124       => 2 ],
 125
 126     # outher WHERE
 127     [ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' }
 128       => 2 ],
 129   ],
 130   'Expected SQL on correlated realiased subquery'
 131 );
 132
 133 # test for subselect identifier leakage
 134 # NOTE - the hodge-podge mix of literal and regular identifuers is *deliberate*
 135 for my $quote_names (0,1) {
 136   my $schema = DBICTest->init_schema( quote_names => $quote_names );
 137
 138   my ($ql, $qr) = $schema->storage->sql_maker->_quote_chars;
 139
 140   my $art_rs = $schema->resultset('Artist')->search ({}, {
 141     order_by => 'me.artistid',
 142     prefetch => 'cds',
 143     rows => 2,
 144   });
 145
 146   my $inner_lim_bindtype = { sqlt_datatype => 'integer' };
 147
 148   for my $inner_relchain (qw( cds_unordered cds ) ) {
 149
 150     my $stupid_latest_competition_release_query = $schema->resultset('Artist')->search(
 151       { 'competition.artistid' => { '!=', { -ident => 'me.artistid' } } },
 152       { alias => 'competition' },
 153     )->search_related( $inner_relchain, {}, {
 154       rows => 1, order_by => 'year', columns => { year => \'year' }, distinct => 1
 155     })->get_column(\'year')->max_rs;
 156
 157     my $final_query = $art_rs->search( {}, {
 158       '+columns' => { max_competition_release => \[
 159         @${ $stupid_latest_competition_release_query->as_query }
 160       ]},
 161     });
 162
 163     # we are using cds_unordered explicitly above - do the sorting manually
 164     my @results = sort { $a->{artistid} <=> $b->{artistid} } @{$final_query->all_hri};
 165     @$_ = sort { $a->{cdid} <=> $b->{cdid} } @$_ for map { $_->{cds} } @results;
 166
 167     is_deeply (
 168       \@results,
 169       [
 170         { artistid => 1, charfield => undef, max_competition_release => 1998, name => "Caterwauler McCrae", rank => 13, cds => [
 171           { artist => 1, cdid => 1, genreid => 1, single_track => undef, title => "Spoonful of bees", year => 1999 },
 172           { artist => 1, cdid => 2, genreid => undef, single_track => undef, title => "Forkful of bees", year => 2001 },
 173           { artist => 1, cdid => 3, genreid => undef, single_track => undef, title => "Caterwaulin' Blues", year => 1997 },
 174         ] },
 175         { artistid => 2, charfield => undef, max_competition_release => 1997, name => "Random Boy Band", rank => 13, cds => [
 176           { artist => 2, cdid => 4, genreid => undef, single_track => undef, title => "Generic Manufactured Singles", year => 2001 },
 177         ] },
 178       ],
 179       "Expected result from weird query",
 180     );
 181
 182     # the decomposition to sql/bind is *deliberate* in both instances
 183     # we want to ensure this keeps working for lietral sql, even when
 184     # as_query switches to return an overloaded dq node
 185     my ($sql, @bind) = @${ $final_query->as_query };
 186
 187     my $correlated_sql = qq{ (
 188       SELECT MAX( year )
 189         FROM (
 190           SELECT year
 191             FROM ${ql}artist${qr} ${ql}competition${qr}
 192             JOIN cd ${ql}${inner_relchain}${qr}
 193               ON ${ql}${inner_relchain}${qr}.${ql}artist${qr} = ${ql}competition${qr}.${ql}artistid${qr}
 194           WHERE ${ql}competition${qr}.${ql}artistid${qr} != ${ql}me${qr}.${ql}artistid${qr}
 195           GROUP BY year
 196           ORDER BY MIN( ${ql}year${qr} )
 197           LIMIT ?
 198         ) ${ql}${inner_relchain}${qr}
 199     )};
 200
 201     is_same_sql_bind(
 202       $sql,
 203       \@bind,
 204       qq{ (
 205         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},
 206                 $correlated_sql,
 207                 ${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}
 208           FROM (
 209             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},
 210                     $correlated_sql
 211               FROM ${ql}artist${qr} ${ql}me${qr}
 212               ORDER BY ${ql}me${qr}.${ql}artistid${qr}
 213               LIMIT ?
 214           ) ${ql}me${qr}
 215           LEFT JOIN cd ${ql}cds${qr}
 216             ON ${ql}cds${qr}.${ql}artist${qr} = ${ql}me${qr}.${ql}artistid${qr}
 217         ORDER BY ${ql}me${qr}.${ql}artistid${qr}
 218       ) },
 219       [
 220         [ $inner_lim_bindtype
 221           => 1 ],
 222         [ $inner_lim_bindtype
 223           => 1 ],
 224         [ { sqlt_datatype => 'integer' }
 225           => 2 ],
 226       ],
 227       "No leakage of correlated subquery identifiers (quote_names => $quote_names, inner alias '$inner_relchain')"
 228     );
 229   }
 230 }
 231
 232 done_testing;