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