diff options
33 files changed, 2307 insertions, 84 deletions
diff --git a/mysql-test/r/create_drop_function.result b/mysql-test/r/create_drop_function.result index 8e529a587fa..3ba6581d61b 100644 --- a/mysql-test/r/create_drop_function.result +++ b/mysql-test/r/create_drop_function.result @@ -3,8 +3,8 @@ CREATE FUNCTION f1(str char(20)) RETURNS CHAR(100) RETURN CONCAT('Hello, ', str, '!'); SELECT * FROM mysql.proc WHERE name like 'f1'; -db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 -test f1 FUNCTION f1 SQL CONTAINS_SQL NO DEFINER str char(20) char(100) CHARSET latin1 RETURN CONCAT('Hello, ', str, '!') root@localhost 2014-09-30 08:00:00 2014-09-30 08:00:00 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION latin1 latin1_swedish_ci latin1_swedish_ci RETURN CONCAT('Hello, ', str, '!') +db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 aggregate +test f1 FUNCTION f1 SQL CONTAINS_SQL NO DEFINER str char(20) char(100) CHARSET latin1 RETURN CONCAT('Hello, ', str, '!') root@localhost 2014-09-30 08:00:00 2014-09-30 08:00:00 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION latin1 latin1_swedish_ci latin1_swedish_ci RETURN CONCAT('Hello, ', str, '!') NONE SELECT f1('world'); f1('world') Hello, world! diff --git a/mysql-test/r/custom_aggregate_functions.result b/mysql-test/r/custom_aggregate_functions.result new file mode 100644 index 00000000000..ca8612ba652 --- /dev/null +++ b/mysql-test/r/custom_aggregate_functions.result @@ -0,0 +1,938 @@ +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 4093 At line 5 in test.f1 +Note 4093 At line 5 in test.f1 +Note 4093 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 4093 At line 6 in test.f5 +Note 4093 At line 6 in test.f5 +Note 4093 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 4093 At line 6 in test.f6 +Note 4093 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 4093 At line 9 in test.f7 +Note 4093 At line 9 in test.f7 +Note 4093 At line 9 in test.f7 +Note 4093 At line 9 in test.f7 +Note 4093 At line 9 in test.f7 +Note 4093 At line 9 in test.f7 +Note 4093 At line 9 in test.f7 +Note 4093 At line 9 in test.f7 +Note 4093 At line 9 in test.f7 +Note 4093 At line 9 in test.f7 +Note 4093 At line 9 in test.f7 +Note 4093 At line 9 in test.f7 +Note 4093 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; diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index 23fbfad09d4..34f8fe25352 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -665,6 +665,7 @@ proc character_set_client char(32) proc collation_connection char(32) proc db_collation char(32) proc body_utf8 longblob +proc aggregate enum('NONE','GROUP') drop table t115; create procedure p108 () begin declare c cursor for select data_type from information_schema.columns; open c; open c; end;// @@ -1270,7 +1271,7 @@ drop table t1; use mysql; INSERT INTO `proc` VALUES ('test','','PROCEDURE','','SQL','CONTAINS_SQL', 'NO','DEFINER','','','BEGIN\r\n \r\nEND','root@%','2006-03-02 18:40:03', -'2006-03-02 18:40:03','','','utf8','utf8_general_ci','utf8_general_ci','n/a'); +'2006-03-02 18:40:03','','','utf8','utf8_general_ci','utf8_general_ci','n/a', 'NONE'); select routine_name from information_schema.routines where ROUTINE_SCHEMA='test'; routine_name diff --git a/mysql-test/r/sp-destruct.result b/mysql-test/r/sp-destruct.result index 5bb3b17d4b8..112fdb3978d 100644 --- a/mysql-test/r/sp-destruct.result +++ b/mysql-test/r/sp-destruct.result @@ -1,4 +1,4 @@ -call mtr.add_suppression("Column count of mysql.proc is wrong. Expected 20, found 19. The table is probably corrupted"); +call mtr.add_suppression("Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted"); call mtr.add_suppression("Stored routine .test...bug14233_[123].: invalid value in column mysql.proc"); flush table mysql.proc; use test; @@ -14,13 +14,13 @@ create table t1 (id int); create trigger t1_ai after insert on t1 for each row call bug14233(); alter table mysql.proc drop security_type; call bug14233(); -ERROR HY000: Column count of mysql.proc is wrong. Expected 20, found 19. The table is probably corrupted +ERROR HY000: Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted create view v1 as select bug14233_f(); -ERROR HY000: Column count of mysql.proc is wrong. Expected 20, found 19. The table is probably corrupted +ERROR HY000: Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted insert into t1 values (0); -ERROR HY000: Column count of mysql.proc is wrong. Expected 20, found 19. The table is probably corrupted +ERROR HY000: Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted show procedure status; -ERROR HY000: Column count of mysql.proc is wrong. Expected 20, found 19. The table is probably corrupted +ERROR HY000: Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted flush table mysql.proc; call bug14233(); ERROR HY000: Incorrect information in file: './mysql/proc.frm' @@ -146,7 +146,7 @@ alter table mysql.proc drop column security_type; # The below statement should not cause assertion failure. drop database mysqltest; Warnings: -Error 1805 Column count of mysql.proc is wrong. Expected 20, found 19. The table is probably corrupted +Error 1805 Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted # Restore mysql.proc. drop table mysql.proc; # diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result index 40643a97765..ad4b18fc37a 100644 --- a/mysql-test/r/sp-error.result +++ b/mysql-test/r/sp-error.result @@ -1212,7 +1212,7 @@ ERROR 42S02: Unknown table 'c' in field list drop procedure bug15091; drop function if exists bug16896; create aggregate function bug16896() returns int return 1; -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 '() returns int return 1' at line 1 +ERROR HY000: Aggregate specific instruction(FETCH GROUP NEXT ROW) missing from the aggregate function DROP PROCEDURE IF EXISTS bug14702; CREATE IF NOT EXISTS PROCEDURE bug14702() BEGIN diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 2b15bd09cc4..3b8f35e0c79 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -5324,7 +5324,7 @@ DROP PROCEDURE bug21414| set names utf8| drop database if exists това_е_дълго_име_за_база_данни_нали| create database това_е_дълго_име_за_база_данни_нали| -INSERT INTO mysql.proc VALUES ('това_е_дълго_име_за_база_данни_нали','това_е_процедура_с_доста_дълго_име_нали_и_още_по_дълго','PROCEDURE','това_е_процедура_с_доста_дълго_име_нали_и_още_по_дълго','SQL','CONTAINS_SQL','NO','DEFINER','','','bad_body','root@localhost',now(), now(),'','', 'utf8', 'utf8_general_ci', 'utf8_general_ci', 'n/a')| +INSERT INTO mysql.proc VALUES ('това_е_дълго_име_за_база_данни_нали','това_е_процедура_с_доста_дълго_име_нали_и_още_по_дълго','PROCEDURE','това_е_процедура_с_доста_дълго_име_нали_и_още_по_дълго','SQL','CONTAINS_SQL','NO','DEFINER','','','bad_body','root@localhost',now(), now(),'','', 'utf8', 'utf8_general_ci', 'utf8_general_ci', 'n/a', 'NONE')| call това_е_дълго_име_за_база_данни_нали.това_е_процедура_с_доста_дълго_име_нали_и_още_по_дълго()| ERROR HY000: Failed to load routine това_е_дълго_име_за_база_данни_нали.това_е_процедура_с_доста_дълго_име_нали_и_още_по_дълго. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6) drop database това_е_дълго_име_за_база_данни_нали| diff --git a/mysql-test/r/system_mysql_db.result b/mysql-test/r/system_mysql_db.result index 7ba77e60e42..1227896853d 100644 --- a/mysql-test/r/system_mysql_db.result +++ b/mysql-test/r/system_mysql_db.result @@ -220,6 +220,7 @@ proc CREATE TABLE `proc` ( `collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `db_collation` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `body_utf8` longblob DEFAULT NULL, + `aggregate` enum('NONE','GROUP') NOT NULL DEFAULT 'NONE', PRIMARY KEY (`db`,`name`,`type`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stored Procedures' show create table event; diff --git a/mysql-test/r/system_mysql_db_fix40123.result b/mysql-test/r/system_mysql_db_fix40123.result index 7ba77e60e42..1227896853d 100644 --- a/mysql-test/r/system_mysql_db_fix40123.result +++ b/mysql-test/r/system_mysql_db_fix40123.result @@ -220,6 +220,7 @@ proc CREATE TABLE `proc` ( `collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `db_collation` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `body_utf8` longblob DEFAULT NULL, + `aggregate` enum('NONE','GROUP') NOT NULL DEFAULT 'NONE', PRIMARY KEY (`db`,`name`,`type`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stored Procedures' show create table event; diff --git a/mysql-test/suite/funcs_1/r/is_columns_mysql.result b/mysql-test/suite/funcs_1/r/is_columns_mysql.result index 21ffae902a4..4305c40770c 100644 --- a/mysql-test/suite/funcs_1/r/is_columns_mysql.result +++ b/mysql-test/suite/funcs_1/r/is_columns_mysql.result @@ -133,6 +133,7 @@ def mysql innodb_table_stats sum_of_other_index_sizes 6 NULL NO bigint NULL NULL def mysql innodb_table_stats table_name 2 NULL NO varchar 64 192 NULL NULL NULL utf8 utf8_bin varchar(64) PRI select,insert,update,references NEVER NULL def mysql plugin dl 2 '' NO varchar 128 384 NULL NULL NULL utf8 utf8_general_ci varchar(128) select,insert,update,references NEVER NULL def mysql plugin name 1 '' NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) PRI select,insert,update,references NEVER NULL +def mysql proc aggregate 21 'NONE' NO enum 5 15 NULL NULL NULL utf8 utf8_general_ci enum('NONE','GROUP') select,insert,update,references NEVER NULL def mysql proc body 11 NULL NO longblob 4294967295 4294967295 NULL NULL NULL NULL NULL longblob select,insert,update,references NEVER NULL def mysql proc body_utf8 20 NULL YES longblob 4294967295 4294967295 NULL NULL NULL NULL NULL longblob select,insert,update,references NEVER NULL def mysql proc character_set_client 17 NULL YES char 32 96 NULL NULL NULL utf8 utf8_bin char(32) select,insert,update,references NEVER NULL @@ -488,6 +489,7 @@ NULL mysql proc modified timestamp NULL NULL NULL NULL timestamp 3.0000 mysql proc collation_connection char 32 96 utf8 utf8_bin char(32) 3.0000 mysql proc db_collation char 32 96 utf8 utf8_bin char(32) 1.0000 mysql proc body_utf8 longblob 4294967295 4294967295 NULL NULL longblob +3.0000 mysql proc aggregate enum 5 15 utf8 utf8_general_ci enum('NONE','GROUP') 3.0000 mysql procs_priv Host char 60 180 utf8 utf8_bin char(60) 3.0000 mysql procs_priv Db char 64 192 utf8 utf8_bin char(64) 3.0000 mysql procs_priv User char 80 240 utf8 utf8_bin char(80) diff --git a/mysql-test/suite/funcs_1/r/is_columns_mysql_embedded.result b/mysql-test/suite/funcs_1/r/is_columns_mysql_embedded.result index c7d10790325..2f1c9188989 100644 --- a/mysql-test/suite/funcs_1/r/is_columns_mysql_embedded.result +++ b/mysql-test/suite/funcs_1/r/is_columns_mysql_embedded.result @@ -119,6 +119,7 @@ def mysql index_stats prefix_arity 4 NULL NO int NULL NULL 10 0 NULL NULL NULL i def mysql index_stats table_name 2 NULL NO varchar 64 192 NULL NULL NULL utf8 utf8_bin varchar(64) PRI NEVER NULL def mysql plugin dl 2 '' NO varchar 128 384 NULL NULL NULL utf8 utf8_general_ci varchar(128) NEVER NULL def mysql plugin name 1 '' NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) PRI NEVER NULL +def mysql proc aggregate 21 'NONE' NO enum 5 15 NULL NULL NULL utf8 utf8_general_ci enum('NONE','GROUP') NEVER NULL def mysql proc body 11 NULL NO longblob 4294967295 4294967295 NULL NULL NULL NULL NULL longblob NEVER NULL def mysql proc body_utf8 20 NULL YES longblob 4294967295 4294967295 NULL NULL NULL NULL NULL longblob NEVER NULL def mysql proc character_set_client 17 NULL YES char 32 96 NULL NULL NULL utf8 utf8_bin char(32) NEVER NULL @@ -472,6 +473,7 @@ NULL mysql proc modified timestamp NULL NULL NULL NULL timestamp 3.0000 mysql proc collation_connection char 32 96 utf8 utf8_bin char(32) 3.0000 mysql proc db_collation char 32 96 utf8 utf8_bin char(32) 1.0000 mysql proc body_utf8 longblob 4294967295 4294967295 NULL NULL longblob +3.0000 mysql proc aggregate enum 5 15 utf8 utf8_general_ci enum('NONE','GROUP') 3.0000 mysql procs_priv Host char 60 180 utf8 utf8_bin char(60) 3.0000 mysql procs_priv Db char 64 192 utf8 utf8_bin char(64) 3.0000 mysql procs_priv User char 80 240 utf8 utf8_bin char(80) diff --git a/mysql-test/suite/funcs_1/r/storedproc.result b/mysql-test/suite/funcs_1/r/storedproc.result index c86a73618df..dc9c31566d2 100644 --- a/mysql-test/suite/funcs_1/r/storedproc.result +++ b/mysql-test/suite/funcs_1/r/storedproc.result @@ -1771,48 +1771,48 @@ Testcase 4.1.9: drop procedure -------------------------------------------------------------------------------- SELECT * from mysql.proc where specific_name='sp9'; -db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 +db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 aggregate DROP PROCEDURE IF EXISTS sp9; SELECT * from mysql.proc where specific_name='sp9'; -db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 +db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 aggregate CREATE PROCEDURE sp9()SELECT * from t1; SELECT * from mysql.proc where specific_name='sp9'; -db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 -db_storedproc sp9 PROCEDURE sp9 SQL CONTAINS_SQL NO DEFINER SELECT * from t1 root@localhost created modified latin1 latin1_swedish_ci latin1_swedish_ci SELECT * from t1 +db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 aggregate +db_storedproc sp9 PROCEDURE sp9 SQL CONTAINS_SQL NO DEFINER SELECT * from t1 root@localhost created modified latin1 latin1_swedish_ci latin1_swedish_ci SELECT * from t1 NONE DROP PROCEDURE sp9; SELECT * from mysql.proc where specific_name='sp9'; -db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 +db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 aggregate CREATE PROCEDURE sp9()SELECT * from t1; SELECT * from mysql.proc where specific_name='sp9'; -db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 -db_storedproc sp9 PROCEDURE sp9 SQL CONTAINS_SQL NO DEFINER SELECT * from t1 root@localhost created modified latin1 latin1_swedish_ci latin1_swedish_ci SELECT * from t1 +db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 aggregate +db_storedproc sp9 PROCEDURE sp9 SQL CONTAINS_SQL NO DEFINER SELECT * from t1 root@localhost created modified latin1 latin1_swedish_ci latin1_swedish_ci SELECT * from t1 NONE DROP PROCEDURE IF EXISTS sp9; SELECT * from mysql.proc where specific_name='sp9'; -db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 +db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 aggregate Testcase 4.1.10: ---------------- DROP FUNCTION -------------------------------------------------------------------------------- SELECT * from mysql.proc where specific_name='fn10' and type='function'; -db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 +db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 aggregate DROP FUNCTION IF EXISTS fn10; SELECT * from mysql.proc where specific_name='fn10' and type='function'; -db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 +db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 aggregate CREATE FUNCTION fn10() returns int return 100; SELECT * from mysql.proc where specific_name='fn10' and type='function'; -db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 -db_storedproc fn10 FUNCTION fn10 SQL CONTAINS_SQL NO DEFINER int(11) return 100 root@localhost created modified latin1 latin1_swedish_ci latin1_swedish_ci return 100 +db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 aggregate +db_storedproc fn10 FUNCTION fn10 SQL CONTAINS_SQL NO DEFINER int(11) return 100 root@localhost created modified latin1 latin1_swedish_ci latin1_swedish_ci return 100 NONE DROP FUNCTION fn10; SELECT * from mysql.proc where specific_name='fn10' and type='function'; -db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 +db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 aggregate CREATE FUNCTION fn10() returns int return 100; SELECT * from mysql.proc where specific_name='fn10' and type='function'; -db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 -db_storedproc fn10 FUNCTION fn10 SQL CONTAINS_SQL NO DEFINER int(11) return 100 root@localhost created modified latin1 latin1_swedish_ci latin1_swedish_ci return 100 +db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 aggregate +db_storedproc fn10 FUNCTION fn10 SQL CONTAINS_SQL NO DEFINER int(11) return 100 root@localhost created modified latin1 latin1_swedish_ci latin1_swedish_ci return 100 NONE DROP FUNCTION IF EXISTS fn10; SELECT * from mysql.proc where specific_name='fn10' and type='function'; -db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 +db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 aggregate Testcase 4.1.11: ---------------- @@ -16172,15 +16172,15 @@ insert into t43 values('abcde', 'a!@#$%^&*('); CREATE PROCEDURE d1.sp4() SELECT * from d1.t43; SELECT * from mysql.proc where specific_name = 'sp4'; -db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 -d1 sp4 PROCEDURE sp4 SQL CONTAINS_SQL NO DEFINER SELECT * from d1.t43 root@localhost modified created latin1 latin1_swedish_ci latin1_swedish_ci SELECT * from d1.t43 +db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 aggregate +d1 sp4 PROCEDURE sp4 SQL CONTAINS_SQL NO DEFINER SELECT * from d1.t43 root@localhost modified created latin1 latin1_swedish_ci latin1_swedish_ci SELECT * from d1.t43 NONE USE db_storedproc; DROP DATABASE d1; CREATE DATABASE d1; USE d1; create table t44(a char(5), b char(10)); SELECT * from mysql.proc where specific_name = 'sp4'; -db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 +db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 aggregate USE db_storedproc; DROP DATABASE d1; @@ -16222,8 +16222,8 @@ CREATE PROCEDURE sp8 ( n char(20) ) sql security DEFINER comment 'initial' USE d2; alter procedure d1.sp8 sql security DEFINER comment 'updated'; SELECT * from mysql.proc where specific_name='sp8' and db='d1'; -db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 -d1 sp8 PROCEDURE sp8 SQL CONTAINS_SQL NO DEFINER n char(20) SELECT * from t1 where t1.f1 = n root@localhost modified created updated latin1 latin1_swedish_ci latin1_swedish_ci SELECT * from t1 where t1.f1 = n +db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 aggregate +d1 sp8 PROCEDURE sp8 SQL CONTAINS_SQL NO DEFINER n char(20) SELECT * from t1 where t1.f1 = n root@localhost modified created updated latin1 latin1_swedish_ci latin1_swedish_ci SELECT * from t1 where t1.f1 = n NONE Testcase 4.4.9: -------------------------------------------------------------------------------- @@ -16239,7 +16239,7 @@ END// USE d2; alter function d1.fn2 sql security DEFINER comment 'updated'; SELECT * from mysql.proc where specific_name='fn2' and db='d1'; -db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 +db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 aggregate d1 fn2 FUNCTION fn2 SQL CONTAINS_SQL NO DEFINER n int int(11) BEGIN declare a int; set a = 0.9 * n; @@ -16248,7 +16248,7 @@ END root@localhost modified created updated latin1 latin1_swedish_ci latin1_swe declare a int; set a = 0.9 * n; return a; -END +END NONE Testcase 4.4.10: -------------------------------------------------------------------------------- @@ -16258,7 +16258,7 @@ SELECT * from t1 where t1.f1 = n; USE d2; DROP PROCEDURE d1.sp9; SELECT * from mysql.proc where specific_name='sp9' and db='d1'; -db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 +db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 aggregate Testcase 4.4.11: -------------------------------------------------------------------------------- @@ -16272,7 +16272,7 @@ END// USE d2; DROP FUNCTION d1.fn3; SELECT * from mysql.proc where specific_name='fn3' and db='d1'; -db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 +db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 aggregate USE db_storedproc; DROP DATABASE d1; DROP DATABASE d2; diff --git a/mysql-test/suite/innodb/r/instant_alter_debug.result b/mysql-test/suite/innodb/r/instant_alter_debug.result index b1407aad1f3..99a263ae95f 100644 --- a/mysql-test/suite/innodb/r/instant_alter_debug.result +++ b/mysql-test/suite/innodb/r/instant_alter_debug.result @@ -35,7 +35,7 @@ ALTER TABLE t4 ADD COLUMN b INT; SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS LEFT JOIN t4 ON (NUMERIC_SCALE = pk); COUNT(*) -1734 +1735 SET DEBUG_SYNC='innodb_inplace_alter_table_enter SIGNAL enter WAIT_FOR delete'; ALTER TABLE t4 ADD COLUMN c INT; connect dml,localhost,root,,; diff --git a/mysql-test/suite/rpl/r/rpl_sp.result b/mysql-test/suite/rpl/r/rpl_sp.result index fbd81cb7146..486c02eef71 100644 --- a/mysql-test/suite/rpl/r/rpl_sp.result +++ b/mysql-test/suite/rpl/r/rpl_sp.result @@ -17,7 +17,7 @@ insert into t1 values (b); insert into t1 values (unix_timestamp()); end| select * from mysql.proc where name='foo' and db='mysqltest1'; -db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 +db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 aggregate mysqltest1 foo PROCEDURE foo SQL CONTAINS_SQL NO DEFINER begin declare b int; set b = 8; @@ -28,10 +28,10 @@ declare b int; set b = 8; insert into t1 values (b); insert into t1 values (unix_timestamp()); -end +end NONE connection slave; select * from mysql.proc where name='foo' and db='mysqltest1'; -db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 +db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 aggregate mysqltest1 foo PROCEDURE foo SQL CONTAINS_SQL NO DEFINER begin declare b int; set b = 8; @@ -42,7 +42,7 @@ declare b int; set b = 8; insert into t1 values (b); insert into t1 values (unix_timestamp()); -end +end NONE connection master; set timestamp=1000000000; call foo(); @@ -137,19 +137,19 @@ select * from t2; a 20 select * from mysql.proc where name="foo4" and db='mysqltest1'; -db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 +db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 aggregate mysqltest1 foo4 PROCEDURE foo4 SQL CONTAINS_SQL YES DEFINER begin insert into t2 values(20),(20); end root@localhost # # latin1 latin1_swedish_ci latin1_swedish_ci begin insert into t2 values(20),(20); -end +end NONE connection master; drop procedure foo4; select * from mysql.proc where name="foo4" and db='mysqltest1'; -db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 +db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 aggregate connection slave; select * from mysql.proc where name="foo4" and db='mysqltest1'; -db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 +db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 aggregate connection master; drop procedure foo; drop procedure foo2; @@ -235,22 +235,22 @@ select fn3(); fn3() 0 select * from mysql.proc where db='mysqltest1'; -db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 +db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 aggregate mysqltest1 fn1 FUNCTION fn1 SQL NO_SQL NO DEFINER int(11) begin return unix_timestamp(); end root@localhost # # latin1 latin1_swedish_ci latin1_swedish_ci begin return unix_timestamp(); -end +end NONE mysqltest1 fn2 FUNCTION fn2 SQL NO_SQL NO DEFINER int(11) begin return unix_timestamp(); end zedjzlcsjhd@localhost # # latin1 latin1_swedish_ci latin1_swedish_ci begin return unix_timestamp(); -end +end NONE mysqltest1 fn3 FUNCTION fn3 SQL READS_SQL_DATA NO DEFINER int(11) begin return 0; end root@localhost # # latin1 latin1_swedish_ci latin1_swedish_ci begin return 0; -end +end NONE select * from t1; a 1000000000 @@ -260,22 +260,22 @@ select * from t1; a 1000000000 select * from mysql.proc where db='mysqltest1'; -db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 +db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 aggregate mysqltest1 fn1 FUNCTION fn1 SQL NO_SQL NO DEFINER int(11) begin return unix_timestamp(); end root@localhost # # latin1 latin1_swedish_ci latin1_swedish_ci begin return unix_timestamp(); -end +end NONE mysqltest1 fn2 FUNCTION fn2 SQL NO_SQL NO DEFINER int(11) begin return unix_timestamp(); end zedjzlcsjhd@localhost # # latin1 latin1_swedish_ci latin1_swedish_ci begin return unix_timestamp(); -end +end NONE mysqltest1 fn3 FUNCTION fn3 SQL READS_SQL_DATA NO DEFINER int(11) begin return 0; end root@localhost # # latin1 latin1_swedish_ci latin1_swedish_ci begin return 0; -end +end NONE connection master; delete from t2; alter table t2 add unique (a); diff --git a/mysql-test/t/custom_aggregate_functions.test b/mysql-test/t/custom_aggregate_functions.test new file mode 100644 index 00000000000..20fcc35f39f --- /dev/null +++ b/mysql-test/t/custom_aggregate_functions.test @@ -0,0 +1,773 @@ +create table t2 (sal int(10)); +delimiter |; + +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| + +delimiter ;| + +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; +select * from t2; +drop table t2; +drop function f1; + +delimiter |; +--error ER_INVALID_AGGREGATE_FUNCTION +create aggregate function f1(x INT) returns INT +begin + insert into t1(sal) values (x); + return x; +end| + +--error ER_NOT_AGGREGATE_FUNCTION +create function f1(x INT) returns INT +begin + set x=5; + fetch group next row; +return x+1; +end | + +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 | +delimiter ;| + +select f1(1); +show create function f1; +--error ER_PARSE_ERROR +alter function f1 aggregate none; +show create function f1; +select f1(1); +drop function f1; + + +delimiter |; + + +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)| +# Since currently recursive functions are disallowed ER_SP_NO_RECURSION +# error will be returned, once we will allow them error about +# insufficient number of locked tables will be returned instead. +--error ER_SP_NO_RECURSION +select f2(2)| +--error ER_SP_NO_RECURSION +select f2(3)| +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| + + +delimiter ;| + +select f1(10); +select f1(sal) from t1; +select f1(sal) from t1 where 1=0; +drop function f1; +delimiter |; + + +#WITHOUT RETURN STATEMENT IN AGGREGATE FUNCTIONS +--error 1320 +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| + +#without handler +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| + +--error 1329 +select f1(sal) from t1| +drop function f1| + +#without loop +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| + +--error 1321 +select f1(sal) from t1| +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| + +--error 1321 +select f1(sal) from t1| +drop function f1| +drop table t1| + +delimiter ;| + +# primary indexing + +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); + +delimiter |; + +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| + +delimiter ;| + +--sorted_result +select id, f1(sal) from t1 where id>= 1 group by counter order by val; +--sorted_result +select id, f1(sal) from t1; +--sorted_result +select id, f1(sal) from t1 where id>= 1; +--sorted_result +select id, f1(sal) from t1 where id>= 1 group by counter; +--sorted_result +select id, f1(sal) from t1 where id>= 1 group by id; +--sorted_result +select id, f1(sal) from t1 where id>= 1 group by val; +--sorted_result +select id, f1(sal) from t1 where id>= 1 group by counter order by counter; +--sorted_result +select id, f1(sal) from t1 where id>= 1 group by counter order by val; +--sorted_result +select id, f1(sal) from t1 where id>= 1 group by counter order by id; +--sorted_result +select id, f1(sal) from t1 where id>= 1 group by val order by counter; +--sorted_result +select id, f1(sal) from t1 where id>= 1 group by val order by id; +--sorted_result +select id, f1(sal) from t1 where id>= 1 group by val order by val; +drop table t1; + +#unique index + +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); + +--sorted_result +select id, f1(sal) from t1; +--sorted_result +select id, f1(sal) from t1 where id>= 1; +--sorted_result +select id, f1(sal) from t1 where id>= 1 group by counter; +--sorted_result +select id, f1(sal) from t1 where id>= 1 group by id; +--sorted_result +select id, f1(sal) from t1 where id>= 1 group by val; +--sorted_result +select id, f1(sal) from t1 where id>= 1 group by counter order by counter; +--sorted_result +select id, f1(sal) from t1 where id>= 1 group by counter order by val; +--sorted_result +select id, f1(sal) from t1 where id>= 1 group by counter order by id; +--sorted_result +select id, f1(sal) from t1 where id>= 1 group by val order by counter; +--sorted_result +select id, f1(sal) from t1 where id>= 1 group by val order by id; +--sorted_result +select id, f1(sal) from t1 where id>= 1 group by val order by val; +drop table t1; + +# compound indexing +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); +--sorted_result +select id, f1(sal) from t1; +--sorted_result +select id, f1(sal) from t1 where id>= 1; +--sorted_result +select id, f1(sal) from t1 where id>= 1 group by counter; +--sorted_result +select id, f1(sal) from t1 where id>= 1 group by id; +--sorted_result +select id, f1(sal) from t1 where id>= 1 group by val; +--sorted_result +select id, f1(sal) from t1 where id>= 1 group by counter order by counter; +--sorted_result +select id, f1(sal) from t1 where id>= 1 group by counter order by val; +--sorted_result +select id, f1(sal) from t1 where id>= 1 group by counter order by id; +--sorted_result +select id, f1(sal) from t1 where id>= 1 group by val order by counter; +--sorted_result +select id, f1(sal) from t1 where id>= 1 group by val order by id; +--sorted_result +select id, f1(sal) from t1 where id>= 1 group by val order by val; +drop table t1; +drop function f1; + +# prepared statement with aggregate functions + +delimiter |; + +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| + +delimiter ;| + +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; +execute test using @param; +execute test using @param; +execute test using @param; +set @param= 1; +execute test using @param; +set @param= 3; +execute test using @param; +set @param= 4; +execute test using @param; +deallocate prepare test; + +prepare test from "select f1(sal) from t1 where id>= ?"; +set @param= 2; +execute test using @param; +execute test using @param; +execute test using @param; +execute test using @param; +set @param= 1; +execute test using @param; +set @param= 3; +execute test using @param; +set @param= 4; +execute test using @param; +set @param= 5; +execute test using @param; +deallocate prepare test; + +drop function f2; + +prepare test from "select f1(sal) from t1 where id>= ?"; +set @param= 2; +execute test using @param; +drop function f1; + +create function f1(x int) returns int + return -1; + +execute test using @param; + +drop function f1; + +delimiter |; + +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| + +delimiter ;| + +execute test using @param; + +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); +delimiter |; + +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)| + +delimiter ;| + +select f1(sal) from t1; + +# group by test + +--sorted_result +select f1(sal) from t1 where id>= 1 group by counter; + +# multiple fetch statements in the loop +--sorted_result +select f3(sal) from t1; + +# incorrect column type +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +select f2(val) from t1; + +#subquery +--sorted_result +select val, id, c from (select f1(sal) as c from t2) as t1, t2; + +#multiple calls to an aggregate function +--sorted_result +select f1(sal),f1(val), f1(id), f1(sal) from t2; + +#string type, also more than one areguments +--sorted_result +select f4(sal, val) from t1; + +#select f1((select sal from t2 where id= 1)) from t1; +--sorted_result +select c from (select f1(sal) as c from t2) as t1; + +# this fails as more than one row is returned +#select f1((select val from t2 where id > 1)) from t1; + +select f1((select val from t2 where 0 > 1)) from t1; +select f1((select val from t2 where id= 1)) from t1; + +select f5(sal) from t1; + +SELECT f1(sal)*f1(sal) FROM t1; + +--sorted_result +SELECT (SELECT f1(sal) FROM t1) FROM t2; +--sorted_result +select id, f1(sal) from t1; +--sorted_result +select id, f1(sal) from t1 where id>= 1; +--sorted_result +select f1(sal), f1(sal) from t1 where id>= 1 group by counter; +--sorted_result +select f1(sal), f1(sal) from t1 where id>= 1 group by id ; +--sorted_result +select f1(sal) from t1 where id>= 1 group by id ; +select f1(sal) from t1 where id>= 1 order by counter; +select f1(sal) from t1 where id>= 1 group by id order by counter; +select counter, id, f1(sal) from t1 where id>= 1 group by id order by counter; +select id, f1(sal) from t1 where id>= 1 group by id order by counter; +drop table t1; +drop table t2; +drop function f1; +drop function f2; +drop function f3; +drop function f4; +drop function f5; +drop function f6; + + +delimiter |; + +# aggregate AND function + +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| + +delimiter ;| + +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; +drop function f1; + +delimiter |; + +# aggregate AVG function + +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| + +delimiter ;| +select f1(sal) from t1 where id>= 1; +drop function f1; +delimiter |; + +# aggregate MAX function + +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| + +delimiter ;| +select f1(sal) from t1 where id>= 1; +drop function f1; +delimiter |; + +# aggregate MIN function + +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| + +delimiter ;| +select f1(sal) from t1 where id>= 1; +drop function f1; +delimiter |; + +# aggregate XOR function + +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| + +delimiter ;| +select f1(sal) from t1 where id>= 1; +drop function f1; +delimiter |; + +# aggregate SUM function + +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| + +delimiter ;| +select f1(sal) from t1 where id>= 1; +delimiter |; + + +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| + +delimiter ;| + +# no parameters +select f2() from t1; + +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); + +# null values +select f1(sal) from t2; + +# no tables +select f1(1); + +# aggregate function called from regular functions +create function f3() returns int +return (select f1(sal) from t1); +select f3(); + +create function f4() returns INT +return 1; + +# regular functions called from aggregate functions +delimiter |; +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| + +delimiter ;| +select f5() from t2; +delimiter |; + +# aggregate functions called from aggregate functions +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| + +delimiter ;| +select f6(sal) from t2; + +# GROUP BY AND ORDER BY +--sorted_result +select id, f1(sal) from t1 where id>= 1 group by id; +--sorted_result +select counter, f1(sal) from t1 where id>= 1 group by counter; +--sorted_result +select val, f1(sal) from t1 where id>= 1 group by val; +--sorted_result +select counter, f1(sal) from t1 where id>= 1 group by id order by counter; +--sorted_result +select counter, id, f1(sal), f1(sal) from t1 where id>= 1 group by id order by counter; +--sorted_result +select counter, id, f1(sal), sum(distinct sal) from t1 where id>= 1 group by id order by counter desc; + + +##### insert aggregate function value into a table ###### +create table t3 (i int); +INSERT INTO t3 (i) select f1(sal) from t1; +select * from t3; + +delimiter |; + +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| + +delimiter ;| +select f7(sal) from t1; + +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; diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test index 9c306de0e3f..dcd56c57650 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -867,7 +867,7 @@ drop table t1; use mysql; INSERT INTO `proc` VALUES ('test','','PROCEDURE','','SQL','CONTAINS_SQL', 'NO','DEFINER','','','BEGIN\r\n \r\nEND','root@%','2006-03-02 18:40:03', -'2006-03-02 18:40:03','','','utf8','utf8_general_ci','utf8_general_ci','n/a'); +'2006-03-02 18:40:03','','','utf8','utf8_general_ci','utf8_general_ci','n/a', 'NONE'); select routine_name from information_schema.routines where ROUTINE_SCHEMA='test'; delete from proc where name=''; use test; diff --git a/mysql-test/t/sp-destruct.test b/mysql-test/t/sp-destruct.test index 31da235d906..0cd80bd5427 100644 --- a/mysql-test/t/sp-destruct.test +++ b/mysql-test/t/sp-destruct.test @@ -10,7 +10,7 @@ -- source include/not_embedded.inc # Supress warnings written to the log file -call mtr.add_suppression("Column count of mysql.proc is wrong. Expected 20, found 19. The table is probably corrupted"); +call mtr.add_suppression("Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted"); call mtr.add_suppression("Stored routine .test...bug14233_[123].: invalid value in column mysql.proc"); # Backup proc table diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test index aa537d3596b..0e16948f438 100644 --- a/mysql-test/t/sp-error.test +++ b/mysql-test/t/sp-error.test @@ -1746,7 +1746,7 @@ drop procedure bug15091; drop function if exists bug16896; --enable_warnings ---error ER_PARSE_ERROR +--error ER_INVALID_AGGREGATE_FUNCTION create aggregate function bug16896() returns int return 1; # diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index 174419220e7..576512e78a4 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -6317,7 +6317,7 @@ set names utf8| drop database if exists това_е_дълго_име_за_база_данни_нали| --enable_warnings create database това_е_дълго_име_за_база_данни_нали| -INSERT INTO mysql.proc VALUES ('това_е_дълго_име_за_база_данни_нали','това_е_процедура_с_доста_дълго_име_нали_и_още_по_дълго','PROCEDURE','това_е_процедура_с_доста_дълго_име_нали_и_още_по_дълго','SQL','CONTAINS_SQL','NO','DEFINER','','','bad_body','root@localhost',now(), now(),'','', 'utf8', 'utf8_general_ci', 'utf8_general_ci', 'n/a')| +INSERT INTO mysql.proc VALUES ('това_е_дълго_име_за_база_данни_нали','това_е_процедура_с_доста_дълго_име_нали_и_още_по_дълго','PROCEDURE','това_е_процедура_с_доста_дълго_име_нали_и_още_по_дълго','SQL','CONTAINS_SQL','NO','DEFINER','','','bad_body','root@localhost',now(), now(),'','', 'utf8', 'utf8_general_ci', 'utf8_general_ci', 'n/a', 'NONE')| --error ER_SP_PROC_TABLE_CORRUPT call това_е_дълго_име_за_база_данни_нали.това_е_процедура_с_доста_дълго_име_нали_и_още_по_дълго()| drop database това_е_дълго_име_за_база_данни_нали| diff --git a/scripts/mysql_system_tables.sql b/scripts/mysql_system_tables.sql index 3b0255abdae..a4935361e18 100644 --- a/scripts/mysql_system_tables.sql +++ b/scripts/mysql_system_tables.sql @@ -80,7 +80,7 @@ CREATE TABLE IF NOT EXISTS time_zone_transition_type ( Time_zone_id int unsign CREATE TABLE IF NOT EXISTS time_zone_leap_second ( Transition_time bigint signed NOT NULL, Correction int signed NOT NULL, PRIMARY KEY TranTime (Transition_time) ) engine=MyISAM CHARACTER SET utf8 comment='Leap seconds information for time zones'; -CREATE TABLE IF NOT EXISTS proc (db char(64) collate utf8_bin DEFAULT '' NOT NULL, name char(64) DEFAULT '' NOT NULL, type enum('FUNCTION','PROCEDURE') NOT NULL, specific_name char(64) DEFAULT '' NOT NULL, language enum('SQL') DEFAULT 'SQL' NOT NULL, sql_data_access enum( 'CONTAINS_SQL', 'NO_SQL', 'READS_SQL_DATA', 'MODIFIES_SQL_DATA') DEFAULT 'CONTAINS_SQL' NOT NULL, is_deterministic enum('YES','NO') DEFAULT 'NO' NOT NULL, security_type enum('INVOKER','DEFINER') DEFAULT 'DEFINER' NOT NULL, param_list blob NOT NULL, returns longblob NOT NULL, body longblob NOT NULL, definer char(141) collate utf8_bin DEFAULT '' NOT NULL, created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', sql_mode set( 'REAL_AS_FLOAT', 'PIPES_AS_CONCAT', 'ANSI_QUOTES', 'IGNORE_SPACE', 'IGNORE_BAD_TABLE_OPTIONS', 'ONLY_FULL_GROUP_BY', 'NO_UNSIGNED_SUBTRACTION', 'NO_DIR_IN_CREATE', 'POSTGRESQL', 'ORACLE', 'MSSQL', 'DB2', 'MAXDB', 'NO_KEY_OPTIONS', 'NO_TABLE_OPTIONS', 'NO_FIELD_OPTIONS', 'MYSQL323', 'MYSQL40', 'ANSI', 'NO_AUTO_VALUE_ON_ZERO', 'NO_BACKSLASH_ESCAPES', 'STRICT_TRANS_TABLES', 'STRICT_ALL_TABLES', 'NO_ZERO_IN_DATE', 'NO_ZERO_DATE', 'INVALID_DATES', 'ERROR_FOR_DIVISION_BY_ZERO', 'TRADITIONAL', 'NO_AUTO_CREATE_USER', 'HIGH_NOT_PRECEDENCE', 'NO_ENGINE_SUBSTITUTION', 'PAD_CHAR_TO_FULL_LENGTH', 'EMPTY_STRING_IS_NULL') DEFAULT '' NOT NULL, comment text collate utf8_bin NOT NULL, character_set_client char(32) collate utf8_bin, collation_connection char(32) collate utf8_bin, db_collation char(32) collate utf8_bin, body_utf8 longblob, PRIMARY KEY (db,name,type)) engine=MyISAM character set utf8 comment='Stored Procedures'; +CREATE TABLE IF NOT EXISTS proc (db char(64) collate utf8_bin DEFAULT '' NOT NULL, name char(64) DEFAULT '' NOT NULL, type enum('FUNCTION','PROCEDURE') NOT NULL, specific_name char(64) DEFAULT '' NOT NULL, language enum('SQL') DEFAULT 'SQL' NOT NULL, sql_data_access enum( 'CONTAINS_SQL', 'NO_SQL', 'READS_SQL_DATA', 'MODIFIES_SQL_DATA') DEFAULT 'CONTAINS_SQL' NOT NULL, is_deterministic enum('YES','NO') DEFAULT 'NO' NOT NULL, security_type enum('INVOKER','DEFINER') DEFAULT 'DEFINER' NOT NULL, param_list blob NOT NULL, returns longblob NOT NULL, body longblob NOT NULL, definer char(141) collate utf8_bin DEFAULT '' NOT NULL, created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', sql_mode set( 'REAL_AS_FLOAT', 'PIPES_AS_CONCAT', 'ANSI_QUOTES', 'IGNORE_SPACE', 'IGNORE_BAD_TABLE_OPTIONS', 'ONLY_FULL_GROUP_BY', 'NO_UNSIGNED_SUBTRACTION', 'NO_DIR_IN_CREATE', 'POSTGRESQL', 'ORACLE', 'MSSQL', 'DB2', 'MAXDB', 'NO_KEY_OPTIONS', 'NO_TABLE_OPTIONS', 'NO_FIELD_OPTIONS', 'MYSQL323', 'MYSQL40', 'ANSI', 'NO_AUTO_VALUE_ON_ZERO', 'NO_BACKSLASH_ESCAPES', 'STRICT_TRANS_TABLES', 'STRICT_ALL_TABLES', 'NO_ZERO_IN_DATE', 'NO_ZERO_DATE', 'INVALID_DATES', 'ERROR_FOR_DIVISION_BY_ZERO', 'TRADITIONAL', 'NO_AUTO_CREATE_USER', 'HIGH_NOT_PRECEDENCE', 'NO_ENGINE_SUBSTITUTION', 'PAD_CHAR_TO_FULL_LENGTH', 'EMPTY_STRING_IS_NULL') DEFAULT '' NOT NULL, comment text collate utf8_bin NOT NULL, character_set_client char(32) collate utf8_bin, collation_connection char(32) collate utf8_bin, db_collation char(32) collate utf8_bin, body_utf8 longblob, aggregate enum('NONE', 'GROUP') DEFAULT 'NONE' NOT NULL, PRIMARY KEY (db,name,type)) engine=MyISAM character set utf8 comment='Stored Procedures'; CREATE TABLE IF NOT EXISTS procs_priv ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(80) binary DEFAULT '' NOT NULL, Routine_name char(64) COLLATE utf8_general_ci DEFAULT '' NOT NULL, Routine_type enum('FUNCTION','PROCEDURE') NOT NULL, Grantor char(141) DEFAULT '' NOT NULL, Proc_priv set('Execute','Alter Routine','Grant') COLLATE utf8_general_ci DEFAULT '' NOT NULL, Timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (Host,Db,User,Routine_name,Routine_type), KEY Grantor (Grantor) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Procedure privileges'; diff --git a/sql/item.cc b/sql/item.cc index c01a8ec29ad..4c2a91dcee7 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -2735,8 +2735,12 @@ Item_sp::Item_sp(THD *thd, Name_resolution_context *context_arg, context(context_arg), m_name(name_arg), m_sp(NULL), func_ctx(NULL), sp_result_field(NULL) { - dummy_table= (TABLE*) thd->calloc(sizeof(TABLE) + sizeof(TABLE_SHARE)); + dummy_table= (TABLE*) thd->calloc(sizeof(TABLE) + sizeof(TABLE_SHARE) + + sizeof(Query_arena)); dummy_table->s= (TABLE_SHARE*) (dummy_table + 1); + /* TODO(cvicentiu) Move this sp_query_arena in the class as a direct member. + Currently it can not be done due to header include dependencies. */ + sp_query_arena= (Query_arena *) (dummy_table->s + 1); memset(&sp_mem_root, 0, sizeof(sp_mem_root)); } @@ -2876,19 +2880,37 @@ Item_sp::execute_impl(THD *thd, Item **args, uint arg_count) */ thd->reset_sub_statement_state(&statement_state, SUB_STMT_FUNCTION); - init_sql_alloc(&sp_mem_root, MEM_ROOT_BLOCK_SIZE, 0, MYF(0)); - Query_arena call_arena(&sp_mem_root, Query_arena::STMT_INITIALIZED_FOR_SP); + /* + If this function is an aggregate function, we want to initialise the + mem_root only once per group. For a regular stored function, we will + initialise once for each call to execute_function. + */ + m_sp->agg_type(); + DBUG_ASSERT(m_sp->agg_type() == GROUP_AGGREGATE || + (m_sp->agg_type() == NOT_AGGREGATE && !func_ctx)); + if (!func_ctx) + { + init_sql_alloc(&sp_mem_root, MEM_ROOT_BLOCK_SIZE, 0, MYF(0)); + *sp_query_arena= Query_arena(&sp_mem_root, + Query_arena::STMT_INITIALIZED_FOR_SP); + } bool err_status= m_sp->execute_function(thd, args, arg_count, sp_result_field, &func_ctx, - &call_arena); - /* Free Items allocated during function execution. */ - delete func_ctx; - func_ctx= NULL; - call_arena.free_items(); - free_root(&sp_mem_root, MYF(0)); - memset(&sp_mem_root, 0, sizeof(sp_mem_root)); - + sp_query_arena); + /* + We free the function context when the function finished executing normally + (quit_func == TRUE) or the function has exited with an error. + */ + if (err_status || func_ctx->quit_func) + { + /* Free Items allocated during function execution. */ + delete func_ctx; + func_ctx= NULL; + sp_query_arena->free_items(); + free_root(&sp_mem_root, MYF(0)); + memset(&sp_mem_root, 0, sizeof(sp_mem_root)); + } thd->restore_sub_statement_state(&statement_state); thd->security_ctx= save_security_ctx; diff --git a/sql/item.h b/sql/item.h index cbbfac92bc1..fb7edfd3d1a 100644 --- a/sql/item.h +++ b/sql/item.h @@ -4485,6 +4485,7 @@ public: uchar result_buf[64]; sp_rcontext *func_ctx; MEM_ROOT sp_mem_root; + Query_arena *sp_query_arena; /* The result field of the stored function. diff --git a/sql/item_func.cc b/sql/item_func.cc index c0021d1a2be..a8b4a64856e 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -6441,6 +6441,36 @@ Item_func_sp::fix_fields(THD *thd, Item **ref) if (res) DBUG_RETURN(TRUE); + if (m_sp->agg_type() == GROUP_AGGREGATE) + { + List<Item> list; + list.empty(); + for (uint i=0; i < arg_count; i++) + list.push_back(*(args+i)); + + Item_sum_sp *item_sp; + Query_arena *arena, backup; + arena= thd->activate_stmt_arena_if_needed(&backup); + + if (arg_count) + item_sp= new (thd->mem_root) Item_sum_sp(thd, context, m_name, sp, list); + else + item_sp= new (thd->mem_root) Item_sum_sp(thd, context, m_name, sp); + + if (arena) + thd->restore_active_arena(arena, &backup); + if (!item_sp) + DBUG_RETURN(TRUE); + *ref= item_sp; + item_sp->name= name; + bool err= item_sp->fix_fields(thd, ref); + if (err) + DBUG_RETURN(TRUE); + + list.empty(); + DBUG_RETURN(FALSE); + } + res= Item_func::fix_fields(thd, ref); if (res) diff --git a/sql/item_sum.cc b/sql/item_sum.cc index 9654f84d2bf..aa61c19306d 100644 --- a/sql/item_sum.cc +++ b/sql/item_sum.cc @@ -30,6 +30,10 @@ #include "sql_priv.h" #include "sql_select.h" #include "uniques.h" +#include "sp_rcontext.h" +#include "sp.h" +#include "sql_parse.h" +#include "sp_head.h" /** Calculate the affordable RAM limit for structures like TREE or Unique @@ -1239,6 +1243,158 @@ Field *Item_sum_hybrid::create_tmp_field(bool group, TABLE *table) DBUG_RETURN(tmp_table_field_from_field_type(table)); } +/*********************************************************************** +** Item_sum_sp class +***********************************************************************/ + +Item_sum_sp::Item_sum_sp(THD *thd, Name_resolution_context *context_arg, + sp_name *name_arg, sp_head *sp, List<Item> &list) + :Item_sum(thd, list), Item_sp(thd, context_arg, name_arg) +{ + maybe_null= 1; + quick_group= 0; + m_sp= sp; +} + +Item_sum_sp::Item_sum_sp(THD *thd, Name_resolution_context *context_arg, + sp_name *name_arg, sp_head *sp) + :Item_sum(thd), Item_sp(thd, context_arg, name_arg) +{ + maybe_null= 1; + quick_group= 0; + m_sp= sp; +} + + +bool +Item_sum_sp::fix_fields(THD *thd, Item **ref) +{ + DBUG_ASSERT(fixed == 0); + if (init_sum_func_check(thd)) + return TRUE; + decimals= 0; + + m_sp= m_sp ? m_sp : sp_handler_function.sp_find_routine(thd, m_name, true); + + if (!m_sp) + { + my_missing_function_error(m_name->m_name, ErrConvDQName(m_name).ptr()); + context->process_error(thd); + return TRUE; + } + + if (init_result_field(thd, max_length, maybe_null, &null_value, &name)) + return TRUE; + + for (uint i= 0 ; i < arg_count ; i++) + { + if (args[i]->fix_fields(thd, args + i) || args[i]->check_cols(1)) + return TRUE; + set_if_bigger(decimals, args[i]->decimals); + m_with_subquery|= args[i]->with_subquery(); + with_window_func|= args[i]->with_window_func; + } + result_field= NULL; + max_length= float_length(decimals); + null_value= 1; + fix_length_and_dec(); + + if (check_sum_func(thd, ref)) + return TRUE; + + memcpy(orig_args, args, sizeof(Item *) * arg_count); + fixed= 1; + return FALSE; +} + +/** + Execute function to store value in result field. + This is called when we need the value to be returned for the function. + Here we send a signal in form of the server status that all rows have been + fetched and now we have to exit from the function with the return value. + @return Function returns error status. + @retval FALSE on success. + @retval TRUE if an error occurred. +*/ + +bool +Item_sum_sp::execute() +{ + THD *thd= current_thd; + bool res; + uint old_server_status= thd->server_status; + + /* We set server status so we can send a signal to exit from the + function with the return value. */ + + thd->server_status= SERVER_STATUS_LAST_ROW_SENT; + res= Item_sp::execute(thd, &null_value, args, arg_count); + thd->server_status= old_server_status; + return res; +} + +/** + Handles the aggregation of the values. + @note: See class description for more details on how and why this is done. + @return The error state. + @retval FALSE on success. + @retval TRUE if an error occurred. +*/ + +bool +Item_sum_sp::add() +{ + return execute_impl(current_thd, args, arg_count); +} + + +void +Item_sum_sp::clear() +{ + delete func_ctx; + func_ctx= NULL; + sp_query_arena->free_items(); + free_root(&sp_mem_root, MYF(0)); +} + +const Type_handler *Item_sum_sp::type_handler() const +{ + DBUG_ENTER("Item_sum_sp::type_handler"); + DBUG_PRINT("info", ("m_sp = %p", (void *) m_sp)); + DBUG_ASSERT(sp_result_field); + // This converts ENUM/SET to STRING + const Type_handler *handler= sp_result_field->type_handler(); + DBUG_RETURN(handler->type_handler_for_item_field()); +} + +void +Item_sum_sp::cleanup() +{ + Item_sp::cleanup(); + Item_sum::cleanup(); +} + +/** + Initialize local members with values from the Field interface. + @note called from Item::fix_fields. +*/ + +void +Item_sum_sp::fix_length_and_dec() +{ + DBUG_ENTER("Item_sum_sp::fix_length_and_dec"); + DBUG_ASSERT(sp_result_field); + Type_std_attributes::set(sp_result_field); + Item_sum::fix_length_and_dec(); + DBUG_VOID_RETURN; +} + +const char * +Item_sum_sp::func_name() const +{ + THD *thd= current_thd; + return Item_sp::func_name(thd); +} /*********************************************************************** ** reset and add of sum_func diff --git a/sql/item_sum.h b/sql/item_sum.h index d05fdbca5e1..fd6b20e2b81 100644 --- a/sql/item_sum.h +++ b/sql/item_sum.h @@ -355,7 +355,7 @@ public: ROW_NUMBER_FUNC, RANK_FUNC, DENSE_RANK_FUNC, PERCENT_RANK_FUNC, CUME_DIST_FUNC, NTILE_FUNC, FIRST_VALUE_FUNC, LAST_VALUE_FUNC, NTH_VALUE_FUNC, LEAD_FUNC, LAG_FUNC, PERCENTILE_CONT_FUNC, - PERCENTILE_DISC_FUNC + PERCENTILE_DISC_FUNC, SP_AGGREGATE_FUNC }; Item **ref_by; /* pointer to a ref to the object used to register it */ @@ -1224,6 +1224,132 @@ private: void set_bits_from_counters(); }; +class sp_head; +class sp_name; +class Query_arena; +struct st_sp_security_context; + +/* + Item_sum_sp handles STORED AGGREGATE FUNCTIONS + + Each Item_sum_sp represents a custom aggregate function. Inside the + function's body, we require at least one occurence of FETCH GROUP NEXT ROW + instruction. This cursor is what makes custom stored aggregates possible. + + During computation the function's add method is called. This in turn performs + an execution of the function. The function will execute from the current + function context (and instruction), if one exists, or from the start if not. + See Item_sp for more details. + + Upon encounter of FETCH GROUP NEXT ROW instruction, the function will pause + execution. We assume that the user has performed the necessary additions for + a row, between two encounters of FETCH GROUP NEXT ROW. + + Example: + create aggregate function f1(x INT) returns int + begin + declare continue handler for not found return s; + declare s int default 0 + loop + fetch group next row; + set s = s + x; + end loop; + end + + The function will always stop after an encounter of FETCH GROUP NEXT ROW, + except (!) on first encounter, as the value for the first row in the + group is already set in the argument x. This behaviour is done so when + a user writes a function, he should "logically" include FETCH GROUP NEXT ROW + before any "add" instructions in the stored function. This means however that + internally, the first occurence doesn't stop the function. See the + implementation of FETCH GROUP NEXT ROW for details as to how it happens. + + Either way, one should assume that after calling "Item_sum_sp::add()" that + the values for that particular row have been added to the aggregation. + + To produce values for val_xxx methods we need an extra syntactic construct. + We require a continue handler when "no more rows are available". val_xxx + methods force a function return by executing the function again, while + setting a server flag that no more rows have been found. This implies + that val_xxx methods should only be called once per group however. + + Example: + DECLARE CONTINUE HANDLER FOR NOT FOUND RETURN ret_val; +*/ +class Item_sum_sp :public Item_sum, + public Item_sp +{ + private: + bool execute(); + +public: + Item_sum_sp(THD *thd, Name_resolution_context *context_arg, sp_name *name, + sp_head *sp); + + Item_sum_sp(THD *thd, Name_resolution_context *context_arg, sp_name *name, + sp_head *sp, List<Item> &list); + + enum Sumfunctype sum_func () const + { + return SP_AGGREGATE_FUNC; + } + void fix_length_and_dec(); + bool fix_fields(THD *thd, Item **ref); + const char *func_name() const; + const Type_handler *type_handler() const; + bool add(); + + /* val_xx functions */ + longlong val_int() + { + if(execute()) + return 0; + return sp_result_field->val_int(); + } + + double val_real() + { + if(execute()) + return 0.0; + return sp_result_field->val_real(); + } + + my_decimal *val_decimal(my_decimal *dec_buf) + { + if(execute()) + return NULL; + return sp_result_field->val_decimal(dec_buf); + } + + String *val_str(String *str) + { + String buf; + char buff[20]; + buf.set(buff, 20, str->charset()); + buf.length(0); + if (execute()) + return NULL; + /* + result_field will set buf pointing to internal buffer + of the resul_field. Due to this it will change any time + when SP is executed. In order to prevent occasional + corruption of returned value, we make here a copy. + */ + sp_result_field->val_str(&buf); + str->copy(buf); + return str; + } + void reset_field(){DBUG_ASSERT(0);} + void update_field(){DBUG_ASSERT(0);} + void clear(); + void cleanup(); + inline Field *get_sp_result_field() + { + return sp_result_field; + } + Item *get_copy(THD *thd) + { return get_item_copy<Item_sum_sp>(thd, this); } +}; /* Items to get the value of a stored sum function */ diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt index 20610117d14..9082bc684f4 100644 --- a/sql/share/errmsg-utf8.txt +++ b/sql/share/errmsg-utf8.txt @@ -7803,3 +7803,7 @@ ER_ARGUMENT_OUT_OF_RANGE eng "Argument to the %s function does not belong to the range [0,1]" ER_WRONG_TYPE_OF_ARGUMENT eng "%s function only accepts arguments that can be converted to numerical types" +ER_NOT_AGGREGATE_FUNCTION + eng "Non-aggregate function contains aggregate specific instructions: (FETCH GROUP NEXT ROW)" +ER_INVALID_AGGREGATE_FUNCTION + eng "Aggregate specific instruction(FETCH GROUP NEXT ROW) missing from the aggregate function" diff --git a/sql/sp.cc b/sql/sp.cc index e02283d1f17..58dd8cfee3d 100644 --- a/sql/sp.cc +++ b/sql/sp.cc @@ -201,6 +201,11 @@ TABLE_FIELD_TYPE proc_table_fields[MYSQL_PROC_FIELD_COUNT] = { C_STRING_WITH_LEN("body_utf8") }, { C_STRING_WITH_LEN("longblob") }, { NULL, 0 } + }, + { + { C_STRING_WITH_LEN("aggregate") }, + { C_STRING_WITH_LEN("enum('NONE','GROUP')") }, + { NULL, 0 } } }; @@ -583,6 +588,22 @@ bool st_sp_chistics::read_from_mysql_proc_row(THD *thd, TABLE *table) return true; suid= str.str[0] == 'I' ? SP_IS_NOT_SUID : SP_IS_SUID; + if (table->field[MYSQL_PROC_FIELD_AGGREGATE]->val_str_nopad(thd->mem_root, + &str)) + return true; + + switch (str.str[0]) { + case 'N': + agg_type= NOT_AGGREGATE; + break; + case 'G': + agg_type= GROUP_AGGREGATE; + break; + default: + agg_type= DEFAULT_AGGREGATE; + } + + if (table->field[MYSQL_PROC_FIELD_COMMENT]->val_str_nopad(thd->mem_root, &comment)) return true; @@ -1183,6 +1204,13 @@ Sp_handler::sp_create_routine(THD *thd, const sp_head *sp) const table->field[MYSQL_PROC_FIELD_NAME]-> store(sp->m_name, system_charset_info); + if (sp->agg_type() != DEFAULT_AGGREGATE) + { + store_failed= store_failed || + table->field[MYSQL_PROC_FIELD_AGGREGATE]-> + store((longlong)sp->agg_type(),TRUE); + } + store_failed= store_failed || table->field[MYSQL_PROC_MYSQL_TYPE]-> store((longlong) type(), true); @@ -1494,6 +1522,9 @@ Sp_handler::sp_update_routine(THD *thd, const Database_qualified_name *name, if (chistics->comment.str) table->field[MYSQL_PROC_FIELD_COMMENT]->store(chistics->comment, system_charset_info); + if (chistics->agg_type != DEFAULT_AGGREGATE) + table->field[MYSQL_PROC_FIELD_AGGREGATE]-> + store((longlong)chistics->agg_type, TRUE); if ((ret= table->file->ha_update_row(table->record[1],table->record[0])) && ret != HA_ERR_RECORD_IS_THE_SAME) ret= SP_WRITE_ROW_FAILED; @@ -2238,11 +2269,12 @@ Sp_handler::show_create_sp(THD *thd, String *buf, sql_mode_t sql_mode) const { sql_mode_t old_sql_mode= thd->variables.sql_mode; + size_t agglen= (chistics.agg_type == GROUP_AGGREGATE)? 10 : 0; /* Make some room to begin with */ if (buf->alloc(100 + db.length + 1 + name.length + params.length + returns.length + - chistics.comment.length + 10 /* length of " DEFINER= "*/ + - USER_HOST_BUFF_SIZE)) + chistics.comment.length + 10 /* length of " DEFINER= "*/ + + agglen + USER_HOST_BUFF_SIZE)) return true; thd->variables.sql_mode= sql_mode; @@ -2250,6 +2282,8 @@ Sp_handler::show_create_sp(THD *thd, String *buf, if (ddl_options.or_replace()) buf->append(STRING_WITH_LEN("OR REPLACE ")); append_definer(thd, buf, &definer.user, &definer.host); + if (chistics.agg_type == GROUP_AGGREGATE) + buf->append(STRING_WITH_LEN("AGGREGATE ")); buf->append(type_lex_cstring()); buf->append(STRING_WITH_LEN(" ")); if (ddl_options.if_not_exists()) @@ -370,6 +370,7 @@ enum MYSQL_PROC_FIELD_COLLATION_CONNECTION, MYSQL_PROC_FIELD_DB_COLLATION, MYSQL_PROC_FIELD_BODY_UTF8, + MYSQL_PROC_FIELD_AGGREGATE, MYSQL_PROC_FIELD_COUNT }; diff --git a/sql/sp_head.cc b/sql/sp_head.cc index 62e198efb9e..a0f0fb271ef 100644 --- a/sql/sp_head.cc +++ b/sql/sp_head.cc @@ -999,6 +999,12 @@ sp_head::execute(THD *thd, bool merge_da_on_success) bool err_status= FALSE; uint ip= 0; sql_mode_t save_sql_mode; + + // TODO(cvicentiu) See if you can drop this bit. This is used to resume + // execution from where we left off. + if (m_chistics.agg_type == GROUP_AGGREGATE) + ip= thd->spcont->instr_ptr; + bool save_abort_on_warning; Query_arena *old_arena; /* per-instruction arena */ @@ -1176,6 +1182,7 @@ sp_head::execute(THD *thd, bool merge_da_on_success) #if defined(ENABLED_PROFILING) thd->profiling.discard_current_query(); #endif + thd->spcont->quit_func= TRUE; break; } @@ -1245,7 +1252,8 @@ sp_head::execute(THD *thd, bool merge_da_on_success) /* Reset sp_rcontext::end_partial_result_set flag. */ ctx->end_partial_result_set= FALSE; - } while (!err_status && !thd->killed && !thd->is_fatal_error); + } while (!err_status && !thd->killed && !thd->is_fatal_error && + !thd->spcont->pause_state); #if defined(ENABLED_PROFILING) thd->profiling.finish_current_query(); @@ -1261,9 +1269,14 @@ sp_head::execute(THD *thd, bool merge_da_on_success) thd->restore_active_arena(&execute_arena, &backup_arena); - thd->spcont->pop_all_cursors(); // To avoid memory leaks after an error + /* Only pop cursors when we're done with group aggregate running. */ + if (m_chistics.agg_type != GROUP_AGGREGATE || + (m_chistics.agg_type == GROUP_AGGREGATE && thd->spcont->quit_func)) + thd->spcont->pop_all_cursors(); // To avoid memory leaks after an error /* Restore all saved */ + if (m_chistics.agg_type == GROUP_AGGREGATE) + thd->spcont->instr_ptr= ip; thd->server_status= (thd->server_status & ~status_backup_mask) | old_server_status; old_packet.swap(thd->packet); DBUG_ASSERT(thd->change_list.is_empty()); @@ -1856,7 +1869,7 @@ sp_head::execute_function(THD *thd, Item **argp, uint argcount, } } - if (!err_status) + if (!err_status && thd->spcont->quit_func) { /* We need result only in function but not in trigger */ @@ -2482,7 +2495,6 @@ sp_head::set_chistics(const st_sp_chistics &chistics) m_chistics.comment.length); } - void sp_head::set_info(longlong created, longlong modified, const st_sp_chistics &chistics, sql_mode_t sql_mode) @@ -4217,6 +4229,35 @@ sp_instr_cfetch::print(String *str) } } +int +sp_instr_agg_cfetch::execute(THD *thd, uint *nextp) +{ + DBUG_ENTER("sp_instr_cfetch::execute"); + int res= 0; + if (!thd->spcont->instr_ptr) + { + *nextp= m_ip+1; + thd->spcont->instr_ptr= m_ip + 1; + } + else if (!thd->spcont->pause_state) + thd->spcont->pause_state= TRUE; + else + { + thd->spcont->pause_state= FALSE; + if (thd->server_status == SERVER_STATUS_LAST_ROW_SENT) + { + my_message(ER_SP_FETCH_NO_DATA, + ER_THD(thd, ER_SP_FETCH_NO_DATA), MYF(0)); + res= -1; + thd->spcont->quit_func= TRUE; + } + else + *nextp= m_ip + 1; + } + DBUG_RETURN(res); +} + + /* sp_instr_cursor_copy_struct class functions diff --git a/sql/sp_head.h b/sql/sp_head.h index 1a994bdf70d..7e477544958 100644 --- a/sql/sp_head.h +++ b/sql/sp_head.h @@ -164,7 +164,10 @@ public: /* Marks routines that have column type references: DECLARE a t1.a%TYPE; */ - HAS_COLUMN_TYPE_REFS= 8192 + HAS_COLUMN_TYPE_REFS= 8192, + /* Set if has FETCH GROUP NEXT ROW instr. Used to ensure that only + functions with AGGREGATE keyword use the instr. */ + HAS_AGGREGATE_INSTR= 16384 }; const Sp_handler *m_handler; @@ -197,6 +200,7 @@ public: enum_sp_suid_behaviour suid() const { return m_chistics.suid; } bool detistic() const { return m_chistics.detistic; } enum_sp_data_access daccess() const { return m_chistics.daccess; } + enum_sp_aggregate_type agg_type() const { return m_chistics.agg_type; } /** Is this routine being executed? */ @@ -720,6 +724,10 @@ public: const LEX_CSTRING &table); void set_chistics(const st_sp_chistics &chistics); + inline void set_chistics_agg_type(enum enum_sp_aggregate_type type) + { + m_chistics.agg_type= type; + } void set_info(longlong created, longlong modified, const st_sp_chistics &chistics, sql_mode_t sql_mode); @@ -1822,6 +1830,32 @@ private: }; // class sp_instr_cfetch : public sp_instr +/* +This class is created for the special fetch instruction +FETCH GROUP NEXT ROW, used in the user-defined aggregate +functions +*/ + +class sp_instr_agg_cfetch : public sp_instr +{ + sp_instr_agg_cfetch(const sp_instr_cfetch &); /**< Prevent use of these */ + void operator=(sp_instr_cfetch &); + +public: + + sp_instr_agg_cfetch(uint ip, sp_pcontext *ctx) + : sp_instr(ip, ctx){} + + virtual ~sp_instr_agg_cfetch() + {} + + virtual int execute(THD *thd, uint *nextp); + + virtual void print(String *str){}; +}; // class sp_instr_agg_cfetch : public sp_instr + + + class sp_instr_error : public sp_instr { diff --git a/sql/sp_rcontext.cc b/sql/sp_rcontext.cc index b6f9e733af1..740941937e8 100644 --- a/sql/sp_rcontext.cc +++ b/sql/sp_rcontext.cc @@ -40,6 +40,7 @@ sp_rcontext::sp_rcontext(const sp_head *owner, Field *return_value_fld, bool in_sub_stmt) :end_partial_result_set(false), + pause_state(false), quit_func(false), instr_ptr(0), m_sp(owner), m_root_parsing_ctx(root_parsing_ctx), m_var_table(NULL), diff --git a/sql/sp_rcontext.h b/sql/sp_rcontext.h index 9b70b1d4c6b..0999271ebde 100644 --- a/sql/sp_rcontext.h +++ b/sql/sp_rcontext.h @@ -178,6 +178,9 @@ public: /// (if one is found). Otherwise the client will hang due to a violation /// of the client/server protocol. bool end_partial_result_set; + bool pause_state; + bool quit_func; + uint instr_ptr; /// The stored program for which this runtime context is created. Used for /// checking if correct runtime context is used for variable handling. diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 2ec287e786b..069b75628e3 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -226,6 +226,13 @@ enum enum_sp_data_access SP_MODIFIES_SQL_DATA }; +enum enum_sp_aggregate_type +{ + DEFAULT_AGGREGATE= 0, + NOT_AGGREGATE, + GROUP_AGGREGATE +}; + const LEX_STRING sp_data_access_name[]= { { C_STRING_WITH_LEN("") }, @@ -1290,6 +1297,7 @@ struct st_sp_chistics enum enum_sp_suid_behaviour suid; bool detistic; enum enum_sp_data_access daccess; + enum enum_sp_aggregate_type agg_type; void init() { bzero(this, sizeof(*this)); } void set(const st_sp_chistics &other) { *this= other; } bool read_from_mysql_proc_row(THD *thd, TABLE *table); diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index b3632c339c4..f073d4173ad 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -1911,7 +1911,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize); opt_field_or_var_spec fields_or_vars opt_load_data_set_spec view_list_opt view_list view_select trigger_tail sp_tail sf_tail event_tail - udf_tail create_function_tail + udf_tail create_function_tail create_aggregate_function_tail install uninstall partition_entry binlog_base64_event normal_key_options normal_key_opts all_key_opt spatial_key_options fulltext_key_options normal_key_opt @@ -2638,8 +2638,16 @@ create: event_tail { } | create_or_replace definer FUNCTION_SYM - { Lex->create_info.set($1); } - sf_tail + { + Lex->create_info.set($1); + } + sf_tail_not_aggregate + { } + | create_or_replace definer AGGREGATE_SYM FUNCTION_SYM + { + Lex->create_info.set($1); + } + sf_tail_aggregate { } | create_or_replace no_definer FUNCTION_SYM { Lex->create_info.set($1); } @@ -2648,9 +2656,8 @@ create: | create_or_replace no_definer AGGREGATE_SYM FUNCTION_SYM { Lex->create_info.set($1); - Lex->udf.type= UDFTYPE_AGGREGATE; } - udf_tail + create_aggregate_function_tail { } | create_or_replace USER_SYM opt_if_not_exists clear_privileges grant_list opt_require_clause opt_resource_options @@ -2677,11 +2684,36 @@ create: { } ; +sf_tail_not_aggregate: + sf_tail + { + if (Lex->sphead->m_flags & sp_head::HAS_AGGREGATE_INSTR) + { + my_yyabort_error((ER_NOT_AGGREGATE_FUNCTION, MYF(0), "")); + } + Lex->sphead->set_chistics_agg_type(NOT_AGGREGATE); + } + +sf_tail_aggregate: + sf_tail + { + if (!(Lex->sphead->m_flags & sp_head::HAS_AGGREGATE_INSTR)) + { + my_yyabort_error((ER_INVALID_AGGREGATE_FUNCTION, MYF(0), "")); + } + Lex->sphead->set_chistics_agg_type(GROUP_AGGREGATE); + } + create_function_tail: - sf_tail { } + sf_tail_not_aggregate { } | udf_tail { Lex->udf.type= UDFTYPE_FUNCTION; } ; +create_aggregate_function_tail: + sf_tail_aggregate + { } + | udf_tail { Lex->udf.type= UDFTYPE_AGGREGATE; } + ; opt_sequence: /* empty */ { } | sequence_defs @@ -4011,7 +4043,19 @@ sp_proc_stmt_fetch_head: ; sp_proc_stmt_fetch: - sp_proc_stmt_fetch_head sp_fetch_list { } + sp_proc_stmt_fetch_head sp_fetch_list { } + | FETCH_SYM GROUP_SYM NEXT_SYM ROW_SYM + { + LEX *lex= Lex; + sp_head *sp= lex->sphead; + lex->sphead->m_flags|= sp_head::HAS_AGGREGATE_INSTR; + sp_instr_agg_cfetch *i= + new (thd->mem_root) sp_instr_agg_cfetch(sp->instructions(), + lex->spcont); + if (i == NULL || + sp->add_instr(i)) + MYSQL_YYABORT; + } ; sp_proc_stmt_close: |