bind => [],
test => '-between with literal sql with a literal (\"\'this\' AND \'that\'")',
},
-
+ {
+ where => {
+ start0 => { -between => [ 1, { -upper => 2 } ] },
+ start1 => { -between => \["? AND ?", 1, 2] },
+ start2 => { -between => \"lower(x) AND upper(y)" },
+ start3 => { -between => [
+ \"lower(x)",
+ \["upper(?)", 'stuff' ],
+ ] },
+ },
+ stmt => "WHERE (
+ ( start0 BETWEEN ? AND upper ? )
+ AND ( start1 BETWEEN ? AND ? )
+ AND ( start2 BETWEEN lower(x) AND upper(y) )
+ AND ( start3 BETWEEN lower(x) AND upper(?) )
+ )",
+ bind => [1, 2, 1, 2, 'stuff'],
+ test => '-between POD test',
+ },
{
parenthesis_significant => 1,
},
{
parenthesis_significant => 1,
+ where => { x => { -in => [] } },
+ 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',
},
{
parenthesis_significant => 1,
where => { x => { -in => \['( ( ?,?,lower(y) ) )', 1, 2] } },
- stmt => "WHERE ( x IN (?, ?, lower(y) ) )",
+ stmt => "WHERE ( x IN ( ?,?,lower(y) ) )", # note that outer parens are opened even though literal was requested (RIBASUSHI)
bind => [1, 2],
test => '-in with a literal arrayrefref',
},
+ {
+ parenthesis_significant => 1,
+ where => {
+ status => { -in => \"(SELECT status_codes\nFROM states)" },
+ },
+ # failed to open outer parens on a multi-line query in 1.61 (semifor)
+ stmt => " WHERE ( status IN ( SELECT status_codes FROM states )) ",
+ bind => [],
+ test => '-in multi-line subquery test',
+ },
+ {
+ parenthesis_significant => 1,
+ where => {
+ customer => { -in => \[
+ 'SELECT cust_id FROM cust WHERE balance > ?',
+ 2000,
+ ]},
+ status => { -in => \'SELECT status_codes FROM states' },
+ },
+ stmt => "
+ WHERE ((
+ customer IN ( SELECT cust_id FROM cust WHERE balance > ? )
+ AND status IN ( SELECT status_codes FROM states )
+ ))
+ ",
+ bind => [2000],
+ test => '-in POD test',
+ },
+ {
+ where => { x => { -in => [ \['LOWER(?)', 'A' ], \'LOWER(b)', { -lower => 'c' } ] } },
+ stmt => " WHERE ( x IN ( LOWER(?), LOWER(b), LOWER ? ) )",
+ bind => [qw/A c/],
+ test => '-in with an array of function array refs with args',
+ },
);
plan tests => @in_between_tests*4;