• Gleb Shchepa's avatar
    Bug #16311231: MISSING DATA ON SUBQUERY WITH WHERE + XOR · f8cd565d
    Gleb Shchepa authored
    IN IN-CLAUSE USING MYISAM OR MEMORY ENGINE
    
    Backport from 5.6. Original message:
    
    The coincidences caused a data loss:
    * The query has IN subqueries nested twice,
    * the WHERE clause of the inner subquery refers to the
      outer field, and the whole WHERE clause returns FALSE,
    * the inner subquery has a LEFT JOIN that joins a single
      row with a row of NULLs; one of that NULL columns
      represents the select list of the subquery.
    
    Normally, that inner subquery should return empty record set.
    However, in our case:
    * the Item_is_not_null_test item goes constant, since
      its underlying field is NULL (because of LEFT JOIN ... ON 
      FALSE of const table row with a row of nulls);
    * we evaluate Item_is_not_null_test::val_int() as a part
      of fake HAVING expression of the transformed subquery;
    * as far as the underlying field is NULL, we optimize
      out the whole fake HAVING expression as FALSE as well
      as a whole subquery with a zero result:
      Impossible HAVING noticed after reading const tables";
    * thus, the optimizer ignores the presence of the WHERE
      clause (the WHERE expression is FALSE in our case, so
      the subquery should return empty set);
    * however, during the evaluation of the 
      Item_is_not_null_test::val_int() in the optimizer,
      it marked its "owner" with the "was_null" flag -- that
      forced the subquery to return UNKNOWN instead of empty
      set.
    That caused a wrong result.
    
    
    The problem is a regression of the small cleanup in
    the fix for the bug11827369 (the Item_is_not_null_test part)
    that conflicts with optimizations in the fix for the bug11752543.
    Before that regression the Item_is_not_null_test items
    never were constants.
    
    The fix is the rollback of Item_is_not_null_test parts
    of the bug11827369 fix.
    f8cd565d
item_cmpfunc.cc 144 KB