1flush status; 2show status like "%custom_aggregate%"; 3Variable_name Value 4Feature_custom_aggregate_functions 0 5create table t2 (sal int(10)); 6create table t3 (sal int(10),id int); 7insert into t3 values (0,1),(1,2),(2,3),(3,4); 8create aggregate function f1(x INT) returns int 9begin 10declare tot_sum int default 0; 11declare continue handler for not found return tot_sum; 12loop 13fetch group next row; 14set tot_sum= tot_sum + x; 15end loop; 16end| 17create aggregate function f2 (x int) returns int 18begin 19declare counter int default 0; 20declare continue handler for not found return 0; 21loop 22fetch group next row; 23set counter =counter + (select f1(sal) from t1); 24end loop; 25end| 26create table t1 (sal int(10),id int(10)); 27INSERT INTO t1 (sal,id) VALUES (5000,1); 28INSERT INTO t1 (sal,id) VALUES (2000,2); 29INSERT INTO t1 (sal,id) VALUES (1000,3); 30Normal select with custom aggregate function 31select f1(sal) from t1 where id>= 1; 32f1(sal) 338000 34show status like "%custom_aggregate%"; 35Variable_name Value 36Feature_custom_aggregate_functions 1 37subqueries with custom aggregates 38explain 39select * from t1, (select f1(sal) as a from t1 where id>= 1) q where q.a=t1.sal; 40id select_type table type possible_keys key key_len ref rows Extra 411 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where 421 PRIMARY <derived2> ref key0 key0 5 test.t1.sal 2 432 DERIVED t1 ALL NULL NULL NULL NULL 3 Using where 44show status like "%custom_aggregate%"; 45Variable_name Value 46Feature_custom_aggregate_functions 2 47explain 48select * from t1, (select sal as a from t1 where (select f1(t3.sal) from t3) >=-1 ) q where q.a=t1.sal; 49id select_type table type possible_keys key key_len ref rows Extra 501 PRIMARY t1 ALL NULL NULL NULL NULL 3 511 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) 523 SUBQUERY t3 ALL NULL NULL NULL NULL 4 53show status like "%custom_aggregate%"; 54Variable_name Value 55Feature_custom_aggregate_functions 3 56explain 57select (select f1(sal) as a from t3 where t3.id= t1.id ) from t1 ; 58id select_type table type possible_keys key key_len ref rows Extra 591 PRIMARY t1 ALL NULL NULL NULL NULL 3 602 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 4 Using where 61show status like "%custom_aggregate%"; 62Variable_name Value 63Feature_custom_aggregate_functions 4 64explain 65select (select f1(sal) as a from t3 where t3.id= t1.id ) from t1 ; 66id select_type table type possible_keys key key_len ref rows Extra 671 PRIMARY t1 ALL NULL NULL NULL NULL 3 682 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 4 Using where 69show status like "%custom_aggregate%"; 70Variable_name Value 71Feature_custom_aggregate_functions 5 72custom aggregates inside other customm aggregates 73explain 74select f2(sal) from t1; 75id select_type table type possible_keys key key_len ref rows Extra 761 SIMPLE t1 ALL NULL NULL NULL NULL 3 77show status like "%custom_aggregate%"; 78Variable_name Value 79Feature_custom_aggregate_functions 6 80cte with custom aggregates 81with agg_sum as ( 82select f1(sal) from t1 where t1.id >=1 group by t1.id 83) 84select * from agg_sum; 85f1(sal) 865000 872000 881000 89show status like "%custom_aggregate%"; 90Variable_name Value 91Feature_custom_aggregate_functions 7 92drop table t2,t1,t3; 93drop function f1; 94drop function f2; 95