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