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

1 files changed, 480 insertions, 0 deletions

diff --git a/mysql-test/main/create_or_replace.result b/mysql-test/main/create_or_replace.result
new file mode 100644
index 00000000000..e26884f1cbf
--- /dev/null
+++ b/mysql-test/main/create_or_replace.result
@@ -0,0 +1,480 @@
+drop table if exists t1,t2,t3;
+CREATE TABLE t2 (a int);
+INSERT INTO t2 VALUES(1),(2),(3);
+#
+# Check first syntax and wrong usage
+#
+CREATE OR REPLACE TABLE IF NOT EXISTS t1 (a int);
+ERROR HY000: Incorrect usage of OR REPLACE and IF NOT EXISTS
+create or replace table mysql.general_log (a int);
+ERROR HY000: You cannot 'CREATE OR REPLACE' a log table if logging is enabled
+create or replace table mysql.slow_log (a int);
+ERROR HY000: You cannot 'CREATE OR REPLACE' a log table if logging is enabled
+#
+# Usage when table doesn't exist
+#
+CREATE OR REPLACE TABLE t1 (a int);
+CREATE TABLE t1 (a int);
+ERROR 42S01: Table 't1' already exists
+DROP TABLE t1;
+CREATE OR REPLACE TEMPORARY TABLE t1 (a int);
+CREATE TEMPORARY TABLE t1 (a int, b int, c int);
+ERROR 42S01: Table 't1' already exists
+DROP TEMPORARY TABLE t1;
+#
+# Testing with temporary tables
+#
+CREATE OR REPLACE TABLE t1 (a int);
+CREATE OR REPLACE TEMPORARY TABLE t1 (a int);
+CREATE OR REPLACE TEMPORARY TABLE t1 (a int, b int);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TEMPORARY TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TEMPORARY TABLE t1;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+create temporary table t1 (i int) engine=InnoDB;
+create or replace temporary table t1 (a int, b int) engine=InnoDB;
+create or replace temporary table t1 (j int);
+show create table t1;
+Table	Create Table
+t1	CREATE TEMPORARY TABLE `t1` (
+  `j` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table t1;
+CREATE OR REPLACE TABLE t1 (a int);
+LOCK TABLES t1 write;
+CREATE OR REPLACE TEMPORARY TABLE t1 (a int);
+CREATE OR REPLACE TEMPORARY TABLE t1 (a int, b int);
+CREATE OR REPLACE TEMPORARY TABLE t1 (a int, b int) engine= innodb;
+CREATE OR REPLACE TEMPORARY TABLE t1 (a int) engine= innodb;
+CREATE OR REPLACE TEMPORARY TABLE t1 (a int, b int) engine=myisam;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TEMPORARY TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TEMPORARY TABLE t1;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+CREATE OR REPLACE TABLE t2 (a int);
+ERROR HY000: Table 't2' was not locked with LOCK TABLES
+DROP TABLE t1;
+UNLOCK TABLES;
+CREATE OR REPLACE TEMPORARY TABLE t1 (a int) SELECT * from t2;
+SELECT * FROM t1;
+a
+1
+2
+3
+CREATE OR REPLACE TEMPORARY TABLE t1 (b int) SELECT * from t2;
+SELECT * FROM t1;
+b	a
+NULL	1
+NULL	2
+NULL	3
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TEMPORARY TABLE `t1` (
+  `b` int(11) DEFAULT NULL,
+  `a` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TEMPORARY TABLE t1 AS SELECT a FROM t2;
+CREATE TEMPORARY TABLE IF NOT EXISTS t1(a int, b int) SELECT 1,2 FROM t2;
+Warnings:
+Note	1050	Table 't1' already exists
+DROP TABLE t1;
+CREATE TABLE t1 (a int);
+CREATE OR REPLACE TABLE t1 AS SELECT 1;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `1` int(1) NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+create table t1 (a int);
+create or replace table t1 as select * from t1;
+ERROR HY000: Table 't1' is specified twice, both as a target for 'CREATE' and as a separate source for data
+create or replace table t1 as select a from (select a from t1) as t3;
+ERROR HY000: Table 't1' is specified twice, both as a target for 'CREATE' and as a separate source for data
+create or replace table t1 as select a from t2 where t2.a in (select a from t1);
+ERROR HY000: Table 't1' is specified twice, both as a target for 'CREATE' and as a separate source for data
+drop table t1;
+#
+# Testing with normal tables
+#
+CREATE OR REPLACE TABLE t1 (a int);
+CREATE OR REPLACE TABLE t1 (a int, b int);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1 (a int) SELECT * from t2;
+SELECT * FROM t1;
+a
+1
+2
+3
+TRUNCATE TABLE t1;
+CREATE TABLE IF NOT EXISTS t1 (a int) SELECT * from t2;
+Warnings:
+Note	1050	Table 't1' already exists
+SELECT * FROM t1;
+a
+DROP TABLE t1;
+CREATE TABLE t1 (i int);
+CREATE OR REPLACE TABLE t1 AS SELECT 1;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `1` int(1) NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE OR REPLACE TABLE t1 (a int);
+LOCK TABLES t1 write,t2 write;
+CREATE OR REPLACE TABLE t1 (a int, b int);
+SELECT * FROM t1;
+a	b
+INSERT INTO t1 values(1,1);
+CREATE OR REPLACE TABLE t1 (a int, b int, c int);
+INSERT INTO t1 values(1,1,1);
+CREATE OR REPLACE TABLE t3 (a int);
+ERROR HY000: Table 't3' was not locked with LOCK TABLES
+UNLOCK TABLES;
+DROP TABLE t1;
+CREATE OR REPLACE TABLE t1 (a int);
+LOCK TABLES t1 write,t2 write;
+CREATE OR REPLACE TABLE t1 (a int, b int) select a,1 from t2;
+SELECT * FROM t2;
+a
+1
+2
+3
+SELECT * FROM t1;
+b	a	1
+NULL	1	1
+NULL	2	1
+NULL	3	1
+SELECT * FROM t1;
+b	a	1
+NULL	1	1
+NULL	2	1
+NULL	3	1
+INSERT INTO t1 values(1,1,1);
+CREATE OR REPLACE TABLE t1 (a int, b int, c int, d int);
+INSERT INTO t1 values(1,1,1,1);
+CREATE OR REPLACE TABLE t3 (a int);
+ERROR HY000: Table 't3' was not locked with LOCK TABLES
+UNLOCK TABLES;
+DROP TABLE t1;
+CREATE OR REPLACE TABLE t1 (a int);
+LOCK TABLES t1 write,t2 write, t1 as t1_read read;
+CREATE OR REPLACE TABLE t1 (a int, b int) select a,1 from t2;
+SELECT * FROM t1;
+b	a	1
+NULL	1	1
+NULL	2	1
+NULL	3	1
+SELECT * FROM t2;
+a
+1
+2
+3
+SELECT * FROM t1 as t1_read;
+ERROR HY000: Table 't1_read' was not locked with LOCK TABLES
+DROP TABLE t1;
+UNLOCK TABLES;
+CREATE OR REPLACE TABLE t1 (a int);
+LOCK TABLE t1 WRITE;
+CREATE OR REPLACE TABLE t1 AS SELECT 1;
+SELECT * from t1;
+1
+1
+SELECT * from t2;
+ERROR HY000: Table 't2' was not locked with LOCK TABLES
+DROP TABLE t1;
+#
+# Test also with InnoDB (transactional engine)
+#
+create table t1 (i int) engine=innodb;
+lock table t1 write;
+create or replace table t1 (j int);
+unlock tables;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `j` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table t1;
+create table t1 (i int) engine=InnoDB;
+lock table t1 write, t2 write;
+create or replace table t1 (j int) engine=innodb;
+unlock tables;
+drop table t1;
+create table t1 (i int) engine=InnoDB;
+create table t3 (i int) engine=InnoDB;
+insert into t3 values(1),(2),(3);
+create table t4 (i int) engine=InnoDB;
+insert into t4 values(1);
+lock table t1 write, t2 write, t3 write, t4 write;
+create or replace table t1 (a int, i int) engine=innodb select t2.a,t3.i from t2,t3;
+select * from t4;
+i
+1
+unlock tables;
+select * from t1 order by a,i;
+a	i
+1	1
+1	2
+1	3
+2	1
+2	2
+2	3
+3	1
+3	2
+3	3
+drop table t1,t3,t4;
+#
+# Test the meta data locks are freed properly
+#
+create database mysqltest2;
+drop table if exists test.t1,mysqltest2.t2;
+Warnings:
+Note	1051	Unknown table 'test.t1'
+Note	1051	Unknown table 'mysqltest2.t2'
+create table test.t1 (i int);
+create table mysqltest2.t2 like test.t1;
+lock table test.t1 write, mysqltest2.t2 write;
+select * from information_schema.metadata_lock_info;
+THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
+#	MDL_INTENTION_EXCLUSIVE	NULL	Global read lock		
+#	MDL_INTENTION_EXCLUSIVE	NULL	Schema metadata lock	mysqltest2	
+#	MDL_INTENTION_EXCLUSIVE	NULL	Schema metadata lock	test	
+#	MDL_SHARED_NO_READ_WRITE	NULL	Table metadata lock	mysqltest2	t2
+#	MDL_SHARED_NO_READ_WRITE	NULL	Table metadata lock	test	t1
+create or replace table test.t1;
+ERROR 42000: A table must have at least 1 column
+show tables;
+Tables_in_test
+t2
+select * from information_schema.metadata_lock_info;
+THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
+#	MDL_INTENTION_EXCLUSIVE	NULL	Global read lock		
+#	MDL_INTENTION_EXCLUSIVE	NULL	Schema metadata lock	mysqltest2	
+#	MDL_INTENTION_EXCLUSIVE	NULL	Schema metadata lock	test	
+#	MDL_SHARED_NO_READ_WRITE	NULL	Table metadata lock	mysqltest2	t2
+create or replace table mysqltest2.t2;
+ERROR 42000: A table must have at least 1 column
+select * from information_schema.metadata_lock_info;
+THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
+create table t1 (i int);
+drop table t1;
+create table test.t1 (i int);
+create table mysqltest2.t2 like test.t1;
+lock table test.t1 write, mysqltest2.t2 write;
+select * from information_schema.metadata_lock_info;
+THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
+#	MDL_INTENTION_EXCLUSIVE	NULL	Global read lock		
+#	MDL_INTENTION_EXCLUSIVE	NULL	Schema metadata lock	mysqltest2	
+#	MDL_INTENTION_EXCLUSIVE	NULL	Schema metadata lock	test	
+#	MDL_SHARED_NO_READ_WRITE	NULL	Table metadata lock	mysqltest2	t2
+#	MDL_SHARED_NO_READ_WRITE	NULL	Table metadata lock	test	t1
+create or replace table test.t1 (a int) select 1 as 'a', 2 as 'a';
+ERROR 42S21: Duplicate column name 'a'
+show tables;
+Tables_in_test
+t2
+select * from information_schema.metadata_lock_info;
+THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
+#	MDL_INTENTION_EXCLUSIVE	NULL	Global read lock		
+#	MDL_INTENTION_EXCLUSIVE	NULL	Schema metadata lock	mysqltest2	
+#	MDL_INTENTION_EXCLUSIVE	NULL	Schema metadata lock	test	
+#	MDL_SHARED_NO_READ_WRITE	NULL	Table metadata lock	mysqltest2	t2
+create or replace table mysqltest2.t2 (a int) select 1 as 'a', 2 as 'a';
+ERROR 42S21: Duplicate column name 'a'
+select * from information_schema.metadata_lock_info;
+THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
+create table t1 (i int);
+drop table t1;
+drop database mysqltest2;
+#
+# Testing CREATE .. LIKE
+#
+create or replace table t1 like t2;
+create or replace table t1 like t2;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table t1;
+create table t1 (b int);
+lock tables t1 write, t2 read;
+create or replace table t1 like t2;
+SELECT * FROM t1;
+a
+INSERT INTO t1 values(1);
+CREATE OR REPLACE TABLE t1 like t2;
+INSERT INTO t1 values(2);
+unlock tables;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table t1;
+create or replace table t1 like t2;
+create or replace table t1 like t1;
+ERROR 42000: Not unique table/alias: 't1'
+drop table t1;
+CREATE TEMPORARY TABLE t1 like t2;
+CREATE OR REPLACE TABLE t1 like t1;
+ERROR 42000: Not unique table/alias: 't1'
+CREATE OR REPLACE TABLE t1 like t1;
+ERROR 42000: Not unique table/alias: 't1'
+drop table t1;
+CREATE TEMPORARY TABLE t1 like t2;
+CREATE OR REPLACE TEMPORARY TABLE t3 like t1;
+CREATE OR REPLACE TEMPORARY TABLE t3 like t3;
+ERROR 42000: Not unique table/alias: 't3'
+drop table t1,t3;
+#
+# Test with prepared statements
+#
+prepare stmt1 from 'create or replace table t1 select * from t2';
+execute stmt1;
+select * from t1;
+a
+1
+2
+3
+execute stmt1;
+select * from t1;
+a
+1
+2
+3
+drop table t1;
+execute stmt1;
+select * from t1;
+a
+1
+2
+3
+deallocate prepare stmt1;
+drop table t1;
+#
+# Test with views
+#
+create view t1 as select 1;
+create table if not exists t1 (a int);
+Warnings:
+Note	1050	Table 't1' already exists
+create or replace table t1 (a int);
+ERROR 42S02: 'test.t1' is a view
+drop table t1;
+ERROR 42S02: 'test.t1' is a view
+drop view t1;
+#
+# MDEV-5602 CREATE OR REPLACE obtains stricter locks than the
+# connection had before
+#
+create table t1 (a int);
+lock table t1 write, t2 read;
+select * from information_schema.metadata_lock_info;
+THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
+#	MDL_INTENTION_EXCLUSIVE	NULL	Global read lock		
+#	MDL_INTENTION_EXCLUSIVE	NULL	Schema metadata lock	test	
+#	MDL_SHARED_NO_READ_WRITE	NULL	Table metadata lock	test	t1
+#	MDL_SHARED_READ	NULL	Table metadata lock	test	t2
+create or replace table t1 (i int);
+select * from information_schema.metadata_lock_info;
+THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
+#	MDL_INTENTION_EXCLUSIVE	NULL	Global read lock		
+#	MDL_INTENTION_EXCLUSIVE	NULL	Schema metadata lock	test	
+#	MDL_SHARED_NO_READ_WRITE	NULL	Table metadata lock	test	t1
+#	MDL_SHARED_READ	NULL	Table metadata lock	test	t2
+create or replace table t1 like t2;
+select * from information_schema.metadata_lock_info;
+THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
+#	MDL_INTENTION_EXCLUSIVE	NULL	Global read lock		
+#	MDL_INTENTION_EXCLUSIVE	NULL	Schema metadata lock	test	
+#	MDL_SHARED_NO_READ_WRITE	NULL	Table metadata lock	test	t1
+#	MDL_SHARED_READ	NULL	Table metadata lock	test	t2
+create or replace table t1 select 1 as f1;
+select * from information_schema.metadata_lock_info;
+THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
+#	MDL_INTENTION_EXCLUSIVE	NULL	Global read lock		
+#	MDL_INTENTION_EXCLUSIVE	NULL	Schema metadata lock	test	
+#	MDL_SHARED_NO_READ_WRITE	NULL	Table metadata lock	test	t1
+#	MDL_SHARED_READ	NULL	Table metadata lock	test	t2
+drop table t1;
+unlock tables;
+#
+# MDEV-6560
+# Assertion `! is_set() ' failed in Diagnostics_area::set_ok_status
+#
+CREATE TABLE t1 (col_int_nokey INT)  ENGINE=InnoDB;
+CREATE OR REPLACE TEMPORARY TABLE tmp LIKE t1;
+LOCK TABLE t1 WRITE;
+connect  con1,localhost,root,,test;
+CREATE OR REPLACE TABLE t1 LIKE tmp;
+connection default;
+KILL QUERY con_id;
+connection con1;
+ERROR 70100: Query execution was interrupted
+CREATE OR REPLACE TABLE t1 (a int);
+connection default;
+KILL QUERY con_id;
+connection con1;
+ERROR 70100: Query execution was interrupted
+disconnect con1;
+connection default;
+drop table t1;
+DROP TABLE t2;
+#
+# MDEV-10824 - Crash in CREATE OR REPLACE TABLE t1 AS SELECT spfunc()
+#
+CREATE TABLE t1(a INT);
+CREATE FUNCTION f1() RETURNS VARCHAR(16383) RETURN 'test';
+CREATE OR REPLACE TABLE t1 AS SELECT f1();
+LOCK TABLE t1 WRITE;
+CREATE OR REPLACE TABLE t1 AS SELECT f1();
+UNLOCK TABLES;
+DROP FUNCTION f1;
+DROP TABLE t1;
+#
+# MDEV-11071 - Assertion `thd->transaction.stmt.is_empty()' failed in
+#              Locked_tables_list::unlock_locked_tables
+#
+CREATE TEMPORARY TABLE t1(a INT) ENGINE=InnoDB;
+CREATE TEMPORARY TABLE t2(a INT);
+CREATE TABLE t3(a INT);
+LOCK TABLE t2 WRITE;
+SELECT * FROM t2;
+a
+CREATE OR REPLACE TEMPORARY TABLE t1(a INT) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
+ERROR HY000: CREATE TEMPORARY TABLE is not allowed with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.
+SELECT * FROM t3;
+ERROR HY000: Table 't3' was not locked with LOCK TABLES
+CREATE OR REPLACE TEMPORARY TABLE t2(a INT) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
+ERROR HY000: CREATE TEMPORARY TABLE is not allowed with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.
+SELECT * FROM t3;
+ERROR HY000: Table 't3' was not locked with LOCK TABLES
+UNLOCK TABLES;
+DROP TABLE t3;