projects / scpubgit/Q-Branch.git / blame
? search:
summary | shortlog | log | commit | commitdiff | tree
blob | history | HEAD
remove obsolete thing that never worked
[scpubgit/Q-Branch.git] / t / 10test.t
CommitLineData
32c34379 1use strict;
2use warnings;
32c34379 3
4use Test::More;
5
2fadf08e 6use SQL::Abstract::Test import => [qw(
7 eq_sql_bind eq_sql eq_bind is_same_sql_bind dumper $sql_differ
8)];
54629227 9
25823711 10my @sql_tests = (
0769ac0e 11 # WHERE condition - equal
25823711 12 {
13 equal => 1,
14 statements => [
15 q/SELECT foo FROM bar WHERE a = 1/,
16 q/SELECT foo FROM bar WHERE a=1/,
17 q/SELECT foo FROM bar WHERE (a = 1)/,
18 q/SELECT foo FROM bar WHERE (a=1)/,
19 q/SELECT foo FROM bar WHERE ( a = 1 )/,
20 q/
21 SELECT
22 foo
23 FROM
24 bar
25 WHERE
26 a = 1
27 /,
28 q/
29 SELECT
30 foo
31 FROM
32 bar
33 WHERE
34 (a = 1)
35 /,
36 q/
37 SELECT
38 foo
39 FROM
40 bar
41 WHERE
42 ( a = 1 )
43 /,
44 q/SELECT foo FROM bar WHERE ((a = 1))/,
45 q/SELECT foo FROM bar WHERE ( (a = 1) )/,
46 q/SELECT foo FROM bar WHERE ( ( a = 1 ) )/,
47 ]
48 },
49 {
50 equal => 1,
51 statements => [
52 q/SELECT foo FROM bar WHERE a = 1 AND b = 1/,
53 q/SELECT foo FROM bar WHERE (a = 1) AND (b = 1)/,
54 q/SELECT foo FROM bar WHERE ((a = 1) AND (b = 1))/,
55 q/SELECT foo FROM bar WHERE (a = 1 AND b = 1)/,
56 q/SELECT foo FROM bar WHERE ((a = 1 AND b = 1))/,
57 q/SELECT foo FROM bar WHERE (((a = 1) AND (b = 1)))/,
58 q/
59 SELECT
60 foo
61 FROM
62 bar
63 WHERE
64 a = 1
65 AND
66 b = 1
67 /,
68 q/
69 SELECT
70 foo
71 FROM
72 bar
73 WHERE
74 (a = 1
75 AND
76 b = 1)
77 /,
78 q/
79 SELECT
80 foo
81 FROM
82 bar
83 WHERE
84 (a = 1)
85 AND
86 (b = 1)
87 /,
88 q/
89 SELECT
90 foo
91 FROM
92 bar
93 WHERE
94 ((a = 1)
95 AND
96 (b = 1))
97 /,
98 ]
99 },
56c0595d 100 {
101 equal => 1,
56c0595d 102 statements => [
103 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 AND c = 1/,
e40f5df9 104 q/SELECT foo FROM bar WHERE (a = 1 AND b = 1 AND c = 1)/,
56c0595d 105 q/SELECT foo FROM bar WHERE (a = 1 AND b = 1) AND c = 1/,
106 q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 AND c = 1)/,
1b17d1b0 107 q/SELECT foo FROM bar WHERE ((((a = 1))) AND (b = 1 AND c = 1))/,
56c0595d 108 ]
109 },
110 {
111 equal => 1,
56c0595d 112 statements => [
113 q/SELECT foo FROM bar WHERE a = 1 OR b = 1 OR c = 1/,
114 q/SELECT foo FROM bar WHERE (a = 1 OR b = 1) OR c = 1/,
115 q/SELECT foo FROM bar WHERE a = 1 OR (b = 1 OR c = 1)/,
1b17d1b0 116 q/SELECT foo FROM bar WHERE a = 1 OR ((b = 1 OR (c = 1)))/,
117 ]
118 },
119 {
120 equal => 1,
121 statements => [
122 q/SELECT foo FROM bar WHERE (a = 1) AND (b = 1 OR c = 1 OR d = 1) AND (e = 1 AND f = 1)/,
123 q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 OR c = 1 OR d = 1) AND e = 1 AND (f = 1)/,
124 q/SELECT foo FROM bar WHERE ( ((a = 1) AND ( b = 1 OR (c = 1 OR d = 1) )) AND ((e = 1)) AND f = 1) /,
56c0595d 125 ]
126 },
e40f5df9 127 {
9e8dab3f 128 equal => 1,
129 statements => [
130 q/SELECT foo FROM bar WHERE (a) AND (b = 2)/,
131 q/SELECT foo FROM bar WHERE (a AND b = 2)/,
132 q/SELECT foo FROM bar WHERE (a AND (b = 2))/,
133 q/SELECT foo FROM bar WHERE a AND (b = 2)/,
134 ]
135 },
136 {
277b5d3f 137 equal => 1,
138 statements => [
139 q/SELECT foo FROM bar WHERE ((NOT a) AND b = 2)/,
140 q/SELECT foo FROM bar WHERE (NOT a) AND (b = 2)/,
141 q/SELECT foo FROM bar WHERE (NOT (a)) AND b = 2/,
142 ],
143 },
144 {
9e8dab3f 145 equal => 0,
146 statements => [
147 q/SELECT foo FROM bar WHERE NOT a AND (b = 2)/,
148 q/SELECT foo FROM bar WHERE (NOT a) AND (b = 2)/,
149 ]
150 },
151 {
e40f5df9 152 equal => 0,
3b9d807e 153 opts => { parenthesis_significant => 1 },
e40f5df9 154 statements => [
155 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 AND c = 1/,
156 q/SELECT foo FROM bar WHERE (a = 1 AND b = 1 AND c = 1)/,
157 q/SELECT foo FROM bar WHERE (a = 1 AND b = 1) AND c = 1/,
158 q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 AND c = 1)/,
159 q/SELECT foo FROM bar WHERE ((((a = 1))) AND (b = 1 AND c = 1))/,
160 ]
161 },
162 {
163 equal => 0,
3b9d807e 164 opts => { parenthesis_significant => 1 },
e40f5df9 165 statements => [
166 q/SELECT foo FROM bar WHERE a = 1 OR b = 1 OR c = 1/,
167 q/SELECT foo FROM bar WHERE (a = 1 OR b = 1) OR c = 1/,
168 q/SELECT foo FROM bar WHERE a = 1 OR (b = 1 OR c = 1)/,
169 q/SELECT foo FROM bar WHERE a = 1 OR ((b = 1 OR (c = 1)))/,
170 ]
171 },
172 {
173 equal => 0,
3b9d807e 174 opts => { parenthesis_significant => 1 },
e40f5df9 175 statements => [
176 q/SELECT foo FROM bar WHERE (a = 1) AND (b = 1 OR c = 1 OR d = 1) AND (e = 1 AND f = 1)/,
177 q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 OR c = 1 OR d = 1) AND e = 1 AND (f = 1)/,
178 q/SELECT foo FROM bar WHERE ( ((a = 1) AND ( b = 1 OR (c = 1 OR d = 1) )) AND ((e = 1)) AND f = 1) /,
179 ]
180 },
25823711 181
182 # WHERE condition - different
183 {
184 equal => 0,
185 statements => [
186 q/SELECT foo FROM bar WHERE a = 1/,
187 q/SELECT quux FROM bar WHERE a = 1/,
188 q/SELECT foo FROM quux WHERE a = 1/,
189 q/FOOBAR foo FROM bar WHERE a = 1/,
190
191 q/SELECT foo FROM bar WHERE a = 2/,
192 q/SELECT foo FROM bar WHERE a < 1/,
193 q/SELECT foo FROM bar WHERE b = 1/,
194 q/SELECT foo FROM bar WHERE (c = 1)/,
195 q/SELECT foo FROM bar WHERE (d = 1)/,
196
197 q/SELECT foo FROM bar WHERE a = 1 AND quux/,
198 q/SELECT foo FROM bar WHERE a = 1 GROUP BY foo/,
199 q/SELECT foo FROM bar WHERE a = 1 ORDER BY foo/,
200 q/SELECT foo FROM bar WHERE a = 1 LIMIT 1/,
201 q/SELECT foo FROM bar WHERE a = 1 OFFSET 1/,
202 q/SELECT foo FROM bar JOIN quux WHERE a = 1/,
203 q/SELECT foo FROM bar JOIN quux ON a = 1 WHERE a = 1/,
204 ]
205 },
206 {
207 equal => 0,
208 statements => [
209 q/SELECT foo FROM bar WHERE a = 1 AND b = 1/,
210 q/SELECT quux FROM bar WHERE a = 1 AND b = 1/,
211 q/SELECT foo FROM quux WHERE a = 1 AND b = 1/,
212 q/FOOBAR foo FROM bar WHERE a = 1 AND b = 1/,
213
214 q/SELECT foo FROM bar WHERE a = 2 AND b = 1/,
215 q/SELECT foo FROM bar WHERE a = 3 AND (b = 1)/,
216 q/SELECT foo FROM bar WHERE (a = 4) AND b = 1/,
217 q/SELECT foo FROM bar WHERE (a = 5) AND (b = 1)/,
218 q/SELECT foo FROM bar WHERE ((a = 6) AND (b = 1))/,
219 q/SELECT foo FROM bar WHERE ((a = 7) AND (b = 1))/,
220
221 q/SELECT foo FROM bar WHERE a = 1 AND b = 2/,
222 q/SELECT foo FROM bar WHERE a = 1 AND (b = 3)/,
223 q/SELECT foo FROM bar WHERE (a = 1) AND b = 4/,
224 q/SELECT foo FROM bar WHERE (a = 1) AND (b = 5)/,
225 q/SELECT foo FROM bar WHERE ((a = 1) AND (b = 6))/,
226 q/SELECT foo FROM bar WHERE ((a = 1) AND (b = 7))/,
227
228 q/SELECT foo FROM bar WHERE a < 1 AND b = 1/,
229 q/SELECT foo FROM bar WHERE b = 1 AND b = 1/,
230 q/SELECT foo FROM bar WHERE (c = 1) AND b = 1/,
231 q/SELECT foo FROM bar WHERE (d = 1) AND b = 1/,
232
233 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 AND quux/,
234 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 GROUP BY foo/,
235 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 ORDER BY foo/,
236 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 LIMIT 1/,
237 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 OFFSET 1/,
238 q/SELECT foo FROM bar JOIN quux WHERE a = 1 AND b = 1/,
239 q/SELECT foo FROM bar JOIN quux ON a = 1 WHERE a = 1 AND b = 1/,
240 ]
241 },
56c0595d 242 {
243 equal => 0,
244 statements => [
245 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 OR c = 1/,
246 q/SELECT foo FROM bar WHERE (a = 1 AND b = 1) OR c = 1/,
247 q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 OR c = 1)/,
248 ]
249 },
250 {
251 equal => 0,
252 statements => [
253 q/SELECT foo FROM bar WHERE a = 1 OR b = 1 AND c = 1/,
254 q/SELECT foo FROM bar WHERE (a = 1 OR b = 1) AND c = 1/,
255 q/SELECT foo FROM bar WHERE a = 1 OR (b = 1 AND c = 1)/,
256 ]
257 },
b6c8b01b 258 {
259 equal => 0,
14190bdf 260 statements => [
14190bdf 261 q/SELECT foo FROM bar WHERE a IN (1,3,2)/,
7d273452 262 q/SELECT foo FROM bar WHERE a IN 1,2,3/,
263 q/SELECT foo FROM bar WHERE a IN (1,2,3)/,
14190bdf 264 q/SELECT foo FROM bar WHERE a IN ((1,2,3))/,
265 ]
266 },
267 {
268 equal => 0,
b6c8b01b 269 statements => [
01b64cb7 270 # BETWEEN with/without parenthesis around itself/RHS is a sticky business
271 # if I made a mistake here, simply rewrite the special BETWEEN handling in
272 # _recurse_parse()
273 #
274 # by RIBASUSHI
b6c8b01b 275 q/SELECT foo FROM bar WHERE ( completion_date BETWEEN ? AND ? AND status = ? )/,
01b64cb7 276 q/SELECT foo FROM bar WHERE completion_date BETWEEN (? AND ?) AND status = ?/,
277 q/SELECT foo FROM bar WHERE ( (completion_date BETWEEN (? AND ?) ) AND status = ? )/,
278 q/SELECT foo FROM bar WHERE ( (completion_date BETWEEN (? AND ? AND status = ?) ) )/,
b6c8b01b 279 ]
280 },
25823711 281
b3b79607 282 # IS NULL (special LHS-only op)
283 {
284 equal => 1,
285 statements => [
286 q/WHERE a IS NOT NULL AND b IS NULL/,
287 q/WHERE (a IS NOT NULL) AND b IS NULL/,
288 q/WHERE a IS NOT NULL AND (b IS NULL)/,
289 q/WHERE (a IS NOT NULL) AND ((b IS NULL))/,
290 ],
291 },
292
25823711 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 => [
54629227 545 q/DELETE FROM cd WHERE ( cdid IN ( SELECT me.cdid FROM (SELECT * FROM cd me WHERE ( year != ? ) GROUP BY me.cdid) me WHERE ( year != ? ) ) )/,
546 q/DELETE FROM cd WHERE ( cdid IN ( SELECT me.cdid FROM cd me WHERE ( year != ? ) GROUP BY me.cdid ) )/,
547 ],
548 },
549 {
550 equal => 0,
551 statements => [
25823711 552 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE a = 1/,
553 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE a = 2/,
554 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE (a = 3)/,
555 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE a = 4/,
556 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE (a = 5)/,
557 q/SELECT * FROM (SELECT * FROM bar WHERE b = 2) AS foo WHERE a = 1/,
558 q/SELECT * FROM (SELECT * FROM bar WHERE b = 3) AS foo WHERE (a = 1)/,
559 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 4)) AS foo WHERE a = 1/,
560 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 5)) AS foo WHERE (a = 1)/,
561 ]
562 },
563 {
564 equal => 0,
565 statements => [
566 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE a = 1/,
567 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 2) AS foo WHERE a = 1/,
568 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 3)) AS foo WHERE a = 1/,
569 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 4)) AS foo WHERE a = 1/,
570 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 5))) AS foo WHERE a = 1/,
571
572 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 6) AS foo WHERE (a = 1)/,
573 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 7) AS foo WHERE (a = 1)/,
574 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 8)) AS foo WHERE (a = 1)/,
575 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 9)) AS foo WHERE (a = 1)/,
576 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 10))) AS foo WHERE (a = 1)/,
577
578 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE a = 2/,
579 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 2) AS foo WHERE a = 2/,
580 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 3)) AS foo WHERE a = 2/,
581 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 4)) AS foo WHERE a = 2/,
582 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 5))) AS foo WHERE a = 2/,
583
584 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 6) AS foo WHERE (a = 2)/,
585 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 7) AS foo WHERE (a = 2)/,
586 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 8)) AS foo WHERE (a = 2)/,
587 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 9)) AS foo WHERE (a = 2)/,
588 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 10))) AS foo WHERE (a = 2)/,
589 ]
590 },
0769ac0e 591
0c2de280 592 # order by
593 {
594 equal => 1,
595 statements => [
596 q/SELECT * FROM foo ORDER BY bar/,
597 q/SELECT * FROM foo ORDER BY bar ASC/,
598 q/SELECT * FROM foo ORDER BY bar asc/,
599 ],
600 },
601 {
602 equal => 1,
603 statements => [
604 q/SELECT * FROM foo ORDER BY bar, baz ASC/,
605 q/SELECT * FROM foo ORDER BY bar ASC, baz/,
606 q/SELECT * FROM foo ORDER BY bar asc, baz ASC/,
607 q/SELECT * FROM foo ORDER BY bar, baz/,
608 ],
609 },
610 {
c84a4321 611 equal => 1,
612 statements => [
613 q/ORDER BY colA, colB LIKE ? DESC, colC LIKE ?/,
614 q/ORDER BY colA ASC, colB LIKE ? DESC, colC LIKE ? ASC/,
615 ],
616 },
617 {
b4085a1a 618 equal => 1,
619 statements => [
620 q/ORDER BY name + ?, [me].[id]/,
621 q/ORDER BY name + ? ASC, [me].[id]/,
622 ],
623 },
624 {
0c2de280 625 equal => 0,
626 opts => { order_by_asc_significant => 1 },
627 statements => [
628 q/SELECT * FROM foo ORDER BY bar/,
629 q/SELECT * FROM foo ORDER BY bar ASC/,
630 q/SELECT * FROM foo ORDER BY bar desc/,
631 ],
632 },
633
0769ac0e 634 # list permutations
9a5b64c9 635 {
f8135ff3 636 equal => 0,
637 statements => [
638 'SELECT a,b,c FROM foo',
639 'SELECT a,c,b FROM foo',
640 'SELECT b,a,c FROM foo',
641 'SELECT b,c,a FROM foo',
642 'SELECT c,a,b FROM foo',
643 'SELECT c,b,a FROM foo',
644 ],
9a5b64c9 645 },
646 {
f8135ff3 647 equal => 0,
648 statements => [
649 'SELECT * FROM foo WHERE a IN (1,2,3)',
650 'SELECT * FROM foo WHERE a IN (1,3,2)',
651 'SELECT * FROM foo WHERE a IN (2,1,3)',
652 'SELECT * FROM foo WHERE a IN (2,3,1)',
653 'SELECT * FROM foo WHERE a IN (3,1,2)',
654 'SELECT * FROM foo WHERE a IN (3,2,1)',
655 ]
76f10e1c 656 },
7cc47319 657
658 # list consistency
659 {
660 equal => 0,
661 statements => [
662 'SELECT a,b FROM foo',
663 'SELECT a,,b FROM foo',
664 'SELECT a,b, FROM foo',
665 'SELECT ,a,b, FROM foo',
666 'SELECT ,a,,b, FROM foo',
667 ],
668 },
669
670 # misc func
76f10e1c 671 {
f8135ff3 672 equal => 0,
673 statements => [
674 'SELECT count(*) FROM foo',
675 'SELECT count(*) AS bar FROM foo',
676 'SELECT count(*) AS "bar" FROM foo',
677 'SELECT count(a) FROM foo',
678 'SELECT count(1) FROM foo',
679 ]
9a5b64c9 680 },
0769ac0e 681 {
682 equal => 1,
683 statements => [
684 'SELECT foo() bar FROM baz',
685 'SELECT foo ( )bar FROM baz',
686 'SELECT foo (())bar FROM baz',
687 'SELECT foo(( ) ) bar FROM baz',
688 ]
689 },
690 {
691 equal => 0,
692 statements => [
693 'SELECT foo() FROM bar',
694 'SELECT foo FROM bar',
695 'SELECT foo FROM bar ()',
696 ]
697 },
b3b79607 698 {
6e9a377b 699 equal => 0,
b3b79607 700 statements => [
6e9a377b 701 'SELECT COUNT * FROM foo',
702 'SELECT COUNT( * ) FROM foo',
b3b79607 703 ]
704 },
6e9a377b 705 # single ? of unknown funcs do not unroll unless
706 # explicitly allowed (e.g. Like)
b3b79607 707 {
6e9a377b 708 equal => 0,
b3b79607 709 statements => [
6e9a377b 710 'SELECT foo FROM bar WHERE bar > foo ?',
711 'SELECT foo FROM bar WHERE bar > foo( ? )',
b3b79607 712 ]
713 },
714 {
715 equal => 1,
716 statements => [
6e9a377b 717 'SELECT foo FROM bar WHERE bar LIKE ?',
718 'SELECT foo FROM bar WHERE bar LiKe (?)',
719 'SELECT foo FROM bar WHERE bar lIkE( (?))',
b3b79607 720 ]
721 },
6e9a377b 722 # test multival
69cc1bd8 723 {
724 equal => 0,
725 statements => [
726 'SELECT foo FROM bar WHERE foo IN (?, ?)',
727 'SELECT foo FROM bar WHERE foo IN ?, ?',
728 ]
729 },
0769ac0e 730 # math
731 {
732 equal => 0,
733 statements => [
734 'SELECT * FROM foo WHERE 1 = ( a > b)',
735 'SELECT * FROM foo WHERE 1 = a > b',
736 'SELECT * FROM foo WHERE (1 = a) > b',
737 ]
738 },
739 {
740 equal => 1,
741 statements => [
742 'SELECT * FROM foo WHERE bar = baz(buzz)',
743 'SELECT * FROM foo WHERE bar = (baz( buzz ))',
744 ]
745 },
6f2a5b66 746 # oddballs
747 {
748 equal => 1,
749 statements => [
750 'WHERE ( foo GLOB ? )',
751 'WHERE foo GLOB ?',
752 ],
1de1d085 753 },
754 {
755 equal => 1,
756 statements => [
757 'SELECT FIRST ? SKIP ? [me].[id], [me].[owner]
758 FROM [books] [me]
759 WHERE ( ( (EXISTS (
760 SELECT FIRST ? SKIP ? [owner].[id]
761 FROM [owners] [owner]
762 WHERE ( [books].[owner] = [owner].[id] )
763 )) AND [source] = ? ) )',
764 'SELECT FIRST ? SKIP ? [me].[id], [me].[owner]
765 FROM [books] [me]
766 WHERE ( ( EXISTS (
767 SELECT FIRST ? SKIP ? [owner].[id]
768 FROM [owners] [owner]
769 WHERE ( [books].[owner] = [owner].[id] )
770 ) AND [source] = ? ) )',
771 ],
772 },
81b3e585 773 {
774 equal => 1,
775 statements => [
776 'WHERE foo = ? FETCH FIRST 1 ROWS ONLY',
777 'WHERE ( foo = ? ) FETCH FIRST 1 ROWS ONLY',
778 'WHERE (( foo = ? )) FETCH FIRST 1 ROWS ONLY',
779 ],
780 },
25823711 781);
782
32c34379 783my @bind_tests = (
784 # scalar - equal
785 {
786 equal => 1,
787 bindvals => [
788 undef,
789 undef,
790 ]
791 },
792 {
793 equal => 1,
794 bindvals => [
795 'foo',
796 'foo',
797 ]
798 },
799 {
800 equal => 1,
801 bindvals => [
802 42,
803 42,
804 '42',
805 ]
806 },
807
808 # scalarref - equal
809 {
810 equal => 1,
811 bindvals => [
812 \'foo',
813 \'foo',
814 ]
815 },
816 {
817 equal => 1,
818 bindvals => [
819 \42,
820 \42,
821 \'42',
822 ]
823 },
824
825 # arrayref - equal
826 {
827 equal => 1,
828 bindvals => [
829 [],
830 []
831 ]
832 },
833 {
834 equal => 1,
835 bindvals => [
836 [42],
837 [42],
838 ['42'],
839 ]
840 },
841 {
842 equal => 1,
843 bindvals => [
844 [1, 42],
845 [1, 42],
846 ['1', 42],
847 [1, '42'],
848 ['1', '42'],
849 ]
850 },
851
852 # hashref - equal
853 {
854 equal => 1,
855 bindvals => [
856 { foo => 42 },
857 { foo => 42 },
858 { foo => '42' },
859 ]
860 },
861 {
862 equal => 1,
863 bindvals => [
864 { foo => 42, bar => 1 },
865 { foo => 42, bar => 1 },
866 { foo => '42', bar => 1 },
867 ]
868 },
869
870 # blessed object - equal
871 {
872 equal => 1,
873 bindvals => [
874 bless(\(local $_ = 42), 'Life::Universe::Everything'),
875 bless(\(local $_ = 42), 'Life::Universe::Everything'),
876 ]
877 },
878 {
879 equal => 1,
880 bindvals => [
881 bless([42], 'Life::Universe::Everything'),
882 bless([42], 'Life::Universe::Everything'),
883 ]
884 },
885 {
886 equal => 1,
887 bindvals => [
888 bless({ answer => 42 }, 'Life::Universe::Everything'),
889 bless({ answer => 42 }, 'Life::Universe::Everything'),
890 ]
891 },
892
893 # complex data structure - equal
894 {
895 equal => 1,
896 bindvals => [
897 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
898 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
899 ]
900 },
901
902
903 # scalar - different
904 {
905 equal => 0,
906 bindvals => [
907 undef,
908 'foo',
909 42,
910 ]
911 },
912
913 # scalarref - different
914 {
915 equal => 0,
916 bindvals => [
917 \undef,
918 \'foo',
919 \42,
920 ]
921 },
922
923 # arrayref - different
924 {
925 equal => 0,
926 bindvals => [
927 [undef],
928 ['foo'],
929 [42],
930 ]
931 },
932
933 # hashref - different
934 {
935 equal => 0,
936 bindvals => [
937 { foo => undef },
938 { foo => 'bar' },
939 { foo => 42 },
940 ]
941 },
942
943 # different types
944 {
945 equal => 0,
946 bindvals => [
947 'foo',
948 \'foo',
949 ['foo'],
950 { foo => 'bar' },
951 ]
952 },
953
954 # complex data structure - different
955 {
956 equal => 0,
957 bindvals => [
958 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
959 [43, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
960 [42, { foo => 'baz', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
961 [42, { bar => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
962 [42, { foo => 'bar', quuux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
963 [42, { foo => 'bar', quux => [0, 1, 2, \3, { quux => [4, 5] } ] }, 8 ],
964 [42, { foo => 'bar', quux => [1, 2, 3, { quux => [4, 5] } ] }, 8 ],
965 [42, { foo => 'bar', quux => [1, 2, \4, { quux => [4, 5] } ] }, 8 ],
966 [42, { foo => 'bar', quux => [1, 2, \3, { quuux => [4, 5] } ] }, 8 ],
967 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5, 6] } ] }, 8 ],
968 [42, { foo => 'bar', quux => [1, 2, \3, { quux => 4 } ] }, 8 ],
969 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5], quuux => 1 } ] }, 8 ],
970 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8, 9 ],
971 ]
972 },
973);
974
ca4f826a 975for my $test (@sql_tests) {
3b9d807e 976
977 # this does not work on 5.8.8 and earlier :(
978 #local @{*SQL::Abstract::Test::}{keys %{$test->{opts}}} = map { \$_ } values %{$test->{opts}}
979 # if $test->{opts};
980
981 my %restore_globals;
982
983 for (keys %{$test->{opts} || {} }) {
984 $restore_globals{$_} = ${${*SQL::Abstract::Test::}{$_}};
985 ${*SQL::Abstract::Test::}{$_} = \ do { my $cp = $test->{opts}{$_} };
986 }
987
25823711 988 my $statements = $test->{statements};
989 while (@$statements) {
990 my $sql1 = shift @$statements;
991 foreach my $sql2 (@$statements) {
e40f5df9 992
25823711 993 my $equal = eq_sql($sql1, $sql2);
e40f5df9 994
56c0595d 995 TODO: {
996 local $TODO = $test->{todo} if $test->{todo};
997
998 if ($test->{equal}) {
999 ok($equal, "equal SQL expressions should have been considered equal");
1000 } else {
1001 ok(!$equal, "different SQL expressions should have been considered not equal");
1002 }
1003
1004 if ($equal ^ $test->{equal}) {
0769ac0e 1005 my ($ast1, $ast2) = map { SQL::Abstract::Test::parse ($_) } ($sql1, $sql2);
2fadf08e 1006 $_ = dumper($_) for ($ast1, $ast2);
0769ac0e 1007
b3b79607 1008 diag "sql1: $sql1";
1009 diag "sql2: $sql2";
2fadf08e 1010 note $sql_differ || 'No differences found';
b3b79607 1011 note "ast1: $ast1";
1012 note "ast2: $ast2";
56c0595d 1013 }
25823711 1014 }
1015 }
1016 }
3b9d807e 1017
1018 ${*SQL::Abstract::Test::}{$_} = \$restore_globals{$_}
1019 for keys %restore_globals;
25823711 1020}
1021
32c34379 1022for my $test (@bind_tests) {
1023 my $bindvals = $test->{bindvals};
1024 while (@$bindvals) {
1025 my $bind1 = shift @$bindvals;
1026 foreach my $bind2 (@$bindvals) {
1027 my $equal = eq_bind($bind1, $bind2);
1028 if ($test->{equal}) {
1029 ok($equal, "equal bind values considered equal");
1030 } else {
1031 ok(!$equal, "different bind values considered not equal");
1032 }
1033
1034 if ($equal ^ $test->{equal}) {
2fadf08e 1035 diag("bind1: " . dumper($bind1));
1036 diag("bind2: " . dumper($bind2));
32c34379 1037 }
1038 }
1039 }
1040}
e7827ba2 1041
1042ok(eq_sql_bind(
1043 "SELECT * FROM foo WHERE id = ?", [42],
1044 "SELECT * FROM foo WHERE (id = ?)", [42],
1045 ),
1046 "eq_sql_bind considers equal SQL expressions and bind values equal"
1047);
1048
1049
1050ok(!eq_sql_bind(
1051 "SELECT * FROM foo WHERE id = ?", [42],
1052 "SELECT * FROM foo WHERE (id = ?)", [0],
1053 ),
1054 "eq_sql_bind considers equal SQL expressions and different bind values different"
1055);
1056
1057ok(!eq_sql_bind(
1058 "SELECT * FROM foo WHERE id = ?", [42],
1059 "SELECT * FROM bar WHERE (id = ?)", [42],
1060 ),
1061 "eq_sql_bind considers different SQL expressions and equal bind values different"
1062);
6f2a5b66 1063
1064# test diag string
1065ok (! eq_sql (
1066 'SELECT owner_name FROM books me WHERE ( source = ? )',
1067 'SELECT owner_name FROM books me WHERE ( sUOrce = ? )',
1068));
1069like(
2fadf08e 1070 $sql_differ,
6f2a5b66 1071 qr/\Q[ source ] != [ sUOrce ]/,
1072 'expected debug of literal diff',
1073);
1074
1075ok (! eq_sql (
1076 'SELECT owner_name FROM books me ORDER BY owner_name',
1077 'SELECT owner_name FROM books me GROUP BY owner_name',
1078));
1079like(
2fadf08e 1080 $sql_differ,
6f2a5b66 1081 qr/\QOP [ORDER BY] != [GROUP BY]/,
1082 'expected debug of op diff',
1083);
1084
1085ok (! eq_sql (
1086 'SELECT owner_name FROM books WHERE ( source = ? )',
1087 'SELECT owner_name FROM books'
1088));
1089
1090like(
2fadf08e 1091 $sql_differ,
6f2a5b66 1092 qr|\Q[WHERE source = ?] != [N/A]|,
1093 'expected debug of missing branch',
1094);
1095
70c6f0e9 1096
1097ok (eq_sql_bind (
1098 \[ 'SELECT foo FROM bar WHERE baz = ? or buzz = ?', [ {} => 1 ], 2 ],
1099 'SELECT foo FROM bar WHERE (baz = ?) OR buzz = ?',
1100 [ [ {} => 1 ], 2 ],
1101), 'arrayrefref unpacks correctly' );
1102
1103is_same_sql_bind(
1104 \[ 'SELECT foo FROM bar WHERE baz = ? or buzz = ?', [ {} => 1 ], 2 ],
1105 \[ 'SELECT foo FROM bar WHERE (( baz = ? OR (buzz = ?) ))', [ {} => 1 ], 2 ],
1106 'double arrayrefref unpacks correctly'
1107);
1108
10e6c946 1109done_testing;
Unnamed repository; edit this file 'description' to name the repository.