EVOLUTION-MANAGER

Edit File: savepoint-3.test

--source include/have_tokudb.inc
SET DEFAULT_STORAGE_ENGINE = 'TokuDB';

delimiter |;

#
# 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,t2|
--enable_warnings
create table t1 (i int) |
create table t2 (i int) |
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) |
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|

delimiter ;|