5 use List::Util qw(sum);
10 $Data::Dumper::Terse = 1;
11 $Data::Dumper::Sortkeys = 1;
14 # WHERE condition - equal
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)/,
22 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) )/,
49 q/SELECT foo FROM bar WHERE ( ( a = 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))/,
60 q/SELECT foo FROM bar WHERE (((a = 1) AND (b = 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)/,
110 q/SELECT foo FROM bar WHERE ((((a = 1))) AND (b = 1 AND 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)/,
119 q/SELECT foo FROM bar WHERE a = 1 OR ((b = 1 OR (c = 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)/,
127 q/SELECT foo FROM bar WHERE ( ((a = 1) AND ( b = 1 OR (c = 1 OR d = 1) )) AND ((e = 1)) AND f = 1) /,
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))/,
136 q/SELECT foo FROM bar WHERE 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)/,
144 q/SELECT foo FROM bar WHERE (NOT (a)) AND b = 2/,
150 q/SELECT foo FROM bar WHERE NOT a AND (b = 2)/,
151 q/SELECT foo FROM bar WHERE (NOT a) AND (b = 2)/,
156 parenthesis_significant => 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)/,
162 q/SELECT foo FROM bar WHERE ((((a = 1))) AND (b = 1 AND c = 1))/,
167 parenthesis_significant => 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)/,
172 q/SELECT foo FROM bar WHERE a = 1 OR ((b = 1 OR (c = 1)))/,
177 parenthesis_significant => 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)/,
181 q/SELECT foo FROM bar WHERE ( ((a = 1) AND ( b = 1 OR (c = 1 OR d = 1) )) AND ((e = 1)) AND f = 1) /,
185 # WHERE condition - different
189 q/SELECT foo FROM bar WHERE a = 1/,
190 q/SELECT quux FROM bar WHERE a = 1/,
191 q/SELECT foo FROM quux WHERE a = 1/,
192 q/FOOBAR foo FROM bar WHERE a = 1/,
194 q/SELECT foo FROM bar WHERE a = 2/,
195 q/SELECT foo FROM bar WHERE a < 1/,
196 q/SELECT foo FROM bar WHERE b = 1/,
197 q/SELECT foo FROM bar WHERE (c = 1)/,
198 q/SELECT foo FROM bar WHERE (d = 1)/,
200 q/SELECT foo FROM bar WHERE a = 1 AND quux/,
201 q/SELECT foo FROM bar WHERE a = 1 GROUP BY foo/,
202 q/SELECT foo FROM bar WHERE a = 1 ORDER BY foo/,
203 q/SELECT foo FROM bar WHERE a = 1 LIMIT 1/,
204 q/SELECT foo FROM bar WHERE a = 1 OFFSET 1/,
205 q/SELECT foo FROM bar JOIN quux WHERE a = 1/,
206 q/SELECT foo FROM bar JOIN quux ON a = 1 WHERE a = 1/,
212 q/SELECT foo FROM bar WHERE a = 1 AND b = 1/,
213 q/SELECT quux FROM bar WHERE a = 1 AND b = 1/,
214 q/SELECT foo FROM quux WHERE a = 1 AND b = 1/,
215 q/FOOBAR foo FROM bar WHERE a = 1 AND b = 1/,
217 q/SELECT foo FROM bar WHERE a = 2 AND b = 1/,
218 q/SELECT foo FROM bar WHERE a = 3 AND (b = 1)/,
219 q/SELECT foo FROM bar WHERE (a = 4) AND b = 1/,
220 q/SELECT foo FROM bar WHERE (a = 5) AND (b = 1)/,
221 q/SELECT foo FROM bar WHERE ((a = 6) AND (b = 1))/,
222 q/SELECT foo FROM bar WHERE ((a = 7) AND (b = 1))/,
224 q/SELECT foo FROM bar WHERE a = 1 AND b = 2/,
225 q/SELECT foo FROM bar WHERE a = 1 AND (b = 3)/,
226 q/SELECT foo FROM bar WHERE (a = 1) AND b = 4/,
227 q/SELECT foo FROM bar WHERE (a = 1) AND (b = 5)/,
228 q/SELECT foo FROM bar WHERE ((a = 1) AND (b = 6))/,
229 q/SELECT foo FROM bar WHERE ((a = 1) AND (b = 7))/,
231 q/SELECT foo FROM bar WHERE a < 1 AND b = 1/,
232 q/SELECT foo FROM bar WHERE b = 1 AND b = 1/,
233 q/SELECT foo FROM bar WHERE (c = 1) AND b = 1/,
234 q/SELECT foo FROM bar WHERE (d = 1) AND b = 1/,
236 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 AND quux/,
237 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 GROUP BY foo/,
238 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 ORDER BY foo/,
239 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 LIMIT 1/,
240 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 OFFSET 1/,
241 q/SELECT foo FROM bar JOIN quux WHERE a = 1 AND b = 1/,
242 q/SELECT foo FROM bar JOIN quux ON a = 1 WHERE a = 1 AND b = 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/,
250 q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 OR 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/,
258 q/SELECT foo FROM bar WHERE a = 1 OR (b = 1 AND c = 1)/,
263 parenthesis_significant => 1,
265 q/SELECT foo FROM bar WHERE a IN (1,2,3)/,
266 q/SELECT foo FROM bar WHERE a IN (1,3,2)/,
267 q/SELECT foo FROM bar WHERE a IN ((1,2,3))/,
273 # BETWEEN with/without parenthesis around itself/RHS is a sticky business
274 # if I made a mistake here, simply rewrite the special BETWEEN handling in
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 = ? )/,
281 q/SELECT foo FROM bar WHERE ( (completion_date BETWEEN (? AND ? AND status = ?) ) )/,
285 # IS NULL (special LHS-only op)
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)/,
292 q/WHERE (a IS NOT NULL) AND ((b IS NULL))/,
296 # JOIN condition - equal
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/,
304 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/,
343 q/SELECT foo FROM bar JOIN baz ON ( ( a = 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/,
354 q/SELECT foo FROM bar JOIN baz ON (((a = 1) AND (b = 1))) WHERE x = 1/,
414 # JOIN condition - different
418 q/SELECT foo FROM bar JOIN quux ON a = 1 WHERE quuux/,
419 q/SELECT quux FROM bar JOIN quux ON a = 1 WHERE quuux/,
420 q/SELECT foo FROM quux JOIN quux ON a = 1 WHERE quuux/,
421 q/FOOBAR foo FROM bar JOIN quux ON a = 1 WHERE quuux/,
423 q/SELECT foo FROM bar JOIN quux ON a = 2 WHERE quuux/,
424 q/SELECT foo FROM bar JOIN quux ON a < 1 WHERE quuux/,
425 q/SELECT foo FROM bar JOIN quux ON b = 1 WHERE quuux/,
426 q/SELECT foo FROM bar JOIN quux ON (c = 1) WHERE quuux/,
427 q/SELECT foo FROM bar JOIN quux ON (d = 1) WHERE quuux/,
429 q/SELECT foo FROM bar JOIN quux ON a = 1 AND quuux/,
430 q/SELECT foo FROM bar JOIN quux ON a = 1 GROUP BY foo/,
431 q/SELECT foo FROM bar JOIN quux ON a = 1 ORDER BY foo/,
432 q/SELECT foo FROM bar JOIN quux ON a = 1 LIMIT 1/,
433 q/SELECT foo FROM bar JOIN quux ON a = 1 OFFSET 1/,
434 q/SELECT foo FROM bar JOIN quux ON a = 1 JOIN quuux/,
435 q/SELECT foo FROM bar JOIN quux ON a = 1 JOIN quuux ON a = 1/,
441 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 WHERE quuux/,
442 q/SELECT quux FROM bar JOIN quux ON a = 1 AND b = 1 WHERE quuux/,
443 q/SELECT foo FROM quux JOIN quux ON a = 1 AND b = 1 WHERE quuux/,
444 q/FOOBAR foo FROM bar JOIN quux ON a = 1 AND b = 1 WHERE quuux/,
446 q/SELECT foo FROM bar JOIN quux ON a = 2 AND b = 1 WHERE quuux/,
447 q/SELECT foo FROM bar JOIN quux ON a = 3 AND (b = 1) WHERE quuux/,
448 q/SELECT foo FROM bar JOIN quux ON (a = 4) AND b = 1 WHERE quuux/,
449 q/SELECT foo FROM bar JOIN quux ON (a = 5) AND (b = 1) WHERE quuux/,
450 q/SELECT foo FROM bar JOIN quux ON ((a = 6) AND (b = 1)) WHERE quuux/,
451 q/SELECT foo FROM bar JOIN quux ON ((a = 7) AND (b = 1)) WHERE quuux/,
453 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 2 WHERE quuux/,
454 q/SELECT foo FROM bar JOIN quux ON a = 1 AND (b = 3) WHERE quuux/,
455 q/SELECT foo FROM bar JOIN quux ON (a = 1) AND b = 4 WHERE quuux/,
456 q/SELECT foo FROM bar JOIN quux ON (a = 1) AND (b = 5) WHERE quuux/,
457 q/SELECT foo FROM bar JOIN quux ON ((a = 1) AND (b = 6)) WHERE quuux/,
458 q/SELECT foo FROM bar JOIN quux ON ((a = 1) AND (b = 7)) WHERE quuux/,
460 q/SELECT foo FROM bar JOIN quux ON a < 1 AND b = 1 WHERE quuux/,
461 q/SELECT foo FROM bar JOIN quux ON b = 1 AND b = 1 WHERE quuux/,
462 q/SELECT foo FROM bar JOIN quux ON (c = 1) AND b = 1 WHERE quuux/,
463 q/SELECT foo FROM bar JOIN quux ON (d = 1) AND b = 1 WHERE quuux/,
465 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 AND quuux/,
466 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 GROUP BY foo/,
467 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 ORDER BY foo/,
468 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 LIMIT 1/,
469 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 OFFSET 1/,
470 q/SELECT foo FROM bar JOIN quux JOIN quuux ON a = 1 AND b = 1/,
471 q/SELECT foo FROM bar JOIN quux ON a = 1 JOIN quuux ON a = 1 AND b = 1/,
475 # DISTINCT ON (...) not confused with JOIN ON (...)
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)/,
483 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ( a = 1 )/,
485 SELECT DISTINCT ON (foo, quux)
494 SELECT DISTINCT ON (foo, quux)
503 SELECT DISTINCT ON (foo, quux)
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) )/,
513 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar 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/,
524 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 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/,
534 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)/,
540 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 1))) AS foo WHERE (a = 1)/,
544 # subselects - different
548 q/DELETE FROM cd WHERE ( cdid IN ( SELECT me.cdid FROM (SELECT * FROM cd me WHERE ( year != ? ) GROUP BY me.cdid) me WHERE ( year != ? ) ) )/,
549 q/DELETE FROM cd WHERE ( cdid IN ( SELECT me.cdid FROM cd me WHERE ( year != ? ) GROUP BY me.cdid ) )/,
555 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE a = 1/,
556 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE a = 2/,
557 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE (a = 3)/,
558 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE a = 4/,
559 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE (a = 5)/,
560 q/SELECT * FROM (SELECT * FROM bar WHERE b = 2) AS foo WHERE a = 1/,
561 q/SELECT * FROM (SELECT * FROM bar WHERE b = 3) AS foo WHERE (a = 1)/,
562 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 4)) AS foo WHERE a = 1/,
563 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 5)) AS foo WHERE (a = 1)/,
569 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE a = 1/,
570 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 2) AS foo WHERE a = 1/,
571 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 3)) AS foo WHERE a = 1/,
572 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 4)) AS foo WHERE a = 1/,
573 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 5))) AS foo WHERE a = 1/,
575 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 6) AS foo WHERE (a = 1)/,
576 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 7) AS foo WHERE (a = 1)/,
577 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 8)) AS foo WHERE (a = 1)/,
578 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 9)) AS foo WHERE (a = 1)/,
579 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 10))) AS foo WHERE (a = 1)/,
581 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE a = 2/,
582 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 2) AS foo WHERE a = 2/,
583 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 3)) AS foo WHERE a = 2/,
584 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 4)) AS foo WHERE a = 2/,
585 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 5))) AS foo WHERE a = 2/,
587 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 6) AS foo WHERE (a = 2)/,
588 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 7) AS foo WHERE (a = 2)/,
589 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 8)) AS foo WHERE (a = 2)/,
590 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 9)) AS foo WHERE (a = 2)/,
591 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 10))) AS foo WHERE (a = 2)/,
599 'SELECT a,b,c FROM foo',
600 'SELECT a,c,b FROM foo',
601 'SELECT b,a,c FROM foo',
602 'SELECT b,c,a FROM foo',
603 'SELECT c,a,b FROM foo',
604 'SELECT c,b,a FROM foo',
610 'SELECT * FROM foo WHERE a IN (1,2,3)',
611 'SELECT * FROM foo WHERE a IN (1,3,2)',
612 'SELECT * FROM foo WHERE a IN (2,1,3)',
613 'SELECT * FROM foo WHERE a IN (2,3,1)',
614 'SELECT * FROM foo WHERE a IN (3,1,2)',
615 'SELECT * FROM foo WHERE a IN (3,2,1)',
623 'SELECT a,b FROM foo',
624 'SELECT a,,b FROM foo',
625 'SELECT a,b, FROM foo',
626 'SELECT ,a,b, FROM foo',
627 'SELECT ,a,,b, FROM foo',
635 'SELECT count(*) FROM foo',
636 'SELECT count(*) AS bar FROM foo',
637 'SELECT count(*) AS "bar" FROM foo',
638 'SELECT count(a) FROM foo',
639 'SELECT count(1) FROM foo',
645 'SELECT foo() bar FROM baz',
646 'SELECT foo ( )bar FROM baz',
647 'SELECT foo (())bar FROM baz',
648 'SELECT foo(( ) ) bar FROM baz',
654 'SELECT foo() FROM bar',
655 'SELECT foo FROM bar',
656 'SELECT foo FROM bar ()',
662 'SELECT COUNT * FROM foo',
663 'SELECT COUNT( * ) FROM foo',
666 # single ? of unknown funcs do not unroll unless
667 # explicitly allowed (e.g. Like)
671 'SELECT foo FROM bar WHERE bar > foo ?',
672 'SELECT foo FROM bar WHERE bar > foo( ? )',
678 'SELECT foo FROM bar WHERE bar LIKE ?',
679 'SELECT foo FROM bar WHERE bar LiKe (?)',
680 'SELECT foo FROM bar WHERE bar lIkE( (?))',
687 'SELECT foo FROM bar WHERE foo IN (?, ?)',
688 'SELECT foo FROM bar WHERE foo IN ?, ?',
695 'SELECT * FROM foo WHERE 1 = ( a > b)',
696 'SELECT * FROM foo WHERE 1 = a > b',
697 'SELECT * FROM foo WHERE (1 = a) > b',
703 'SELECT * FROM foo WHERE bar = baz(buzz)',
704 'SELECT * FROM foo WHERE bar = (baz( buzz ))',
711 'WHERE ( foo GLOB ? )',
798 { foo => 42, bar => 1 },
799 { foo => 42, bar => 1 },
800 { foo => '42', bar => 1 },
804 # blessed object - equal
808 bless(\(local $_ = 42), 'Life::Universe::Everything'),
809 bless(\(local $_ = 42), 'Life::Universe::Everything'),
815 bless([42], 'Life::Universe::Everything'),
816 bless([42], 'Life::Universe::Everything'),
822 bless({ answer => 42 }, 'Life::Universe::Everything'),
823 bless({ answer => 42 }, 'Life::Universe::Everything'),
827 # complex data structure - equal
831 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
832 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
847 # scalarref - different
857 # arrayref - different
867 # hashref - different
888 # complex data structure - different
892 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
893 [43, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
894 [42, { foo => 'baz', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
895 [42, { bar => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
896 [42, { foo => 'bar', quuux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
897 [42, { foo => 'bar', quux => [0, 1, 2, \3, { quux => [4, 5] } ] }, 8 ],
898 [42, { foo => 'bar', quux => [1, 2, 3, { quux => [4, 5] } ] }, 8 ],
899 [42, { foo => 'bar', quux => [1, 2, \4, { quux => [4, 5] } ] }, 8 ],
900 [42, { foo => 'bar', quux => [1, 2, \3, { quuux => [4, 5] } ] }, 8 ],
901 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5, 6] } ] }, 8 ],
902 [42, { foo => 'bar', quux => [1, 2, \3, { quux => 4 } ] }, 8 ],
903 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5], quuux => 1 } ] }, 8 ],
904 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8, 9 ],
911 map { $_ * ($_ - 1) / 2 }
912 map { scalar @{$_->{statements}} }
916 map { $_ * ($_ - 1) / 2 }
917 map { scalar @{$_->{bindvals}} }
922 use_ok('SQL::Abstract::Test', import => [qw(
923 eq_sql_bind eq_sql eq_bind is_same_sql_bind
926 for my $test (@sql_tests) {
927 my $statements = $test->{statements};
928 while (@$statements) {
929 my $sql1 = shift @$statements;
930 foreach my $sql2 (@$statements) {
932 no warnings qw/once/; # perl 5.10 is dumb
933 local $SQL::Abstract::Test::parenthesis_significant = $test->{parenthesis_significant}
934 if $test->{parenthesis_significant};
935 my $equal = eq_sql($sql1, $sql2);
938 local $TODO = $test->{todo} if $test->{todo};
940 if ($test->{equal}) {
941 ok($equal, "equal SQL expressions should have been considered equal");
943 ok(!$equal, "different SQL expressions should have been considered not equal");
946 if ($equal ^ $test->{equal}) {
947 my ($ast1, $ast2) = map { SQL::Abstract::Test::parse ($_) } ($sql1, $sql2);
948 $_ = Dumper $_ for ($ast1, $ast2);
952 note $SQL::Abstract::Test::sql_differ;
961 for my $test (@bind_tests) {
962 my $bindvals = $test->{bindvals};
964 my $bind1 = shift @$bindvals;
965 foreach my $bind2 (@$bindvals) {
966 my $equal = eq_bind($bind1, $bind2);
967 if ($test->{equal}) {
968 ok($equal, "equal bind values considered equal");
970 ok(!$equal, "different bind values considered not equal");
973 if ($equal ^ $test->{equal}) {
974 diag("bind1: " . Dumper($bind1));
975 diag("bind2: " . Dumper($bind2));
982 "SELECT * FROM foo WHERE id = ?", [42],
983 "SELECT * FROM foo WHERE (id = ?)", [42],
985 "eq_sql_bind considers equal SQL expressions and bind values equal"
990 "SELECT * FROM foo WHERE id = ?", [42],
991 "SELECT * FROM foo WHERE (id = ?)", [0],
993 "eq_sql_bind considers equal SQL expressions and different bind values different"
997 "SELECT * FROM foo WHERE id = ?", [42],
998 "SELECT * FROM bar WHERE (id = ?)", [42],
1000 "eq_sql_bind considers different SQL expressions and equal bind values different"
1005 'SELECT owner_name FROM books me WHERE ( source = ? )',
1006 'SELECT owner_name FROM books me WHERE ( sUOrce = ? )',
1009 $SQL::Abstract::Test::sql_differ,
1010 qr/\Q[ source ] != [ sUOrce ]/,
1011 'expected debug of literal diff',
1015 'SELECT owner_name FROM books me ORDER BY owner_name',
1016 'SELECT owner_name FROM books me GROUP BY owner_name',
1019 $SQL::Abstract::Test::sql_differ,
1020 qr/\QOP [ORDER BY] != [GROUP BY]/,
1021 'expected debug of op diff',
1025 'SELECT owner_name FROM books WHERE ( source = ? )',
1026 'SELECT owner_name FROM books'
1030 $SQL::Abstract::Test::sql_differ,
1031 qr|\Q[WHERE source = ?] != [N/A]|,
1032 'expected debug of missing branch',