5 use List::Util qw(sum);
10 $Data::Dumper::Terse = 1;
11 $Data::Dumper::Sortkeys = 1;
13 # equivalent to $Module::Install::AUTHOR
17 ( -e ($^O eq 'VMS' ? './inc/_author' : './inc/.author') )
20 if (not $author and not $ENV{SQLATEST_TESTER} and not $ENV{AUTOMATED_TESTING}) {
21 plan skip_all => 'Skipping resource intensive self-tests, use SQLATEST_TESTER=1 to run';
25 # WHERE condition - equal
29 q/SELECT foo FROM bar WHERE a = 1/,
30 q/SELECT foo FROM bar WHERE a=1/,
31 q/SELECT foo FROM bar WHERE (a = 1)/,
32 q/SELECT foo FROM bar WHERE (a=1)/,
33 q/SELECT foo FROM bar WHERE ( a = 1 )/,
58 q/SELECT foo FROM bar WHERE ((a = 1))/,
59 q/SELECT foo FROM bar WHERE ( (a = 1) )/,
60 q/SELECT foo FROM bar WHERE ( ( a = 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/SELECT foo FROM bar WHERE (a = 1 AND b = 1)/,
70 q/SELECT foo FROM bar WHERE ((a = 1 AND b = 1))/,
71 q/SELECT foo FROM bar WHERE (((a = 1) AND (b = 1)))/,
117 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 AND c = 1/,
118 q/SELECT foo FROM bar WHERE (a = 1 AND b = 1 AND c = 1)/,
119 q/SELECT foo FROM bar WHERE (a = 1 AND b = 1) AND c = 1/,
120 q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 AND c = 1)/,
121 q/SELECT foo FROM bar WHERE ((((a = 1))) AND (b = 1 AND c = 1))/,
127 q/SELECT foo FROM bar WHERE a = 1 OR b = 1 OR c = 1/,
128 q/SELECT foo FROM bar WHERE (a = 1 OR b = 1) OR c = 1/,
129 q/SELECT foo FROM bar WHERE a = 1 OR (b = 1 OR c = 1)/,
130 q/SELECT foo FROM bar WHERE a = 1 OR ((b = 1 OR (c = 1)))/,
136 q/SELECT foo FROM bar WHERE (a = 1) AND (b = 1 OR c = 1 OR d = 1) AND (e = 1 AND f = 1)/,
137 q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 OR c = 1 OR d = 1) AND e = 1 AND (f = 1)/,
138 q/SELECT foo FROM bar WHERE ( ((a = 1) AND ( b = 1 OR (c = 1 OR d = 1) )) AND ((e = 1)) AND f = 1) /,
144 q/SELECT foo FROM bar WHERE (a) AND (b = 2)/,
145 q/SELECT foo FROM bar WHERE (a AND b = 2)/,
146 q/SELECT foo FROM bar WHERE (a AND (b = 2))/,
147 q/SELECT foo FROM bar WHERE a AND (b = 2)/,
153 q/SELECT foo FROM bar WHERE ((NOT a) AND b = 2)/,
154 q/SELECT foo FROM bar WHERE (NOT a) AND (b = 2)/,
155 q/SELECT foo FROM bar WHERE (NOT (a)) AND b = 2/,
161 q/SELECT foo FROM bar WHERE NOT a AND (b = 2)/,
162 q/SELECT foo FROM bar WHERE (NOT a) AND (b = 2)/,
167 parenthesis_significant => 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 q/SELECT foo FROM bar WHERE (a = 1 AND b = 1) AND c = 1/,
172 q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 AND c = 1)/,
173 q/SELECT foo FROM bar WHERE ((((a = 1))) AND (b = 1 AND c = 1))/,
178 parenthesis_significant => 1,
180 q/SELECT foo FROM bar WHERE a = 1 OR b = 1 OR c = 1/,
181 q/SELECT foo FROM bar WHERE (a = 1 OR b = 1) OR c = 1/,
182 q/SELECT foo FROM bar WHERE a = 1 OR (b = 1 OR c = 1)/,
183 q/SELECT foo FROM bar WHERE a = 1 OR ((b = 1 OR (c = 1)))/,
188 parenthesis_significant => 1,
190 q/SELECT foo FROM bar WHERE (a = 1) AND (b = 1 OR c = 1 OR d = 1) AND (e = 1 AND f = 1)/,
191 q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 OR c = 1 OR d = 1) AND e = 1 AND (f = 1)/,
192 q/SELECT foo FROM bar WHERE ( ((a = 1) AND ( b = 1 OR (c = 1 OR d = 1) )) AND ((e = 1)) AND f = 1) /,
196 # WHERE condition - different
200 q/SELECT foo FROM bar WHERE a = 1/,
201 q/SELECT quux FROM bar WHERE a = 1/,
202 q/SELECT foo FROM quux WHERE a = 1/,
203 q/FOOBAR foo FROM bar WHERE a = 1/,
205 q/SELECT foo FROM bar WHERE a = 2/,
206 q/SELECT foo FROM bar WHERE a < 1/,
207 q/SELECT foo FROM bar WHERE b = 1/,
208 q/SELECT foo FROM bar WHERE (c = 1)/,
209 q/SELECT foo FROM bar WHERE (d = 1)/,
211 q/SELECT foo FROM bar WHERE a = 1 AND quux/,
212 q/SELECT foo FROM bar WHERE a = 1 GROUP BY foo/,
213 q/SELECT foo FROM bar WHERE a = 1 ORDER BY foo/,
214 q/SELECT foo FROM bar WHERE a = 1 LIMIT 1/,
215 q/SELECT foo FROM bar WHERE a = 1 OFFSET 1/,
216 q/SELECT foo FROM bar JOIN quux WHERE a = 1/,
217 q/SELECT foo FROM bar JOIN quux ON a = 1 WHERE a = 1/,
223 q/SELECT foo FROM bar WHERE a = 1 AND b = 1/,
224 q/SELECT quux FROM bar WHERE a = 1 AND b = 1/,
225 q/SELECT foo FROM quux WHERE a = 1 AND b = 1/,
226 q/FOOBAR foo FROM bar WHERE a = 1 AND b = 1/,
228 q/SELECT foo FROM bar WHERE a = 2 AND b = 1/,
229 q/SELECT foo FROM bar WHERE a = 3 AND (b = 1)/,
230 q/SELECT foo FROM bar WHERE (a = 4) AND b = 1/,
231 q/SELECT foo FROM bar WHERE (a = 5) AND (b = 1)/,
232 q/SELECT foo FROM bar WHERE ((a = 6) AND (b = 1))/,
233 q/SELECT foo FROM bar WHERE ((a = 7) AND (b = 1))/,
235 q/SELECT foo FROM bar WHERE a = 1 AND b = 2/,
236 q/SELECT foo FROM bar WHERE a = 1 AND (b = 3)/,
237 q/SELECT foo FROM bar WHERE (a = 1) AND b = 4/,
238 q/SELECT foo FROM bar WHERE (a = 1) AND (b = 5)/,
239 q/SELECT foo FROM bar WHERE ((a = 1) AND (b = 6))/,
240 q/SELECT foo FROM bar WHERE ((a = 1) AND (b = 7))/,
242 q/SELECT foo FROM bar WHERE a < 1 AND b = 1/,
243 q/SELECT foo FROM bar WHERE b = 1 AND b = 1/,
244 q/SELECT foo FROM bar WHERE (c = 1) AND b = 1/,
245 q/SELECT foo FROM bar WHERE (d = 1) AND b = 1/,
247 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 AND quux/,
248 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 GROUP BY foo/,
249 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 ORDER BY foo/,
250 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 LIMIT 1/,
251 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 OFFSET 1/,
252 q/SELECT foo FROM bar JOIN quux WHERE a = 1 AND b = 1/,
253 q/SELECT foo FROM bar JOIN quux ON a = 1 WHERE a = 1 AND b = 1/,
259 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 OR c = 1/,
260 q/SELECT foo FROM bar WHERE (a = 1 AND b = 1) OR c = 1/,
261 q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 OR c = 1)/,
267 q/SELECT foo FROM bar WHERE a = 1 OR b = 1 AND c = 1/,
268 q/SELECT foo FROM bar WHERE (a = 1 OR b = 1) AND c = 1/,
269 q/SELECT foo FROM bar WHERE a = 1 OR (b = 1 AND c = 1)/,
274 parenthesis_significant => 1,
276 q/SELECT foo FROM bar WHERE a IN (1,2,3)/,
277 q/SELECT foo FROM bar WHERE a IN (1,3,2)/,
278 q/SELECT foo FROM bar WHERE a IN ((1,2,3))/,
284 # BETWEEN with/without parenthesis around itself/RHS is a sticky business
285 # if I made a mistake here, simply rewrite the special BETWEEN handling in
289 q/SELECT foo FROM bar WHERE ( completion_date BETWEEN ? AND ? AND status = ? )/,
290 q/SELECT foo FROM bar WHERE completion_date BETWEEN (? AND ?) AND status = ?/,
291 q/SELECT foo FROM bar WHERE ( (completion_date BETWEEN (? AND ?) ) AND status = ? )/,
292 q/SELECT foo FROM bar WHERE ( (completion_date BETWEEN (? AND ? AND status = ?) ) )/,
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)',
621 'SELECT count(*) FROM foo',
622 'SELECT count(*) AS bar FROM foo',
623 'SELECT count(*) AS "bar" FROM foo',
624 'SELECT count(a) FROM foo',
625 'SELECT count(1) FROM foo',
632 'SELECT foo() bar FROM baz',
633 'SELECT foo ( )bar FROM baz',
634 'SELECT foo (())bar FROM baz',
635 'SELECT foo(( ) ) bar FROM baz',
641 'SELECT foo() FROM bar',
642 'SELECT foo FROM bar',
643 'SELECT foo FROM bar ()',
650 'SELECT * FROM foo WHERE 1 = ( a > b)',
651 'SELECT * FROM foo WHERE 1 = a > b',
652 'SELECT * FROM foo WHERE (1 = a) > b',
658 'SELECT * FROM foo WHERE bar = baz(buzz)',
659 'SELECT * FROM foo WHERE bar = (baz( buzz ))',
745 { foo => 42, bar => 1 },
746 { foo => 42, bar => 1 },
747 { foo => '42', bar => 1 },
751 # blessed object - equal
755 bless(\(local $_ = 42), 'Life::Universe::Everything'),
756 bless(\(local $_ = 42), 'Life::Universe::Everything'),
762 bless([42], 'Life::Universe::Everything'),
763 bless([42], 'Life::Universe::Everything'),
769 bless({ answer => 42 }, 'Life::Universe::Everything'),
770 bless({ answer => 42 }, 'Life::Universe::Everything'),
774 # complex data structure - equal
778 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
779 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
794 # scalarref - different
804 # arrayref - different
814 # hashref - different
835 # complex data structure - different
839 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
840 [43, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
841 [42, { foo => 'baz', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
842 [42, { bar => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
843 [42, { foo => 'bar', quuux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
844 [42, { foo => 'bar', quux => [0, 1, 2, \3, { quux => [4, 5] } ] }, 8 ],
845 [42, { foo => 'bar', quux => [1, 2, 3, { quux => [4, 5] } ] }, 8 ],
846 [42, { foo => 'bar', quux => [1, 2, \4, { quux => [4, 5] } ] }, 8 ],
847 [42, { foo => 'bar', quux => [1, 2, \3, { quuux => [4, 5] } ] }, 8 ],
848 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5, 6] } ] }, 8 ],
849 [42, { foo => 'bar', quux => [1, 2, \3, { quux => 4 } ] }, 8 ],
850 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5], quuux => 1 } ] }, 8 ],
851 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8, 9 ],
858 map { $_ * ($_ - 1) / 2 }
859 map { scalar @{$_->{statements}} }
863 map { $_ * ($_ - 1) / 2 }
864 map { scalar @{$_->{bindvals}} }
869 use_ok('SQL::Abstract::Test', import => [qw(
870 eq_sql_bind eq_sql eq_bind is_same_sql_bind
873 for my $test (@sql_tests) {
874 my $statements = $test->{statements};
875 while (@$statements) {
876 my $sql1 = shift @$statements;
877 foreach my $sql2 (@$statements) {
879 no warnings qw/once/; # perl 5.10 is dumb
880 local $SQL::Abstract::Test::parenthesis_significant = $test->{parenthesis_significant}
881 if $test->{parenthesis_significant};
882 my $equal = eq_sql($sql1, $sql2);
885 local $TODO = $test->{todo} if $test->{todo};
887 if ($test->{equal}) {
888 ok($equal, "equal SQL expressions should have been considered equal");
890 ok(!$equal, "different SQL expressions should have been considered not equal");
893 if ($equal ^ $test->{equal}) {
894 my ($ast1, $ast2) = map { SQL::Abstract::Test::parse ($_) } ($sql1, $sql2);
896 $_ = Dumper $_ for ($ast1, $ast2);
908 for my $test (@bind_tests) {
909 my $bindvals = $test->{bindvals};
911 my $bind1 = shift @$bindvals;
912 foreach my $bind2 (@$bindvals) {
913 my $equal = eq_bind($bind1, $bind2);
914 if ($test->{equal}) {
915 ok($equal, "equal bind values considered equal");
917 ok(!$equal, "different bind values considered not equal");
920 if ($equal ^ $test->{equal}) {
921 diag("bind1: " . Dumper($bind1));
922 diag("bind2: " . Dumper($bind2));
929 "SELECT * FROM foo WHERE id = ?", [42],
930 "SELECT * FROM foo WHERE (id = ?)", [42],
932 "eq_sql_bind considers equal SQL expressions and bind values equal"
937 "SELECT * FROM foo WHERE id = ?", [42],
938 "SELECT * FROM foo WHERE (id = ?)", [0],
940 "eq_sql_bind considers equal SQL expressions and different bind values different"
944 "SELECT * FROM foo WHERE id = ?", [42],
945 "SELECT * FROM bar WHERE (id = ?)", [42],
947 "eq_sql_bind considers different SQL expressions and equal bind values different"