diff options
author | Michael Widenius <monty@mariadb.org> | 2018-03-09 14:05:35 +0200 |
---|---|---|
committer | Monty <monty@mariadb.org> | 2018-03-29 13:59:44 +0300 |
commit | a7abddeffa6a760ce948c2dfb007cdf3f1a369d5 (patch) | |
tree | 70eb743fa965a17380bbc0ac88ae79ca1075b896 /mysql-test/main/custom_aggregate_functions.result | |
parent | ab1941266c59a19703a74b5593cf3f508a5752d7 (diff) | |
download | mariadb-git-a7abddeffa6a760ce948c2dfb007cdf3f1a369d5.tar.gz |
Create 'main' test directory and move 't' and 'r' there
Diffstat (limited to 'mysql-test/main/custom_aggregate_functions.result')
-rw-r--r-- | mysql-test/main/custom_aggregate_functions.result | 949 |
1 files changed, 949 insertions, 0 deletions
diff --git a/mysql-test/main/custom_aggregate_functions.result b/mysql-test/main/custom_aggregate_functions.result new file mode 100644 index 00000000000..67be44c43f7 --- /dev/null +++ b/mysql-test/main/custom_aggregate_functions.result @@ -0,0 +1,949 @@ +create table t2 (sal int(10)); +create aggregate function f1(x INT) returns int +begin +declare continue handler for not found return 0; +loop +fetch group next row; +insert into t2 (sal) values (x); +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,1); +INSERT INTO t1 (sal,id) VALUES (1000,1); +select f1(sal) from t1 where id>= 1; +f1(sal) +0 +Warnings: +Note 4094 At line 5 in test.f1 +Note 4094 At line 5 in test.f1 +Note 4094 At line 5 in test.f1 +select * from t2; +sal +5000 +2000 +1000 +drop table t2; +drop function f1; +create aggregate function f1(x INT) returns INT +begin +insert into t1(sal) values (x); +return x; +end| +ERROR HY000: Aggregate specific instruction(FETCH GROUP NEXT ROW) missing from the aggregate function +create function f1(x INT) returns INT +begin +set x=5; +fetch group next row; +return x+1; +end | +ERROR HY000: Non-aggregate function contains aggregate specific instructions: (FETCH GROUP NEXT ROW) +create aggregate function f1(x INT) returns INT +begin +declare continue handler for not found return x; +loop +fetch group next row; +end loop; +end | +select f1(1); +f1(1) +1 +show create function f1; +Function sql_mode Create Function character_set_client collation_connection Database Collation +f1 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` AGGREGATE FUNCTION `f1`(x INT) RETURNS int(11) +begin +declare continue handler for not found return x; +loop +fetch group next row; +end loop; +end latin1 latin1_swedish_ci latin1_swedish_ci +alter function f1 aggregate none; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'aggregate none' at line 1 +show create function f1; +Function sql_mode Create Function character_set_client collation_connection Database Collation +f1 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` AGGREGATE FUNCTION `f1`(x INT) RETURNS int(11) +begin +declare continue handler for not found return x; +loop +fetch group next row; +end loop; +end latin1 latin1_swedish_ci latin1_swedish_ci +select f1(1); +f1(1) +1 +drop function f1; +create aggregate function f2(i int) returns int +begin +FEtCH GROUP NEXT ROW; +if i <= 0 then +return 0; +elseif i = 1 then +return (select count(*) from t1 where id = i); +else +return (select count(*) + f2( i - 1) from t1 where id = i); +end if; +end| +select f2(1)| +f2(1) +3 +select f2(2)| +ERROR HY000: Recursive stored functions and triggers are not allowed +select f2(3)| +ERROR HY000: Recursive stored functions and triggers are not allowed +drop function f2| +create aggregate function f1(x int) returns int +begin +declare mini int default 0; +declare continue handler for not found return mini; +loop +fetch group next row; +set mini= mini+x; +fetch group next row; +end loop; +end| +select f1(10); +f1(10) +10 +select f1(sal) from t1; +f1(sal) +6000 +select f1(sal) from t1 where 1=0; +f1(sal) +NULL +drop function f1; +create aggregate function f1(x int) returns int +begin +declare mini int default 0; +LOOP +FETCH GROUP NEXT ROW; +set mini = mini + x; +END LOOP; +end| +ERROR 42000: No RETURN found in FUNCTION test.f1 +create aggregate function f1(x int) returns int +begin +declare mini int default 0; +LOOP +FETCH GROUP NEXT ROW; +set mini = mini + x; +END LOOP; +return -1; +end| +select f1(sal) from t1| +ERROR 02000: No data - zero rows fetched, selected, or processed +drop function f1| +create aggregate function f1(x int) returns int +begin +declare mini int default 0; +declare continue handler for not found return mini; +FETCH GROUP NEXT ROW; +set mini = mini + x; +end| +select f1(sal) from t1| +ERROR 2F005: FUNCTION f1 ended without RETURN +drop function f1| +create aggregate function f1(x int) returns int +begin +declare mini int default 0; +declare continue handler for not found set mini=-1; +LOOP +FETCH GROUP NEXT ROW; +set mini = mini + x; +END LOOP; +return 0; +end| +select f1(sal) from t1| +ERROR 2F005: FUNCTION f1 ended without RETURN +drop function f1| +drop table t1| +create table t1 (sal int, id int, val int, counter int, primary key(id)); +INSERT INTO t1 (sal, id, val, counter) VALUES (1000, 1, 10, 2); +INSERT INTO t1 (sal, id, val, counter) VALUES (2000, 2, 16, 2); +INSERT INTO t1 (sal, id, val, counter) VALUES (6000, 3, 18, 1); +INSERT INTO t1 (sal, id, val, counter) VALUES (5000, 4, 15, 3); +INSERT INTO t1 (sal, id, val, counter) VALUES (3000, 5, 11, 5); +create aggregate function f1(x INT) returns double +begin +declare z double default 0; +declare continue handler for not found return z; +loop +fetch group next row; +set z= z+x; +end loop; +end| +select id, f1(sal) from t1 where id>= 1 group by counter order by val; +id f1(sal) +1 3000 +3 6000 +4 5000 +5 3000 +select id, f1(sal) from t1; +id f1(sal) +1 17000 +select id, f1(sal) from t1 where id>= 1; +id f1(sal) +1 17000 +select id, f1(sal) from t1 where id>= 1 group by counter; +id f1(sal) +1 3000 +3 6000 +4 5000 +5 3000 +select id, f1(sal) from t1 where id>= 1 group by id; +id f1(sal) +1 1000 +2 2000 +3 6000 +4 5000 +5 3000 +select id, f1(sal) from t1 where id>= 1 group by val; +id f1(sal) +1 1000 +2 2000 +3 6000 +4 5000 +5 3000 +select id, f1(sal) from t1 where id>= 1 group by counter order by counter; +id f1(sal) +1 3000 +3 6000 +4 5000 +5 3000 +select id, f1(sal) from t1 where id>= 1 group by counter order by val; +id f1(sal) +1 3000 +3 6000 +4 5000 +5 3000 +select id, f1(sal) from t1 where id>= 1 group by counter order by id; +id f1(sal) +1 3000 +3 6000 +4 5000 +5 3000 +select id, f1(sal) from t1 where id>= 1 group by val order by counter; +id f1(sal) +1 1000 +2 2000 +3 6000 +4 5000 +5 3000 +select id, f1(sal) from t1 where id>= 1 group by val order by id; +id f1(sal) +1 1000 +2 2000 +3 6000 +4 5000 +5 3000 +select id, f1(sal) from t1 where id>= 1 group by val order by val; +id f1(sal) +1 1000 +2 2000 +3 6000 +4 5000 +5 3000 +drop table t1; +create table t1 (sal int, id int, val int, counter int, primary key(id), unique key(val)); +INSERT INTO t1 (sal, id, val, counter) VALUES (1000, 1, 10, 2); +INSERT INTO t1 (sal, id, val, counter) VALUES (2000, 2, NULL, 2); +INSERT INTO t1 (sal, id, val, counter) VALUES (6000, 3, 18, 1); +INSERT INTO t1 (sal, id, val, counter) VALUES (5000, 4, 15, 3); +INSERT INTO t1 (sal, id, val, counter) VALUES (3000, 5, 11, 5); +select id, f1(sal) from t1; +id f1(sal) +1 17000 +select id, f1(sal) from t1 where id>= 1; +id f1(sal) +1 17000 +select id, f1(sal) from t1 where id>= 1 group by counter; +id f1(sal) +1 3000 +3 6000 +4 5000 +5 3000 +select id, f1(sal) from t1 where id>= 1 group by id; +id f1(sal) +1 1000 +2 2000 +3 6000 +4 5000 +5 3000 +select id, f1(sal) from t1 where id>= 1 group by val; +id f1(sal) +1 1000 +2 2000 +3 6000 +4 5000 +5 3000 +select id, f1(sal) from t1 where id>= 1 group by counter order by counter; +id f1(sal) +1 3000 +3 6000 +4 5000 +5 3000 +select id, f1(sal) from t1 where id>= 1 group by counter order by val; +id f1(sal) +1 3000 +3 6000 +4 5000 +5 3000 +select id, f1(sal) from t1 where id>= 1 group by counter order by id; +id f1(sal) +1 3000 +3 6000 +4 5000 +5 3000 +select id, f1(sal) from t1 where id>= 1 group by val order by counter; +id f1(sal) +1 1000 +2 2000 +3 6000 +4 5000 +5 3000 +select id, f1(sal) from t1 where id>= 1 group by val order by id; +id f1(sal) +1 1000 +2 2000 +3 6000 +4 5000 +5 3000 +select id, f1(sal) from t1 where id>= 1 group by val order by val; +id f1(sal) +1 1000 +2 2000 +3 6000 +4 5000 +5 3000 +drop table t1; +create table t1 (sal int, id int, val int, counter int, primary key(id), INDEX name (val,counter)); +INSERT INTO t1 (sal, id, val, counter) VALUES (1000, 1, 10, 2); +INSERT INTO t1 (sal, id, val, counter) VALUES (2000, 2, 10, 4); +INSERT INTO t1 (sal, id, val, counter) VALUES (6000, 3, 18, 1); +INSERT INTO t1 (sal, id, val, counter) VALUES (5000, 4, 11, 3); +INSERT INTO t1 (sal, id, val, counter) VALUES (3000, 5, 11, 5); +select id, f1(sal) from t1; +id f1(sal) +1 17000 +select id, f1(sal) from t1 where id>= 1; +id f1(sal) +1 17000 +select id, f1(sal) from t1 where id>= 1 group by counter; +id f1(sal) +1 1000 +2 2000 +3 6000 +4 5000 +5 3000 +select id, f1(sal) from t1 where id>= 1 group by id; +id f1(sal) +1 1000 +2 2000 +3 6000 +4 5000 +5 3000 +select id, f1(sal) from t1 where id>= 1 group by val; +id f1(sal) +1 3000 +3 6000 +4 8000 +select id, f1(sal) from t1 where id>= 1 group by counter order by counter; +id f1(sal) +1 1000 +2 2000 +3 6000 +4 5000 +5 3000 +select id, f1(sal) from t1 where id>= 1 group by counter order by val; +id f1(sal) +1 1000 +2 2000 +3 6000 +4 5000 +5 3000 +select id, f1(sal) from t1 where id>= 1 group by counter order by id; +id f1(sal) +1 1000 +2 2000 +3 6000 +4 5000 +5 3000 +select id, f1(sal) from t1 where id>= 1 group by val order by counter; +id f1(sal) +1 3000 +3 6000 +4 8000 +select id, f1(sal) from t1 where id>= 1 group by val order by id; +id f1(sal) +1 3000 +3 6000 +4 8000 +select id, f1(sal) from t1 where id>= 1 group by val order by val; +id f1(sal) +1 3000 +3 6000 +4 8000 +drop table t1; +drop function f1; +create aggregate function f1(x INT) returns double +begin +declare z double default 0; +declare continue handler for not found return z; +loop +fetch group next row; +set z= z+x; +end loop; +end| +create aggregate function f2() returns double +begin +declare z int default 0; +declare continue handler for not found return z; +loop +fetch group next row; +set z = z+1; +end loop; +end| +create table t1 (sal int, id int, val int, counter int); +INSERT INTO t1 (sal, id, val, counter) VALUES (1000, 2, 10, 2); +INSERT INTO t1 (sal, id, val, counter) VALUES (2000, 1, 16, 5); +INSERT INTO t1 (sal, id, val, counter) VALUES (6000, 2, 18, 1); +INSERT INTO t1 (sal, id, val, counter) VALUES (5000, 3, 15, 3); +INSERT INTO t1 (sal, id, val, counter) VALUES (3000, 4, 11, 4); +prepare test from "select f2() from t1 where id>= ?"; +set @param= 2; +execute test using @param; +f2() +4 +execute test using @param; +f2() +4 +execute test using @param; +f2() +4 +execute test using @param; +f2() +4 +set @param= 1; +execute test using @param; +f2() +5 +set @param= 3; +execute test using @param; +f2() +2 +set @param= 4; +execute test using @param; +f2() +1 +deallocate prepare test; +prepare test from "select f1(sal) from t1 where id>= ?"; +set @param= 2; +execute test using @param; +f1(sal) +15000 +execute test using @param; +f1(sal) +15000 +execute test using @param; +f1(sal) +15000 +execute test using @param; +f1(sal) +15000 +set @param= 1; +execute test using @param; +f1(sal) +17000 +set @param= 3; +execute test using @param; +f1(sal) +8000 +set @param= 4; +execute test using @param; +f1(sal) +3000 +set @param= 5; +execute test using @param; +f1(sal) +NULL +deallocate prepare test; +drop function f2; +prepare test from "select f1(sal) from t1 where id>= ?"; +set @param= 2; +execute test using @param; +f1(sal) +15000 +drop function f1; +create function f1(x int) returns int +return -1; +execute test using @param; +f1(sal) +-1 +-1 +-1 +-1 +drop function f1; +create aggregate function f1(x INT) returns double +begin +declare z double default 0; +declare continue handler for not found return z; +loop +fetch group next row; +set z= z+x; +end loop; +end| +execute test using @param; +f1(sal) +15000 +deallocate prepare test; +drop table t1; +drop function f1; +create table t1 (sal int, id int, val varchar(10), counter int); +INSERT INTO t1 (sal, id, val, counter) VALUES (1000, 2, 'ab', 2); +INSERT INTO t1 (sal, id, val, counter) VALUES (1000, 1, 'cd', 5); +INSERT INTO t1 (sal, id, val, counter) VALUES (1000, 2, 'ef', 1); +INSERT INTO t1 (sal, id, val, counter) VALUES (1000, 3, 'gh', 3); +INSERT INTO t1 (sal, id, val, counter) VALUES (1000, 4, 'ij', 4); +create table t2 (sal int, id int, val int, counter int); +INSERT INTO t2 (sal, id, val, counter) VALUES (1000, 2, 10, 2); +INSERT INTO t2 (sal, id, val, counter) VALUES (2000, 1, 16, 5); +INSERT INTO t2 (sal, id, val, counter) VALUES (6000, 2, 18, 1); +INSERT INTO t2 (sal, id, val, counter) VALUES (5000, 3, 15, 3); +INSERT INTO t2 (sal, id, val, counter) VALUES (3000, 4, 11, 4); +create aggregate function f1(x double) returns double +begin +declare z double default 0; +declare continue handler for not found return z; +loop +fetch group next row; +set z= z+x; +end loop; +end| +create aggregate function f2(x INT) returns CHAR(10) +begin +declare mini INT default 0; +declare continue handler for not found return mini; +loop +fetch group next row; +set mini= mini + x; +end loop; +end| +create aggregate function f3(x INT) returns CHAR(10) +begin +declare mini INT default 0; +declare continue handler for not found return mini; +loop +fetch group next row; +set mini= mini + x; +fetch group next row; +set mini= mini - x; +end loop; +end| +create aggregate function f4(x INT, y varchar(10)) returns varchar(1000) +begin +declare str varchar(1000) default ''; +declare continue handler for not found return str; +loop +fetch group next row; +set str= concat(str,y); +end loop; +end| +create aggregate function f5(x INT) returns varchar(1000) +begin +declare z int default 0; +DECLARE cur1 CURSOR FOR SELECT sal FROM test.t2; +declare continue handler for not found return 0; +loop +fetch group next row; +set z = z+x; +end loop; +end| +create function f6(x int) returns int +return (select f1(sal) from t1)| +select f1(sal) from t1; +f1(sal) +5000 +select f1(sal) from t1 where id>= 1 group by counter; +f1(sal) +1000 +1000 +1000 +1000 +1000 +select f3(sal) from t1; +f3(sal) +1000 +select f2(val) from t1; +ERROR 22007: Incorrect integer value: 'ab' for column 'x' at row 1 +select val, id, c from (select f1(sal) as c from t2) as t1, t2; +val id c +10 2 17000 +11 4 17000 +15 3 17000 +16 1 17000 +18 2 17000 +select f1(sal),f1(val), f1(id), f1(sal) from t2; +f1(sal) f1(val) f1(id) f1(sal) +17000 70 12 17000 +select f4(sal, val) from t1; +f4(sal, val) +abcdefghij +select c from (select f1(sal) as c from t2) as t1; +c +17000 +select f1((select val from t2 where 0 > 1)) from t1; +f1((select val from t2 where 0 > 1)) +NULL +select f1((select val from t2 where id= 1)) from t1; +f1((select val from t2 where id= 1)) +80 +select f5(sal) from t1; +f5(sal) +0 +SELECT f1(sal)*f1(sal) FROM t1; +f1(sal)*f1(sal) +25000000 +SELECT (SELECT f1(sal) FROM t1) FROM t2; +(SELECT f1(sal) FROM t1) +5000 +5000 +5000 +5000 +5000 +select id, f1(sal) from t1; +id f1(sal) +2 5000 +select id, f1(sal) from t1 where id>= 1; +id f1(sal) +2 5000 +select f1(sal), f1(sal) from t1 where id>= 1 group by counter; +f1(sal) f1(sal) +1000 1000 +1000 1000 +1000 1000 +1000 1000 +1000 1000 +select f1(sal), f1(sal) from t1 where id>= 1 group by id ; +f1(sal) f1(sal) +1000 1000 +1000 1000 +1000 1000 +2000 2000 +select f1(sal) from t1 where id>= 1 group by id ; +f1(sal) +1000 +1000 +1000 +2000 +select f1(sal) from t1 where id>= 1 order by counter; +f1(sal) +5000 +select f1(sal) from t1 where id>= 1 group by id order by counter; +f1(sal) +2000 +1000 +1000 +1000 +select counter, id, f1(sal) from t1 where id>= 1 group by id order by counter; +counter id f1(sal) +2 2 2000 +3 3 1000 +4 4 1000 +5 1 1000 +select id, f1(sal) from t1 where id>= 1 group by id order by counter; +id f1(sal) +2 2000 +3 1000 +4 1000 +1 1000 +drop table t1; +drop table t2; +drop function f1; +drop function f2; +drop function f3; +drop function f4; +drop function f5; +drop function f6; +create aggregate function f1(x INT) returns INT +begin +declare z double default 1000; +declare continue handler for not found return z; +loop +fetch group next row; +set z= (z&x); +end loop; +end| +create table t1 (sal int, id int, val int, counter int); +INSERT INTO t1 (sal, id, val, counter) VALUES (1000, 2, 10, 2); +INSERT INTO t1 (sal, id, val, counter) VALUES (7000, 1, 16, 5); +INSERT INTO t1 (sal, id, val, counter) VALUES (6000, 2, 18, 1); +INSERT INTO t1 (sal, id, val, counter) VALUES (5000, 3, 15, 3); +INSERT INTO t1 (sal, id, val, counter) VALUES (3000, 4, 11, 4); +INSERT INTO t1 (sal, id, val, counter) VALUES (2000, 5, 10, 7); +INSERT INTO t1 (sal, id, val, counter) VALUES (5000, 7, 13, 8); +INSERT INTO t1 (sal, id, val, counter) VALUES (6000, 6, 19, 9); +INSERT INTO t1 (sal, id, val, counter) VALUES (7000, 7, 12, 0); +INSERT INTO t1 (sal, id, val, counter) VALUES (4000, 6, 14, 1); +INSERT INTO t1 (sal, id, val, counter) VALUES (8000, 5, 19, 3); +INSERT INTO t1 (sal, id, val, counter) VALUES (9000, 4, 11, 4); +INSERT INTO t1 (sal, id, val, counter) VALUES (1000, 3, 11, 2); +select f1(sal) from t1 where id>= 1; +f1(sal) +768 +drop function f1; +create aggregate function f1(x INT) returns double +begin +declare z double default 0; +declare count double default 0; +declare continue handler for not found return z/count; +loop +fetch group next row; +set z= z+x; +set count= count+1; +end loop; +end| +select f1(sal) from t1 where id>= 1; +f1(sal) +4923.076923076923 +drop function f1; +create aggregate function f1(x INT) returns INT +begin +declare maxi INT default -1; +declare continue handler for not found return maxi; +loop +fetch group next row; +if maxi < x then +set maxi= x; +end if; +end loop; +end| +select f1(sal) from t1 where id>= 1; +f1(sal) +9000 +drop function f1; +create aggregate function f1(x INT) returns double +begin +declare mini INT default 100000; +declare continue handler for not found return mini; +loop +fetch group next row; +if mini > x then +set mini = x; +end if; +end loop; +end| +select f1(sal) from t1 where id>= 1; +f1(sal) +1000 +drop function f1; +create aggregate function f1(x INT) returns double +begin +declare z double default 0; +declare continue handler for not found return z; +loop +fetch group next row; +set z= z^x; +end loop; +end| +select f1(sal) from t1 where id>= 1; +f1(sal) +16288 +drop function f1; +create aggregate function f1(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| +select f1(sal) from t1 where id>= 1; +f1(sal) +64000 +create aggregate function f2() returns INT +begin +declare z double default 0; +declare continue handler for not found return z; +loop +fetch group next row; +set z= z+1; +end loop; +end| +select f2() from t1; +f2() +13 +create table t2 (sal int, id int); +INSERT INTO t2 (sal, id) VALUES (NULL, 1); +INSERT INTO t2 (sal, id) VALUES (2000, 1); +INSERT INTO t2 (sal, id) VALUES (3000, 1); +select f1(sal) from t2; +f1(sal) +NULL +select f1(1); +f1(1) +1 +create function f3() returns int +return (select f1(sal) from t1); +select f3(); +f3() +64000 +create function f4() returns INT +return 1; +create aggregate function f5() returns INT +begin +declare z double default 0; +declare continue handler for not found return z; +loop +fetch group next row; +set z= z+f3(); +end loop; +end| +select f5() from t2; +f5() +192000 +Warnings: +Note 4094 At line 6 in test.f5 +Note 4094 At line 6 in test.f5 +Note 4094 At line 6 in test.f5 +create aggregate function f6(x INT) returns INT +begin +declare z int default 0; +declare continue handler for not found return z; +loop +fetch group next row; +if x then +set z= z+(select f1(sal) from t1); +end if; +end loop; +end| +select f6(sal) from t2; +f6(sal) +128000 +Warnings: +Note 4094 At line 6 in test.f6 +Note 4094 At line 6 in test.f6 +select id, f1(sal) from t1 where id>= 1 group by id; +id f1(sal) +1 7000 +2 7000 +3 6000 +4 12000 +5 10000 +6 10000 +7 12000 +select counter, f1(sal) from t1 where id>= 1 group by counter; +counter f1(sal) +0 7000 +1 10000 +2 2000 +3 13000 +4 12000 +5 7000 +7 2000 +8 5000 +9 6000 +select val, f1(sal) from t1 where id>= 1 group by val; +val f1(sal) +10 3000 +11 13000 +12 7000 +13 5000 +14 4000 +15 5000 +16 7000 +18 6000 +19 14000 +select counter, f1(sal) from t1 where id>= 1 group by id order by counter; +counter f1(sal) +0 12000 +2 6000 +2 7000 +4 12000 +5 7000 +7 10000 +9 10000 +select counter, id, f1(sal), f1(sal) from t1 where id>= 1 group by id order by counter; +counter id f1(sal) f1(sal) +0 7 12000 12000 +2 2 7000 7000 +2 3 6000 6000 +4 4 12000 12000 +5 1 7000 7000 +7 5 10000 10000 +9 6 10000 10000 +select counter, id, f1(sal), sum(distinct sal) from t1 where id>= 1 group by id order by counter desc; +counter id f1(sal) sum(distinct sal) +0 7 12000 12000 +2 2 7000 7000 +2 3 6000 6000 +4 4 12000 12000 +5 1 7000 7000 +7 5 10000 10000 +9 6 10000 10000 +create table t3 (i int); +INSERT INTO t3 (i) select f1(sal) from t1; +select * from t3; +i +64000 +create aggregate function f7(x INT) returns INT +begin +declare z int default 0; +DECLARE done BOOLEAN DEFAULT FALSE; +DECLARE a,b,c INT; +DECLARE cur1 CURSOR FOR SELECT id FROM test.t2; +declare continue handler for not found return z; +outer_loop: LOOP +FETCH GROUP NEXT ROW; +set z= z+x; +inner_block: begin +DECLARE cur2 CURSOR FOR SELECT id FROM test.t2; +DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; +OPEN cur2; +read_loop: LOOP +FETCH cur2 INTO a; +IF done THEN +CLOSE cur2; +LEAVE read_loop; +END IF; +END LOOP read_loop; +end inner_block; +END LOOP outer_loop; +end| +select f7(sal) from t1; +f7(sal) +64000 +Warnings: +Note 4094 At line 9 in test.f7 +Note 4094 At line 9 in test.f7 +Note 4094 At line 9 in test.f7 +Note 4094 At line 9 in test.f7 +Note 4094 At line 9 in test.f7 +Note 4094 At line 9 in test.f7 +Note 4094 At line 9 in test.f7 +Note 4094 At line 9 in test.f7 +Note 4094 At line 9 in test.f7 +Note 4094 At line 9 in test.f7 +Note 4094 At line 9 in test.f7 +Note 4094 At line 9 in test.f7 +Note 4094 At line 9 in test.f7 +drop table t1; +drop table t2; +drop table t3; +drop function f1; +drop function f2; +drop function f3; +drop function f4; +drop function f5; +drop function f6; +drop function f7; +create aggregate function f1(x date) returns date +begin +declare continue handler for not found return x; +loop +fetch group next row; +end loop; +end| +select f1('2001-01-01'),cast(f1('2001-01-01') as time); +f1('2001-01-01') cast(f1('2001-01-01') as time) +2001-01-01 00:00:00 +drop function f1; |