Commit | Line | Data |
cf02fc47 |
1 | #!/usr/bin/perl |
2 | |
3 | use strict; |
4 | use warnings; |
5 | use Test::More; |
6 | use Test::Exception; |
2fadf08e |
7 | use SQL::Abstract::Test import => [qw(is_same_sql_bind diag_where)]; |
cf02fc47 |
8 | |
cf02fc47 |
9 | use SQL::Abstract; |
10 | |
cf02fc47 |
11 | my @in_between_tests = ( |
12 | { |
13 | where => { x => { -between => [1, 2] } }, |
14 | stmt => 'WHERE (x BETWEEN ? AND ?)', |
15 | bind => [qw/1 2/], |
16 | test => '-between with two placeholders', |
17 | }, |
18 | { |
19 | where => { x => { -between => [\"1", 2] } }, |
20 | stmt => 'WHERE (x BETWEEN 1 AND ?)', |
21 | bind => [qw/2/], |
22 | test => '-between with one literal sql arg and one placeholder', |
23 | }, |
24 | { |
25 | where => { x => { -between => [1, \"2"] } }, |
26 | stmt => 'WHERE (x BETWEEN ? AND 2)', |
27 | bind => [qw/1/], |
28 | test => '-between with one placeholder and one literal sql arg', |
29 | }, |
30 | { |
31 | where => { x => { -between => [\'current_date - 1', \'current_date - 0'] } }, |
32 | stmt => 'WHERE (x BETWEEN current_date - 1 AND current_date - 0)', |
33 | bind => [], |
34 | test => '-between with two literal sql arguments', |
35 | }, |
36 | { |
4d8b3dc4 |
37 | where => { x => { -between => [ \['current_date - ?', 1], \['current_date - ?', 0] ] } }, |
38 | stmt => 'WHERE (x BETWEEN current_date - ? AND current_date - ?)', |
39 | bind => [1, 0], |
40 | test => '-between with two literal sql arguments with bind', |
41 | }, |
42 | { |
cf02fc47 |
43 | where => { x => { -between => \['? AND ?', 1, 2] } }, |
44 | stmt => 'WHERE (x BETWEEN ? AND ?)', |
45 | bind => [1,2], |
46 | test => '-between with literal sql with placeholders (\["? AND ?", scalar, scalar])', |
47 | }, |
48 | { |
49 | where => { x => { -between => \["'something' AND ?", 2] } }, |
50 | stmt => "WHERE (x BETWEEN 'something' AND ?)", |
51 | bind => [2], |
52 | test => '-between with literal sql with one literal arg and one placeholder (\["\'something\' AND ?", scalar])', |
53 | }, |
54 | { |
55 | where => { x => { -between => \["? AND 'something'", 1] } }, |
56 | stmt => "WHERE (x BETWEEN ? AND 'something')", |
57 | bind => [1], |
58 | test => '-between with literal sql with one placeholder and one literal arg (\["? AND \'something\'", scalar])', |
59 | }, |
60 | { |
4d8b3dc4 |
61 | where => { x => { -between => \"'this' AND 'that'" } }, |
cf02fc47 |
62 | stmt => "WHERE (x BETWEEN 'this' AND 'that')", |
63 | bind => [], |
4d8b3dc4 |
64 | test => '-between with literal sql with a literal (\"\'this\' AND \'that\'")', |
cf02fc47 |
65 | }, |
7f54040f |
66 | |
67 | # generate a set of invalid -between tests |
68 | ( map { { |
69 | where => { x => { -between => $_ } }, |
70 | test => 'invalid -between args', |
71 | exception => qr|Operator 'BETWEEN' requires either an arrayref with two defined values or expressions, or a single literal scalarref/arrayref-ref|, |
72 | } } ( |
73 | [ 1, 2, 3 ], |
74 | [ 1, undef, 3 ], |
75 | [ undef, 2, 3 ], |
76 | [ 1, 2, undef ], |
77 | [ 1, undef ], |
78 | [ undef, 2 ], |
79 | [ undef, undef ], |
80 | [ 1 ], |
81 | [ undef ], |
82 | [], |
83 | 1, |
84 | undef, |
85 | )), |
e41c3bdd |
86 | { |
87 | where => { |
0336eddb |
88 | start0 => { -between => [ 1, { -upper => 2 } ] }, |
e41c3bdd |
89 | start1 => { -between => \["? AND ?", 1, 2] }, |
90 | start2 => { -between => \"lower(x) AND upper(y)" }, |
91 | start3 => { -between => [ |
92 | \"lower(x)", |
93 | \["upper(?)", 'stuff' ], |
94 | ] }, |
95 | }, |
96 | stmt => "WHERE ( |
b3b79607 |
97 | ( start0 BETWEEN ? AND UPPER ? ) |
e41c3bdd |
98 | AND ( start1 BETWEEN ? AND ? ) |
99 | AND ( start2 BETWEEN lower(x) AND upper(y) ) |
100 | AND ( start3 BETWEEN lower(x) AND upper(?) ) |
101 | )", |
102 | bind => [1, 2, 1, 2, 'stuff'], |
103 | test => '-between POD test', |
104 | }, |
5e5cbf51 |
105 | { |
106 | args => { bindtype => 'columns' }, |
107 | where => { |
108 | start0 => { -between => [ 1, { -upper => 2 } ] }, |
109 | start1 => { -between => \["? AND ?", [ start1 => 1], [start1 => 2] ] }, |
110 | start2 => { -between => \"lower(x) AND upper(y)" }, |
111 | start3 => { -between => [ |
112 | \"lower(x)", |
113 | \["upper(?)", [ start3 => 'stuff'] ], |
114 | ] }, |
115 | }, |
116 | stmt => "WHERE ( |
117 | ( start0 BETWEEN ? AND UPPER ? ) |
118 | AND ( start1 BETWEEN ? AND ? ) |
119 | AND ( start2 BETWEEN lower(x) AND upper(y) ) |
120 | AND ( start3 BETWEEN lower(x) AND upper(?) ) |
121 | )", |
122 | bind => [ |
123 | [ start0 => 1 ], |
124 | [ start0 => 2 ], |
125 | [ start1 => 1 ], |
126 | [ start1 => 2 ], |
127 | [ start3 => 'stuff' ], |
128 | ], |
129 | test => '-between POD test', |
130 | }, |
4a1f01a3 |
131 | |
132 | { |
133 | parenthesis_significant => 1, |
134 | where => { x => { -in => [ 1 .. 3] } }, |
135 | stmt => "WHERE ( x IN (?, ?, ?) )", |
136 | bind => [ 1 .. 3], |
137 | test => '-in with an array of scalars', |
138 | }, |
139 | { |
140 | parenthesis_significant => 1, |
e41c3bdd |
141 | where => { x => { -in => [] } }, |
142 | stmt => "WHERE ( 0=1 )", |
143 | bind => [], |
144 | test => '-in with an empty array', |
145 | }, |
146 | { |
147 | parenthesis_significant => 1, |
4a1f01a3 |
148 | where => { x => { -in => \'( 1,2,lower(y) )' } }, |
b9a4fdae |
149 | stmt => "WHERE ( x IN ( 1,2,lower(y) ) )", |
4a1f01a3 |
150 | bind => [], |
151 | test => '-in with a literal scalarref', |
152 | }, |
153 | { |
154 | parenthesis_significant => 1, |
155 | where => { x => { -in => \['( ( ?,?,lower(y) ) )', 1, 2] } }, |
b9a4fdae |
156 | stmt => "WHERE ( x IN ( ?,?,lower(y) ) )", # note that outer parens are opened even though literal was requested (RIBASUSHI) |
4a1f01a3 |
157 | bind => [1, 2], |
158 | test => '-in with a literal arrayrefref', |
159 | }, |
e41c3bdd |
160 | { |
161 | parenthesis_significant => 1, |
162 | where => { |
171a709f |
163 | status => { -in => \"(SELECT status_codes\nFROM states)" }, |
164 | }, |
165 | # failed to open outer parens on a multi-line query in 1.61 (semifor) |
166 | stmt => " WHERE ( status IN ( SELECT status_codes FROM states )) ", |
167 | bind => [], |
168 | test => '-in multi-line subquery test', |
169 | }, |
170 | { |
171 | parenthesis_significant => 1, |
172 | where => { |
e41c3bdd |
173 | customer => { -in => \[ |
174 | 'SELECT cust_id FROM cust WHERE balance > ?', |
175 | 2000, |
176 | ]}, |
177 | status => { -in => \'SELECT status_codes FROM states' }, |
178 | }, |
179 | stmt => " |
180 | WHERE (( |
181 | customer IN ( SELECT cust_id FROM cust WHERE balance > ? ) |
182 | AND status IN ( SELECT status_codes FROM states ) |
183 | )) |
184 | ", |
185 | bind => [2000], |
186 | test => '-in POD test', |
187 | }, |
0336eddb |
188 | { |
189 | where => { x => { -in => [ \['LOWER(?)', 'A' ], \'LOWER(b)', { -lower => 'c' } ] } }, |
190 | stmt => " WHERE ( x IN ( LOWER(?), LOWER(b), LOWER ? ) )", |
191 | bind => [qw/A c/], |
192 | test => '-in with an array of function array refs with args', |
193 | }, |
279eb282 |
194 | { |
032dfe20 |
195 | exception => qr/ |
196 | \QSQL::Abstract before v1.75 used to generate incorrect SQL \E |
197 | \Qwhen the -IN operator was given an undef-containing list: \E |
198 | \Q!!!AUDIT YOUR CODE AND DATA!!! (the upcoming Data::Query-based \E |
199 | \Qversion of SQL::Abstract will emit the logically correct SQL \E |
200 | \Qinstead of raising this exception)\E |
201 | /x, |
279eb282 |
202 | where => { x => { -in => [ 1, undef ] } }, |
032dfe20 |
203 | stmt => " WHERE ( x IN ( ? ) OR x IS NULL )", |
279eb282 |
204 | bind => [ 1 ], |
428975b0 |
205 | test => '-in with undef as an element', |
279eb282 |
206 | }, |
207 | { |
032dfe20 |
208 | exception => qr/ |
209 | \QSQL::Abstract before v1.75 used to generate incorrect SQL \E |
210 | \Qwhen the -IN operator was given an undef-containing list: \E |
211 | \Q!!!AUDIT YOUR CODE AND DATA!!! (the upcoming Data::Query-based \E |
212 | \Qversion of SQL::Abstract will emit the logically correct SQL \E |
213 | \Qinstead of raising this exception)\E |
214 | /x, |
279eb282 |
215 | where => { x => { -in => [ 1, undef, 2, 3, undef ] } }, |
032dfe20 |
216 | stmt => " WHERE ( x IN ( ?, ?, ? ) OR x IS NULL )", |
279eb282 |
217 | bind => [ 1, 2, 3 ], |
032dfe20 |
218 | test => '-in with multiple undef elements', |
279eb282 |
219 | }, |
cf02fc47 |
220 | ); |
221 | |
cf02fc47 |
222 | for my $case (@in_between_tests) { |
223 | TODO: { |
224 | local $TODO = $case->{todo} if $case->{todo}; |
4a1f01a3 |
225 | local $SQL::Abstract::Test::parenthesis_significant = $case->{parenthesis_significant}; |
cf02fc47 |
226 | |
cf02fc47 |
227 | |
032dfe20 |
228 | my @w; |
229 | local $SIG{__WARN__} = sub { push @w, @_ }; |
2fadf08e |
230 | |
032dfe20 |
231 | my $sql = SQL::Abstract->new ($case->{args} || {}); |
4d8b3dc4 |
232 | |
032dfe20 |
233 | if ($case->{exception}) { |
234 | throws_ok { $sql->where($case->{where}) } $case->{exception}; |
235 | } |
236 | else { |
2fadf08e |
237 | my ($stmt, @bind) = $sql->where($case->{where}); |
238 | is_same_sql_bind( |
239 | $stmt, |
240 | \@bind, |
241 | $case->{stmt}, |
242 | $case->{bind}, |
243 | ) || diag_where ( $case->{where} ); |
032dfe20 |
244 | } |
245 | |
246 | is (@w, 0, $case->{test} || 'No warnings within in-between tests') |
247 | || diag join "\n", 'Emitted warnings:', @w; |
cf02fc47 |
248 | } |
249 | } |
10e6c946 |
250 | |
251 | done_testing; |