-
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.
Chaithra Gopalareddy authoredProblem: 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