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