diff options
Diffstat (limited to 'mysql-test/main/sp-error.test')
| -rw-r--r-- | mysql-test/main/sp-error.test | 3869 |
1 files changed, 3869 insertions, 0 deletions
diff --git a/mysql-test/main/sp-error.test b/mysql-test/main/sp-error.test new file mode 100644 index 00000000..c112dd0f --- /dev/null +++ b/mysql-test/main/sp-error.test @@ -0,0 +1,3869 @@ +# +# Stored PROCEDURE error tests +# + +--source include/default_charset.inc + +--disable_warnings +drop table if exists t1, t2; +--enable_warnings + +--disable_ps2_protocol +# Backup the mysql.proc table +--enable_prepare_warnings +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval SELECT * FROM mysql.proc INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/proc.txt'; +--disable_prepare_warnings +--enable_ps2_protocol +# Make sure we don't have any procedures left. +delete from mysql.proc; + +delimiter |; + +# This should give three syntax errors (sometimes crashed; bug #643) +# (Unfortunately, this is not a 100% test, on some platforms this +# passed despite the bug.) +--error 1064 +create procedure syntaxerror(t int)| +--error 1064 +create procedure syntaxerror(t int)| +--error 1064 +create procedure syntaxerror(t int)| + +# Check that we get the right error, i.e. UDF declaration parses correctly, +# but foo.so doesn't exist. +# This generates an error message containing a misleading errno which +# might vary between systems (it usually doesn't have anything to do with +# the actual failing dlopen()). +#--error 1126 +#create function foo returns real soname "foo.so"| + + +--disable_warnings +drop table if exists t3| +--enable_warnings +create table t3 ( x int )| +insert into t3 values (2), (3)| + +--enable_prepare_warnings +create procedure bad_into(out param int) + select x from t3 into param| +--disable_prepare_warnings + +--error 1172 +call bad_into(@x)| + +drop procedure bad_into| +drop table t3| + + +create procedure proc1() + set @x = 42| + +create function func1() returns int + return 42| + +# Can't create recursively +--error 1303 +create procedure foo() + create procedure bar() set @x=3| +--error 1303 +create procedure foo() + create function bar() returns double return 2.3| + +# Already exists +--error 1304 +create procedure proc1() + set @x = 42| +--error 1304 +create function func1() returns int + return 42| + +drop procedure proc1| +drop function func1| + +# Does not exist +--error 1305 +alter procedure foo| +--error 1305 +alter function foo| +--error 1305 +drop procedure foo| +--error 1305 +drop function foo| +--error 1305 +call foo()| +drop procedure if exists foo| +--error 1305 +show create procedure foo| +--error 1305 +show create function foo| + +# LEAVE/ITERATE with no match +--error 1308 +create procedure foo() +foo: loop + leave bar; +end loop| +--error 1308 +create procedure foo() +foo: loop + iterate bar; +end loop| +--error 1308 +create procedure foo() +foo: begin + iterate foo; +end| + +# Redefining label +--error 1309 +create procedure foo() +foo: loop + foo: loop + set @x=2; + end loop foo; +end loop foo| + +# End label mismatch +--error 1310 +create procedure foo() +foo: loop + set @x=2; +end loop bar| + +# RETURN in FUNCTION only +--error 1313 +create procedure foo() + return 42| + +# Wrong number of arguments +create procedure p(x int) + set @x = x| +create function f(x int) returns int + return x+42| + +--error 1318 +call p()| +--error 1318 +call p(1, 2)| +--error 1318 +select f()| +--error 1318 +select f(1, 2)| + +drop procedure p| +drop function f| + +--error 1319 +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 1319 +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 1320 +create function f(val int) returns int +begin + declare x int; + + set x = val+3; +end| + +create function f(val int) returns int +begin + declare x int; + + set x = val+3; + if x < 4 then + return x; + end if; +end| + +--error 1321 +select f(10)| + +drop function f| + +--error ER_PARSE_ERROR +create procedure p() +begin + declare c cursor for insert into test.t1 values ("foo", 42); + + open c; + close c; +end| + +--error 1323 +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 1324 +create procedure p() +begin + declare c cursor for select * from test.t; + + open cc; + close c; +end| + +--disable_warnings +drop table if exists t1| +--enable_warnings +create table t1 (val int)| + +create procedure p() +begin + declare c cursor for select * from test.t1; + + open c; + open c; + close c; +end| +--error 1325 +call p()| +drop procedure p| + +create procedure p() +begin + declare c cursor for select * from test.t1; + + open c; + close c; + close c; +end| +--error 1326 +call p()| +drop procedure p| + +--error 1305 +alter procedure bar3 sql security invoker| + +drop table t1| + +--disable_warnings +drop table if exists t1| +--enable_warnings +create table t1 (val int, x float)| +insert into t1 values (42, 3.1), (19, 1.2)| + +--error 1327 +create procedure p() +begin + declare x int; + declare c cursor for select * from t1; + + open c; + fetch c into x, y; + close c; +end| + +create procedure p() +begin + declare x int; + declare c cursor for select * from t1; + + open c; + fetch c into x; + close c; +end| +--error 1328 +call p()| +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| +--error 1328 +call p()| +drop procedure p| + +--error 1330 +create procedure p(in x int, x char(10)) +begin +end| +--error 1330 +create function p(x int, x char(10)) +begin +end| + +--error 1331 +create procedure p() +begin + declare x float; + declare x int; +end| + +--error 1332 +create procedure p() +begin + declare c condition for 1064; + declare c condition for 1065; +end| + +--error 1333 +create procedure p() +begin + declare c cursor for select * from t1; + declare c cursor for select field from t1; +end| + +# USE is not allowed +--error ER_SP_BADSTATEMENT +create procedure u() + use sptmp| + +# Enforced standard order of declarations +--error 1337 +create procedure p() +begin + declare c cursor for select * from t1; + declare x int; +end| +--error 1337 +create procedure p() +begin + declare x int; + declare continue handler for sqlstate '42S99' set x = 1; + declare foo condition for sqlstate '42S99'; +end| + +--error 1338 +create procedure p() +begin + declare x int; + declare continue handler for sqlstate '42S99' set x = 1; + declare c cursor for select * from t1; +end| + +# Check in and inout arguments. +--disable_warnings +drop procedure if exists p| +--enable_warnings +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| +# For reference: this is ok +call p(@tmp_x, @tmp_y, @tmp_z)| +select @tmp_x, @tmp_y, @tmp_z| + +--error ER_SP_NOT_VAR_ARG +call p(42, 43, @tmp_z)| +--error ER_SP_NOT_VAR_ARG +call p(42, @tmp_y, 43)| + +drop procedure p| + + +# +# Let us test that we can access mysql.proc table for routines +# definitions lookup without locking it explicitly. +# +create procedure p() begin end| +lock table t1 read| +# This should succeed +call p()| +unlock tables| +drop procedure p| +# Let us check restrictions which this ability puts on mysql.proc locking. +--error ER_WRONG_LOCK_OF_SYSTEM_TABLE +lock tables t1 read, mysql.proc write| +--error ER_WRONG_LOCK_OF_SYSTEM_TABLE +lock tables mysql.proc write, mysql.user write| +# Locking for read should be OK +lock tables t1 read, mysql.proc read| +unlock tables| +# You also should be able lock only mysql.proc for write +lock tables mysql.proc write| +unlock tables| + + +# +# Check that in functions we don't allow to update tables which +# are used by statements which invoke these functions. +# +--disable_ps2_protocol +--disable_warnings +drop function if exists f1| +--enable_warnings +create function f1(i int) returns int +begin + insert into t1 (val) values (i); + return 0; +end| +--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG +select val, f1(val) from t1| +# Table alias should not matter +--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG +select val, f1(val) from t1 as tab| +select * from t1| +--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG +update t1 set val= f1(val)| +select * from t1| +# But this should be OK +select f1(17)| +select * from t1| +# Cleanup +delete from t1 where val= 17| +drop function f1| +--enable_ps2_protocol + +# +# BUG#1965 +# +create procedure bug1965() +begin + declare c cursor for select val from t1 order by valname; + open c; + close c; +end| + +--error 1054 +call bug1965()| +drop procedure bug1965| + +# +# BUG#1966 +# +--error 1327 +select 1 into a| + +# +# BUG#1653 +# +--disable_warnings +drop table if exists t3| +--enable_warnings +create table t3 (column_1_0 int)| + +create procedure bug1653() + update t3 set column_1 = 0| + +--error 1054 +call bug1653()| +drop table t3| +create table t3 (column_1 int)| +call bug1653()| + +drop procedure bug1653| +drop table t3| + +# +# BUG#2259 +# +# Note: When this bug existed, it did not necessarily cause a crash +# in all builds, but valgrind did give warnings. +create procedure bug2259() +begin + declare v1 int; + declare c1 cursor for select s1 from t1; + + fetch c1 into v1; +end| + +--error 1326 +call bug2259()| +drop procedure bug2259| + +# +# BUG#2272 +# +create procedure bug2272() +begin + declare v int; + + update t1 set v = 42; +end| + +insert into t1 values (666, 51.3)| +--error 1054 +call bug2272()| +truncate table t1| +drop procedure bug2272| + +# +# BUG#2329 +# +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| + +--error 1054 +call bug2329_1()| +--error 1054 +call bug2329_2()| +drop procedure bug2329_1| +drop procedure bug2329_2| + +# +# BUG#3287 +# +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| +--error 1339 +select bug3287()| +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| +--error 1339 +call bug3287(2)| +drop procedure bug3287| + +# +# BUG#3297 +# +--disable_warnings +drop table if exists t3| +--enable_warnings +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| +--error ER_DUP_ENTRY +call bug3279(@x)| +select @x| +drop procedure bug3279| +drop table t3| + +# +# BUG#3339 +# +--error 1049 +create procedure nodb.bug3339() begin end| + +# +# BUG#2653 +# +--error ER_SP_UNDECLARED_VAR +create procedure bug2653_1(a int, out b int) + set b = aa| + + +--error ER_SP_UNDECLARED_VAR +create procedure bug2653_2(a int, out b int) +begin + if aa < 0 then + set b = - a; + else + set b = a; + end if; +end| + + +# +# BUG#4344 +# +--error 1357 +create procedure bug4344() drop procedure bug4344| +--error 1357 +create procedure bug4344() drop function bug4344| + +# +# BUG#3294: Stored procedure crash if table dropped before use +# (Actually, when an error occurs within an error handler.) +--disable_warnings +drop procedure if exists bug3294| +--enable_warnings +create procedure bug3294() +begin + declare continue handler for sqlexception drop table t5; + drop table t5; + drop table t5; +end| + +create table t5 (x int)| +--error 1051 +call bug3294()| +drop procedure bug3294| + +# +# BUG#876: Stored Procedures: Invalid SQLSTATE is allowed in +# a DECLARE ? HANDLER FOR stmt. +# +--disable_warnings +drop procedure if exists bug8776_1| +drop procedure if exists bug8776_2| +drop procedure if exists bug8776_3| +drop procedure if exists bug8776_4| +--enable_warnings +--error ER_SP_BAD_SQLSTATE +create procedure bug8776_1() +begin + declare continue handler for sqlstate '42S0200test' begin end; + begin end; +end| + +--error ER_SP_BAD_SQLSTATE +create procedure bug8776_2() +begin + declare continue handler for sqlstate '4200' begin end; + begin end; +end| + +--error ER_SP_BAD_SQLSTATE +create procedure bug8776_3() +begin + declare continue handler for sqlstate '420000' begin end; + begin end; +end| + +--error ER_SP_BAD_SQLSTATE +create procedure bug8776_4() +begin + declare continue handler for sqlstate '42x00' begin end; + begin end; +end| + + +# +# BUG#6600: Stored procedure crash after repeated calls with check table +# +--error ER_SP_BADSTATEMENT +create procedure bug6600() + check table t1| + +# Check these two as well, while we're at it. (Although it isn't really +# related to the bug report, but to the fix.) +--error ER_SP_BADSTATEMENT +create procedure bug6600() + lock table t1 read| +--error ER_SP_BADSTATEMENT +create procedure bug6600() + unlock table t1| + +# +# BUG#9566: explicit LOCK TABLE and store procedures result in illegal state +# +# We should not think that mysql.proc table does not exist if we are unable +# to open it under LOCK TABLE or in prelocked mode. +# +--disable_warnings +drop procedure if exists bug9566| +--enable_warnings +create procedure bug9566() +begin + select * from t1; +end| +lock table t1 read| +# This should fail since we forgot to lock mysql.proc for writing +# explicitly, and we can't open mysql.proc for _writing_ if there +# are locked tables. +--error ER_LOCK_OR_ACTIVE_TRANSACTION +alter procedure bug9566 comment 'Some comment'| +unlock tables| +# This should succeed +drop procedure bug9566| + + +# +# BUG#7299: Stored procedures: exception handler catches not-found conditions +# +--disable_warnings +drop procedure if exists bug7299| +--enable_warnings +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| +--error ER_SP_FETCH_NO_DATA +call bug7299()| +drop procedure bug7299| + + +# +# BUG#9073: Able to declare two handlers for same condition in same scope +# +--error ER_SP_DUP_HANDLER +create procedure bug9073() +begin + declare continue handler for sqlexception select 1; + declare continue handler for sqlexception select 2; +end| +--error ER_SP_DUP_HANDLER +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 ER_SP_DUP_HANDLER +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 ER_SP_DUP_HANDLER +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| + +# This should still work. +--disable_warnings +drop procedure if exists bug9073| +--enable_warnings +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| + + +# +# BUG#7047: Stored procedure crash if alter procedure +# +--error ER_SP_NO_DROP_SP +create procedure bug7047() + alter procedure bug7047| +--error ER_SP_NO_DROP_SP +create function bug7047() returns int +begin + alter function bug7047; + return 0; +end| + + +# +# BUG#8408: Stored procedure crash if function contains SHOW +# BUG#9058: Stored Procedures: Crash if function included SELECT +# + +# Some things are caught when parsing +--error ER_SP_NO_RETSET +create function bug8408() returns int +begin + select * from t1; + return 0; +end| +--error ER_SP_NO_RETSET +create function bug8408() returns int +begin + show warnings; + return 0; +end| +--error ER_SP_NO_RETSET +create function bug8408(a int) returns int +begin + declare b int; + select b; + return b; +end| + +--disable_warnings +drop function if exists bug8408_f| +drop procedure if exists bug8408_p| +--enable_warnings + +# Some things must be caught at invokation time +create function bug8408_f() returns int +begin + call bug8408_p(); + return 0; +end| +create procedure bug8408_p() + select * from t1| + +call bug8408_p()| +--error ER_SP_NO_RETSET +select bug8408_f()| + +drop procedure bug8408_p| +drop function bug8408_f| + +# But this is ok +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| + +drop function bug8408| +truncate table t1| + + +# +# BUG#10537: Server crashes while loading data file into table through +# procedure. +# Disable load until it's PS and SP safe +--disable_warnings +drop procedure if exists bug10537| +--enable_warnings +--error ER_SP_BADSTATEMENT +create procedure bug10537() + load data local infile '/tmp/somefile' into table t1| + + +# +# BUG#8409: Stored procedure crash if function contains FLUSH +# +--disable_warnings +drop function if exists bug8409| +--enable_warnings +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create function bug8409() + returns int +begin + flush tables; + return 5; +end| +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create function bug8409() returns int begin reset query cache; +return 1; end| +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create function bug8409() returns int begin reset master; +return 1; end| +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create function bug8409() returns int begin reset slave; +return 1; end| +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create function bug8409() returns int begin flush hosts; +return 1; end| +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create function bug8409() returns int begin flush privileges; +return 1; end| +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create function bug8409() returns int begin flush tables with read lock; +return 1; end| +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create function bug8409() returns int begin flush tables; +return 1; end| +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create function bug8409() returns int begin flush logs; +return 1; end| +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create function bug8409() returns int begin flush status; +return 1; end| +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create function bug8409() returns int begin flush slave; +return 1; end| +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create function bug8409() returns int begin flush master; +return 1; end| +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create function bug8409() returns int begin flush des_key_file; +return 1; end| +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create function bug8409() returns int begin flush user_resources; +return 1; end| + + +# +# BUG#9529: Stored Procedures: No Warning on truncation of procedure name +# during creation. +# BUG#17015: Routine name truncation not an error +# When we started using utf8 for mysql.proc, this limit appeared +# to be higher, but in reality the names were truncated. +--error ER_TOO_LONG_IDENT +create procedure bug9529_901234567890123456789012345678901234567890123456789012345() +begin +end| + +--disable_warnings +drop procedure if exists bug17015_0123456789012345678901234567890123456789012345678901234| +--enable_warnings +# Check the upper limit, just to make sure. +create procedure bug17015_0123456789012345678901234567890123456789012345678901234() +begin +end| + +--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' +show procedure status like 'bug17015%'| +drop procedure bug17015_0123456789012345678901234567890123456789012345678901234| + + +# +# BUG#10969: Stored procedures: crash if default() function +# +--disable_warnings +drop procedure if exists bug10969| +--enable_warnings +--error ER_WRONG_COLUMN_NAME +create procedure bug10969() +begin + declare s1 int default 0; + select default(s1) from t30; +end| + +# This should work +create procedure bug10969() +begin + declare s1 int default 0; + select default(t30.s1) from t30; +end| + +drop procedure bug10969| + + +drop table t1| + +delimiter ;| + +# BUG#9814: Closing a cursor that is not open +create table t1(f1 int); +create table t2(f1 int); + +delimiter |; +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| +delimiter ;| +--error 1326 +call SP001(); +drop procedure SP001; +drop table t1, t2; + +# Bug #11394 "Recursion in SP crash server" and bug #11600 "Stored +# procedures: crash with function calling itself". +# We have to disable recursion since in many cases LEX and many +# Item's can't be used in reentrant way nowdays. +delimiter |; +--disable_warnings +drop function if exists bug11394| +drop function if exists bug11394_1| +drop function if exists bug11394_2| +drop procedure if exists bug11394| +--enable_warnings +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| +# If we allow recursive functions without additional modifications +# this will crash server since Item for "IN" is not reenterable. +--error 1424 +select bug11394(2)| +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| +# The following statement will crash because some LEX members responsible +# for selects cannot be used in reentrant fashion. +--error 1424 +select bug11394_1(2)| +drop function bug11394_1| +# Note that the following should be allowed since it does not contains +# recursion +create function bug11394_2(i int) returns int return i| +select bug11394_2(bug11394_2(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| +--error ER_SP_RECURSION_LIMIT +call bug11394(2, 1)| +set @@max_sp_recursion_depth=10| +call bug11394(2, 1)| +set @@max_sp_recursion_depth=default| +drop procedure bug11394| +delimiter ;| + + +# +# BUG 12490 (Packets out of order if calling HELP CONTENTS from Stored Procedure) +# +--error ER_SP_BADSTATEMENT +CREATE PROCEDURE BUG_12490() HELP CONTENTS; +--error ER_PARSE_ERROR +CREATE FUNCTION BUG_12490() RETURNS INT HELP CONTENTS; +CREATE TABLE t_bug_12490(a int); +--error ER_SP_BADSTATEMENT +CREATE TRIGGER BUG_12490 BEFORE UPDATE ON t_bug_12490 FOR EACH ROW HELP CONTENTS; +DROP TABLE t_bug_12490; + +# +# Bug#11834 "Re-execution of prepared statement with dropped function +# crashes server". Also tests handling of prepared stmts which use +# stored functions but does not require prelocking. +# +--disable_warnings +drop function if exists bug11834_1; +drop function if exists bug11834_2; +--enable_warnings +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; +# Re-execution of statement should not crash server. +execute stmt; +drop function bug11834_1; +# Attempt to execute statement should return proper error and +# should not crash server. + +# NOTE! The error we get from the below query indicates that the sp bug11834_2 +# does not exist(this is wrong but can be accepted) +# This behaviour has been reported as bug#21294 +--error ER_SP_DOES_NOT_EXIST +execute stmt; +deallocate prepare stmt; +drop function bug11834_2; + +# +# Bug#12953 "Stored procedures: crash if OPTIMIZE TABLE in function" +# +delimiter |; +--disable_warnings +DROP FUNCTION IF EXISTS bug12953| +--enable_warnings +--error ER_SP_NO_RETSET +CREATE FUNCTION bug12953() RETURNS INT +BEGIN + OPTIMIZE TABLE t1; + RETURN 1; +END| +delimiter ;| + +# +# Bug##12995 "Inside function "Table 't4' was not locked with LOCK TABLES" +# +delimiter |; +--disable_warnings +DROP FUNCTION IF EXISTS bug12995| +--enable_warnings +--error ER_SP_BADSTATEMENT +CREATE FUNCTION bug12995() RETURNS INT +BEGIN + HANDLER t1 OPEN; + RETURN 1; +END| +--error ER_SP_BADSTATEMENT +CREATE FUNCTION bug12995() RETURNS INT +BEGIN + HANDLER t1 READ FIRST; + RETURN 1; +END| +--error ER_SP_BADSTATEMENT +CREATE FUNCTION bug12995() RETURNS INT +BEGIN + HANDLER t1 CLOSE; + RETURN 1; +END| +--error 1305 +SELECT bug12995()| +delimiter ;| + + +# +# BUG#12712: SET AUTOCOMMIT should fail within SP/functions/triggers +# +--disable_warnings +drop procedure if exists bug12712; +drop function if exists bug12712; +--enable_warnings +# Can... +create procedure bug12712() + set session autocommit = 0; + +select @@autocommit; +set @au = @@autocommit; +call bug12712(); +select @@autocommit; +set session autocommit = @au; + +delimiter |; +create function bug12712() + returns int +begin + call bug12712(); + return 0; +end| + +# Can't... +--error ER_SP_CANT_SET_AUTOCOMMIT +set @x = bug12712()| +drop procedure bug12712| +drop function bug12712| +--error ER_SP_CANT_SET_AUTOCOMMIT +create function bug12712() + returns int +begin + set session autocommit = 0; + return 0; +end| +--error ER_SP_CANT_SET_AUTOCOMMIT +create function bug12712() + returns int +begin + set @@autocommit = 0; + return 0; +end| +--error ER_SP_CANT_SET_AUTOCOMMIT +create function bug12712() + returns int +begin + set local autocommit = 0; + return 0; +end| +delimiter ;| +--error ER_SP_CANT_SET_AUTOCOMMIT +create trigger bug12712 + before insert on t1 for each row set session autocommit = 0; + +# +# BUG#9367: Stored procedures: client hang after "show warnings" +# +--disable_parsing +--disable_warnings +drop procedure if exists bug9367; +--enable_warnings +create table t1 (s1 int); +select s1 from t1; +delimiter |; +create procedure bug9367() +begin + declare v int; + declare c cursor for select s1 from t1; + open c; + show warnings; + fetch c into v; + select v; +end| +delimiter ;| +call bug9367(); +drop procedure bug9367; +drop table t1; +--enable_parsing + +# +# BUG#13510: Setting password local variable changes current password +# +delimiter |; +--disable_warnings +drop procedure if exists bug13510_1| +drop procedure if exists bug13510_2| +drop procedure if exists bug13510_3| +drop procedure if exists bug13510_4| +--enable_warnings + +--error ER_SP_BAD_VAR_SHADOW +create procedure bug13510_1() +begin + declare password varchar(10); + + set password = 'foo1'; + select password; +end| + +# Check that an error message is sent +--error ER_PARSE_ERROR +set names='foo2'| + +--error ER_SP_BAD_VAR_SHADOW +create procedure bug13510_2() +begin + declare names varchar(10); + + set names = 'foo2'; + select names; +end| + +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()| +call bug13510_4()| + +drop procedure bug13510_3| +drop procedure bug13510_4| + + +# +# Test that statements which implicitly commit transaction are prohibited +# in stored function and triggers. Attempt to create function or trigger +# containing such statement should produce error (includes test for +# bug #13627). +# +--disable_warnings +drop function if exists bug_13627_f| +--enable_warnings + +CREATE TABLE t1 (a int)| +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN DROP TRIGGER test1; END | +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE FUNCTION bug_13627_f() returns int BEGIN DROP TRIGGER test1; return 1; END | + +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create table t2 (a int); END | +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE FUNCTION bug_13627_f() returns int BEGIN create table t2 (a int); return 1; END | + +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create index t1_i on t1 (a); END | +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE FUNCTION bug_13627_f() returns int BEGIN create index t1_i on t1 (a); return 1; END | + +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN alter table t1 add column b int; END | +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE FUNCTION bug_13627_f() returns int BEGIN alter table t1 add column b int; return 1; END | + +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN rename table t1 to t2; END | +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE FUNCTION bug_13627_f() returns int BEGIN rename table t1 to t2; return 1; END | + +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN truncate table t1; END | +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE FUNCTION bug_13627_f() returns int BEGIN truncate table t1; return 1; END | + +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop table t1; END | +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE FUNCTION bug_13627_f() returns int BEGIN drop table t1; return 1; END | + +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop index t1_i on t1; END | +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE FUNCTION bug_13627_f() returns int BEGIN drop index t1_i on t1; return 1; END | + +-- error ER_SP_BADSTATEMENT +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN unlock tables; END | +-- error ER_SP_BADSTATEMENT +CREATE FUNCTION bug_13627_f() returns int BEGIN unlock tables; return 1; END | + +-- error ER_SP_BADSTATEMENT +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN LOCK TABLE t1 READ; END | +-- error ER_SP_BADSTATEMENT +CREATE FUNCTION bug_13627_f() returns int BEGIN LOCK TABLE t1 READ; return 1; END | + +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create database mysqltest; END | +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE FUNCTION bug_13627_f() returns int BEGIN create database mysqltest; return 1; END | + +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop database mysqltest; END | +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE FUNCTION bug_13627_f() returns int BEGIN drop database mysqltest; return 1; END | + +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create user 'mysqltest_1'; END | +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE FUNCTION bug_13627_f() returns int BEGIN create user 'mysqltest_1'; return 1; END | + +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE TRIGGER bug21975 BEFORE INSERT ON t1 FOR EACH ROW BEGIN grant select on t1 to 'mysqltest_1'; END | +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE FUNCTION bug21975() returns int BEGIN grant select on t1 to 'mysqltest_1'; return 1; END | + +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE TRIGGER bug21975 BEFORE INSERT ON t1 FOR EACH ROW BEGIN revoke select on t1 from 'mysqltest_1'; END | +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE FUNCTION bug21975() returns int BEGIN revoke select on t1 from 'mysqltest_1'; return 1; END | + +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE TRIGGER bug21975 BEFORE INSERT ON t1 FOR EACH ROW BEGIN revoke all privileges on *.* from 'mysqltest_1'; END | +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE FUNCTION bug21975() returns int BEGIN revoke all privileges on *.* from 'mysqltest_1'; return 1; END | + +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop user 'mysqltest_1'; END | +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE FUNCTION bug_13627_f() returns int BEGIN drop user 'mysqltest_1'; return 1; END | + +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN rename user 'mysqltest_2' to 'mysqltest_1'; END | +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE FUNCTION bug_13627_f() returns int BEGIN rename user 'mysqltest_2' to 'mysqltest_1'; return 1; END | + +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create view v1 as select 1; END | +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE FUNCTION bug_13627_f() returns int BEGIN create view v1 as select 1; return 1; END | + +-- error ER_SP_BADSTATEMENT +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN alter view v1 as select 1; END | +-- error ER_SP_BADSTATEMENT +CREATE FUNCTION bug_13627_f() returns int BEGIN alter view v1 as select 1; return 1; END | + +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop view v1; END | +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE FUNCTION bug_13627_f() returns int BEGIN drop view v1; return 1; END | + +-- error ER_SP_NO_RECURSIVE_CREATE +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 ER_SP_NO_RECURSIVE_CREATE +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 ER_SP_NO_DROP_SP +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop function bug_13627_f; END | +-- error ER_SP_NO_DROP_SP +CREATE FUNCTION bug_13627_f() returns int BEGIN drop function bug_13627_f; return 1; END | + +-- error ER_SP_NO_RECURSIVE_CREATE +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create function f2 () returns int return 1; END | +-- error ER_SP_NO_RECURSIVE_CREATE +CREATE FUNCTION bug_13627_f() returns int BEGIN create function f2 () returns int return 1; return 1; END | + +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| + +delimiter ;| + +# BUG#12329: "Bogus error msg when executing PS with stored procedure after +# SP was re-created". See also test for related bug#13399 in trigger.test +drop function if exists bug12329; +--enable_warnings +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; +drop function bug12329; +create function bug12329() returns int return (select a+100 from t2); +select bug12329(); +execute stmt1; +deallocate prepare stmt1; +drop function bug12329; +drop table t1, t2; + +# +# Bug#13514 "server crash when create a stored procedure before choose a +# database" and +# Bug#13587 "Server crash when SP is created without database +# selected" +# +create database mysqltest1; +use mysqltest1; +drop database mysqltest1; +--error ER_NO_DB_ERROR +create function f1() returns int return 1; +delimiter |; +--error ER_NO_DB_ERROR +create procedure p1(out param1 int) +begin + select count(*) into param1 from t3; +end| +delimiter ;| +use test; + + +# +# BUG#13037: undefined variable in IF cause erroneous error-message +# + +--disable_warnings +DROP PROCEDURE IF EXISTS bug13037_p1; +DROP PROCEDURE IF EXISTS bug13037_p2; +DROP PROCEDURE IF EXISTS bug13037_p3; +--enable_warnings + +delimiter |; + +--error ER_SP_UNDECLARED_VAR +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| + +delimiter ;| + +--echo + +--error 1054 +CALL bug13037_p2(); +--error 1054 +CALL bug13037_p3(); + +--error 1054 +CALL bug13037_p2(); +--error 1054 +CALL bug13037_p3(); + +DROP PROCEDURE bug13037_p2; +DROP PROCEDURE bug13037_p3; + +# +# Bug#14569 "editing a stored procedure kills mysqld-nt" +# +create database mysqltest1; +create database mysqltest2; +use mysqltest1; +drop database mysqltest1; +create procedure mysqltest2.p1() select version(); +--error ER_NO_DB_ERROR +create procedure p2() select version(); +use mysqltest2; +--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' +show procedure status; +drop database mysqltest2; +use test; + +# +# Bug#13012 "SP: REPAIR/BACKUP/RESTORE TABLE crashes the server" +# +delimiter |; +--disable_warnings +DROP FUNCTION IF EXISTS bug13012| +--enable_warnings +--error ER_SP_NO_RETSET +CREATE FUNCTION bug13012() RETURNS INT +BEGIN + REPAIR TABLE t1; + RETURN 1; +END| +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| +--error ER_SP_NO_RETSET +SELECT bug13012_2()| +drop table t1| +drop procedure bug13012_1| +drop function bug13012_2| +delimiter ;| + +# +# BUG#11555 "Stored procedures: current SP tables locking make +# impossible view security". We should not expose names of tables +# which are implicitly used by view (via stored routines/triggers). +# +# Note that SQL standard assumes that you simply won't be able drop table +# and leave some objects (routines/views/triggers) which were depending on +# it. Such objects should be dropped in advance (by default) or will be +# dropped simultaneously with table (DROP TABLE with CASCADE clause). +# So these tests probably should go away once we will implement standard +# behavior. +--disable_warnings +drop function if exists bug11555_1; +drop function if exists bug11555_2; +drop view if exists v1, v2, v3, v4; +--enable_warnings +create function bug11555_1() returns int return (select max(i) from t1); +create function bug11555_2() returns int return bug11555_1(); +# It is OK to report name of implicitly used table which is missing +# when we create view. +# For stored functions however, because of exceptions handlers, there is +# no easy way to find out if a missing table makes the view invalid. +create view v1 as select bug11555_1(); +drop view v1; +create view v2 as select bug11555_2(); +drop view v2; +# But we should hide name of missing implicitly used table when we use view +--disable_view_protocol +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; +--error ER_VIEW_INVALID +select * from v1; +--error ER_VIEW_INVALID +select * from v2; +--error ER_VIEW_INVALID +select * from v3; +# Note that creation of view which depends on broken view is yet +# another form of view usage. +create view v4 as select * from v1; +drop view v1, v2, v3, v4; +--enable_view_protocol +# We also should hide details about broken triggers which are +# invoked for view. +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; +# Limitation, the desired error is ER_VIEW_INVALID +--error ER_NO_SUCH_TABLE +insert into v1 values (1); +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(); +# Limitation, the desired error is ER_VIEW_INVALID +--error ER_NO_SUCH_TABLE +insert into v1 values (2); +drop function bug11555_1; +drop table t1; +drop view v1; + +# +# BUG#15658: Server crashes after creating function as empty string +# +--disable_warnings +drop procedure if exists ` bug15658`; +--enable_warnings + +--error ER_SP_WRONG_NAME +create procedure ``() select 1; +--error ER_SP_WRONG_NAME +create procedure ` `() select 1; +--error ER_SP_WRONG_NAME +create procedure `bug15658 `() select 1; +--error ER_WRONG_DB_NAME +create procedure ``.bug15658() select 1; +--error ER_WRONG_DB_NAME +create procedure `x `.bug15658() select 1; + +# This should work +create procedure ` bug15658`() select 1; +call ` bug15658`(); +--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' +show procedure status; +drop procedure ` bug15658`; + + +# +# BUG#14270: Stored procedures: crash if load index +# +--disable_warnings +drop function if exists bug14270; +drop table if exists t1; +--enable_warnings + +create table t1 (s1 int primary key); + +delimiter |; +--error ER_SP_NO_RETSET +create function bug14270() returns int +begin + load index into cache t1; + return 1; +end| + +--error ER_SP_NO_RETSET +create function bug14270() returns int +begin + cache index t1 key (`primary`) in keycache1; + return 1; +end| +delimiter ;| + +drop table t1; + + +# +# BUG#15091: Sp Returns Unknown error in order clause....and +# there is no order by clause +# +--disable_warnings +drop procedure if exists bug15091; +--enable_warnings + +delimiter |; +--error ER_SP_UNDECLARED_VAR +create procedure bug15091() +begin + declare selectstr varchar(6000) default ' '; + declare conditionstr varchar(5000) default ''; + + set selectstr = concat(selectstr, + ' and ', + c.operatorid, + 'in (',conditionstr, ')'); +end| +delimiter ;| + + +# +# BUG#16896: Stored function: unused AGGREGATE-clause in CREATE FUNCTION +# +--disable_warnings +drop function if exists bug16896; +--enable_warnings + +--error ER_INVALID_AGGREGATE_FUNCTION +create aggregate function bug16896() returns int return 1; + +# +# +# BUG#14702: misleading error message when syntax error in CREATE +# PROCEDURE +# +# Misleading error message was given when IF NOT EXISTS was used in +# CREATE PROCEDURE. +# +--disable_warnings +DROP PROCEDURE IF EXISTS bug14702; +--enable_warnings + +--error ER_PARSE_ERROR +CREATE IF NOT EXISTS PROCEDURE bug14702() +BEGIN +END; + +CREATE PROCEDURE IF NOT EXISTS bug14702() +BEGIN +END; +DROP PROCEDURE IF EXISTS bug14702; + +# +# BUG#20953: create proc with a create view that uses local +# vars/params should fail to create +# +# See test case for what syntax is forbidden in a view. +# +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1 (i INT); + +# We do not have to drop this procedure and view because they won't be +# created. +--error ER_PARSE_ERROR +CREATE PROCEDURE bug20953() CREATE VIEW v AS SELECT 1 INTO @a; +--error ER_PARSE_ERROR +CREATE PROCEDURE bug20953() CREATE VIEW v AS SELECT 1 INTO DUMPFILE "file"; +--error ER_PARSE_ERROR +CREATE PROCEDURE bug20953() CREATE VIEW v AS SELECT 1 INTO OUTFILE "file"; +--error ER_PARSE_ERROR +CREATE PROCEDURE bug20953() + CREATE VIEW v AS SELECT i FROM t1 PROCEDURE ANALYSE(); +--error ER_PARSE_ERROR +CREATE PROCEDURE bug20953() CREATE VIEW v AS SELECT 1 FROM (SELECT 1) AS d1 into @w; +--error ER_VIEW_SELECT_VARIABLE +CREATE PROCEDURE bug20953(i INT) CREATE VIEW v AS SELECT i; +delimiter |; +--error ER_VIEW_SELECT_VARIABLE +CREATE PROCEDURE bug20953() +BEGIN + DECLARE i INT; + CREATE VIEW v AS SELECT i; +END | +delimiter ;| +--error ER_VIEW_SELECT_VARIABLE +PREPARE stmt FROM "CREATE VIEW v AS SELECT ?"; + +DROP TABLE t1; + + +# +# BUG#24491 "using alias from source table in insert ... on duplicate key" +# +--disable_warnings +drop tables if exists t1; +drop procedure if exists bug24491; +--enable_warnings +create table t1 (id int primary key auto_increment, value varchar(10)); +insert into t1 (id, value) values (1, 'FIRST'), (2, 'SECOND'), (3, 'THIRD'); +# Let us create routine with INSERT ... SELECT ... ON DUPLICATE KEY UPDATE +# statement which in its ON DUPLICATE KEY clause erroneously tries to assign +# value to a column which is mentioned only in SELECT part. +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'; +# Both first and second calls to it should fail +--error ER_BAD_FIELD_ERROR +call bug24491(); +--error ER_BAD_FIELD_ERROR +call bug24491(); +drop procedure bug24491; +# And now the same test for more complex case which is more close +# to the one that was reported originally. +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'; +--error ER_BAD_FIELD_ERROR +call bug24491(); +--error ER_BAD_FIELD_ERROR +call bug24491(); +drop procedure bug24491; +drop tables t1; + +# +# BUG#18914: Calling certain SPs from triggers fail +# +# Failing to call a procedure that does implicit commit from a trigger +# is a correct behaviour, however the error message was misleading. +# +# DROP TABLE IF EXISTS is also fixed to give correct error instead of +# "Table doesn't exist". +# +--disable_warnings +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; +--enable_warnings + +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; + +delimiter |; +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 | +delimiter ;| + +CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW + CALL bug18914_p1(); + +--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +INSERT INTO t1 VALUES (1); + +--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +SELECT bug18914_f1(); + +--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +SELECT bug18914_f2(); + +--error ER_NO_SUCH_TABLE +SELECT * FROM t2; + +DROP FUNCTION bug18914_f1; +DROP FUNCTION bug18914_f2; +DROP PROCEDURE bug18914_p1; +DROP PROCEDURE bug18914_p2; +DROP TABLE t1; + +# +# Bug#20713 (Functions will not not continue for SQLSTATE VALUE '42S02') +# + +--disable_warnings +drop table if exists bogus_table_20713; +drop function if exists func_20713_a; +drop function if exists func_20713_b; +--enable_warnings + +create table bogus_table_20713( id int(10) not null primary key); +insert into bogus_table_20713 values (1), (2), (3); + +delimiter //; + +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// + +delimiter ;// + +set @in_func := 0; +select func_20713_a(); +select @in_func; + +set @in_func := 0; +select func_20713_b(); +select @in_func; + +drop table bogus_table_20713; + +set @in_func := 0; +select func_20713_a(); +select @in_func; + +set @in_func := 0; +select func_20713_b(); +select @in_func; + +drop function if exists func_20713_a; +drop function if exists func_20713_b; + +# +# Bug#25345 (Cursors from Functions) +# + +--disable_warnings +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; +--enable_warnings + +create table table_25345_a (a int); +create table table_25345_b (b int); + +delimiter ||; + +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 || + +delimiter ;|| + +call proc_25345(); +--error ER_SP_NO_RETSET +select func_25345(); +select func_25345_b(); + +drop table table_25345_a; + +call proc_25345(); +--error ER_SP_NO_RETSET +select func_25345(); +select func_25345_b(); + +drop table table_25345_b; +drop procedure proc_25345; +drop function func_25345; +drop function func_25345_b; + +# +# End of 5.0 tests +# +--echo End of 5.0 tests + +# +# Bug#16164 "Easter egg": check that SHOW AUTHORS is disabled in +# stored functions/triggers +# +--disable_warnings +drop function if exists bug16164; +--enable_warnings +delimiter |; +--error ER_SP_NO_RETSET +create function bug16164() returns int +begin + show authors; + return 42; +end| +delimiter ;| + + +# +# BUG#20701: BINARY keyword should be forbidden in stored routines +# +--disable_warnings +drop function if exists bug20701; +--enable_warnings +create function bug20701() returns varchar(25) binary return "test"; +drop function bug20701; +create function bug20701() returns varchar(25) return "test"; +drop function bug20701; + +# +# Bug#26503 (Illegal SQL exception handler code causes the server to crash) +# + +delimiter //; + +--error ER_SP_LILABEL_MISMATCH +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 ER_SP_LILABEL_MISMATCH +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 ER_SP_LILABEL_MISMATCH +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 ER_SP_LILABEL_MISMATCH +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// + +delimiter ;// + + +# +# Bug#28360 (RENAME DATABASE destroys routines) +# + +--disable_warnings +drop procedure if exists proc_28360; +drop function if exists func_28360; +--enable_warnings + +delimiter //; + +--error ER_SP_NO_DROP_SP +CREATE PROCEDURE proc_28360() +BEGIN + ALTER DATABASE `#mysql50#upgrade-me` UPGRADE DATA DIRECTORY NAME; +END// + +--error ER_SP_NO_DROP_SP +CREATE FUNCTION func_28360() RETURNS int +BEGIN + ALTER DATABASE `#mysql50#upgrade-me` UPGRADE DATA DIRECTORY NAME; + RETURN 0; +END// + +delimiter ;// + + +# +# Bug#29223 declare cursor c for SHOW ..... +# + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +--enable_warnings +--delimiter | +--error ER_PARSE_ERROR +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| +--delimiter ; + +# +# Bug#29816 Syntactically wrong query fails with misleading error message +# + +--disable_warnings +DROP DATABASE IF EXISTS mysqltest; +--enable_warnings +CREATE DATABASE mysqltest; +USE mysqltest; +DROP DATABASE mysqltest; +# Both ER_SP_DOES_NOT_EXIST and ER_PARSE_ERROR are valid here, +# the result is implementation dependent: +# See Bug#29816 for details +--error ER_SP_DOES_NOT_EXIST +SELECT inexistent(), 1 + ,; +--error ER_SP_DOES_NOT_EXIST +SELECT inexistent(); +--error ER_PARSE_ERROR +SELECT .inexistent(); +--error ER_PARSE_ERROR +SELECT ..inexistent(); +USE test; + +# +# Bug#30904 SET PASSWORD statement is non-transactional +# + +delimiter |; + +--error ER_SP_CANT_SET_AUTOCOMMIT +create function f1() returns int +begin + set @test = 1, password = password('foo'); + return 1; +end| + +--error ER_SP_CANT_SET_AUTOCOMMIT +create trigger t1 + before insert on t2 for each row set password = password('foo');| + +delimiter ;| + +# +# Bug#30882 Dropping a temporary table inside a stored function may cause a server crash +# + +--disable_warnings +drop function if exists f1; +drop function if exists f2; +drop table if exists t1, t2; +--enable_warnings + +delimiter |; +create function f1() returns int +begin + drop temporary table t1; + return 1; +end| +delimiter ;| +--error ER_BAD_TABLE_ERROR +create temporary table t1 as select f1(); + +delimiter |; +create function f2() returns int +begin + create temporary table t2 as select f1(); + return 1; +end| +delimiter ;| +--error ER_BAD_TABLE_ERROR +create temporary table t1 as select f2(); + +drop function f1; +drop function f2; + +delimiter |; +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| +delimiter ;| +--error ER_BAD_TABLE_ERROR +create temporary table t1 as select f2(); + +drop function f1; +drop function f2; + +create temporary table t2(a int); +select * from t2; +delimiter |; +create function f2() returns int +begin + drop temporary table t2; + return 1; +end| +delimiter ;| +--disable_ps2_protocol +select f2(); +--enable_ps2_protocol + +drop function f2; +--error ER_BAD_TABLE_ERROR +drop table t2; + +--echo End of 5.1 tests + +# +# Bug#33983 (Stored Procedures: wrong end <label> syntax is accepted) +# + +--disable_warnings +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; +--enable_warnings + +delimiter |; + +--error ER_SP_LABEL_MISMATCH +create procedure proc_33983_a() +begin + label1: + begin + label2: + begin + select 1; + end label1; + end; +end| + +--error ER_SP_LABEL_MISMATCH +create procedure proc_33983_b() +begin + label1: + repeat + label2: + repeat + select 1; + until FALSE end repeat label1; + until FALSE end repeat; +end| + +--error ER_SP_LABEL_MISMATCH +create procedure proc_33983_c() +begin + label1: + while TRUE do + label2: + while TRUE do + select 1; + end while label1; + end while; +end| + +--error ER_SP_LABEL_MISMATCH +create procedure proc_33983_d() +begin + label1: + loop + label2: + loop + select 1; + end loop label1; + end loop; +end| + +CREATE TABLE t1 (a INT)| +INSERT INTO t1 VALUES (1),(2)| +CREATE PROCEDURE p1(a INT) BEGIN END| +--error ER_SUBQUERY_NO_1_ROW +CALL p1((SELECT * FROM t1))| +DROP PROCEDURE IF EXISTS p1| +DROP TABLE t1| + +delimiter ;| + +# +# Bug#21801: SQL exception handlers and warnings +# + +--disable_warnings +drop procedure if exists p1; +--enable_warnings +delimiter |; +create procedure p1() +begin + create table t1 (a int) engine=MyISAM; + drop table t1; +end| +delimiter ;| +call p1(); +call p1(); +drop procedure p1; + +# +# Bug#8759 (Stored Procedures: SQLSTATE '00000' should be illegal) +# + +--disable_warnings +drop procedure if exists proc_8759; +--enable_warnings + +delimiter $$; + +--error ER_SP_BAD_SQLSTATE +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 ER_SP_BAD_SQLSTATE +create procedure proc_8759() +begin + declare continue handler for sqlstate '00000' set @x=0; +end$$ + +delimiter ;$$ + +# +# Bug#36510 (Stored Procedures: mysql_error_code 0 should be illegal) +# + +--disable_warnings +drop procedure if exists proc_36510; +--enable_warnings + +delimiter $$; + +--error ER_SP_BAD_SQLSTATE +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 ER_SP_BAD_SQLSTATE +create procedure proc_36510() +begin + declare continue handler for sqlstate '00123' set @x=0; +end$$ + +--error ER_WRONG_VALUE +create procedure proc_36510() +begin + declare should_be_illegal condition for 0; + declare continue handler for should_be_illegal set @x=0; +end$$ + +--error ER_WRONG_VALUE +create procedure proc_36510() +begin + declare continue handler for 0 set @x=0; +end$$ +delimiter ;$$ + +# +# Bug#15192: "fatal errors" are caught by handlers in stored procedures +# + +--disable_warnings +drop procedure if exists p1; +--enable_warnings +set @old_recursion_depth = @@max_sp_recursion_depth; +set @@max_sp_recursion_depth = 255; +delimiter |; +create procedure p1(a int) +begin + declare continue handler for 1436 -- ER_STACK_OVERRUN_NEED_MORE + select 'exception'; + call p1(a+1); +end| +delimiter ;| +--error 0,ER_STACK_OVERRUN_NEED_MORE,ER_SP_RECURSION_LIMIT +call p1(1); +set @@max_sp_recursion_depth = @old_recursion_depth; +drop procedure p1; + +# +# BUG#NNNN: New bug synopsis +# +#--disable_warnings +#drop procedure if exists bugNNNN; +#drop function if exists bugNNNN; +#--enable_warnings +#create procedure bugNNNN... +#create function bugNNNN... + +# +# CLEANUP and RESTORE +# +LOAD DATA INFILE '../../tmp/proc.txt' INTO TABLE mysql.proc; +remove_file $MYSQLTEST_VARDIR/tmp/proc.txt; + +# +# Bug #38159: Function parsing problem generates misleading error message +# + +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,1), (2,2); +--error ER_FUNC_INEXISTENT_NAME_COLLISION +SELECT MAX (a) FROM t1 WHERE b = 999999; +SELECT AVG (a) FROM t1 WHERE b = 999999; +--error ER_SP_DOES_NOT_EXIST +SELECT non_existent (a) FROM t1 WHERE b = 999999; +DROP TABLE t1; + + +# +# Bug #46374 crash, INSERT INTO t1 uses function, function modifies t1 +# +CREATE TABLE t1 ( f2 INTEGER, f3 INTEGER ); +INSERT INTO t1 VALUES ( 1, 1 ); + +delimiter |; + +CREATE FUNCTION func_1 () RETURNS INTEGER +BEGIN + INSERT INTO t1 SELECT * FROM t1 ; + RETURN 1 ; +END| + +delimiter ;| + +# The bug caused the following INSERT statement to trigger +# an assertion. Error 1442 is the correct response +# +--error 1442 +INSERT INTO t1 SELECT * FROM (SELECT 2 AS f1, 2 AS f2) AS A WHERE func_1() = 5; + +# Cleanup +DROP FUNCTION func_1; +DROP TABLE t1; + + + +--echo # +--echo # Bug #47788: Crash in TABLE_LIST::hide_view_error on UPDATE + VIEW + +--echo # SP + MERGE + ALTER +--echo # + +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; + +--error ER_NON_UPDATABLE_TABLE +CALL p1(5); + +ALTER TABLE t1 CHANGE COLUMN b b2 INT; + +--error ER_VIEW_INVALID +CALL p1(7); + +DROP PROCEDURE p1; +DROP VIEW v1; +DROP TABLE t1; + +--echo # +--echo # Bug#12428824 - PARSER STACK OVERFLOW AND CRASH IN SP_ADD_USED_ROUTINE +--echo # WITH OBSCURE QUERY +--echo # + +--error ER_TOO_LONG_IDENT +SELECT very_long_fn_name_1111111111111111111111111111111111111111111111111111111111111111111111111222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222225555555555555555555555555577777777777777777777777777777777777777777777777777777777777777777777777788888888999999999999999999999(); + +--error ER_TOO_LONG_IDENT +CALL very_long_pr_name_1111111111111111111111111111111111111111111111111111111111111111111111111222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222225555555555555555555555555577777777777777777777777777777777777777777777777777777777777777777777777788888888999999999999999999999(); + +--error ER_WRONG_DB_NAME +SELECT very_long_db_name_1111111111111111111111111111111111111111111111111111111111111111111111111222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222225555555555555555555555555577777777777777777777777777777777777777777777777777777777777777777777777788888888999999999999999999999.simple_func(); + +--error ER_WRONG_DB_NAME +CALL very_long_db_name_1111111111111111111111111111111111111111111111111111111111111111111111111222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222225555555555555555555555555577777777777777777777777777777777777777777777777777777777777777777777777788888888999999999999999999999.simple_proc(); + +--error ER_TOO_LONG_IDENT +SELECT db_name.very_long_fn_name_111111111111111111111111111111111111111111111111111111111111111111111111122222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222999999999999999999999(); + +--error ER_TOO_LONG_IDENT +CALL db_name.very_long_pr_name_111111111111111111111111111111111111111111111111111111111111111111111111122222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222999999999999999999999(); +--echo End of 5.1 tests + +--echo # +--echo # Bug#23032: Handlers declared in a SP do not handle warnings generated in sub-SP +--echo # + +--echo +--echo # - Case 1 +--echo + +--disable_warnings +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; +--enable_warnings + +delimiter |; + +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| + +delimiter ;| + +CALL p1(); + +DROP PROCEDURE p1; +DROP PROCEDURE p2; + +--echo +--echo # - Case 2 +--echo + +delimiter |; + +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| + +delimiter ;| + +CALL p1(); +CALL p6(); + +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP PROCEDURE p3; +DROP PROCEDURE p4; +DROP PROCEDURE p5; +DROP PROCEDURE p6; + +--echo +--echo # - Case 3: check that "Exception trumps No Data". +--echo + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (1), (2), (3); + +delimiter |; + +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| + +delimiter ;| + +CALL p1(); + +DROP PROCEDURE p1; +DROP TABLE t1; + +--echo # +--echo # Bug#36185: Incorrect precedence for warning and exception handlers +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP PROCEDURE IF EXISTS p1; +--enable_warnings + +CREATE TABLE t1 (a INT, b INT NOT NULL); + +delimiter |; + +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| + +delimiter ;| + +CALL p1(); + +DROP TABLE t1; +DROP PROCEDURE p1; + +--echo # +--echo # Bug#5889: Exit handler for a warning doesn't hide the warning in trigger +--echo # +SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1, 2); + +delimiter |; + +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| + +delimiter ;| + +UPDATE t1 SET b = 20; + +SHOW WARNINGS; + +SELECT * FROM t1; + +DROP TRIGGER t1_bu; +DROP TABLE t1; +SET sql_mode = DEFAULT; + +--echo # +--echo # Bug#9857: Stored procedures: handler for sqlwarning ignored +--echo # + +SET @sql_mode_saved = @@sql_mode; +SET sql_mode = traditional; + +delimiter |; + +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| + +delimiter ;| + +CALL p1(); +SHOW WARNINGS; + +CALL p2(); +SHOW WARNINGS; + +DROP PROCEDURE p1; +DROP PROCEDURE p2; +SET sql_mode = @sql_mode_saved; + +--echo # +--echo # Bug#55850: Trigger warnings not cleared. +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t2; +DROP PROCEDURE IF EXISTS p1; +--enable_warnings + +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); + +# What happened before the patch was: +# - INSERT INTO t1 added 3 warnings about overflow in 'x', 'y' and 'z' columns; +# - t1_bi run and added 3 warnings about overflow in 'a', 'b' and 'c' columns; +# - t1_ai run and added 3 warnings about overflow in 'd', 'e' and 'f' columns; +# => we had 9 warnings. +# +# Now what happens is: +# - INSERT INTO t1 adds 3 warnings about overflow in 'x', 'y' and 'z' columns; +# - t1_bi adds 3 warnings about overflow in 'a', 'b' and 'c' columns; +# - The warnings added by triggers are cleared; +# - t1_ai run and added 3 warnings about overflow in 'd', 'e' and 'f' columns; +# - The warnings added by triggers are cleared; +# => we have 3 warnings. + +--echo +CALL p1(); + +--echo +SHOW WARNINGS; + +--echo +DROP TABLE t1; +DROP TABLE t2; +DROP PROCEDURE p1; + +--echo # ---------------------------------------------------------------------- +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); + +delimiter |; + +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| + +delimiter ;| + +CREATE PROCEDURE p1() + INSERT INTO t1 VALUES(99999, 99999, 99999); + +--echo +--error ER_BAD_NULL_ERROR +CALL p1(); + +--echo +SHOW WARNINGS; + +--echo +DROP TABLE t1; +DROP TABLE t2; +DROP PROCEDURE p1; +SET sql_mode = DEFAULT; + +--echo +--echo ################################################################### +--echo # Tests for the following bugs: +--echo # - Bug#11763171: 55852 - Possibly inappropriate handler activation. +--echo # - Bug#11749343: 38806 - Wrong scope for SQL HANDLERS in SP. +--echo ################################################################### +--echo + +# +# Structure of SQL-block: +# BEGIN +# <Handler declaration block> +# <Statement block> +# END +# +# Scope of Handler-decl-block is Statement-block. +# I.e. SQL-conditions thrown in the Handler-decl-block can not be handled by +# the same block, only by outer SQL-blocks. +# +# This rule is recursive, i.e. if a Handler-decl-block has nested SQL-blocks, +# the SQL-conditions from those nested blocks can not be handled by the this +# Handler-decl-block, only by outer SQL-blocks. +# + +delimiter |; + +--echo +--echo # -- Check that SQL-conditions thrown by Statement-blocks are +--echo # -- handled by Handler-decl blocks properly. +--echo + +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| + +--echo +CALL p1()| + +--echo +--echo # -- Check that SQL-conditions thrown by Statement-blocks are +--echo # -- handled by Handler-decl blocks properly in case of nested +--echo # -- SQL-blocks. +--echo + +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| + +--echo +CALL p2()| + +--echo +--echo # -- Check SQL-handler resolution rules. +--echo + +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| + +--echo +CALL p3()| +--echo + +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| + +--echo +CALL p4()| +--echo + +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| + +--echo +CALL p5()| + +--echo +--echo # -- Check that handlers don't handle its own exceptions. +--echo + +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| + +--echo +--error ER_SIGNAL_EXCEPTION +CALL p6()| + +--echo +--echo # -- Check that handlers don't handle its own warnings. +--echo + +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| + +--echo +CALL p7()| + +--echo +--echo # -- Check that conditions for handlers are not handled by the handlers +--echo # -- from the same block. +--echo + +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| + +--echo +CALL p8()| + +--echo +--echo # -- Check that conditions for handlers are not handled by the handlers +--echo # -- from the same block even if they are thrown deep down the stack. +--echo + +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| + +--echo +CALL p9()| + +--echo +--echo # -- Check that handlers are chosen properly in case of deep stack and +--echo # -- nested SQL-blocks. +--echo + +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| + +--echo +CALL p10()| + +--echo +--echo # -- Test stored procedure from Peter's mail. +--echo + +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| + +--echo +CALL p11()| + +--echo +--echo # -- Check that runtime stack-trace can be deeper than parsing-time one. +--echo + +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| + +--echo +CALL p12()| + +--echo +--echo # -- Check that handler-call-frames are removed properly for EXIT +--echo # -- handlers. +--echo + +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| + +--echo +CALL p13()| + +delimiter ;| + +--echo +--echo # That's it. Cleanup. +--echo + +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; + +--echo +--echo # Bug#12731619: NESTED SP HANDLERS CAN TRIGGER ASSERTION +--echo + +--disable_ps2_protocol +--disable_warnings +DROP FUNCTION IF EXISTS f1; +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1(msg VARCHAR(255)); + +delimiter |; +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| + +delimiter ;| + +--echo +SELECT f1(); +--echo +SELECT * FROM t1; +--echo + +DROP FUNCTION f1; +DROP TABLE t1; +--enable_ps2_protocol + +--echo +--echo # Check that handled SQL-conditions are properly cleared from DA. +--echo + +--disable_warnings +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; +--enable_warnings + +CREATE TABLE t1(a CHAR, b CHAR, c CHAR); +CREATE TABLE t2(a SMALLINT, b SMALLINT, c SMALLINT); + +delimiter |; + +--echo +--echo # Check that SQL-conditions for which SQL-handler has been invoked, +--echo # are cleared from the Diagnostics Area. Note, there might be several +--echo # SQL-conditions, but SQL-handler must be invoked only once. +--echo + +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| + +--echo +CALL p1()| +--echo +SELECT * FROM t1| + +--echo +--echo # Check that SQL-conditions for which SQL-handler has *not* been +--echo # invoked, are *still* cleared from the Diagnostics Area. +--echo + +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| + +--echo +CALL p2()| + +--echo +--echo # Check that if there are two equally ranked SQL-handlers to handle +--echo # SQL-conditions from SQL-statement, only one of them will be invoked. +--echo + +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| + +--echo +CALL p3()| + +--echo +--echo # The same as p3, but 1264 comes first. +--echo + +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| + +--echo +CALL p4()| + +--echo +--echo # Check that if a SQL-handler raised its own SQL-conditions, there are +--echo # preserved after handler exit. +--echo + +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| + +--echo +CALL p5()| + +--echo +--echo # Check that SQL-conditions are available inside the handler, but +--echo # cleared after the handler exits. +--echo + +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| + +--echo +CALL p6()| + +delimiter ;| + +--echo +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP PROCEDURE p3; +DROP PROCEDURE p4; +DROP PROCEDURE p5; +DROP PROCEDURE p6; +DROP TABLE t1; +DROP TABLE t2; + +--echo +--echo # Bug#13059316: ASSERTION FAILURE IN SP_RCONTEXT.CC +--echo # Check DECLARE statements that raise conditions before handlers +--echo # are declared. +--echo + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p2; +--enable_warnings + +delimiter |; + +SET sql_mode = ''| +CREATE PROCEDURE p1() +BEGIN + DECLARE var1 INTEGER DEFAULT 'string'; + DECLARE EXIT HANDLER FOR SQLWARNING SELECT 'H1'; +END| + +--echo +CALL p1()| +--echo +SET sql_mode = DEFAULT| + +CREATE PROCEDURE p2() +BEGIN + DECLARE EXIT HANDLER FOR SQLWARNING SELECT 'H2'; + CALL p1(); +END| + +--echo +CALL p2()| + +delimiter ;| + +--echo +DROP PROCEDURE p1; +DROP PROCEDURE p2; + + +--echo # +--echo # Bug#13113222 RQG_SIGNAL_RESIGNAL FAILED WITH ASSERTION. +--echo # + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p2; +--enable_warnings + +delimiter |; +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| +delimiter ;| + +SET @old_max_error_count= @@session.max_error_count; +SET SESSION max_error_count= 0; +CALL p1(); +CALL p2(); +SET SESSION max_error_count= @old_max_error_count; + +DROP PROCEDURE p1; +DROP PROCEDURE p2; + +--echo +--echo # Bug#12652873: 61392: Continue handler for NOT FOUND being triggered +--echo # from internal stored function. +--echo + +--disable_warnings +DROP FUNCTION IF EXISTS f1; +DROP FUNCTION IF EXISTS f2; +DROP TABLE IF EXISTS t1; +--enable_warnings + +--echo + +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1, 2); + +delimiter |; + +--echo +--echo # f1() raises NOT_FOUND condition. +--echo # Raising NOT_FOUND can not be simulated by SIGNAL, +--echo # because SIGNAL would raise SQL-error in that case. +--echo + +--enable_prepare_warnings +CREATE FUNCTION f1() RETURNS INTEGER +BEGIN + DECLARE v VARCHAR(5) DEFAULT -1; + SELECT b FROM t1 WHERE a = 2 INTO v; + RETURN v; +END| +--disable_prepare_warnings + +--echo +--echo # Here we check that the NOT_FOUND condition raised in f1() +--echo # is not visible in the outer function (f2), i.e. the continue +--echo # handler in f2() will not be called. +--echo + +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| + +delimiter ;| + +SET @msg = ''; + +--echo +SELECT f2(); +--echo +SELECT @msg; +--echo + +DROP FUNCTION f1; +DROP FUNCTION f2; +DROP TABLE t1; + +--echo # +--echo # 10.2 Test +--echo # +--echo # MDEV-MDEV-22058: Assertion `!is_set() || (m_status == DA_OK_BULK && +--echo # is_bulk_op())' failed in Diagnostics_area::set_ok_status +--echo # + +SET @old_optimizer_switch = @@SESSION.OPTIMIZER_SWITCH; + +SET @cmd:="SET @@SESSION.SQL_MODE=(SELECT 'a')"; + +--error ER_ILLEGAL_SUBQUERY_OPTIMIZER_SWITCHES +SET @@SESSION.OPTIMIZER_SWITCH="in_to_exists=OFF,materialization=OFF"; +PREPARE stmt FROM @cmd; + +set @@SESSION.OPTIMIZER_SWITCH=@old_optimizer_switch; + +--echo # End of 10.2 Test |