t/prefetch/correlated.t

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