diff options
Diffstat (limited to 'mysql-test/r/sp-error.result')
| -rw-r--r-- | mysql-test/r/sp-error.result | 2876 |
1 files changed, 0 insertions, 2876 deletions
diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result deleted file mode 100644 index fc43bdf17e9..00000000000 --- a/mysql-test/r/sp-error.result +++ /dev/null @@ -1,2876 +0,0 @@ -drop table if exists t1, t2; -SELECT * FROM mysql.proc INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/proc.txt'; -delete from mysql.proc; -create procedure syntaxerror(t int)| -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 '' at line 1 -create procedure syntaxerror(t int)| -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 '' at line 1 -create procedure syntaxerror(t int)| -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 '' at line 1 -drop table if exists t3| -create table t3 ( x int )| -insert into t3 values (2), (3)| -create procedure bad_into(out param int) -select x from t3 into param| -call bad_into(@x)| -ERROR 42000: Result consisted of more than one row -drop procedure bad_into| -drop table t3| -create procedure proc1() -set @x = 42| -create function func1() returns int -return 42| -create procedure foo() -create procedure bar() set @x=3| -ERROR 2F003: Can't create a PROCEDURE from within another stored routine -create procedure foo() -create function bar() returns double return 2.3| -ERROR 2F003: Can't create a FUNCTION from within another stored routine -create procedure proc1() -set @x = 42| -ERROR 42000: PROCEDURE proc1 already exists -create function func1() returns int -return 42| -ERROR 42000: FUNCTION func1 already exists -drop procedure proc1| -drop function func1| -alter procedure foo| -ERROR 42000: PROCEDURE test.foo does not exist -alter function foo| -ERROR 42000: FUNCTION test.foo does not exist -drop procedure foo| -ERROR 42000: PROCEDURE test.foo does not exist -drop function foo| -ERROR 42000: FUNCTION test.foo does not exist -call foo()| -ERROR 42000: PROCEDURE test.foo does not exist -drop procedure if exists foo| -Warnings: -Note 1305 PROCEDURE test.foo does not exist -show create procedure foo| -ERROR 42000: PROCEDURE foo does not exist -show create function foo| -ERROR 42000: FUNCTION foo does not exist -create procedure foo() -foo: loop -leave bar; -end loop| -ERROR 42000: LEAVE with no matching label: bar -create procedure foo() -foo: loop -iterate bar; -end loop| -ERROR 42000: ITERATE with no matching label: bar -create procedure foo() -foo: begin -iterate foo; -end| -ERROR 42000: ITERATE with no matching label: foo -create procedure foo() -foo: loop -foo: loop -set @x=2; -end loop foo; -end loop foo| -ERROR 42000: Redefining label foo -create procedure foo() -foo: loop -set @x=2; -end loop bar| -ERROR 42000: End-label bar without match -create procedure foo() -return 42| -ERROR 42000: RETURN is only allowed in a FUNCTION -create procedure p(x int) -set @x = x| -create function f(x int) returns int -return x+42| -call p()| -ERROR 42000: Incorrect number of arguments for PROCEDURE test.p; expected 1, got 0 -call p(1, 2)| -ERROR 42000: Incorrect number of arguments for PROCEDURE test.p; expected 1, got 2 -select f()| -ERROR 42000: Incorrect number of arguments for FUNCTION test.f; expected 1, got 0 -select f(1, 2)| -ERROR 42000: Incorrect number of arguments for FUNCTION test.f; expected 1, got 2 -drop procedure p| -drop function f| -create procedure p(val int, out res int) -begin -declare x int default 0; -declare continue handler for foo set x = 1; -insert into test.t1 values (val); -if (x) then -set res = 0; -else -set res = 1; -end if; -end| -ERROR 42000: Undefined CONDITION: foo -create procedure p(val int, out res int) -begin -declare x int default 0; -declare foo condition for 1146; -declare continue handler for bar set x = 1; -insert into test.t1 values (val); -if (x) then -set res = 0; -else -set res = 1; -end if; -end| -ERROR 42000: Undefined CONDITION: bar -create function f(val int) returns int -begin -declare x int; -set x = val+3; -end| -ERROR 42000: No RETURN found in FUNCTION test.f -create function f(val int) returns int -begin -declare x int; -set x = val+3; -if x < 4 then -return x; -end if; -end| -select f(10)| -ERROR 2F005: FUNCTION f ended without RETURN -drop function f| -create procedure p() -begin -declare c cursor for insert into test.t1 values ("foo", 42); -open c; -close c; -end| -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 'insert into test.t1 values ("foo", 42); -open c; -close c; -end' at line 3 -create procedure p() -begin -declare x int; -declare c cursor for select * into x from test.t limit 1; -open c; -close c; -end| -ERROR 42000: Cursor SELECT must not have INTO -create procedure p() -begin -declare c cursor for select * from test.t; -open cc; -close c; -end| -ERROR 42000: Undefined CURSOR: cc -drop table if exists t1| -create table t1 (val int)| -create procedure p() -begin -declare c cursor for select * from test.t1; -open c; -open c; -close c; -end| -call p()| -ERROR 24000: Cursor is already open -drop procedure p| -create procedure p() -begin -declare c cursor for select * from test.t1; -open c; -close c; -close c; -end| -call p()| -ERROR 24000: Cursor is not open -drop procedure p| -alter procedure bar3 sql security invoker| -ERROR 42000: PROCEDURE test.bar3 does not exist -drop table t1| -drop table if exists t1| -create table t1 (val int, x float)| -insert into t1 values (42, 3.1), (19, 1.2)| -create procedure p() -begin -declare x int; -declare c cursor for select * from t1; -open c; -fetch c into x, y; -close c; -end| -ERROR 42000: Undeclared variable: y -create procedure p() -begin -declare x int; -declare c cursor for select * from t1; -open c; -fetch c into x; -close c; -end| -call p()| -ERROR HY000: Incorrect number of FETCH variables -drop procedure p| -create procedure p() -begin -declare x int; -declare y float; -declare z int; -declare c cursor for select * from t1; -open c; -fetch c into x, y, z; -close c; -end| -call p()| -ERROR HY000: Incorrect number of FETCH variables -drop procedure p| -create procedure p(in x int, x char(10)) -begin -end| -ERROR 42000: Duplicate parameter: x -create function p(x int, x char(10)) -begin -end| -ERROR 42000: Duplicate parameter: x -create procedure p() -begin -declare x float; -declare x int; -end| -ERROR 42000: Duplicate variable: x -create procedure p() -begin -declare c condition for 1064; -declare c condition for 1065; -end| -ERROR 42000: Duplicate condition: c -create procedure p() -begin -declare c cursor for select * from t1; -declare c cursor for select field from t1; -end| -ERROR 42000: Duplicate cursor: c -create procedure u() -use sptmp| -ERROR 0A000: USE is not allowed in stored procedures -create procedure p() -begin -declare c cursor for select * from t1; -declare x int; -end| -ERROR 42000: Variable or condition declaration after cursor or handler declaration -create procedure p() -begin -declare x int; -declare continue handler for sqlstate '42S99' set x = 1; -declare foo condition for sqlstate '42S99'; -end| -ERROR 42000: Variable or condition declaration after cursor or handler declaration -create procedure p() -begin -declare x int; -declare continue handler for sqlstate '42S99' set x = 1; -declare c cursor for select * from t1; -end| -ERROR 42000: Cursor declaration after handler declaration -drop procedure if exists p| -create procedure p(in x int, inout y int, out z int) -begin -set y = x+y; -set z = x+y; -end| -set @tmp_x = 42| -set @tmp_y = 3| -set @tmp_z = 0| -call p(@tmp_x, @tmp_y, @tmp_z)| -select @tmp_x, @tmp_y, @tmp_z| -@tmp_x @tmp_y @tmp_z -42 45 87 -call p(42, 43, @tmp_z)| -ERROR 42000: OUT or INOUT argument 2 for routine test.p is not a variable or NEW pseudo-variable in BEFORE trigger -call p(42, @tmp_y, 43)| -ERROR 42000: OUT or INOUT argument 3 for routine test.p is not a variable or NEW pseudo-variable in BEFORE trigger -drop procedure p| -create procedure p() begin end| -lock table t1 read| -call p()| -unlock tables| -drop procedure p| -lock tables t1 read, mysql.proc write| -ERROR HY000: You can't combine write-locking of system tables with other tables or lock types -lock tables mysql.proc write, mysql.user write| -ERROR HY000: You can't combine write-locking of system tables with other tables or lock types -lock tables t1 read, mysql.proc read| -unlock tables| -lock tables mysql.proc write| -unlock tables| -drop function if exists f1| -create function f1(i int) returns int -begin -insert into t1 (val) values (i); -return 0; -end| -select val, f1(val) from t1| -ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger -select val, f1(val) from t1 as tab| -ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger -select * from t1| -val x -42 3.1 -19 1.2 -update t1 set val= f1(val)| -ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger -select * from t1| -val x -42 3.1 -19 1.2 -select f1(17)| -f1(17) -0 -select * from t1| -val x -42 3.1 -19 1.2 -17 NULL -delete from t1 where val= 17| -drop function f1| -create procedure bug1965() -begin -declare c cursor for select val from t1 order by valname; -open c; -close c; -end| -call bug1965()| -ERROR 42S22: Unknown column 'valname' in 'order clause' -drop procedure bug1965| -select 1 into a| -ERROR 42000: Undeclared variable: a -drop table if exists t3| -create table t3 (column_1_0 int)| -create procedure bug1653() -update t3 set column_1 = 0| -call bug1653()| -ERROR 42S22: Unknown column 'column_1' in 'field list' -drop table t3| -create table t3 (column_1 int)| -call bug1653()| -drop procedure bug1653| -drop table t3| -create procedure bug2259() -begin -declare v1 int; -declare c1 cursor for select s1 from t1; -fetch c1 into v1; -end| -call bug2259()| -ERROR 24000: Cursor is not open -drop procedure bug2259| -create procedure bug2272() -begin -declare v int; -update t1 set v = 42; -end| -insert into t1 values (666, 51.3)| -call bug2272()| -ERROR 42S22: Unknown column 'v' in 'field list' -truncate table t1| -drop procedure bug2272| -create procedure bug2329_1() -begin -declare v int; -insert into t1 (v) values (5); -end| -create procedure bug2329_2() -begin -declare v int; -replace t1 set v = 5; -end| -call bug2329_1()| -ERROR 42S22: Unknown column 'v' in 'field list' -call bug2329_2()| -ERROR 42S22: Unknown column 'v' in 'field list' -drop procedure bug2329_1| -drop procedure bug2329_2| -create function bug3287() returns int -begin -declare v int default null; -case -when v is not null then return 1; -end case; -return 2; -end| -select bug3287()| -ERROR 20000: Case not found for CASE statement -drop function bug3287| -create procedure bug3287(x int) -case x -when 0 then -insert into test.t1 values (x, 0.1); -when 1 then -insert into test.t1 values (x, 1.1); -end case| -call bug3287(2)| -ERROR 20000: Case not found for CASE statement -drop procedure bug3287| -drop table if exists t3| -create table t3 (s1 int, primary key (s1))| -insert into t3 values (5),(6)| -create procedure bug3279(out y int) -begin -declare x int default 0; -begin -declare exit handler for sqlexception set x = x+1; -insert into t3 values (5); -end; -if x < 2 then -set x = x+1; -insert into t3 values (6); -end if; -set y = x; -end| -set @x = 0| -call bug3279(@x)| -ERROR 23000: Duplicate entry '6' for key 'PRIMARY' -select @x| -@x -0 -drop procedure bug3279| -drop table t3| -create procedure nodb.bug3339() begin end| -ERROR 42000: Unknown database 'nodb' -create procedure bug2653_1(a int, out b int) -set b = aa| -create procedure bug2653_2(a int, out b int) -begin -if aa < 0 then -set b = - a; -else -set b = a; -end if; -end| -call bug2653_1(1, @b)| -ERROR 42S22: Unknown column 'aa' in 'field list' -call bug2653_2(2, @b)| -ERROR 42S22: Unknown column 'aa' in 'field list' -drop procedure bug2653_1| -drop procedure bug2653_2| -create procedure bug4344() drop procedure bug4344| -ERROR HY000: Can't drop or alter a PROCEDURE from within another stored routine -create procedure bug4344() drop function bug4344| -ERROR HY000: Can't drop or alter a FUNCTION from within another stored routine -drop procedure if exists bug3294| -create procedure bug3294() -begin -declare continue handler for sqlexception drop table t5; -drop table t5; -drop table t5; -end| -create table t5 (x int)| -call bug3294()| -ERROR 42S02: Unknown table 'test.t5' -drop procedure bug3294| -drop procedure if exists bug8776_1| -drop procedure if exists bug8776_2| -drop procedure if exists bug8776_3| -drop procedure if exists bug8776_4| -create procedure bug8776_1() -begin -declare continue handler for sqlstate '42S0200test' begin end; -begin end; -end| -ERROR 42000: Bad SQLSTATE: '42S0200test' -create procedure bug8776_2() -begin -declare continue handler for sqlstate '4200' begin end; -begin end; -end| -ERROR 42000: Bad SQLSTATE: '4200' -create procedure bug8776_3() -begin -declare continue handler for sqlstate '420000' begin end; -begin end; -end| -ERROR 42000: Bad SQLSTATE: '420000' -create procedure bug8776_4() -begin -declare continue handler for sqlstate '42x00' begin end; -begin end; -end| -ERROR 42000: Bad SQLSTATE: '42x00' -create procedure bug6600() -check table t1| -ERROR 0A000: CHECK is not allowed in stored procedures -create procedure bug6600() -lock table t1 read| -ERROR 0A000: LOCK is not allowed in stored procedures -create procedure bug6600() -unlock table t1| -ERROR 0A000: UNLOCK is not allowed in stored procedures -drop procedure if exists bug9566| -create procedure bug9566() -begin -select * from t1; -end| -lock table t1 read| -alter procedure bug9566 comment 'Some comment'| -ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction -unlock tables| -drop procedure bug9566| -drop procedure if exists bug7299| -create procedure bug7299() -begin -declare v int; -declare c cursor for select val from t1; -declare exit handler for sqlexception select 'Error!'; -open c; -fetch c into v; -end| -truncate table t1| -call bug7299()| -ERROR 02000: No data - zero rows fetched, selected, or processed -drop procedure bug7299| -create procedure bug9073() -begin -declare continue handler for sqlexception select 1; -declare continue handler for sqlexception select 2; -end| -ERROR 42000: Duplicate handler declared in the same block -create procedure bug9073() -begin -declare condname1 condition for 1234; -declare continue handler for condname1 select 1; -declare exit handler for condname1 select 2; -end| -ERROR 42000: Duplicate handler declared in the same block -create procedure bug9073() -begin -declare condname1 condition for sqlstate '42000'; -declare condname2 condition for sqlstate '42000'; -declare exit handler for condname1 select 1; -declare continue handler for condname2 select 2; -end| -ERROR 42000: Duplicate handler declared in the same block -create procedure bug9073() -begin -declare condname1 condition for sqlstate '42000'; -declare exit handler for condname1 select 1; -declare exit handler for sqlstate '42000' select 2; -end| -ERROR 42000: Duplicate handler declared in the same block -drop procedure if exists bug9073| -create procedure bug9073() -begin -declare condname1 condition for sqlstate '42000'; -declare continue handler for condname1 select 1; -begin -declare exit handler for sqlstate '42000' select 2; -begin -declare continue handler for sqlstate '42000' select 3; -end; -end; -end| -drop procedure bug9073| -create procedure bug7047() -alter procedure bug7047| -ERROR HY000: Can't drop or alter a PROCEDURE from within another stored routine -create function bug7047() returns int -begin -alter function bug7047; -return 0; -end| -ERROR HY000: Can't drop or alter a FUNCTION from within another stored routine -create function bug8408() returns int -begin -select * from t1; -return 0; -end| -ERROR 0A000: Not allowed to return a result set from a function -create function bug8408() returns int -begin -show warnings; -return 0; -end| -ERROR 0A000: Not allowed to return a result set from a function -create function bug8408(a int) returns int -begin -declare b int; -select b; -return b; -end| -ERROR 0A000: Not allowed to return a result set from a function -drop function if exists bug8408_f| -drop procedure if exists bug8408_p| -create function bug8408_f() returns int -begin -call bug8408_p(); -return 0; -end| -create procedure bug8408_p() -select * from t1| -call bug8408_p()| -val x -select bug8408_f()| -ERROR 0A000: Not allowed to return a result set from a function -drop procedure bug8408_p| -drop function bug8408_f| -create function bug8408() returns int -begin -declare n int default 0; -select count(*) into n from t1; -return n; -end| -insert into t1 value (2, 2.7), (3, 3.14), (7, 7.0)| -select *,bug8408() from t1| -val x bug8408() -2 2.7 3 -3 3.14 3 -7 7 3 -drop function bug8408| -truncate table t1| -drop procedure if exists bug10537| -create procedure bug10537() -load data local infile '/tmp/somefile' into table t1| -ERROR 0A000: LOAD DATA is not allowed in stored procedures -drop function if exists bug8409| -create function bug8409() -returns int -begin -flush tables; -return 5; -end| -ERROR 0A000: FLUSH is not allowed in stored function or trigger -create function bug8409() returns int begin reset query cache; -return 1; end| -ERROR 0A000: RESET is not allowed in stored function or trigger -create function bug8409() returns int begin reset master; -return 1; end| -ERROR 0A000: RESET is not allowed in stored function or trigger -create function bug8409() returns int begin reset slave; -return 1; end| -ERROR 0A000: RESET is not allowed in stored function or trigger -create function bug8409() returns int begin flush hosts; -return 1; end| -ERROR 0A000: FLUSH is not allowed in stored function or trigger -create function bug8409() returns int begin flush privileges; -return 1; end| -ERROR 0A000: FLUSH is not allowed in stored function or trigger -create function bug8409() returns int begin flush tables with read lock; -return 1; end| -ERROR 0A000: FLUSH is not allowed in stored function or trigger -create function bug8409() returns int begin flush tables; -return 1; end| -ERROR 0A000: FLUSH is not allowed in stored function or trigger -create function bug8409() returns int begin flush logs; -return 1; end| -ERROR 0A000: FLUSH is not allowed in stored function or trigger -create function bug8409() returns int begin flush status; -return 1; end| -ERROR 0A000: FLUSH is not allowed in stored function or trigger -create function bug8409() returns int begin flush slave; -return 1; end| -ERROR 0A000: FLUSH is not allowed in stored function or trigger -create function bug8409() returns int begin flush master; -return 1; end| -ERROR 0A000: FLUSH is not allowed in stored function or trigger -create function bug8409() returns int begin flush des_key_file; -return 1; end| -ERROR 0A000: FLUSH is not allowed in stored function or trigger -create function bug8409() returns int begin flush user_resources; -return 1; end| -ERROR 0A000: FLUSH is not allowed in stored function or trigger -create procedure bug9529_901234567890123456789012345678901234567890123456789012345() -begin -end| -ERROR 42000: Identifier name 'bug9529_901234567890123456789012345678901234567890123456789012345' is too long -drop procedure if exists bug17015_0123456789012345678901234567890123456789012345678901234| -create procedure bug17015_0123456789012345678901234567890123456789012345678901234() -begin -end| -show procedure status like 'bug17015%'| -Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation -test bug17015_0123456789012345678901234567890123456789012345678901234 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci -drop procedure bug17015_0123456789012345678901234567890123456789012345678901234| -drop procedure if exists bug10969| -create procedure bug10969() -begin -declare s1 int default 0; -select default(s1) from t30; -end| -ERROR 42000: Incorrect column name 's1' -create procedure bug10969() -begin -declare s1 int default 0; -select default(t30.s1) from t30; -end| -drop procedure bug10969| -drop table t1| -create table t1(f1 int); -create table t2(f1 int); -CREATE PROCEDURE SP001() -P1: BEGIN -DECLARE ENDTABLE INT DEFAULT 0; -DECLARE TEMP_NUM INT; -DECLARE TEMP_SUM INT; -DECLARE C1 CURSOR FOR SELECT F1 FROM t1; -DECLARE C2 CURSOR FOR SELECT F1 FROM t2; -DECLARE CONTINUE HANDLER FOR NOT FOUND SET ENDTABLE = 1; -SET ENDTABLE=0; -SET TEMP_SUM=0; -SET TEMP_NUM=0; -OPEN C1; -FETCH C1 INTO TEMP_NUM; -WHILE ENDTABLE = 0 DO -SET TEMP_SUM=TEMP_NUM+TEMP_SUM; -FETCH C1 INTO TEMP_NUM; -END WHILE; -SELECT TEMP_SUM; -CLOSE C1; -CLOSE C1; -SELECT 'end of proc'; -END P1| -call SP001(); -TEMP_SUM -0 -ERROR 24000: Cursor is not open -drop procedure SP001; -drop table t1, t2; -drop function if exists bug11394| -drop function if exists bug11394_1| -drop function if exists bug11394_2| -drop procedure if exists bug11394| -create function bug11394(i int) returns int -begin -if i <= 0 then -return 0; -else -return (i in (100, 200, bug11394(i-1), 400)); -end if; -end| -select bug11394(2)| -ERROR HY000: Recursive stored functions and triggers are not allowed -drop function bug11394| -create function bug11394_1(i int) returns int -begin -if i <= 0 then -return 0; -else -return (select bug11394_1(i-1)); -end if; -end| -select bug11394_1(2)| -ERROR HY000: Recursive stored functions and triggers are not allowed -drop function bug11394_1| -create function bug11394_2(i int) returns int return i| -select bug11394_2(bug11394_2(10))| -bug11394_2(bug11394_2(10)) -10 -drop function bug11394_2| -create procedure bug11394(i int, j int) -begin -if i > 0 then -call bug11394(i - 1,(select 1)); -end if; -end| -call bug11394(2, 1)| -ERROR HY000: Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine bug11394 -set @@max_sp_recursion_depth=10| -call bug11394(2, 1)| -set @@max_sp_recursion_depth=default| -drop procedure bug11394| -CREATE PROCEDURE BUG_12490() HELP CONTENTS; -ERROR 0A000: HELP is not allowed in stored procedures -CREATE FUNCTION BUG_12490() RETURNS INT HELP CONTENTS; -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 'HELP CONTENTS' at line 1 -CREATE TABLE t_bug_12490(a int); -CREATE TRIGGER BUG_12490 BEFORE UPDATE ON t_bug_12490 FOR EACH ROW HELP CONTENTS; -ERROR 0A000: HELP is not allowed in stored procedures -DROP TABLE t_bug_12490; -drop function if exists bug11834_1; -drop function if exists bug11834_2; -create function bug11834_1() returns int return 10; -create function bug11834_2() returns int return bug11834_1(); -prepare stmt from "select bug11834_2()"; -execute stmt; -bug11834_2() -10 -execute stmt; -bug11834_2() -10 -drop function bug11834_1; -execute stmt; -ERROR 42000: FUNCTION test.bug11834_1 does not exist -deallocate prepare stmt; -drop function bug11834_2; -DROP FUNCTION IF EXISTS bug12953| -CREATE FUNCTION bug12953() RETURNS INT -BEGIN -OPTIMIZE TABLE t1; -RETURN 1; -END| -ERROR 0A000: Not allowed to return a result set from a function -DROP FUNCTION IF EXISTS bug12995| -CREATE FUNCTION bug12995() RETURNS INT -BEGIN -HANDLER t1 OPEN; -RETURN 1; -END| -ERROR 0A000: HANDLER is not allowed in stored procedures -CREATE FUNCTION bug12995() RETURNS INT -BEGIN -HANDLER t1 READ FIRST; -RETURN 1; -END| -ERROR 0A000: HANDLER is not allowed in stored procedures -CREATE FUNCTION bug12995() RETURNS INT -BEGIN -HANDLER t1 CLOSE; -RETURN 1; -END| -ERROR 0A000: HANDLER is not allowed in stored procedures -SELECT bug12995()| -ERROR 42000: FUNCTION test.bug12995 does not exist -drop procedure if exists bug12712; -drop function if exists bug12712; -create procedure bug12712() -set session autocommit = 0; -select @@autocommit; -@@autocommit -1 -set @au = @@autocommit; -call bug12712(); -select @@autocommit; -@@autocommit -0 -set session autocommit = @au; -create function bug12712() -returns int -begin -call bug12712(); -return 0; -end| -set @x = bug12712()| -ERROR HY000: Not allowed to set autocommit from a stored function or trigger -drop procedure bug12712| -drop function bug12712| -create function bug12712() -returns int -begin -set session autocommit = 0; -return 0; -end| -ERROR HY000: Not allowed to set autocommit from a stored function or trigger -create function bug12712() -returns int -begin -set @@autocommit = 0; -return 0; -end| -ERROR HY000: Not allowed to set autocommit from a stored function or trigger -create function bug12712() -returns int -begin -set local autocommit = 0; -return 0; -end| -ERROR HY000: Not allowed to set autocommit from a stored function or trigger -create trigger bug12712 -before insert on t1 for each row set session autocommit = 0; -ERROR HY000: Not allowed to set autocommit from a stored function or trigger -drop procedure if exists bug13510_1| -drop procedure if exists bug13510_2| -drop procedure if exists bug13510_3| -drop procedure if exists bug13510_4| -create procedure bug13510_1() -begin -declare password varchar(10); -set password = 'foo1'; -select password; -end| -ERROR 42000: Variable 'password' must be quoted with `...`, or renamed -set names='foo2'| -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 '' at line 1 -create procedure bug13510_2() -begin -declare names varchar(10); -set names = 'foo2'; -select names; -end| -ERROR 42000: Variable 'names' must be quoted with `...`, or renamed -create procedure bug13510_3() -begin -declare password varchar(10); -set `password` = 'foo3'; -select password; -end| -create procedure bug13510_4() -begin -declare names varchar(10); -set `names` = 'foo4'; -select names; -end| -call bug13510_3()| -password -foo3 -call bug13510_4()| -names -foo4 -drop procedure bug13510_3| -drop procedure bug13510_4| -drop function if exists bug_13627_f| -CREATE TABLE t1 (a int)| -CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN DROP TRIGGER test1; END | -ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger -CREATE FUNCTION bug_13627_f() returns int BEGIN DROP TRIGGER test1; return 1; END | -ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger -CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create table t2 (a int); END | -ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger -CREATE FUNCTION bug_13627_f() returns int BEGIN create table t2 (a int); return 1; END | -ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger -CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create index t1_i on t1 (a); END | -ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger -CREATE FUNCTION bug_13627_f() returns int BEGIN create index t1_i on t1 (a); return 1; END | -ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger -CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN alter table t1 add column b int; END | -ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger -CREATE FUNCTION bug_13627_f() returns int BEGIN alter table t1 add column b int; return 1; END | -ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger -CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN rename table t1 to t2; END | -ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger -CREATE FUNCTION bug_13627_f() returns int BEGIN rename table t1 to t2; return 1; END | -ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger -CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN truncate table t1; END | -ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger -CREATE FUNCTION bug_13627_f() returns int BEGIN truncate table t1; return 1; END | -ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger -CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop table t1; END | -ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger -CREATE FUNCTION bug_13627_f() returns int BEGIN drop table t1; return 1; END | -ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger -CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop index t1_i on t1; END | -ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger -CREATE FUNCTION bug_13627_f() returns int BEGIN drop index t1_i on t1; return 1; END | -ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger -CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN unlock tables; END | -ERROR 0A000: UNLOCK is not allowed in stored procedures -CREATE FUNCTION bug_13627_f() returns int BEGIN unlock tables; return 1; END | -ERROR 0A000: UNLOCK is not allowed in stored procedures -CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN LOCK TABLE t1 READ; END | -ERROR 0A000: LOCK is not allowed in stored procedures -CREATE FUNCTION bug_13627_f() returns int BEGIN LOCK TABLE t1 READ; return 1; END | -ERROR 0A000: LOCK is not allowed in stored procedures -CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create database mysqltest; END | -ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger -CREATE FUNCTION bug_13627_f() returns int BEGIN create database mysqltest; return 1; END | -ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger -CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop database mysqltest; END | -ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger -CREATE FUNCTION bug_13627_f() returns int BEGIN drop database mysqltest; return 1; END | -ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger -CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create user 'mysqltest_1'; END | -ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger -CREATE FUNCTION bug_13627_f() returns int BEGIN create user 'mysqltest_1'; return 1; END | -ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger -CREATE TRIGGER bug21975 BEFORE INSERT ON t1 FOR EACH ROW BEGIN grant select on t1 to 'mysqltest_1'; END | -ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger -CREATE FUNCTION bug21975() returns int BEGIN grant select on t1 to 'mysqltest_1'; return 1; END | -ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger -CREATE TRIGGER bug21975 BEFORE INSERT ON t1 FOR EACH ROW BEGIN revoke select on t1 from 'mysqltest_1'; END | -ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger -CREATE FUNCTION bug21975() returns int BEGIN revoke select on t1 from 'mysqltest_1'; return 1; END | -ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger -CREATE TRIGGER bug21975 BEFORE INSERT ON t1 FOR EACH ROW BEGIN revoke all privileges on *.* from 'mysqltest_1'; END | -ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger -CREATE FUNCTION bug21975() returns int BEGIN revoke all privileges on *.* from 'mysqltest_1'; return 1; END | -ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger -CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop user 'mysqltest_1'; END | -ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger -CREATE FUNCTION bug_13627_f() returns int BEGIN drop user 'mysqltest_1'; return 1; END | -ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger -CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN rename user 'mysqltest_2' to 'mysqltest_1'; END | -ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger -CREATE FUNCTION bug_13627_f() returns int BEGIN rename user 'mysqltest_2' to 'mysqltest_1'; return 1; END | -ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger -CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create view v1 as select 1; END | -ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger -CREATE FUNCTION bug_13627_f() returns int BEGIN create view v1 as select 1; return 1; END | -ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger -CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN alter view v1 as select 1; END | -ERROR 0A000: ALTER VIEW is not allowed in stored procedures -CREATE FUNCTION bug_13627_f() returns int BEGIN alter view v1 as select 1; return 1; END | -ERROR 0A000: ALTER VIEW is not allowed in stored procedures -CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop view v1; END | -ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger -CREATE FUNCTION bug_13627_f() returns int BEGIN drop view v1; return 1; END | -ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger -CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create trigger tr2 before insert on t1 for each row do select 1; END | -ERROR 2F003: Can't create a TRIGGER from within another stored routine -CREATE FUNCTION bug_13627_f() returns int BEGIN create trigger tr2 before insert on t1 for each row do select 1; return 1; END | -ERROR 2F003: Can't create a TRIGGER from within another stored routine -CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop function bug_13627_f; END | -ERROR HY000: Can't drop or alter a FUNCTION from within another stored routine -CREATE FUNCTION bug_13627_f() returns int BEGIN drop function bug_13627_f; return 1; END | -ERROR HY000: Can't drop or alter a FUNCTION from within another stored routine -CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create function f2 () returns int return 1; END | -ERROR 2F003: Can't create a FUNCTION from within another stored routine -CREATE FUNCTION bug_13627_f() returns int BEGIN create function f2 () returns int return 1; return 1; END | -ERROR 2F003: Can't create a FUNCTION from within another stored routine -CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW -BEGIN -CREATE TEMPORARY TABLE t2 (a int); -DROP TEMPORARY TABLE t2; -END | -CREATE FUNCTION bug_13627_f() returns int -BEGIN -CREATE TEMPORARY TABLE t2 (a int); -DROP TEMPORARY TABLE t2; -return 1; -END | -drop table t1| -drop function bug_13627_f| -drop function if exists bug12329; -Warnings: -Note 1305 FUNCTION test.bug12329 does not exist -create table t1 as select 1 a; -create table t2 as select 1 a; -create function bug12329() returns int return (select a from t1); -prepare stmt1 from 'select bug12329()'; -execute stmt1; -bug12329() -1 -drop function bug12329; -create function bug12329() returns int return (select a+100 from t2); -select bug12329(); -bug12329() -101 -execute stmt1; -bug12329() -101 -deallocate prepare stmt1; -drop function bug12329; -drop table t1, t2; -create database mysqltest1; -use mysqltest1; -drop database mysqltest1; -create function f1() returns int return 1; -ERROR 3D000: No database selected -create procedure p1(out param1 int) -begin -select count(*) into param1 from t3; -end| -ERROR 3D000: No database selected -use test; -DROP PROCEDURE IF EXISTS bug13037_p1; -DROP PROCEDURE IF EXISTS bug13037_p2; -DROP PROCEDURE IF EXISTS bug13037_p3; -CREATE PROCEDURE bug13037_p1() -BEGIN -IF bug13037_foo THEN -SELECT 1; -END IF; -END| -CREATE PROCEDURE bug13037_p2() -BEGIN -SET @bug13037_foo = bug13037_bar; -END| -CREATE PROCEDURE bug13037_p3() -BEGIN -SELECT bug13037_foo; -END| - -CALL bug13037_p1(); -ERROR 42S22: Unknown column 'bug13037_foo' in 'field list' -CALL bug13037_p2(); -ERROR 42S22: Unknown column 'bug13037_bar' in 'field list' -CALL bug13037_p3(); -ERROR 42S22: Unknown column 'bug13037_foo' in 'field list' -CALL bug13037_p1(); -ERROR 42S22: Unknown column 'bug13037_foo' in 'field list' -CALL bug13037_p2(); -ERROR 42S22: Unknown column 'bug13037_bar' in 'field list' -CALL bug13037_p3(); -ERROR 42S22: Unknown column 'bug13037_foo' in 'field list' -DROP PROCEDURE bug13037_p1; -DROP PROCEDURE bug13037_p2; -DROP PROCEDURE bug13037_p3; -create database mysqltest1; -create database mysqltest2; -use mysqltest1; -drop database mysqltest1; -create procedure mysqltest2.p1() select version(); -create procedure p2() select version(); -ERROR 3D000: No database selected -use mysqltest2; -show procedure status; -Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation -mysqltest2 p1 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci -drop database mysqltest2; -use test; -DROP FUNCTION IF EXISTS bug13012| -CREATE FUNCTION bug13012() RETURNS INT -BEGIN -REPAIR TABLE t1; -RETURN 1; -END| -ERROR 0A000: Not allowed to return a result set from a function -create table t1 (a int)| -CREATE PROCEDURE bug13012_1() REPAIR TABLE t1| -CREATE FUNCTION bug13012_2() RETURNS INT -BEGIN -CALL bug13012_1(); -RETURN 1; -END| -SELECT bug13012_2()| -ERROR 0A000: Not allowed to return a result set from a function -drop table t1| -drop procedure bug13012_1| -drop function bug13012_2| -drop function if exists bug11555_1; -drop function if exists bug11555_2; -drop view if exists v1, v2, v3, v4; -create function bug11555_1() returns int return (select max(i) from t1); -create function bug11555_2() returns int return bug11555_1(); -create view v1 as select bug11555_1(); -drop view v1; -create view v2 as select bug11555_2(); -drop view v2; -create table t1 (i int); -create view v1 as select bug11555_1(); -create view v2 as select bug11555_2(); -create view v3 as select * from v1; -drop table t1; -select * from v1; -ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -select * from v2; -ERROR HY000: View 'test.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -select * from v3; -ERROR HY000: View 'test.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -create view v4 as select * from v1; -drop view v1, v2, v3, v4; -drop function bug11555_1; -drop function bug11555_2; -create table t1 (i int); -create table t2 (i int); -create trigger t1_ai after insert on t1 for each row insert into t2 values (new.i); -create view v1 as select * from t1; -drop table t2; -insert into v1 values (1); -ERROR 42S02: Table 'test.t2' doesn't exist -drop trigger t1_ai; -create function bug11555_1() returns int return (select max(i) from t2); -create trigger t1_ai after insert on t1 for each row set @a:=bug11555_1(); -insert into v1 values (2); -ERROR 42S02: Table 'test.t2' doesn't exist -drop function bug11555_1; -drop table t1; -drop view v1; -drop procedure if exists ` bug15658`; -create procedure ``() select 1; -ERROR 42000: Incorrect routine name '' -create procedure ` `() select 1; -ERROR 42000: Incorrect routine name ' ' -create procedure `bug15658 `() select 1; -ERROR 42000: Incorrect routine name 'bug15658 ' -create procedure ``.bug15658() select 1; -ERROR 42000: Incorrect database name '' -create procedure `x `.bug15658() select 1; -ERROR 42000: Incorrect database name 'x ' -create procedure ` bug15658`() select 1; -call ` bug15658`(); -1 -1 -show procedure status; -Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation -test bug15658 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci -drop procedure ` bug15658`; -drop function if exists bug14270; -drop table if exists t1; -create table t1 (s1 int primary key); -create function bug14270() returns int -begin -load index into cache t1; -return 1; -end| -ERROR 0A000: Not allowed to return a result set from a function -create function bug14270() returns int -begin -cache index t1 key (`primary`) in keycache1; -return 1; -end| -ERROR 0A000: Not allowed to return a result set from a function -drop table t1; -drop procedure if exists bug15091; -create procedure bug15091() -begin -declare selectstr varchar(6000) default ' '; -declare conditionstr varchar(5000) default ''; -set selectstr = concat(selectstr, -' and ', -c.operatorid, -'in (',conditionstr, ')'); -end| -call bug15091(); -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 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 -END; -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 'IF NOT EXISTS PROCEDURE bug14702() -BEGIN -END' at line 1 -CREATE PROCEDURE IF NOT EXISTS bug14702() -BEGIN -END; -DROP PROCEDURE IF EXISTS bug14702; -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 (i INT); -CREATE PROCEDURE bug20953() CREATE VIEW v AS SELECT 1 INTO @a; -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 'INTO @a' at line 1 -CREATE PROCEDURE bug20953() CREATE VIEW v AS SELECT 1 INTO DUMPFILE "file"; -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 'INTO DUMPFILE "file"' at line 1 -CREATE PROCEDURE bug20953() CREATE VIEW v AS SELECT 1 INTO OUTFILE "file"; -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 'INTO OUTFILE "file"' at line 1 -CREATE PROCEDURE bug20953() -CREATE VIEW v AS SELECT i FROM t1 PROCEDURE ANALYSE(); -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 'PROCEDURE ANALYSE()' at line 2 -CREATE PROCEDURE bug20953() CREATE VIEW v AS SELECT 1 FROM (SELECT 1) AS d1 into @w; -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 'into @w' at line 1 -CREATE PROCEDURE bug20953(i INT) CREATE VIEW v AS SELECT i; -ERROR HY000: View's SELECT contains a variable or parameter -CREATE PROCEDURE bug20953() -BEGIN -DECLARE i INT; -CREATE VIEW v AS SELECT i; -END | -ERROR HY000: View's SELECT contains a variable or parameter -PREPARE stmt FROM "CREATE VIEW v AS SELECT ?"; -ERROR HY000: View's SELECT contains a variable or parameter -DROP TABLE t1; -drop tables if exists t1; -drop procedure if exists bug24491; -create table t1 (id int primary key auto_increment, value varchar(10)); -insert into t1 (id, value) values (1, 'FIRST'), (2, 'SECOND'), (3, 'THIRD'); -create procedure bug24491() -insert into t1 (id, value) select * from (select 4 as i, 'FOURTH' as v) as y on duplicate key update v = 'DUP'; -call bug24491(); -ERROR 42S22: Unknown column 'v' in 'field list' -call bug24491(); -ERROR 42S22: Unknown column 'v' in 'field list' -drop procedure bug24491; -create procedure bug24491() -insert into t1 (id, value) select * from (select 4 as id, 'FOURTH' as value) as y on duplicate key update y.value = 'DUP'; -call bug24491(); -ERROR 42S22: Unknown column 'y.value' in 'field list' -call bug24491(); -ERROR 42S22: Unknown column 'y.value' in 'field list' -drop procedure bug24491; -drop tables t1; -DROP FUNCTION IF EXISTS bug18914_f1; -DROP FUNCTION IF EXISTS bug18914_f2; -DROP PROCEDURE IF EXISTS bug18914_p1; -DROP PROCEDURE IF EXISTS bug18914_p2; -DROP TABLE IF EXISTS t1, t2; -CREATE TABLE t1 (i INT); -CREATE PROCEDURE bug18914_p1() CREATE TABLE t2 (i INT); -CREATE PROCEDURE bug18914_p2() DROP TABLE IF EXISTS no_such_table; -CREATE FUNCTION bug18914_f1() RETURNS INT -BEGIN -CALL bug18914_p1(); -RETURN 1; -END | -CREATE FUNCTION bug18914_f2() RETURNS INT -BEGIN -CALL bug18914_p2(); -RETURN 1; -END | -CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW -CALL bug18914_p1(); -INSERT INTO t1 VALUES (1); -ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger -SELECT bug18914_f1(); -ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger -SELECT bug18914_f2(); -ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger -SELECT * FROM t2; -ERROR 42S02: Table 'test.t2' doesn't exist -DROP FUNCTION bug18914_f1; -DROP FUNCTION bug18914_f2; -DROP PROCEDURE bug18914_p1; -DROP PROCEDURE bug18914_p2; -DROP TABLE t1; -drop table if exists bogus_table_20713; -drop function if exists func_20713_a; -drop function if exists func_20713_b; -create table bogus_table_20713( id int(10) not null primary key); -insert into bogus_table_20713 values (1), (2), (3); -create function func_20713_a() returns int(11) -begin -declare id int; -declare continue handler for sqlexception set id=null; -set @in_func := 1; -set id = (select id from bogus_table_20713 where id = 3); -set @in_func := 2; -return id; -end// -create function func_20713_b() returns int(11) -begin -declare id int; -declare continue handler for sqlstate value '42S02' set id=null; -set @in_func := 1; -set id = (select id from bogus_table_20713 where id = 3); -set @in_func := 2; -return id; -end// -set @in_func := 0; -select func_20713_a(); -func_20713_a() -NULL -select @in_func; -@in_func -2 -set @in_func := 0; -select func_20713_b(); -func_20713_b() -NULL -select @in_func; -@in_func -2 -drop table bogus_table_20713; -set @in_func := 0; -select func_20713_a(); -func_20713_a() -NULL -select @in_func; -@in_func -2 -set @in_func := 0; -select func_20713_b(); -func_20713_b() -NULL -select @in_func; -@in_func -2 -drop function if exists func_20713_a; -drop function if exists func_20713_b; -drop table if exists table_25345_a; -drop table if exists table_25345_b; -drop procedure if exists proc_25345; -drop function if exists func_25345; -drop function if exists func_25345_b; -create table table_25345_a (a int); -create table table_25345_b (b int); -create procedure proc_25345() -begin -declare c1 cursor for select a from table_25345_a; -declare c2 cursor for select b from table_25345_b; -select 1 as result; -end || -create function func_25345() returns int(11) -begin -call proc_25345(); -return 1; -end || -create function func_25345_b() returns int(11) -begin -declare c1 cursor for select a from table_25345_a; -declare c2 cursor for select b from table_25345_b; -return 1; -end || -call proc_25345(); -result -1 -select func_25345(); -ERROR 0A000: Not allowed to return a result set from a function -select func_25345_b(); -func_25345_b() -1 -drop table table_25345_a; -call proc_25345(); -result -1 -select func_25345(); -ERROR 0A000: Not allowed to return a result set from a function -select func_25345_b(); -func_25345_b() -1 -drop table table_25345_b; -drop procedure proc_25345; -drop function func_25345; -drop function func_25345_b; -End of 5.0 tests -drop function if exists bug16164; -create function bug16164() returns int -begin -show authors; -return 42; -end| -ERROR 0A000: Not allowed to return a result set from a function -drop function if exists bug20701; -create function bug20701() returns varchar(25) binary return "test"; -drop function bug20701; -create function bug20701() returns varchar(25) return "test"; -drop function bug20701; -create procedure proc_26503_error_1() -begin -retry: -repeat -begin -declare continue handler for sqlexception -begin -iterate retry; -end -select "do something"; -end -until true end repeat retry; -end// -ERROR 42000: ITERATE with no matching label: retry -create procedure proc_26503_error_2() -begin -retry: -repeat -begin -declare continue handler for sqlexception -iterate retry; -select "do something"; -end -until true end repeat retry; -end// -ERROR 42000: ITERATE with no matching label: retry -create procedure proc_26503_error_3() -begin -retry: -repeat -begin -declare continue handler for sqlexception -begin -leave retry; -end -select "do something"; -end -until true end repeat retry; -end// -ERROR 42000: LEAVE with no matching label: retry -create procedure proc_26503_error_4() -begin -retry: -repeat -begin -declare continue handler for sqlexception -leave retry; -select "do something"; -end -until true end repeat retry; -end// -ERROR 42000: LEAVE with no matching label: retry -drop procedure if exists proc_28360; -drop function if exists func_28360; -CREATE PROCEDURE proc_28360() -BEGIN -ALTER DATABASE `#mysql50#upgrade-me` UPGRADE DATA DIRECTORY NAME; -END// -ERROR HY000: Can't drop or alter a DATABASE from within another stored routine -CREATE FUNCTION func_28360() RETURNS int -BEGIN -ALTER DATABASE `#mysql50#upgrade-me` UPGRADE DATA DIRECTORY NAME; -RETURN 0; -END// -ERROR HY000: Can't drop or alter a DATABASE from within another stored routine -DROP PROCEDURE IF EXISTS p1; -CREATE PROCEDURE p1() -BEGIN -DECLARE c char(100); -DECLARE cur1 CURSOR FOR SHOW TABLES; -OPEN cur1; -FETCH cur1 INTO c; -select c; -CLOSE cur1; -END| -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 'SHOW TABLES; -OPEN cur1; -FETCH cur1 INTO c; -select c; -CLOSE cur1; -END' at line 4 -DROP DATABASE IF EXISTS mysqltest; -CREATE DATABASE mysqltest; -USE mysqltest; -DROP DATABASE mysqltest; -SELECT inexistent(), 1 + ,; -ERROR 42000: FUNCTION inexistent does not exist -SELECT inexistent(); -ERROR 42000: FUNCTION inexistent does not exist -SELECT .inexistent(); -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 '()' at line 1 -SELECT ..inexistent(); -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 '..inexistent()' at line 1 -USE test; -create function f1() returns int -begin -set @test = 1, password = password('foo'); -return 1; -end| -ERROR HY000: Not allowed to set autocommit from a stored function or trigger -create trigger t1 -before insert on t2 for each row set password = password('foo');| -ERROR HY000: Not allowed to set autocommit from a stored function or trigger -drop function if exists f1; -drop function if exists f2; -drop table if exists t1, t2; -create function f1() returns int -begin -drop temporary table t1; -return 1; -end| -create temporary table t1 as select f1(); -ERROR 42S02: Unknown table 'test.t1' -create function f2() returns int -begin -create temporary table t2 as select f1(); -return 1; -end| -create temporary table t1 as select f2(); -ERROR 42S02: Unknown table 'test.t1' -drop function f1; -drop function f2; -create function f1() returns int -begin -drop temporary table t2,t1; -return 1; -end| -create function f2() returns int -begin -create temporary table t2 as select f1(); -return 1; -end| -create temporary table t1 as select f2(); -ERROR 42S02: Unknown table 'test.t2,test.t1' -drop function f1; -drop function f2; -create temporary table t2(a int); -select * from t2; -a -create function f2() returns int -begin -drop temporary table t2; -return 1; -end| -select f2(); -f2() -1 -drop function f2; -drop table t2; -ERROR 42S02: Unknown table 'test.t2' -End of 5.1 tests -drop procedure if exists proc_33983_a; -drop procedure if exists proc_33983_b; -drop procedure if exists proc_33983_c; -drop procedure if exists proc_33983_d; -create procedure proc_33983_a() -begin -label1: -begin -label2: -begin -select 1; -end label1; -end; -end| -ERROR 42000: End-label label1 without match -create procedure proc_33983_b() -begin -label1: -repeat -label2: -repeat -select 1; -until FALSE end repeat label1; -until FALSE end repeat; -end| -ERROR 42000: End-label label1 without match -create procedure proc_33983_c() -begin -label1: -while TRUE do -label2: -while TRUE do -select 1; -end while label1; -end while; -end| -ERROR 42000: End-label label1 without match -create procedure proc_33983_d() -begin -label1: -loop -label2: -loop -select 1; -end loop label1; -end loop; -end| -ERROR 42000: End-label label1 without match -CREATE TABLE t1 (a INT)| -INSERT INTO t1 VALUES (1),(2)| -CREATE PROCEDURE p1(a INT) BEGIN END| -CALL p1((SELECT * FROM t1))| -ERROR 21000: Subquery returns more than 1 row -DROP PROCEDURE IF EXISTS p1| -DROP TABLE t1| -drop procedure if exists p1; -create procedure p1() -begin -create table t1 (a int) engine=MyISAM; -drop table t1; -end| -call p1(); -call p1(); -drop procedure p1; -drop procedure if exists proc_8759; -create procedure proc_8759() -begin -declare should_be_illegal condition for sqlstate '00000'; -declare continue handler for should_be_illegal set @x=0; -end$$ -ERROR 42000: Bad SQLSTATE: '00000' -create procedure proc_8759() -begin -declare continue handler for sqlstate '00000' set @x=0; -end$$ -ERROR 42000: Bad SQLSTATE: '00000' -drop procedure if exists proc_36510; -create procedure proc_36510() -begin -declare should_be_illegal condition for sqlstate '00123'; -declare continue handler for should_be_illegal set @x=0; -end$$ -ERROR 42000: Bad SQLSTATE: '00123' -create procedure proc_36510() -begin -declare continue handler for sqlstate '00123' set @x=0; -end$$ -ERROR 42000: Bad SQLSTATE: '00123' -create procedure proc_36510() -begin -declare should_be_illegal condition for 0; -declare continue handler for should_be_illegal set @x=0; -end$$ -ERROR HY000: Incorrect CONDITION value: '0' -create procedure proc_36510() -begin -declare continue handler for 0 set @x=0; -end$$ -ERROR HY000: Incorrect CONDITION value: '0' -drop procedure if exists p1; -set @old_recursion_depth = @@max_sp_recursion_depth; -set @@max_sp_recursion_depth = 255; -create procedure p1(a int) -begin -declare continue handler for 1436 -- ER_STACK_OVERRUN_NEED_MORE -select 'exception'; -call p1(a+1); -end| -call p1(1); -set @@max_sp_recursion_depth = @old_recursion_depth; -drop procedure p1; -LOAD DATA INFILE '../../tmp/proc.txt' INTO TABLE mysql.proc; -CREATE TABLE t1 (a INT, b INT); -INSERT INTO t1 VALUES (1,1), (2,2); -SELECT MAX (a) FROM t1 WHERE b = 999999; -ERROR 42000: FUNCTION test.MAX does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual -SELECT AVG (a) FROM t1 WHERE b = 999999; -AVG (a) -NULL -SELECT non_existent (a) FROM t1 WHERE b = 999999; -ERROR 42000: FUNCTION test.non_existent does not exist -DROP TABLE t1; -CREATE TABLE t1 ( f2 INTEGER, f3 INTEGER ); -INSERT INTO t1 VALUES ( 1, 1 ); -CREATE FUNCTION func_1 () RETURNS INTEGER -BEGIN -INSERT INTO t1 SELECT * FROM t1 ; -RETURN 1 ; -END| -INSERT INTO t1 SELECT * FROM (SELECT 2 AS f1, 2 AS f2) AS A WHERE func_1() = 5; -ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger -DROP FUNCTION func_1; -DROP TABLE t1; -# -# Bug #47788: Crash in TABLE_LIST::hide_view_error on UPDATE + VIEW + -# SP + MERGE + ALTER -# -CREATE TABLE t1 (pk INT, b INT, KEY (b)); -CREATE ALGORITHM = TEMPTABLE VIEW v1 AS SELECT * FROM t1; -CREATE PROCEDURE p1 (a int) UPDATE IGNORE v1 SET b = a; -CALL p1(5); -ERROR HY000: The target table v1 of the UPDATE is not updatable -ALTER TABLE t1 CHANGE COLUMN b b2 INT; -CALL p1(7); -ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -DROP PROCEDURE p1; -DROP VIEW v1; -DROP TABLE t1; -# -# Bug#12428824 - PARSER STACK OVERFLOW AND CRASH IN SP_ADD_USED_ROUTINE -# WITH OBSCURE QUERY -# -SELECT very_long_fn_name_1111111111111111111111111111111111111111111111111111111111111111111111111222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222225555555555555555555555555577777777777777777777777777777777777777777777777777777777777777777777777788888888999999999999999999999(); -ERROR 42000: Identifier name 'very_long_fn_name_1111111111111111111111111111111111111111111111111111111111111111111111111222222222' is too long -CALL very_long_pr_name_1111111111111111111111111111111111111111111111111111111111111111111111111222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222225555555555555555555555555577777777777777777777777777777777777777777777777777777777777777777777777788888888999999999999999999999(); -ERROR 42000: Identifier name 'very_long_pr_name_1111111111111111111111111111111111111111111111111111111111111111111111111222222222' is too long -SELECT very_long_db_name_1111111111111111111111111111111111111111111111111111111111111111111111111222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222225555555555555555555555555577777777777777777777777777777777777777777777777777777777777777777777777788888888999999999999999999999.simple_func(); -ERROR 42000: Incorrect database name 'very_long_db_name_1111111111111111111111111111111111111111111111111111111111111111111111111222222222' -CALL very_long_db_name_1111111111111111111111111111111111111111111111111111111111111111111111111222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222225555555555555555555555555577777777777777777777777777777777777777777777777777777777777777777777777788888888999999999999999999999.simple_proc(); -ERROR 42000: Incorrect database name 'very_long_db_name_1111111111111111111111111111111111111111111111111111111111111111111111111222222222' -SELECT db_name.very_long_fn_name_111111111111111111111111111111111111111111111111111111111111111111111111122222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222999999999999999999999(); -ERROR 42000: Identifier name 'very_long_fn_name_1111111111111111111111111111111111111111111111111111111111111111111111111222222222' is too long -CALL db_name.very_long_pr_name_111111111111111111111111111111111111111111111111111111111111111111111111122222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222999999999999999999999(); -ERROR 42000: Identifier name 'very_long_pr_name_1111111111111111111111111111111111111111111111111111111111111111111111111222222222' is too long -End of 5.1 tests -# -# Bug#23032: Handlers declared in a SP do not handle warnings generated in sub-SP -# - -# - Case 1 - -DROP PROCEDURE IF EXISTS p1; -DROP PROCEDURE IF EXISTS p2; -DROP PROCEDURE IF EXISTS p3; -DROP PROCEDURE IF EXISTS p4; -DROP PROCEDURE IF EXISTS p5; -DROP PROCEDURE IF EXISTS p6; -CREATE PROCEDURE p1() -BEGIN -SELECT CAST('10 ' as unsigned integer); -SELECT 1; -CALL p2(); -END| -CREATE PROCEDURE p2() -BEGIN -SELECT CAST('10 ' as unsigned integer); -END| -CALL p1(); -CAST('10 ' as unsigned integer) -10 -1 -1 -CAST('10 ' as unsigned integer) -10 -Warnings: -Note 1292 Truncated incorrect INTEGER value: '10 ' -DROP PROCEDURE p1; -DROP PROCEDURE p2; - -# - Case 2 - -CREATE PROCEDURE p1() -BEGIN -DECLARE c INT DEFAULT 0; -DECLARE CONTINUE HANDLER FOR SQLSTATE '22007' SET c = c + 1; -CALL p2(); -CALL p3(); -CALL p4(); -SELECT c; -SELECT @@warning_count; -SHOW WARNINGS; -END| -CREATE PROCEDURE p2() -BEGIN -SELECT CAST('10 ' as unsigned integer); -END| -CREATE PROCEDURE p3() -BEGIN -SELECT CAST('10 ' as unsigned integer); -SELECT 1; -END| -CREATE PROCEDURE p4() -BEGIN -SELECT CAST('10 ' as unsigned integer); -CALL p2(); -END| -CREATE PROCEDURE p5() -BEGIN -SELECT CAST('10 ' as unsigned integer); -SHOW WARNINGS; -END| -CREATE PROCEDURE P6() -BEGIN -DECLARE c INT DEFAULT 0; -DECLARE CONTINUE HANDLER FOR SQLSTATE '22007' SET c = c + 1; -CALL p5(); -SELECT c; -END| -CALL p1(); -CAST('10 ' as unsigned integer) -10 -CAST('10 ' as unsigned integer) -10 -1 -1 -CAST('10 ' as unsigned integer) -10 -CAST('10 ' as unsigned integer) -10 -c -3 -@@warning_count -0 -Level Code Message -CALL p6(); -CAST('10 ' as unsigned integer) -10 -Level Code Message -Note 1292 Truncated incorrect INTEGER value: '10 ' -c -1 -DROP PROCEDURE p1; -DROP PROCEDURE p2; -DROP PROCEDURE p3; -DROP PROCEDURE p4; -DROP PROCEDURE p5; -DROP PROCEDURE p6; - -# - Case 3: check that "Exception trumps No Data". - -DROP TABLE IF EXISTS t1; -CREATE TABLE t1(a INT); -INSERT INTO t1 VALUES (1), (2), (3); -CREATE PROCEDURE p1() -BEGIN -DECLARE c CURSOR FOR SELECT a FROM t1; -OPEN c; -BEGIN -DECLARE v1 INT; -DECLARE v2 INT; -DECLARE EXIT HANDLER FOR SQLEXCEPTION -SELECT "Error caught (expected)"; -DECLARE EXIT HANDLER FOR NOT FOUND -SELECT "End of Result Set found!"; -WHILE TRUE DO -FETCH c INTO v1, v2; -END WHILE; -END; -CLOSE c; -SELECT a INTO @foo FROM t1 LIMIT 1; # Clear warning stack -END| -CALL p1(); -Error caught (expected) -Error caught (expected) -DROP PROCEDURE p1; -DROP TABLE t1; -# -# Bug#36185: Incorrect precedence for warning and exception handlers -# -DROP TABLE IF EXISTS t1; -DROP PROCEDURE IF EXISTS p1; -CREATE TABLE t1 (a INT, b INT NOT NULL); -CREATE PROCEDURE p1() -BEGIN -DECLARE CONTINUE HANDLER FOR SQLWARNING SELECT 'warning'; -DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SELECT 'exception'; -INSERT INTO t1 VALUES (CAST('10 ' AS SIGNED), NULL); -END| -CALL p1(); -exception -exception -DROP TABLE t1; -DROP PROCEDURE p1; -# -# Bug#5889: Exit handler for a warning doesn't hide the warning in trigger -# -SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; -CREATE TABLE t1(a INT, b INT); -INSERT INTO t1 VALUES (1, 2); -CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW -BEGIN -DECLARE EXIT HANDLER FOR SQLWARNING -SET NEW.a = 10; -SET NEW.a = 99999999999; -END| -UPDATE t1 SET b = 20; -SHOW WARNINGS; -Level Code Message -SELECT * FROM t1; -a b -10 20 -DROP TRIGGER t1_bu; -DROP TABLE t1; -SET sql_mode = DEFAULT; -# -# Bug#9857: Stored procedures: handler for sqlwarning ignored -# -SET @sql_mode_saved = @@sql_mode; -SET sql_mode = traditional; -CREATE PROCEDURE p1() -BEGIN -DECLARE CONTINUE HANDLER FOR SQLWARNING -SELECT 'warning caught (expected)'; -SELECT 5 / 0; -END| -CREATE PROCEDURE p2() -BEGIN -DECLARE CONTINUE HANDLER FOR SQLEXCEPTION -SELECT 'error caught (unexpected)'; -SELECT 5 / 0; -END| -CALL p1(); -5 / 0 -NULL -warning caught (expected) -warning caught (expected) -SHOW WARNINGS; -Level Code Message -CALL p2(); -5 / 0 -NULL -Warnings: -Warning 1365 Division by 0 -SHOW WARNINGS; -Level Code Message -Warning 1365 Division by 0 -DROP PROCEDURE p1; -DROP PROCEDURE p2; -SET sql_mode = @sql_mode_saved; -# -# Bug#55850: Trigger warnings not cleared. -# -DROP TABLE IF EXISTS t1; -DROP TABLE IF EXISTS t2; -DROP PROCEDURE IF EXISTS p1; -CREATE TABLE t1(x SMALLINT, y SMALLINT, z SMALLINT); -CREATE TABLE t2(a SMALLINT, b SMALLINT, c SMALLINT, -d SMALLINT, e SMALLINT, f SMALLINT); -CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW -INSERT IGNORE INTO t2(a, b, c) VALUES(99999, 99999, 99999); -CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW -INSERT IGNORE INTO t2(d, e, f) VALUES(99999, 99999, 99999); -CREATE PROCEDURE p1() -INSERT IGNORE INTO t1 VALUES(99999, 99999, 99999); - -CALL p1(); -Warnings: -Warning 1264 Out of range value for column 'x' at row 1 -Warning 1264 Out of range value for column 'y' at row 1 -Warning 1264 Out of range value for column 'z' at row 1 - -SHOW WARNINGS; -Level Code Message -Warning 1264 Out of range value for column 'x' at row 1 -Warning 1264 Out of range value for column 'y' at row 1 -Warning 1264 Out of range value for column 'z' at row 1 - -DROP TABLE t1; -DROP TABLE t2; -DROP PROCEDURE p1; -# ---------------------------------------------------------------------- -SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; -CREATE TABLE t1(x SMALLINT, y SMALLINT, z SMALLINT); -CREATE TABLE t2(a SMALLINT, b SMALLINT, c SMALLINT NOT NULL); -CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW -BEGIN -INSERT INTO t2 VALUES( -CAST('111111 ' AS SIGNED), -CAST('222222 ' AS SIGNED), -NULL); -END| -CREATE PROCEDURE p1() -INSERT INTO t1 VALUES(99999, 99999, 99999); - -CALL p1(); -ERROR 23000: Column 'c' cannot be null - -SHOW WARNINGS; -Level Code Message -Warning 1264 Out of range value for column 'x' at row 1 -Warning 1264 Out of range value for column 'y' at row 1 -Warning 1264 Out of range value for column 'z' at row 1 -Note 1292 Truncated incorrect INTEGER value: '111111 ' -Warning 1264 Out of range value for column 'a' at row 1 -Note 1292 Truncated incorrect INTEGER value: '222222 ' -Warning 1264 Out of range value for column 'b' at row 1 -Error 1048 Column 'c' cannot be null -Note 4094 At line 6 in test.t1_bi -Note 4094 At line 2 in test.p1 - -DROP TABLE t1; -DROP TABLE t2; -DROP PROCEDURE p1; -SET sql_mode = DEFAULT; - -################################################################### -# Tests for the following bugs: -# - Bug#11763171: 55852 - Possibly inappropriate handler activation. -# - Bug#11749343: 38806 - Wrong scope for SQL HANDLERS in SP. -################################################################### - - -# -- Check that SQL-conditions thrown by Statement-blocks are -# -- handled by Handler-decl blocks properly. - -CREATE PROCEDURE p1() -BEGIN -DECLARE CONTINUE HANDLER FOR SQLEXCEPTION -SELECT 'H1' AS HandlerId; -DECLARE CONTINUE HANDLER FOR SQLWARNING -SELECT 'H2' AS HandlerId; -SIGNAL SQLSTATE '01000'; # Should be handled by H2. -END| - -CALL p1()| -HandlerId -H2 - -# -- Check that SQL-conditions thrown by Statement-blocks are -# -- handled by Handler-decl blocks properly in case of nested -# -- SQL-blocks. - -CREATE PROCEDURE p2() -BEGIN -DECLARE CONTINUE HANDLER FOR SQLEXCEPTION -SELECT 'H1' AS HandlerId; -DECLARE CONTINUE HANDLER FOR SQLWARNING -SELECT 'H2' AS HandlerId; -BEGIN -SELECT 'B1' AS BlockId; -BEGIN -SELECT 'B2' AS BlockId; -BEGIN -SELECT 'B3' AS BlockId; -SIGNAL SQLSTATE '01000'; # Should be handled by H2. -END; -END; -END; -END| - -CALL p2()| -BlockId -B1 -BlockId -B2 -BlockId -B3 -HandlerId -H2 - -# -- Check SQL-handler resolution rules. - -CREATE PROCEDURE p3() -BEGIN -DECLARE CONTINUE HANDLER FOR SQLEXCEPTION -SELECT 'H1' AS HandlerId; -DECLARE CONTINUE HANDLER FOR SQLWARNING -SELECT 'H2' AS HandlerId; -DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' - SELECT 'H3' AS HandlerId; -SIGNAL SQLSTATE '01000'; # Should be handled by H3. -END| - -CALL p3()| -HandlerId -H3 - -CREATE PROCEDURE p4() -BEGIN -DECLARE CONTINUE HANDLER FOR SQLEXCEPTION -SELECT 'H1' AS HandlerId; -DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' - SELECT 'H2' AS HandlerId; -DECLARE CONTINUE HANDLER FOR SQLWARNING -SELECT 'H3' AS HandlerId; -SIGNAL SQLSTATE '01000'; # Should be handled by H2. -END| - -CALL p4()| -HandlerId -H2 - -CREATE PROCEDURE p5() -BEGIN -DECLARE CONTINUE HANDLER FOR SQLEXCEPTION -SELECT 'H1' AS HandlerId; -DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' - SELECT 'H2' AS HandlerId; -BEGIN -DECLARE CONTINUE HANDLER FOR SQLWARNING -SELECT 'H3' AS HandlerId; -SIGNAL SQLSTATE '01000'; # Should be handled by H3. -END; -END| - -CALL p5()| -HandlerId -H3 - -# -- Check that handlers don't handle its own exceptions. - -CREATE PROCEDURE p6() -BEGIN -DECLARE CONTINUE HANDLER FOR SQLEXCEPTION -BEGIN -SELECT 'H1' AS HandlerId; -SIGNAL SQLSTATE 'HY000'; # Should *not* be handled by H1. -END; -SELECT 'S1' AS SignalId; -SIGNAL SQLSTATE 'HY000'; # Should be handled by H1. -END| - -CALL p6()| -SignalId -S1 -HandlerId -H1 -ERROR HY000: Unhandled user-defined exception condition - -# -- Check that handlers don't handle its own warnings. - -CREATE PROCEDURE p7() -BEGIN -DECLARE CONTINUE HANDLER FOR SQLWARNING -BEGIN -SELECT 'H1' AS HandlerId; -SIGNAL SQLSTATE '01000'; # Should *not* be handled by H1. -END; -SELECT 'S1' AS SignalId; -SIGNAL SQLSTATE '01000'; # Should be handled by H1. -END| - -CALL p7()| -SignalId -S1 -HandlerId -H1 -Warnings: -Warning 1642 Unhandled user-defined warning condition - -# -- Check that conditions for handlers are not handled by the handlers -# -- from the same block. - -CREATE PROCEDURE p8() -BEGIN -DECLARE CONTINUE HANDLER FOR SQLWARNING -SELECT 'H1' AS HandlerId; -DECLARE CONTINUE HANDLER FOR SQLEXCEPTION -BEGIN -SELECT 'H2' AS HandlerId; -SIGNAL SQLSTATE '01000'; # Should *not* be handled by H1. -END; -SELECT 'S1' AS SignalId; -SIGNAL SQLSTATE 'HY000'; # Should be handled by H2. -END| - -CALL p8()| -SignalId -S1 -HandlerId -H2 -Warnings: -Warning 1642 Unhandled user-defined warning condition - -# -- Check that conditions for handlers are not handled by the handlers -# -- from the same block even if they are thrown deep down the stack. - -CREATE PROCEDURE p9() -BEGIN -DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' - SELECT 'Wrong:H1:1' AS HandlerId; -DECLARE CONTINUE HANDLER FOR SQLWARNING -SELECT 'Wrong:H1:2' AS HandlerId; -DECLARE CONTINUE HANDLER FOR SQLEXCEPTION -BEGIN -DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' - SELECT 'Wrong:H2:1' AS HandlerId; -DECLARE CONTINUE HANDLER FOR SQLWARNING -SELECT 'Wrong:H2:2' AS HandlerId; -DECLARE CONTINUE HANDLER FOR SQLEXCEPTION -BEGIN -DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' - SELECT 'Wrong:H3:1' AS HandlerId; -DECLARE CONTINUE HANDLER FOR SQLWARNING -SELECT 'Wrong:H3:2' AS HandlerId; -DECLARE CONTINUE HANDLER FOR SQLEXCEPTION -BEGIN -DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' - SELECT 'Wrong:H4:1' AS HandlerId; -DECLARE CONTINUE HANDLER FOR SQLWARNING -SELECT 'Wrong:H4:2' AS HandlerId; -DECLARE CONTINUE HANDLER FOR SQLEXCEPTION -BEGIN -DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' - SELECT 'Wrong:H5:1' AS HandlerId; -DECLARE CONTINUE HANDLER FOR SQLWARNING -SELECT 'Wrong:H5:2' AS HandlerId; -DECLARE CONTINUE HANDLER FOR SQLEXCEPTION -BEGIN -DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' - SELECT 'Wrong:H6:1' AS HandlerId; -DECLARE CONTINUE HANDLER FOR SQLWARNING -SELECT 'Wrong:H6:2' AS HandlerId; -DECLARE CONTINUE HANDLER FOR SQLEXCEPTION -BEGIN -SELECT 'H2' AS HandlerId; -SIGNAL SQLSTATE '01000'; # Should *not* be handled by H1. -END; -SELECT 'S6' AS SignalId; -SIGNAL SQLSTATE 'HY000'; -END; -SELECT 'S5' AS SignalId; -SIGNAL SQLSTATE 'HY000'; -END; -SELECT 'S4' AS SignalId; -SIGNAL SQLSTATE 'HY000'; -END; -SELECT 'S3' AS SignalId; -SIGNAL SQLSTATE 'HY000'; -END; -SELECT 'S2' AS SignalId; -SIGNAL SQLSTATE 'HY000'; -END; -SELECT 'S1' AS SignalId; -SIGNAL SQLSTATE 'HY000'; # Should be handled by H2. -END| - -CALL p9()| -SignalId -S1 -SignalId -S2 -SignalId -S3 -SignalId -S4 -SignalId -S5 -SignalId -S6 -HandlerId -H2 -Warnings: -Warning 1642 Unhandled user-defined warning condition - -# -- Check that handlers are choosen properly in case of deep stack and -# -- nested SQL-blocks. - -CREATE PROCEDURE p10() -BEGIN -DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' - SELECT 'H1' AS HandlerId; -DECLARE CONTINUE HANDLER FOR SQLWARNING -SELECT 'H2' AS HandlerId; -BEGIN -BEGIN -BEGIN -DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' - SELECT 'Wrong:H1:1' AS HandlerId; -DECLARE CONTINUE HANDLER FOR SQLWARNING -SELECT 'Wrong:H1:2' AS HandlerId; -DECLARE CONTINUE HANDLER FOR SQLEXCEPTION -BEGIN -DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' - SELECT 'Wrong:H2:1' AS HandlerId; -DECLARE CONTINUE HANDLER FOR SQLWARNING -SELECT 'Wrong:H2:2' AS HandlerId; -DECLARE CONTINUE HANDLER FOR SQLEXCEPTION -BEGIN -DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' - SELECT 'Wrong:H3:1' AS HandlerId; -DECLARE CONTINUE HANDLER FOR SQLWARNING -SELECT 'Wrong:H3:2' AS HandlerId; -DECLARE CONTINUE HANDLER FOR SQLEXCEPTION -BEGIN -DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' - SELECT 'Wrong:H4:1' AS HandlerId; -DECLARE CONTINUE HANDLER FOR SQLWARNING -SELECT 'Wrong:H4:2' AS HandlerId; -DECLARE CONTINUE HANDLER FOR SQLEXCEPTION -BEGIN -DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' - SELECT 'Wrong:H5:1' AS HandlerId; -DECLARE CONTINUE HANDLER FOR SQLWARNING -SELECT 'Wrong:H5:2' AS HandlerId; -DECLARE CONTINUE HANDLER FOR SQLEXCEPTION -BEGIN -DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' - SELECT 'Wrong:H6:1' AS HandlerId; -DECLARE CONTINUE HANDLER FOR SQLWARNING -SELECT 'Wrong:H6:2' AS HandlerId; -DECLARE CONTINUE HANDLER FOR SQLEXCEPTION -BEGIN -SELECT 'H2' AS HandlerId; -SIGNAL SQLSTATE '01000'; # Should be handled by H1. -END; -SELECT 'S6' AS SignalId; -SIGNAL SQLSTATE 'HY000'; -END; -SELECT 'S5' AS SignalId; -SIGNAL SQLSTATE 'HY000'; -END; -SELECT 'S4' AS SignalId; -SIGNAL SQLSTATE 'HY000'; -END; -SELECT 'S3' AS SignalId; -SIGNAL SQLSTATE 'HY000'; -END; -SELECT 'S2' AS SignalId; -SIGNAL SQLSTATE 'HY000'; -END; -SELECT 'S1' AS SignalId; -SIGNAL SQLSTATE 'HY000'; # Should be handled by H2. -END; -END; -END; -END| - -CALL p10()| -SignalId -S1 -SignalId -S2 -SignalId -S3 -SignalId -S4 -SignalId -S5 -SignalId -S6 -HandlerId -H2 -HandlerId -H1 - -# -- Test stored procedure from Peter's mail. - -CREATE PROCEDURE p11() -BEGIN -DECLARE CONTINUE HANDLER FOR SQLEXCEPTION -SELECT 'H1' AS HandlerId; -DECLARE CONTINUE HANDLER FOR SQLWARNING -SELECT 'H2' AS HandlerId; -BEGIN -DECLARE CONTINUE HANDLER FOR SQLSTATE '01000', 1249 -BEGIN -DECLARE CONTINUE HANDLER FOR SQLEXCEPTION -SELECT 'H3' AS HandlerId; -DECLARE CONTINUE HANDLER FOR SQLWARNING -SELECT 'H4' AS HandlerId; -BEGIN -SELECT 'H5' AS HandlerId; -SELECT 'S3' AS SignalId; -SIGNAL SQLSTATE 'HY000'; # H3 -SELECT 'S4' AS SignalId; -SIGNAL SQLSTATE '22003'; # H3 -SELECT 'S5' AS SignalId; -SIGNAL SQLSTATE '01000' SET MYSQL_ERRNO = 1249; # H4 -END; -END; -SELECT 'S6' AS SignalId; -SIGNAL SQLSTATE 'HY000'; # H1 -SELECT 'S7' AS SignalId; -SIGNAL SQLSTATE '22003'; # H1 -SELECT 'S8' AS SignalId; -SIGNAL SQLSTATE '01000' SET MYSQL_ERRNO = 1249; # H5 -END; -SELECT 'S1' AS SignalId; -SIGNAL SQLSTATE 'HY000'; # H1 -SELECT 'S2' AS SignalId; -SIGNAL SQLSTATE '01000' SET MYSQL_ERRNO = 1249; # H2 -END| - -CALL p11()| -SignalId -S6 -HandlerId -H1 -SignalId -S7 -HandlerId -H1 -SignalId -S8 -HandlerId -H5 -SignalId -S3 -HandlerId -H3 -SignalId -S4 -HandlerId -H3 -SignalId -S5 -HandlerId -H4 -SignalId -S1 -HandlerId -H1 -SignalId -S2 -HandlerId -H2 - -# -- Check that runtime stack-trace can be deeper than parsing-time one. - -CREATE PROCEDURE p12() -BEGIN -DECLARE CONTINUE HANDLER FOR SQLSTATE '01001' - BEGIN -DECLARE CONTINUE HANDLER FOR SQLSTATE '01001' - BEGIN -DECLARE CONTINUE HANDLER FOR SQLSTATE '01001' - BEGIN -DECLARE CONTINUE HANDLER FOR SQLSTATE '01001' - BEGIN -DECLARE CONTINUE HANDLER FOR SQLSTATE '01001' - BEGIN -SELECT 'H1:5' AS HandlerId; -SIGNAL SQLSTATE '01002'; -END; -SELECT 'H1:4' AS HandlerId; -SIGNAL SQLSTATE '01001'; -END; -SELECT 'H1:3' AS HandlerId; -SIGNAL SQLSTATE '01001'; -END; -SELECT 'H1:2' AS HandlerId; -SIGNAL SQLSTATE '01001'; -END; -SELECT 'H1:1' AS HandlerId; -SIGNAL SQLSTATE '01001'; -END; -######################################################### -DECLARE CONTINUE HANDLER FOR SQLSTATE '01002' - SELECT 'OK' AS Msg; -######################################################### -BEGIN -DECLARE CONTINUE HANDLER FOR SQLWARNING -BEGIN -DECLARE CONTINUE HANDLER FOR SQLWARNING -BEGIN -DECLARE CONTINUE HANDLER FOR SQLWARNING -BEGIN -DECLARE CONTINUE HANDLER FOR SQLWARNING -BEGIN -DECLARE CONTINUE HANDLER FOR SQLWARNING -BEGIN -SELECT 'H2:5' AS HandlerId; -SIGNAL SQLSTATE '01001'; -END; -SELECT 'H2:4' AS HandlerId; -SIGNAL SQLSTATE '01000'; -END; -SELECT 'H2:3' AS HandlerId; -SIGNAL SQLSTATE '01000'; -END; -SELECT 'H2:2' AS HandlerId; -SIGNAL SQLSTATE '01000'; -END; -SELECT 'H2:1' AS HandlerId; -SIGNAL SQLSTATE '01000'; -END; -####################################################### -SELECT 'Throw 01000' AS Msg; -SIGNAL SQLSTATE '01000'; -END; -END| - -CALL p12()| -Msg -Throw 01000 -HandlerId -H2:1 -HandlerId -H2:2 -HandlerId -H2:3 -HandlerId -H2:4 -HandlerId -H2:5 -HandlerId -H1:1 -HandlerId -H1:2 -HandlerId -H1:3 -HandlerId -H1:4 -HandlerId -H1:5 -Warnings: -Warning 1642 Unhandled user-defined warning condition - -# -- Check that handler-call-frames are removed properly for EXIT -# -- handlers. - -CREATE PROCEDURE p13() -BEGIN -DECLARE CONTINUE HANDLER FOR SQLWARNING -BEGIN -DECLARE CONTINUE HANDLER FOR SQLWARNING -BEGIN -DECLARE EXIT HANDLER FOR SQLWARNING -BEGIN -SELECT 'EXIT handler 3' AS Msg; -END; -SELECT 'CONTINUE handler 2: 1' AS Msg; -SIGNAL SQLSTATE '01000'; -SELECT 'CONTINUE handler 2: 2' AS Msg; -END; -SELECT 'CONTINUE handler 1: 1' AS Msg; -SIGNAL SQLSTATE '01000'; -SELECT 'CONTINUE handler 1: 2' AS Msg; -END; -SELECT 'Throw 01000' AS Msg; -SIGNAL SQLSTATE '01000'; -END| - -CALL p13()| -Msg -Throw 01000 -Msg -CONTINUE handler 1: 1 -Msg -CONTINUE handler 2: 1 -Msg -EXIT handler 3 -Msg -CONTINUE handler 1: 2 - -# That's it. Cleanup. - -DROP PROCEDURE p1; -DROP PROCEDURE p2; -DROP PROCEDURE p3; -DROP PROCEDURE p4; -DROP PROCEDURE p5; -DROP PROCEDURE p6; -DROP PROCEDURE p7; -DROP PROCEDURE p8; -DROP PROCEDURE p9; -DROP PROCEDURE p10; -DROP PROCEDURE p11; -DROP PROCEDURE p12; -DROP PROCEDURE p13; - -# Bug#12731619: NESTED SP HANDLERS CAN TRIGGER ASSERTION - -DROP FUNCTION IF EXISTS f1; -DROP TABLE IF EXISTS t1; -CREATE TABLE t1(msg VARCHAR(255)); -CREATE FUNCTION f1() RETURNS INT -BEGIN -DECLARE CONTINUE HANDLER FOR SQLEXCEPTION # handler 1 -BEGIN -DECLARE CONTINUE HANDLER FOR SQLEXCEPTION # handler 2 -BEGIN -INSERT INTO t1 VALUE('WRONG: Inside H2'); -RETURN 2; -END; -INSERT INTO t1 VALUE('CORRECT: Inside H1'); -RETURN 1; -END; -BEGIN -DECLARE CONTINUE HANDLER FOR SQLWARNING # handler 3 -BEGIN -INSERT INTO t1 VALUE('WRONG: Inside H3'); -RETURN 3; -END; -INSERT INTO t1 VALUE('CORRECT: Calling f1()'); -RETURN f1(); # -- exception here -END; -INSERT INTO t1 VALUE('WRONG: Returning 10'); -RETURN 10; -END| - -SELECT f1(); -f1() -1 - -SELECT * FROM t1; -msg -CORRECT: Calling f1() -CORRECT: Inside H1 - -DROP FUNCTION f1; -DROP TABLE t1; - -# Check that handled SQL-conditions are properly cleared from DA. - -DROP TABLE IF EXISTS t1; -DROP TABLE IF EXISTS t2; -DROP PROCEDURE IF EXISTS p1; -DROP PROCEDURE IF EXISTS p2; -DROP PROCEDURE IF EXISTS p3; -DROP PROCEDURE IF EXISTS p4; -DROP PROCEDURE IF EXISTS p5; -CREATE TABLE t1(a CHAR, b CHAR, c CHAR); -CREATE TABLE t2(a SMALLINT, b SMALLINT, c SMALLINT); - -# Check that SQL-conditions for which SQL-handler has been invoked, -# are cleared from the Diagnostics Area. Note, there might be several -# SQL-conditions, but SQL-handler must be invoked only once. - -CREATE PROCEDURE p1() -BEGIN -DECLARE EXIT HANDLER FOR SQLWARNING -SELECT 'Warning caught' AS msg; -# The INSERT below raises 3 SQL-conditions (warnings). The EXIT HANDLER -# above must be invoked once (for one condition), but all three conditions -# must be cleared from the Diagnostics Area. -INSERT IGNORE INTO t1 VALUES('qqqq', 'ww', 'eee'); -# The following INSERT will not be executed, because of the EXIT HANDLER. -INSERT INTO t1 VALUES('zzz', 'xx', 'yyyy'); -END| - -CALL p1()| -msg -Warning caught - -SELECT * FROM t1| -a b c -q w e - -# Check that SQL-conditions for which SQL-handler has *not* been -# invoked, are *still* cleared from the Diagnostics Area. - -CREATE PROCEDURE p2() -BEGIN -DECLARE CONTINUE HANDLER FOR 1292 -SELECT 'Warning 1292 caught' AS msg; -# The following INSERT raises 6 SQL-warnings with code 1292, -# and 3 SQL-warnings with code 1264. The CONTINUE HANDLER above must be -# invoked once, and all nine SQL-warnings must be cleared from -# the Diagnostics Area. -INSERT IGNORE INTO t2 -SELECT -CAST(CONCAT(CAST('1 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER), -CAST(CONCAT(CAST('2 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER), -CAST(CONCAT(CAST('3 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER); -END| - -CALL p2()| -msg -Warning 1292 caught - -# Check that if there are two equally ranked SQL-handlers to handle -# SQL-conditions from SQL-statement, only one of them will be invoked. - -CREATE PROCEDURE p3() -BEGIN -DECLARE CONTINUE HANDLER FOR 1292 -SELECT 'Warning 1292 caught' AS msg; -DECLARE CONTINUE HANDLER FOR 1264 -SELECT 'Warning 1264 caught' AS msg; -# The following INSERT raises 6 SQL-warnings with code 1292, -# and 3 SQL-warnings with code 1264. Only one of the CONTINUE HANDLERs above -# must be called, and only once. The SQL Standard does not define, which one -# should be invoked. -INSERT INTO t2 -SELECT -CAST(CONCAT(CAST('1 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER), -CAST(CONCAT(CAST('2 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER), -CAST(CONCAT(CAST('3 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER); -END| - -CALL p3()| -msg -Warning 1264 caught - -# The same as p3, but 1264 comes first. - -CREATE PROCEDURE p4() -BEGIN -DECLARE CONTINUE HANDLER FOR 1292 -SELECT 'Warning 1292 caught' AS msg; -DECLARE CONTINUE HANDLER FOR 1264 -SELECT 'Warning 1264 caught' AS msg; -# The following INSERT raises 4 SQL-warnings with code 1292, -# and 3 SQL-warnings with code 1264. Only one of the CONTINUE HANDLERs above -# must be called, and only once. The SQL Standard does not define, which one -# should be invoked. -INSERT INTO t2 -SELECT -CAST(999999 AS SIGNED INTEGER), -CAST(CONCAT(CAST('2 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER), -CAST(CONCAT(CAST('3 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER); -END| - -CALL p4()| -msg -Warning 1264 caught - -# Check that if a SQL-handler raised its own SQL-conditions, there are -# preserved after handler exit. - -CREATE PROCEDURE p5() -BEGIN -DECLARE EXIT HANDLER FOR 1292 -BEGIN -SELECT 'Handler for 1292 (1)' AS Msg; -SIGNAL SQLSTATE '01000' SET MYSQL_ERRNO = 1234; -SHOW WARNINGS; -SELECT 'Handler for 1292 (2)' AS Msg; -END; -INSERT IGNORE INTO t2 -SELECT -CAST(999999 AS SIGNED INTEGER), -CAST(CONCAT(CAST('2 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER), -CAST(CONCAT(CAST('3 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER); -END| - -CALL p5()| -Msg -Handler for 1292 (1) -Level Code Message -Warning 1234 Unhandled user-defined warning condition -Msg -Handler for 1292 (2) -Warnings: -Warning 1234 Unhandled user-defined warning condition - -# Check that SQL-conditions are available inside the handler, but -# cleared after the handler exits. - -CREATE PROCEDURE p6() -BEGIN -DECLARE CONTINUE HANDLER FOR 1292 -BEGIN -SHOW WARNINGS; -SELECT 'Handler for 1292' Msg; -END; -INSERT IGNORE INTO t2 -SELECT -CAST(CONCAT(CAST('1 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER), -CAST(CONCAT(CAST('2 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER), -CAST(CONCAT(CAST('3 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER); -END| - -CALL p6()| -Level Code Message -Note 1292 Truncated incorrect INTEGER value: '1 ' -Note 1292 Truncated incorrect INTEGER value: '1999999 ' -Warning 1264 Out of range value for column 'a' at row 1 -Note 1292 Truncated incorrect INTEGER value: '2 ' -Note 1292 Truncated incorrect INTEGER value: '2999999 ' -Warning 1264 Out of range value for column 'b' at row 1 -Note 1292 Truncated incorrect INTEGER value: '3 ' -Note 1292 Truncated incorrect INTEGER value: '3999999 ' -Warning 1264 Out of range value for column 'c' at row 1 -Msg -Handler for 1292 - -DROP PROCEDURE p1; -DROP PROCEDURE p2; -DROP PROCEDURE p3; -DROP PROCEDURE p4; -DROP PROCEDURE p5; -DROP PROCEDURE p6; -DROP TABLE t1; -DROP TABLE t2; - -# Bug#13059316: ASSERTION FAILURE IN SP_RCONTEXT.CC -# Check DECLARE statements that raise conditions before handlers -# are declared. - -DROP PROCEDURE IF EXISTS p1; -DROP PROCEDURE IF EXISTS p2; -SET sql_mode = ''; -CREATE PROCEDURE p1() -BEGIN -DECLARE var1 INTEGER DEFAULT 'string'; -DECLARE EXIT HANDLER FOR SQLWARNING SELECT 'H1'; -END| - -CALL p1()| -Warnings: -Warning 1366 Incorrect integer value: 'string' for column 'var1' at row 1 - -SET sql_mode = DEFAULT; -CREATE PROCEDURE p2() -BEGIN -DECLARE EXIT HANDLER FOR SQLWARNING SELECT 'H2'; -CALL p1(); -END| - -CALL p2()| -H2 -H2 - -DROP PROCEDURE p1; -DROP PROCEDURE p2; -# -# Bug#13113222 RQG_SIGNAL_RESIGNAL FAILED WITH ASSERTION. -# -DROP PROCEDURE IF EXISTS p1; -DROP PROCEDURE IF EXISTS p2; -CREATE PROCEDURE p1() -BEGIN -DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SELECT 'triggered p1'; -# This will trigger an error. -SIGNAL SQLSTATE 'HY000'; -END| -CREATE PROCEDURE p2() -BEGIN -DECLARE CONTINUE HANDLER FOR SQLWARNING SELECT 'triggered p2'; -# This will trigger a warning. -SIGNAL SQLSTATE '01000'; -END| -SET @old_max_error_count= @@session.max_error_count; -SET SESSION max_error_count= 0; -CALL p1(); -triggered p1 -triggered p1 -CALL p2(); -SET SESSION max_error_count= @old_max_error_count; -DROP PROCEDURE p1; -DROP PROCEDURE p2; - -# Bug#12652873: 61392: Continue handler for NOT FOUND being triggered -# from internal stored function. - -DROP FUNCTION IF EXISTS f1; -DROP FUNCTION IF EXISTS f2; -DROP TABLE IF EXISTS t1; - -CREATE TABLE t1 (a INT, b INT); -INSERT INTO t1 VALUES (1, 2); - -# f1() raises NOT_FOUND condition. -# Raising NOT_FOUND can not be simulated by SIGNAL, -# because SIGNAL would raise SQL-error in that case. - -CREATE FUNCTION f1() RETURNS INTEGER -BEGIN -DECLARE v VARCHAR(5) DEFAULT -1; -SELECT b FROM t1 WHERE a = 2 INTO v; -RETURN v; -END| - -# Here we check that the NOT_FOUND condition raised in f1() -# is not visible in the outer function (f2), i.e. the continue -# handler in f2() will not be called. - -CREATE FUNCTION f2() RETURNS INTEGER -BEGIN -DECLARE v INTEGER; -DECLARE CONTINUE HANDLER FOR NOT FOUND -SET @msg = 'Handler activated.'; -SELECT f1() INTO v; -RETURN v; -END| -SET @msg = ''; - -SELECT f2(); -f2() --1 - -SELECT @msg; -@msg - - -DROP FUNCTION f1; -DROP FUNCTION f2; -DROP TABLE t1; |