t/05in_between.t

   1 use strict;
   2 use warnings;
   3 use Test::More;
   4 use Test::Exception;
   5 use SQL::Abstract::Test import => [qw(is_same_sql_bind diag_where)];
   6
   7 use SQL::Abstract;
   8
   9 my @in_between_tests = (
  10   {
  11     where => { x => { -between => [1, 2] } },
  12     stmt => 'WHERE (x BETWEEN ? AND ?)',
  13     bind => [qw/1 2/],
  14     test => '-between with two placeholders',
  15   },
  16   {
  17     where => { x => { -between => [\"1", 2] } },
  18     stmt => 'WHERE (x BETWEEN 1 AND ?)',
  19     bind => [qw/2/],
  20     test => '-between with one literal sql arg and one placeholder',
  21   },
  22   {
  23     where => { x => { -between => [1, \"2"] } },
  24     stmt => 'WHERE (x BETWEEN ? AND 2)',
  25     bind => [qw/1/],
  26     test => '-between with one placeholder and one literal sql arg',
  27   },
  28   {
  29     where => { x => { -between => [\'current_date - 1', \'current_date - 0'] } },
  30     stmt => 'WHERE (x BETWEEN current_date - 1 AND current_date - 0)',
  31     bind => [],
  32     test => '-between with two literal sql arguments',
  33   },
  34   {
  35     where => { x => { -between => [ \['current_date - ?', 1], \['current_date - ?', 0] ] } },
  36     stmt => 'WHERE (x BETWEEN current_date - ? AND current_date - ?)',
  37     bind => [1, 0],
  38     test => '-between with two literal sql arguments with bind',
  39   },
  40   {
  41     where => { x => { -between => \['? AND ?', 1, 2] } },
  42     stmt => 'WHERE (x BETWEEN ? AND ?)',
  43     bind => [1,2],
  44     test => '-between with literal sql with placeholders (\["? AND ?", scalar, scalar])',
  45   },
  46   {
  47     where => { x => { -between => \["'something' AND ?", 2] } },
  48     stmt => "WHERE (x BETWEEN 'something' AND ?)",
  49     bind => [2],
  50     test => '-between with literal sql with one literal arg and one placeholder (\["\'something\' AND ?", scalar])',
  51   },
  52   {
  53     where => { x => { -between => \["? AND 'something'", 1] } },
  54     stmt => "WHERE (x BETWEEN ? AND 'something')",
  55     bind => [1],
  56     test => '-between with literal sql with one placeholder and one literal arg (\["? AND \'something\'", scalar])',
  57   },
  58   {
  59     where => { x => { -between => \"'this' AND 'that'" } },
  60     stmt => "WHERE (x BETWEEN 'this' AND 'that')",
  61     bind => [],
  62     test => '-between with literal sql with a literal (\"\'this\' AND \'that\'")',
  63   },
  64
  65   # generate a set of invalid -between tests
  66   ( map { {
  67     where => { x => { -between => $_ } },
  68     test => 'invalid -between args',
  69     exception => qr|Operator 'BETWEEN' requires either an arrayref with two defined values or expressions, or a single literal scalarref/arrayref-ref|,
  70   } } (
  71     [ 1, 2, 3 ],
  72     [ 1, undef, 3 ],
  73     [ undef, 2, 3 ],
  74     [ 1, 2, undef ],
  75     [ 1, undef ],
  76     [ undef, 2 ],
  77     [ undef, undef ],
  78     [ 1 ],
  79     [ undef ],
  80     [],
  81     1,
  82     undef,
  83   )),
  84   {
  85     where => {
  86       start0 => { -between => [ 1, { -upper => 2 } ] },
  87       start1 => { -between => \["? AND ?", 1, 2] },
  88       start2 => { -between => \"lower(x) AND upper(y)" },
  89       start3 => { -between => [
  90         \"lower(x)",
  91         \["upper(?)", 'stuff' ],
  92       ] },
  93     },
  94     stmt => "WHERE (
  95           ( start0 BETWEEN ? AND UPPER ?          )
  96       AND ( start1 BETWEEN ? AND ?                )
  97       AND ( start2 BETWEEN lower(x) AND upper(y)  )
  98       AND ( start3 BETWEEN lower(x) AND upper(?)  )
  99     )",
 100     bind => [1, 2, 1, 2, 'stuff'],
 101     test => '-between POD test',
 102   },
 103   {
 104     args => { bindtype => 'columns' },
 105     where => {
 106       start0 => { -between => [ 1, { -upper => 2 } ] },
 107       start1 => { -between => \["? AND ?", [ start1 => 1], [start1 => 2] ] },
 108       start2 => { -between => \"lower(x) AND upper(y)" },
 109       start3 => { -between => [
 110         \"lower(x)",
 111         \["upper(?)", [ start3 => 'stuff'] ],
 112       ] },
 113     },
 114     stmt => "WHERE (
 115           ( start0 BETWEEN ? AND UPPER ?          )
 116       AND ( start1 BETWEEN ? AND ?                )
 117       AND ( start2 BETWEEN lower(x) AND upper(y)  )
 118       AND ( start3 BETWEEN lower(x) AND upper(?)  )
 119     )",
 120     bind => [
 121       [ start0 => 1 ],
 122       [ start0 => 2 ],
 123       [ start1 => 1 ],
 124       [ start1 => 2 ],
 125       [ start3 => 'stuff' ],
 126     ],
 127     test => '-between POD test',
 128   },
 129
 130   {
 131     parenthesis_significant => 1,
 132     where => { x => { -in => [ 1 .. 3] } },
 133     stmt => "WHERE ( x IN (?, ?, ?) )",
 134     bind => [ 1 .. 3],
 135     test => '-in with an array of scalars',
 136   },
 137   {
 138     parenthesis_significant => 1,
 139     where => { x => { -in => [] } },
 140     stmt => "WHERE ( 0=1 )",
 141     bind => [],
 142     test => '-in with an empty array',
 143   },
 144   {
 145     parenthesis_significant => 1,
 146     where => { x => { -in => \'( 1,2,lower(y) )' } },
 147     stmt => "WHERE ( x IN ( 1,2,lower(y) ) )",
 148     bind => [],
 149     test => '-in with a literal scalarref',
 150   },
 151
 152   # note that outer parens are opened even though literal was requested below
 153   {
 154     parenthesis_significant => 1,
 155     where => { x => { -in => \['( ( ?,?,lower(y) ) )', 1, 2] } },
 156     stmt => "WHERE ( x IN ( ?,?,lower(y) ) )",
 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     stmt => " WHERE ( status IN ( SELECT status_codes FROM states )) ",
 166     bind => [],
 167     test => '-in multi-line subquery test',
 168   },
 169   {
 170     parenthesis_significant => 1,
 171     where => {
 172       customer => { -in => \[
 173         'SELECT cust_id FROM cust WHERE balance > ?',
 174         2000,
 175       ]},
 176       status => { -in => \'SELECT status_codes FROM states' },
 177     },
 178     stmt => "
 179       WHERE ((
 180             customer IN ( SELECT cust_id FROM cust WHERE balance > ? )
 181         AND status IN ( SELECT status_codes FROM states )
 182       ))
 183     ",
 184     bind => [2000],
 185     test => '-in POD test',
 186   },
 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;