diff options
Diffstat (limited to 'mysql-test/r/sp.result')
-rw-r--r-- | mysql-test/r/sp.result | 75 |
1 files changed, 61 insertions, 14 deletions
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 4142fc0b796..02cca6b7284 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -997,9 +997,9 @@ select f5(1)| f5(1) 1 select f5(2)| -ERROR HY000: Recursive stored functions and triggers are not allowed. +ERROR HY000: Recursive stored functions and triggers are not allowed select f5(3)| -ERROR HY000: Recursive stored functions and triggers are not allowed. +ERROR HY000: Recursive stored functions and triggers are not allowed create function f6() returns int begin declare n int; @@ -1048,11 +1048,11 @@ ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function create function f1() returns int return (select sum(data) from t1) + (select sum(data) from v1)| select f1()| -ERROR HY000: Recursive stored functions and triggers are not allowed. +ERROR HY000: Recursive stored functions and triggers are not allowed select * from v1| -ERROR HY000: Recursive stored functions and triggers are not allowed. +ERROR HY000: Recursive stored functions and triggers are not allowed select * from v2| -ERROR HY000: Recursive stored functions and triggers are not allowed. +ERROR HY000: Recursive stored functions and triggers are not allowed drop function f1| create function f1() returns int return (select sum(data) from t1)| @@ -1140,9 +1140,12 @@ insert into t3 values (1), (2), (3); return (select count(*) from t3 as a, t3 as b); end| select f11()| -ERROR HY000: Can't reopen table: 'a' +f11() +9 select f11() from t1| -ERROR HY000: Can't reopen table: 'a' +f11() +9 +9 create function f12_1() returns int begin drop temporary table if exists t3; @@ -1156,6 +1159,7 @@ drop temporary table t3| select f12_1()| f12_1() 3 +drop temporary table t3| select f12_1() from t1 limit 1| f12_1() 3 @@ -4060,9 +4064,9 @@ select @@max_sp_recursion_depth| @@max_sp_recursion_depth 4 select bug10100f(3)| -ERROR HY000: Recursive stored functions and triggers are not allowed. +ERROR HY000: Recursive stored functions and triggers are not allowed select bug10100f(6)| -ERROR HY000: Recursive stored functions and triggers are not allowed. +ERROR HY000: Recursive stored functions and triggers are not allowed call bug10100t(5)| res 120 @@ -4131,7 +4135,7 @@ select @@max_sp_recursion_depth| @@max_sp_recursion_depth 0 select bug10100f(5)| -ERROR HY000: Recursive stored functions and triggers are not allowed. +ERROR HY000: Recursive stored functions and triggers are not allowed call bug10100t(5)| ERROR HY000: Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine bug10100p deallocate prepare stmt2| @@ -6041,12 +6045,12 @@ select bug20777(9223372036854775810) as '9223372036854775810 2**63+2'; 9223372036854775810 2**63+2 9223372036854775810 select bug20777(-9223372036854775808) as 'lower bounds signed bigint'; -ERROR 22003: BIGINT UNSIGNED value is out of range in '(f1@0 - 10)' +ERROR 22003: BIGINT UNSIGNED value is out of range in 'f1@0 - 10' select bug20777(9223372036854775807) as 'upper bounds signed bigint'; upper bounds signed bigint 9223372036854775807 select bug20777(0) as 'lower bounds unsigned bigint'; -ERROR 22003: BIGINT UNSIGNED value is out of range in '(f1@0 - 10)' +ERROR 22003: BIGINT UNSIGNED value is out of range in 'f1@0 - 10' select bug20777(18446744073709551615) as 'upper bounds unsigned bigint'; upper bounds unsigned bigint 18446744073709551615 @@ -6056,7 +6060,7 @@ upper bounds unsigned bigint + 1 Warnings: Warning 1264 Out of range value for column 'f1' at row 1 select bug20777(-1) as 'lower bounds unsigned bigint - 1'; -ERROR 22003: BIGINT UNSIGNED value is out of range in '(f1@0 - 10)' +ERROR 22003: BIGINT UNSIGNED value is out of range in 'f1@0 - 10' create table examplebug20777 as select 0 as 'i', bug20777(9223372036854775806) as '2**63-2', @@ -6417,6 +6421,8 @@ set x=-1; return x; end| create view v1 as select 1 as one, f1() as days; +connect bug29408, localhost, root,,*NO-ONE*; +connection bug29408; show create view test.v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `test`.`v1` AS select 1 AS `one`,`f1`() AS `days` latin1 latin1_swedish_ci @@ -6425,6 +6431,8 @@ where table_name='v1' and table_schema='test'; column_name one days +connection default; +disconnect bug29408; drop view v1; drop function f1; @@ -6929,7 +6937,7 @@ CREATE TEMPORARY TABLE t3 LIKE t1; CREATE PROCEDURE p1 () BEGIN SELECT f1 FROM t3 AS A WHERE A.f1 IN ( SELECT f1 FROM t3 ) ; END| CALL p1; -ERROR HY000: Can't reopen table: 'A' +f1 CREATE VIEW t3 AS SELECT f1 FROM t2 A WHERE A.f1 IN ( SELECT f1 FROM t2 ); DROP TABLE t3; CALL p1; @@ -8042,3 +8050,42 @@ CALL sp1(); drop user 'foo'@'%'; drop procedure sp1; #End of 10.1 tests +# +# MDEV-11081: CURSOR for query with GROUP BY +# +CREATE TABLE t1 (name VARCHAR(10), value INT); +INSERT INTO t1 VALUES ('b',1); +INSERT INTO t1 VALUES ('b',1); +INSERT INTO t1 VALUES ('c',1); +INSERT INTO t1 VALUES ('a',1); +INSERT INTO t1 VALUES ('a',1); +INSERT INTO t1 VALUES ('a',1); +CREATE PROCEDURE p1 () +BEGIN +DECLARE done INT DEFAULT FALSE; +DECLARE v_name VARCHAR(10); +DECLARE v_total INT; +DECLARE c CURSOR FOR +SELECT name, SUM(value) AS total FROM t1 GROUP BY name; +DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; +OPEN c; +read_loop: +LOOP +FETCH c INTO v_name, v_total; +IF done THEN +LEAVE read_loop; +END IF; +SELECT v_name, v_total; +END LOOP; +CLOSE c; +END; +| +CALL p1(); +v_name v_total +a 3 +v_name v_total +b 2 +v_name v_total +c 1 +DROP PROCEDURE p1; +DROP TABLE t1; |