• Gleb Shchepa's avatar
    Bug #44139: Table scan when NULL appears in IN clause · 2b78dbff
    Gleb Shchepa authored
    SELECT ... WHERE ... IN (NULL, ...) does full table scan,
    even if the same query without the NULL uses efficient range scan.
    
    The bugfix for the bug 18360 introduced an optimization:
    if
      1) all right-hand arguments of the IN function are constants
      2) result types of all right argument items are compatible
         enough to use the same single comparison function to
         compare all of them to the left argument,
    
    then
    
      we can convert the right-hand list of constant items to an array
      of equally-typed constant values for the further
      QUICK index access etc. (see Item_func_in::fix_length_and_dec()).
    
    The Item_null constant item objects have STRING_RESULT
    result types, so, as far as Item_func_in::fix_length_and_dec()
    is aware of NULLs in the right list, this improvement efficiently
    optimizes IN function calls with a mixed right list of NULLs and
    string constants. However, the optimization doesn't affect mixed
    lists of NULLs and integers, floats etc., because there is no
    unique common comparator.
    
    
    New optimization has been added to ignore the result type
    of NULL constants in the static analysis of mixed right-hand lists.
    This is safe, because at the execution phase we care about
    presence of NULLs anyway.
    
    1. The collect_cmp_types() function has been modified to optionally
       ignore NULL constants in the item list.
    2. NULL-skipping code of the Item_func_in::fix_length_and_dec()
       function has been modified to work not only with in_string
       vectors but with in_vectors of other types.
    2b78dbff
func_in.test 16 KB