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