diff options
Diffstat (limited to 'mysql-test/t/sp_trans.test')
| -rw-r--r-- | mysql-test/t/sp_trans.test | 180 |
1 files changed, 180 insertions, 0 deletions
diff --git a/mysql-test/t/sp_trans.test b/mysql-test/t/sp_trans.test index 82e1cd2f1c9..d72eaf5dca0 100644 --- a/mysql-test/t/sp_trans.test +++ b/mysql-test/t/sp_trans.test @@ -176,6 +176,186 @@ drop table t1, t2| # +# BUG#13825 "Triggers: crash if release savepoint". +# Also general test for handling of savepoints in stored routines. +# +# According to SQL standard we should establish new savepoint +# level before executing stored function/trigger and destroy +# this savepoint level after execution. Stored procedures by +# default should be executed using the same savepoint level +# as their caller (to execute stored procedure using new +# savepoint level one should explicitly specify NEW SAVEPOINT +# LEVEL clause in procedure creation statement which MySQL +# does not support yet). +--disable_warnings +drop function if exists bug13825_0| +drop function if exists bug13825_1| +drop function if exists bug13825_2| +drop function if exists bug13825_3| +drop function if exists bug13825_4| +drop function if exists bug13825_5| +drop procedure if exists bug13825_0| +drop procedure if exists bug13825_1| +drop procedure if exists bug13825_2| +drop table if exists t1| +--enable_warnings +create table t1 (i int) engine=innodb| +create table t2 (i int) engine=innodb| +create function bug13825_0() returns int +begin + rollback to savepoint x; + return 1; +end| +create function bug13825_1() returns int +begin + release savepoint x; + return 1; +end| +create function bug13825_2() returns int +begin + insert into t1 values (2); + savepoint x; + insert into t1 values (3); + rollback to savepoint x; + insert into t1 values (4); + return 1; +end| +create procedure bug13825_0() +begin + rollback to savepoint x; +end| +create procedure bug13825_1() +begin + release savepoint x; +end| +create procedure bug13825_2() +begin + savepoint x; +end| +insert into t2 values (1)| +create trigger t2_bi before insert on t2 for each row + rollback to savepoint x| +create trigger t2_bu before update on t2 for each row + release savepoint x| +create trigger t2_bd before delete on t2 for each row +begin + insert into t1 values (2); + savepoint x; + insert into t1 values (3); + rollback to savepoint x; + insert into t1 values (4); +end| +create function bug13825_3(rb int) returns int +begin + insert into t1 values(1); + savepoint x; + insert into t1 values(2); + if rb then + rollback to savepoint x; + end if; + insert into t1 values(3); + return rb; +end| +create function bug13825_4() returns int +begin + savepoint x; + insert into t1 values(2); + rollback to savepoint x; + return 0; +end| +create function bug13825_5(p int) returns int +begin + savepoint x; + insert into t2 values(p); + rollback to savepoint x; + insert into t2 values(p+1); + return p; +end| +set autocommit= 0| +# Test of savepoint level handling for stored functions and triggers +begin | +insert into t1 values (1)| +savepoint x| +--error ER_SP_DOES_NOT_EXIST +set @a:= bug13825_0()| +--error ER_SP_DOES_NOT_EXIST +insert into t2 values (2)| +--error ER_SP_DOES_NOT_EXIST +set @a:= bug13825_1()| +--error ER_SP_DOES_NOT_EXIST +update t2 set i = 2| +set @a:= bug13825_2()| +select * from t1| +rollback to savepoint x| +select * from t1| +delete from t2| +select * from t1| +rollback to savepoint x| +select * from t1| +# Of course savepoints set in function should not be visible from its caller +release savepoint x| +set @a:= bug13825_2()| +select * from t1| +--error ER_SP_DOES_NOT_EXIST +rollback to savepoint x| +delete from t1| +commit| +# Test of savepoint level handling for stored procedures +begin| +insert into t1 values (5)| +savepoint x| +insert into t1 values (6)| +call bug13825_0()| +select * from t1| +call bug13825_1()| +--error ER_SP_DOES_NOT_EXIST +rollback to savepoint x| +savepoint x| +insert into t1 values (7)| +call bug13825_2()| +rollback to savepoint x| +select * from t1| +delete from t1| +commit| +set autocommit= 1| +# Let us test that savepoints work inside of functions +# even in auto-commit mode +select bug13825_3(0)| +select * from t1| +delete from t1| +select bug13825_3(1)| +select * from t1| +delete from t1| +# Curious case: rolling back to savepoint which is set by first +# statement in function should not rollback whole transaction. +set autocommit= 0| +begin| +insert into t1 values (1)| +set @a:= bug13825_4()| +select * from t1| +delete from t1| +commit| +set autocommit= 1| +# Other curious case: savepoint in the middle of statement +drop table t2| +create table t2 (i int) engine=innodb| +insert into t1 values (1), (bug13825_5(2)), (3)| +select * from t1| +select * from t2| +# Cleanup +drop function bug13825_0| +drop function bug13825_1| +drop function bug13825_2| +drop function bug13825_3| +drop function bug13825_4| +drop function bug13825_5| +drop procedure bug13825_0| +drop procedure bug13825_1| +drop procedure bug13825_2| +drop table t1, t2| + + +# # BUG#NNNN: New bug synopsis # #--disable_warnings |