Create 'main' test directory and move 't' and 'r' there

1 files changed, 0 insertions, 707 deletions

diff --git a/mysql-test/t/sp_trans.test b/mysql-test/t/sp_trans.test
deleted file mode 100644
index a9759b99269..00000000000
--- a/mysql-test/t/sp_trans.test
+++ /dev/null
@@ -1,707 +0,0 @@
-#
-# tests that require InnoDB...
-#
-
--- source include/have_log_bin.inc
--- source include/have_innodb.inc
-
-set SQL_MODE="";
-
-disable_query_log;
-call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
-enable_query_log;
-
---disable_warnings
-drop table if exists t1, t2, t3;
---enable_warnings
-
-delimiter |;
-
-#
-# BUG#8850: Truncate table in a stored procedure locks the tables
-#
---disable_warnings
-drop procedure if exists bug8850|
---enable_warnings
-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|
-
-call bug8850()|
-set autocommit=1|
-select * from t1|
-drop table t1|
-drop procedure bug8850|
-
-
-#
-# BUG#10015: Crash in InnoDB if stored routines are used
-# (crash happens in auto-commit mode)
-#
---disable_warnings
-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|
---enable_warnings
-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|
-drop function bug10015_1|
-# Test couple of a bit more complex cases
-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|
-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|
-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|
-select * from t2|
-drop function bug10015_4|
-# Now let us test how statement rollback works
-# This function will cause the whole stmt to be rolled back,
-# there should not be any traces left.
-create function bug10015_5(i int) returns int
-  begin
-    if (i = 5) then
-      insert into t2 values (1, 0);
-    end if;
-    return i;
-  end|
---error ER_DUP_ENTRY
-insert into t1 values (bug10015_5(4)), (bug10015_5(5))|
-select * from t1|
-drop function bug10015_5|
-# Thanks to error-handler this function should not cause rollback
-# of statement calling it. But insert statement in it should be 
-# rolled back completely and don't leave any traces in t2.
-# Unfortunately we can't implement such behavior in 5.0, so it
-# is something to be fixed in later 5.* releases (TODO).
-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|
-select * from t1|
-select * from t2|
-drop function bug10015_6|
-# Let us test that we don't allow any statements causing transaction
-# commit in stored functions (we test only most interesting cases here).
-# Cases which can be caught at creation time:
---error 1422
-create function bug10015_7() returns int
-  begin
-    alter table t1 add k int;
-    return 1;
-  end|
---error 1422
-create function bug10015_7() returns int
-  begin
-    start transaction;
-    return 1;
-  end|
---error 1422
-create function bug10015_7() returns int
-  begin
-    drop table t1;
-    return 1;
-  end|
-# It should be OK to drop temporary table.
-create function bug10015_7() returns int
-  begin
-    drop temporary table t1;
-    return 1;
-  end|
-drop function bug10015_7|
---error 1422
-create function bug10015_7() returns int
-  begin
-    commit;
-    return 1;
-  end|
-# Now let us test cases which we can catch only at run-time:
-create function bug10015_7() returns int
-  begin
-    call bug10015_8();
-    return 1;
-  end|
-create procedure bug10015_8() alter table t1 add k int|
---error 1422
-select *, bug10015_7() from t1|
-drop procedure bug10015_8|
-create procedure bug10015_8() start transaction|
---error 1422
-select *, bug10015_7() from t1|
-drop procedure bug10015_8|
-# Again it is OK to drop temporary table
-# We are surpressing warnings since they are not essential
-create procedure bug10015_8() drop temporary table if exists t1_temp|
---disable_warnings
-select *, bug10015_7() from t1|
---enable_warnings
-drop procedure bug10015_8|
-create procedure bug10015_8() commit|
---error 1422
-select *, bug10015_7() from t1|
-drop procedure bug10015_8|
-drop function bug10015_7|
-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#14840: CONTINUE handler problem
-#
---disable_warnings
-drop table if exists t3|
-drop procedure if exists bug14840_1|
-drop procedure if exists bug14840_2|
---enable_warnings
-
-create table t3
-(
-  x int,
-  y int,
-  primary key (x)
-) engine=InnoDB|
-
-# This used to hang the client since the insert returned with an
-# error status (left over from the update) even though it succeeded,
-# which caused the execution to end at that point.
-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|
-
-# A simpler (non-transactional) case: insert at select should be done
-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()|
-
-delete from t3|
-insert into t3 values (1, 3), (2, 5)|
-call bug14840_2()|
-
-drop procedure bug14840_1|
-drop procedure bug14840_2|
-drop table t3|
-
-
-#
-# BUG#10656: Stored Procedure - Create index and Truncate table command error
-#
---disable_warnings
-drop procedure if exists bug10656_create_index|
-drop procedure if exists bug10656_myjoin|
-drop procedure if exists bug10656_truncate_table|
---enable_warnings
-
-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|
-
-#
-# BUG#3448
-#
---disable_warnings
-create table t3 (
-  a int primary key,
-  ach char(1)
-) engine = innodb|
-
-create table t4 (
-  b int  primary key,
-  bch char(1)
-) engine = innodb|
---enable_warnings
-
-insert into t3 values (1 , 'aCh1' ) , ('2' , 'aCh2')|
-insert into t4 values (1 , 'bCh1' )|
-
---disable_warnings
-drop procedure if exists bug3448|
---enable_warnings
-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|
-call bug3448()|
-call bug3448()|
-
-drop procedure bug3448|
-drop table t3, t4|
-
-#
-# BUG#14210: "Simple query with > operator on large table gives server
-# crash"
-# Check that cursors work in case when HEAP tables are converted to
-# MyISAM
-#
---disable_warnings
-drop procedure if exists bug14210|
---enable_warnings
-set @@session.max_heap_table_size=16384|
-select @@session.max_heap_table_size|
-# To trigger the memory corruption the original table must be InnoDB.
-# No harm if it's not, so don't warn if the suite is run with --skip-innodb
---disable_warnings
-create table t3 (a char(255)) engine=InnoDB|
---enable_warnings
-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|
---disable_warnings
-call bug14210_fill_table()|
---enable_warnings
-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|
-
-drop table t3, t4|
-drop procedure bug14210|
-set @@session.max_heap_table_size=default|
-
-#
-# BUG#7787: Stored procedures: improper warning for "grant execute" statement
-#
-
-# Prepare.
-
-CREATE DATABASE db_bug7787|
-use db_bug7787|
-
-# Test.
-
-CREATE PROCEDURE p1()
-  SHOW ENGINE INNODB STATUS; |
-
-GRANT EXECUTE ON PROCEDURE p1 TO user_bug7787@localhost|
-
-# Cleanup.
-
-DROP DATABASE db_bug7787|
-drop user user_bug7787@localhost|
-use test|
-
-#
-# Bug#13575 SP funcs in select with distinct/group and order by can
-#           produce bad data
-#
-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|
-drop function bug13575|
-drop table t3|
-
-#
-# BUG#11758414: Default storage_engine not honored when set
-#               from within a stored procedure
-#
-SELECT @@GLOBAL.storage_engine INTO @old_engine|
-SET @@GLOBAL.storage_engine=InnoDB|
-SET @@SESSION.storage_engine=InnoDB|
-# show defaults at define-time
-SHOW GLOBAL VARIABLES LIKE 'storage_engine'|
-SHOW SESSION VARIABLES LIKE 'storage_engine'|
-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|
-# show that changing defaults within SP stuck
-SHOW GLOBAL VARIABLES LIKE 'storage_engine'|
-SHOW SESSION VARIABLES LIKE 'storage_engine'|
-DROP PROCEDURE bug11758414|
-DROP TABLE t1, t2|
-SET @@GLOBAL.storage_engine=@old_engine|
-
---echo #
---echo # End of 5.1 tests
---echo #
-
---echo # 
---echo # Bug #35877 Update .. WHERE with function, constraint violation, crash 
---echo #           
-
---disable_warnings
-DROP TABLE IF EXISTS t1_aux|
-DROP TABLE IF EXISTS t1_not_null|
-DROP FUNCTION IF EXISTS f1_two_inserts|
---enable_warnings
-
--- echo # 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|
-
--- error ER_BAD_NULL_ERROR
-UPDATE t1_aux SET f2 = 2 WHERE f1 = f1_two_inserts()|
-
--- echo # InnoDB test
-ALTER TABLE t1_not_null ENGINE = InnoDB|
-ALTER TABLE t1_aux ENGINE = InnoDB|
-
--- error ER_BAD_NULL_ERROR
-UPDATE t1_aux SET f2 = 2 WHERE f1 = f1_two_inserts()|
-
-DROP TABLE t1_aux, t1_not_null|
-DROP FUNCTION f1_two_inserts|
-
---echo #
---echo # Bug#49938: Failing assertion: inode or deadlock in fsp/fsp0fsp.c
---echo #
-
---disable_warnings
-DROP PROCEDURE IF EXISTS p1|
-DROP TABLE IF EXISTS t1|
---enable_warnings
-
-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|
-
-#
-# BUG#NNNN: New bug synopsis
-#
-#--disable_warnings
-#drop procedure if exists bugNNNN|
-#--enable_warnings
-#create procedure bugNNNN...
-
-delimiter ;|