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