single fire -nest warning because DBIC expects it, fix an order_by bug
[scpubgit/Q-Branch.git] / t / 02where.t
1 use strict;
2 use warnings;
3 use Test::More;
4 use Test::Warn;
5 use Test::Exception;
6 use SQL::Abstract::Test import => [qw(is_same_sql_bind diag_where dumper) ];
7
8 use SQL::Abstract;
9
10 my $not_stringifiable = bless {}, 'SQLA::NotStringifiable';
11
12 my @handle_tests = (
13     {
14         where => 'foo',
15         order => [],
16         stmt => ' WHERE foo',
17         bind => [],
18     },
19     {
20         where => {
21             requestor => 'inna',
22             worker => ['nwiger', 'rcwe', 'sfz'],
23             status => { '!=', 'completed' }
24         },
25         order => [],
26         stmt => " WHERE ( requestor = ? AND status != ? AND ( ( worker = ? ) OR"
27               . " ( worker = ? ) OR ( worker = ? ) ) )",
28         bind => [qw/inna completed nwiger rcwe sfz/],
29     },
30
31     {
32         where  => [
33             status => 'completed',
34             user   => 'nwiger',
35         ],
36         stmt => " WHERE ( status = ? OR user = ? )",
37         bind => [qw/completed nwiger/],
38     },
39
40     {
41         where  => {
42             user   => 'nwiger',
43             status => 'completed'
44         },
45         order => [qw/ticket/],
46         stmt => " WHERE ( status = ? AND user = ? ) ORDER BY ticket",
47         bind => [qw/completed nwiger/],
48     },
49
50     {
51         where  => {
52             user   => 'nwiger',
53             status => { '!=', 'completed' }
54         },
55         order => [qw/ticket/],
56         stmt => " WHERE ( status != ? AND user = ? ) ORDER BY ticket",
57         bind => [qw/completed nwiger/],
58     },
59
60     {
61         where  => {
62             status   => 'completed',
63             reportid => { 'in', [567, 2335, 2] }
64         },
65         order => [],
66         stmt => " WHERE ( reportid IN ( ?, ?, ? ) AND status = ? )",
67         bind => [qw/567 2335 2 completed/],
68     },
69
70     {
71         where  => {
72             status   => 'completed',
73             reportid => { 'not in', [567, 2335, 2] }
74         },
75         order => [],
76         stmt => " WHERE ( reportid NOT IN ( ?, ?, ? ) AND status = ? )",
77         bind => [qw/567 2335 2 completed/],
78     },
79
80     {
81         where  => {
82             status   => 'completed',
83             completion_date => { 'between', ['2002-10-01', '2003-02-06'] },
84         },
85         order => \'ticket, requestor',
86         stmt => "WHERE ( ( completion_date BETWEEN ? AND ? ) AND status = ? ) ORDER BY ticket, requestor",
87         bind => [qw/2002-10-01 2003-02-06 completed/],
88     },
89
90     {
91         where => [
92             {
93                 user   => 'nwiger',
94                 status => { 'in', ['pending', 'dispatched'] },
95             },
96             {
97                 user   => 'robot',
98                 status => 'unassigned',
99             },
100         ],
101         order => [],
102         stmt => " WHERE ( ( status IN ( ?, ? ) AND user = ? ) OR ( status = ? AND user = ? ) )",
103         bind => [qw/pending dispatched nwiger unassigned robot/],
104     },
105
106     {
107         where => {
108             priority  => [ {'>', 3}, {'<', 1} ],
109             requestor => \'is not null',
110         },
111         order => 'priority',
112         stmt => " WHERE ( ( ( priority > ? ) OR ( priority < ? ) ) AND requestor is not null ) ORDER BY priority",
113         bind => [qw/3 1/],
114     },
115
116     {
117         where => {
118             requestor => { '!=', ['-and', undef, ''] },
119         },
120         stmt => " WHERE ( requestor IS NOT NULL AND requestor != ? )",
121         bind => [''],
122     },
123
124     {
125         where => {
126             priority  => [ {'>', 3}, {'<', 1} ],
127             requestor => { '!=', undef },
128         },
129         order => [qw/a b c d e f g/],
130         stmt => " WHERE ( ( ( priority > ? ) OR ( priority < ? ) ) AND requestor IS NOT NULL )"
131               . " ORDER BY a, b, c, d, e, f, g",
132         bind => [qw/3 1/],
133     },
134
135     {
136         where => {
137             priority  => { 'between', [1, 3] },
138             requestor => { 'like', undef },
139         },
140         order => \'requestor, ticket',
141         stmt => " WHERE ( ( priority BETWEEN ? AND ? ) AND requestor IS NULL ) ORDER BY requestor, ticket",
142         bind => [qw/1 3/],
143         warns => qr/Supplying an undefined argument to 'LIKE' is deprecated/,
144     },
145
146
147     {
148         where => {
149           id  => 1,
150           num => {
151            '<=' => 20,
152            '>'  => 10,
153           },
154         },
155         stmt => " WHERE ( id = ? AND ( num <= ? AND num > ? ) )",
156         bind => [qw/1 20 10/],
157     },
158
159     {
160         where => { foo => {-not_like => [7,8,9]},
161                    fum => {'like' => [qw/a b/]},
162                    nix => {'between' => [100,200] },
163                    nox => {'not between' => [150,160] },
164                    wix => {'in' => [qw/zz yy/]},
165                    wux => {'not_in'  => [qw/30 40/]}
166                  },
167         stmt => " WHERE ( ( ( foo NOT LIKE ? ) OR ( foo NOT LIKE ? ) OR ( foo NOT LIKE ? ) ) AND ( ( fum LIKE ? ) OR ( fum LIKE ? ) ) AND ( nix BETWEEN ? AND ? ) AND ( nox NOT BETWEEN ? AND ? ) AND wix IN ( ?, ? ) AND wux NOT IN ( ?, ? ) )",
168         bind => [7,8,9,'a','b',100,200,150,160,'zz','yy','30','40'],
169         warns => qr/\QA multi-element arrayref as an argument to the inequality op 'NOT LIKE' is technically equivalent to an always-true 1=1/,
170     },
171
172     {
173         where => {
174             bar => {'!=' => []},
175         },
176         stmt => " WHERE ( 1=1 )",
177         bind => [],
178     },
179
180     {
181         where => {
182             id  => [],
183         },
184         stmt => " WHERE ( 0=1 )",
185         bind => [],
186     },
187
188
189     {
190         where => {
191             foo => \["IN (?, ?)", 22, 33],
192             bar => [-and =>  \["> ?", 44], \["< ?", 55] ],
193         },
194         stmt => " WHERE ( (bar > ? AND bar < ?) AND foo IN (?, ?) )",
195         bind => [44, 55, 22, 33],
196     },
197
198     {
199         where => {
200           -and => [
201             user => 'nwiger',
202             [
203               -and => [ workhrs => {'>', 20}, geo => 'ASIA' ],
204               -or => { workhrs => {'<', 50}, geo => 'EURO' },
205             ],
206           ],
207         },
208         stmt => "WHERE ( user = ? AND (
209                ( workhrs > ? AND geo = ? )
210             OR ( geo = ? OR workhrs < ? )
211           ) )",
212         bind => [qw/nwiger 20 ASIA EURO 50/],
213     },
214
215    {
216        where => { -and => [{}, { 'me.id' => '1'}] },
217        stmt => " WHERE ( ( me.id = ? ) )",
218        bind => [ 1 ],
219    },
220
221    {
222        where => { foo => $not_stringifiable, },
223        stmt => " WHERE ( foo = ? )",
224        bind => [ $not_stringifiable ],
225    },
226
227    {
228        where => \[ 'foo = ?','bar' ],
229        stmt => " WHERE (foo = ?)",
230        bind => [ "bar" ],
231    },
232
233    {
234        where => [ \[ 'foo = ?','bar' ] ],
235        stmt => " WHERE (foo = ?)",
236        bind => [ "bar" ],
237    },
238
239    {
240        where => { -bool => \'function(x)' },
241        stmt => " WHERE function(x)",
242        bind => [],
243    },
244
245    {
246        where => { -bool => 'foo' },
247        stmt => " WHERE foo",
248        bind => [],
249    },
250
251    {
252        where => { -and => [-bool => 'foo', -bool => 'bar'] },
253        stmt => " WHERE foo AND bar",
254        bind => [],
255    },
256
257    {
258        where => { -or => [-bool => 'foo', -bool => 'bar'] },
259        stmt => " WHERE foo OR bar",
260        bind => [],
261    },
262
263    {
264        where => { -not_bool => \'function(x)' },
265        stmt => " WHERE NOT function(x)",
266        bind => [],
267    },
268
269    {
270        where => { -not_bool => 'foo' },
271        stmt => " WHERE NOT foo",
272        bind => [],
273    },
274
275    {
276        where => { -and => [-not_bool => 'foo', -not_bool => 'bar'] },
277        stmt => " WHERE (NOT foo) AND (NOT bar)",
278        bind => [],
279    },
280
281    {
282        where => { -or => [-not_bool => 'foo', -not_bool => 'bar'] },
283        stmt => " WHERE (NOT foo) OR (NOT bar)",
284        bind => [],
285    },
286
287    {
288        where => { -bool => \['function(?)', 20]  },
289        stmt => " WHERE function(?)",
290        bind => [20],
291    },
292
293    {
294        where => { -not_bool => \['function(?)', 20]  },
295        stmt => " WHERE NOT function(?)",
296        bind => [20],
297    },
298
299    {
300        where => { -bool => { a => 1, b => 2}  },
301        stmt => " WHERE a = ? AND b = ?",
302        bind => [1, 2],
303    },
304
305    {
306        where => { -bool => [ a => 1, b => 2] },
307        stmt => " WHERE a = ? OR b = ?",
308        bind => [1, 2],
309    },
310
311    {
312        where => { -not_bool => { a => 1, b => 2}  },
313        stmt => " WHERE NOT (a = ? AND b = ?)",
314        bind => [1, 2],
315    },
316
317    {
318        where => { -not_bool => [ a => 1, b => 2] },
319        stmt => " WHERE NOT ( a = ? OR b = ? )",
320        bind => [1, 2],
321    },
322
323 # Op against internal function
324    {
325        where => { bool1 => { '=' => { -not_bool => 'bool2' } } },
326        stmt => " WHERE ( bool1 = (NOT bool2) )",
327        bind => [],
328    },
329    {
330        where => { -not_bool => { -not_bool => { -not_bool => 'bool2' } } },
331        stmt => " WHERE ( NOT ( NOT ( NOT bool2 ) ) )",
332        bind => [],
333    },
334
335 # Op against random functions (these two are oracle-specific)
336    {
337        where => { timestamp => { '!=' => { -trunc => { -year => \'sysdate' } } } },
338        stmt => " WHERE ( timestamp != TRUNC (YEAR sysdate) )",
339        bind => [],
340    },
341    {
342        where => { timestamp => { '>=' => { -to_date => '2009-12-21 00:00:00' } } },
343        stmt => " WHERE ( timestamp >= TO_DATE ? )",
344        bind => ['2009-12-21 00:00:00'],
345    },
346
347 # Legacy function specs
348    {
349        where => { ip => {'<<=' => '127.0.0.1/32' } },
350        stmt => "WHERE ( ip <<= ? )",
351        bind => ['127.0.0.1/32'],
352    },
353    {
354        where => { foo => { 'GLOB' => '*str*' } },
355        stmt => " WHERE foo GLOB ? ",
356        bind => [ '*str*' ],
357    },
358    {
359        where => { foo => { 'REGEXP' => 'bar|baz' } },
360        stmt => " WHERE foo REGEXP ? ",
361        bind => [ 'bar|baz' ],
362    },
363
364 # Tests for -not
365 # Basic tests only
366     {
367         where => { -not => { a => 1 } },
368         stmt  => " WHERE ( (NOT a = ?) ) ",
369         bind => [ 1 ],
370     },
371     {
372         where => { a => 1, -not => { b => 2 } },
373         stmt  => " WHERE ( ( (NOT b = ?) AND a = ? ) ) ",
374         bind => [ 2, 1 ],
375     },
376     {
377         where => { -not => { a => 1, b => 2, c => 3 } },
378         stmt  => " WHERE ( (NOT ( a = ? AND b = ? AND c = ? )) ) ",
379         bind => [ 1, 2, 3 ],
380     },
381     {
382         where => { -not => [ a => 1, b => 2, c => 3 ] },
383         stmt  => " WHERE ( (NOT ( a = ? OR b = ? OR c = ? )) ) ",
384         bind => [ 1, 2, 3 ],
385     },
386     {
387         where => { -not => { c => 3, -not => { b => 2, -not => { a => 1 } } } },
388         stmt  => " WHERE ( (NOT ( (NOT ( (NOT a = ?) AND b = ? )) AND c = ? )) ) ",
389         bind => [ 1, 2, 3 ],
390     },
391     {
392         where => { -not => { -bool => 'c', -not => { -not_bool => 'b', -not => { a => 1 } } } },
393         stmt  => " WHERE ( (NOT ( c AND (NOT ( (NOT a = ?) AND (NOT b) )) )) ) ",
394         bind => [ 1 ],
395     },
396     {
397         where => \"0",
398         stmt  => " WHERE ( 0 ) ",
399         bind => [ ],
400     },
401 );
402
403 for my $case (@handle_tests) {
404     my $sql = SQL::Abstract->new;
405     my ($stmt, @bind);
406     lives_ok {
407       warnings_exist {
408         ($stmt, @bind) = $sql->where($case->{where}, $case->{order});
409       } $case->{warns} || [];
410     };
411
412     is_same_sql_bind($stmt, \@bind, $case->{stmt}, $case->{bind})
413       || do { diag_where ( $case->{where} ); diag dumper($sql->_expand_expr($case->{where})) };
414 }
415
416 done_testing;