• Igor Babaev's avatar
    Fixed bug mdev-3913. · c9b63e6a
    Igor Babaev authored
    The wrong result set returned by the left join query  from
    the bug test case happened due to several inconsistencies 
    and bugs of the legacy mysql code.
    
    The bug test case uses an execution plan that employs a scan
    of a materialized IN subquery from the WHERE condition.
    When materializing such an IN- subquery the optimizer injects
    additional equalities  into the WHERE clause. These equalities
    express the constraints imposed by the subquery predicate.
    The injected equality of the query in the  test case happens
    to belong to the same equality class, and a new equality 
    imposing a condition on the rows of the materialized subquery
    is inferred from this class. Simultaneously the multiple
    equality is added to the ON expression of the LEFT JOIN
    used in the main query.
      
    The inferred equality of the form f1=f2 is taken into account
    when optimizing the scan of  the rows the temporary table 
    that is the result of the subquery materialization: only the 
    values of the field f1 are read from the table into the record 
    buffer. Meanwhile the inferred equality is removed from the
    WHERE conditions altogether as a constraint on the fields
    of the temporary table that has been used when filling this table. 
    This equality is supposed to be removed from the ON expression
    when the multiple equalities of the ON expression are converted
    into an optimal set of equality predicates. It supposed to be
    removed from the ON expression as an equality inferred from only
    equalities of the WHERE condition. Yet, it did not happened
    due to the following bug in the code.
    
    Erroneously the code tried to build multiple equality for ON
    expression twice: the first time, when it called optimize_cond()
    for the WHERE condition, the second time, when it called
    this function for the HAVING condition. When executing
    optimize_con() for the WHERE condition  a reference
    to the multiple equality of the WHERE condition is set
    in the multiple equality of the  ON expression. This reference
    would allow later to convert multiple equalities of the
    ON expression into equality predicates. However the 
    the second call of build_equal_items() for the ON expression
    that happened when optimize_cond() was called for the
    HAVING condition reset this reference to NULL.
    
    This bug fix blocks calling build_equal_items() for ON
    expressions for the second time. In general, it will be
    beneficial for many queries as it removes from ON 
    expressions any equalities that are to be checked for the
    WHERE condition.
    The patch also fixes two bugs in the list manipulation
    operations and a bug in the function  
    substitute_for_best_equal_field() that resulted
    in passing wrong reference to the multiple equalities
    of where conditions when processing multiple
    equalities  of ON expressions.
    
    The code of substitute_for_best_equal_field() and
    the code the helper function eliminate_item_equal()
    were also streamlined and cleaned up.
    Now the conversion of the multiple equalities into
    an optimal set of equality predicates first produces
    the sequence of the all equalities processing multiple
    equalities one by one, and, only after this, it inserts
    the equalities at the beginning of the other conditions.
    
    The multiple changes in the output of EXPLAIN
    EXTENDED are mainly the result of this streamlining,
    but in some cases is the result of the removal of
    unneeded equalities from ON expressions. In
    some test cases this removal were reflected in the
    output of EXPLAIN resulted in disappearance of 
    “Using where” in some rows of the execution plans.
    c9b63e6a
join_nested.result 64.3 KB