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