X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=t%2F05in_between.t;h=14c8f4e9fac53ab38f6c731380d3d70ca25a1721;hb=ca4f826a37ccb5194b0b5b9b4190b4007d647d9c;hp=1cb6b43a2370faa4d20b6ace58865a9a346cffc1;hpb=46dc2f3e47d514cd376003cea2df63222c492b0b;p=dbsrgits%2FSQL-Abstract.git diff --git a/t/05in_between.t b/t/05in_between.t index 1cb6b43..14c8f4e 100644 --- a/t/05in_between.t +++ b/t/05in_between.t @@ -1,8 +1,9 @@ use strict; use warnings; use Test::More; +use Test::Warn; use Test::Exception; -use SQL::Abstract::Test import => [qw(is_same_sql_bind diag_where)]; +use SQL::Abstract::Test import => [qw(is_same_sql_bind diag_where dumper)]; use SQL::Abstract; @@ -66,7 +67,7 @@ my @in_between_tests = ( ( map { { where => { x => { -between => $_ } }, test => 'invalid -between args', - exception => qr|Operator 'BETWEEN' requires either an arrayref with two defined values or expressions, or a single literal scalarref/arrayref-ref|, + throws => qr|Operator 'BETWEEN' requires either an arrayref with two defined values or expressions, or a single literal scalarref/arrayref-ref|, } } ( [ 1, 2, 3 ], [ 1, undef, 3 ], @@ -126,48 +127,65 @@ my @in_between_tests = ( ], test => '-between POD test', }, + { + where => { 'test1.a' => { 'In', ['boom', 'bang'] } }, + stmt => ' WHERE ( test1.a IN ( ?, ? ) )', + bind => ['boom', 'bang'], + test => 'In (no dash, initial cap) with qualified column', + }, + { + where => { a => { 'between', ['boom', 'bang'] } }, + stmt => ' WHERE ( a BETWEEN ? AND ? )', + bind => ['boom', 'bang'], + test => 'between (no dash) with two placeholders', + }, { - parenthesis_significant => 1, where => { x => { -in => [ 1 .. 3] } }, - stmt => "WHERE ( x IN (?, ?, ?) )", - bind => [ 1 .. 3], + stmt => "WHERE x IN (?, ?, ?)", + bind => [ 1 .. 3 ], test => '-in with an array of scalars', }, { - parenthesis_significant => 1, where => { x => { -in => [] } }, - stmt => "WHERE ( 0=1 )", + stmt => "WHERE 0=1", bind => [], test => '-in with an empty array', }, { - parenthesis_significant => 1, where => { x => { -in => \'( 1,2,lower(y) )' } }, - stmt => "WHERE ( x IN ( 1,2,lower(y) ) )", + stmt => "WHERE x IN ( 1,2,lower(y) )", bind => [], test => '-in with a literal scalarref', }, # note that outer parens are opened even though literal was requested below { - parenthesis_significant => 1, where => { x => { -in => \['( ( ?,?,lower(y) ) )', 1, 2] } }, - stmt => "WHERE ( x IN ( ?,?,lower(y) ) )", + stmt => "WHERE x IN ( ?,?,lower(y) )", bind => [1, 2], test => '-in with a literal arrayrefref', }, { - parenthesis_significant => 1, where => { status => { -in => \"(SELECT status_codes\nFROM states)" }, }, - stmt => " WHERE ( status IN ( SELECT status_codes FROM states )) ", + stmt => " WHERE status IN ( SELECT status_codes FROM states )", bind => [], test => '-in multi-line subquery test', }, + + # check that the outer paren opener is not too agressive + # note: this syntax *is not legal* on SQLite (maybe others) + # see end of https://rt.cpan.org/Ticket/Display.html?id=99503 + { + where => { foo => { -in => \ '(SELECT 1) UNION (SELECT 2)' } }, + stmt => 'WHERE foo IN ( (SELECT 1) UNION (SELECT 2) )', + bind => [], + test => '-in paren-opening works on balanced pairs only', + }, + { - parenthesis_significant => 1, where => { customer => { -in => \[ 'SELECT cust_id FROM cust WHERE balance > ?', @@ -176,10 +194,9 @@ my @in_between_tests = ( status => { -in => \'SELECT status_codes FROM states' }, }, stmt => " - WHERE (( + WHERE customer IN ( SELECT cust_id FROM cust WHERE balance > ? ) AND status IN ( SELECT status_codes FROM states ) - )) ", bind => [2000], test => '-in POD test', @@ -192,7 +209,7 @@ my @in_between_tests = ( test => '-in with an array of function array refs with args', }, { - exception => qr/ + throws => qr/ \QSQL::Abstract before v1.75 used to generate incorrect SQL \E \Qwhen the -IN operator was given an undef-containing list: \E \Q!!!AUDIT YOUR CODE AND DATA!!! (the upcoming Data::Query-based \E @@ -205,7 +222,7 @@ my @in_between_tests = ( test => '-in with undef as an element', }, { - exception => qr/ + throws => qr/ \QSQL::Abstract before v1.75 used to generate incorrect SQL \E \Qwhen the -IN operator was given an undef-containing list: \E \Q!!!AUDIT YOUR CODE AND DATA!!! (the upcoming Data::Query-based \E @@ -217,34 +234,89 @@ my @in_between_tests = ( bind => [ 1, 2, 3 ], test => '-in with multiple undef elements', }, + { + where => { a => { -in => 42 }, b => { -not_in => 42 } }, + stmt => ' WHERE a IN ( ? ) AND b NOT IN ( ? )', + bind => [ 42, 42 ], + test => '-in, -not_in with scalar', + }, + { + where => { a => { -in => [] }, b => { -not_in => [] } }, + stmt => ' WHERE ( 0=1 AND 1=1 )', + bind => [], + test => '-in, -not_in with empty arrays', + }, + { + throws => qr/ + \QSQL::Abstract before v1.75 used to generate incorrect SQL \E + \Qwhen the -IN operator was given an undef-containing list: \E + \Q!!!AUDIT YOUR CODE AND DATA!!! (the upcoming Data::Query-based \E + \Qversion of SQL::Abstract will emit the logically correct SQL \E + \Qinstead of raising this exception)\E + /x, + where => { a => { -in => [42, undef] }, b => { -not_in => [42, undef] } }, + stmt => ' WHERE ( ( a IN ( ? ) OR a IS NULL ) AND b NOT IN ( ? ) AND b IS NOT NULL )', + bind => [ 42, 42 ], + test => '-in, -not_in with undef among elements', + }, + { + throws => qr/ + \QSQL::Abstract before v1.75 used to generate incorrect SQL \E + \Qwhen the -IN operator was given an undef-containing list: \E + \Q!!!AUDIT YOUR CODE AND DATA!!! (the upcoming Data::Query-based \E + \Qversion of SQL::Abstract will emit the logically correct SQL \E + \Qinstead of raising this exception)\E + /x, + where => { a => { -in => [undef] }, b => { -not_in => [undef] } }, + stmt => ' WHERE ( a IS NULL AND b IS NOT NULL )', + bind => [], + test => '-in, -not_in with just undef element', + }, + { + where => { a => { -in => undef } }, + throws => qr/Argument passed to the 'IN' operator can not be undefined/, + test => '-in with undef argument', + }, + + { + where => { -in => [42] }, + throws => qr/Illegal use of top-level '-in'/, + test => 'Top level -in', + }, + { + where => { -between => [42, 69] }, + throws => qr/Illegal use of top-level '-between'/, + test => 'Top level -between', + }, ); for my $case (@in_between_tests) { TODO: { local $TODO = $case->{todo} if $case->{todo}; local $SQL::Abstract::Test::parenthesis_significant = $case->{parenthesis_significant}; + my $label = $case->{test} || 'in-between test'; + my $sql = SQL::Abstract->new($case->{args} || {}); - my @w; - local $SIG{__WARN__} = sub { push @w, @_ }; - - my $sql = SQL::Abstract->new ($case->{args} || {}); - - if ($case->{exception}) { - throws_ok { $sql->where($case->{where}) } $case->{exception}; + if (my $e = $case->{throws}) { + my $stmt; + throws_ok { ($stmt) = $sql->where($case->{where}) } $e, "$label throws correctly" + or diag dumper ({ where => $case->{where}, result => $stmt }); } else { - my ($stmt, @bind) = $sql->where($case->{where}); + my ($stmt, @bind); + warnings_are { + ($stmt, @bind) = $sql->where($case->{where}); + } [], "$label gives no warnings"; + is_same_sql_bind( $stmt, \@bind, $case->{stmt}, $case->{bind}, + "$label generates correct SQL and bind", ) || diag_where ( $case->{where} ); } - - is (@w, 0, $case->{test} || 'No warnings within in-between tests') - || diag join "\n", 'Emitted warnings:', @w; } }