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