subselect.result, subselect.test:

  Added test cases for bug #7351.
item_cmpfunc.cc:
  Fixed bug #7351: incorrect result for a query with a
  subquery returning empty set.
  If in the predicate v IN (SELECT a FROM t WHERE cond)
  v is null, then the result of the predicate is either
  INKNOWN or FALSE. It is FALSE if the subquery returns
  an empty set.
item_subselect.cc:
  Fixed bug #7351: incorrect result for a query with a
  subquery returning empty set.
  The problem was due to not a quite legal transformation
  for 'IN' subqueries. A subquery containing a predicate
  of the form
  v IN (SELECT a FROM t WHERE cond)
  was transformed into
  EXISTS(SELECT a FROM t WHERE cond AND (a=v OR a IS NULL)).
  Yet, this transformation is valid only if v is not null.
  If v is null, then, in the case when
  (SELECT a FROM t WHERE cond) returns an empty set the value
  of the predicate is FALSE, otherwise the result of the
  predicate is INKNOWN.
  The fix resolves this problem by changing the result
  of the transformation to
  EXISTS(SELECT a FROM t WHERE cond AND (v IS NULL OR (a=v OR a IS NULL)))
  in the case when v is nullable.
  The new transformation prevents applying the lookup
  optimization for IN subqueries. To make it still
  applicable we have to introduce guarded access methods.

1 files changed, 3 insertions, 2 deletions

diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index a135f08ae45..3d79c16b5d0 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -636,12 +636,13 @@ longlong Item_in_optimizer::val_int()
 {
   DBUG_ASSERT(fixed == 1);
   cache->store(args[0]);
+  longlong tmp= args[1]->val_int_result();
   if (cache->null_value)
   {
-    null_value= 1;
+    if (tmp)
+      null_value= 1;
     return 0;
   }
-  longlong tmp= args[1]->val_int_result();
   null_value= args[1]->null_value;
   return tmp;
 }