Handle { foo => { -in => [undef, …] } }

It should expand to 'foo IN (…) OR foo IS NULL', and 'foo NOT IN (…)
AND foo IS NOT NULL' for the -not_in case.

diff --git a/lib/SQL/Abstract/Converter.pm b/lib/SQL/Abstract/Converter.pm
index ec12707..2d79ec9 100644 (file)
--- a/lib/SQL/Abstract/Converter.pm
+++ b/lib/SQL/Abstract/Converter.pm
@@ -506,6 +506,15 @@ sub _where_hashpair_to_dq {
           $op, $self->_ident_to_dq($k), $self->_literal_to_dq($$rhs)
         );
       }
+      if (grep !defined, @$rhs) {
+        my ($inop, $logic, $nullop) = $op =~ /^NOT/
+          ? (-not_in => AND => { '!=' => undef })
+          : (-in => OR => undef);
+        return $self->_expr_to_dq_ARRAYREF([
+            { $k => { $inop => [grep defined, @$rhs] } },
+            { $k => $nullop },
+          ], $logic);
+      }
       return $self->_literal_to_dq(
         $op =~ /^NOT/ ? $self->{sqltrue} : $self->{sqlfalse}
       ) unless @$rhs;

diff --git a/t/01generate.t b/t/01generate.t
index 91e51bb..c256b75 100644 (file)
--- a/t/01generate.t
+++ b/t/01generate.t
@@ -552,6 +552,13 @@ my @tests = (
               stmt_q => 'SELECT * FROM `test` WHERE ( 0=1 AND 1=1 AND `c` IN ( ? ))',
               bind => [ 42 ],
       },
+      {
+              func => 'select',
+              args => ['test', '*', { a => { -in => [42, undef] }, b => { -not_in => [42, undef] } } ],
+              stmt => 'SELECT * FROM test WHERE ( ( a IN ( ? ) OR a IS NULL ) AND b NOT IN ( ? ) AND b IS NOT NULL )',
+              stmt_q => 'SELECT * FROM `test` WHERE ( ( `a` IN ( ? ) OR `a` IS NULL ) AND `b` NOT IN ( ? ) AND `b` IS NOT NULL )',
+              bind => [ 42, 42 ],
+      },
 );
 
 for my $t (@tests) {