Commit | Line | Data |
4c2b30d6 |
1 | use strict; |
2 | use warnings; |
3 | |
4 | use Test::More; |
52864fbd |
5 | use Test::Deep; |
4c2b30d6 |
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 | |
4c2b30d6 |
22 | my $c_rs = $cdrs->search ({}, { |
23 | prefetch => 'tracks', |
24 | '+columns' => { sibling_count => $cdrs->search( |
25 | { |
26 | 'siblings.artist' => { -ident => 'me.artist' }, |
0e773352 |
27 | 'siblings.cdid' => { '!=' => ['-and', { -ident => 'me.cdid' }, 23414] }, |
4c2b30d6 |
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 |
8d005ad9 |
39 | WHERE me.artist != ? |
40 | AND siblings.artist = me.artist |
4c2b30d6 |
41 | AND siblings.cdid != me.cdid |
42 | AND siblings.cdid != ? |
4c2b30d6 |
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 != ? |
4c2b30d6 |
49 | )', |
50 | [ |
36fd7f07 |
51 | |
52 | # subselect |
0e773352 |
53 | [ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' } |
54 | => 2 ], |
36fd7f07 |
55 | |
8d005ad9 |
56 | [ { sqlt_datatype => 'integer', dbic_colname => 'siblings.cdid' } |
57 | => 23414 ], |
58 | |
36fd7f07 |
59 | # outher WHERE |
0e773352 |
60 | [ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' } |
61 | => 2 ], |
4c2b30d6 |
62 | ], |
63 | 'Expected SQL on correlated realiased subquery' |
64 | ); |
65 | |
36fd7f07 |
66 | my $queries = 0; |
67 | $schema->storage->debugcb(sub { $queries++; }); |
68 | $schema->storage->debug(1); |
69 | |
52864fbd |
70 | cmp_deeply ( |
4c2b30d6 |
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 | |
36fd7f07 |
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 | |
36fd7f07 |
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 |
8d005ad9 |
105 | WHERE me.artist != ? |
106 | AND siblings.artist = me.artist |
36fd7f07 |
107 | AND siblings.cdid != me.cdid |
108 | AND siblings.cdid != ? |
36fd7f07 |
109 | ), |
110 | (SELECT MIN( year ), MAX( year ) |
111 | FROM cd siblings |
8d005ad9 |
112 | WHERE me.artist != ? |
113 | AND siblings.artist = me.artist |
36fd7f07 |
114 | ), |
f7f53a89 |
115 | tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at |
36fd7f07 |
116 | FROM cd me |
117 | LEFT JOIN track tracks |
118 | ON tracks.cd = me.cdid |
119 | WHERE me.artist != ? |
36fd7f07 |
120 | )', |
121 | [ |
122 | |
123 | # first subselect |
0e773352 |
124 | [ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' } |
125 | => 2 ], |
36fd7f07 |
126 | |
8d005ad9 |
127 | [ { sqlt_datatype => 'integer', dbic_colname => 'siblings.cdid' } |
128 | => 23414 ], |
129 | |
36fd7f07 |
130 | # second subselect |
0e773352 |
131 | [ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' } |
132 | => 2 ], |
36fd7f07 |
133 | |
36fd7f07 |
134 | # outher WHERE |
0e773352 |
135 | [ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' } |
136 | => 2 ], |
36fd7f07 |
137 | ], |
138 | 'Expected SQL on correlated realiased subquery' |
139 | ); |
140 | |
0dadd60d |
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 | |
c6a7ffb3 |
171 | # we are using cds_unordered explicitly above - do the sorting manually |
172 | my @results = sort { $a->{artistid} <=> $b->{artistid} } @{$final_query->all_hri}; |
173 | @$_ = sort { $a->{cdid} <=> $b->{cdid} } @$_ for map { $_->{cds} } @results; |
174 | |
0dadd60d |
175 | is_deeply ( |
c6a7ffb3 |
176 | \@results, |
0dadd60d |
177 | [ |
178 | { artistid => 1, charfield => undef, max_competition_release => 1998, name => "Caterwauler McCrae", rank => 13, cds => [ |
0dadd60d |
179 | { artist => 1, cdid => 1, genreid => 1, single_track => undef, title => "Spoonful of bees", year => 1999 }, |
c6a7ffb3 |
180 | { artist => 1, cdid => 2, genreid => undef, single_track => undef, title => "Forkful of bees", year => 2001 }, |
181 | { artist => 1, cdid => 3, genreid => undef, single_track => undef, title => "Caterwaulin' Blues", year => 1997 }, |
0dadd60d |
182 | ] }, |
183 | { artistid => 2, charfield => undef, max_competition_release => 1997, name => "Random Boy Band", rank => 13, cds => [ |
184 | { artist => 2, cdid => 4, genreid => undef, single_track => undef, title => "Generic Manufactured Singles", year => 2001 }, |
185 | ] }, |
186 | ], |
187 | "Expected result from weird query", |
188 | ); |
189 | |
190 | # the decomposition to sql/bind is *deliberate* in both instances |
191 | # we want to ensure this keeps working for lietral sql, even when |
192 | # as_query switches to return an overloaded dq node |
193 | my ($sql, @bind) = @${ $final_query->as_query }; |
194 | |
195 | my $correlated_sql = qq{ ( |
196 | SELECT MAX( year ) |
197 | FROM ( |
198 | SELECT year |
199 | FROM ${ql}artist${qr} ${ql}competition${qr} |
200 | JOIN cd ${ql}${inner_relchain}${qr} |
201 | ON ${ql}${inner_relchain}${qr}.${ql}artist${qr} = ${ql}competition${qr}.${ql}artistid${qr} |
202 | WHERE ${ql}competition${qr}.${ql}artistid${qr} != ${ql}me${qr}.${ql}artistid${qr} |
203 | GROUP BY year |
204 | ORDER BY MIN( ${ql}year${qr} ) |
205 | LIMIT ? |
206 | ) ${ql}${inner_relchain}${qr} |
207 | )}; |
208 | |
209 | is_same_sql_bind( |
210 | $sql, |
211 | \@bind, |
212 | qq{ ( |
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 | ${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} |
216 | FROM ( |
217 | 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}, |
218 | $correlated_sql |
219 | FROM ${ql}artist${qr} ${ql}me${qr} |
220 | ORDER BY ${ql}me${qr}.${ql}artistid${qr} |
221 | LIMIT ? |
222 | ) ${ql}me${qr} |
223 | LEFT JOIN cd ${ql}cds${qr} |
224 | ON ${ql}cds${qr}.${ql}artist${qr} = ${ql}me${qr}.${ql}artistid${qr} |
225 | ORDER BY ${ql}me${qr}.${ql}artistid${qr} |
226 | ) }, |
227 | [ |
228 | [ $inner_lim_bindtype |
229 | => 1 ], |
230 | [ $inner_lim_bindtype |
231 | => 1 ], |
232 | [ { sqlt_datatype => 'integer' } |
233 | => 2 ], |
234 | ], |
235 | "No leakage of correlated subquery identifiers (quote_names => $quote_names, inner alias '$inner_relchain')" |
236 | ); |
237 | } |
238 | } |
239 | |
4c2b30d6 |
240 | done_testing; |