• Igor Babaev's avatar
    MDEV-21184 Assertion `used_tables_cache == 0' failed in Item_func::fix_fields · 1c97cd33
    Igor Babaev authored
               with condition_pushdown_from_having
    
    This bug could manifest itself for queries with GROUP BY and HAVING clauses
    when the HAVING clause was a conjunctive condition that depended
    exclusively on grouping fields and at least one conjunct contained an
    equality of the form fld=sq where fld is a grouping field and sq is a
    constant subquery.
    In this case the optimizer tries to perform a pushdown of the HAVING
    condition into WHERE. To construct the pushable condition the optimizer
    first transforms all multiple equalities in HAVING into simple equalities.
    This has to be done for a proper processing of the pushed conditions
    in WHERE. The multiple equalities at all AND/OR levels must be converted
    to simple equalities because any multiple equality may refer to a multiple
    equality at the upper level.
    Before this patch the conversion was performed like this:
      multiple_equality(x,f1,...,fn) => x=f1 and ... and x=fn.
    When an equality item for x=fi was constructed both the items for x and fi
    were cloned. If x happened to be a constant subquery that could not be
    cloned the conversion failed. If the conversions of multiple equalities
    previously performed had succeeded then the whole condition became in an
    inconsistent state that could cause different failures.
    The solution provided by the patch is:
    1. to use a different conversion rule if x is a constant
      multiple_equality(x,f1,...,fn) => f1=x and f2=f1 and ... and fn=f1
    2. not to clone x if it's a constant.
    Such conversions cannot fail and besides the result of the conversion
    preserves the equivalence of f1,...,fn that can be used for other
    optimizations.
    This patch also made sure that expensive predicates are not pushed from
    HAVING to WHERE.
    1c97cd33
derived_cond_pushdown.result 513 KB