projects / dbsrgits/SQL-Abstract.git / blame
? search:
summary | shortlog | log | commit | commitdiff | tree
blob | history | HEAD
Add non-equality tests for ordered lists
[dbsrgits/SQL-Abstract.git] / t / 10test.t
CommitLineData
32c34379 1#!/usr/bin/perl
2
3use strict;
4use warnings;
5use List::Util qw(sum);
32c34379 6
7use Test::More;
8
7823ae89 9# equivalent to $Module::Install::AUTHOR
10my $author = (
11 ( not -d './inc' )
12 or
13 ( -e ($^O eq 'VMS' ? './inc/_author' : './inc/.author') )
14);
15
16if (not $author and not $ENV{SQLATEST_TESTER} and not $ENV{AUTOMATED_TESTING}) {
17 plan skip_all => 'Skipping resource intensive self-tests, use SQLATEST_TESTER=1 to run';
18}
19
32c34379 20
25823711 21my @sql_tests = (
22 # WHERE condition - equal
23 {
24 equal => 1,
25 statements => [
26 q/SELECT foo FROM bar WHERE a = 1/,
27 q/SELECT foo FROM bar WHERE a=1/,
28 q/SELECT foo FROM bar WHERE (a = 1)/,
29 q/SELECT foo FROM bar WHERE (a=1)/,
30 q/SELECT foo FROM bar WHERE ( a = 1 )/,
31 q/
32 SELECT
33 foo
34 FROM
35 bar
36 WHERE
37 a = 1
38 /,
39 q/
40 SELECT
41 foo
42 FROM
43 bar
44 WHERE
45 (a = 1)
46 /,
47 q/
48 SELECT
49 foo
50 FROM
51 bar
52 WHERE
53 ( a = 1 )
54 /,
55 q/SELECT foo FROM bar WHERE ((a = 1))/,
56 q/SELECT foo FROM bar WHERE ( (a = 1) )/,
57 q/SELECT foo FROM bar WHERE ( ( a = 1 ) )/,
58 ]
59 },
60 {
61 equal => 1,
62 statements => [
63 q/SELECT foo FROM bar WHERE a = 1 AND b = 1/,
64 q/SELECT foo FROM bar WHERE (a = 1) AND (b = 1)/,
65 q/SELECT foo FROM bar WHERE ((a = 1) AND (b = 1))/,
66 q/SELECT foo FROM bar WHERE (a = 1 AND b = 1)/,
67 q/SELECT foo FROM bar WHERE ((a = 1 AND b = 1))/,
68 q/SELECT foo FROM bar WHERE (((a = 1) AND (b = 1)))/,
69 q/
70 SELECT
71 foo
72 FROM
73 bar
74 WHERE
75 a = 1
76 AND
77 b = 1
78 /,
79 q/
80 SELECT
81 foo
82 FROM
83 bar
84 WHERE
85 (a = 1
86 AND
87 b = 1)
88 /,
89 q/
90 SELECT
91 foo
92 FROM
93 bar
94 WHERE
95 (a = 1)
96 AND
97 (b = 1)
98 /,
99 q/
100 SELECT
101 foo
102 FROM
103 bar
104 WHERE
105 ((a = 1)
106 AND
107 (b = 1))
108 /,
109 ]
110 },
56c0595d 111 {
112 equal => 1,
56c0595d 113 statements => [
114 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 AND c = 1/,
e40f5df9 115 q/SELECT foo FROM bar WHERE (a = 1 AND b = 1 AND c = 1)/,
56c0595d 116 q/SELECT foo FROM bar WHERE (a = 1 AND b = 1) AND c = 1/,
117 q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 AND c = 1)/,
1b17d1b0 118 q/SELECT foo FROM bar WHERE ((((a = 1))) AND (b = 1 AND c = 1))/,
56c0595d 119 ]
120 },
121 {
122 equal => 1,
56c0595d 123 statements => [
124 q/SELECT foo FROM bar WHERE a = 1 OR b = 1 OR c = 1/,
125 q/SELECT foo FROM bar WHERE (a = 1 OR b = 1) OR c = 1/,
126 q/SELECT foo FROM bar WHERE a = 1 OR (b = 1 OR c = 1)/,
1b17d1b0 127 q/SELECT foo FROM bar WHERE a = 1 OR ((b = 1 OR (c = 1)))/,
128 ]
129 },
130 {
131 equal => 1,
132 statements => [
133 q/SELECT foo FROM bar WHERE (a = 1) AND (b = 1 OR c = 1 OR d = 1) AND (e = 1 AND f = 1)/,
134 q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 OR c = 1 OR d = 1) AND e = 1 AND (f = 1)/,
135 q/SELECT foo FROM bar WHERE ( ((a = 1) AND ( b = 1 OR (c = 1 OR d = 1) )) AND ((e = 1)) AND f = 1) /,
56c0595d 136 ]
137 },
e40f5df9 138 {
139 equal => 0,
140 parenthesis_significant => 1,
141 statements => [
142 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 AND c = 1/,
143 q/SELECT foo FROM bar WHERE (a = 1 AND b = 1 AND c = 1)/,
144 q/SELECT foo FROM bar WHERE (a = 1 AND b = 1) AND c = 1/,
145 q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 AND c = 1)/,
146 q/SELECT foo FROM bar WHERE ((((a = 1))) AND (b = 1 AND c = 1))/,
147 ]
148 },
149 {
150 equal => 0,
151 parenthesis_significant => 1,
152 statements => [
153 q/SELECT foo FROM bar WHERE a = 1 OR b = 1 OR c = 1/,
154 q/SELECT foo FROM bar WHERE (a = 1 OR b = 1) OR c = 1/,
155 q/SELECT foo FROM bar WHERE a = 1 OR (b = 1 OR c = 1)/,
156 q/SELECT foo FROM bar WHERE a = 1 OR ((b = 1 OR (c = 1)))/,
157 ]
158 },
159 {
160 equal => 0,
161 parenthesis_significant => 1,
162 statements => [
163 q/SELECT foo FROM bar WHERE (a = 1) AND (b = 1 OR c = 1 OR d = 1) AND (e = 1 AND f = 1)/,
164 q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 OR c = 1 OR d = 1) AND e = 1 AND (f = 1)/,
165 q/SELECT foo FROM bar WHERE ( ((a = 1) AND ( b = 1 OR (c = 1 OR d = 1) )) AND ((e = 1)) AND f = 1) /,
166 ]
167 },
25823711 168
169 # WHERE condition - different
170 {
171 equal => 0,
172 statements => [
173 q/SELECT foo FROM bar WHERE a = 1/,
174 q/SELECT quux FROM bar WHERE a = 1/,
175 q/SELECT foo FROM quux WHERE a = 1/,
176 q/FOOBAR foo FROM bar WHERE a = 1/,
177
178 q/SELECT foo FROM bar WHERE a = 2/,
179 q/SELECT foo FROM bar WHERE a < 1/,
180 q/SELECT foo FROM bar WHERE b = 1/,
181 q/SELECT foo FROM bar WHERE (c = 1)/,
182 q/SELECT foo FROM bar WHERE (d = 1)/,
183
184 q/SELECT foo FROM bar WHERE a = 1 AND quux/,
185 q/SELECT foo FROM bar WHERE a = 1 GROUP BY foo/,
186 q/SELECT foo FROM bar WHERE a = 1 ORDER BY foo/,
187 q/SELECT foo FROM bar WHERE a = 1 LIMIT 1/,
188 q/SELECT foo FROM bar WHERE a = 1 OFFSET 1/,
189 q/SELECT foo FROM bar JOIN quux WHERE a = 1/,
190 q/SELECT foo FROM bar JOIN quux ON a = 1 WHERE a = 1/,
191 ]
192 },
193 {
194 equal => 0,
195 statements => [
196 q/SELECT foo FROM bar WHERE a = 1 AND b = 1/,
197 q/SELECT quux FROM bar WHERE a = 1 AND b = 1/,
198 q/SELECT foo FROM quux WHERE a = 1 AND b = 1/,
199 q/FOOBAR foo FROM bar WHERE a = 1 AND b = 1/,
200
201 q/SELECT foo FROM bar WHERE a = 2 AND b = 1/,
202 q/SELECT foo FROM bar WHERE a = 3 AND (b = 1)/,
203 q/SELECT foo FROM bar WHERE (a = 4) AND b = 1/,
204 q/SELECT foo FROM bar WHERE (a = 5) AND (b = 1)/,
205 q/SELECT foo FROM bar WHERE ((a = 6) AND (b = 1))/,
206 q/SELECT foo FROM bar WHERE ((a = 7) AND (b = 1))/,
207
208 q/SELECT foo FROM bar WHERE a = 1 AND b = 2/,
209 q/SELECT foo FROM bar WHERE a = 1 AND (b = 3)/,
210 q/SELECT foo FROM bar WHERE (a = 1) AND b = 4/,
211 q/SELECT foo FROM bar WHERE (a = 1) AND (b = 5)/,
212 q/SELECT foo FROM bar WHERE ((a = 1) AND (b = 6))/,
213 q/SELECT foo FROM bar WHERE ((a = 1) AND (b = 7))/,
214
215 q/SELECT foo FROM bar WHERE a < 1 AND b = 1/,
216 q/SELECT foo FROM bar WHERE b = 1 AND b = 1/,
217 q/SELECT foo FROM bar WHERE (c = 1) AND b = 1/,
218 q/SELECT foo FROM bar WHERE (d = 1) AND b = 1/,
219
220 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 AND quux/,
221 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 GROUP BY foo/,
222 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 ORDER BY foo/,
223 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 LIMIT 1/,
224 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 OFFSET 1/,
225 q/SELECT foo FROM bar JOIN quux WHERE a = 1 AND b = 1/,
226 q/SELECT foo FROM bar JOIN quux ON a = 1 WHERE a = 1 AND b = 1/,
227 ]
228 },
56c0595d 229 {
230 equal => 0,
231 statements => [
232 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 OR c = 1/,
233 q/SELECT foo FROM bar WHERE (a = 1 AND b = 1) OR c = 1/,
234 q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 OR c = 1)/,
235 ]
236 },
237 {
238 equal => 0,
239 statements => [
240 q/SELECT foo FROM bar WHERE a = 1 OR b = 1 AND c = 1/,
241 q/SELECT foo FROM bar WHERE (a = 1 OR b = 1) AND c = 1/,
242 q/SELECT foo FROM bar WHERE a = 1 OR (b = 1 AND c = 1)/,
243 ]
244 },
b6c8b01b 245 {
246 equal => 0,
247 statements => [
01b64cb7 248 # BETWEEN with/without parenthesis around itself/RHS is a sticky business
249 # if I made a mistake here, simply rewrite the special BETWEEN handling in
250 # _recurse_parse()
251 #
252 # by RIBASUSHI
b6c8b01b 253 q/SELECT foo FROM bar WHERE ( completion_date BETWEEN ? AND ? AND status = ? )/,
01b64cb7 254 q/SELECT foo FROM bar WHERE completion_date BETWEEN (? AND ?) AND status = ?/,
255 q/SELECT foo FROM bar WHERE ( (completion_date BETWEEN (? AND ?) ) AND status = ? )/,
256 q/SELECT foo FROM bar WHERE ( (completion_date BETWEEN (? AND ? AND status = ?) ) )/,
b6c8b01b 257 ]
258 },
25823711 259
260 # JOIN condition - equal
261 {
262 equal => 1,
263 statements => [
264 q/SELECT foo FROM bar JOIN baz ON a = 1 WHERE x = 1/,
265 q/SELECT foo FROM bar JOIN baz ON a=1 WHERE x = 1/,
266 q/SELECT foo FROM bar JOIN baz ON (a = 1) WHERE x = 1/,
267 q/SELECT foo FROM bar JOIN baz ON (a=1) WHERE x = 1/,
268 q/SELECT foo FROM bar JOIN baz ON ( a = 1 ) WHERE x = 1/,
269 q/
270 SELECT
271 foo
272 FROM
273 bar
274 JOIN
275 baz
276 ON
277 a = 1
278 WHERE
279 x = 1
280 /,
281 q/
282 SELECT
283 foo
284 FROM
285 bar
286 JOIN
287 baz
288 ON
289 (a = 1)
290 WHERE
291 x = 1
292 /,
293 q/
294 SELECT
295 foo
296 FROM
297 bar
298 JOIN
299 baz
300 ON
301 ( a = 1 )
302 WHERE
303 x = 1
304 /,
305 q/SELECT foo FROM bar JOIN baz ON ((a = 1)) WHERE x = 1/,
306 q/SELECT foo FROM bar JOIN baz ON ( (a = 1) ) WHERE x = 1/,
307 q/SELECT foo FROM bar JOIN baz ON ( ( a = 1 ) ) WHERE x = 1/,
308 ]
309 },
310 {
311 equal => 1,
312 statements => [
313 q/SELECT foo FROM bar JOIN baz ON a = 1 AND b = 1 WHERE x = 1/,
314 q/SELECT foo FROM bar JOIN baz ON (a = 1) AND (b = 1) WHERE x = 1/,
315 q/SELECT foo FROM bar JOIN baz ON ((a = 1) AND (b = 1)) WHERE x = 1/,
316 q/SELECT foo FROM bar JOIN baz ON (a = 1 AND b = 1) WHERE x = 1/,
317 q/SELECT foo FROM bar JOIN baz ON ((a = 1 AND b = 1)) WHERE x = 1/,
318 q/SELECT foo FROM bar JOIN baz ON (((a = 1) AND (b = 1))) WHERE x = 1/,
319 q/
320 SELECT
321 foo
322 FROM
323 bar
324 JOIN
325 baz
326 ON
327 a = 1
328 AND
329 b = 1
330 WHERE
331 x = 1
332 /,
333 q/
334 SELECT
335 foo
336 FROM
337 bar
338 JOIN
339 baz
340 ON
341 (a = 1
342 AND
343 b = 1)
344 WHERE
345 x = 1
346 /,
347 q/
348 SELECT
349 foo
350 FROM
351 bar
352 JOIN
353 baz
354 ON
355 (a = 1)
356 AND
357 (b = 1)
358 WHERE
359 x = 1
360 /,
361 q/
362 SELECT
363 foo
364 FROM
365 bar
366 JOIN
367 baz
368 ON
369 ((a = 1)
370 AND
371 (b = 1))
372 WHERE
373 x = 1
374 /,
375 ]
376 },
377
378 # JOIN condition - different
379 {
380 equal => 0,
381 statements => [
382 q/SELECT foo FROM bar JOIN quux ON a = 1 WHERE quuux/,
383 q/SELECT quux FROM bar JOIN quux ON a = 1 WHERE quuux/,
384 q/SELECT foo FROM quux JOIN quux ON a = 1 WHERE quuux/,
385 q/FOOBAR foo FROM bar JOIN quux ON a = 1 WHERE quuux/,
386
387 q/SELECT foo FROM bar JOIN quux ON a = 2 WHERE quuux/,
388 q/SELECT foo FROM bar JOIN quux ON a < 1 WHERE quuux/,
389 q/SELECT foo FROM bar JOIN quux ON b = 1 WHERE quuux/,
390 q/SELECT foo FROM bar JOIN quux ON (c = 1) WHERE quuux/,
391 q/SELECT foo FROM bar JOIN quux ON (d = 1) WHERE quuux/,
392
393 q/SELECT foo FROM bar JOIN quux ON a = 1 AND quuux/,
394 q/SELECT foo FROM bar JOIN quux ON a = 1 GROUP BY foo/,
395 q/SELECT foo FROM bar JOIN quux ON a = 1 ORDER BY foo/,
396 q/SELECT foo FROM bar JOIN quux ON a = 1 LIMIT 1/,
397 q/SELECT foo FROM bar JOIN quux ON a = 1 OFFSET 1/,
398 q/SELECT foo FROM bar JOIN quux ON a = 1 JOIN quuux/,
399 q/SELECT foo FROM bar JOIN quux ON a = 1 JOIN quuux ON a = 1/,
400 ]
401 },
402 {
403 equal => 0,
404 statements => [
405 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 WHERE quuux/,
406 q/SELECT quux FROM bar JOIN quux ON a = 1 AND b = 1 WHERE quuux/,
407 q/SELECT foo FROM quux JOIN quux ON a = 1 AND b = 1 WHERE quuux/,
408 q/FOOBAR foo FROM bar JOIN quux ON a = 1 AND b = 1 WHERE quuux/,
409
410 q/SELECT foo FROM bar JOIN quux ON a = 2 AND b = 1 WHERE quuux/,
411 q/SELECT foo FROM bar JOIN quux ON a = 3 AND (b = 1) WHERE quuux/,
412 q/SELECT foo FROM bar JOIN quux ON (a = 4) AND b = 1 WHERE quuux/,
413 q/SELECT foo FROM bar JOIN quux ON (a = 5) AND (b = 1) WHERE quuux/,
414 q/SELECT foo FROM bar JOIN quux ON ((a = 6) AND (b = 1)) WHERE quuux/,
415 q/SELECT foo FROM bar JOIN quux ON ((a = 7) AND (b = 1)) WHERE quuux/,
416
417 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 2 WHERE quuux/,
418 q/SELECT foo FROM bar JOIN quux ON a = 1 AND (b = 3) WHERE quuux/,
419 q/SELECT foo FROM bar JOIN quux ON (a = 1) AND b = 4 WHERE quuux/,
420 q/SELECT foo FROM bar JOIN quux ON (a = 1) AND (b = 5) WHERE quuux/,
421 q/SELECT foo FROM bar JOIN quux ON ((a = 1) AND (b = 6)) WHERE quuux/,
422 q/SELECT foo FROM bar JOIN quux ON ((a = 1) AND (b = 7)) WHERE quuux/,
423
424 q/SELECT foo FROM bar JOIN quux ON a < 1 AND b = 1 WHERE quuux/,
425 q/SELECT foo FROM bar JOIN quux ON b = 1 AND b = 1 WHERE quuux/,
426 q/SELECT foo FROM bar JOIN quux ON (c = 1) AND b = 1 WHERE quuux/,
427 q/SELECT foo FROM bar JOIN quux ON (d = 1) AND b = 1 WHERE quuux/,
428
429 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 AND quuux/,
430 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 GROUP BY foo/,
431 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 ORDER BY foo/,
432 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 LIMIT 1/,
433 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 OFFSET 1/,
434 q/SELECT foo FROM bar JOIN quux JOIN quuux ON a = 1 AND b = 1/,
435 q/SELECT foo FROM bar JOIN quux ON a = 1 JOIN quuux ON a = 1 AND b = 1/,
436 ]
437 },
438
439 # DISTINCT ON (...) not confused with JOIN ON (...)
440 {
441 equal => 1,
442 statements => [
443 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE a = 1/,
444 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE a=1/,
445 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE (a = 1)/,
446 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE (a=1)/,
447 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ( a = 1 )/,
448 q/
449 SELECT DISTINCT ON (foo, quux)
450 foo,
451 quux
452 FROM
453 bar
454 WHERE
455 a = 1
456 /,
457 q/
458 SELECT DISTINCT ON (foo, quux)
459 foo,
460 quux
461 FROM
462 bar
463 WHERE
464 (a = 1)
465 /,
466 q/
467 SELECT DISTINCT ON (foo, quux)
468 foo,
469 quux
470 FROM
471 bar
472 WHERE
473 ( a = 1 )
474 /,
475 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ((a = 1))/,
476 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ( (a = 1) )/,
477 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ( ( a = 1 ) )/,
478 ]
479 },
480
481 # subselects - equal
482 {
483 equal => 1,
484 statements => [
485 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE a = 1/,
486 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE (a = 1)/,
487 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE a = 1/,
488 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE (a = 1)/,
489 ]
490 },
491 {
492 equal => 1,
493 statements => [
494 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE a = 1/,
495 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 1)) AS foo WHERE a = 1/,
496 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 1) AS foo WHERE a = 1/,
497 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 1)) AS foo WHERE a = 1/,
498 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 1))) AS foo WHERE a = 1/,
499
500 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE (a = 1)/,
501 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 1)) AS foo WHERE (a = 1)/,
502 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 1) AS foo WHERE (a = 1)/,
503 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 1)) AS foo WHERE (a = 1)/,
504 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 1))) AS foo WHERE (a = 1)/,
505 ]
506 },
507
508 # subselects - different
509 {
510 equal => 0,
511 statements => [
512 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE a = 1/,
513 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE a = 2/,
514 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE (a = 3)/,
515 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE a = 4/,
516 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE (a = 5)/,
517 q/SELECT * FROM (SELECT * FROM bar WHERE b = 2) AS foo WHERE a = 1/,
518 q/SELECT * FROM (SELECT * FROM bar WHERE b = 3) AS foo WHERE (a = 1)/,
519 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 4)) AS foo WHERE a = 1/,
520 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 5)) AS foo WHERE (a = 1)/,
521 ]
522 },
523 {
524 equal => 0,
525 statements => [
526 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE a = 1/,
527 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 2) AS foo WHERE a = 1/,
528 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 3)) AS foo WHERE a = 1/,
529 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 4)) AS foo WHERE a = 1/,
530 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 5))) AS foo WHERE a = 1/,
531
532 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 6) AS foo WHERE (a = 1)/,
533 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 7) AS foo WHERE (a = 1)/,
534 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 8)) AS foo WHERE (a = 1)/,
535 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 9)) AS foo WHERE (a = 1)/,
536 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 10))) AS foo WHERE (a = 1)/,
537
538 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE a = 2/,
539 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 2) AS foo WHERE a = 2/,
540 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 3)) AS foo WHERE a = 2/,
541 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 4)) AS foo WHERE a = 2/,
542 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 5))) AS foo WHERE a = 2/,
543
544 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 6) AS foo WHERE (a = 2)/,
545 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 7) AS foo WHERE (a = 2)/,
546 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 8)) AS foo WHERE (a = 2)/,
547 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 9)) AS foo WHERE (a = 2)/,
548 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 10))) AS foo WHERE (a = 2)/,
549 ]
550 },
9a5b64c9 551 {
552 equal => 0,
553 statements => [
554 'SELECT a,b,c FROM foo',
555 'SELECT a,c,b FROM foo',
556 'SELECT b,a,c FROM foo',
557 'SELECT b,c,a FROM foo',
558 'SELECT c,a,b FROM foo',
559 'SELECT c,b,a FROM foo',
560 ]
561 },
562 {
563 equal => 0,
564 statements => [
565 'SELECT * FROM foo WHERE a IN (1,2,3)',
566 'SELECT * FROM foo WHERE a IN (1,3,2)',
567 'SELECT * FROM foo WHERE a IN (2,1,3)',
568 'SELECT * FROM foo WHERE a IN (2,3,1)',
569 'SELECT * FROM foo WHERE a IN (3,1,2)',
570 'SELECT * FROM foo WHERE a IN (3,2,1)',
571 ]
572 },
25823711 573);
574
32c34379 575my @bind_tests = (
576 # scalar - equal
577 {
578 equal => 1,
579 bindvals => [
580 undef,
581 undef,
582 ]
583 },
584 {
585 equal => 1,
586 bindvals => [
587 'foo',
588 'foo',
589 ]
590 },
591 {
592 equal => 1,
593 bindvals => [
594 42,
595 42,
596 '42',
597 ]
598 },
599
600 # scalarref - equal
601 {
602 equal => 1,
603 bindvals => [
604 \'foo',
605 \'foo',
606 ]
607 },
608 {
609 equal => 1,
610 bindvals => [
611 \42,
612 \42,
613 \'42',
614 ]
615 },
616
617 # arrayref - equal
618 {
619 equal => 1,
620 bindvals => [
621 [],
622 []
623 ]
624 },
625 {
626 equal => 1,
627 bindvals => [
628 [42],
629 [42],
630 ['42'],
631 ]
632 },
633 {
634 equal => 1,
635 bindvals => [
636 [1, 42],
637 [1, 42],
638 ['1', 42],
639 [1, '42'],
640 ['1', '42'],
641 ]
642 },
643
644 # hashref - equal
645 {
646 equal => 1,
647 bindvals => [
648 { foo => 42 },
649 { foo => 42 },
650 { foo => '42' },
651 ]
652 },
653 {
654 equal => 1,
655 bindvals => [
656 { foo => 42, bar => 1 },
657 { foo => 42, bar => 1 },
658 { foo => '42', bar => 1 },
659 ]
660 },
661
662 # blessed object - equal
663 {
664 equal => 1,
665 bindvals => [
666 bless(\(local $_ = 42), 'Life::Universe::Everything'),
667 bless(\(local $_ = 42), 'Life::Universe::Everything'),
668 ]
669 },
670 {
671 equal => 1,
672 bindvals => [
673 bless([42], 'Life::Universe::Everything'),
674 bless([42], 'Life::Universe::Everything'),
675 ]
676 },
677 {
678 equal => 1,
679 bindvals => [
680 bless({ answer => 42 }, 'Life::Universe::Everything'),
681 bless({ answer => 42 }, 'Life::Universe::Everything'),
682 ]
683 },
684
685 # complex data structure - equal
686 {
687 equal => 1,
688 bindvals => [
689 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
690 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
691 ]
692 },
693
694
695 # scalar - different
696 {
697 equal => 0,
698 bindvals => [
699 undef,
700 'foo',
701 42,
702 ]
703 },
704
705 # scalarref - different
706 {
707 equal => 0,
708 bindvals => [
709 \undef,
710 \'foo',
711 \42,
712 ]
713 },
714
715 # arrayref - different
716 {
717 equal => 0,
718 bindvals => [
719 [undef],
720 ['foo'],
721 [42],
722 ]
723 },
724
725 # hashref - different
726 {
727 equal => 0,
728 bindvals => [
729 { foo => undef },
730 { foo => 'bar' },
731 { foo => 42 },
732 ]
733 },
734
735 # different types
736 {
737 equal => 0,
738 bindvals => [
739 'foo',
740 \'foo',
741 ['foo'],
742 { foo => 'bar' },
743 ]
744 },
745
746 # complex data structure - different
747 {
748 equal => 0,
749 bindvals => [
750 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
751 [43, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
752 [42, { foo => 'baz', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
753 [42, { bar => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
754 [42, { foo => 'bar', quuux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
755 [42, { foo => 'bar', quux => [0, 1, 2, \3, { quux => [4, 5] } ] }, 8 ],
756 [42, { foo => 'bar', quux => [1, 2, 3, { quux => [4, 5] } ] }, 8 ],
757 [42, { foo => 'bar', quux => [1, 2, \4, { quux => [4, 5] } ] }, 8 ],
758 [42, { foo => 'bar', quux => [1, 2, \3, { quuux => [4, 5] } ] }, 8 ],
759 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5, 6] } ] }, 8 ],
760 [42, { foo => 'bar', quux => [1, 2, \3, { quux => 4 } ] }, 8 ],
761 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5], quuux => 1 } ] }, 8 ],
762 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8, 9 ],
763 ]
764 },
765);
766
25823711 767plan tests => 1 +
768 sum(
769 map { $_ * ($_ - 1) / 2 }
770 map { scalar @{$_->{statements}} }
771 @sql_tests
772 ) +
773 sum(
774 map { $_ * ($_ - 1) / 2 }
775 map { scalar @{$_->{bindvals}} }
776 @bind_tests
e7827ba2 777 ) +
778 3;
32c34379 779
e7827ba2 780use_ok('SQL::Abstract::Test', import => [qw(
781 eq_sql_bind eq_sql eq_bind is_same_sql_bind
782)]);
32c34379 783
25823711 784for my $test (@sql_tests) {
785 my $statements = $test->{statements};
786 while (@$statements) {
787 my $sql1 = shift @$statements;
788 foreach my $sql2 (@$statements) {
e40f5df9 789
790 no warnings qw/once/; # perl 5.10 is dumb
791 local $SQL::Abstract::Test::parenthesis_significant = $test->{parenthesis_significant}
792 if $test->{parenthesis_significant};
25823711 793 my $equal = eq_sql($sql1, $sql2);
e40f5df9 794
56c0595d 795 TODO: {
796 local $TODO = $test->{todo} if $test->{todo};
797
798 if ($test->{equal}) {
799 ok($equal, "equal SQL expressions should have been considered equal");
800 } else {
801 ok(!$equal, "different SQL expressions should have been considered not equal");
802 }
803
804 if ($equal ^ $test->{equal}) {
805 diag("sql1: $sql1");
806 diag("sql2: $sql2");
807 }
25823711 808 }
809 }
810 }
811}
812
32c34379 813for my $test (@bind_tests) {
814 my $bindvals = $test->{bindvals};
815 while (@$bindvals) {
816 my $bind1 = shift @$bindvals;
817 foreach my $bind2 (@$bindvals) {
818 my $equal = eq_bind($bind1, $bind2);
819 if ($test->{equal}) {
820 ok($equal, "equal bind values considered equal");
821 } else {
822 ok(!$equal, "different bind values considered not equal");
823 }
824
825 if ($equal ^ $test->{equal}) {
826 diag("bind1: " . Dumper($bind1));
827 diag("bind2: " . Dumper($bind2));
828 }
829 }
830 }
831}
e7827ba2 832
833ok(eq_sql_bind(
834 "SELECT * FROM foo WHERE id = ?", [42],
835 "SELECT * FROM foo WHERE (id = ?)", [42],
836 ),
837 "eq_sql_bind considers equal SQL expressions and bind values equal"
838);
839
840
841ok(!eq_sql_bind(
842 "SELECT * FROM foo WHERE id = ?", [42],
843 "SELECT * FROM foo WHERE (id = ?)", [0],
844 ),
845 "eq_sql_bind considers equal SQL expressions and different bind values different"
846);
847
848ok(!eq_sql_bind(
849 "SELECT * FROM foo WHERE id = ?", [42],
850 "SELECT * FROM bar WHERE (id = ?)", [42],
851 ),
852 "eq_sql_bind considers different SQL expressions and equal bind values different"
853);
Generate Where Clauses! (git://git.shadowcat.co.uk/dbsrgits/SQL-Abstract.git)