path: root/mysql-test/r/sp_trans.result

set SQL_MODE="";
drop table if exists t1, t2, t3;
drop procedure if exists bug8850|
create table t1 (a int) engine=innodb|
create procedure bug8850()
begin
truncate table t1; insert t1 values (1); rollback;
end|
set autocommit=0|
insert t1 values (2)|
call bug8850()|
commit|
select * from t1|
a
call bug8850()|
set autocommit=1|
select * from t1|
a
drop table t1|
drop procedure bug8850|
drop function if exists bug10015_1|
drop function if exists bug10015_2|
drop function if exists bug10015_3|
drop function if exists bug10015_4|
drop function if exists bug10015_5|
drop function if exists bug10015_6|
drop function if exists bug10015_7|
drop procedure if exists bug10015_8|
create table t1 (id int) engine=innodb|
create table t2 (id int primary key, j int) engine=innodb|
insert into t1 values (1),(2),(3)|
create function bug10015_1() returns int return (select count(*) from t1)|
select *, bug10015_1() from t1|
id	bug10015_1()
1	3
2	3
3	3
drop function bug10015_1|
create function bug10015_2() returns int 
begin
declare i, s int;
set i:= (select min(id) from t1);
set s:= (select max(id) from t1);
return (s - i);
end|
select *, bug10015_2() from t1|
id	bug10015_2()
1	2
2	2
3	2
drop function bug10015_2|
create function bug10015_3() returns int 
return (select max(a.id - b.id) from t1 as a, t1 as b where a.id >= b.id)|
select *, bug10015_3() from t1|
id	bug10015_3()
1	2
2	2
3	2
drop function bug10015_3|
create function bug10015_4(i int) returns int 
begin
declare m int;
set m:= (select max(id) from t2);
insert into t2 values (i, m);
return m;
end|
select *, bug10015_4(id) from t1|
id	bug10015_4(id)
1	NULL
2	1
3	2
select * from t2|
id	j
1	NULL
2	1
3	2
drop function bug10015_4|
create function bug10015_5(i int) returns int
begin
if (i = 5) then
insert into t2 values (1, 0);
end if;
return i;
end|
insert into t1 values (bug10015_5(4)), (bug10015_5(5))|
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
select * from t1|
id
1
2
3
drop function bug10015_5|
create function bug10015_6(i int) returns int
begin
declare continue handler for sqlexception set @error_in_func:= 1;
if (i = 5) then
insert into t2 values (4, 0), (1, 0);
end if;
return i;
end|
set @error_in_func:= 0|
insert into t1 values (bug10015_6(5)), (bug10015_6(6))|
select @error_in_func|
@error_in_func
1
select * from t1|
id
1
2
3
5
6
select * from t2|
id	j
1	NULL
2	1
3	2
4	0
drop function bug10015_6|
create function bug10015_7() returns int
begin
alter table t1 add k int;
return 1;
end|
ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger
create function bug10015_7() returns int
begin
start transaction;
return 1;
end|
ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger
create function bug10015_7() returns int
begin
drop table t1;
return 1;
end|
ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger
create function bug10015_7() returns int
begin
drop temporary table t1;
return 1;
end|
drop function bug10015_7|
create function bug10015_7() returns int
begin
commit;
return 1;
end|
ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger
create function bug10015_7() returns int
begin
call bug10015_8();
return 1;
end|
create procedure bug10015_8() alter table t1 add k int|
select *, bug10015_7() from t1|
ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger
drop procedure bug10015_8|
create procedure bug10015_8() start transaction|
select *, bug10015_7() from t1|
ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger
drop procedure bug10015_8|
create procedure bug10015_8() drop temporary table if exists t1_temp|
select *, bug10015_7() from t1|
id	bug10015_7()
1	1
2	1
3	1
5	1
6	1
drop procedure bug10015_8|
create procedure bug10015_8() commit|
select *, bug10015_7() from t1|
ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger
drop procedure bug10015_8|
drop function bug10015_7|
drop table t1, t2|
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|
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|
begin |
insert into t1 values (1)|
savepoint x|
set @a:= bug13825_0()|
ERROR 42000: SAVEPOINT x does not exist
insert into t2 values (2)|
ERROR 42000: SAVEPOINT x does not exist
set @a:= bug13825_1()|
ERROR 42000: SAVEPOINT x does not exist
update t2 set i = 2|
ERROR 42000: SAVEPOINT x does not exist
set @a:= bug13825_2()|
select * from t1|
i
1
2
4
rollback to savepoint x|
select * from t1|
i
1
delete from t2|
select * from t1|
i
1
2
4
rollback to savepoint x|
select * from t1|
i
1
release savepoint x|
set @a:= bug13825_2()|
select * from t1|
i
1
2
4
rollback to savepoint x|
ERROR 42000: SAVEPOINT x does not exist
delete from t1|
commit|
begin|
insert into t1 values (5)|
savepoint x|
insert into t1 values (6)|
call bug13825_0()|
select * from t1|
i
5
call bug13825_1()|
rollback to savepoint x|
ERROR 42000: SAVEPOINT x does not exist
savepoint x|
insert into t1 values (7)|
call bug13825_2()|
rollback to savepoint x|
select * from t1|
i
5
7
delete from t1|
commit|
set autocommit= 1|
select bug13825_3(0)|
bug13825_3(0)
0
select * from t1|
i
1
2
3
delete from t1|
select bug13825_3(1)|
bug13825_3(1)
1
select * from t1|
i
1
3
delete from t1|
set autocommit= 0|
begin|
insert into t1 values (1)|
set @a:= bug13825_4()|
select * from t1|
i
1
delete from t1|
commit|
set autocommit= 1|
drop table t2|
create table t2 (i int) engine=innodb|
insert into t1 values (1), (bug13825_5(2)), (3)|
select * from t1|
i
1
2
3
select * from t2|
i
3
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|
drop table if exists t3|
drop procedure if exists bug14840_1|
drop procedure if exists bug14840_2|
create table t3
(
x int,
y int,
primary key (x)
) engine=InnoDB|
create procedure bug14840_1()
begin
declare err int default 0;
declare continue handler for sqlexception
set err = err + 1;
start transaction;
update t3 set x = 1, y = 42 where x = 2;
insert into t3 values (3, 4711);
if err > 0 then
rollback;
else
commit;
end if;
select * from t3;
end|
create procedure bug14840_2()
begin
declare err int default 0;
declare continue handler for sqlexception
begin
set err = err + 1;
select err as 'Ping';
end;
update t3 set x = 1, y = 42 where x = 2;
update t3 set x = 1, y = 42 where x = 2;
insert into t3 values (3, 4711);
select * from t3;
end|
insert into t3 values (1, 3), (2, 5)|
call bug14840_1()|
x	y
1	3
2	5
delete from t3|
insert into t3 values (1, 3), (2, 5)|
call bug14840_2()|
Ping
1
Ping
2
x	y
1	3
2	5
3	4711
drop procedure bug14840_1|
drop procedure bug14840_2|
drop table t3|
drop procedure if exists bug10656_create_index|
drop procedure if exists bug10656_myjoin|
drop procedure if exists bug10656_truncate_table|
CREATE TABLE t3 (
`ID` int(11) default NULL,
`txt` char(5) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1|
INSERT INTO t3 (`ID`,`txt`) VALUES
(1,'a'), (2,'b'), (3,'c'), (4,'d')|
CREATE TABLE t4 (
`ID` int(11) default NULL,
`txt` char(5) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1|
INSERT INTO t4 (`ID`,`txt`) VALUES
(1,'a'), (2,'b'), (3,'c'), (4,'d')|
create procedure bug10656_create_index()
begin
create index bug10656_my_index on t3 (ID);
end|
call bug10656_create_index()|
create procedure bug10656_myjoin()
begin
update t3, t4 set t3.txt = t4.txt where t3.id = t4.id;
end|
call bug10656_myjoin()|
create procedure bug10656_truncate_table()
begin
truncate table t3;
end|
call bug10656_truncate_table()|
drop procedure bug10656_create_index|
drop procedure bug10656_myjoin|
drop procedure bug10656_truncate_table|
drop table t3, t4|
create table t3 (
a int primary key,
ach char(1)
) engine = innodb|
create table t4 (
b int  primary key,
bch char(1)
) engine = innodb|
insert into t3 values (1 , 'aCh1' ) , ('2' , 'aCh2')|
Warnings:
Warning	1265	Data truncated for column 'ach' at row 1
Warning	1265	Data truncated for column 'ach' at row 2
insert into t4 values (1 , 'bCh1' )|
Warnings:
Warning	1265	Data truncated for column 'bch' at row 1
drop procedure if exists bug3448|
create procedure bug3448()
select * from t3 inner join t4 on t3.a = t4.b|
select * from t3 inner join t4 on t3.a = t4.b|
a	ach	b	bch
1	a	1	b
call bug3448()|
a	ach	b	bch
1	a	1	b
call bug3448()|
a	ach	b	bch
1	a	1	b
drop procedure bug3448|
drop table t3, t4|
drop procedure if exists bug14210|
set @@session.max_heap_table_size=16384|
select @@session.max_heap_table_size|
@@session.max_heap_table_size
16384
create table t3 (a char(255)) engine=InnoDB|
create procedure bug14210_fill_table()
begin
declare table_size, max_table_size int default 0;
select @@session.max_heap_table_size into max_table_size;
delete from t3;
insert into t3 (a) values (repeat('a', 255));
repeat
insert into t3 select a from t3;
select count(*)*255 from t3 into table_size;
until table_size > max_table_size*2 end repeat;
end|
call bug14210_fill_table()|
drop procedure bug14210_fill_table|
create table t4 like t3|
create procedure bug14210()
begin
declare a char(255);
declare done int default 0;
declare c cursor for select * from t3;
declare continue handler for sqlstate '02000' set done = 1;
open c;
repeat
fetch c into a;
if not done then
insert into t4 values (upper(a));
end if;
until done end repeat;
close c;
end|
call bug14210()|
select count(*) from t4|
count(*)
256
drop table t3, t4|
drop procedure bug14210|
set @@session.max_heap_table_size=default|
CREATE DATABASE db_bug7787|
use db_bug7787|
CREATE PROCEDURE p1()
SHOW ENGINE INNODB STATUS; |
GRANT EXECUTE ON PROCEDURE p1 TO user_bug7787@localhost|
DROP DATABASE db_bug7787|
drop user user_bug7787@localhost|
use test|
create table t3 (f1 int, f2 varchar(3), primary key(f1)) engine=innodb|
insert into t3 values (1,'aaa'),(2,'bbb'),(3,'ccc')|
CREATE FUNCTION bug13575 ( p1 integer ) 
returns varchar(3) 
BEGIN 
DECLARE v1 VARCHAR(10) DEFAULT null;
SELECT f2 INTO v1 FROM t3 WHERE f1 = p1; 
RETURN v1;
END|
select distinct f1, bug13575(f1) from t3 order by f1|
f1	bug13575(f1)
1	aaa
2	bbb
3	ccc
drop function bug13575|
drop table t3|
SELECT @@GLOBAL.storage_engine INTO @old_engine|
SET @@GLOBAL.storage_engine=InnoDB|
SET @@SESSION.storage_engine=InnoDB|
SHOW GLOBAL VARIABLES LIKE 'storage_engine'|
Variable_name	Value
storage_engine	InnoDB
SHOW SESSION VARIABLES LIKE 'storage_engine'|
Variable_name	Value
storage_engine	InnoDB
CREATE PROCEDURE bug11758414()
BEGIN
SET @@GLOBAL.storage_engine="MyISAM";
SET @@SESSION.storage_engine="MyISAM"; 
# show defaults at execution time / that setting them worked
SHOW GLOBAL VARIABLES LIKE 'storage_engine';
SHOW SESSION VARIABLES LIKE 'storage_engine';
CREATE TABLE t1 (id int);
CREATE TABLE t2 (id int) ENGINE=InnoDB;
# show we're heeding the default (at run-time, not parse-time!)
 SHOW CREATE TABLE t1;
 # show that we didn't break explicit override with ENGINE=...
SHOW CREATE TABLE t2;
END;
|
CALL bug11758414|
Variable_name	Value
storage_engine	MyISAM
Variable_name	Value
storage_engine	MyISAM
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
Table	Create Table
t2	CREATE TABLE `t2` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
SHOW GLOBAL VARIABLES LIKE 'storage_engine'|
Variable_name	Value
storage_engine	MyISAM
SHOW SESSION VARIABLES LIKE 'storage_engine'|
Variable_name	Value
storage_engine	MyISAM
DROP PROCEDURE bug11758414|
DROP TABLE t1, t2|
SET @@GLOBAL.storage_engine=@old_engine|
#
# End of 5.1 tests
#
# 
# Bug #35877 Update .. WHERE with function, constraint violation, crash 
#           
DROP TABLE IF EXISTS t1_aux|
DROP TABLE IF EXISTS t1_not_null|
DROP FUNCTION IF EXISTS f1_two_inserts|
# MyISAM test
CREATE TABLE t1_not_null (f1 BIGINT, f2 BIGINT NOT NULL)|
CREATE TABLE t1_aux (f1 BIGINT, f2 BIGINT)|
INSERT INTO t1_aux VALUES (1,1)|
CREATE FUNCTION f1_two_inserts() returns INTEGER
BEGIN
INSERT INTO t1_not_null SET f1 = 10, f2 = NULL;
RETURN 1;
END|
UPDATE t1_aux SET f2 = 2 WHERE f1 = f1_two_inserts()|
ERROR 23000: Column 'f2' cannot be null
# InnoDB test
ALTER TABLE t1_not_null ENGINE = InnoDB|
ALTER TABLE t1_aux ENGINE = InnoDB|
UPDATE t1_aux SET f2 = 2 WHERE f1 = f1_two_inserts()|
ERROR 23000: Column 'f2' cannot be null
DROP TABLE t1_aux, t1_not_null|
DROP FUNCTION f1_two_inserts|
#
# Bug#49938: Failing assertion: inode or deadlock in fsp/fsp0fsp.c
#
DROP PROCEDURE IF EXISTS p1|
DROP TABLE IF EXISTS t1|
CREATE TABLE t1 (a INT) ENGINE=INNODB|
CREATE PROCEDURE p1()
BEGIN
TRUNCATE TABLE t1;
END|
LOCK TABLES t1 WRITE|
CALL p1()|
FLUSH TABLES;
UNLOCK TABLES|
CALL p1()|
DROP PROCEDURE p1|
DROP TABLE t1|