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,
14190bdf 271 parenthesis_significant => 1,
272 statements => [
273 q/SELECT foo FROM bar WHERE a IN (1,2,3)/,
274 q/SELECT foo FROM bar WHERE a IN (1,3,2)/,
275 q/SELECT foo FROM bar WHERE a IN ((1,2,3))/,
276 ]
277 },
278 {
279 equal => 0,
b6c8b01b 280 statements => [
01b64cb7 281 # BETWEEN with/without parenthesis around itself/RHS is a sticky business
282 # if I made a mistake here, simply rewrite the special BETWEEN handling in
283 # _recurse_parse()
284 #
285 # by RIBASUSHI
b6c8b01b 286 q/SELECT foo FROM bar WHERE ( completion_date BETWEEN ? AND ? AND status = ? )/,
01b64cb7 287 q/SELECT foo FROM bar WHERE completion_date BETWEEN (? AND ?) AND status = ?/,
288 q/SELECT foo FROM bar WHERE ( (completion_date BETWEEN (? AND ?) ) AND status = ? )/,
289 q/SELECT foo FROM bar WHERE ( (completion_date BETWEEN (? AND ? AND status = ?) ) )/,
b6c8b01b 290 ]
291 },
25823711 292
293 # JOIN condition - equal
294 {
295 equal => 1,
296 statements => [
297 q/SELECT foo FROM bar JOIN baz ON a = 1 WHERE x = 1/,
298 q/SELECT foo FROM bar JOIN baz ON a=1 WHERE x = 1/,
299 q/SELECT foo FROM bar JOIN baz ON (a = 1) WHERE x = 1/,
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/
303 SELECT
304 foo
305 FROM
306 bar
307 JOIN
308 baz
309 ON
310 a = 1
311 WHERE
312 x = 1
313 /,
314 q/
315 SELECT
316 foo
317 FROM
318 bar
319 JOIN
320 baz
321 ON
322 (a = 1)
323 WHERE
324 x = 1
325 /,
326 q/
327 SELECT
328 foo
329 FROM
330 bar
331 JOIN
332 baz
333 ON
334 ( a = 1 )
335 WHERE
336 x = 1
337 /,
338 q/SELECT foo FROM bar JOIN baz ON ((a = 1)) WHERE x = 1/,
339 q/SELECT foo FROM bar JOIN baz ON ( (a = 1) ) WHERE x = 1/,
340 q/SELECT foo FROM bar JOIN baz ON ( ( a = 1 ) ) WHERE x = 1/,
341 ]
342 },
343 {
344 equal => 1,
345 statements => [
346 q/SELECT foo FROM bar JOIN baz ON a = 1 AND b = 1 WHERE x = 1/,
347 q/SELECT foo FROM bar JOIN baz ON (a = 1) AND (b = 1) WHERE x = 1/,
348 q/SELECT foo FROM bar JOIN baz ON ((a = 1) AND (b = 1)) WHERE x = 1/,
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/
353 SELECT
354 foo
355 FROM
356 bar
357 JOIN
358 baz
359 ON
360 a = 1
361 AND
362 b = 1
363 WHERE
364 x = 1
365 /,
366 q/
367 SELECT
368 foo
369 FROM
370 bar
371 JOIN
372 baz
373 ON
374 (a = 1
375 AND
376 b = 1)
377 WHERE
378 x = 1
379 /,
380 q/
381 SELECT
382 foo
383 FROM
384 bar
385 JOIN
386 baz
387 ON
388 (a = 1)
389 AND
390 (b = 1)
391 WHERE
392 x = 1
393 /,
394 q/
395 SELECT
396 foo
397 FROM
398 bar
399 JOIN
400 baz
401 ON
402 ((a = 1)
403 AND
404 (b = 1))
405 WHERE
406 x = 1
407 /,
408 ]
409 },
410
411 # JOIN condition - different
412 {
413 equal => 0,
414 statements => [
415 q/SELECT foo FROM bar JOIN quux ON a = 1 WHERE quuux/,
416 q/SELECT quux FROM bar JOIN quux ON a = 1 WHERE quuux/,
417 q/SELECT foo FROM quux JOIN quux ON a = 1 WHERE quuux/,
418 q/FOOBAR foo FROM bar JOIN quux ON a = 1 WHERE quuux/,
419
420 q/SELECT foo FROM bar JOIN quux ON a = 2 WHERE quuux/,
421 q/SELECT foo FROM bar JOIN quux ON a < 1 WHERE quuux/,
422 q/SELECT foo FROM bar JOIN quux ON b = 1 WHERE quuux/,
423 q/SELECT foo FROM bar JOIN quux ON (c = 1) WHERE quuux/,
424 q/SELECT foo FROM bar JOIN quux ON (d = 1) WHERE quuux/,
425
426 q/SELECT foo FROM bar JOIN quux ON a = 1 AND quuux/,
427 q/SELECT foo FROM bar JOIN quux ON a = 1 GROUP BY foo/,
428 q/SELECT foo FROM bar JOIN quux ON a = 1 ORDER BY foo/,
429 q/SELECT foo FROM bar JOIN quux ON a = 1 LIMIT 1/,
430 q/SELECT foo FROM bar JOIN quux ON a = 1 OFFSET 1/,
431 q/SELECT foo FROM bar JOIN quux ON a = 1 JOIN quuux/,
432 q/SELECT foo FROM bar JOIN quux ON a = 1 JOIN quuux ON a = 1/,
433 ]
434 },
435 {
436 equal => 0,
437 statements => [
438 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 WHERE quuux/,
439 q/SELECT quux FROM bar JOIN quux ON a = 1 AND b = 1 WHERE quuux/,
440 q/SELECT foo FROM quux JOIN quux ON a = 1 AND b = 1 WHERE quuux/,
441 q/FOOBAR foo FROM bar JOIN quux ON a = 1 AND b = 1 WHERE quuux/,
442
443 q/SELECT foo FROM bar JOIN quux ON a = 2 AND b = 1 WHERE quuux/,
444 q/SELECT foo FROM bar JOIN quux ON a = 3 AND (b = 1) WHERE quuux/,
445 q/SELECT foo FROM bar JOIN quux ON (a = 4) AND b = 1 WHERE quuux/,
446 q/SELECT foo FROM bar JOIN quux ON (a = 5) AND (b = 1) WHERE quuux/,
447 q/SELECT foo FROM bar JOIN quux ON ((a = 6) AND (b = 1)) WHERE quuux/,
448 q/SELECT foo FROM bar JOIN quux ON ((a = 7) AND (b = 1)) WHERE quuux/,
449
450 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 2 WHERE quuux/,
451 q/SELECT foo FROM bar JOIN quux ON a = 1 AND (b = 3) WHERE quuux/,
452 q/SELECT foo FROM bar JOIN quux ON (a = 1) AND b = 4 WHERE quuux/,
453 q/SELECT foo FROM bar JOIN quux ON (a = 1) AND (b = 5) WHERE quuux/,
454 q/SELECT foo FROM bar JOIN quux ON ((a = 1) AND (b = 6)) WHERE quuux/,
455 q/SELECT foo FROM bar JOIN quux ON ((a = 1) AND (b = 7)) WHERE quuux/,
456
457 q/SELECT foo FROM bar JOIN quux ON a < 1 AND b = 1 WHERE quuux/,
458 q/SELECT foo FROM bar JOIN quux ON b = 1 AND b = 1 WHERE quuux/,
459 q/SELECT foo FROM bar JOIN quux ON (c = 1) AND b = 1 WHERE quuux/,
460 q/SELECT foo FROM bar JOIN quux ON (d = 1) AND b = 1 WHERE quuux/,
461
462 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 AND quuux/,
463 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 GROUP BY foo/,
464 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 ORDER BY foo/,
465 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 LIMIT 1/,
466 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 OFFSET 1/,
467 q/SELECT foo FROM bar JOIN quux JOIN quuux ON a = 1 AND b = 1/,
468 q/SELECT foo FROM bar JOIN quux ON a = 1 JOIN quuux ON a = 1 AND b = 1/,
469 ]
470 },
471
472 # DISTINCT ON (...) not confused with JOIN ON (...)
473 {
474 equal => 1,
475 statements => [
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 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE (a = 1)/,
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/
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/
500 SELECT DISTINCT ON (foo, quux)
501 foo,
502 quux
503 FROM
504 bar
505 WHERE
506 ( a = 1 )
507 /,
508 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ((a = 1))/,
509 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ( (a = 1) )/,
510 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ( ( a = 1 ) )/,
511 ]
512 },
513
514 # subselects - equal
515 {
516 equal => 1,
517 statements => [
518 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE a = 1/,
519 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE (a = 1)/,
520 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE a = 1/,
521 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE (a = 1)/,
522 ]
523 },
524 {
525 equal => 1,
526 statements => [
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 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 1) AS foo WHERE a = 1/,
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
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 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 ]
539 },
540
541 # subselects - different
542 {
543 equal => 0,
544 statements => [
545 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE a = 1/,
546 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE a = 2/,
547 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE (a = 3)/,
548 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE a = 4/,
549 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE (a = 5)/,
550 q/SELECT * FROM (SELECT * FROM bar WHERE b = 2) AS foo WHERE a = 1/,
551 q/SELECT * FROM (SELECT * FROM bar WHERE b = 3) AS foo WHERE (a = 1)/,
552 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 4)) AS foo WHERE a = 1/,
553 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 5)) AS foo WHERE (a = 1)/,
554 ]
555 },
556 {
557 equal => 0,
558 statements => [
559 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE a = 1/,
560 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 2) AS foo WHERE a = 1/,
561 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 3)) AS foo WHERE a = 1/,
562 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 4)) AS foo WHERE a = 1/,
563 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 5))) AS foo WHERE a = 1/,
564
565 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 6) AS foo WHERE (a = 1)/,
566 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 7) AS foo WHERE (a = 1)/,
567 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 8)) AS foo WHERE (a = 1)/,
568 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 9)) AS foo WHERE (a = 1)/,
569 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 10))) AS foo WHERE (a = 1)/,
570
571 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE a = 2/,
572 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 2) AS foo WHERE a = 2/,
573 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 3)) AS foo WHERE a = 2/,
574 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 4)) AS foo WHERE a = 2/,
575 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 5))) AS foo WHERE a = 2/,
576
577 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 6) AS foo WHERE (a = 2)/,
578 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 7) AS foo WHERE (a = 2)/,
579 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 8)) AS foo WHERE (a = 2)/,
580 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 9)) AS foo WHERE (a = 2)/,
581 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 10))) AS foo WHERE (a = 2)/,
582 ]
583 },
9a5b64c9 584 {
585 equal => 0,
586 statements => [
587 'SELECT a,b,c FROM foo',
588 'SELECT a,c,b FROM foo',
589 'SELECT b,a,c FROM foo',
590 'SELECT b,c,a FROM foo',
591 'SELECT c,a,b FROM foo',
592 'SELECT c,b,a FROM foo',
593 ]
594 },
595 {
596 equal => 0,
597 statements => [
76f10e1c 598 'SELECT count(*) FROM foo',
599 'SELECT count(a) FROM foo',
600 'SELECT count(1) FROM foo',
601 ]
602 },
603 {
604 equal => 0,
605 statements => [
9a5b64c9 606 'SELECT * FROM foo WHERE a IN (1,2,3)',
607 'SELECT * FROM foo WHERE a IN (1,3,2)',
608 'SELECT * FROM foo WHERE a IN (2,1,3)',
609 'SELECT * FROM foo WHERE a IN (2,3,1)',
610 'SELECT * FROM foo WHERE a IN (3,1,2)',
611 'SELECT * FROM foo WHERE a IN (3,2,1)',
612 ]
613 },
25823711 614);
615
32c34379 616my @bind_tests = (
617 # scalar - equal
618 {
619 equal => 1,
620 bindvals => [
621 undef,
622 undef,
623 ]
624 },
625 {
626 equal => 1,
627 bindvals => [
628 'foo',
629 'foo',
630 ]
631 },
632 {
633 equal => 1,
634 bindvals => [
635 42,
636 42,
637 '42',
638 ]
639 },
640
641 # scalarref - equal
642 {
643 equal => 1,
644 bindvals => [
645 \'foo',
646 \'foo',
647 ]
648 },
649 {
650 equal => 1,
651 bindvals => [
652 \42,
653 \42,
654 \'42',
655 ]
656 },
657
658 # arrayref - equal
659 {
660 equal => 1,
661 bindvals => [
662 [],
663 []
664 ]
665 },
666 {
667 equal => 1,
668 bindvals => [
669 [42],
670 [42],
671 ['42'],
672 ]
673 },
674 {
675 equal => 1,
676 bindvals => [
677 [1, 42],
678 [1, 42],
679 ['1', 42],
680 [1, '42'],
681 ['1', '42'],
682 ]
683 },
684
685 # hashref - equal
686 {
687 equal => 1,
688 bindvals => [
689 { foo => 42 },
690 { foo => 42 },
691 { foo => '42' },
692 ]
693 },
694 {
695 equal => 1,
696 bindvals => [
697 { foo => 42, bar => 1 },
698 { foo => 42, bar => 1 },
699 { foo => '42', bar => 1 },
700 ]
701 },
702
703 # blessed object - equal
704 {
705 equal => 1,
706 bindvals => [
707 bless(\(local $_ = 42), 'Life::Universe::Everything'),
708 bless(\(local $_ = 42), 'Life::Universe::Everything'),
709 ]
710 },
711 {
712 equal => 1,
713 bindvals => [
714 bless([42], 'Life::Universe::Everything'),
715 bless([42], 'Life::Universe::Everything'),
716 ]
717 },
718 {
719 equal => 1,
720 bindvals => [
721 bless({ answer => 42 }, 'Life::Universe::Everything'),
722 bless({ answer => 42 }, 'Life::Universe::Everything'),
723 ]
724 },
725
726 # complex data structure - equal
727 {
728 equal => 1,
729 bindvals => [
730 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
731 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
732 ]
733 },
734
735
736 # scalar - different
737 {
738 equal => 0,
739 bindvals => [
740 undef,
741 'foo',
742 42,
743 ]
744 },
745
746 # scalarref - different
747 {
748 equal => 0,
749 bindvals => [
750 \undef,
751 \'foo',
752 \42,
753 ]
754 },
755
756 # arrayref - different
757 {
758 equal => 0,
759 bindvals => [
760 [undef],
761 ['foo'],
762 [42],
763 ]
764 },
765
766 # hashref - different
767 {
768 equal => 0,
769 bindvals => [
770 { foo => undef },
771 { foo => 'bar' },
772 { foo => 42 },
773 ]
774 },
775
776 # different types
777 {
778 equal => 0,
779 bindvals => [
780 'foo',
781 \'foo',
782 ['foo'],
783 { foo => 'bar' },
784 ]
785 },
786
787 # complex data structure - different
788 {
789 equal => 0,
790 bindvals => [
791 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
792 [43, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
793 [42, { foo => 'baz', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
794 [42, { bar => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
795 [42, { foo => 'bar', quuux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
796 [42, { foo => 'bar', quux => [0, 1, 2, \3, { quux => [4, 5] } ] }, 8 ],
797 [42, { foo => 'bar', quux => [1, 2, 3, { quux => [4, 5] } ] }, 8 ],
798 [42, { foo => 'bar', quux => [1, 2, \4, { quux => [4, 5] } ] }, 8 ],
799 [42, { foo => 'bar', quux => [1, 2, \3, { quuux => [4, 5] } ] }, 8 ],
800 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5, 6] } ] }, 8 ],
801 [42, { foo => 'bar', quux => [1, 2, \3, { quux => 4 } ] }, 8 ],
802 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5], quuux => 1 } ] }, 8 ],
803 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8, 9 ],
804 ]
805 },
806);
807
25823711 808plan tests => 1 +
809 sum(
810 map { $_ * ($_ - 1) / 2 }
811 map { scalar @{$_->{statements}} }
812 @sql_tests
813 ) +
814 sum(
815 map { $_ * ($_ - 1) / 2 }
816 map { scalar @{$_->{bindvals}} }
817 @bind_tests
e7827ba2 818 ) +
819 3;
32c34379 820
e7827ba2 821use_ok('SQL::Abstract::Test', import => [qw(
822 eq_sql_bind eq_sql eq_bind is_same_sql_bind
823)]);
32c34379 824
25823711 825for my $test (@sql_tests) {
826 my $statements = $test->{statements};
827 while (@$statements) {
828 my $sql1 = shift @$statements;
829 foreach my $sql2 (@$statements) {
e40f5df9 830
831 no warnings qw/once/; # perl 5.10 is dumb
832 local $SQL::Abstract::Test::parenthesis_significant = $test->{parenthesis_significant}
833 if $test->{parenthesis_significant};
25823711 834 my $equal = eq_sql($sql1, $sql2);
e40f5df9 835
56c0595d 836 TODO: {
837 local $TODO = $test->{todo} if $test->{todo};
838
839 if ($test->{equal}) {
840 ok($equal, "equal SQL expressions should have been considered equal");
841 } else {
842 ok(!$equal, "different SQL expressions should have been considered not equal");
843 }
844
845 if ($equal ^ $test->{equal}) {
846 diag("sql1: $sql1");
847 diag("sql2: $sql2");
848 }
25823711 849 }
850 }
851 }
852}
853
32c34379 854for my $test (@bind_tests) {
855 my $bindvals = $test->{bindvals};
856 while (@$bindvals) {
857 my $bind1 = shift @$bindvals;
858 foreach my $bind2 (@$bindvals) {
859 my $equal = eq_bind($bind1, $bind2);
860 if ($test->{equal}) {
861 ok($equal, "equal bind values considered equal");
862 } else {
863 ok(!$equal, "different bind values considered not equal");
864 }
865
866 if ($equal ^ $test->{equal}) {
867 diag("bind1: " . Dumper($bind1));
868 diag("bind2: " . Dumper($bind2));
869 }
870 }
871 }
872}
e7827ba2 873
874ok(eq_sql_bind(
875 "SELECT * FROM foo WHERE id = ?", [42],
876 "SELECT * FROM foo WHERE (id = ?)", [42],
877 ),
878 "eq_sql_bind considers equal SQL expressions and bind values equal"
879);
880
881
882ok(!eq_sql_bind(
883 "SELECT * FROM foo WHERE id = ?", [42],
884 "SELECT * FROM foo WHERE (id = ?)", [0],
885 ),
886 "eq_sql_bind considers equal SQL expressions and different bind values different"
887);
888
889ok(!eq_sql_bind(
890 "SELECT * FROM foo WHERE id = ?", [42],
891 "SELECT * FROM bar WHERE (id = ?)", [42],
892 ),
893 "eq_sql_bind considers different SQL expressions and equal bind values different"
894);
Generate Where Clauses! (git://git.shadowcat.co.uk/dbsrgits/SQL-Abstract.git)