projects / scpubgit/Q-Branch.git / blame
? search:
summary | shortlog | log | commit | commitdiff | tree
blob | history | HEAD
Tokenizer fixed \o/
[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
7823ae89 13# equivalent to $Module::Install::AUTHOR
14my $author = (
15 ( not -d './inc' )
16 or
17 ( -e ($^O eq 'VMS' ? './inc/_author' : './inc/.author') )
18);
19
20if (not $author and not $ENV{SQLATEST_TESTER} and not $ENV{AUTOMATED_TESTING}) {
21 plan skip_all => 'Skipping resource intensive self-tests, use SQLATEST_TESTER=1 to run';
22}
23
25823711 24my @sql_tests = (
0769ac0e 25 # WHERE condition - equal
25823711 26 {
27 equal => 1,
28 statements => [
29 q/SELECT foo FROM bar WHERE a = 1/,
30 q/SELECT foo FROM bar WHERE a=1/,
31 q/SELECT foo FROM bar WHERE (a = 1)/,
32 q/SELECT foo FROM bar WHERE (a=1)/,
33 q/SELECT foo FROM bar WHERE ( a = 1 )/,
34 q/
35 SELECT
36 foo
37 FROM
38 bar
39 WHERE
40 a = 1
41 /,
42 q/
43 SELECT
44 foo
45 FROM
46 bar
47 WHERE
48 (a = 1)
49 /,
50 q/
51 SELECT
52 foo
53 FROM
54 bar
55 WHERE
56 ( a = 1 )
57 /,
58 q/SELECT foo FROM bar WHERE ((a = 1))/,
59 q/SELECT foo FROM bar WHERE ( (a = 1) )/,
60 q/SELECT foo FROM bar WHERE ( ( a = 1 ) )/,
61 ]
62 },
63 {
64 equal => 1,
65 statements => [
66 q/SELECT foo FROM bar WHERE a = 1 AND b = 1/,
67 q/SELECT foo FROM bar WHERE (a = 1) AND (b = 1)/,
68 q/SELECT foo FROM bar WHERE ((a = 1) AND (b = 1))/,
69 q/SELECT foo FROM bar WHERE (a = 1 AND b = 1)/,
70 q/SELECT foo FROM bar WHERE ((a = 1 AND b = 1))/,
71 q/SELECT foo FROM bar WHERE (((a = 1) AND (b = 1)))/,
72 q/
73 SELECT
74 foo
75 FROM
76 bar
77 WHERE
78 a = 1
79 AND
80 b = 1
81 /,
82 q/
83 SELECT
84 foo
85 FROM
86 bar
87 WHERE
88 (a = 1
89 AND
90 b = 1)
91 /,
92 q/
93 SELECT
94 foo
95 FROM
96 bar
97 WHERE
98 (a = 1)
99 AND
100 (b = 1)
101 /,
102 q/
103 SELECT
104 foo
105 FROM
106 bar
107 WHERE
108 ((a = 1)
109 AND
110 (b = 1))
111 /,
112 ]
113 },
56c0595d 114 {
115 equal => 1,
56c0595d 116 statements => [
117 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 AND c = 1/,
e40f5df9 118 q/SELECT foo FROM bar WHERE (a = 1 AND b = 1 AND c = 1)/,
56c0595d 119 q/SELECT foo FROM bar WHERE (a = 1 AND b = 1) AND c = 1/,
120 q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 AND c = 1)/,
1b17d1b0 121 q/SELECT foo FROM bar WHERE ((((a = 1))) AND (b = 1 AND c = 1))/,
56c0595d 122 ]
123 },
124 {
125 equal => 1,
56c0595d 126 statements => [
127 q/SELECT foo FROM bar WHERE a = 1 OR b = 1 OR c = 1/,
128 q/SELECT foo FROM bar WHERE (a = 1 OR b = 1) OR c = 1/,
129 q/SELECT foo FROM bar WHERE a = 1 OR (b = 1 OR c = 1)/,
1b17d1b0 130 q/SELECT foo FROM bar WHERE a = 1 OR ((b = 1 OR (c = 1)))/,
131 ]
132 },
133 {
134 equal => 1,
135 statements => [
136 q/SELECT foo FROM bar WHERE (a = 1) AND (b = 1 OR c = 1 OR d = 1) AND (e = 1 AND f = 1)/,
137 q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 OR c = 1 OR d = 1) AND e = 1 AND (f = 1)/,
138 q/SELECT foo FROM bar WHERE ( ((a = 1) AND ( b = 1 OR (c = 1 OR d = 1) )) AND ((e = 1)) AND f = 1) /,
56c0595d 139 ]
140 },
e40f5df9 141 {
9e8dab3f 142 equal => 1,
143 statements => [
144 q/SELECT foo FROM bar WHERE (a) AND (b = 2)/,
145 q/SELECT foo FROM bar WHERE (a AND b = 2)/,
146 q/SELECT foo FROM bar WHERE (a AND (b = 2))/,
147 q/SELECT foo FROM bar WHERE a AND (b = 2)/,
148 ]
149 },
150 {
277b5d3f 151 equal => 1,
152 statements => [
153 q/SELECT foo FROM bar WHERE ((NOT a) AND b = 2)/,
154 q/SELECT foo FROM bar WHERE (NOT a) AND (b = 2)/,
155 q/SELECT foo FROM bar WHERE (NOT (a)) AND b = 2/,
156 ],
157 },
158 {
9e8dab3f 159 equal => 0,
160 statements => [
161 q/SELECT foo FROM bar WHERE NOT a AND (b = 2)/,
162 q/SELECT foo FROM bar WHERE (NOT a) AND (b = 2)/,
163 ]
164 },
165 {
e40f5df9 166 equal => 0,
167 parenthesis_significant => 1,
168 statements => [
169 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 AND c = 1/,
170 q/SELECT foo FROM bar WHERE (a = 1 AND b = 1 AND c = 1)/,
171 q/SELECT foo FROM bar WHERE (a = 1 AND b = 1) AND c = 1/,
172 q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 AND c = 1)/,
173 q/SELECT foo FROM bar WHERE ((((a = 1))) AND (b = 1 AND c = 1))/,
174 ]
175 },
176 {
177 equal => 0,
178 parenthesis_significant => 1,
179 statements => [
180 q/SELECT foo FROM bar WHERE a = 1 OR b = 1 OR c = 1/,
181 q/SELECT foo FROM bar WHERE (a = 1 OR b = 1) OR c = 1/,
182 q/SELECT foo FROM bar WHERE a = 1 OR (b = 1 OR c = 1)/,
183 q/SELECT foo FROM bar WHERE a = 1 OR ((b = 1 OR (c = 1)))/,
184 ]
185 },
186 {
187 equal => 0,
188 parenthesis_significant => 1,
189 statements => [
190 q/SELECT foo FROM bar WHERE (a = 1) AND (b = 1 OR c = 1 OR d = 1) AND (e = 1 AND f = 1)/,
191 q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 OR c = 1 OR d = 1) AND e = 1 AND (f = 1)/,
192 q/SELECT foo FROM bar WHERE ( ((a = 1) AND ( b = 1 OR (c = 1 OR d = 1) )) AND ((e = 1)) AND f = 1) /,
193 ]
194 },
25823711 195
196 # WHERE condition - different
197 {
198 equal => 0,
199 statements => [
200 q/SELECT foo FROM bar WHERE a = 1/,
201 q/SELECT quux FROM bar WHERE a = 1/,
202 q/SELECT foo FROM quux WHERE a = 1/,
203 q/FOOBAR foo FROM bar WHERE a = 1/,
204
205 q/SELECT foo FROM bar WHERE a = 2/,
206 q/SELECT foo FROM bar WHERE a < 1/,
207 q/SELECT foo FROM bar WHERE b = 1/,
208 q/SELECT foo FROM bar WHERE (c = 1)/,
209 q/SELECT foo FROM bar WHERE (d = 1)/,
210
211 q/SELECT foo FROM bar WHERE a = 1 AND quux/,
212 q/SELECT foo FROM bar WHERE a = 1 GROUP BY foo/,
213 q/SELECT foo FROM bar WHERE a = 1 ORDER BY foo/,
214 q/SELECT foo FROM bar WHERE a = 1 LIMIT 1/,
215 q/SELECT foo FROM bar WHERE a = 1 OFFSET 1/,
216 q/SELECT foo FROM bar JOIN quux WHERE a = 1/,
217 q/SELECT foo FROM bar JOIN quux ON a = 1 WHERE a = 1/,
218 ]
219 },
220 {
221 equal => 0,
222 statements => [
223 q/SELECT foo FROM bar WHERE a = 1 AND b = 1/,
224 q/SELECT quux FROM bar WHERE a = 1 AND b = 1/,
225 q/SELECT foo FROM quux WHERE a = 1 AND b = 1/,
226 q/FOOBAR foo FROM bar WHERE a = 1 AND b = 1/,
227
228 q/SELECT foo FROM bar WHERE a = 2 AND b = 1/,
229 q/SELECT foo FROM bar WHERE a = 3 AND (b = 1)/,
230 q/SELECT foo FROM bar WHERE (a = 4) AND b = 1/,
231 q/SELECT foo FROM bar WHERE (a = 5) AND (b = 1)/,
232 q/SELECT foo FROM bar WHERE ((a = 6) AND (b = 1))/,
233 q/SELECT foo FROM bar WHERE ((a = 7) AND (b = 1))/,
234
235 q/SELECT foo FROM bar WHERE a = 1 AND b = 2/,
236 q/SELECT foo FROM bar WHERE a = 1 AND (b = 3)/,
237 q/SELECT foo FROM bar WHERE (a = 1) AND b = 4/,
238 q/SELECT foo FROM bar WHERE (a = 1) AND (b = 5)/,
239 q/SELECT foo FROM bar WHERE ((a = 1) AND (b = 6))/,
240 q/SELECT foo FROM bar WHERE ((a = 1) AND (b = 7))/,
241
242 q/SELECT foo FROM bar WHERE a < 1 AND b = 1/,
243 q/SELECT foo FROM bar WHERE b = 1 AND b = 1/,
244 q/SELECT foo FROM bar WHERE (c = 1) AND b = 1/,
245 q/SELECT foo FROM bar WHERE (d = 1) AND b = 1/,
246
247 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 AND quux/,
248 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 GROUP BY foo/,
249 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 ORDER BY foo/,
250 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 LIMIT 1/,
251 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 OFFSET 1/,
252 q/SELECT foo FROM bar JOIN quux WHERE a = 1 AND b = 1/,
253 q/SELECT foo FROM bar JOIN quux ON a = 1 WHERE a = 1 AND b = 1/,
254 ]
255 },
56c0595d 256 {
257 equal => 0,
258 statements => [
259 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 OR c = 1/,
260 q/SELECT foo FROM bar WHERE (a = 1 AND b = 1) OR c = 1/,
261 q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 OR c = 1)/,
262 ]
263 },
264 {
265 equal => 0,
266 statements => [
267 q/SELECT foo FROM bar WHERE a = 1 OR b = 1 AND c = 1/,
268 q/SELECT foo FROM bar WHERE (a = 1 OR b = 1) AND c = 1/,
269 q/SELECT foo FROM bar WHERE a = 1 OR (b = 1 AND c = 1)/,
270 ]
271 },
b6c8b01b 272 {
273 equal => 0,
14190bdf 274 parenthesis_significant => 1,
275 statements => [
276 q/SELECT foo FROM bar WHERE a IN (1,2,3)/,
277 q/SELECT foo FROM bar WHERE a IN (1,3,2)/,
278 q/SELECT foo FROM bar WHERE a IN ((1,2,3))/,
279 ]
280 },
281 {
282 equal => 0,
b6c8b01b 283 statements => [
01b64cb7 284 # BETWEEN with/without parenthesis around itself/RHS is a sticky business
285 # if I made a mistake here, simply rewrite the special BETWEEN handling in
286 # _recurse_parse()
287 #
288 # by RIBASUSHI
b6c8b01b 289 q/SELECT foo FROM bar WHERE ( completion_date BETWEEN ? AND ? AND status = ? )/,
01b64cb7 290 q/SELECT foo FROM bar WHERE completion_date BETWEEN (? AND ?) AND status = ?/,
291 q/SELECT foo FROM bar WHERE ( (completion_date BETWEEN (? AND ?) ) AND status = ? )/,
292 q/SELECT foo FROM bar WHERE ( (completion_date BETWEEN (? AND ? AND status = ?) ) )/,
b6c8b01b 293 ]
294 },
25823711 295
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
595 # list permutations
9a5b64c9 596 {
f8135ff3 597 equal => 0,
598 statements => [
599 'SELECT a,b,c FROM foo',
600 'SELECT a,c,b FROM foo',
601 'SELECT b,a,c FROM foo',
602 'SELECT b,c,a FROM foo',
603 'SELECT c,a,b FROM foo',
604 'SELECT c,b,a FROM foo',
605 ],
9a5b64c9 606 },
607 {
f8135ff3 608 equal => 0,
609 statements => [
610 'SELECT * FROM foo WHERE a IN (1,2,3)',
611 'SELECT * FROM foo WHERE a IN (1,3,2)',
612 'SELECT * FROM foo WHERE a IN (2,1,3)',
613 'SELECT * FROM foo WHERE a IN (2,3,1)',
614 'SELECT * FROM foo WHERE a IN (3,1,2)',
615 'SELECT * FROM foo WHERE a IN (3,2,1)',
616 ]
76f10e1c 617 },
618 {
f8135ff3 619 equal => 0,
620 statements => [
621 'SELECT count(*) FROM foo',
622 'SELECT count(*) AS bar FROM foo',
623 'SELECT count(*) AS "bar" FROM foo',
624 'SELECT count(a) FROM foo',
625 'SELECT count(1) FROM foo',
626 ]
9a5b64c9 627 },
0769ac0e 628 # func
629 {
630 equal => 1,
631 statements => [
632 'SELECT foo() bar FROM baz',
633 'SELECT foo ( )bar FROM baz',
634 'SELECT foo (())bar FROM baz',
635 'SELECT foo(( ) ) bar FROM baz',
636 ]
637 },
638 {
639 equal => 0,
640 statements => [
641 'SELECT foo() FROM bar',
642 'SELECT foo FROM bar',
643 'SELECT foo FROM bar ()',
644 ]
645 },
646 # math
647 {
648 equal => 0,
649 statements => [
650 'SELECT * FROM foo WHERE 1 = ( a > b)',
651 'SELECT * FROM foo WHERE 1 = a > b',
652 'SELECT * FROM foo WHERE (1 = a) > b',
653 ]
654 },
655 {
656 equal => 1,
657 statements => [
658 'SELECT * FROM foo WHERE bar = baz(buzz)',
659 'SELECT * FROM foo WHERE bar = (baz( buzz ))',
660 ]
661 },
25823711 662);
663
32c34379 664my @bind_tests = (
665 # scalar - equal
666 {
667 equal => 1,
668 bindvals => [
669 undef,
670 undef,
671 ]
672 },
673 {
674 equal => 1,
675 bindvals => [
676 'foo',
677 'foo',
678 ]
679 },
680 {
681 equal => 1,
682 bindvals => [
683 42,
684 42,
685 '42',
686 ]
687 },
688
689 # scalarref - equal
690 {
691 equal => 1,
692 bindvals => [
693 \'foo',
694 \'foo',
695 ]
696 },
697 {
698 equal => 1,
699 bindvals => [
700 \42,
701 \42,
702 \'42',
703 ]
704 },
705
706 # arrayref - equal
707 {
708 equal => 1,
709 bindvals => [
710 [],
711 []
712 ]
713 },
714 {
715 equal => 1,
716 bindvals => [
717 [42],
718 [42],
719 ['42'],
720 ]
721 },
722 {
723 equal => 1,
724 bindvals => [
725 [1, 42],
726 [1, 42],
727 ['1', 42],
728 [1, '42'],
729 ['1', '42'],
730 ]
731 },
732
733 # hashref - equal
734 {
735 equal => 1,
736 bindvals => [
737 { foo => 42 },
738 { foo => 42 },
739 { foo => '42' },
740 ]
741 },
742 {
743 equal => 1,
744 bindvals => [
745 { foo => 42, bar => 1 },
746 { foo => 42, bar => 1 },
747 { foo => '42', bar => 1 },
748 ]
749 },
750
751 # blessed object - equal
752 {
753 equal => 1,
754 bindvals => [
755 bless(\(local $_ = 42), 'Life::Universe::Everything'),
756 bless(\(local $_ = 42), 'Life::Universe::Everything'),
757 ]
758 },
759 {
760 equal => 1,
761 bindvals => [
762 bless([42], 'Life::Universe::Everything'),
763 bless([42], 'Life::Universe::Everything'),
764 ]
765 },
766 {
767 equal => 1,
768 bindvals => [
769 bless({ answer => 42 }, 'Life::Universe::Everything'),
770 bless({ answer => 42 }, 'Life::Universe::Everything'),
771 ]
772 },
773
774 # complex data structure - equal
775 {
776 equal => 1,
777 bindvals => [
778 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
779 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
780 ]
781 },
782
783
784 # scalar - different
785 {
786 equal => 0,
787 bindvals => [
788 undef,
789 'foo',
790 42,
791 ]
792 },
793
794 # scalarref - different
795 {
796 equal => 0,
797 bindvals => [
798 \undef,
799 \'foo',
800 \42,
801 ]
802 },
803
804 # arrayref - different
805 {
806 equal => 0,
807 bindvals => [
808 [undef],
809 ['foo'],
810 [42],
811 ]
812 },
813
814 # hashref - different
815 {
816 equal => 0,
817 bindvals => [
818 { foo => undef },
819 { foo => 'bar' },
820 { foo => 42 },
821 ]
822 },
823
824 # different types
825 {
826 equal => 0,
827 bindvals => [
828 'foo',
829 \'foo',
830 ['foo'],
831 { foo => 'bar' },
832 ]
833 },
834
835 # complex data structure - different
836 {
837 equal => 0,
838 bindvals => [
839 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
840 [43, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
841 [42, { foo => 'baz', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
842 [42, { bar => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
843 [42, { foo => 'bar', quuux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
844 [42, { foo => 'bar', quux => [0, 1, 2, \3, { quux => [4, 5] } ] }, 8 ],
845 [42, { foo => 'bar', quux => [1, 2, 3, { quux => [4, 5] } ] }, 8 ],
846 [42, { foo => 'bar', quux => [1, 2, \4, { quux => [4, 5] } ] }, 8 ],
847 [42, { foo => 'bar', quux => [1, 2, \3, { quuux => [4, 5] } ] }, 8 ],
848 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5, 6] } ] }, 8 ],
849 [42, { foo => 'bar', quux => [1, 2, \3, { quux => 4 } ] }, 8 ],
850 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5], quuux => 1 } ] }, 8 ],
851 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8, 9 ],
852 ]
853 },
854);
855
25823711 856plan tests => 1 +
857 sum(
858 map { $_ * ($_ - 1) / 2 }
859 map { scalar @{$_->{statements}} }
860 @sql_tests
861 ) +
862 sum(
863 map { $_ * ($_ - 1) / 2 }
864 map { scalar @{$_->{bindvals}} }
865 @bind_tests
e7827ba2 866 ) +
867 3;
32c34379 868
e7827ba2 869use_ok('SQL::Abstract::Test', import => [qw(
870 eq_sql_bind eq_sql eq_bind is_same_sql_bind
871)]);
32c34379 872
25823711 873for my $test (@sql_tests) {
874 my $statements = $test->{statements};
875 while (@$statements) {
876 my $sql1 = shift @$statements;
877 foreach my $sql2 (@$statements) {
e40f5df9 878
879 no warnings qw/once/; # perl 5.10 is dumb
880 local $SQL::Abstract::Test::parenthesis_significant = $test->{parenthesis_significant}
881 if $test->{parenthesis_significant};
25823711 882 my $equal = eq_sql($sql1, $sql2);
e40f5df9 883
56c0595d 884 TODO: {
885 local $TODO = $test->{todo} if $test->{todo};
886
887 if ($test->{equal}) {
888 ok($equal, "equal SQL expressions should have been considered equal");
889 } else {
890 ok(!$equal, "different SQL expressions should have been considered not equal");
891 }
892
893 if ($equal ^ $test->{equal}) {
0769ac0e 894 my ($ast1, $ast2) = map { SQL::Abstract::Test::parse ($_) } ($sql1, $sql2);
895
896 $_ = Dumper $_ for ($ast1, $ast2);
897
56c0595d 898 diag("sql1: $sql1");
899 diag("sql2: $sql2");
0769ac0e 900 note("ast1: $ast1");
901 note("ast2: $ast2");
56c0595d 902 }
25823711 903 }
904 }
905 }
906}
907
32c34379 908for my $test (@bind_tests) {
909 my $bindvals = $test->{bindvals};
910 while (@$bindvals) {
911 my $bind1 = shift @$bindvals;
912 foreach my $bind2 (@$bindvals) {
913 my $equal = eq_bind($bind1, $bind2);
914 if ($test->{equal}) {
915 ok($equal, "equal bind values considered equal");
916 } else {
917 ok(!$equal, "different bind values considered not equal");
918 }
919
920 if ($equal ^ $test->{equal}) {
921 diag("bind1: " . Dumper($bind1));
922 diag("bind2: " . Dumper($bind2));
923 }
924 }
925 }
926}
e7827ba2 927
928ok(eq_sql_bind(
929 "SELECT * FROM foo WHERE id = ?", [42],
930 "SELECT * FROM foo WHERE (id = ?)", [42],
931 ),
932 "eq_sql_bind considers equal SQL expressions and bind values equal"
933);
934
935
936ok(!eq_sql_bind(
937 "SELECT * FROM foo WHERE id = ?", [42],
938 "SELECT * FROM foo WHERE (id = ?)", [0],
939 ),
940 "eq_sql_bind considers equal SQL expressions and different bind values different"
941);
942
943ok(!eq_sql_bind(
944 "SELECT * FROM foo WHERE id = ?", [42],
945 "SELECT * FROM bar WHERE (id = ?)", [42],
946 ),
947 "eq_sql_bind considers different SQL expressions and equal bind values different"
948);
Unnamed repository; edit this file 'description' to name the repository.