Commit | Line | Data |
cf02fc47 |
1 | use strict; |
2 | use warnings; |
3 | use Test::More; |
97084113 |
4 | use Test::Warn; |
cf02fc47 |
5 | use Test::Exception; |
db8e4588 |
6 | use SQL::Abstract::Test import => [qw(is_same_sql_bind diag_where dumper)]; |
cf02fc47 |
7 | |
cf02fc47 |
8 | use SQL::Abstract; |
9 | |
cf02fc47 |
10 | my @in_between_tests = ( |
11 | { |
12 | where => { x => { -between => [1, 2] } }, |
13 | stmt => 'WHERE (x BETWEEN ? AND ?)', |
14 | bind => [qw/1 2/], |
15 | test => '-between with two placeholders', |
16 | }, |
17 | { |
18 | where => { x => { -between => [\"1", 2] } }, |
19 | stmt => 'WHERE (x BETWEEN 1 AND ?)', |
20 | bind => [qw/2/], |
21 | test => '-between with one literal sql arg and one placeholder', |
22 | }, |
23 | { |
24 | where => { x => { -between => [1, \"2"] } }, |
25 | stmt => 'WHERE (x BETWEEN ? AND 2)', |
26 | bind => [qw/1/], |
27 | test => '-between with one placeholder and one literal sql arg', |
28 | }, |
29 | { |
30 | where => { x => { -between => [\'current_date - 1', \'current_date - 0'] } }, |
31 | stmt => 'WHERE (x BETWEEN current_date - 1 AND current_date - 0)', |
32 | bind => [], |
33 | test => '-between with two literal sql arguments', |
34 | }, |
35 | { |
4d8b3dc4 |
36 | where => { x => { -between => [ \['current_date - ?', 1], \['current_date - ?', 0] ] } }, |
37 | stmt => 'WHERE (x BETWEEN current_date - ? AND current_date - ?)', |
38 | bind => [1, 0], |
39 | test => '-between with two literal sql arguments with bind', |
40 | }, |
41 | { |
cf02fc47 |
42 | where => { x => { -between => \['? AND ?', 1, 2] } }, |
43 | stmt => 'WHERE (x BETWEEN ? AND ?)', |
44 | bind => [1,2], |
45 | test => '-between with literal sql with placeholders (\["? AND ?", scalar, scalar])', |
46 | }, |
47 | { |
48 | where => { x => { -between => \["'something' AND ?", 2] } }, |
49 | stmt => "WHERE (x BETWEEN 'something' AND ?)", |
50 | bind => [2], |
51 | test => '-between with literal sql with one literal arg and one placeholder (\["\'something\' AND ?", scalar])', |
52 | }, |
53 | { |
54 | where => { x => { -between => \["? AND 'something'", 1] } }, |
55 | stmt => "WHERE (x BETWEEN ? AND 'something')", |
56 | bind => [1], |
57 | test => '-between with literal sql with one placeholder and one literal arg (\["? AND \'something\'", scalar])', |
58 | }, |
59 | { |
4d8b3dc4 |
60 | where => { x => { -between => \"'this' AND 'that'" } }, |
cf02fc47 |
61 | stmt => "WHERE (x BETWEEN 'this' AND 'that')", |
62 | bind => [], |
4d8b3dc4 |
63 | test => '-between with literal sql with a literal (\"\'this\' AND \'that\'")', |
cf02fc47 |
64 | }, |
7f54040f |
65 | |
66 | # generate a set of invalid -between tests |
67 | ( map { { |
68 | where => { x => { -between => $_ } }, |
69 | test => 'invalid -between args', |
97084113 |
70 | throws => qr|Operator 'BETWEEN' requires either an arrayref with two defined values or expressions, or a single literal scalarref/arrayref-ref|, |
7f54040f |
71 | } } ( |
72 | [ 1, 2, 3 ], |
73 | [ 1, undef, 3 ], |
74 | [ undef, 2, 3 ], |
75 | [ 1, 2, undef ], |
76 | [ 1, undef ], |
77 | [ undef, 2 ], |
78 | [ undef, undef ], |
79 | [ 1 ], |
80 | [ undef ], |
81 | [], |
82 | 1, |
83 | undef, |
84 | )), |
e41c3bdd |
85 | { |
86 | where => { |
0336eddb |
87 | start0 => { -between => [ 1, { -upper => 2 } ] }, |
e41c3bdd |
88 | start1 => { -between => \["? AND ?", 1, 2] }, |
89 | start2 => { -between => \"lower(x) AND upper(y)" }, |
90 | start3 => { -between => [ |
91 | \"lower(x)", |
92 | \["upper(?)", 'stuff' ], |
93 | ] }, |
94 | }, |
95 | stmt => "WHERE ( |
03e6883c |
96 | ( start0 BETWEEN ? AND UPPER(?) ) |
97 | AND ( start1 BETWEEN ? AND ? ) |
98 | AND ( start2 BETWEEN lower(x) AND upper(y) ) |
99 | AND ( start3 BETWEEN lower(x) AND upper(?) ) |
100 | )", |
101 | bind => [1, 2, 1, 2, 'stuff'], |
102 | test => '-between POD test', |
103 | }, |
104 | { |
105 | args => { restore_old_unop_handling => 1 }, |
106 | where => { |
107 | start0 => { -between => [ 1, { -upper => 2 } ] }, |
108 | start1 => { -between => \["? AND ?", 1, 2] }, |
109 | start2 => { -between => \"lower(x) AND upper(y)" }, |
110 | start3 => { -between => [ |
111 | \"lower(x)", |
112 | \["upper(?)", 'stuff' ], |
113 | ] }, |
114 | }, |
115 | stmt => "WHERE ( |
b3b79607 |
116 | ( start0 BETWEEN ? AND UPPER ? ) |
e41c3bdd |
117 | AND ( start1 BETWEEN ? AND ? ) |
118 | AND ( start2 BETWEEN lower(x) AND upper(y) ) |
119 | AND ( start3 BETWEEN lower(x) AND upper(?) ) |
120 | )", |
121 | bind => [1, 2, 1, 2, 'stuff'], |
122 | test => '-between POD test', |
123 | }, |
5e5cbf51 |
124 | { |
125 | args => { bindtype => 'columns' }, |
126 | where => { |
127 | start0 => { -between => [ 1, { -upper => 2 } ] }, |
128 | start1 => { -between => \["? AND ?", [ start1 => 1], [start1 => 2] ] }, |
129 | start2 => { -between => \"lower(x) AND upper(y)" }, |
130 | start3 => { -between => [ |
131 | \"lower(x)", |
132 | \["upper(?)", [ start3 => 'stuff'] ], |
133 | ] }, |
134 | }, |
135 | stmt => "WHERE ( |
03e6883c |
136 | ( start0 BETWEEN ? AND UPPER(?) ) |
137 | AND ( start1 BETWEEN ? AND ? ) |
138 | AND ( start2 BETWEEN lower(x) AND upper(y) ) |
139 | AND ( start3 BETWEEN lower(x) AND upper(?) ) |
140 | )", |
141 | bind => [ |
142 | [ start0 => 1 ], |
143 | [ start0 => 2 ], |
144 | [ start1 => 1 ], |
145 | [ start1 => 2 ], |
146 | [ start3 => 'stuff' ], |
147 | ], |
148 | test => '-between POD test', |
149 | }, |
150 | { |
151 | args => { restore_old_unop_handling => 1, bindtype => 'columns' }, |
152 | where => { |
153 | start0 => { -between => [ 1, { -upper => 2 } ] }, |
154 | start1 => { -between => \["? AND ?", [ start1 => 1], [start1 => 2] ] }, |
155 | start2 => { -between => \"lower(x) AND upper(y)" }, |
156 | start3 => { -between => [ |
157 | \"lower(x)", |
158 | \["upper(?)", [ start3 => 'stuff'] ], |
159 | ] }, |
160 | }, |
161 | stmt => "WHERE ( |
5e5cbf51 |
162 | ( start0 BETWEEN ? AND UPPER ? ) |
163 | AND ( start1 BETWEEN ? AND ? ) |
164 | AND ( start2 BETWEEN lower(x) AND upper(y) ) |
165 | AND ( start3 BETWEEN lower(x) AND upper(?) ) |
166 | )", |
167 | bind => [ |
168 | [ start0 => 1 ], |
169 | [ start0 => 2 ], |
170 | [ start1 => 1 ], |
171 | [ start1 => 2 ], |
172 | [ start3 => 'stuff' ], |
173 | ], |
174 | test => '-between POD test', |
175 | }, |
904c3621 |
176 | { |
177 | where => { 'test1.a' => { 'In', ['boom', 'bang'] } }, |
178 | stmt => ' WHERE ( test1.a IN ( ?, ? ) )', |
179 | bind => ['boom', 'bang'], |
180 | test => 'In (no dash, initial cap) with qualified column', |
181 | }, |
182 | { |
183 | where => { a => { 'between', ['boom', 'bang'] } }, |
184 | stmt => ' WHERE ( a BETWEEN ? AND ? )', |
185 | bind => ['boom', 'bang'], |
186 | test => 'between (no dash) with two placeholders', |
187 | }, |
4a1f01a3 |
188 | |
189 | { |
4a1f01a3 |
190 | where => { x => { -in => [ 1 .. 3] } }, |
7d273452 |
191 | stmt => "WHERE x IN (?, ?, ?)", |
192 | bind => [ 1 .. 3 ], |
4a1f01a3 |
193 | test => '-in with an array of scalars', |
194 | }, |
195 | { |
e41c3bdd |
196 | where => { x => { -in => [] } }, |
7d273452 |
197 | stmt => "WHERE 0=1", |
e41c3bdd |
198 | bind => [], |
199 | test => '-in with an empty array', |
200 | }, |
201 | { |
4a1f01a3 |
202 | where => { x => { -in => \'( 1,2,lower(y) )' } }, |
7d273452 |
203 | stmt => "WHERE x IN ( 1,2,lower(y) )", |
4a1f01a3 |
204 | bind => [], |
205 | test => '-in with a literal scalarref', |
206 | }, |
46dc2f3e |
207 | |
208 | # note that outer parens are opened even though literal was requested below |
4a1f01a3 |
209 | { |
4a1f01a3 |
210 | where => { x => { -in => \['( ( ?,?,lower(y) ) )', 1, 2] } }, |
7d273452 |
211 | stmt => "WHERE x IN ( ?,?,lower(y) )", |
4a1f01a3 |
212 | bind => [1, 2], |
213 | test => '-in with a literal arrayrefref', |
214 | }, |
e41c3bdd |
215 | { |
e41c3bdd |
216 | where => { |
171a709f |
217 | status => { -in => \"(SELECT status_codes\nFROM states)" }, |
218 | }, |
7d273452 |
219 | stmt => " WHERE status IN ( SELECT status_codes FROM states )", |
171a709f |
220 | bind => [], |
221 | test => '-in multi-line subquery test', |
222 | }, |
a5f91feb |
223 | |
224 | # check that the outer paren opener is not too agressive |
225 | # note: this syntax *is not legal* on SQLite (maybe others) |
226 | # see end of https://rt.cpan.org/Ticket/Display.html?id=99503 |
227 | { |
228 | where => { foo => { -in => \ '(SELECT 1) UNION (SELECT 2)' } }, |
229 | stmt => 'WHERE foo IN ( (SELECT 1) UNION (SELECT 2) )', |
230 | bind => [], |
231 | test => '-in paren-opening works on balanced pairs only', |
232 | }, |
233 | |
171a709f |
234 | { |
171a709f |
235 | where => { |
e41c3bdd |
236 | customer => { -in => \[ |
237 | 'SELECT cust_id FROM cust WHERE balance > ?', |
238 | 2000, |
239 | ]}, |
240 | status => { -in => \'SELECT status_codes FROM states' }, |
241 | }, |
242 | stmt => " |
7d273452 |
243 | WHERE |
e41c3bdd |
244 | customer IN ( SELECT cust_id FROM cust WHERE balance > ? ) |
245 | AND status IN ( SELECT status_codes FROM states ) |
e41c3bdd |
246 | ", |
247 | bind => [2000], |
248 | test => '-in POD test', |
249 | }, |
46dc2f3e |
250 | |
0336eddb |
251 | { |
252 | where => { x => { -in => [ \['LOWER(?)', 'A' ], \'LOWER(b)', { -lower => 'c' } ] } }, |
03e6883c |
253 | stmt => " WHERE ( x IN ( LOWER(?), LOWER(b), LOWER(?) ) )", |
254 | bind => [qw/A c/], |
255 | test => '-in with an array of function array refs with args', |
256 | }, |
257 | { |
258 | args => { restore_old_unop_handling => 1 }, |
259 | where => { x => { -in => [ \['LOWER(?)', 'A' ], \'LOWER(b)', { -lower => 'c' } ] } }, |
0336eddb |
260 | stmt => " WHERE ( x IN ( LOWER(?), LOWER(b), LOWER ? ) )", |
261 | bind => [qw/A c/], |
262 | test => '-in with an array of function array refs with args', |
263 | }, |
279eb282 |
264 | { |
97084113 |
265 | throws => qr/ |
032dfe20 |
266 | \QSQL::Abstract before v1.75 used to generate incorrect SQL \E |
267 | \Qwhen the -IN operator was given an undef-containing list: \E |
268 | \Q!!!AUDIT YOUR CODE AND DATA!!! (the upcoming Data::Query-based \E |
269 | \Qversion of SQL::Abstract will emit the logically correct SQL \E |
270 | \Qinstead of raising this exception)\E |
271 | /x, |
279eb282 |
272 | where => { x => { -in => [ 1, undef ] } }, |
032dfe20 |
273 | stmt => " WHERE ( x IN ( ? ) OR x IS NULL )", |
279eb282 |
274 | bind => [ 1 ], |
428975b0 |
275 | test => '-in with undef as an element', |
279eb282 |
276 | }, |
277 | { |
97084113 |
278 | throws => qr/ |
032dfe20 |
279 | \QSQL::Abstract before v1.75 used to generate incorrect SQL \E |
280 | \Qwhen the -IN operator was given an undef-containing list: \E |
281 | \Q!!!AUDIT YOUR CODE AND DATA!!! (the upcoming Data::Query-based \E |
282 | \Qversion of SQL::Abstract will emit the logically correct SQL \E |
283 | \Qinstead of raising this exception)\E |
284 | /x, |
279eb282 |
285 | where => { x => { -in => [ 1, undef, 2, 3, undef ] } }, |
032dfe20 |
286 | stmt => " WHERE ( x IN ( ?, ?, ? ) OR x IS NULL )", |
279eb282 |
287 | bind => [ 1, 2, 3 ], |
032dfe20 |
288 | test => '-in with multiple undef elements', |
279eb282 |
289 | }, |
904c3621 |
290 | { |
291 | where => { a => { -in => 42 }, b => { -not_in => 42 } }, |
7d273452 |
292 | stmt => ' WHERE a IN ( ? ) AND b NOT IN ( ? )', |
904c3621 |
293 | bind => [ 42, 42 ], |
294 | test => '-in, -not_in with scalar', |
295 | }, |
296 | { |
297 | where => { a => { -in => [] }, b => { -not_in => [] } }, |
298 | stmt => ' WHERE ( 0=1 AND 1=1 )', |
299 | bind => [], |
300 | test => '-in, -not_in with empty arrays', |
301 | }, |
302 | { |
303 | throws => qr/ |
304 | \QSQL::Abstract before v1.75 used to generate incorrect SQL \E |
305 | \Qwhen the -IN operator was given an undef-containing list: \E |
306 | \Q!!!AUDIT YOUR CODE AND DATA!!! (the upcoming Data::Query-based \E |
307 | \Qversion of SQL::Abstract will emit the logically correct SQL \E |
308 | \Qinstead of raising this exception)\E |
309 | /x, |
310 | where => { a => { -in => [42, undef] }, b => { -not_in => [42, undef] } }, |
311 | stmt => ' WHERE ( ( a IN ( ? ) OR a IS NULL ) AND b NOT IN ( ? ) AND b IS NOT NULL )', |
312 | bind => [ 42, 42 ], |
313 | test => '-in, -not_in with undef among elements', |
314 | }, |
315 | { |
316 | throws => qr/ |
317 | \QSQL::Abstract before v1.75 used to generate incorrect SQL \E |
318 | \Qwhen the -IN operator was given an undef-containing list: \E |
319 | \Q!!!AUDIT YOUR CODE AND DATA!!! (the upcoming Data::Query-based \E |
320 | \Qversion of SQL::Abstract will emit the logically correct SQL \E |
321 | \Qinstead of raising this exception)\E |
322 | /x, |
323 | where => { a => { -in => [undef] }, b => { -not_in => [undef] } }, |
324 | stmt => ' WHERE ( a IS NULL AND b IS NOT NULL )', |
325 | bind => [], |
326 | test => '-in, -not_in with just undef element', |
327 | }, |
328 | { |
329 | where => { a => { -in => undef } }, |
330 | throws => qr/Argument passed to the 'IN' operator can not be undefined/, |
331 | test => '-in with undef argument', |
332 | }, |
ddd6fbb6 |
333 | |
334 | { |
3445a1e7 |
335 | where => { -in => [ 'bob', 4, 2 ] }, |
336 | stmt => ' WHERE (bob IN (?, ?))', |
337 | bind => [ 4, 2 ], |
ddd6fbb6 |
338 | test => 'Top level -in', |
339 | }, |
2c99e31e |
340 | # This works but then SQL::Abstract::Tree breaks - something for a later commit |
341 | # { |
342 | # where => { -in => [ { -list => [ qw(x y) ] }, { -list => [ 1, 3 ] }, { -list => [ 2, 4 ] } ] }, |
343 | # stmt => ' WHERE ((x, y) IN ((?, ?), (?, ?))', |
344 | # bind => [ 1, 3, 2, 4 ], |
345 | # test => 'Top level -in with list args', |
346 | # }, |
ddd6fbb6 |
347 | { |
348 | where => { -between => [42, 69] }, |
b798961f |
349 | throws => qr/Fatal: Operator 'BETWEEN' requires/, |
8d6d3ec0 |
350 | test => 'Top level -between with broken args', |
351 | }, |
352 | { |
353 | where => { |
354 | -between => [ |
355 | { -op => [ '+', { -ident => 'foo' }, 2 ] }, |
356 | 3, 4 |
357 | ], |
358 | }, |
359 | stmt => ' WHERE (foo + ? BETWEEN ? AND ?)', |
360 | bind => [ 2, 3, 4 ], |
361 | test => 'Top level -between with useful LHS', |
ddd6fbb6 |
362 | }, |
39bf6cea |
363 | { |
364 | where => { |
365 | -in => [ |
c2065120 |
366 | { -row => [ 'x', 'y' ] }, |
367 | { -row => [ 1, 2 ] }, |
368 | { -row => [ 3, 4 ] }, |
39bf6cea |
369 | ], |
370 | }, |
371 | stmt => ' WHERE (x, y) IN ((?, ?), (?, ?))', |
372 | bind => [ 1..4 ], |
1279622f |
373 | test => 'Complex top-level -in', |
374 | }, |
375 | { |
376 | where => { -is => [ 'bob', undef ] }, |
377 | stmt => ' WHERE bob IS NULL', |
378 | bind => [], |
379 | test => 'Top level -is ok', |
39bf6cea |
380 | }, |
4d865061 |
381 | { |
382 | where => { -op => [ in => x => 1, 2, 3 ] }, |
383 | stmt => ' WHERE x IN (?, ?, ?)', |
384 | bind => [ 1, 2, 3 ], |
385 | test => 'Raw -op passes through correctly' |
386 | }, |
387 | |
cf02fc47 |
388 | ); |
389 | |
cf02fc47 |
390 | for my $case (@in_between_tests) { |
391 | TODO: { |
392 | local $TODO = $case->{todo} if $case->{todo}; |
4a1f01a3 |
393 | local $SQL::Abstract::Test::parenthesis_significant = $case->{parenthesis_significant}; |
df7b1db3 |
394 | my $label = $case->{test} || 'in-between test'; |
cf02fc47 |
395 | |
ca4f826a |
396 | my $sql = SQL::Abstract->new($case->{args} || {}); |
4d8b3dc4 |
397 | |
97084113 |
398 | if (my $e = $case->{throws}) { |
db8e4588 |
399 | my $stmt; |
400 | throws_ok { ($stmt) = $sql->where($case->{where}) } $e, "$label throws correctly" |
401 | or diag dumper ({ where => $case->{where}, result => $stmt }); |
032dfe20 |
402 | } |
403 | else { |
97084113 |
404 | my ($stmt, @bind); |
8d6d3ec0 |
405 | lives_ok { |
406 | warnings_are { |
407 | ($stmt, @bind) = $sql->where($case->{where}); |
408 | } [], "$label gives no warnings"; |
409 | |
410 | is_same_sql_bind( |
411 | $stmt, |
412 | \@bind, |
413 | $case->{stmt}, |
414 | $case->{bind}, |
415 | "$label generates correct SQL and bind", |
416 | ) || diag dumper ({ where => $case->{where}, exp => $sql->_expand_expr($case->{where}) }); |
417 | } || diag dumper ({ where => $case->{where}, exp => $sql->_expand_expr($case->{where}) }); |
032dfe20 |
418 | } |
cf02fc47 |
419 | } |
420 | } |
10e6c946 |
421 | |
422 | done_testing; |