t/05in_between.t

   1 #!/usr/bin/perl
   2
   3 use strict;
   4 use warnings;
   5 use Test::More;
   6 use Test::Exception;
   7 use SQL::Abstract::Test import => [qw(is_same_sql_bind diag_where)];
   8
   9 use SQL::Abstract;
  10
  11 my @in_between_tests = (
  12   {
  13     where => { x => { -between => [1, 2] } },
  14     stmt => 'WHERE (x BETWEEN ? AND ?)',
  15     bind => [qw/1 2/],
  16     test => '-between with two placeholders',
  17   },
  18   {
  19     where => { x => { -between => [\"1", 2] } },
  20     stmt => 'WHERE (x BETWEEN 1 AND ?)',
  21     bind => [qw/2/],
  22     test => '-between with one literal sql arg and one placeholder',
  23   },
  24   {
  25     where => { x => { -between => [1, \"2"] } },
  26     stmt => 'WHERE (x BETWEEN ? AND 2)',
  27     bind => [qw/1/],
  28     test => '-between with one placeholder and one literal sql arg',
  29   },
  30   {
  31     where => { x => { -between => [\'current_date - 1', \'current_date - 0'] } },
  32     stmt => 'WHERE (x BETWEEN current_date - 1 AND current_date - 0)',
  33     bind => [],
  34     test => '-between with two literal sql arguments',
  35   },
  36   {
  37     where => { x => { -between => [ \['current_date - ?', 1], \['current_date - ?', 0] ] } },
  38     stmt => 'WHERE (x BETWEEN current_date - ? AND current_date - ?)',
  39     bind => [1, 0],
  40     test => '-between with two literal sql arguments with bind',
  41   },
  42   {
  43     where => { x => { -between => \['? AND ?', 1, 2] } },
  44     stmt => 'WHERE (x BETWEEN ? AND ?)',
  45     bind => [1,2],
  46     test => '-between with literal sql with placeholders (\["? AND ?", scalar, scalar])',
  47   },
  48   {
  49     where => { x => { -between => \["'something' AND ?", 2] } },
  50     stmt => "WHERE (x BETWEEN 'something' AND ?)",
  51     bind => [2],
  52     test => '-between with literal sql with one literal arg and one placeholder (\["\'something\' AND ?", scalar])',
  53   },
  54   {
  55     where => { x => { -between => \["? AND 'something'", 1] } },
  56     stmt => "WHERE (x BETWEEN ? AND 'something')",
  57     bind => [1],
  58     test => '-between with literal sql with one placeholder and one literal arg (\["? AND \'something\'", scalar])',
  59   },
  60   {
  61     where => { x => { -between => \"'this' AND 'that'" } },
  62     stmt => "WHERE (x BETWEEN 'this' AND 'that')",
  63     bind => [],
  64     test => '-between with literal sql with a literal (\"\'this\' AND \'that\'")',
  65   },
  66
  67   # generate a set of invalid -between tests
  68   ( map { {
  69     where => { x => { -between => $_ } },
  70     test => 'invalid -between args',
  71     exception => qr|Operator 'BETWEEN' requires either an arrayref with two defined values or expressions, or a single literal scalarref/arrayref-ref|,
  72   } } (
  73     [ 1, 2, 3 ],
  74     [ 1, undef, 3 ],
  75     [ undef, 2, 3 ],
  76     [ 1, 2, undef ],
  77     [ 1, undef ],
  78     [ undef, 2 ],
  79     [ undef, undef ],
  80     [ 1 ],
  81     [ undef ],
  82     [],
  83     1,
  84     undef,
  85   )),
  86   {
  87     where => {
  88       start0 => { -between => [ 1, { -upper => 2 } ] },
  89       start1 => { -between => \["? AND ?", 1, 2] },
  90       start2 => { -between => \"lower(x) AND upper(y)" },
  91       start3 => { -between => [
  92         \"lower(x)",
  93         \["upper(?)", 'stuff' ],
  94       ] },
  95     },
  96     stmt => "WHERE (
  97           ( start0 BETWEEN ? AND UPPER ?          )
  98       AND ( start1 BETWEEN ? AND ?                )
  99       AND ( start2 BETWEEN lower(x) AND upper(y)  )
 100       AND ( start3 BETWEEN lower(x) AND upper(?)  )
 101     )",
 102     bind => [1, 2, 1, 2, 'stuff'],
 103     test => '-between POD test',
 104   },
 105   {
 106     args => { bindtype => 'columns' },
 107     where => {
 108       start0 => { -between => [ 1, { -upper => 2 } ] },
 109       start1 => { -between => \["? AND ?", [ start1 => 1], [start1 => 2] ] },
 110       start2 => { -between => \"lower(x) AND upper(y)" },
 111       start3 => { -between => [
 112         \"lower(x)",
 113         \["upper(?)", [ start3 => 'stuff'] ],
 114       ] },
 115     },
 116     stmt => "WHERE (
 117           ( start0 BETWEEN ? AND UPPER ?          )
 118       AND ( start1 BETWEEN ? AND ?                )
 119       AND ( start2 BETWEEN lower(x) AND upper(y)  )
 120       AND ( start3 BETWEEN lower(x) AND upper(?)  )
 121     )",
 122     bind => [
 123       [ start0 => 1 ],
 124       [ start0 => 2 ],
 125       [ start1 => 1 ],
 126       [ start1 => 2 ],
 127       [ start3 => 'stuff' ],
 128     ],
 129     test => '-between POD test',
 130   },
 131
 132   {
 133     parenthesis_significant => 1,
 134     where => { x => { -in => [ 1 .. 3] } },
 135     stmt => "WHERE ( x IN (?, ?, ?) )",
 136     bind => [ 1 .. 3],
 137     test => '-in with an array of scalars',
 138   },
 139   {
 140     parenthesis_significant => 1,
 141     where => { x => { -in => [] } },
 142     stmt => "WHERE ( 0=1 )",
 143     bind => [],
 144     test => '-in with an empty array',
 145   },
 146   {
 147     parenthesis_significant => 1,
 148     where => { x => { -in => \'( 1,2,lower(y) )' } },
 149     stmt => "WHERE ( x IN ( 1,2,lower(y) ) )",
 150     bind => [],
 151     test => '-in with a literal scalarref',
 152   },
 153   {
 154     parenthesis_significant => 1,
 155     where => { x => { -in => \['( ( ?,?,lower(y) ) )', 1, 2] } },
 156     stmt => "WHERE ( x IN ( ?,?,lower(y) ) )",  # note that outer parens are opened even though literal was requested (RIBASUSHI)
 157     bind => [1, 2],
 158     test => '-in with a literal arrayrefref',
 159   },
 160   {
 161     parenthesis_significant => 1,
 162     where => {
 163       status => { -in => \"(SELECT status_codes\nFROM states)" },
 164     },
 165     # failed to open outer parens on a multi-line query in 1.61 (semifor)
 166     stmt => " WHERE ( status IN ( SELECT status_codes FROM states )) ",
 167     bind => [],
 168     test => '-in multi-line subquery test',
 169   },
 170   {
 171     parenthesis_significant => 1,
 172     where => {
 173       customer => { -in => \[
 174         'SELECT cust_id FROM cust WHERE balance > ?',
 175         2000,
 176       ]},
 177       status => { -in => \'SELECT status_codes FROM states' },
 178     },
 179     stmt => "
 180       WHERE ((
 181             customer IN ( SELECT cust_id FROM cust WHERE balance > ? )
 182         AND status IN ( SELECT status_codes FROM states )
 183       ))
 184     ",
 185     bind => [2000],
 186     test => '-in POD test',
 187   },
 188   {
 189     where => { x => { -in => [ \['LOWER(?)', 'A' ], \'LOWER(b)', { -lower => 'c' } ] } },
 190     stmt => " WHERE ( x IN ( LOWER(?), LOWER(b), LOWER ? ) )",
 191     bind => [qw/A c/],
 192     test => '-in with an array of function array refs with args',
 193   },
 194   {
 195     exception => qr/
 196       \QSQL::Abstract before v1.75 used to generate incorrect SQL \E
 197       \Qwhen the -IN operator was given an undef-containing list: \E
 198       \Q!!!AUDIT YOUR CODE AND DATA!!! (the upcoming Data::Query-based \E
 199       \Qversion of SQL::Abstract will emit the logically correct SQL \E
 200       \Qinstead of raising this exception)\E
 201     /x,
 202     where => { x => { -in => [ 1, undef ] } },
 203     stmt => " WHERE ( x IN ( ? ) OR x IS NULL )",
 204     bind => [ 1 ],
 205     test => '-in with undef as an element',
 206   },
 207   {
 208     exception => qr/
 209       \QSQL::Abstract before v1.75 used to generate incorrect SQL \E
 210       \Qwhen the -IN operator was given an undef-containing list: \E
 211       \Q!!!AUDIT YOUR CODE AND DATA!!! (the upcoming Data::Query-based \E
 212       \Qversion of SQL::Abstract will emit the logically correct SQL \E
 213       \Qinstead of raising this exception)\E
 214     /x,
 215     where => { x => { -in => [ 1, undef, 2, 3, undef ] } },
 216     stmt => " WHERE ( x IN ( ?, ?, ? ) OR x IS NULL )",
 217     bind => [ 1, 2, 3 ],
 218     test => '-in with multiple undef elements',
 219   },
 220 );
 221
 222 for my $case (@in_between_tests) {
 223   TODO: {
 224     local $TODO = $case->{todo} if $case->{todo};
 225     local $SQL::Abstract::Test::parenthesis_significant = $case->{parenthesis_significant};
 226
 227
 228     my @w;
 229     local $SIG{__WARN__} = sub { push @w, @_ };
 230
 231     my $sql = SQL::Abstract->new ($case->{args} || {});
 232
 233     if ($case->{exception}) {
 234       throws_ok { $sql->where($case->{where}) } $case->{exception};
 235     }
 236     else {
 237       my ($stmt, @bind) = $sql->where($case->{where});
 238       is_same_sql_bind(
 239         $stmt,
 240         \@bind,
 241         $case->{stmt},
 242         $case->{bind},
 243       ) || diag_where ( $case->{where} );
 244     }
 245
 246     is (@w, 0, $case->{test} || 'No warnings within in-between tests')
 247       || diag join "\n", 'Emitted warnings:', @w;
 248   }
 249 }
 250
 251 done_testing;