X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=t%2F05in_between.t;h=12a5658e5f034c0eb000cd5a73e9edaefa22ad0e;hb=10e6c94609fc94467ef8f281ffe192d8038ffc9e;hp=bf323752f730167dd4918dd42da912279473798f;hpb=b9a4fdaeeb2cc0b566968a740fd9063a095ade07;p=dbsrgits%2FSQL-Abstract.git

diff --git a/t/05in_between.t b/t/05in_between.t
index bf32375..12a5658 100644
--- a/t/05in_between.t
+++ b/t/05in_between.t
@@ -66,7 +66,7 @@ my @in_between_tests = (
   },
   {
     where => {
-      start0 => { -between => [ 1, 2 ] },
+      start0 => { -between => [ 1, { -upper => 2 } ] },
       start1 => { -between => \["? AND ?", 1, 2] },
       start2 => { -between => \"lower(x) AND upper(y)" },
       start3 => { -between => [
@@ -75,7 +75,7 @@ my @in_between_tests = (
       ] },
     },
     stmt => "WHERE (
-          ( start0 BETWEEN ? AND ?                )
+          ( start0 BETWEEN ? AND UPPER ?          )
       AND ( start1 BETWEEN ? AND ?                )
       AND ( start2 BETWEEN lower(x) AND upper(y)  )
       AND ( start3 BETWEEN lower(x) AND upper(?)  )
@@ -83,6 +83,32 @@ my @in_between_tests = (
     bind => [1, 2, 1, 2, 'stuff'],
     test => '-between POD test',
   },
+  {
+    args => { bindtype => 'columns' },
+    where => {
+      start0 => { -between => [ 1, { -upper => 2 } ] },
+      start1 => { -between => \["? AND ?", [ start1 => 1], [start1 => 2] ] },
+      start2 => { -between => \"lower(x) AND upper(y)" },
+      start3 => { -between => [
+        \"lower(x)",
+        \["upper(?)", [ start3 => '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 => [
+      [ start0 => 1 ],
+      [ start0 => 2 ],
+      [ start1 => 1 ],
+      [ start1 => 2 ],
+      [ start3 => 'stuff' ],
+    ],
+    test => '-between POD test',
+  },
 
   {
     parenthesis_significant => 1,
@@ -115,6 +141,16 @@ my @in_between_tests = (
   {
     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,
@@ -130,10 +166,26 @@ my @in_between_tests = (
     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',
+  },
+  {
+    where => { x => { -in => [ 1, undef ] } },
+    stmt => " WHERE ( x IN ( ?, NULL ) )",
+    bind => [ 1 ],
+    test => '-in with undef as an element', 
+  },
+  {
+    where => { x => { -in => [ 1, undef, 2, 3, undef ] } },
+    stmt => " WHERE ( x IN ( ?, NULL, ?, ?, NULL ) )",
+    bind => [ 1, 2, 3 ],
+    test => '-in with undef as an element',
+  },
 );
 
-plan tests => @in_between_tests*4;
-
 for my $case (@in_between_tests) {
   TODO: {
     local $TODO = $case->{todo} if $case->{todo};
@@ -161,3 +213,5 @@ for my $case (@in_between_tests) {
     }, "$case->{test} doesn't die");
   }
 }
+
+done_testing;