[MDEV-14520] Custom aggregate functions work incorrectly with WITH ROLLUP clause Created: 2017-11-28  Updated: 2018-05-19  Resolved: 2018-05-19

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Procedure, Stored routines
Affects Version/s: 10.3
Fix Version/s: 10.3.7

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Varun Gupta (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-7773 Aggregate stored functions Closed
Sprint: 10.3.6-1

 Description   

Note: The agg_sum function definition is taken as is from main.aggregate_functions test.

--delimiter |
 
create aggregate function agg_sum(x INT) returns INT
 
begin
 
declare z int default 0;
 
declare continue handler for not found return z;
 
loop
 
fetch group next row;
 
set z= z+x;
 
end loop;
 
end|
 
--delimiter ;
 
 
 
create table t1 (i int);
 
insert into t1 values (1),(2),(2),(3);
 
select i, agg_sum(i) from t1 group by i with rollup;
 
--echo #
 
--echo # Compare with
 
select i, sum(i) from t1 group by i with rollup;
 
 
 
# Cleanup
 
drop function agg_sum;
 
drop table t1;

Actual result with agg_sum

 
select i, agg_sum(i) from t1 group by i with rollup;
 
i	agg_sum(i)
 
1	1
 
2	8
 
3	6
 
NULL	6

Expected result with agg_sum, actual result with sum

 
select i, sum(i) from t1 group by i with rollup;
 
i	sum(i)
 
1	1
 
2	4
 
3	3
 
NULL	8



 Comments   
Comment by Varun Gupta (Inactive) [ 2017-11-30 ]

Currently the main reason for the incorrect results in case of use of rollup in custom aggregate functions that in the function JOIN::rollup_make_fields, it says that create copies of item_sum items for each sum level, currently copy_or_same() function is missing from custom aggregate functions. This function would basically create a copy of custom aggregate functions Item_sum_sp object when we do rollup

Comment by Varun Gupta (Inactive) [ 2018-05-17 ]

Patch
http://lists.askmonty.org/pipermail/commits/2018-May/012556.html

Comment by Vicențiu Ciorbaru [ 2018-05-18 ]

One comment then OK to push once buildbot clears the tests:

+Item* Item_sum_sp::copy_or_same(THD *thd)
 
+{
 
+  Item_sum_sp *copy_item= new (thd->mem_root) Item_sum_sp(thd, this);
 
+  copy_item->init_result_field(thd, max_length, maybe_null, &null_value, &name);
 
+  return copy_item;
 
+}
 
+

init_result_field should take &copy_item->null_value and &copy_item->name, otherwise we're using the previous item's fields null_value. There is no apparent bug as the with rollup execution happens after the parent's item values have been computed, but you are reusing the parent item's null_value field instead of the child's.

Comment by Vicențiu Ciorbaru [ 2018-05-18 ]

OK to push once the comment has been addressed.

Generated at Thu Feb 08 08:14:13 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.