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