projects / dbsrgits/SQL-Abstract.git / blame
? search:
summary | shortlog | log | commit | commitdiff | tree
blob | history | HEAD
Merge 'trunk' into 'test_refactor'
[dbsrgits/SQL-Abstract.git] / t / 10test.t
CommitLineData
32c34379 1#!/usr/bin/perl
2
3use strict;
4use warnings;
5use List::Util qw(sum);
32c34379 6
7use Test::More;
8
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 {
9e8dab3f 139 equal => 1,
140 statements => [
141 q/SELECT foo FROM bar WHERE (a) AND (b = 2)/,
142 q/SELECT foo FROM bar WHERE (a AND b = 2)/,
143 q/SELECT foo FROM bar WHERE (a AND (b = 2))/,
144 q/SELECT foo FROM bar WHERE a AND (b = 2)/,
145 ]
146 },
147 {
277b5d3f 148 equal => 1,
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 q/SELECT foo FROM bar WHERE (NOT (a)) AND b = 2/,
153 ],
154 },
155 {
9e8dab3f 156 equal => 0,
157 statements => [
158 q/SELECT foo FROM bar WHERE NOT a AND (b = 2)/,
159 q/SELECT foo FROM bar WHERE (NOT a) AND (b = 2)/,
160 ]
161 },
162 {
e40f5df9 163 equal => 0,
164 parenthesis_significant => 1,
165 statements => [
166 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 AND c = 1/,
167 q/SELECT foo FROM bar WHERE (a = 1 AND b = 1 AND c = 1)/,
168 q/SELECT foo FROM bar WHERE (a = 1 AND b = 1) AND c = 1/,
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 ]
172 },
173 {
174 equal => 0,
175 parenthesis_significant => 1,
176 statements => [
177 q/SELECT foo FROM bar WHERE a = 1 OR b = 1 OR c = 1/,
178 q/SELECT foo FROM bar WHERE (a = 1 OR b = 1) OR c = 1/,
179 q/SELECT foo FROM bar WHERE a = 1 OR (b = 1 OR c = 1)/,
180 q/SELECT foo FROM bar WHERE a = 1 OR ((b = 1 OR (c = 1)))/,
181 ]
182 },
183 {
184 equal => 0,
185 parenthesis_significant => 1,
186 statements => [
187 q/SELECT foo FROM bar WHERE (a = 1) AND (b = 1 OR c = 1 OR d = 1) AND (e = 1 AND f = 1)/,
188 q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 OR c = 1 OR d = 1) AND e = 1 AND (f = 1)/,
189 q/SELECT foo FROM bar WHERE ( ((a = 1) AND ( b = 1 OR (c = 1 OR d = 1) )) AND ((e = 1)) AND f = 1) /,
190 ]
191 },
25823711 192
193 # WHERE condition - different
194 {
195 equal => 0,
196 statements => [
197 q/SELECT foo FROM bar WHERE a = 1/,
198 q/SELECT quux FROM bar WHERE a = 1/,
199 q/SELECT foo FROM quux WHERE a = 1/,
200 q/FOOBAR foo FROM bar WHERE a = 1/,
201
202 q/SELECT foo FROM bar WHERE a = 2/,
203 q/SELECT foo FROM bar WHERE a < 1/,
204 q/SELECT foo FROM bar WHERE b = 1/,
205 q/SELECT foo FROM bar WHERE (c = 1)/,
206 q/SELECT foo FROM bar WHERE (d = 1)/,
207
208 q/SELECT foo FROM bar WHERE a = 1 AND quux/,
209 q/SELECT foo FROM bar WHERE a = 1 GROUP BY foo/,
210 q/SELECT foo FROM bar WHERE a = 1 ORDER BY foo/,
211 q/SELECT foo FROM bar WHERE a = 1 LIMIT 1/,
212 q/SELECT foo FROM bar WHERE a = 1 OFFSET 1/,
213 q/SELECT foo FROM bar JOIN quux WHERE a = 1/,
214 q/SELECT foo FROM bar JOIN quux ON a = 1 WHERE a = 1/,
215 ]
216 },
217 {
218 equal => 0,
219 statements => [
220 q/SELECT foo FROM bar WHERE a = 1 AND b = 1/,
221 q/SELECT quux FROM bar WHERE a = 1 AND b = 1/,
222 q/SELECT foo FROM quux WHERE a = 1 AND b = 1/,
223 q/FOOBAR foo FROM bar WHERE a = 1 AND b = 1/,
224
225 q/SELECT foo FROM bar WHERE a = 2 AND b = 1/,
226 q/SELECT foo FROM bar WHERE a = 3 AND (b = 1)/,
227 q/SELECT foo FROM bar WHERE (a = 4) AND b = 1/,
228 q/SELECT foo FROM bar WHERE (a = 5) AND (b = 1)/,
229 q/SELECT foo FROM bar WHERE ((a = 6) AND (b = 1))/,
230 q/SELECT foo FROM bar WHERE ((a = 7) AND (b = 1))/,
231
232 q/SELECT foo FROM bar WHERE a = 1 AND b = 2/,
233 q/SELECT foo FROM bar WHERE a = 1 AND (b = 3)/,
234 q/SELECT foo FROM bar WHERE (a = 1) AND b = 4/,
235 q/SELECT foo FROM bar WHERE (a = 1) AND (b = 5)/,
236 q/SELECT foo FROM bar WHERE ((a = 1) AND (b = 6))/,
237 q/SELECT foo FROM bar WHERE ((a = 1) AND (b = 7))/,
238
239 q/SELECT foo FROM bar WHERE a < 1 AND b = 1/,
240 q/SELECT foo FROM bar WHERE b = 1 AND b = 1/,
241 q/SELECT foo FROM bar WHERE (c = 1) AND b = 1/,
242 q/SELECT foo FROM bar WHERE (d = 1) AND b = 1/,
243
244 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 AND quux/,
245 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 GROUP BY foo/,
246 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 ORDER BY foo/,
247 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 LIMIT 1/,
248 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 OFFSET 1/,
249 q/SELECT foo FROM bar JOIN quux WHERE a = 1 AND b = 1/,
250 q/SELECT foo FROM bar JOIN quux ON a = 1 WHERE a = 1 AND b = 1/,
251 ]
252 },
56c0595d 253 {
254 equal => 0,
255 statements => [
256 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 OR c = 1/,
257 q/SELECT foo FROM bar WHERE (a = 1 AND b = 1) OR c = 1/,
258 q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 OR c = 1)/,
259 ]
260 },
261 {
262 equal => 0,
263 statements => [
264 q/SELECT foo FROM bar WHERE a = 1 OR b = 1 AND c = 1/,
265 q/SELECT foo FROM bar WHERE (a = 1 OR b = 1) AND c = 1/,
266 q/SELECT foo FROM bar WHERE a = 1 OR (b = 1 AND c = 1)/,
267 ]
268 },
b6c8b01b 269 {
270 equal => 0,
271 statements => [
01b64cb7 272 # BETWEEN with/without parenthesis around itself/RHS is a sticky business
273 # if I made a mistake here, simply rewrite the special BETWEEN handling in
274 # _recurse_parse()
275 #
276 # by RIBASUSHI
b6c8b01b 277 q/SELECT foo FROM bar WHERE ( completion_date BETWEEN ? AND ? AND status = ? )/,
01b64cb7 278 q/SELECT foo FROM bar WHERE completion_date BETWEEN (? AND ?) AND status = ?/,
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 = ?) ) )/,
b6c8b01b 281 ]
282 },
25823711 283
284 # JOIN condition - equal
285 {
286 equal => 1,
287 statements => [
288 q/SELECT foo FROM bar JOIN baz ON a = 1 WHERE x = 1/,
289 q/SELECT foo FROM bar JOIN baz ON a=1 WHERE x = 1/,
290 q/SELECT foo FROM bar JOIN baz ON (a = 1) WHERE x = 1/,
291 q/SELECT foo FROM bar JOIN baz ON (a=1) WHERE x = 1/,
292 q/SELECT foo FROM bar JOIN baz ON ( a = 1 ) WHERE x = 1/,
293 q/
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/
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/SELECT foo FROM bar JOIN baz ON ((a = 1)) WHERE x = 1/,
330 q/SELECT foo FROM bar JOIN baz ON ( (a = 1) ) WHERE x = 1/,
331 q/SELECT foo FROM bar JOIN baz ON ( ( a = 1 ) ) WHERE x = 1/,
332 ]
333 },
334 {
335 equal => 1,
336 statements => [
337 q/SELECT foo FROM bar JOIN baz ON a = 1 AND b = 1 WHERE x = 1/,
338 q/SELECT foo FROM bar JOIN baz ON (a = 1) AND (b = 1) WHERE x = 1/,
339 q/SELECT foo FROM bar JOIN baz ON ((a = 1) AND (b = 1)) WHERE x = 1/,
340 q/SELECT foo FROM bar JOIN baz ON (a = 1 AND b = 1) WHERE x = 1/,
341 q/SELECT foo FROM bar JOIN baz ON ((a = 1 AND b = 1)) WHERE x = 1/,
342 q/SELECT foo FROM bar JOIN baz ON (((a = 1) AND (b = 1))) WHERE x = 1/,
343 q/
344 SELECT
345 foo
346 FROM
347 bar
348 JOIN
349 baz
350 ON
351 a = 1
352 AND
353 b = 1
354 WHERE
355 x = 1
356 /,
357 q/
358 SELECT
359 foo
360 FROM
361 bar
362 JOIN
363 baz
364 ON
365 (a = 1
366 AND
367 b = 1)
368 WHERE
369 x = 1
370 /,
371 q/
372 SELECT
373 foo
374 FROM
375 bar
376 JOIN
377 baz
378 ON
379 (a = 1)
380 AND
381 (b = 1)
382 WHERE
383 x = 1
384 /,
385 q/
386 SELECT
387 foo
388 FROM
389 bar
390 JOIN
391 baz
392 ON
393 ((a = 1)
394 AND
395 (b = 1))
396 WHERE
397 x = 1
398 /,
399 ]
400 },
401
402 # JOIN condition - different
403 {
404 equal => 0,
405 statements => [
406 q/SELECT foo FROM bar JOIN quux ON a = 1 WHERE quuux/,
407 q/SELECT quux FROM bar JOIN quux ON a = 1 WHERE quuux/,
408 q/SELECT foo FROM quux JOIN quux ON a = 1 WHERE quuux/,
409 q/FOOBAR foo FROM bar JOIN quux ON a = 1 WHERE quuux/,
410
411 q/SELECT foo FROM bar JOIN quux ON a = 2 WHERE quuux/,
412 q/SELECT foo FROM bar JOIN quux ON a < 1 WHERE quuux/,
413 q/SELECT foo FROM bar JOIN quux ON b = 1 WHERE quuux/,
414 q/SELECT foo FROM bar JOIN quux ON (c = 1) WHERE quuux/,
415 q/SELECT foo FROM bar JOIN quux ON (d = 1) WHERE quuux/,
416
417 q/SELECT foo FROM bar JOIN quux ON a = 1 AND quuux/,
418 q/SELECT foo FROM bar JOIN quux ON a = 1 GROUP BY foo/,
419 q/SELECT foo FROM bar JOIN quux ON a = 1 ORDER BY foo/,
420 q/SELECT foo FROM bar JOIN quux ON a = 1 LIMIT 1/,
421 q/SELECT foo FROM bar JOIN quux ON a = 1 OFFSET 1/,
422 q/SELECT foo FROM bar JOIN quux ON a = 1 JOIN quuux/,
423 q/SELECT foo FROM bar JOIN quux ON a = 1 JOIN quuux ON a = 1/,
424 ]
425 },
426 {
427 equal => 0,
428 statements => [
429 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 WHERE quuux/,
430 q/SELECT quux FROM bar JOIN quux ON a = 1 AND b = 1 WHERE quuux/,
431 q/SELECT foo FROM quux JOIN quux ON a = 1 AND b = 1 WHERE quuux/,
432 q/FOOBAR foo FROM bar JOIN quux ON a = 1 AND b = 1 WHERE quuux/,
433
434 q/SELECT foo FROM bar JOIN quux ON a = 2 AND b = 1 WHERE quuux/,
435 q/SELECT foo FROM bar JOIN quux ON a = 3 AND (b = 1) WHERE quuux/,
436 q/SELECT foo FROM bar JOIN quux ON (a = 4) AND b = 1 WHERE quuux/,
437 q/SELECT foo FROM bar JOIN quux ON (a = 5) AND (b = 1) WHERE quuux/,
438 q/SELECT foo FROM bar JOIN quux ON ((a = 6) AND (b = 1)) WHERE quuux/,
439 q/SELECT foo FROM bar JOIN quux ON ((a = 7) AND (b = 1)) WHERE quuux/,
440
441 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 2 WHERE quuux/,
442 q/SELECT foo FROM bar JOIN quux ON a = 1 AND (b = 3) WHERE quuux/,
443 q/SELECT foo FROM bar JOIN quux ON (a = 1) AND b = 4 WHERE quuux/,
444 q/SELECT foo FROM bar JOIN quux ON (a = 1) AND (b = 5) WHERE quuux/,
445 q/SELECT foo FROM bar JOIN quux ON ((a = 1) AND (b = 6)) WHERE quuux/,
446 q/SELECT foo FROM bar JOIN quux ON ((a = 1) AND (b = 7)) WHERE quuux/,
447
448 q/SELECT foo FROM bar JOIN quux ON a < 1 AND b = 1 WHERE quuux/,
449 q/SELECT foo FROM bar JOIN quux ON b = 1 AND b = 1 WHERE quuux/,
450 q/SELECT foo FROM bar JOIN quux ON (c = 1) AND b = 1 WHERE quuux/,
451 q/SELECT foo FROM bar JOIN quux ON (d = 1) AND b = 1 WHERE quuux/,
452
453 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 AND quuux/,
454 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 GROUP BY foo/,
455 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 ORDER BY foo/,
456 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 LIMIT 1/,
457 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 OFFSET 1/,
458 q/SELECT foo FROM bar JOIN quux JOIN quuux ON a = 1 AND b = 1/,
459 q/SELECT foo FROM bar JOIN quux ON a = 1 JOIN quuux ON a = 1 AND b = 1/,
460 ]
461 },
462
463 # DISTINCT ON (...) not confused with JOIN ON (...)
464 {
465 equal => 1,
466 statements => [
467 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE a = 1/,
468 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE a=1/,
469 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE (a = 1)/,
470 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE (a=1)/,
471 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ( a = 1 )/,
472 q/
473 SELECT DISTINCT ON (foo, quux)
474 foo,
475 quux
476 FROM
477 bar
478 WHERE
479 a = 1
480 /,
481 q/
482 SELECT DISTINCT ON (foo, quux)
483 foo,
484 quux
485 FROM
486 bar
487 WHERE
488 (a = 1)
489 /,
490 q/
491 SELECT DISTINCT ON (foo, quux)
492 foo,
493 quux
494 FROM
495 bar
496 WHERE
497 ( a = 1 )
498 /,
499 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ((a = 1))/,
500 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ( (a = 1) )/,
501 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ( ( a = 1 ) )/,
502 ]
503 },
504
505 # subselects - equal
506 {
507 equal => 1,
508 statements => [
509 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE a = 1/,
510 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE (a = 1)/,
511 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE a = 1/,
512 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE (a = 1)/,
513 ]
514 },
515 {
516 equal => 1,
517 statements => [
518 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE a = 1/,
519 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 1)) AS foo WHERE a = 1/,
520 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 1) AS foo WHERE a = 1/,
521 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 1)) AS foo WHERE a = 1/,
522 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 1))) AS foo WHERE a = 1/,
523
524 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE (a = 1)/,
525 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 1)) AS foo WHERE (a = 1)/,
526 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 1) AS foo WHERE (a = 1)/,
527 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 1)) AS foo WHERE (a = 1)/,
528 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 1))) AS foo WHERE (a = 1)/,
529 ]
530 },
531
532 # subselects - different
533 {
534 equal => 0,
535 statements => [
536 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE a = 1/,
537 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE a = 2/,
538 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE (a = 3)/,
539 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE a = 4/,
540 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE (a = 5)/,
541 q/SELECT * FROM (SELECT * FROM bar WHERE b = 2) AS foo WHERE a = 1/,
542 q/SELECT * FROM (SELECT * FROM bar WHERE b = 3) AS foo WHERE (a = 1)/,
543 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 4)) AS foo WHERE a = 1/,
544 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 5)) AS foo WHERE (a = 1)/,
545 ]
546 },
547 {
548 equal => 0,
549 statements => [
550 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE a = 1/,
551 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 2) AS foo WHERE a = 1/,
552 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 3)) AS foo WHERE a = 1/,
553 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 4)) AS foo WHERE a = 1/,
554 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 5))) AS foo WHERE a = 1/,
555
556 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 6) AS foo WHERE (a = 1)/,
557 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 7) AS foo WHERE (a = 1)/,
558 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 8)) AS foo WHERE (a = 1)/,
559 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 9)) AS foo WHERE (a = 1)/,
560 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 10))) AS foo WHERE (a = 1)/,
561
562 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE a = 2/,
563 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 2) AS foo WHERE a = 2/,
564 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 3)) AS foo WHERE a = 2/,
565 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 4)) AS foo WHERE a = 2/,
566 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 5))) AS foo WHERE a = 2/,
567
568 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 6) AS foo WHERE (a = 2)/,
569 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 7) AS foo WHERE (a = 2)/,
570 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 8)) AS foo WHERE (a = 2)/,
571 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 9)) AS foo WHERE (a = 2)/,
572 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 10))) AS foo WHERE (a = 2)/,
573 ]
574 },
9a5b64c9 575 {
576 equal => 0,
577 statements => [
578 'SELECT a,b,c FROM foo',
579 'SELECT a,c,b FROM foo',
580 'SELECT b,a,c FROM foo',
581 'SELECT b,c,a FROM foo',
582 'SELECT c,a,b FROM foo',
583 'SELECT c,b,a FROM foo',
584 ]
585 },
586 {
587 equal => 0,
588 statements => [
76f10e1c 589 'SELECT count(*) FROM foo',
590 'SELECT count(a) FROM foo',
591 'SELECT count(1) FROM foo',
592 ]
593 },
594 {
595 equal => 0,
596 statements => [
9a5b64c9 597 'SELECT * FROM foo WHERE a IN (1,2,3)',
598 'SELECT * FROM foo WHERE a IN (1,3,2)',
599 'SELECT * FROM foo WHERE a IN (2,1,3)',
600 'SELECT * FROM foo WHERE a IN (2,3,1)',
601 'SELECT * FROM foo WHERE a IN (3,1,2)',
602 'SELECT * FROM foo WHERE a IN (3,2,1)',
603 ]
604 },
25823711 605);
606
32c34379 607my @bind_tests = (
608 # scalar - equal
609 {
610 equal => 1,
611 bindvals => [
612 undef,
613 undef,
614 ]
615 },
616 {
617 equal => 1,
618 bindvals => [
619 'foo',
620 'foo',
621 ]
622 },
623 {
624 equal => 1,
625 bindvals => [
626 42,
627 42,
628 '42',
629 ]
630 },
631
632 # scalarref - equal
633 {
634 equal => 1,
635 bindvals => [
636 \'foo',
637 \'foo',
638 ]
639 },
640 {
641 equal => 1,
642 bindvals => [
643 \42,
644 \42,
645 \'42',
646 ]
647 },
648
649 # arrayref - equal
650 {
651 equal => 1,
652 bindvals => [
653 [],
654 []
655 ]
656 },
657 {
658 equal => 1,
659 bindvals => [
660 [42],
661 [42],
662 ['42'],
663 ]
664 },
665 {
666 equal => 1,
667 bindvals => [
668 [1, 42],
669 [1, 42],
670 ['1', 42],
671 [1, '42'],
672 ['1', '42'],
673 ]
674 },
675
676 # hashref - equal
677 {
678 equal => 1,
679 bindvals => [
680 { foo => 42 },
681 { foo => 42 },
682 { foo => '42' },
683 ]
684 },
685 {
686 equal => 1,
687 bindvals => [
688 { foo => 42, bar => 1 },
689 { foo => 42, bar => 1 },
690 { foo => '42', bar => 1 },
691 ]
692 },
693
694 # blessed object - equal
695 {
696 equal => 1,
697 bindvals => [
698 bless(\(local $_ = 42), 'Life::Universe::Everything'),
699 bless(\(local $_ = 42), 'Life::Universe::Everything'),
700 ]
701 },
702 {
703 equal => 1,
704 bindvals => [
705 bless([42], 'Life::Universe::Everything'),
706 bless([42], 'Life::Universe::Everything'),
707 ]
708 },
709 {
710 equal => 1,
711 bindvals => [
712 bless({ answer => 42 }, 'Life::Universe::Everything'),
713 bless({ answer => 42 }, 'Life::Universe::Everything'),
714 ]
715 },
716
717 # complex data structure - equal
718 {
719 equal => 1,
720 bindvals => [
721 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
722 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
723 ]
724 },
725
726
727 # scalar - different
728 {
729 equal => 0,
730 bindvals => [
731 undef,
732 'foo',
733 42,
734 ]
735 },
736
737 # scalarref - different
738 {
739 equal => 0,
740 bindvals => [
741 \undef,
742 \'foo',
743 \42,
744 ]
745 },
746
747 # arrayref - different
748 {
749 equal => 0,
750 bindvals => [
751 [undef],
752 ['foo'],
753 [42],
754 ]
755 },
756
757 # hashref - different
758 {
759 equal => 0,
760 bindvals => [
761 { foo => undef },
762 { foo => 'bar' },
763 { foo => 42 },
764 ]
765 },
766
767 # different types
768 {
769 equal => 0,
770 bindvals => [
771 'foo',
772 \'foo',
773 ['foo'],
774 { foo => 'bar' },
775 ]
776 },
777
778 # complex data structure - different
779 {
780 equal => 0,
781 bindvals => [
782 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
783 [43, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
784 [42, { foo => 'baz', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
785 [42, { bar => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
786 [42, { foo => 'bar', quuux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
787 [42, { foo => 'bar', quux => [0, 1, 2, \3, { quux => [4, 5] } ] }, 8 ],
788 [42, { foo => 'bar', quux => [1, 2, 3, { quux => [4, 5] } ] }, 8 ],
789 [42, { foo => 'bar', quux => [1, 2, \4, { quux => [4, 5] } ] }, 8 ],
790 [42, { foo => 'bar', quux => [1, 2, \3, { quuux => [4, 5] } ] }, 8 ],
791 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5, 6] } ] }, 8 ],
792 [42, { foo => 'bar', quux => [1, 2, \3, { quux => 4 } ] }, 8 ],
793 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5], quuux => 1 } ] }, 8 ],
794 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8, 9 ],
795 ]
796 },
797);
798
25823711 799plan tests => 1 +
800 sum(
801 map { $_ * ($_ - 1) / 2 }
802 map { scalar @{$_->{statements}} }
803 @sql_tests
804 ) +
805 sum(
806 map { $_ * ($_ - 1) / 2 }
807 map { scalar @{$_->{bindvals}} }
808 @bind_tests
e7827ba2 809 ) +
810 3;
32c34379 811
e7827ba2 812use_ok('SQL::Abstract::Test', import => [qw(
813 eq_sql_bind eq_sql eq_bind is_same_sql_bind
814)]);
32c34379 815
25823711 816for my $test (@sql_tests) {
817 my $statements = $test->{statements};
818 while (@$statements) {
819 my $sql1 = shift @$statements;
820 foreach my $sql2 (@$statements) {
e40f5df9 821
822 no warnings qw/once/; # perl 5.10 is dumb
823 local $SQL::Abstract::Test::parenthesis_significant = $test->{parenthesis_significant}
824 if $test->{parenthesis_significant};
25823711 825 my $equal = eq_sql($sql1, $sql2);
e40f5df9 826
56c0595d 827 TODO: {
828 local $TODO = $test->{todo} if $test->{todo};
829
830 if ($test->{equal}) {
831 ok($equal, "equal SQL expressions should have been considered equal");
832 } else {
833 ok(!$equal, "different SQL expressions should have been considered not equal");
834 }
835
836 if ($equal ^ $test->{equal}) {
837 diag("sql1: $sql1");
838 diag("sql2: $sql2");
839 }
25823711 840 }
841 }
842 }
843}
844
32c34379 845for my $test (@bind_tests) {
846 my $bindvals = $test->{bindvals};
847 while (@$bindvals) {
848 my $bind1 = shift @$bindvals;
849 foreach my $bind2 (@$bindvals) {
850 my $equal = eq_bind($bind1, $bind2);
851 if ($test->{equal}) {
852 ok($equal, "equal bind values considered equal");
853 } else {
854 ok(!$equal, "different bind values considered not equal");
855 }
856
857 if ($equal ^ $test->{equal}) {
858 diag("bind1: " . Dumper($bind1));
859 diag("bind2: " . Dumper($bind2));
860 }
861 }
862 }
863}
e7827ba2 864
865ok(eq_sql_bind(
866 "SELECT * FROM foo WHERE id = ?", [42],
867 "SELECT * FROM foo WHERE (id = ?)", [42],
868 ),
869 "eq_sql_bind considers equal SQL expressions and bind values equal"
870);
871
872
873ok(!eq_sql_bind(
874 "SELECT * FROM foo WHERE id = ?", [42],
875 "SELECT * FROM foo WHERE (id = ?)", [0],
876 ),
877 "eq_sql_bind considers equal SQL expressions and different bind values different"
878);
879
880ok(!eq_sql_bind(
881 "SELECT * FROM foo WHERE id = ?", [42],
882 "SELECT * FROM bar WHERE (id = ?)", [42],
883 ),
884 "eq_sql_bind considers different SQL expressions and equal bind values different"
885);
Generate Where Clauses! (git://git.shadowcat.co.uk/dbsrgits/SQL-Abstract.git)