Skip to content
  • Chaithra Gopalareddy's avatar
    c7115fd9
    Bug#18014565: WRONG RESULT COMPUTATION USING ALL() AND GROUP BY · c7115fd9
    Chaithra Gopalareddy authored
    Problem:
    Aggregation does not happen correctly because substitution for
    subquery is created with wrong item.
    
    Analysis:
    Currently while creating a substitution for ALL/ANY subqueries,
    optimizer uses "real_item" instead of the ref_item.
    If this happens to be a reference to an aggregate function
    then it would be creating the substitution with SUM_FUNC_ITEM
    rather than the REF_ITEM.
    
    If an aggregate function is present in having clause, optimizer
    calls split_sum_func2 to add the aggregate functions
    to the list of items in the select list.
    
    Ex: In the following query
    select f1, sum(f2) as sum from t1 group by f1 having sum >
                                                  all (select 1);
    
    "sum" in having clause is a reference to sum(f2). So while
    creating the substitution we create using the real_item of
    "sum" which is sum(f2). In split_sum_func2 we add this
    "sum(f2)" to the item list. As a result the item list now
    becomes "sum(f2), f1, sum(f2)". This results in creation
    of three fields in tmp_table. But both the sum(f2)'s would
    be pointing to the same result_field. So, while aggregating
    same result is added twice.
    Before the fix for Bug#16095534, the type would remain
    REF_ITEM and thereby optimizer would not be adding "sum"
    to the select item list.
    
    Solution:
    The problem addressed in Bug#16095534 exists only for Item_ref
    objects created while resolving not for the ref objects created
    during parsing. So use real_item only for such items.
    c7115fd9
    Bug#18014565: WRONG RESULT COMPUTATION USING ALL() AND GROUP BY
    Chaithra Gopalareddy authored
    Problem:
    Aggregation does not happen correctly because substitution for
    subquery is created with wrong item.
    
    Analysis:
    Currently while creating a substitution for ALL/ANY subqueries,
    optimizer uses "real_item" instead of the ref_item.
    If this happens to be a reference to an aggregate function
    then it would be creating the substitution with SUM_FUNC_ITEM
    rather than the REF_ITEM.
    
    If an aggregate function is present in having clause, optimizer
    calls split_sum_func2 to add the aggregate functions
    to the list of items in the select list.
    
    Ex: In the following query
    select f1, sum(f2) as sum from t1 group by f1 having sum >
                                                  all (select 1);
    
    "sum" in having clause is a reference to sum(f2). So while
    creating the substitution we create using the real_item of
    "sum" which is sum(f2). In split_sum_func2 we add this
    "sum(f2)" to the item list. As a result the item list now
    becomes "sum(f2), f1, sum(f2)". This results in creation
    of three fields in tmp_table. But both the sum(f2)'s would
    be pointing to the same result_field. So, while aggregating
    same result is added twice.
    Before the fix for Bug#16095534, the type would remain
    REF_ITEM and thereby optimizer would not be adding "sum"
    to the select item list.
    
    Solution:
    The problem addressed in Bug#16095534 exists only for Item_ref
    objects created while resolving not for the ref objects created
    during parsing. So use real_item only for such items.
Loading