1--source include/default_optimizer_switch.inc 2 3flush status; 4show status like "%custom_aggregate%"; 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); 8delimiter |; 9 10create aggregate function f1(x INT) returns int 11begin 12 declare tot_sum int default 0; 13 declare continue handler for not found return tot_sum; 14 loop 15 fetch group next row; 16 set tot_sum= tot_sum + x; 17 end loop; 18end| 19 20create aggregate function f2 (x int) returns int 21begin 22 declare counter int default 0; 23 declare continue handler for not found return 0; 24 loop 25 fetch group next row; 26 set counter =counter + (select f1(sal) from t1); 27 end loop; 28end| 29 30delimiter ;| 31 32create table t1 (sal int(10),id int(10)); 33INSERT INTO t1 (sal,id) VALUES (5000,1); 34INSERT INTO t1 (sal,id) VALUES (2000,2); 35INSERT INTO t1 (sal,id) VALUES (1000,3); 36 37--echo Normal select with custom aggregate function 38select f1(sal) from t1 where id>= 1; 39show status like "%custom_aggregate%"; 40 41 42--echo subqueries with custom aggregates 43explain 44select * from t1, (select f1(sal) as a from t1 where id>= 1) q where q.a=t1.sal; 45show status like "%custom_aggregate%"; 46 47explain 48select * from t1, (select sal as a from t1 where (select f1(t3.sal) from t3) >=-1 ) q where q.a=t1.sal; 49show status like "%custom_aggregate%"; 50 51explain 52select (select f1(sal) as a from t3 where t3.id= t1.id ) from t1 ; 53show status like "%custom_aggregate%"; 54 55explain 56select (select f1(sal) as a from t3 where t3.id= t1.id ) from t1 ; 57show status like "%custom_aggregate%"; 58 59--echo custom aggregates inside other customm aggregates 60 61explain 62select f2(sal) from t1; 63show status like "%custom_aggregate%"; 64 65--echo cte with custom aggregates 66 67with agg_sum as ( 68 select f1(sal) from t1 where t1.id >=1 group by t1.id 69) 70select * from agg_sum; 71show status like "%custom_aggregate%"; 72 73drop table t2,t1,t3; 74drop function f1; 75drop function f2; 76