projects / dbsrgits/SQL-Abstract.git / blame
? search:
summary | shortlog | log | commit | commitdiff | tree
blob | history | HEAD
Merge 'trunk' into 'and_or'
[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 },
551);
552
32c34379 553my @bind_tests = (
554 # scalar - equal
555 {
556 equal => 1,
557 bindvals => [
558 undef,
559 undef,
560 ]
561 },
562 {
563 equal => 1,
564 bindvals => [
565 'foo',
566 'foo',
567 ]
568 },
569 {
570 equal => 1,
571 bindvals => [
572 42,
573 42,
574 '42',
575 ]
576 },
577
578 # scalarref - equal
579 {
580 equal => 1,
581 bindvals => [
582 \'foo',
583 \'foo',
584 ]
585 },
586 {
587 equal => 1,
588 bindvals => [
589 \42,
590 \42,
591 \'42',
592 ]
593 },
594
595 # arrayref - equal
596 {
597 equal => 1,
598 bindvals => [
599 [],
600 []
601 ]
602 },
603 {
604 equal => 1,
605 bindvals => [
606 [42],
607 [42],
608 ['42'],
609 ]
610 },
611 {
612 equal => 1,
613 bindvals => [
614 [1, 42],
615 [1, 42],
616 ['1', 42],
617 [1, '42'],
618 ['1', '42'],
619 ]
620 },
621
622 # hashref - equal
623 {
624 equal => 1,
625 bindvals => [
626 { foo => 42 },
627 { foo => 42 },
628 { foo => '42' },
629 ]
630 },
631 {
632 equal => 1,
633 bindvals => [
634 { foo => 42, bar => 1 },
635 { foo => 42, bar => 1 },
636 { foo => '42', bar => 1 },
637 ]
638 },
639
640 # blessed object - equal
641 {
642 equal => 1,
643 bindvals => [
644 bless(\(local $_ = 42), 'Life::Universe::Everything'),
645 bless(\(local $_ = 42), 'Life::Universe::Everything'),
646 ]
647 },
648 {
649 equal => 1,
650 bindvals => [
651 bless([42], 'Life::Universe::Everything'),
652 bless([42], 'Life::Universe::Everything'),
653 ]
654 },
655 {
656 equal => 1,
657 bindvals => [
658 bless({ answer => 42 }, 'Life::Universe::Everything'),
659 bless({ answer => 42 }, 'Life::Universe::Everything'),
660 ]
661 },
662
663 # complex data structure - equal
664 {
665 equal => 1,
666 bindvals => [
667 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
668 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
669 ]
670 },
671
672
673 # scalar - different
674 {
675 equal => 0,
676 bindvals => [
677 undef,
678 'foo',
679 42,
680 ]
681 },
682
683 # scalarref - different
684 {
685 equal => 0,
686 bindvals => [
687 \undef,
688 \'foo',
689 \42,
690 ]
691 },
692
693 # arrayref - different
694 {
695 equal => 0,
696 bindvals => [
697 [undef],
698 ['foo'],
699 [42],
700 ]
701 },
702
703 # hashref - different
704 {
705 equal => 0,
706 bindvals => [
707 { foo => undef },
708 { foo => 'bar' },
709 { foo => 42 },
710 ]
711 },
712
713 # different types
714 {
715 equal => 0,
716 bindvals => [
717 'foo',
718 \'foo',
719 ['foo'],
720 { foo => 'bar' },
721 ]
722 },
723
724 # complex data structure - different
725 {
726 equal => 0,
727 bindvals => [
728 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
729 [43, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
730 [42, { foo => 'baz', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
731 [42, { bar => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
732 [42, { foo => 'bar', quuux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
733 [42, { foo => 'bar', quux => [0, 1, 2, \3, { quux => [4, 5] } ] }, 8 ],
734 [42, { foo => 'bar', quux => [1, 2, 3, { quux => [4, 5] } ] }, 8 ],
735 [42, { foo => 'bar', quux => [1, 2, \4, { quux => [4, 5] } ] }, 8 ],
736 [42, { foo => 'bar', quux => [1, 2, \3, { quuux => [4, 5] } ] }, 8 ],
737 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5, 6] } ] }, 8 ],
738 [42, { foo => 'bar', quux => [1, 2, \3, { quux => 4 } ] }, 8 ],
739 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5], quuux => 1 } ] }, 8 ],
740 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8, 9 ],
741 ]
742 },
743);
744
25823711 745plan tests => 1 +
746 sum(
747 map { $_ * ($_ - 1) / 2 }
748 map { scalar @{$_->{statements}} }
749 @sql_tests
750 ) +
751 sum(
752 map { $_ * ($_ - 1) / 2 }
753 map { scalar @{$_->{bindvals}} }
754 @bind_tests
e7827ba2 755 ) +
756 3;
32c34379 757
e7827ba2 758use_ok('SQL::Abstract::Test', import => [qw(
759 eq_sql_bind eq_sql eq_bind is_same_sql_bind
760)]);
32c34379 761
25823711 762for my $test (@sql_tests) {
763 my $statements = $test->{statements};
764 while (@$statements) {
765 my $sql1 = shift @$statements;
766 foreach my $sql2 (@$statements) {
e40f5df9 767
768 no warnings qw/once/; # perl 5.10 is dumb
769 local $SQL::Abstract::Test::parenthesis_significant = $test->{parenthesis_significant}
770 if $test->{parenthesis_significant};
25823711 771 my $equal = eq_sql($sql1, $sql2);
e40f5df9 772
56c0595d 773 TODO: {
774 local $TODO = $test->{todo} if $test->{todo};
775
776 if ($test->{equal}) {
777 ok($equal, "equal SQL expressions should have been considered equal");
778 } else {
779 ok(!$equal, "different SQL expressions should have been considered not equal");
780 }
781
782 if ($equal ^ $test->{equal}) {
783 diag("sql1: $sql1");
784 diag("sql2: $sql2");
785 }
25823711 786 }
787 }
788 }
789}
790
32c34379 791for my $test (@bind_tests) {
792 my $bindvals = $test->{bindvals};
793 while (@$bindvals) {
794 my $bind1 = shift @$bindvals;
795 foreach my $bind2 (@$bindvals) {
796 my $equal = eq_bind($bind1, $bind2);
797 if ($test->{equal}) {
798 ok($equal, "equal bind values considered equal");
799 } else {
800 ok(!$equal, "different bind values considered not equal");
801 }
802
803 if ($equal ^ $test->{equal}) {
804 diag("bind1: " . Dumper($bind1));
805 diag("bind2: " . Dumper($bind2));
806 }
807 }
808 }
809}
e7827ba2 810
811ok(eq_sql_bind(
812 "SELECT * FROM foo WHERE id = ?", [42],
813 "SELECT * FROM foo WHERE (id = ?)", [42],
814 ),
815 "eq_sql_bind considers equal SQL expressions and bind values equal"
816);
817
818
819ok(!eq_sql_bind(
820 "SELECT * FROM foo WHERE id = ?", [42],
821 "SELECT * FROM foo WHERE (id = ?)", [0],
822 ),
823 "eq_sql_bind considers equal SQL expressions and different bind values different"
824);
825
826ok(!eq_sql_bind(
827 "SELECT * FROM foo WHERE id = ?", [42],
828 "SELECT * FROM bar WHERE (id = ?)", [42],
829 ),
830 "eq_sql_bind considers different SQL expressions and equal bind values different"
831);
Generate Where Clauses! (git://git.shadowcat.co.uk/dbsrgits/SQL-Abstract.git)