diff options
author | Vicențiu Ciorbaru <vicentiu@mariadb.org> | 2017-12-03 21:44:05 +0200 |
---|---|---|
committer | Vicențiu Ciorbaru <vicentiu@mariadb.org> | 2017-12-03 21:44:05 +0200 |
commit | 42e8c6392ca6479e58fd43d1978b4f0cd33dff16 (patch) | |
tree | b1f182849fb501c5f39b2c1d3cbde05dd09d2be3 | |
parent | 32e60af5235657185bfa35ec82215ea6cee282af (diff) | |
download | mariadb-git-bb-10.3-vicentiu-rebased.tar.gz |
forgot to add test filebb-10.3-vicentiu-rebased
-rw-r--r-- | mysql-test/t/custom_aggregate_functions.test | 773 |
1 files changed, 773 insertions, 0 deletions
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; |