8 use SQL::Abstract::Test import => [qw(
9 eq_sql_bind eq_sql eq_bind is_same_sql_bind dumper $sql_differ
13 # WHERE condition - equal
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/SELECT foo FROM bar WHERE (a=1)/,
21 q/SELECT foo FROM bar WHERE ( a = 1 )/,
46 q/SELECT foo FROM bar WHERE ((a = 1))/,
47 q/SELECT foo FROM bar WHERE ( (a = 1) )/,
48 q/SELECT foo FROM bar WHERE ( ( a = 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/SELECT foo FROM bar WHERE ((a = 1 AND b = 1))/,
59 q/SELECT foo FROM bar WHERE (((a = 1) AND (b = 1)))/,
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)/,
107 q/SELECT foo FROM bar WHERE (a = 1 AND b = 1) AND c = 1/,
108 q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 AND c = 1)/,
109 q/SELECT foo FROM bar WHERE ((((a = 1))) AND (b = 1 AND c = 1))/,
115 q/SELECT foo FROM bar WHERE a = 1 OR b = 1 OR c = 1/,
116 q/SELECT foo FROM bar WHERE (a = 1 OR b = 1) OR c = 1/,
117 q/SELECT foo FROM bar WHERE a = 1 OR (b = 1 OR c = 1)/,
118 q/SELECT foo FROM bar WHERE a = 1 OR ((b = 1 OR (c = 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)/,
125 q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 OR c = 1 OR d = 1) AND e = 1 AND (f = 1)/,
126 q/SELECT foo FROM bar WHERE ( ((a = 1) AND ( b = 1 OR (c = 1 OR d = 1) )) AND ((e = 1)) AND f = 1) /,
132 q/SELECT foo FROM bar WHERE (a) AND (b = 2)/,
133 q/SELECT foo FROM bar WHERE (a AND b = 2)/,
134 q/SELECT foo FROM bar WHERE (a AND (b = 2))/,
135 q/SELECT foo FROM bar WHERE a AND (b = 2)/,
141 q/SELECT foo FROM bar WHERE ((NOT a) AND b = 2)/,
142 q/SELECT foo FROM bar WHERE (NOT a) AND (b = 2)/,
143 q/SELECT foo FROM bar WHERE (NOT (a)) AND b = 2/,
149 q/SELECT foo FROM bar WHERE NOT a AND (b = 2)/,
150 q/SELECT foo FROM bar WHERE (NOT a) AND (b = 2)/,
155 opts => { parenthesis_significant => 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 q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 AND c = 1)/,
161 q/SELECT foo FROM bar WHERE ((((a = 1))) AND (b = 1 AND c = 1))/,
166 opts => { parenthesis_significant => 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 q/SELECT foo FROM bar WHERE a = 1 OR (b = 1 OR c = 1)/,
171 q/SELECT foo FROM bar WHERE a = 1 OR ((b = 1 OR (c = 1)))/,
176 opts => { parenthesis_significant => 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 q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 OR c = 1 OR d = 1) AND e = 1 AND (f = 1)/,
180 q/SELECT foo FROM bar WHERE ( ((a = 1) AND ( b = 1 OR (c = 1 OR d = 1) )) AND ((e = 1)) AND f = 1) /,
184 # WHERE condition - different
188 q/SELECT foo FROM bar WHERE a = 1/,
189 q/SELECT quux FROM bar WHERE a = 1/,
190 q/SELECT foo FROM quux WHERE a = 1/,
191 q/FOOBAR foo FROM bar WHERE a = 1/,
193 q/SELECT foo FROM bar WHERE a = 2/,
194 q/SELECT foo FROM bar WHERE a < 1/,
195 q/SELECT foo FROM bar WHERE b = 1/,
196 q/SELECT foo FROM bar WHERE (c = 1)/,
197 q/SELECT foo FROM bar WHERE (d = 1)/,
199 q/SELECT foo FROM bar WHERE a = 1 AND quux/,
200 q/SELECT foo FROM bar WHERE a = 1 GROUP BY foo/,
201 q/SELECT foo FROM bar WHERE a = 1 ORDER BY foo/,
202 q/SELECT foo FROM bar WHERE a = 1 LIMIT 1/,
203 q/SELECT foo FROM bar WHERE a = 1 OFFSET 1/,
204 q/SELECT foo FROM bar JOIN quux WHERE a = 1/,
205 q/SELECT foo FROM bar JOIN quux ON a = 1 WHERE a = 1/,
211 q/SELECT foo FROM bar WHERE a = 1 AND b = 1/,
212 q/SELECT quux FROM bar WHERE a = 1 AND b = 1/,
213 q/SELECT foo FROM quux WHERE a = 1 AND b = 1/,
214 q/FOOBAR foo FROM bar WHERE a = 1 AND b = 1/,
216 q/SELECT foo FROM bar WHERE a = 2 AND b = 1/,
217 q/SELECT foo FROM bar WHERE a = 3 AND (b = 1)/,
218 q/SELECT foo FROM bar WHERE (a = 4) AND b = 1/,
219 q/SELECT foo FROM bar WHERE (a = 5) AND (b = 1)/,
220 q/SELECT foo FROM bar WHERE ((a = 6) AND (b = 1))/,
221 q/SELECT foo FROM bar WHERE ((a = 7) AND (b = 1))/,
223 q/SELECT foo FROM bar WHERE a = 1 AND b = 2/,
224 q/SELECT foo FROM bar WHERE a = 1 AND (b = 3)/,
225 q/SELECT foo FROM bar WHERE (a = 1) AND b = 4/,
226 q/SELECT foo FROM bar WHERE (a = 1) AND (b = 5)/,
227 q/SELECT foo FROM bar WHERE ((a = 1) AND (b = 6))/,
228 q/SELECT foo FROM bar WHERE ((a = 1) AND (b = 7))/,
230 q/SELECT foo FROM bar WHERE a < 1 AND b = 1/,
231 q/SELECT foo FROM bar WHERE b = 1 AND b = 1/,
232 q/SELECT foo FROM bar WHERE (c = 1) AND b = 1/,
233 q/SELECT foo FROM bar WHERE (d = 1) AND b = 1/,
235 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 AND quux/,
236 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 GROUP BY foo/,
237 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 ORDER BY foo/,
238 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 LIMIT 1/,
239 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 OFFSET 1/,
240 q/SELECT foo FROM bar JOIN quux WHERE a = 1 AND b = 1/,
241 q/SELECT foo FROM bar JOIN quux ON a = 1 WHERE a = 1 AND b = 1/,
247 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 OR c = 1/,
248 q/SELECT foo FROM bar WHERE (a = 1 AND b = 1) OR c = 1/,
249 q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 OR c = 1)/,
255 q/SELECT foo FROM bar WHERE a = 1 OR b = 1 AND c = 1/,
256 q/SELECT foo FROM bar WHERE (a = 1 OR b = 1) AND c = 1/,
257 q/SELECT foo FROM bar WHERE a = 1 OR (b = 1 AND c = 1)/,
262 opts => { parenthesis_significant => 1 },
264 q/SELECT foo FROM bar WHERE a IN (1,2,3)/,
265 q/SELECT foo FROM bar WHERE a IN (1,3,2)/,
266 q/SELECT foo FROM bar WHERE a IN ((1,2,3))/,
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
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 = ?/,
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 = ?) ) )/,
284 # IS NULL (special LHS-only op)
288 q/WHERE a IS NOT NULL AND b IS NULL/,
289 q/WHERE (a IS NOT NULL) AND b IS NULL/,
290 q/WHERE a IS NOT NULL AND (b IS NULL)/,
291 q/WHERE (a IS NOT NULL) AND ((b IS NULL))/,
295 # JOIN condition - equal
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/SELECT foo FROM bar JOIN baz ON (a=1) WHERE x = 1/,
303 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 q/SELECT foo FROM bar JOIN baz ON ( (a = 1) ) WHERE x = 1/,
342 q/SELECT foo FROM bar JOIN baz ON ( ( a = 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/SELECT foo FROM bar JOIN baz ON ((a = 1 AND b = 1)) WHERE x = 1/,
353 q/SELECT foo FROM bar JOIN baz ON (((a = 1) AND (b = 1))) WHERE x = 1/,
413 # JOIN condition - different
417 q/SELECT foo FROM bar JOIN quux ON a = 1 WHERE quuux/,
418 q/SELECT quux FROM bar JOIN quux ON a = 1 WHERE quuux/,
419 q/SELECT foo FROM quux JOIN quux ON a = 1 WHERE quuux/,
420 q/FOOBAR foo FROM bar JOIN quux ON a = 1 WHERE quuux/,
422 q/SELECT foo FROM bar JOIN quux ON a = 2 WHERE quuux/,
423 q/SELECT foo FROM bar JOIN quux ON a < 1 WHERE quuux/,
424 q/SELECT foo FROM bar JOIN quux ON b = 1 WHERE quuux/,
425 q/SELECT foo FROM bar JOIN quux ON (c = 1) WHERE quuux/,
426 q/SELECT foo FROM bar JOIN quux ON (d = 1) WHERE quuux/,
428 q/SELECT foo FROM bar JOIN quux ON a = 1 AND quuux/,
429 q/SELECT foo FROM bar JOIN quux ON a = 1 GROUP BY foo/,
430 q/SELECT foo FROM bar JOIN quux ON a = 1 ORDER BY foo/,
431 q/SELECT foo FROM bar JOIN quux ON a = 1 LIMIT 1/,
432 q/SELECT foo FROM bar JOIN quux ON a = 1 OFFSET 1/,
433 q/SELECT foo FROM bar JOIN quux ON a = 1 JOIN quuux/,
434 q/SELECT foo FROM bar JOIN quux ON a = 1 JOIN quuux ON a = 1/,
440 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 WHERE quuux/,
441 q/SELECT quux FROM bar JOIN quux ON a = 1 AND b = 1 WHERE quuux/,
442 q/SELECT foo FROM quux JOIN quux ON a = 1 AND b = 1 WHERE quuux/,
443 q/FOOBAR foo FROM bar JOIN quux ON a = 1 AND b = 1 WHERE quuux/,
445 q/SELECT foo FROM bar JOIN quux ON a = 2 AND b = 1 WHERE quuux/,
446 q/SELECT foo FROM bar JOIN quux ON a = 3 AND (b = 1) WHERE quuux/,
447 q/SELECT foo FROM bar JOIN quux ON (a = 4) AND b = 1 WHERE quuux/,
448 q/SELECT foo FROM bar JOIN quux ON (a = 5) AND (b = 1) WHERE quuux/,
449 q/SELECT foo FROM bar JOIN quux ON ((a = 6) AND (b = 1)) WHERE quuux/,
450 q/SELECT foo FROM bar JOIN quux ON ((a = 7) AND (b = 1)) WHERE quuux/,
452 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 2 WHERE quuux/,
453 q/SELECT foo FROM bar JOIN quux ON a = 1 AND (b = 3) WHERE quuux/,
454 q/SELECT foo FROM bar JOIN quux ON (a = 1) AND b = 4 WHERE quuux/,
455 q/SELECT foo FROM bar JOIN quux ON (a = 1) AND (b = 5) WHERE quuux/,
456 q/SELECT foo FROM bar JOIN quux ON ((a = 1) AND (b = 6)) WHERE quuux/,
457 q/SELECT foo FROM bar JOIN quux ON ((a = 1) AND (b = 7)) WHERE quuux/,
459 q/SELECT foo FROM bar JOIN quux ON a < 1 AND b = 1 WHERE quuux/,
460 q/SELECT foo FROM bar JOIN quux ON b = 1 AND b = 1 WHERE quuux/,
461 q/SELECT foo FROM bar JOIN quux ON (c = 1) AND b = 1 WHERE quuux/,
462 q/SELECT foo FROM bar JOIN quux ON (d = 1) AND b = 1 WHERE quuux/,
464 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 AND quuux/,
465 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 GROUP BY foo/,
466 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 ORDER BY foo/,
467 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 LIMIT 1/,
468 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 OFFSET 1/,
469 q/SELECT foo FROM bar JOIN quux JOIN quuux ON a = 1 AND b = 1/,
470 q/SELECT foo FROM bar JOIN quux ON a = 1 JOIN quuux ON a = 1 AND b = 1/,
474 # DISTINCT ON (...) not confused with JOIN ON (...)
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/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE (a=1)/,
482 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ( a = 1 )/,
484 SELECT DISTINCT ON (foo, quux)
493 SELECT DISTINCT ON (foo, quux)
502 SELECT DISTINCT ON (foo, quux)
510 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ((a = 1))/,
511 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ( (a = 1) )/,
512 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar 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 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE a = 1/,
523 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 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 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 1)) AS foo WHERE a = 1/,
533 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 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 1)) AS foo WHERE (a = 1)/,
539 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 1))) AS foo WHERE (a = 1)/,
543 # subselects - different
547 q/DELETE FROM cd WHERE ( cdid IN ( SELECT me.cdid FROM (SELECT * FROM cd me WHERE ( year != ? ) GROUP BY me.cdid) me WHERE ( year != ? ) ) )/,
548 q/DELETE FROM cd WHERE ( cdid IN ( SELECT me.cdid FROM cd me WHERE ( year != ? ) GROUP BY me.cdid ) )/,
554 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE a = 1/,
555 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE a = 2/,
556 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE (a = 3)/,
557 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE a = 4/,
558 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE (a = 5)/,
559 q/SELECT * FROM (SELECT * FROM bar WHERE b = 2) AS foo WHERE a = 1/,
560 q/SELECT * FROM (SELECT * FROM bar WHERE b = 3) AS foo WHERE (a = 1)/,
561 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 4)) AS foo WHERE a = 1/,
562 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 5)) AS foo WHERE (a = 1)/,
568 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE a = 1/,
569 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 2) AS foo WHERE a = 1/,
570 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 3)) AS foo WHERE a = 1/,
571 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 4)) AS foo WHERE a = 1/,
572 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 5))) AS foo WHERE a = 1/,
574 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 6) AS foo WHERE (a = 1)/,
575 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 7) AS foo WHERE (a = 1)/,
576 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 8)) AS foo WHERE (a = 1)/,
577 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 9)) AS foo WHERE (a = 1)/,
578 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 10))) AS foo WHERE (a = 1)/,
580 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE a = 2/,
581 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 2) AS foo WHERE a = 2/,
582 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 3)) AS foo WHERE a = 2/,
583 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 4)) AS foo WHERE a = 2/,
584 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 5))) AS foo WHERE a = 2/,
586 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 6) AS foo WHERE (a = 2)/,
587 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 7) AS foo WHERE (a = 2)/,
588 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 8)) AS foo WHERE (a = 2)/,
589 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 9)) AS foo WHERE (a = 2)/,
590 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 10))) AS foo WHERE (a = 2)/,
598 q/SELECT * FROM foo ORDER BY bar/,
599 q/SELECT * FROM foo ORDER BY bar ASC/,
600 q/SELECT * FROM foo ORDER BY bar asc/,
606 q/SELECT * FROM foo ORDER BY bar, baz ASC/,
607 q/SELECT * FROM foo ORDER BY bar ASC, baz/,
608 q/SELECT * FROM foo ORDER BY bar asc, baz ASC/,
609 q/SELECT * FROM foo ORDER BY bar, baz/,
615 q/ORDER BY colA, colB LIKE ? DESC, colC LIKE ?/,
616 q/ORDER BY colA ASC, colB LIKE ? DESC, colC LIKE ? ASC/,
622 q/ORDER BY name + ?, [me].[id]/,
623 q/ORDER BY name + ? ASC, [me].[id]/,
628 opts => { order_by_asc_significant => 1 },
630 q/SELECT * FROM foo ORDER BY bar/,
631 q/SELECT * FROM foo ORDER BY bar ASC/,
632 q/SELECT * FROM foo ORDER BY bar desc/,
640 'SELECT a,b,c FROM foo',
641 'SELECT a,c,b FROM foo',
642 'SELECT b,a,c FROM foo',
643 'SELECT b,c,a FROM foo',
644 'SELECT c,a,b FROM foo',
645 'SELECT c,b,a FROM foo',
651 'SELECT * FROM foo WHERE a IN (1,2,3)',
652 'SELECT * FROM foo WHERE a IN (1,3,2)',
653 'SELECT * FROM foo WHERE a IN (2,1,3)',
654 'SELECT * FROM foo WHERE a IN (2,3,1)',
655 'SELECT * FROM foo WHERE a IN (3,1,2)',
656 'SELECT * FROM foo WHERE a IN (3,2,1)',
664 'SELECT a,b FROM foo',
665 'SELECT a,,b FROM foo',
666 'SELECT a,b, FROM foo',
667 'SELECT ,a,b, FROM foo',
668 'SELECT ,a,,b, FROM foo',
676 'SELECT count(*) FROM foo',
677 'SELECT count(*) AS bar FROM foo',
678 'SELECT count(*) AS "bar" FROM foo',
679 'SELECT count(a) FROM foo',
680 'SELECT count(1) FROM foo',
686 'SELECT foo() bar FROM baz',
687 'SELECT foo ( )bar FROM baz',
688 'SELECT foo (())bar FROM baz',
689 'SELECT foo(( ) ) bar FROM baz',
695 'SELECT foo() FROM bar',
696 'SELECT foo FROM bar',
697 'SELECT foo FROM bar ()',
703 'SELECT COUNT * FROM foo',
704 'SELECT COUNT( * ) FROM foo',
707 # single ? of unknown funcs do not unroll unless
708 # explicitly allowed (e.g. Like)
712 'SELECT foo FROM bar WHERE bar > foo ?',
713 'SELECT foo FROM bar WHERE bar > foo( ? )',
719 'SELECT foo FROM bar WHERE bar LIKE ?',
720 'SELECT foo FROM bar WHERE bar LiKe (?)',
721 'SELECT foo FROM bar WHERE bar lIkE( (?))',
728 'SELECT foo FROM bar WHERE foo IN (?, ?)',
729 'SELECT foo FROM bar WHERE foo IN ?, ?',
736 'SELECT * FROM foo WHERE 1 = ( a > b)',
737 'SELECT * FROM foo WHERE 1 = a > b',
738 'SELECT * FROM foo WHERE (1 = a) > b',
744 'SELECT * FROM foo WHERE bar = baz(buzz)',
745 'SELECT * FROM foo WHERE bar = (baz( buzz ))',
752 'WHERE ( foo GLOB ? )',
759 'SELECT FIRST ? SKIP ? [me].[id], [me].[owner]
762 SELECT FIRST ? SKIP ? [owner].[id]
763 FROM [owners] [owner]
764 WHERE ( [books].[owner] = [owner].[id] )
765 )) AND [source] = ? ) )',
766 'SELECT FIRST ? SKIP ? [me].[id], [me].[owner]
769 SELECT FIRST ? SKIP ? [owner].[id]
770 FROM [owners] [owner]
771 WHERE ( [books].[owner] = [owner].[id] )
772 ) AND [source] = ? ) )',
778 'WHERE foo = ? FETCH FIRST 1 ROWS ONLY',
779 'WHERE ( foo = ? ) FETCH FIRST 1 ROWS ONLY',
780 'WHERE (( foo = ? )) FETCH FIRST 1 ROWS ONLY',
866 { foo => 42, bar => 1 },
867 { foo => 42, bar => 1 },
868 { foo => '42', bar => 1 },
872 # blessed object - equal
876 bless(\(local $_ = 42), 'Life::Universe::Everything'),
877 bless(\(local $_ = 42), 'Life::Universe::Everything'),
883 bless([42], 'Life::Universe::Everything'),
884 bless([42], 'Life::Universe::Everything'),
890 bless({ answer => 42 }, 'Life::Universe::Everything'),
891 bless({ answer => 42 }, 'Life::Universe::Everything'),
895 # complex data structure - equal
899 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
900 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
915 # scalarref - different
925 # arrayref - different
935 # hashref - different
956 # complex data structure - different
960 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
961 [43, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
962 [42, { foo => 'baz', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
963 [42, { bar => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
964 [42, { foo => 'bar', quuux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
965 [42, { foo => 'bar', quux => [0, 1, 2, \3, { quux => [4, 5] } ] }, 8 ],
966 [42, { foo => 'bar', quux => [1, 2, 3, { quux => [4, 5] } ] }, 8 ],
967 [42, { foo => 'bar', quux => [1, 2, \4, { quux => [4, 5] } ] }, 8 ],
968 [42, { foo => 'bar', quux => [1, 2, \3, { quuux => [4, 5] } ] }, 8 ],
969 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5, 6] } ] }, 8 ],
970 [42, { foo => 'bar', quux => [1, 2, \3, { quux => 4 } ] }, 8 ],
971 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5], quuux => 1 } ] }, 8 ],
972 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8, 9 ],
977 for my $test ( @sql_tests ) {
979 # this does not work on 5.8.8 and earlier :(
980 #local @{*SQL::Abstract::Test::}{keys %{$test->{opts}}} = map { \$_ } values %{$test->{opts}}
985 for (keys %{$test->{opts} || {} }) {
986 $restore_globals{$_} = ${${*SQL::Abstract::Test::}{$_}};
987 ${*SQL::Abstract::Test::}{$_} = \ do { my $cp = $test->{opts}{$_} };
990 my $statements = $test->{statements};
991 while (@$statements) {
992 my $sql1 = shift @$statements;
993 foreach my $sql2 (@$statements) {
995 my $equal = eq_sql($sql1, $sql2);
998 local $TODO = $test->{todo} if $test->{todo};
1000 if ($test->{equal}) {
1001 ok($equal, "equal SQL expressions should have been considered equal");
1003 ok(!$equal, "different SQL expressions should have been considered not equal");
1006 if ($equal ^ $test->{equal}) {
1007 my ($ast1, $ast2) = map { SQL::Abstract::Test::parse ($_) } ($sql1, $sql2);
1008 $_ = dumper($_) for ($ast1, $ast2);
1012 note $sql_differ || 'No differences found';
1020 ${*SQL::Abstract::Test::}{$_} = \$restore_globals{$_}
1021 for keys %restore_globals;
1024 for my $test (@bind_tests) {
1025 my $bindvals = $test->{bindvals};
1026 while (@$bindvals) {
1027 my $bind1 = shift @$bindvals;
1028 foreach my $bind2 (@$bindvals) {
1029 my $equal = eq_bind($bind1, $bind2);
1030 if ($test->{equal}) {
1031 ok($equal, "equal bind values considered equal");
1033 ok(!$equal, "different bind values considered not equal");
1036 if ($equal ^ $test->{equal}) {
1037 diag("bind1: " . dumper($bind1));
1038 diag("bind2: " . dumper($bind2));
1045 "SELECT * FROM foo WHERE id = ?", [42],
1046 "SELECT * FROM foo WHERE (id = ?)", [42],
1048 "eq_sql_bind considers equal SQL expressions and bind values equal"
1053 "SELECT * FROM foo WHERE id = ?", [42],
1054 "SELECT * FROM foo WHERE (id = ?)", [0],
1056 "eq_sql_bind considers equal SQL expressions and different bind values different"
1060 "SELECT * FROM foo WHERE id = ?", [42],
1061 "SELECT * FROM bar WHERE (id = ?)", [42],
1063 "eq_sql_bind considers different SQL expressions and equal bind values different"
1068 'SELECT owner_name FROM books me WHERE ( source = ? )',
1069 'SELECT owner_name FROM books me WHERE ( sUOrce = ? )',
1073 qr/\Q[ source ] != [ sUOrce ]/,
1074 'expected debug of literal diff',
1078 'SELECT owner_name FROM books me ORDER BY owner_name',
1079 'SELECT owner_name FROM books me GROUP BY owner_name',
1083 qr/\QOP [ORDER BY] != [GROUP BY]/,
1084 'expected debug of op diff',
1088 'SELECT owner_name FROM books WHERE ( source = ? )',
1089 'SELECT owner_name FROM books'
1094 qr|\Q[WHERE source = ?] != [N/A]|,
1095 'expected debug of missing branch',