path: root/mysql-test/main/create_or_replace.result

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 COLLATE=latin1_swedish_ci
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 COLLATE=latin1_swedish_ci
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 COLLATE=latin1_swedish_ci
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 COLLATE=latin1_swedish_ci
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 COLLATE=latin1_swedish_ci
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 COLLATE=latin1_swedish_ci
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 COLLATE=latin1_swedish_ci
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 COLLATE=latin1_swedish_ci
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 COLLATE=latin1_swedish_ci
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 COLLATE=latin1_swedish_ci
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,mysqltest2.t2'
create table test.t1 (i int) engine=myisam;
create table mysqltest2.t2 like test.t1;
lock table test.t1 write, mysqltest2.t2 write;
select * from information_schema.metadata_lock_info
where table_schema!='mysql' or table_name not like 'innodb_%_stats';
THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
#	MDL_BACKUP_DDL	NULL	Backup lock		
#	MDL_BACKUP_DML	NULL	Backup 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
t1
t2
select * from information_schema.metadata_lock_info
where table_schema!='mysql' or table_name not like 'innodb_%_stats';
THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
#	MDL_BACKUP_DDL	NULL	Backup lock		
#	MDL_BACKUP_DML	NULL	Backup 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 mysqltest2.t2;
ERROR 42000: A table must have at least 1 column
select * from information_schema.metadata_lock_info
where table_schema!='mysql' or table_name not like 'innodb_%_stats';
THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
#	MDL_BACKUP_DDL	NULL	Backup lock		
#	MDL_BACKUP_DML	NULL	Backup 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
drop table mysqltest2.t2;
drop table t1;
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
where table_schema!='mysql' or table_name not like 'innodb_%_stats';
THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
#	MDL_BACKUP_DDL	NULL	Backup lock		
#	MDL_BACKUP_DML	NULL	Backup 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
t1
t2
select * from information_schema.metadata_lock_info
where table_schema!='mysql' or table_name not like 'innodb_%_stats';
THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
#	MDL_BACKUP_DDL	NULL	Backup lock		
#	MDL_BACKUP_DML	NULL	Backup 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 mysqltest2.t2 (a int) select 1 as 'a', 2 as 'a';
ERROR 42S21: Duplicate column name 'a'
select * from information_schema.metadata_lock_info
where table_schema!='mysql' or table_name not like 'innodb_%_stats';
THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
#	MDL_BACKUP_DDL	NULL	Backup lock		
#	MDL_BACKUP_DML	NULL	Backup 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
drop table mysqltest2.t2;
drop table t1;
create table t1 (i int);
drop table t1;
create table test.t1 (i int) engine=innodb;
create table mysqltest2.t2 like test.t1;
lock table test.t1 write, mysqltest2.t2 write;
select * from information_schema.metadata_lock_info
where table_schema!='mysql' or table_name not like 'innodb_%_stats';
THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
#	MDL_BACKUP_DDL	NULL	Backup 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
unlock tables;
drop table test.t1,mysqltest2.t2;
create table test.t1 (i int) engine=aria transactional=1 checksum=1;
create table mysqltest2.t2 like test.t1;
lock table test.t1 write, mysqltest2.t2 write;
select * from information_schema.metadata_lock_info
where table_schema!='mysql' or table_name not like 'innodb_%_stats';
THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
#	MDL_BACKUP_DDL	NULL	Backup 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
unlock tables;
drop table t1;
create table test.t1 (i int);
drop database mysqltest2;
drop table test.t1;
#
# MDEV-23391 Server crash in close_thread_table or assertion, upon CREATE OR REPLACE TABLE under lock
#
create table t1 (i int);
lock table t1 write;
select * from information_schema.metadata_lock_info
where table_schema!='mysql' or table_name not like 'innodb_%_stats';
THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
#	MDL_BACKUP_DDL	NULL	Backup lock		
#	MDL_BACKUP_DML	NULL	Backup lock		
#	MDL_INTENTION_EXCLUSIVE	NULL	Schema metadata lock	test	
#	MDL_SHARED_NO_READ_WRITE	NULL	Table metadata lock	test	t1
create or replace table t1 (a char(1)) engine=Innodb select 'foo' as a;
ERROR 22001: Data too long for column 'a' at row 1
drop table t1;
show tables;
Tables_in_test
t2
select * from information_schema.metadata_lock_info
where table_schema!='mysql' or table_name not like 'innodb_%_stats';
THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
create table t1 (i int);
drop table t1;
#
# 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 COLLATE=latin1_swedish_ci
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 COLLATE=latin1_swedish_ci
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
where table_schema!='mysql' or table_name not like 'innodb_%_stats';
THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
#	MDL_BACKUP_DDL	NULL	Backup lock		
#	MDL_BACKUP_DML	NULL	Backup 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
where table_schema!='mysql' or table_name not like 'innodb_%_stats';
THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
#	MDL_BACKUP_DDL	NULL	Backup lock		
#	MDL_BACKUP_DML	NULL	Backup 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
where table_schema!='mysql' or table_name not like 'innodb_%_stats';
THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
#	MDL_BACKUP_DDL	NULL	Backup lock		
#	MDL_BACKUP_DML	NULL	Backup 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
where table_schema!='mysql' or table_name not like 'innodb_%_stats';
THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
#	MDL_BACKUP_DDL	NULL	Backup lock		
#	MDL_BACKUP_DML	NULL	Backup 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-11129
# CREATE OR REPLACE TABLE t1 AS SELECT spfunc() crashes if spfunc()
# references t1
#
CREATE OR REPLACE TABLE t1(a INT);
CREATE FUNCTION f1() RETURNS VARCHAR(16383)
BEGIN
INSERT INTO t1 VALUES(1);
RETURN 'test';
END;
$$
CREATE OR REPLACE TABLE t1 AS SELECT f1();
ERROR HY000: Table 't1' is specified twice, both as a target for 'CREATE' and as a separate source for data
LOCK TABLE t1 WRITE;
CREATE OR REPLACE TABLE t1 AS SELECT f1();
ERROR HY000: Table 't1' was not locked with LOCK TABLES
UNLOCK TABLES;
DROP FUNCTION f1;
DROP TABLE t1;
#
# MDEV-14410 - Assertion `table->pos_in_locked_tables == __null ||
#              table->pos_in_locked_tables->table == table' failed in
#              mark_used_tables_as_free_for_reuse
#
CREATE TABLE t1 (a INT);
CREATE TABLE t2 (b INT);
CREATE TABLE t3 (c INT);
CREATE TRIGGER tr1 BEFORE INSERT ON t3 FOR EACH ROW INSERT INTO t1 VALUES ();
CREATE TRIGGER tr2 BEFORE INSERT ON t2 FOR EACH ROW INSERT INTO t3 SELECT * FROM t1;
LOCK TABLE t1 WRITE, t2 WRITE;
CREATE OR REPLACE TABLE t1 (i INT);
UNLOCK TABLES;
INSERT INTO t2 VALUES (1);
DROP TABLE t1, t2, t3;
#
# 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(c INT DEFAULT '');
ERROR 42000: Invalid default value for 'c'
SELECT * FROM t3;
ERROR HY000: Table 't3' was not locked with LOCK TABLES
CREATE OR REPLACE TEMPORARY TABLE t2(c INT DEFAULT '');
ERROR 42000: Invalid default value for 'c'
SELECT * FROM t3;
ERROR HY000: Table 't3' was not locked with LOCK TABLES
UNLOCK TABLES;
DROP TABLE t3;
#
# MDEV-29697 Assertion failure in Diagnostics_area::set_ok_status
#            upon CREATE OR REPLACE causing ER_UPDATE_TABLE_USED
#
CREATE TABLE t (a INT) ENGINE=MyISAM;
CREATE TABLE tm (a INT) ENGINE=MERGE UNION(t);
CREATE OR REPLACE TABLE t LIKE tm;
ERROR HY000: Table 'tm' is specified twice, both as a target for 'CREATE' and as a separate source for data
DROP TABLE IF EXISTS tm, t;
# End of 10.4 tests
#
# MDEV-25292 Atomic CREATE OR REPLACE TABLE
#
create table t1 (a int);
insert t1 values (1), (1);
create table t2 (c int);
create or replace table t2 (a int, b int, key k (a), key k (b));
ERROR 42000: Duplicate key name 'k'
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `c` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
create or replace table t2 (a int, b int, key k (a), key k (b)) as select a, a as b from t1;
ERROR 42000: Duplicate key name 'k'
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `c` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
create or replace table t2 (a int primary key) as select * from t1;
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `c` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
set @old_mode= @@sql_mode;
set @@sql_mode='ALLOW_INVALID_DATES';
create table t3 (dt datetime default '2008-02-31 00:00:00');
set @@sql_mode= @old_mode;
create or replace table t2 like t3;
ERROR 42000: Invalid default value for 'dt'
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `c` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
# LOCK TABLES
lock tables t2 write, t1 write;
flush tables;
show open tables like 't2';
Database	Table	In_use	Name_locked
test	t2	1	0
create or replace table t2 (y int);
flush tables;
show open tables like 't2';
Database	Table	In_use	Name_locked
test	t2	1	0
create or replace table t2 like t1;
flush tables;
show open tables like 't2';
Database	Table	In_use	Name_locked
test	t2	1	0
create or replace table t2 (y int) as select * from t1;
flush tables;
show open tables like 't2';
Database	Table	In_use	Name_locked
test	t2	1	0
unlock tables;
# SP
create or replace procedure sp(n int)
begin
select concat('sp call ', n, ':') as '';
show open tables like 't2';
create or replace table t2 (y int);
select 'create or replace table t2 (y int);' as '';
show open tables like 't2';
insert into t2 values (2);
select 'insert into t2 values (2);' as '';
show open tables like 't2';
create or replace table t2 like t1;
select 'create or replace table t2 like t1;' as '';
show open tables like 't2';
create or replace table t2 (y int) as select * from t1;
select 'create or replace table t2 (y int) as select * from t1;' as '';
show open tables like 't2';
select 'select * from t2;' as '';
select * from t2;
show open tables  like 't2';
end $
flush tables;
call sp(1);

sp call 1:
Database	Table	In_use	Name_locked

create or replace table t2 (y int);
Database	Table	In_use	Name_locked

insert into t2 values (2);
Database	Table	In_use	Name_locked
test	t2	0	0

create or replace table t2 like t1;
Database	Table	In_use	Name_locked

create or replace table t2 (y int) as select * from t1;
Database	Table	In_use	Name_locked

select * from t2;
y	a
NULL	1
NULL	1
Database	Table	In_use	Name_locked
test	t2	0	0
call sp(2);

sp call 2:
Database	Table	In_use	Name_locked
test	t2	0	0

create or replace table t2 (y int);
Database	Table	In_use	Name_locked

insert into t2 values (2);
Database	Table	In_use	Name_locked
test	t2	0	0

create or replace table t2 like t1;
Database	Table	In_use	Name_locked

create or replace table t2 (y int) as select * from t1;
Database	Table	In_use	Name_locked

select * from t2;
y	a
NULL	1
NULL	1
Database	Table	In_use	Name_locked
test	t2	0	0
# SP under LOCK TABLES
lock tables t2 write, t1 write;
call sp(3);

sp call 3:
Database	Table	In_use	Name_locked
test	t2	1	0

create or replace table t2 (y int);
Database	Table	In_use	Name_locked
test	t2	1	0

insert into t2 values (2);
Database	Table	In_use	Name_locked
test	t2	1	0

create or replace table t2 like t1;
Database	Table	In_use	Name_locked
test	t2	1	0

create or replace table t2 (y int) as select * from t1;
Database	Table	In_use	Name_locked
test	t2	1	0

select * from t2;
y	a
NULL	1
NULL	1
Database	Table	In_use	Name_locked
test	t2	1	0
call sp(4);

sp call 4:
Database	Table	In_use	Name_locked
test	t2	1	0

create or replace table t2 (y int);
Database	Table	In_use	Name_locked
test	t2	1	0

insert into t2 values (2);
Database	Table	In_use	Name_locked
test	t2	1	0

create or replace table t2 like t1;
Database	Table	In_use	Name_locked
test	t2	1	0

create or replace table t2 (y int) as select * from t1;
Database	Table	In_use	Name_locked
test	t2	1	0

select * from t2;
y	a
NULL	1
NULL	1
Database	Table	In_use	Name_locked
test	t2	1	0
unlock tables;
drop procedure sp;
drop tables t1, t2, t3;
# Trigger
create table t1 (a int);
create trigger a before insert on t1 for each row set @s= 1;
create or replace table t1 (old int);
show create trigger a;
ERROR HY000: Trigger does not exist
drop table t1;
# PS: check thd->change_list sanity
create table t1 (a int not null, b char(10) as (concat('', dayname('2020-02-02'))))  collate utf8_bin;
prepare stmt from 'insert into t1 (b) values (2)';
create or replace table t1 (x int);
drop table t1;
drop prepare stmt;
# Foreign keys
create table t1 (x int primary key, y int) engine innodb;
create table t2 (x int references t1(x)) engine innodb;
create or replace table t1 (x int primary key);
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (t2)
create or replace table t1 (x int primary key);
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (t2)
create table t3 (x int);
create or replace table t1 like t3;
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (t2)
create or replace table t1 like t3;
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (t2)
create or replace table t1 select * from t3;
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (t2)
create or replace table t1 select * from t3;
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (t2)
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `x` int(11) NOT NULL,
  `y` int(11) DEFAULT NULL,
  PRIMARY KEY (`x`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
drop tables t3, t2, t1;
# UNIQUE
create table t1 (pk int auto_increment primary key, a varchar(2300), unique (a)) engine aria;
insert into t1 (a) values ('a'), ('b'), ('c');
create table t2 (x int);
create or replace table t2 engine aria select * from t1;
select * from t2;
pk	a
1	a
2	b
3	c
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `pk` int(11) NOT NULL DEFAULT 0,
  `a` varchar(2300) DEFAULT NULL
) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1
drop tables t2, t1;
#
# MDEV-28956 Locking is broken if CREATE OR REPLACE fails under LOCK TABLES
#
# Atomic CREATE OR REPLACE part:
#
create table t1 (pk int primary key) engine=innodb;
create or replace table t2 (a int primary key references t1 (pk)) engine=innodb;
lock tables t1 write, t2 write;
create or replace table t2 (c1 int not null, c1 varchar(255) ) engine=innodb;
ERROR 42S21: Duplicate column name 'c1'
select * from t1;
pk
select * from t2;
a
unlock tables;
drop tables t2, t1;
#
# MDEV-28933 CREATE OR REPLACE fails to recreate same constraint name
#
use test;
create table t (a int primary key) engine=innodb;
create or replace table u (
a int primary key,
constraint c foreign key d (a) references t (a)) engine=innodb;
select * from information_schema.innodb_sys_foreign;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
test/c	test/u	test/t	1	0
select * from information_schema.innodb_sys_foreign_cols;
ID	FOR_COL_NAME	REF_COL_NAME	POS
test/c	a	a	0
create or replace table u (
a int primary key,
constraint c foreign key d (a) references t (a)) engine=innodb;
select * from information_schema.innodb_sys_foreign;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
test/c	test/u	test/t	1	0
select * from information_schema.innodb_sys_foreign_cols;
ID	FOR_COL_NAME	REF_COL_NAME	POS
test/c	a	a	0
drop tables u, t;
select * from information_schema.innodb_sys_foreign;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
select * from information_schema.innodb_sys_foreign_cols;
ID	FOR_COL_NAME	REF_COL_NAME	POS
#
# MDEV-29544 SIGSEGV in HA_CREATE_INFO::finalize_locked_tables
#
call mtr.add_suppression("mysql.innodb_index_stats");
set sql_mode= '';
create table t (x int) engine innodb;
insert into t values (77);
alter table mysql.innodb_index_stats modify stat_description char(10);
Warnings:
Warning	1265	Data truncated for column 'stat_description' at row 2
Warning	1265	Data truncated for column 'stat_description' at row 3
lock table t write;
create or replace table t (y int);
unlock tables;
alter table mysql.innodb_index_stats modify stat_description varchar(1024) not null;
select * from t;
y
drop table t;
set sql_mode= default;
#
# MDEV-29620 Assertion `next_insert_id == 0' failed in handler::ha_external_lock
#
create or replace table t1 (i serial) as select * from (values(1), (2)) dt;
drop table t1;
#
# MDEV-29698 Server crash or assertion failure upon CREATE OR REPLACE with a MERGE table
#
create table t (a int) engine=myisam;
create table tm (a int) engine=merge union(t);
create or replace table t like tm;
ERROR HY000: Table 'tm' is specified twice, both as a target for 'CREATE' and as a separate source for data
drop tables tm, t;
create table t (a int) engine=myisam;
create temporary table tm (a int) engine=merge union=(t);
create or replace table tt like tm;
drop tables tt, tm, t;
#
# MDEV-29787 CREATE OR REPLACE does not work with custom DATA/INDEX DIRECTORY
#
create table t1 (a int) engine=MyISAM
data directory 'MYSQL_TMP_DIR'
index directory 'MYSQL_TMP_DIR';
MYSQLD_DATADIR/test:
t1.MYD
t1.MYI
t1.frm
MYSQL_TMP_DIR:
t1.MYD
t1.MYI
create or replace table t1 (b int) engine=MyISAM
data directory 'MYSQL_TMP_DIR'
index directory 'MYSQL_TMP_DIR';
MYSQLD_DATADIR/test:
t1.MYD
t1.MYI
t1.frm
MYSQL_TMP_DIR:
t1.MYD
t1.MYI
drop table t1;
MYSQLD_DATADIR/test:
MYSQL_TMP_DIR:
#
# MDEV-29793 Assertion failure in translog_write_record upon CREATE OR REPLACE
#
CREATE TABLE t1 (id INT, f INT, KEY(f), PRIMARY KEY (id)) ENGINE=Aria;
INSERT INTO t1 VALUES (1,1),(2,7),(3,9);
CREATE TABLE t2 (a INT) ENGINE=Aria;
INSERT INTO t2 VALUES (2),(1);
CREATE OR REPLACE TABLE t3 AS SELECT t2.* FROM t2, t1 WHERE t2.a = t1.f OR t2.a = t1.id;
DROP TABLE t1, t2, t3;