make _table use expand/render code
[scpubgit/Q-Branch.git] / t / 05in_between.t
CommitLineData
cf02fc47 1use strict;
2use warnings;
3use Test::More;
97084113 4use Test::Warn;
cf02fc47 5use Test::Exception;
db8e4588 6use SQL::Abstract::Test import => [qw(is_same_sql_bind diag_where dumper)];
cf02fc47 7
cf02fc47 8use SQL::Abstract;
9
cf02fc47 10my @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 (
b3b79607 96 ( start0 BETWEEN ? AND UPPER ? )
e41c3bdd 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 },
5e5cbf51 104 {
105 args => { bindtype => 'columns' },
106 where => {
107 start0 => { -between => [ 1, { -upper => 2 } ] },
108 start1 => { -between => \["? AND ?", [ start1 => 1], [start1 => 2] ] },
109 start2 => { -between => \"lower(x) AND upper(y)" },
110 start3 => { -between => [
111 \"lower(x)",
112 \["upper(?)", [ start3 => 'stuff'] ],
113 ] },
114 },
115 stmt => "WHERE (
116 ( start0 BETWEEN ? AND UPPER ? )
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 => [
122 [ start0 => 1 ],
123 [ start0 => 2 ],
124 [ start1 => 1 ],
125 [ start1 => 2 ],
126 [ start3 => 'stuff' ],
127 ],
128 test => '-between POD test',
129 },
904c3621 130 {
131 where => { 'test1.a' => { 'In', ['boom', 'bang'] } },
132 stmt => ' WHERE ( test1.a IN ( ?, ? ) )',
133 bind => ['boom', 'bang'],
134 test => 'In (no dash, initial cap) with qualified column',
135 },
136 {
137 where => { a => { 'between', ['boom', 'bang'] } },
138 stmt => ' WHERE ( a BETWEEN ? AND ? )',
139 bind => ['boom', 'bang'],
140 test => 'between (no dash) with two placeholders',
141 },
4a1f01a3 142
143 {
4a1f01a3 144 where => { x => { -in => [ 1 .. 3] } },
7d273452 145 stmt => "WHERE x IN (?, ?, ?)",
146 bind => [ 1 .. 3 ],
4a1f01a3 147 test => '-in with an array of scalars',
148 },
149 {
e41c3bdd 150 where => { x => { -in => [] } },
7d273452 151 stmt => "WHERE 0=1",
e41c3bdd 152 bind => [],
153 test => '-in with an empty array',
154 },
155 {
4a1f01a3 156 where => { x => { -in => \'( 1,2,lower(y) )' } },
7d273452 157 stmt => "WHERE x IN ( 1,2,lower(y) )",
4a1f01a3 158 bind => [],
159 test => '-in with a literal scalarref',
160 },
46dc2f3e 161
162 # note that outer parens are opened even though literal was requested below
4a1f01a3 163 {
4a1f01a3 164 where => { x => { -in => \['( ( ?,?,lower(y) ) )', 1, 2] } },
7d273452 165 stmt => "WHERE x IN ( ?,?,lower(y) )",
4a1f01a3 166 bind => [1, 2],
167 test => '-in with a literal arrayrefref',
168 },
e41c3bdd 169 {
e41c3bdd 170 where => {
171a709f 171 status => { -in => \"(SELECT status_codes\nFROM states)" },
172 },
7d273452 173 stmt => " WHERE status IN ( SELECT status_codes FROM states )",
171a709f 174 bind => [],
175 test => '-in multi-line subquery test',
176 },
a5f91feb 177
178 # check that the outer paren opener is not too agressive
179 # note: this syntax *is not legal* on SQLite (maybe others)
180 # see end of https://rt.cpan.org/Ticket/Display.html?id=99503
181 {
182 where => { foo => { -in => \ '(SELECT 1) UNION (SELECT 2)' } },
183 stmt => 'WHERE foo IN ( (SELECT 1) UNION (SELECT 2) )',
184 bind => [],
185 test => '-in paren-opening works on balanced pairs only',
186 },
187
171a709f 188 {
171a709f 189 where => {
e41c3bdd 190 customer => { -in => \[
191 'SELECT cust_id FROM cust WHERE balance > ?',
192 2000,
193 ]},
194 status => { -in => \'SELECT status_codes FROM states' },
195 },
196 stmt => "
7d273452 197 WHERE
e41c3bdd 198 customer IN ( SELECT cust_id FROM cust WHERE balance > ? )
199 AND status IN ( SELECT status_codes FROM states )
e41c3bdd 200 ",
201 bind => [2000],
202 test => '-in POD test',
203 },
46dc2f3e 204
0336eddb 205 {
206 where => { x => { -in => [ \['LOWER(?)', 'A' ], \'LOWER(b)', { -lower => 'c' } ] } },
207 stmt => " WHERE ( x IN ( LOWER(?), LOWER(b), LOWER ? ) )",
208 bind => [qw/A c/],
209 test => '-in with an array of function array refs with args',
210 },
279eb282 211 {
97084113 212 throws => qr/
032dfe20 213 \QSQL::Abstract before v1.75 used to generate incorrect SQL \E
214 \Qwhen the -IN operator was given an undef-containing list: \E
215 \Q!!!AUDIT YOUR CODE AND DATA!!! (the upcoming Data::Query-based \E
216 \Qversion of SQL::Abstract will emit the logically correct SQL \E
217 \Qinstead of raising this exception)\E
218 /x,
279eb282 219 where => { x => { -in => [ 1, undef ] } },
032dfe20 220 stmt => " WHERE ( x IN ( ? ) OR x IS NULL )",
279eb282 221 bind => [ 1 ],
428975b0 222 test => '-in with undef as an element',
279eb282 223 },
224 {
97084113 225 throws => qr/
032dfe20 226 \QSQL::Abstract before v1.75 used to generate incorrect SQL \E
227 \Qwhen the -IN operator was given an undef-containing list: \E
228 \Q!!!AUDIT YOUR CODE AND DATA!!! (the upcoming Data::Query-based \E
229 \Qversion of SQL::Abstract will emit the logically correct SQL \E
230 \Qinstead of raising this exception)\E
231 /x,
279eb282 232 where => { x => { -in => [ 1, undef, 2, 3, undef ] } },
032dfe20 233 stmt => " WHERE ( x IN ( ?, ?, ? ) OR x IS NULL )",
279eb282 234 bind => [ 1, 2, 3 ],
032dfe20 235 test => '-in with multiple undef elements',
279eb282 236 },
904c3621 237 {
238 where => { a => { -in => 42 }, b => { -not_in => 42 } },
7d273452 239 stmt => ' WHERE a IN ( ? ) AND b NOT IN ( ? )',
904c3621 240 bind => [ 42, 42 ],
241 test => '-in, -not_in with scalar',
242 },
243 {
244 where => { a => { -in => [] }, b => { -not_in => [] } },
245 stmt => ' WHERE ( 0=1 AND 1=1 )',
246 bind => [],
247 test => '-in, -not_in with empty arrays',
248 },
249 {
250 throws => qr/
251 \QSQL::Abstract before v1.75 used to generate incorrect SQL \E
252 \Qwhen the -IN operator was given an undef-containing list: \E
253 \Q!!!AUDIT YOUR CODE AND DATA!!! (the upcoming Data::Query-based \E
254 \Qversion of SQL::Abstract will emit the logically correct SQL \E
255 \Qinstead of raising this exception)\E
256 /x,
257 where => { a => { -in => [42, undef] }, b => { -not_in => [42, undef] } },
258 stmt => ' WHERE ( ( a IN ( ? ) OR a IS NULL ) AND b NOT IN ( ? ) AND b IS NOT NULL )',
259 bind => [ 42, 42 ],
260 test => '-in, -not_in with undef among elements',
261 },
262 {
263 throws => qr/
264 \QSQL::Abstract before v1.75 used to generate incorrect SQL \E
265 \Qwhen the -IN operator was given an undef-containing list: \E
266 \Q!!!AUDIT YOUR CODE AND DATA!!! (the upcoming Data::Query-based \E
267 \Qversion of SQL::Abstract will emit the logically correct SQL \E
268 \Qinstead of raising this exception)\E
269 /x,
270 where => { a => { -in => [undef] }, b => { -not_in => [undef] } },
271 stmt => ' WHERE ( a IS NULL AND b IS NOT NULL )',
272 bind => [],
273 test => '-in, -not_in with just undef element',
274 },
275 {
276 where => { a => { -in => undef } },
277 throws => qr/Argument passed to the 'IN' operator can not be undefined/,
278 test => '-in with undef argument',
279 },
ddd6fbb6 280
281 {
282 where => { -in => [42] },
283 throws => qr/Illegal use of top-level '-in'/,
284 test => 'Top level -in',
285 },
286 {
287 where => { -between => [42, 69] },
288 throws => qr/Illegal use of top-level '-between'/,
289 test => 'Top level -between',
290 },
cf02fc47 291);
292
cf02fc47 293for my $case (@in_between_tests) {
294 TODO: {
295 local $TODO = $case->{todo} if $case->{todo};
4a1f01a3 296 local $SQL::Abstract::Test::parenthesis_significant = $case->{parenthesis_significant};
df7b1db3 297 my $label = $case->{test} || 'in-between test';
cf02fc47 298
ca4f826a 299 my $sql = SQL::Abstract->new($case->{args} || {});
4d8b3dc4 300
97084113 301 if (my $e = $case->{throws}) {
db8e4588 302 my $stmt;
303 throws_ok { ($stmt) = $sql->where($case->{where}) } $e, "$label throws correctly"
304 or diag dumper ({ where => $case->{where}, result => $stmt });
032dfe20 305 }
306 else {
97084113 307 my ($stmt, @bind);
308 warnings_are {
309 ($stmt, @bind) = $sql->where($case->{where});
df7b1db3 310 } [], "$label gives no warnings";
97084113 311
2fadf08e 312 is_same_sql_bind(
313 $stmt,
314 \@bind,
315 $case->{stmt},
316 $case->{bind},
df7b1db3 317 "$label generates correct SQL and bind",
e817ab40 318 ) || diag dumper ({ where => $case->{where}, exp => $sql->_expand_expr($case->{where}) });
032dfe20 319 }
cf02fc47 320 }
321}
10e6c946 322
323done_testing;