From a7abddeffa6a760ce948c2dfb007cdf3f1a369d5 Mon Sep 17 00:00:00 2001
From: Michael Widenius <monty@mariadb.org>
Date: Fri, 9 Mar 2018 14:05:35 +0200
Subject: Create 'main' test directory and move 't' and 'r' there

---
 mysql-test/main/sp_trans.result | 647 ++++++++++++++++++++++++++++++++++++++++
 1 file changed, 647 insertions(+)
 create mode 100644 mysql-test/main/sp_trans.result

(limited to 'mysql-test/main/sp_trans.result')

diff --git a/mysql-test/main/sp_trans.result b/mysql-test/main/sp_trans.result
new file mode 100644
index 00000000000..ef802279c9c
--- /dev/null
+++ b/mysql-test/main/sp_trans.result
@@ -0,0 +1,647 @@
+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|
-- 
cgit v1.2.1