X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=t%2F10test.t;h=aa140d496f54a747de7f3c77658103dc33389131;hb=c4d7cfcf707fc754a03934300414c16416257698;hp=885402648034915d7b0dd261f796dff07c979893;hpb=bc41b61a7748c2967e521cc03a141d99f39f0cac;p=dbsrgits%2FSQL-Abstract.git diff --git a/t/10test.t b/t/10test.t index 8854026..aa140d4 100644 --- a/t/10test.t +++ b/t/10test.t @@ -6,17 +6,9 @@ use List::Util qw(sum); use Test::More; -# equivalent to $Module::Install::AUTHOR -my $author = ( - ( not -d './inc' ) - or - ( -e ($^O eq 'VMS' ? './inc/_author' : './inc/.author') ) -); - -if (not $author and not $ENV{SQLATEST_TESTER} and not $ENV{AUTOMATED_TESTING}) { - plan skip_all => 'Skipping resource intensive self-tests, use SQLATEST_TESTER=1 to run'; -} - +use Data::Dumper; +$Data::Dumper::Terse = 1; +$Data::Dumper::Sortkeys = 1; my @sql_tests = ( # WHERE condition - equal @@ -268,6 +260,15 @@ my @sql_tests = ( }, { equal => 0, + parenthesis_significant => 1, + statements => [ + q/SELECT foo FROM bar WHERE a IN (1,2,3)/, + q/SELECT foo FROM bar WHERE a IN (1,3,2)/, + q/SELECT foo FROM bar WHERE a IN ((1,2,3))/, + ] + }, + { + equal => 0, statements => [ # BETWEEN with/without parenthesis around itself/RHS is a sticky business # if I made a mistake here, simply rewrite the special BETWEEN handling in @@ -281,6 +282,17 @@ my @sql_tests = ( ] }, + # IS NULL (special LHS-only op) + { + equal => 1, + statements => [ + q/WHERE a IS NOT NULL AND b IS NULL/, + q/WHERE (a IS NOT NULL) AND b IS NULL/, + q/WHERE a IS NOT NULL AND (b IS NULL)/, + q/WHERE (a IS NOT NULL) AND ((b IS NULL))/, + ], + }, + # JOIN condition - equal { equal => 1, @@ -533,6 +545,13 @@ my @sql_tests = ( { equal => 0, statements => [ + q/DELETE FROM cd WHERE ( cdid IN ( SELECT me.cdid FROM (SELECT * FROM cd me WHERE ( year != ? ) GROUP BY me.cdid) me WHERE ( year != ? ) ) )/, + q/DELETE FROM cd WHERE ( cdid IN ( SELECT me.cdid FROM cd me WHERE ( year != ? ) GROUP BY me.cdid ) )/, + ], + }, + { + equal => 0, + statements => [ q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE a = 1/, q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE a = 2/, q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE (a = 3)/, @@ -572,35 +591,107 @@ my @sql_tests = ( q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 10))) AS foo WHERE (a = 2)/, ] }, + + # list permutations + { + equal => 0, + statements => [ + 'SELECT a,b,c FROM foo', + 'SELECT a,c,b FROM foo', + 'SELECT b,a,c FROM foo', + 'SELECT b,c,a FROM foo', + 'SELECT c,a,b FROM foo', + 'SELECT c,b,a FROM foo', + ], + }, { - equal => 0, - statements => [ - 'SELECT a,b,c FROM foo', - 'SELECT a,c,b FROM foo', - 'SELECT b,a,c FROM foo', - 'SELECT b,c,a FROM foo', - 'SELECT c,a,b FROM foo', - 'SELECT c,b,a FROM foo', - ] + equal => 0, + statements => [ + 'SELECT * FROM foo WHERE a IN (1,2,3)', + 'SELECT * FROM foo WHERE a IN (1,3,2)', + 'SELECT * FROM foo WHERE a IN (2,1,3)', + 'SELECT * FROM foo WHERE a IN (2,3,1)', + 'SELECT * FROM foo WHERE a IN (3,1,2)', + 'SELECT * FROM foo WHERE a IN (3,2,1)', + ] }, { - equal => 0, - statements => [ - 'SELECT count(*) FROM foo', - 'SELECT count(a) FROM foo', - 'SELECT count(1) FROM foo', - ] + equal => 0, + statements => [ + 'SELECT count(*) FROM foo', + 'SELECT count(*) AS bar FROM foo', + 'SELECT count(*) AS "bar" FROM foo', + 'SELECT count(a) FROM foo', + 'SELECT count(1) FROM foo', + ] }, + # misc func { - equal => 0, - statements => [ - 'SELECT * FROM foo WHERE a IN (1,2,3)', - 'SELECT * FROM foo WHERE a IN (1,3,2)', - 'SELECT * FROM foo WHERE a IN (2,1,3)', - 'SELECT * FROM foo WHERE a IN (2,3,1)', - 'SELECT * FROM foo WHERE a IN (3,1,2)', - 'SELECT * FROM foo WHERE a IN (3,2,1)', - ] + equal => 1, + statements => [ + 'SELECT foo() bar FROM baz', + 'SELECT foo ( )bar FROM baz', + 'SELECT foo (())bar FROM baz', + 'SELECT foo(( ) ) bar FROM baz', + ] + }, + { + equal => 0, + statements => [ + 'SELECT foo() FROM bar', + 'SELECT foo FROM bar', + 'SELECT foo FROM bar ()', + ] + }, + # single ? of unknown funcs can unroll + # (think ...LIKE ?...) + { + equal => 1, + statements => [ + 'SELECT foo FROM bar WHERE bar > foo ?', + 'SELECT foo FROM bar WHERE bar > foo (?)', + 'SELECT foo FROM bar WHERE bar > foo( ? )', + ] + }, + { + equal => 1, + statements => [ + 'SELECT foo FROM bar WHERE bar > (foo ?)', + 'SELECT foo FROM bar WHERE bar > (foo( ? ))', + 'SELECT foo FROM bar WHERE bar > (( foo (?) ))', + ] + }, + { + equal => 1, + statements => [ + 'SELECT foo FROM bar WHERE bar foo ?', + 'SELECT foo FROM bar WHERE bar foo (?)', + 'SELECT foo FROM bar WHERE bar foo( (?))', + ] + }, + # not so about multival + { + equal => 0, + statements => [ + 'SELECT foo FROM bar WHERE foo IN (?, ?)', + 'SELECT foo FROM bar WHERE foo IN ?, ?', + ] + }, + # math + { + equal => 0, + statements => [ + 'SELECT * FROM foo WHERE 1 = ( a > b)', + 'SELECT * FROM foo WHERE 1 = a > b', + 'SELECT * FROM foo WHERE (1 = a) > b', + ] + }, + { + equal => 1, + statements => [ + 'SELECT * FROM foo WHERE bar = baz(buzz)', + 'SELECT * FROM foo WHERE bar = (baz( buzz ))', + ] }, ); @@ -834,8 +925,14 @@ for my $test (@sql_tests) { } if ($equal ^ $test->{equal}) { - diag("sql1: $sql1"); - diag("sql2: $sql2"); + my ($ast1, $ast2) = map { SQL::Abstract::Test::parse ($_) } ($sql1, $sql2); + $_ = Dumper $_ for ($ast1, $ast2); + + diag "sql1: $sql1"; + diag "sql2: $sql2"; + note $SQL::Abstract::Test::sql_differ; + note "ast1: $ast1"; + note "ast2: $ast2"; } } }