projects / scpubgit/Q-Branch.git / blame
? search:
summary | shortlog | log | commit | commitdiff | tree
blob | history | HEAD
Extra stopword (we need to find a way to introduce dialects...)
[scpubgit/Q-Branch.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,
3b9d807e 156 opts => { parenthesis_significant => 1 },
e40f5df9 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,
3b9d807e 167 opts => { parenthesis_significant => 1 },
e40f5df9 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,
3b9d807e 177 opts => { parenthesis_significant => 1 },
e40f5df9 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,
3b9d807e 263 opts => { parenthesis_significant => 1 },
14190bdf 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
b3b79607 285 # IS NULL (special LHS-only op)
286 {
287 equal => 1,
288 statements => [
289 q/WHERE a IS NOT NULL AND b IS NULL/,
290 q/WHERE (a IS NOT NULL) AND b IS NULL/,
291 q/WHERE a IS NOT NULL AND (b IS NULL)/,
292 q/WHERE (a IS NOT NULL) AND ((b IS NULL))/,
293 ],
294 },
295
25823711 296 # JOIN condition - equal
297 {
298 equal => 1,
299 statements => [
300 q/SELECT foo FROM bar JOIN baz ON a = 1 WHERE x = 1/,
301 q/SELECT foo FROM bar JOIN baz ON a=1 WHERE x = 1/,
302 q/SELECT foo FROM bar JOIN baz ON (a = 1) WHERE x = 1/,
303 q/SELECT foo FROM bar JOIN baz ON (a=1) WHERE x = 1/,
304 q/SELECT foo FROM bar JOIN baz ON ( a = 1 ) WHERE x = 1/,
305 q/
306 SELECT
307 foo
308 FROM
309 bar
310 JOIN
311 baz
312 ON
313 a = 1
314 WHERE
315 x = 1
316 /,
317 q/
318 SELECT
319 foo
320 FROM
321 bar
322 JOIN
323 baz
324 ON
325 (a = 1)
326 WHERE
327 x = 1
328 /,
329 q/
330 SELECT
331 foo
332 FROM
333 bar
334 JOIN
335 baz
336 ON
337 ( a = 1 )
338 WHERE
339 x = 1
340 /,
341 q/SELECT foo FROM bar JOIN baz ON ((a = 1)) WHERE x = 1/,
342 q/SELECT foo FROM bar JOIN baz ON ( (a = 1) ) WHERE x = 1/,
343 q/SELECT foo FROM bar JOIN baz ON ( ( a = 1 ) ) WHERE x = 1/,
344 ]
345 },
346 {
347 equal => 1,
348 statements => [
349 q/SELECT foo FROM bar JOIN baz ON a = 1 AND b = 1 WHERE x = 1/,
350 q/SELECT foo FROM bar JOIN baz ON (a = 1) AND (b = 1) WHERE x = 1/,
351 q/SELECT foo FROM bar JOIN baz ON ((a = 1) AND (b = 1)) WHERE x = 1/,
352 q/SELECT foo FROM bar JOIN baz ON (a = 1 AND b = 1) WHERE x = 1/,
353 q/SELECT foo FROM bar JOIN baz ON ((a = 1 AND b = 1)) WHERE x = 1/,
354 q/SELECT foo FROM bar JOIN baz ON (((a = 1) AND (b = 1))) WHERE x = 1/,
355 q/
356 SELECT
357 foo
358 FROM
359 bar
360 JOIN
361 baz
362 ON
363 a = 1
364 AND
365 b = 1
366 WHERE
367 x = 1
368 /,
369 q/
370 SELECT
371 foo
372 FROM
373 bar
374 JOIN
375 baz
376 ON
377 (a = 1
378 AND
379 b = 1)
380 WHERE
381 x = 1
382 /,
383 q/
384 SELECT
385 foo
386 FROM
387 bar
388 JOIN
389 baz
390 ON
391 (a = 1)
392 AND
393 (b = 1)
394 WHERE
395 x = 1
396 /,
397 q/
398 SELECT
399 foo
400 FROM
401 bar
402 JOIN
403 baz
404 ON
405 ((a = 1)
406 AND
407 (b = 1))
408 WHERE
409 x = 1
410 /,
411 ]
412 },
413
414 # JOIN condition - different
415 {
416 equal => 0,
417 statements => [
418 q/SELECT foo FROM bar JOIN quux ON a = 1 WHERE quuux/,
419 q/SELECT quux FROM bar JOIN quux ON a = 1 WHERE quuux/,
420 q/SELECT foo FROM quux JOIN quux ON a = 1 WHERE quuux/,
421 q/FOOBAR foo FROM bar JOIN quux ON a = 1 WHERE quuux/,
422
423 q/SELECT foo FROM bar JOIN quux ON a = 2 WHERE quuux/,
424 q/SELECT foo FROM bar JOIN quux ON a < 1 WHERE quuux/,
425 q/SELECT foo FROM bar JOIN quux ON b = 1 WHERE quuux/,
426 q/SELECT foo FROM bar JOIN quux ON (c = 1) WHERE quuux/,
427 q/SELECT foo FROM bar JOIN quux ON (d = 1) WHERE quuux/,
428
429 q/SELECT foo FROM bar JOIN quux ON a = 1 AND quuux/,
430 q/SELECT foo FROM bar JOIN quux ON a = 1 GROUP BY foo/,
431 q/SELECT foo FROM bar JOIN quux ON a = 1 ORDER BY foo/,
432 q/SELECT foo FROM bar JOIN quux ON a = 1 LIMIT 1/,
433 q/SELECT foo FROM bar JOIN quux ON a = 1 OFFSET 1/,
434 q/SELECT foo FROM bar JOIN quux ON a = 1 JOIN quuux/,
435 q/SELECT foo FROM bar JOIN quux ON a = 1 JOIN quuux ON a = 1/,
436 ]
437 },
438 {
439 equal => 0,
440 statements => [
441 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 WHERE quuux/,
442 q/SELECT quux FROM bar JOIN quux ON a = 1 AND b = 1 WHERE quuux/,
443 q/SELECT foo FROM quux JOIN quux ON a = 1 AND b = 1 WHERE quuux/,
444 q/FOOBAR foo FROM bar JOIN quux ON a = 1 AND b = 1 WHERE quuux/,
445
446 q/SELECT foo FROM bar JOIN quux ON a = 2 AND b = 1 WHERE quuux/,
447 q/SELECT foo FROM bar JOIN quux ON a = 3 AND (b = 1) WHERE quuux/,
448 q/SELECT foo FROM bar JOIN quux ON (a = 4) AND b = 1 WHERE quuux/,
449 q/SELECT foo FROM bar JOIN quux ON (a = 5) AND (b = 1) WHERE quuux/,
450 q/SELECT foo FROM bar JOIN quux ON ((a = 6) AND (b = 1)) WHERE quuux/,
451 q/SELECT foo FROM bar JOIN quux ON ((a = 7) AND (b = 1)) WHERE quuux/,
452
453 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 2 WHERE quuux/,
454 q/SELECT foo FROM bar JOIN quux ON a = 1 AND (b = 3) WHERE quuux/,
455 q/SELECT foo FROM bar JOIN quux ON (a = 1) AND b = 4 WHERE quuux/,
456 q/SELECT foo FROM bar JOIN quux ON (a = 1) AND (b = 5) WHERE quuux/,
457 q/SELECT foo FROM bar JOIN quux ON ((a = 1) AND (b = 6)) WHERE quuux/,
458 q/SELECT foo FROM bar JOIN quux ON ((a = 1) AND (b = 7)) WHERE quuux/,
459
460 q/SELECT foo FROM bar JOIN quux ON a < 1 AND b = 1 WHERE quuux/,
461 q/SELECT foo FROM bar JOIN quux ON b = 1 AND b = 1 WHERE quuux/,
462 q/SELECT foo FROM bar JOIN quux ON (c = 1) AND b = 1 WHERE quuux/,
463 q/SELECT foo FROM bar JOIN quux ON (d = 1) AND b = 1 WHERE quuux/,
464
465 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 AND quuux/,
466 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 GROUP BY foo/,
467 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 ORDER BY foo/,
468 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 LIMIT 1/,
469 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 OFFSET 1/,
470 q/SELECT foo FROM bar JOIN quux JOIN quuux ON a = 1 AND b = 1/,
471 q/SELECT foo FROM bar JOIN quux ON a = 1 JOIN quuux ON a = 1 AND b = 1/,
472 ]
473 },
474
475 # DISTINCT ON (...) not confused with JOIN ON (...)
476 {
477 equal => 1,
478 statements => [
479 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE a = 1/,
480 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE a=1/,
481 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE (a = 1)/,
482 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE (a=1)/,
483 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ( a = 1 )/,
484 q/
485 SELECT DISTINCT ON (foo, quux)
486 foo,
487 quux
488 FROM
489 bar
490 WHERE
491 a = 1
492 /,
493 q/
494 SELECT DISTINCT ON (foo, quux)
495 foo,
496 quux
497 FROM
498 bar
499 WHERE
500 (a = 1)
501 /,
502 q/
503 SELECT DISTINCT ON (foo, quux)
504 foo,
505 quux
506 FROM
507 bar
508 WHERE
509 ( a = 1 )
510 /,
511 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ((a = 1))/,
512 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ( (a = 1) )/,
513 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ( ( a = 1 ) )/,
514 ]
515 },
516
517 # subselects - equal
518 {
519 equal => 1,
520 statements => [
521 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE a = 1/,
522 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE (a = 1)/,
523 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE a = 1/,
524 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE (a = 1)/,
525 ]
526 },
527 {
528 equal => 1,
529 statements => [
530 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE a = 1/,
531 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 1)) AS foo WHERE a = 1/,
532 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 1) AS foo WHERE a = 1/,
533 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 1)) AS foo WHERE a = 1/,
534 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 1))) AS foo WHERE a = 1/,
535
536 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE (a = 1)/,
537 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 1)) AS foo WHERE (a = 1)/,
538 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 1) AS foo WHERE (a = 1)/,
539 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 1)) AS foo WHERE (a = 1)/,
540 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 1))) AS foo WHERE (a = 1)/,
541 ]
542 },
543
544 # subselects - different
545 {
546 equal => 0,
547 statements => [
54629227 548 q/DELETE FROM cd WHERE ( cdid IN ( SELECT me.cdid FROM (SELECT * FROM cd me WHERE ( year != ? ) GROUP BY me.cdid) me WHERE ( year != ? ) ) )/,
549 q/DELETE FROM cd WHERE ( cdid IN ( SELECT me.cdid FROM cd me WHERE ( year != ? ) GROUP BY me.cdid ) )/,
550 ],
551 },
552 {
553 equal => 0,
554 statements => [
25823711 555 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE a = 1/,
556 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE a = 2/,
557 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE (a = 3)/,
558 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE a = 4/,
559 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE (a = 5)/,
560 q/SELECT * FROM (SELECT * FROM bar WHERE b = 2) AS foo WHERE a = 1/,
561 q/SELECT * FROM (SELECT * FROM bar WHERE b = 3) AS foo WHERE (a = 1)/,
562 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 4)) AS foo WHERE a = 1/,
563 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 5)) AS foo WHERE (a = 1)/,
564 ]
565 },
566 {
567 equal => 0,
568 statements => [
569 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE a = 1/,
570 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 2) AS foo WHERE a = 1/,
571 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 3)) AS foo WHERE a = 1/,
572 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 4)) AS foo WHERE a = 1/,
573 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 5))) AS foo WHERE a = 1/,
574
575 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 6) AS foo WHERE (a = 1)/,
576 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 7) AS foo WHERE (a = 1)/,
577 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 8)) AS foo WHERE (a = 1)/,
578 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 9)) AS foo WHERE (a = 1)/,
579 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 10))) AS foo WHERE (a = 1)/,
580
581 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE a = 2/,
582 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 2) AS foo WHERE a = 2/,
583 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 3)) AS foo WHERE a = 2/,
584 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 4)) AS foo WHERE a = 2/,
585 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 5))) AS foo WHERE a = 2/,
586
587 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 6) AS foo WHERE (a = 2)/,
588 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 7) AS foo WHERE (a = 2)/,
589 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 8)) AS foo WHERE (a = 2)/,
590 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 9)) AS foo WHERE (a = 2)/,
591 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 10))) AS foo WHERE (a = 2)/,
592 ]
593 },
0769ac0e 594
0c2de280 595 # order by
596 {
597 equal => 1,
598 statements => [
599 q/SELECT * FROM foo ORDER BY bar/,
600 q/SELECT * FROM foo ORDER BY bar ASC/,
601 q/SELECT * FROM foo ORDER BY bar asc/,
602 ],
603 },
604 {
605 equal => 1,
606 statements => [
607 q/SELECT * FROM foo ORDER BY bar, baz ASC/,
608 q/SELECT * FROM foo ORDER BY bar ASC, baz/,
609 q/SELECT * FROM foo ORDER BY bar asc, baz ASC/,
610 q/SELECT * FROM foo ORDER BY bar, baz/,
611 ],
612 },
613 {
c84a4321 614 equal => 1,
615 statements => [
616 q/ORDER BY colA, colB LIKE ? DESC, colC LIKE ?/,
617 q/ORDER BY colA ASC, colB LIKE ? DESC, colC LIKE ? ASC/,
618 ],
619 },
620 {
b4085a1a 621 equal => 1,
622 statements => [
623 q/ORDER BY name + ?, [me].[id]/,
624 q/ORDER BY name + ? ASC, [me].[id]/,
625 ],
626 },
627 {
0c2de280 628 equal => 0,
629 opts => { order_by_asc_significant => 1 },
630 statements => [
631 q/SELECT * FROM foo ORDER BY bar/,
632 q/SELECT * FROM foo ORDER BY bar ASC/,
633 q/SELECT * FROM foo ORDER BY bar desc/,
634 ],
635 },
636
0769ac0e 637 # list permutations
9a5b64c9 638 {
f8135ff3 639 equal => 0,
640 statements => [
641 'SELECT a,b,c FROM foo',
642 'SELECT a,c,b FROM foo',
643 'SELECT b,a,c FROM foo',
644 'SELECT b,c,a FROM foo',
645 'SELECT c,a,b FROM foo',
646 'SELECT c,b,a FROM foo',
647 ],
9a5b64c9 648 },
649 {
f8135ff3 650 equal => 0,
651 statements => [
652 'SELECT * FROM foo WHERE a IN (1,2,3)',
653 'SELECT * FROM foo WHERE a IN (1,3,2)',
654 'SELECT * FROM foo WHERE a IN (2,1,3)',
655 'SELECT * FROM foo WHERE a IN (2,3,1)',
656 'SELECT * FROM foo WHERE a IN (3,1,2)',
657 'SELECT * FROM foo WHERE a IN (3,2,1)',
658 ]
76f10e1c 659 },
7cc47319 660
661 # list consistency
662 {
663 equal => 0,
664 statements => [
665 'SELECT a,b FROM foo',
666 'SELECT a,,b FROM foo',
667 'SELECT a,b, FROM foo',
668 'SELECT ,a,b, FROM foo',
669 'SELECT ,a,,b, FROM foo',
670 ],
671 },
672
673 # misc func
76f10e1c 674 {
f8135ff3 675 equal => 0,
676 statements => [
677 'SELECT count(*) FROM foo',
678 'SELECT count(*) AS bar FROM foo',
679 'SELECT count(*) AS "bar" FROM foo',
680 'SELECT count(a) FROM foo',
681 'SELECT count(1) FROM foo',
682 ]
9a5b64c9 683 },
0769ac0e 684 {
685 equal => 1,
686 statements => [
687 'SELECT foo() bar FROM baz',
688 'SELECT foo ( )bar FROM baz',
689 'SELECT foo (())bar FROM baz',
690 'SELECT foo(( ) ) bar FROM baz',
691 ]
692 },
693 {
694 equal => 0,
695 statements => [
696 'SELECT foo() FROM bar',
697 'SELECT foo FROM bar',
698 'SELECT foo FROM bar ()',
699 ]
700 },
b3b79607 701 {
6e9a377b 702 equal => 0,
b3b79607 703 statements => [
6e9a377b 704 'SELECT COUNT * FROM foo',
705 'SELECT COUNT( * ) FROM foo',
b3b79607 706 ]
707 },
6e9a377b 708 # single ? of unknown funcs do not unroll unless
709 # explicitly allowed (e.g. Like)
b3b79607 710 {
6e9a377b 711 equal => 0,
b3b79607 712 statements => [
6e9a377b 713 'SELECT foo FROM bar WHERE bar > foo ?',
714 'SELECT foo FROM bar WHERE bar > foo( ? )',
b3b79607 715 ]
716 },
717 {
718 equal => 1,
719 statements => [
6e9a377b 720 'SELECT foo FROM bar WHERE bar LIKE ?',
721 'SELECT foo FROM bar WHERE bar LiKe (?)',
722 'SELECT foo FROM bar WHERE bar lIkE( (?))',
b3b79607 723 ]
724 },
6e9a377b 725 # test multival
69cc1bd8 726 {
727 equal => 0,
728 statements => [
729 'SELECT foo FROM bar WHERE foo IN (?, ?)',
730 'SELECT foo FROM bar WHERE foo IN ?, ?',
731 ]
732 },
0769ac0e 733 # math
734 {
735 equal => 0,
736 statements => [
737 'SELECT * FROM foo WHERE 1 = ( a > b)',
738 'SELECT * FROM foo WHERE 1 = a > b',
739 'SELECT * FROM foo WHERE (1 = a) > b',
740 ]
741 },
742 {
743 equal => 1,
744 statements => [
745 'SELECT * FROM foo WHERE bar = baz(buzz)',
746 'SELECT * FROM foo WHERE bar = (baz( buzz ))',
747 ]
748 },
6f2a5b66 749 # oddballs
750 {
751 equal => 1,
752 statements => [
753 'WHERE ( foo GLOB ? )',
754 'WHERE foo GLOB ?',
755 ],
1de1d085 756 },
757 {
758 equal => 1,
759 statements => [
760 'SELECT FIRST ? SKIP ? [me].[id], [me].[owner]
761 FROM [books] [me]
762 WHERE ( ( (EXISTS (
763 SELECT FIRST ? SKIP ? [owner].[id]
764 FROM [owners] [owner]
765 WHERE ( [books].[owner] = [owner].[id] )
766 )) AND [source] = ? ) )',
767 'SELECT FIRST ? SKIP ? [me].[id], [me].[owner]
768 FROM [books] [me]
769 WHERE ( ( EXISTS (
770 SELECT FIRST ? SKIP ? [owner].[id]
771 FROM [owners] [owner]
772 WHERE ( [books].[owner] = [owner].[id] )
773 ) AND [source] = ? ) )',
774 ],
775 },
81b3e585 776 {
777 equal => 1,
778 statements => [
779 'WHERE foo = ? FETCH FIRST 1 ROWS ONLY',
780 'WHERE ( foo = ? ) FETCH FIRST 1 ROWS ONLY',
781 'WHERE (( foo = ? )) FETCH FIRST 1 ROWS ONLY',
782 ],
783 },
25823711 784);
785
32c34379 786my @bind_tests = (
787 # scalar - equal
788 {
789 equal => 1,
790 bindvals => [
791 undef,
792 undef,
793 ]
794 },
795 {
796 equal => 1,
797 bindvals => [
798 'foo',
799 'foo',
800 ]
801 },
802 {
803 equal => 1,
804 bindvals => [
805 42,
806 42,
807 '42',
808 ]
809 },
810
811 # scalarref - equal
812 {
813 equal => 1,
814 bindvals => [
815 \'foo',
816 \'foo',
817 ]
818 },
819 {
820 equal => 1,
821 bindvals => [
822 \42,
823 \42,
824 \'42',
825 ]
826 },
827
828 # arrayref - equal
829 {
830 equal => 1,
831 bindvals => [
832 [],
833 []
834 ]
835 },
836 {
837 equal => 1,
838 bindvals => [
839 [42],
840 [42],
841 ['42'],
842 ]
843 },
844 {
845 equal => 1,
846 bindvals => [
847 [1, 42],
848 [1, 42],
849 ['1', 42],
850 [1, '42'],
851 ['1', '42'],
852 ]
853 },
854
855 # hashref - equal
856 {
857 equal => 1,
858 bindvals => [
859 { foo => 42 },
860 { foo => 42 },
861 { foo => '42' },
862 ]
863 },
864 {
865 equal => 1,
866 bindvals => [
867 { foo => 42, bar => 1 },
868 { foo => 42, bar => 1 },
869 { foo => '42', bar => 1 },
870 ]
871 },
872
873 # blessed object - equal
874 {
875 equal => 1,
876 bindvals => [
877 bless(\(local $_ = 42), 'Life::Universe::Everything'),
878 bless(\(local $_ = 42), 'Life::Universe::Everything'),
879 ]
880 },
881 {
882 equal => 1,
883 bindvals => [
884 bless([42], 'Life::Universe::Everything'),
885 bless([42], 'Life::Universe::Everything'),
886 ]
887 },
888 {
889 equal => 1,
890 bindvals => [
891 bless({ answer => 42 }, 'Life::Universe::Everything'),
892 bless({ answer => 42 }, 'Life::Universe::Everything'),
893 ]
894 },
895
896 # complex data structure - equal
897 {
898 equal => 1,
899 bindvals => [
900 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
901 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
902 ]
903 },
904
905
906 # scalar - different
907 {
908 equal => 0,
909 bindvals => [
910 undef,
911 'foo',
912 42,
913 ]
914 },
915
916 # scalarref - different
917 {
918 equal => 0,
919 bindvals => [
920 \undef,
921 \'foo',
922 \42,
923 ]
924 },
925
926 # arrayref - different
927 {
928 equal => 0,
929 bindvals => [
930 [undef],
931 ['foo'],
932 [42],
933 ]
934 },
935
936 # hashref - different
937 {
938 equal => 0,
939 bindvals => [
940 { foo => undef },
941 { foo => 'bar' },
942 { foo => 42 },
943 ]
944 },
945
946 # different types
947 {
948 equal => 0,
949 bindvals => [
950 'foo',
951 \'foo',
952 ['foo'],
953 { foo => 'bar' },
954 ]
955 },
956
957 # complex data structure - different
958 {
959 equal => 0,
960 bindvals => [
961 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
962 [43, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
963 [42, { foo => 'baz', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
964 [42, { bar => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
965 [42, { foo => 'bar', quuux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
966 [42, { foo => 'bar', quux => [0, 1, 2, \3, { quux => [4, 5] } ] }, 8 ],
967 [42, { foo => 'bar', quux => [1, 2, 3, { quux => [4, 5] } ] }, 8 ],
968 [42, { foo => 'bar', quux => [1, 2, \4, { quux => [4, 5] } ] }, 8 ],
969 [42, { foo => 'bar', quux => [1, 2, \3, { quuux => [4, 5] } ] }, 8 ],
970 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5, 6] } ] }, 8 ],
971 [42, { foo => 'bar', quux => [1, 2, \3, { quux => 4 } ] }, 8 ],
972 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5], quuux => 1 } ] }, 8 ],
973 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8, 9 ],
974 ]
975 },
976);
977
e7827ba2 978use_ok('SQL::Abstract::Test', import => [qw(
979 eq_sql_bind eq_sql eq_bind is_same_sql_bind
980)]);
32c34379 981
b4085a1a 982for my $test ( @sql_tests ) {
3b9d807e 983
984 # this does not work on 5.8.8 and earlier :(
985 #local @{*SQL::Abstract::Test::}{keys %{$test->{opts}}} = map { \$_ } values %{$test->{opts}}
986 # if $test->{opts};
987
988 my %restore_globals;
989
990 for (keys %{$test->{opts} || {} }) {
991 $restore_globals{$_} = ${${*SQL::Abstract::Test::}{$_}};
992 ${*SQL::Abstract::Test::}{$_} = \ do { my $cp = $test->{opts}{$_} };
993 }
994
25823711 995 my $statements = $test->{statements};
996 while (@$statements) {
997 my $sql1 = shift @$statements;
998 foreach my $sql2 (@$statements) {
e40f5df9 999
25823711 1000 my $equal = eq_sql($sql1, $sql2);
e40f5df9 1001
56c0595d 1002 TODO: {
1003 local $TODO = $test->{todo} if $test->{todo};
1004
1005 if ($test->{equal}) {
1006 ok($equal, "equal SQL expressions should have been considered equal");
1007 } else {
1008 ok(!$equal, "different SQL expressions should have been considered not equal");
1009 }
1010
1011 if ($equal ^ $test->{equal}) {
0769ac0e 1012 my ($ast1, $ast2) = map { SQL::Abstract::Test::parse ($_) } ($sql1, $sql2);
0769ac0e 1013 $_ = Dumper $_ for ($ast1, $ast2);
1014
b3b79607 1015 diag "sql1: $sql1";
1016 diag "sql2: $sql2";
1017 note $SQL::Abstract::Test::sql_differ;
1018 note "ast1: $ast1";
1019 note "ast2: $ast2";
56c0595d 1020 }
25823711 1021 }
1022 }
1023 }
3b9d807e 1024
1025 ${*SQL::Abstract::Test::}{$_} = \$restore_globals{$_}
1026 for keys %restore_globals;
25823711 1027}
1028
32c34379 1029for my $test (@bind_tests) {
1030 my $bindvals = $test->{bindvals};
1031 while (@$bindvals) {
1032 my $bind1 = shift @$bindvals;
1033 foreach my $bind2 (@$bindvals) {
1034 my $equal = eq_bind($bind1, $bind2);
1035 if ($test->{equal}) {
1036 ok($equal, "equal bind values considered equal");
1037 } else {
1038 ok(!$equal, "different bind values considered not equal");
1039 }
1040
1041 if ($equal ^ $test->{equal}) {
1042 diag("bind1: " . Dumper($bind1));
1043 diag("bind2: " . Dumper($bind2));
1044 }
1045 }
1046 }
1047}
e7827ba2 1048
1049ok(eq_sql_bind(
1050 "SELECT * FROM foo WHERE id = ?", [42],
1051 "SELECT * FROM foo WHERE (id = ?)", [42],
1052 ),
1053 "eq_sql_bind considers equal SQL expressions and bind values equal"
1054);
1055
1056
1057ok(!eq_sql_bind(
1058 "SELECT * FROM foo WHERE id = ?", [42],
1059 "SELECT * FROM foo WHERE (id = ?)", [0],
1060 ),
1061 "eq_sql_bind considers equal SQL expressions and different bind values different"
1062);
1063
1064ok(!eq_sql_bind(
1065 "SELECT * FROM foo WHERE id = ?", [42],
1066 "SELECT * FROM bar WHERE (id = ?)", [42],
1067 ),
1068 "eq_sql_bind considers different SQL expressions and equal bind values different"
1069);
6f2a5b66 1070
1071# test diag string
1072ok (! eq_sql (
1073 'SELECT owner_name FROM books me WHERE ( source = ? )',
1074 'SELECT owner_name FROM books me WHERE ( sUOrce = ? )',
1075));
1076like(
1077 $SQL::Abstract::Test::sql_differ,
1078 qr/\Q[ source ] != [ sUOrce ]/,
1079 'expected debug of literal diff',
1080);
1081
1082ok (! eq_sql (
1083 'SELECT owner_name FROM books me ORDER BY owner_name',
1084 'SELECT owner_name FROM books me GROUP BY owner_name',
1085));
1086like(
1087 $SQL::Abstract::Test::sql_differ,
1088 qr/\QOP [ORDER BY] != [GROUP BY]/,
1089 'expected debug of op diff',
1090);
1091
1092ok (! eq_sql (
1093 'SELECT owner_name FROM books WHERE ( source = ? )',
1094 'SELECT owner_name FROM books'
1095));
1096
1097like(
1098 $SQL::Abstract::Test::sql_differ,
1099 qr|\Q[WHERE source = ?] != [N/A]|,
1100 'expected debug of missing branch',
1101);
1102
10e6c946 1103done_testing;
Unnamed repository; edit this file 'description' to name the repository.