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|