projects / scpubgit/Q-Branch.git / blame
? search:
summary | shortlog | log | commit | commitdiff | tree
blob | history | HEAD
Whitespace/comment cleanup
[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 = (
f8135ff3 22 # WHERE condition - equal
25823711 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 {
f8135ff3 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 ],
9a5b64c9 594 },
595 {
f8135ff3 596 equal => 0,
597 statements => [
598 'SELECT * FROM foo WHERE a IN (1,2,3)',
599 'SELECT * FROM foo WHERE a IN (1,3,2)',
600 'SELECT * FROM foo WHERE a IN (2,1,3)',
601 'SELECT * FROM foo WHERE a IN (2,3,1)',
602 'SELECT * FROM foo WHERE a IN (3,1,2)',
603 'SELECT * FROM foo WHERE a IN (3,2,1)',
604 ]
76f10e1c 605 },
606 {
f8135ff3 607 equal => 0,
608 statements => [
609 'SELECT count(*) FROM foo',
610 'SELECT count(*) AS bar FROM foo',
611 'SELECT count(*) AS "bar" FROM foo',
612 'SELECT count(a) FROM foo',
613 'SELECT count(1) FROM foo',
614 ]
9a5b64c9 615 },
25823711 616);
617
32c34379 618my @bind_tests = (
619 # scalar - equal
620 {
621 equal => 1,
622 bindvals => [
623 undef,
624 undef,
625 ]
626 },
627 {
628 equal => 1,
629 bindvals => [
630 'foo',
631 'foo',
632 ]
633 },
634 {
635 equal => 1,
636 bindvals => [
637 42,
638 42,
639 '42',
640 ]
641 },
642
643 # scalarref - equal
644 {
645 equal => 1,
646 bindvals => [
647 \'foo',
648 \'foo',
649 ]
650 },
651 {
652 equal => 1,
653 bindvals => [
654 \42,
655 \42,
656 \'42',
657 ]
658 },
659
660 # arrayref - equal
661 {
662 equal => 1,
663 bindvals => [
664 [],
665 []
666 ]
667 },
668 {
669 equal => 1,
670 bindvals => [
671 [42],
672 [42],
673 ['42'],
674 ]
675 },
676 {
677 equal => 1,
678 bindvals => [
679 [1, 42],
680 [1, 42],
681 ['1', 42],
682 [1, '42'],
683 ['1', '42'],
684 ]
685 },
686
687 # hashref - equal
688 {
689 equal => 1,
690 bindvals => [
691 { foo => 42 },
692 { foo => 42 },
693 { foo => '42' },
694 ]
695 },
696 {
697 equal => 1,
698 bindvals => [
699 { foo => 42, bar => 1 },
700 { foo => 42, bar => 1 },
701 { foo => '42', bar => 1 },
702 ]
703 },
704
705 # blessed object - equal
706 {
707 equal => 1,
708 bindvals => [
709 bless(\(local $_ = 42), 'Life::Universe::Everything'),
710 bless(\(local $_ = 42), 'Life::Universe::Everything'),
711 ]
712 },
713 {
714 equal => 1,
715 bindvals => [
716 bless([42], 'Life::Universe::Everything'),
717 bless([42], 'Life::Universe::Everything'),
718 ]
719 },
720 {
721 equal => 1,
722 bindvals => [
723 bless({ answer => 42 }, 'Life::Universe::Everything'),
724 bless({ answer => 42 }, 'Life::Universe::Everything'),
725 ]
726 },
727
728 # complex data structure - equal
729 {
730 equal => 1,
731 bindvals => [
732 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
733 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
734 ]
735 },
736
737
738 # scalar - different
739 {
740 equal => 0,
741 bindvals => [
742 undef,
743 'foo',
744 42,
745 ]
746 },
747
748 # scalarref - different
749 {
750 equal => 0,
751 bindvals => [
752 \undef,
753 \'foo',
754 \42,
755 ]
756 },
757
758 # arrayref - different
759 {
760 equal => 0,
761 bindvals => [
762 [undef],
763 ['foo'],
764 [42],
765 ]
766 },
767
768 # hashref - different
769 {
770 equal => 0,
771 bindvals => [
772 { foo => undef },
773 { foo => 'bar' },
774 { foo => 42 },
775 ]
776 },
777
778 # different types
779 {
780 equal => 0,
781 bindvals => [
782 'foo',
783 \'foo',
784 ['foo'],
785 { foo => 'bar' },
786 ]
787 },
788
789 # complex data structure - different
790 {
791 equal => 0,
792 bindvals => [
793 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
794 [43, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
795 [42, { foo => 'baz', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
796 [42, { bar => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
797 [42, { foo => 'bar', quuux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
798 [42, { foo => 'bar', quux => [0, 1, 2, \3, { quux => [4, 5] } ] }, 8 ],
799 [42, { foo => 'bar', quux => [1, 2, 3, { quux => [4, 5] } ] }, 8 ],
800 [42, { foo => 'bar', quux => [1, 2, \4, { quux => [4, 5] } ] }, 8 ],
801 [42, { foo => 'bar', quux => [1, 2, \3, { quuux => [4, 5] } ] }, 8 ],
802 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5, 6] } ] }, 8 ],
803 [42, { foo => 'bar', quux => [1, 2, \3, { quux => 4 } ] }, 8 ],
804 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5], quuux => 1 } ] }, 8 ],
805 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8, 9 ],
806 ]
807 },
808);
809
25823711 810plan tests => 1 +
811 sum(
812 map { $_ * ($_ - 1) / 2 }
813 map { scalar @{$_->{statements}} }
814 @sql_tests
815 ) +
816 sum(
817 map { $_ * ($_ - 1) / 2 }
818 map { scalar @{$_->{bindvals}} }
819 @bind_tests
e7827ba2 820 ) +
821 3;
32c34379 822
e7827ba2 823use_ok('SQL::Abstract::Test', import => [qw(
824 eq_sql_bind eq_sql eq_bind is_same_sql_bind
825)]);
32c34379 826
25823711 827for my $test (@sql_tests) {
828 my $statements = $test->{statements};
829 while (@$statements) {
830 my $sql1 = shift @$statements;
831 foreach my $sql2 (@$statements) {
e40f5df9 832
833 no warnings qw/once/; # perl 5.10 is dumb
834 local $SQL::Abstract::Test::parenthesis_significant = $test->{parenthesis_significant}
835 if $test->{parenthesis_significant};
25823711 836 my $equal = eq_sql($sql1, $sql2);
e40f5df9 837
56c0595d 838 TODO: {
839 local $TODO = $test->{todo} if $test->{todo};
840
841 if ($test->{equal}) {
842 ok($equal, "equal SQL expressions should have been considered equal");
843 } else {
844 ok(!$equal, "different SQL expressions should have been considered not equal");
845 }
846
847 if ($equal ^ $test->{equal}) {
848 diag("sql1: $sql1");
849 diag("sql2: $sql2");
850 }
25823711 851 }
852 }
853 }
854}
855
32c34379 856for my $test (@bind_tests) {
857 my $bindvals = $test->{bindvals};
858 while (@$bindvals) {
859 my $bind1 = shift @$bindvals;
860 foreach my $bind2 (@$bindvals) {
861 my $equal = eq_bind($bind1, $bind2);
862 if ($test->{equal}) {
863 ok($equal, "equal bind values considered equal");
864 } else {
865 ok(!$equal, "different bind values considered not equal");
866 }
867
868 if ($equal ^ $test->{equal}) {
869 diag("bind1: " . Dumper($bind1));
870 diag("bind2: " . Dumper($bind2));
871 }
872 }
873 }
874}
e7827ba2 875
876ok(eq_sql_bind(
877 "SELECT * FROM foo WHERE id = ?", [42],
878 "SELECT * FROM foo WHERE (id = ?)", [42],
879 ),
880 "eq_sql_bind considers equal SQL expressions and bind values equal"
881);
882
883
884ok(!eq_sql_bind(
885 "SELECT * FROM foo WHERE id = ?", [42],
886 "SELECT * FROM foo WHERE (id = ?)", [0],
887 ),
888 "eq_sql_bind considers equal SQL expressions and different bind values different"
889);
890
891ok(!eq_sql_bind(
892 "SELECT * FROM foo WHERE id = ?", [42],
893 "SELECT * FROM bar WHERE (id = ?)", [42],
894 ),
895 "eq_sql_bind considers different SQL expressions and equal bind values different"
896);
Unnamed repository; edit this file 'description' to name the repository.