8 use DBIC::SqlMakerTest;
10 my $schema = DBICTest->init_schema();
11 my $orig_debug = $schema->storage->debug;
13 my $cdrs = $schema->resultset('CD')->search({ 'me.artist' => { '!=', 2 }});
17 siblings => $cdrs->search ({ artist => $_->get_column('artist') })->count - 1,
18 track_titles => [ map { $_->title } ($_->tracks->all) ],
22 my $c_rs = $cdrs->search ({}, {
24 '+columns' => { sibling_count => $cdrs->search(
26 'siblings.artist' => { -ident => 'me.artist' },
27 'siblings.cdid' => { '!=' => ['-and', { -ident => 'me.cdid' }, 23414] },
28 }, { alias => 'siblings' },
29 )->count_rs->as_query,
36 SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track,
39 WHERE siblings.artist = me.artist
40 AND siblings.cdid != me.cdid
41 AND siblings.cdid != ?
44 tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at
46 LEFT JOIN track tracks
47 ON tracks.cd = me.cdid
53 [ { sqlt_datatype => 'integer', dbic_colname => 'siblings.cdid' }
56 [ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' }
60 [ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' }
63 'Expected SQL on correlated realiased subquery'
67 $schema->storage->debugcb(sub { $queries++; });
68 $schema->storage->debug(1);
73 track_titles => [ map { $_->title } ($_->tracks->all) ],
74 siblings => $_->get_column ('sibling_count'),
79 'Proper information retrieved from correlated subquery'
82 is ($queries, 1, 'Only 1 query fired to retrieve everything');
84 $schema->storage->debug($orig_debug);
85 $schema->storage->debugcb(undef);
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' }},
96 '+as' => [qw/active_from active_to/],
102 SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track,
105 WHERE siblings.artist = me.artist
106 AND siblings.cdid != me.cdid
107 AND siblings.cdid != ?
110 (SELECT MIN( year ), MAX( year )
112 WHERE siblings.artist = me.artist
115 tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at
117 LEFT JOIN track tracks
118 ON tracks.cd = me.cdid
124 [ { sqlt_datatype => 'integer', dbic_colname => 'siblings.cdid' }
127 [ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' }
131 [ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' }
135 [ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' }
138 'Expected SQL on correlated realiased subquery'
141 $schema->storage->disconnect;
143 # test for subselect identifier leakage
144 # NOTE - the hodge-podge mix of literal and regular identifuers is *deliberate*
145 for my $quote_names (0,1) {
146 my $schema = DBICTest->init_schema( quote_names => $quote_names );
148 my ($ql, $qr) = $schema->storage->sql_maker->_quote_chars;
150 my $art_rs = $schema->resultset('Artist')->search ({}, {
151 order_by => 'me.artistid',
156 my $inner_lim_bindtype = { sqlt_datatype => 'integer' };
158 for my $inner_relchain (qw( cds_unordered cds ) ) {
160 my $stupid_latest_competition_release_query = $schema->resultset('Artist')->search(
161 { 'competition.artistid' => { '!=', { -ident => 'me.artistid' } } },
162 { alias => 'competition' },
163 )->search_related( $inner_relchain, {}, {
164 rows => 1, order_by => 'year', columns => { year => \'year' }, distinct => 1
165 })->get_column(\'year')->max_rs;
167 my $final_query = $art_rs->search( {}, {
168 '+columns' => { max_competition_release => \[
169 @${ $stupid_latest_competition_release_query->as_query }
173 # we are using cds_unordered explicitly above - do the sorting manually
174 my @results = sort { $a->{artistid} <=> $b->{artistid} } @{$final_query->all_hri};
175 @$_ = sort { $a->{cdid} <=> $b->{cdid} } @$_ for map { $_->{cds} } @results;
180 { artistid => 1, charfield => undef, max_competition_release => 1998, name => "Caterwauler McCrae", rank => 13, cds => [
181 { artist => 1, cdid => 1, genreid => 1, single_track => undef, title => "Spoonful of bees", year => 1999 },
182 { artist => 1, cdid => 2, genreid => undef, single_track => undef, title => "Forkful of bees", year => 2001 },
183 { artist => 1, cdid => 3, genreid => undef, single_track => undef, title => "Caterwaulin' Blues", year => 1997 },
185 { artistid => 2, charfield => undef, max_competition_release => 1997, name => "Random Boy Band", rank => 13, cds => [
186 { artist => 2, cdid => 4, genreid => undef, single_track => undef, title => "Generic Manufactured Singles", year => 2001 },
189 "Expected result from weird query",
192 # the decomposition to sql/bind is *deliberate* in both instances
193 # we want to ensure this keeps working for lietral sql, even when
194 # as_query switches to return an overloaded dq node
195 my ($sql, @bind) = @${ $final_query->as_query };
197 my $correlated_sql = qq{ (
201 FROM ${ql}artist${qr} ${ql}competition${qr}
202 JOIN cd ${ql}${inner_relchain}${qr}
203 ON ${ql}${inner_relchain}${qr}.${ql}artist${qr} = ${ql}competition${qr}.${ql}artistid${qr}
204 WHERE ${ql}competition${qr}.${ql}artistid${qr} != ${ql}me${qr}.${ql}artistid${qr}
206 ORDER BY MIN( ${ql}year${qr} )
208 ) ${ql}${inner_relchain}${qr}
215 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},
217 ${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}
219 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},
221 FROM ${ql}artist${qr} ${ql}me${qr}
222 ORDER BY ${ql}me${qr}.${ql}artistid${qr}
225 LEFT JOIN cd ${ql}cds${qr}
226 ON ${ql}cds${qr}.${ql}artist${qr} = ${ql}me${qr}.${ql}artistid${qr}
227 ORDER BY ${ql}me${qr}.${ql}artistid${qr}
230 [ $inner_lim_bindtype
232 [ $inner_lim_bindtype
234 [ { sqlt_datatype => 'integer' }
237 "No leakage of correlated subquery identifiers (quote_names => $quote_names, inner alias '$inner_relchain')"