projects / dbsrgits/SQL-Abstract.git / blame
? search:
summary | shortlog | log | commit | commitdiff | tree
blob | history | HEAD
These 'optimizations' are silly
[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 },
635 # math
636 {
637 equal => 0,
638 statements => [
639 'SELECT * FROM foo WHERE 1 = ( a > b)',
640 'SELECT * FROM foo WHERE 1 = a > b',
641 'SELECT * FROM foo WHERE (1 = a) > b',
642 ]
643 },
644 {
645 equal => 1,
646 statements => [
647 'SELECT * FROM foo WHERE bar = baz(buzz)',
648 'SELECT * FROM foo WHERE bar = (baz( buzz ))',
649 ]
650 },
25823711 651);
652
32c34379 653my @bind_tests = (
654 # scalar - equal
655 {
656 equal => 1,
657 bindvals => [
658 undef,
659 undef,
660 ]
661 },
662 {
663 equal => 1,
664 bindvals => [
665 'foo',
666 'foo',
667 ]
668 },
669 {
670 equal => 1,
671 bindvals => [
672 42,
673 42,
674 '42',
675 ]
676 },
677
678 # scalarref - equal
679 {
680 equal => 1,
681 bindvals => [
682 \'foo',
683 \'foo',
684 ]
685 },
686 {
687 equal => 1,
688 bindvals => [
689 \42,
690 \42,
691 \'42',
692 ]
693 },
694
695 # arrayref - equal
696 {
697 equal => 1,
698 bindvals => [
699 [],
700 []
701 ]
702 },
703 {
704 equal => 1,
705 bindvals => [
706 [42],
707 [42],
708 ['42'],
709 ]
710 },
711 {
712 equal => 1,
713 bindvals => [
714 [1, 42],
715 [1, 42],
716 ['1', 42],
717 [1, '42'],
718 ['1', '42'],
719 ]
720 },
721
722 # hashref - equal
723 {
724 equal => 1,
725 bindvals => [
726 { foo => 42 },
727 { foo => 42 },
728 { foo => '42' },
729 ]
730 },
731 {
732 equal => 1,
733 bindvals => [
734 { foo => 42, bar => 1 },
735 { foo => 42, bar => 1 },
736 { foo => '42', bar => 1 },
737 ]
738 },
739
740 # blessed object - equal
741 {
742 equal => 1,
743 bindvals => [
744 bless(\(local $_ = 42), 'Life::Universe::Everything'),
745 bless(\(local $_ = 42), 'Life::Universe::Everything'),
746 ]
747 },
748 {
749 equal => 1,
750 bindvals => [
751 bless([42], 'Life::Universe::Everything'),
752 bless([42], 'Life::Universe::Everything'),
753 ]
754 },
755 {
756 equal => 1,
757 bindvals => [
758 bless({ answer => 42 }, 'Life::Universe::Everything'),
759 bless({ answer => 42 }, 'Life::Universe::Everything'),
760 ]
761 },
762
763 # complex data structure - equal
764 {
765 equal => 1,
766 bindvals => [
767 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
768 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
769 ]
770 },
771
772
773 # scalar - different
774 {
775 equal => 0,
776 bindvals => [
777 undef,
778 'foo',
779 42,
780 ]
781 },
782
783 # scalarref - different
784 {
785 equal => 0,
786 bindvals => [
787 \undef,
788 \'foo',
789 \42,
790 ]
791 },
792
793 # arrayref - different
794 {
795 equal => 0,
796 bindvals => [
797 [undef],
798 ['foo'],
799 [42],
800 ]
801 },
802
803 # hashref - different
804 {
805 equal => 0,
806 bindvals => [
807 { foo => undef },
808 { foo => 'bar' },
809 { foo => 42 },
810 ]
811 },
812
813 # different types
814 {
815 equal => 0,
816 bindvals => [
817 'foo',
818 \'foo',
819 ['foo'],
820 { foo => 'bar' },
821 ]
822 },
823
824 # complex data structure - different
825 {
826 equal => 0,
827 bindvals => [
828 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
829 [43, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
830 [42, { foo => 'baz', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
831 [42, { bar => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
832 [42, { foo => 'bar', quuux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
833 [42, { foo => 'bar', quux => [0, 1, 2, \3, { quux => [4, 5] } ] }, 8 ],
834 [42, { foo => 'bar', quux => [1, 2, 3, { quux => [4, 5] } ] }, 8 ],
835 [42, { foo => 'bar', quux => [1, 2, \4, { quux => [4, 5] } ] }, 8 ],
836 [42, { foo => 'bar', quux => [1, 2, \3, { quuux => [4, 5] } ] }, 8 ],
837 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5, 6] } ] }, 8 ],
838 [42, { foo => 'bar', quux => [1, 2, \3, { quux => 4 } ] }, 8 ],
839 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5], quuux => 1 } ] }, 8 ],
840 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8, 9 ],
841 ]
842 },
843);
844
25823711 845plan tests => 1 +
846 sum(
847 map { $_ * ($_ - 1) / 2 }
848 map { scalar @{$_->{statements}} }
849 @sql_tests
850 ) +
851 sum(
852 map { $_ * ($_ - 1) / 2 }
853 map { scalar @{$_->{bindvals}} }
854 @bind_tests
e7827ba2 855 ) +
856 3;
32c34379 857
e7827ba2 858use_ok('SQL::Abstract::Test', import => [qw(
859 eq_sql_bind eq_sql eq_bind is_same_sql_bind
860)]);
32c34379 861
25823711 862for my $test (@sql_tests) {
863 my $statements = $test->{statements};
864 while (@$statements) {
865 my $sql1 = shift @$statements;
866 foreach my $sql2 (@$statements) {
e40f5df9 867
868 no warnings qw/once/; # perl 5.10 is dumb
869 local $SQL::Abstract::Test::parenthesis_significant = $test->{parenthesis_significant}
870 if $test->{parenthesis_significant};
25823711 871 my $equal = eq_sql($sql1, $sql2);
e40f5df9 872
56c0595d 873 TODO: {
874 local $TODO = $test->{todo} if $test->{todo};
875
876 if ($test->{equal}) {
877 ok($equal, "equal SQL expressions should have been considered equal");
878 } else {
879 ok(!$equal, "different SQL expressions should have been considered not equal");
880 }
881
882 if ($equal ^ $test->{equal}) {
0769ac0e 883 my ($ast1, $ast2) = map { SQL::Abstract::Test::parse ($_) } ($sql1, $sql2);
884
885 $_ = Dumper $_ for ($ast1, $ast2);
886
56c0595d 887 diag("sql1: $sql1");
888 diag("sql2: $sql2");
0769ac0e 889 note("ast1: $ast1");
890 note("ast2: $ast2");
56c0595d 891 }
25823711 892 }
893 }
894 }
895}
896
32c34379 897for my $test (@bind_tests) {
898 my $bindvals = $test->{bindvals};
899 while (@$bindvals) {
900 my $bind1 = shift @$bindvals;
901 foreach my $bind2 (@$bindvals) {
902 my $equal = eq_bind($bind1, $bind2);
903 if ($test->{equal}) {
904 ok($equal, "equal bind values considered equal");
905 } else {
906 ok(!$equal, "different bind values considered not equal");
907 }
908
909 if ($equal ^ $test->{equal}) {
910 diag("bind1: " . Dumper($bind1));
911 diag("bind2: " . Dumper($bind2));
912 }
913 }
914 }
915}
e7827ba2 916
917ok(eq_sql_bind(
918 "SELECT * FROM foo WHERE id = ?", [42],
919 "SELECT * FROM foo WHERE (id = ?)", [42],
920 ),
921 "eq_sql_bind considers equal SQL expressions and bind values equal"
922);
923
924
925ok(!eq_sql_bind(
926 "SELECT * FROM foo WHERE id = ?", [42],
927 "SELECT * FROM foo WHERE (id = ?)", [0],
928 ),
929 "eq_sql_bind considers equal SQL expressions and different bind values different"
930);
931
932ok(!eq_sql_bind(
933 "SELECT * FROM foo WHERE id = ?", [42],
934 "SELECT * FROM bar WHERE (id = ?)", [42],
935 ),
936 "eq_sql_bind considers different SQL expressions and equal bind values different"
937);
Generate Where Clauses! (git://git.shadowcat.co.uk/dbsrgits/SQL-Abstract.git)