],
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,
},
{
+ parenthesis_significant => 1,
where => { x => { -in => [ \['LOWER(?)', 'A' ], \'LOWER(b)', { -lower => 'c' } ] } },
stmt => " WHERE ( x IN ( LOWER(?), LOWER(b), LOWER ? ) )",
bind => [qw/A c/],
\Qversion of SQL::Abstract will emit the logically correct SQL \E
\Qinstead of raising this exception)\E
/x,
+ parenthesis_significant => 1,
where => { x => { -in => [ 1, undef ] } },
stmt => " WHERE ( x IN ( ? ) OR x IS NULL )",
bind => [ 1 ],
\Qversion of SQL::Abstract will emit the logically correct SQL \E
\Qinstead of raising this exception)\E
/x,
+ parenthesis_significant => 1,
where => { x => { -in => [ 1, undef, 2, 3, undef ] } },
stmt => " WHERE ( x IN ( ?, ?, ? ) OR x IS NULL )",
bind => [ 1, 2, 3 ],
test => '-in with multiple undef elements',
},
+ {
+ parenthesis_significant => 1,
+ 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,
+ parenthesis_significant => 1,
+ 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',
+ },
);
for my $case (@in_between_tests) {