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