Bug#48177 - SELECTs with NOT IN subqueries containing NULL

            values return too many records

WHERE clauses with "outer_value_list NOT IN subselect" were
handled incorrectly if the outer value list contained multiple 
items where at least one of these could be NULL. The first 
outer record with NULL value was handled correctly, but if a 
second record with NULL value existed, the optimizer would 
choose to reuse the result it got on the last execution of the 
subselect. This is incorrect if the outer value list has 
multiple items.
     
The fix is to make Item_in_optimizer::val_int (in 
item_cmpfunc.cc) reuse the result of the latest execution
for NULL values only if all values in the outer_value_list 
are NULL.


mysql-test/r/subselect3.result:
  Added test for BUG#48177
mysql-test/t/subselect3.test:
  Added test for BUG#48177
sql/item_cmpfunc.cc:
  Make Item_in_optimizer::val_int (in item_cmpfunc.cc) reuse the result of the latest execution for NULL values only if all values in the outer_value_list are NULL.

1 files changed, 54 insertions, 42 deletions

diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index df92c165f2d..c6b88cd8188 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -1585,61 +1585,73 @@ longlong Item_in_optimizer::val_int()
   
   if (cache->null_value)
   {
+    /*
+      We're evaluating 
+      "<outer_value_list> [NOT] IN (SELECT <inner_value_list>...)" 
+      where one or more of the outer values is NULL. 
+    */
     if (((Item_in_subselect*)args[1])->is_top_level_item())
     {
       /*
-        We're evaluating "NULL IN (SELECT ...)". The result can be NULL or
-        FALSE, and we can return one instead of another. Just return NULL.
+        We're evaluating a top level item, e.g. 
+	"<outer_value_list> IN (SELECT <inner_value_list>...)",
+	and in this case a NULL value in the outer_value_list means
+        that the result shall be NULL/FALSE (makes no difference for
+        top level items). The cached value is NULL, so just return
+        NULL.
       */
       null_value= 1;
     }
     else
     {
-      if (!((Item_in_subselect*)args[1])->is_correlated &&
-          result_for_null_param != UNKNOWN)
+      /*
+	We're evaluating an item where a NULL value in either the
+        outer or inner value list does not automatically mean that we
+        can return NULL/FALSE. An example of such a query is
+        "<outer_value_list> NOT IN (SELECT <inner_value_list>...)" 
+        The result when there is at least one NULL value is: NULL if the
+        SELECT evaluated over the non-NULL values produces at least
+        one row, FALSE otherwise
+      */
+      Item_in_subselect *item_subs=(Item_in_subselect*)args[1]; 
+      bool all_left_cols_null= true;
+      const uint ncols= cache->cols();
+
+      /*
+        Turn off the predicates that are based on column compares for
+        which the left part is currently NULL
+      */
+      for (uint i= 0; i < ncols; i++)
       {
-        /* Use cached value from previous execution */
-        null_value= result_for_null_param;
+        if (cache->element_index(i)->null_value)
+          item_subs->set_cond_guard_var(i, FALSE);
+        else 
+          all_left_cols_null= false;
       }
-      else
+
+      if (!((Item_in_subselect*)args[1])->is_correlated && 
+          all_left_cols_null && result_for_null_param != UNKNOWN)
       {
-        /*
-          We're evaluating "NULL IN (SELECT ...)". The result is:
-             FALSE if SELECT produces an empty set, or
-             NULL  otherwise.
-          We disable the predicates we've pushed down into subselect, run the
-          subselect and see if it has produced any rows.
+        /* 
+           This is a non-correlated subquery, all values in the outer
+           value list are NULL, and we have already evaluated the
+           subquery for all NULL values: Return the same result we
+           did last time without evaluating the subquery.
         */
-        Item_in_subselect *item_subs=(Item_in_subselect*)args[1]; 
-        if (cache->cols() == 1)
-        {
-          item_subs->set_cond_guard_var(0, FALSE);
-          (void) args[1]->val_bool_result();
-          result_for_null_param= null_value= !item_subs->engine->no_rows();
-          item_subs->set_cond_guard_var(0, TRUE);
-        }
-        else
-        {
-          uint i;
-          uint ncols= cache->cols();
-          /*
-            Turn off the predicates that are based on column compares for
-            which the left part is currently NULL
-          */
-          for (i= 0; i < ncols; i++)
-          {
-            if (cache->element_index(i)->null_value)
-              item_subs->set_cond_guard_var(i, FALSE);
-          }
-          
-          (void) args[1]->val_bool_result();
-          result_for_null_param= null_value= !item_subs->engine->no_rows();
-          
-          /* Turn all predicates back on */
-          for (i= 0; i < ncols; i++)
-            item_subs->set_cond_guard_var(i, TRUE);
-        }
+        null_value= result_for_null_param;
+      } 
+      else 
+      {
+        /* The subquery has to be evaluated */
+        (void) args[1]->val_bool_result();
+        null_value= !item_subs->engine->no_rows();
+        if (all_left_cols_null)
+          result_for_null_param= null_value;
       }
+
+      /* Turn all predicates back on */
+      for (uint i= 0; i < ncols; i++)
+        item_subs->set_cond_guard_var(i, TRUE);
     }
     return 0;
   }