diff options
Diffstat (limited to 'mysql-test')
22 files changed, 772 insertions, 167 deletions
diff --git a/mysql-test/extra/binlog_tests/mix_innodb_myisam_binlog.test b/mysql-test/extra/binlog_tests/mix_innodb_myisam_binlog.test index aaadda941fb..68aa2b9a644 100644 --- a/mysql-test/extra/binlog_tests/mix_innodb_myisam_binlog.test +++ b/mysql-test/extra/binlog_tests/mix_innodb_myisam_binlog.test @@ -262,7 +262,6 @@ DROP TABLE IF EXISTS t2; CREATE TABLE t2 (a int, b int, primary key (a)) engine=innodb; INSERT INTO t1 VALUES (4,4); ---error ER_DUP_ENTRY CREATE TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1; SELECT * from t2; TRUNCATE table t2; @@ -277,11 +276,9 @@ CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a)) engine=innodb ; INSERT INTO t1 values (7,7); ROLLBACK; INSERT INTO t1 values (8,8); ---error ER_DUP_ENTRY CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1; COMMIT; INSERT INTO t1 values (9,9); ---error ER_DUP_ENTRY CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1; ROLLBACK; SELECT * from t2; @@ -291,11 +288,9 @@ INSERT INTO t1 values (10,10); INSERT INTO t2 select * from t1; SELECT * from t1; INSERT INTO t2 values (100,100); ---error ER_DUP_ENTRY CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1; COMMIT; INSERT INTO t2 values (101,101); ---error ER_DUP_ENTRY CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1; ROLLBACK; SELECT * from t2; diff --git a/mysql-test/include/commit.inc b/mysql-test/include/commit.inc index c0d30b56ef7..81fbdb03bca 100644 --- a/mysql-test/include/commit.inc +++ b/mysql-test/include/commit.inc @@ -723,7 +723,7 @@ call p_verify_status_increment(4, 4, 4, 4); --echo # Sic: no table is created. create table if not exists t2 (a int) select 6 union select 7; --echo # Sic: first commits the statement, and then the transaction. -call p_verify_status_increment(2, 0, 4, 4); +call p_verify_status_increment(2, 0, 2, 0); create table t3 select a from t2; call p_verify_status_increment(2, 0, 4, 4); alter table t3 add column (b int); diff --git a/mysql-test/include/deadlock.inc b/mysql-test/include/deadlock.inc index 89c34abc871..84d23b80811 100644 --- a/mysql-test/include/deadlock.inc +++ b/mysql-test/include/deadlock.inc @@ -159,7 +159,7 @@ drop table if exists A; create table A (c int); insert into A (c) values (0); ---error 0,ER_LOCK_DEADLOCK,ER_UPDATE_TABLE_USED +--error 0,ER_LOCK_DEADLOCK,ER_TABLE_EXISTS_ERROR create table a as select * from A; drop table A; diff --git a/mysql-test/include/mix1.inc b/mysql-test/include/mix1.inc index fe6abe13892..f1cefe24704 100644 --- a/mysql-test/include/mix1.inc +++ b/mysql-test/include/mix1.inc @@ -742,7 +742,6 @@ drop table if exists t2; CREATE TABLE t2 (a int, b int, primary key (a)); BEGIN; INSERT INTO t2 values(100,100); ---error ER_DUP_ENTRY CREATE TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1; SELECT * from t2; ROLLBACK; @@ -756,13 +755,11 @@ drop table t2; CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a)); BEGIN; INSERT INTO t2 values(100,100); ---error ER_DUP_ENTRY CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1; SELECT * from t2; COMMIT; BEGIN; INSERT INTO t2 values(101,101); ---error ER_DUP_ENTRY CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1; SELECT * from t2; ROLLBACK; diff --git a/mysql-test/r/auto_increment.result b/mysql-test/r/auto_increment.result index 4a2e108f8c6..dbeedb481cd 100644 --- a/mysql-test/r/auto_increment.result +++ b/mysql-test/r/auto_increment.result @@ -469,7 +469,7 @@ drop table t1; CREATE TABLE t1 ( a INT ); INSERT INTO t1 VALUES (1), (1); CREATE TABLE t2 ( a INT AUTO_INCREMENT KEY ); -CREATE TABLE IF NOT EXISTS t2 AS SELECT a FROM t1; +INSERT INTO t2 SELECT a FROM t1; ERROR 23000: Duplicate entry '1' for key 'PRIMARY' UPDATE t2 SET a = 2; SELECT a FROM t2; diff --git a/mysql-test/r/commit_1innodb.result b/mysql-test/r/commit_1innodb.result index a9b04d54400..993d33d7a38 100644 --- a/mysql-test/r/commit_1innodb.result +++ b/mysql-test/r/commit_1innodb.result @@ -830,7 +830,7 @@ create table if not exists t2 (a int) select 6 union select 7; Warnings: Note 1050 Table 't2' already exists # Sic: first commits the statement, and then the transaction. -call p_verify_status_increment(2, 0, 4, 4); +call p_verify_status_increment(2, 0, 2, 0); SUCCESS create table t3 select a from t2; diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index fce775c5952..a84ce6aaddf 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -1,5 +1,6 @@ drop table if exists t1,t2,t3,t4,t5; drop database if exists mysqltest; +drop view if exists v1; create table t1 (b char(0)); insert into t1 values (""),(null); select * from t1; @@ -264,15 +265,14 @@ create table if not exists t1 select 1,2; Warnings: Note 1050 Table 't1' already exists create table if not exists t1 select 1,2,3,4; -ERROR 21S01: Column count doesn't match value count at row 1 +Warnings: +Note 1050 Table 't1' already exists create table if not exists t1 select 1; Warnings: Note 1050 Table 't1' already exists select * from t1; 1 2 3 1 2 3 -0 1 2 -0 0 1 drop table t1; flush status; create table t1 (a int not null, b int, primary key (a)); @@ -280,28 +280,21 @@ insert into t1 values (1,1); create table if not exists t1 select 2; Warnings: Note 1050 Table 't1' already exists -Warning 1364 Field 'a' doesn't have a default value select * from t1; a b 1 1 -0 2 create table if not exists t1 select 3 as 'a',4 as 'b'; Warnings: Note 1050 Table 't1' already exists -create table if not exists t1 select 3 as 'a',3 as 'b'; -ERROR 23000: Duplicate entry '3' for key 'PRIMARY' show warnings; Level Code Message Note 1050 Table 't1' already exists -Error 1062 Duplicate entry '3' for key 'PRIMARY' show status like "Opened_tables"; Variable_name Value Opened_tables 2 select * from t1; a b 1 1 -0 2 -3 4 drop table t1; create table `t1 `(a int); ERROR 42000: Incorrect table name 't1 ' @@ -611,7 +604,7 @@ b drop table t1,t2; create table t1 (a int); create table t1 select * from t1; -ERROR HY000: You can't specify target table 't1' for update in FROM clause +ERROR 42S01: Table 't1' already exists create table t2 union = (t1) select * from t1; ERROR HY000: 'test.t2' is not BASE TABLE flush tables with read lock; @@ -811,7 +804,8 @@ create table t1 (primary key(a)) select "b" as b; ERROR 42000: Key column 'a' doesn't exist in table create table t1 (a int); create table if not exists t1 select 1 as a, 2 as b; -ERROR 21S01: Column count doesn't match value count at row 1 +Warnings: +Note 1050 Table 't1' already exists drop table t1; create table t1 (primary key (a)) (select 1 as a) union all (select 1 as a); ERROR 23000: Duplicate entry '1' for key 'PRIMARY' @@ -823,25 +817,14 @@ Warnings: Note 1050 Table 't1' already exists select * from t1; i -1 create table if not exists t1 select * from t1; -ERROR HY000: You can't specify target table 't1' for update in FROM clause +Warnings: +Note 1050 Table 't1' already exists select * from t1; i -1 +drop table t1; create table t1 select coalesce('a' collate latin1_swedish_ci,'b' collate latin1_bin); ERROR HY000: Illegal mix of collations (latin1_swedish_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation 'coalesce' -select * from t1; -i -1 -alter table t1 add primary key (i); -create table if not exists t1 (select 2 as i) union all (select 2 as i); -ERROR 23000: Duplicate entry '2' for key 'PRIMARY' -select * from t1; -i -1 -2 -drop table t1; create temporary table t1 (j int); create table if not exists t1 select 1; select * from t1; @@ -893,8 +876,6 @@ select * from t2; i 1 2 -1 -2 unlock tables; drop table t1, t2; create table t1 (upgrade int); @@ -1586,11 +1567,9 @@ CREATE TABLE t1 (a INTEGER AUTO_INCREMENT PRIMARY KEY, b INTEGER NOT NULL); INSERT IGNORE INTO t1 (b) VALUES (5); CREATE TABLE IF NOT EXISTS t2 (a INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY) SELECT a FROM t1; -CREATE TABLE IF NOT EXISTS t2 (a INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY) -SELECT a FROM t1; +INSERT INTO t2 SELECT a FROM t1; ERROR 23000: Duplicate entry '1' for key 'PRIMARY' -CREATE TABLE IF NOT EXISTS t2 (a INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY) -SELECT a FROM t1; +INSERT INTO t2 SELECT a FROM t1; ERROR 23000: Duplicate entry '1' for key 'PRIMARY' DROP TABLE t1, t2; # @@ -1620,7 +1599,7 @@ drop table if exists t2; Warnings: Note 1051 Unknown table 't2' CREATE TABLE t2 (a int, b int, primary key (a)); -CREATE TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1; +INSERT INTO t2 select * from t1; ERROR 23000: Duplicate entry '1' for key 'PRIMARY' SELECT * from t2; a b @@ -1633,13 +1612,7 @@ a b 1 1 drop table t2; CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a)); -CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1; -ERROR 23000: Duplicate entry '1' for key 'PRIMARY' -SELECT * from t2; -a b -1 1 -TRUNCATE table t2; -INSERT INTO t2 select * from t1; +INSERT INTO t2 SELECT * FROM t1; ERROR 23000: Duplicate entry '1' for key 'PRIMARY' SELECT * from t2; a b @@ -1964,11 +1937,7 @@ CREATE TRIGGER f BEFORE INSERT ON t1 FOR EACH ROW BEGIN INSERT INTO t1 ( `int` ) VALUES (4 ),( 8 ),( 2 ) ; END ; | -CREATE TABLE IF NOT EXISTS t1 ( -`pk` INTEGER NOT NULL AUTO_INCREMENT , -`int` INTEGER , -PRIMARY KEY ( `pk` ) -) SELECT `pk` , `int_key` FROM B ; +INSERT INTO t1 (pk, int_key) SELECT `pk` , `int_key` FROM B ; ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. CREATE TRIGGER f BEFORE INSERT ON t1 FOR EACH ROW BEGIN @@ -2088,3 +2057,347 @@ t2 CREATE TABLE `t2` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 set @@sql_mode= @old_mode; drop tables t1, t2; +CREATE TABLE t1 (id int); +CREATE TABLE t2 (id int); +INSERT INTO t1 VALUES (1), (1); +INSERT INTO t2 VALUES (2), (2); +CREATE VIEW v1 AS SELECT id FROM t2; +CREATE TABLE IF NOT EXISTS v1(a int, b int) SELECT id, id FROM t1; +Warnings: +Note 1050 Table 'v1' already exists +SHOW CREATE TABLE v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t2`.`id` AS `id` from `t2` latin1 latin1_swedish_ci +SELECT * FROM t2; +id +2 +2 +SELECT * FROM v1; +id +2 +2 +DROP VIEW v1; +CREATE TEMPORARY TABLE tt1 AS SELECT id FROM t2; +CREATE TEMPORARY TABLE IF NOT EXISTS tt1(a int, b int) SELECT id, id FROM t1; +Warnings: +Note 1050 Table 'tt1' already exists +SELECT * FROM t2; +id +2 +2 +SELECT * FROM tt1; +id +2 +2 +DROP TEMPORARY TABLE tt1; +DROP TABLE t1, t2; +# +# WL#5370 "Changing 'CREATE TABLE IF NOT EXISTS ... SELECT' +# behaviour. +# +# +# 1. Basic case: a base table. +# +create table if not exists t1 (a int) select 1 as a; +select * from t1; +a +1 +create table t1 (a int) select 2 as a; +ERROR 42S01: Table 't1' already exists +select * from t1; +a +1 +# Produces an essential warning ER_TABLE_EXISTS. +create table if not exists t1 (a int) select 2 as a; +Warnings: +Note 1050 Table 't1' already exists +# No new data in t1. +select * from t1; +a +1 +drop table t1; +# +# 2. A temporary table. +# +create temporary table if not exists t1 (a int) select 1 as a; +select * from t1; +a +1 +create temporary table t1 (a int) select 2 as a; +ERROR 42S01: Table 't1' already exists +select * from t1; +a +1 +# An essential warning. +create temporary table if not exists t1 (a int) select 2 as a; +Warnings: +Note 1050 Table 't1' already exists +# No new data in t1. +select * from t1; +a +1 +drop temporary table t1; +# +# 3. Creating a base table in presence of a temporary table. +# +create table t1 (a int); +# Create a view for convenience of querying t1 shadowed by a temp. +create view v1 as select a from t1; +drop table t1; +create temporary table t1 (a int) select 1 as a; +create table if not exists t1 (a int) select 2 as a; +select * from t1; +a +1 +select * from v1; +a +2 +# Note: an essential warning. +create table if not exists t1 (a int) select 3 as a; +Warnings: +Note 1050 Table 't1' already exists +select * from t1; +a +1 +select * from v1; +a +2 +drop temporary table t1; +select * from t1; +a +2 +drop view v1; +drop table t1; +# +# 4. Creating a temporary table in presence of a base table. +# +create table t1 (a int) select 1 as a; +create temporary table if not exists t1 select 2 as a; +select * from t1; +a +2 +# Note: an essential warning. +create temporary table if not exists t1 select 3 as a; +Warnings: +Note 1050 Table 't1' already exists +select * from t1; +a +2 +drop temporary table t1; +select * from t1; +a +1 +drop table t1; +# +# 5. Creating a base table in presence of an updatable view. +# +create table t2 (a int unique); +create view t1 as select a from t2; +insert into t1 (a) values (1); +create table t1 (a int); +ERROR 42S01: Table 't1' already exists +# Note: an essential warning. +create table if not exists t1 (a int); +Warnings: +Note 1050 Table 't1' already exists +create table t1 (a int) select 2 as a; +ERROR 42S01: Table 't1' already exists +select * from t1; +a +1 +# Note: an essential warning. +create table if not exists t1 (a int) select 2 as a; +Warnings: +Note 1050 Table 't1' already exists +select * from t1; +a +1 +select * from t2; +a +1 +create temporary table if not exists t1 (a int) select 3 as a; +select * from t1; +a +3 +select * from t2; +a +1 +# Note: an essential warning. +create temporary table if not exists t1 (a int) select 4 as a; +Warnings: +Note 1050 Table 't1' already exists +select * from t1; +a +3 +select * from t2; +a +1 +drop temporary table t1; +# +# Repeating the test with a non-updatable view. +# +drop view t1; +create view t1 as select a + 5 as a from t2; +insert into t1 (a) values (1); +ERROR HY000: The target table t1 of the INSERT is not insertable-into +update t1 set a=3 where a=2; +ERROR HY000: Column 'a' is not updatable +create table t1 (a int); +ERROR 42S01: Table 't1' already exists +# Note: an essential warning. +create table if not exists t1 (a int); +Warnings: +Note 1050 Table 't1' already exists +create table t1 (a int) select 2 as a; +ERROR 42S01: Table 't1' already exists +select * from t1; +a +6 +# Note: an essential warning. +create table if not exists t1 (a int) select 2 as a; +Warnings: +Note 1050 Table 't1' already exists +select * from t1; +a +6 +select * from t2; +a +1 +create temporary table if not exists t1 (a int) select 3 as a; +select * from t1; +a +3 +select * from t2; +a +1 +# Note: an essential warning. +create temporary table if not exists t1 (a int) select 4 as a; +Warnings: +Note 1050 Table 't1' already exists +select * from t1; +a +3 +select * from t2; +a +1 +drop temporary table t1; +drop view t1; +drop table t2; +# +# Repeating the test with a view select a constant number +# +create view t1 as select 1 as a; +insert into t1 (a) values (1); +ERROR HY000: The target table t1 of the INSERT is not insertable-into +update t1 set a=3 where a=2; +ERROR HY000: The target table t1 of the UPDATE is not updatable +create table t1 (a int); +ERROR 42S01: Table 't1' already exists +# Note: an essential warning. +create table if not exists t1 (a int); +Warnings: +Note 1050 Table 't1' already exists +create table t1 (a int) select 2 as a; +ERROR 42S01: Table 't1' already exists +select * from t1; +a +1 +# Note: an essential warning. +create table if not exists t1 (a int) select 2 as a; +Warnings: +Note 1050 Table 't1' already exists +select * from t1; +a +1 +create temporary table if not exists t1 (a int) select 3 as a; +select * from t1; +a +3 +# Note: an essential warning. +create temporary table if not exists t1 (a int) select 4 as a; +Warnings: +Note 1050 Table 't1' already exists +select * from t1; +a +3 +drop temporary table t1; +drop view t1; +# +# 6. Test of unique_table(). +# +create table t1 (a int) select 1 as a; +create temporary table if not exists t1 (a int) select * from t1; +create temporary table if not exists t1 (a int) select * from t1; +ERROR HY000: Can't reopen table: 't1' +select * from t1; +a +1 +drop temporary table t1; +select * from t1; +a +1 +drop table t1; +create temporary table t1 (a int) select 1 as a; +create table if not exists t1 (a int) select * from t1; +create table if not exists t1 (a int) select * from t1; +Warnings: +Note 1050 Table 't1' already exists +select * from t1; +a +1 +drop temporary table t1; +select * from t1; +a +1 +drop table t1; +create table if not exists t1 (a int) select * from t1; +ERROR 42S02: Table 'test.t1' doesn't exist +# +# 7. Test of non-matching columns, REPLACE and IGNORE. +# +create table t1 (a int) select 1 as b, 2 as c; +select * from t1; +a b c +NULL 1 2 +drop table t1; +create table if not exists t1 (a int, b date, c date) select 1 as b, 2 as c; +Warnings: +Warning 1264 Out of range value for column 'b' at row 1 +Warning 1264 Out of range value for column 'c' at row 1 +select * from t1; +a b c +NULL 0000-00-00 0000-00-00 +drop table t1; +set @@session.sql_mode='STRICT_ALL_TABLES'; +create table if not exists t1 (a int, b date, c date) select 1 as b, 2 as c; +ERROR 22007: Incorrect date value: '1' for column 'b' at row 1 +select * from t1; +ERROR 42S02: Table 'test.t1' doesn't exist +create table if not exists t1 (a int, b date, c date) +replace select 1 as b, 2 as c; +ERROR 22007: Incorrect date value: '1' for column 'b' at row 1 +select * from t1; +ERROR 42S02: Table 'test.t1' doesn't exist +create table if not exists t1 (a int, b date, c date) +ignore select 1 as b, 2 as c; +Warnings: +Warning 1264 Out of range value for column 'b' at row 1 +Warning 1264 Out of range value for column 'c' at row 1 +select * from t1; +a b c +NULL 0000-00-00 0000-00-00 +set @@session.sql_mode=default; +drop table t1; +create table if not exists t1 (a int unique, b int) +replace select 1 as a, 1 as b union select 1 as a, 2 as b; +select * from t1; +a b +1 2 +drop table t1; +create table if not exists t1 (a int unique, b int) +ignore select 1 as a, 1 as b union select 1 as a, 2 as b; +select * from t1; +a b +1 1 +drop table t1; +# diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result index f7f0cea3b19..c7e54288f8b 100644 --- a/mysql-test/r/merge.result +++ b/mysql-test/r/merge.result @@ -933,7 +933,8 @@ DROP TABLE tm1, t1, t2; CREATE TABLE t1(c1 INT); CREATE TABLE t2 (c1 INT) ENGINE=MERGE UNION=(t1) INSERT_METHOD=FIRST; CREATE TABLE IF NOT EXISTS t1 SELECT * FROM t2; -ERROR HY000: You can't specify target table 't1' for update in FROM clause +Warnings: +Note 1050 Table 't1' already exists DROP TABLE t1, t2; CREATE TABLE t1 (id INT NOT NULL, ref INT NOT NULL, INDEX (id)) ENGINE=MyISAM; CREATE TABLE t2 LIKE t1; diff --git a/mysql-test/r/ps_ddl.result b/mysql-test/r/ps_ddl.result index 9752c5160a6..76d6c735f43 100644 --- a/mysql-test/r/ps_ddl.result +++ b/mysql-test/r/ps_ddl.result @@ -1833,7 +1833,6 @@ SUCCESS select * from t2; a 1 -1 execute stmt; Warnings: Note 1050 Table 't2' already exists @@ -1843,8 +1842,6 @@ SUCCESS select * from t2; a 1 -1 -1 drop table t2; create temporary table t2 (a varchar(10)); execute stmt; @@ -1852,7 +1849,6 @@ Warnings: Note 1050 Table 't2' already exists select * from t2; a -1 call p_verify_reprepare_count(1); SUCCESS diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result index 36d82e4045f..b9b1c94ce1a 100644 --- a/mysql-test/r/trigger.result +++ b/mysql-test/r/trigger.result @@ -1420,9 +1420,7 @@ create trigger t1_ai after insert on t1 for each row set @a := 7; create table t2 (j int); insert into t2 values (1), (2); set @a:=""; -create table if not exists t1 select * from t2; -Warnings: -Note 1050 Table 't1' already exists +insert into t1 select * from t2; select * from t1; i 7 @@ -1434,9 +1432,7 @@ drop trigger t1_bi; drop trigger t1_ai; create table t3 (isave int); create trigger t1_bi before insert on t1 for each row insert into t3 values (new.i); -create table if not exists t1 select * from t2; -Warnings: -Note 1050 Table 't1' already exists +insert into t1 select * from t2; select * from t1; i 7 @@ -1622,10 +1618,8 @@ After DELETE, old=REPLACE, inserting a new key After INSERT, new=REPLACE, deleting the duplicate truncate t1; truncate t1_op_log; -create table if not exists t1 +insert into t1 select NULL, "CREATE TABLE ... SELECT, inserting a new key"; -Warnings: -Note 1050 Table 't1' already exists set @id=last_insert_id(); select * from t1; id operation @@ -1635,10 +1629,8 @@ operation Before INSERT, new=CREATE TABLE ... SELECT, inserting a new key After INSERT, new=CREATE TABLE ... SELECT, inserting a new key truncate t1_op_log; -create table if not exists t1 replace +replace into t1 select @id, "CREATE TABLE ... REPLACE SELECT, deleting a duplicate key"; -Warnings: -Note 1050 Table 't1' already exists select * from t1; id operation 1 CREATE TABLE ... REPLACE SELECT, deleting a duplicate key @@ -1817,10 +1809,8 @@ After DELETE, old=REPLACE, inserting a new key After INSERT, new=REPLACE, deleting the duplicate truncate t1; truncate t1_op_log; -create table if not exists v1 +insert into v1 select NULL, "CREATE TABLE ... SELECT, inserting a new key"; -Warnings: -Note 1050 Table 'v1' already exists set @id=last_insert_id(); select * from t1; id operation @@ -1830,10 +1820,8 @@ operation Before INSERT, new=CREATE TABLE ... SELECT, inserting a new key After INSERT, new=CREATE TABLE ... SELECT, inserting a new key truncate t1_op_log; -create table if not exists v1 replace +replace into v1 select @id, "CREATE TABLE ... REPLACE SELECT, deleting a duplicate key"; -Warnings: -Note 1050 Table 'v1' already exists select * from t1; id operation 1 CREATE TABLE ... REPLACE SELECT, deleting a duplicate key @@ -2083,7 +2071,8 @@ BEGIN UPDATE a_nonextisting_table SET a = 1; END// CREATE TABLE IF NOT EXISTS t2 ( a INT, b INT ) SELECT a, b FROM t1; -ERROR 42S02: Table 'test.a_nonextisting_table' doesn't exist +Warnings: +Note 1050 Table 't2' already exists SELECT * FROM t2; a b DROP TABLE t1, t2; diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index 2080f4e8b61..33fc4333d1c 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -428,7 +428,7 @@ ERROR 42000: Incorrect usage/placement of 'SQL_CALC_FOUND_ROWS' create temporary table t1 select a from t1 union select a from t2; drop temporary table t1; create table t1 select a from t1 union select a from t2; -ERROR HY000: You can't specify target table 't1' for update in FROM clause +ERROR 42S01: Table 't1' already exists select a from t1 union select a from t2 order by t2.a; ERROR 42S22: Unknown column 't2.a' in 'order clause' drop table t1,t2; diff --git a/mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result b/mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result index 52065ef1a3d..003ce5c9685 100644 --- a/mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result +++ b/mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result @@ -321,7 +321,8 @@ Note 1051 Unknown table 't2' CREATE TABLE t2 (a int, b int, primary key (a)) engine=innodb; INSERT INTO t1 VALUES (4,4); CREATE TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1; -ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +Warnings: +Note 1050 Table 't2' already exists SELECT * from t2; a b TRUNCATE table t2; @@ -339,11 +340,13 @@ Warnings: Warning 1196 Some non-transactional changed tables couldn't be rolled back INSERT INTO t1 values (8,8); CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1; -ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +Warnings: +Note 1050 Table 't2' already exists COMMIT; INSERT INTO t1 values (9,9); CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1; -ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +Warnings: +Note 1050 Table 't2' already exists ROLLBACK; Warnings: Warning 1196 Some non-transactional changed tables couldn't be rolled back @@ -367,11 +370,13 @@ a b 10 10 INSERT INTO t2 values (100,100); CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1; -ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +Warnings: +Note 1050 Table 't2' already exists COMMIT; INSERT INTO t2 values (101,101); CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1; -ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +Warnings: +Note 1050 Table 't2' already exists ROLLBACK; SELECT * from t2; a b @@ -413,41 +418,13 @@ master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # BEGIN -master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS `t2` ( - `a` int(11) NOT NULL DEFAULT '0', - `b` int(11) DEFAULT NULL, - PRIMARY KEY (`a`) -) ENGINE=InnoDB -master-bin.000001 # Query # # COMMIT -master-bin.000001 # Query # # BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # BEGIN -master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS `t2` ( - `a` int(11) NOT NULL DEFAULT '0', - `b` int(11) DEFAULT NULL, - PRIMARY KEY (`a`) -) ENGINE=InnoDB -master-bin.000001 # Query # # ROLLBACK -master-bin.000001 # Query # # BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F master-bin.000001 # Query # # COMMIT -master-bin.000001 # Query # # BEGIN -master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS `t2` ( - `a` int(11) NOT NULL DEFAULT '0', - `b` int(11) DEFAULT NULL, - PRIMARY KEY (`a`) -) ENGINE=InnoDB -master-bin.000001 # Query # # COMMIT -master-bin.000001 # Query # # BEGIN -master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS `t2` ( - `a` int(11) NOT NULL DEFAULT '0', - `b` int(11) DEFAULT NULL, - PRIMARY KEY (`a`) -) ENGINE=InnoDB -master-bin.000001 # Query # # ROLLBACK master-bin.000001 # Query # # use `test`; DROP TABLE `t1` /* generated by server */ master-bin.000001 # Query # # use `test`; DROP TEMPORARY TABLE IF EXISTS `t2` /* generated by server */ reset master; diff --git a/mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result b/mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result index 7c83c1c13a5..90c6f3ad3dc 100644 --- a/mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result +++ b/mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result @@ -315,7 +315,8 @@ Note 1051 Unknown table 't2' CREATE TABLE t2 (a int, b int, primary key (a)) engine=innodb; INSERT INTO t1 VALUES (4,4); CREATE TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1; -ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +Warnings: +Note 1050 Table 't2' already exists SELECT * from t2; a b TRUNCATE table t2; @@ -335,11 +336,13 @@ Warnings: Warning 1196 Some non-transactional changed tables couldn't be rolled back INSERT INTO t1 values (8,8); CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1; -ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +Warnings: +Note 1050 Table 't2' already exists COMMIT; INSERT INTO t1 values (9,9); CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1; -ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +Warnings: +Note 1050 Table 't2' already exists ROLLBACK; Warnings: Warning 1196 Some non-transactional changed tables couldn't be rolled back @@ -363,11 +366,13 @@ a b 10 10 INSERT INTO t2 values (100,100); CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1; -ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +Warnings: +Note 1050 Table 't2' already exists COMMIT; INSERT INTO t2 values (101,101); CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1; -ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +Warnings: +Note 1050 Table 't2' already exists ROLLBACK; SELECT * from t2; a b @@ -412,9 +417,6 @@ master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; INSERT INTO t2 values (100,100) master-bin.000001 # Query # # COMMIT -master-bin.000001 # Query # # BEGIN -master-bin.000001 # Query # # use `test`; INSERT INTO t2 values (101,101) -master-bin.000001 # Query # # ROLLBACK master-bin.000001 # Query # # use `test`; DROP TABLE t1,t2 reset master; create table t1 (a int) engine=innodb; diff --git a/mysql-test/suite/innodb/r/innodb_mysql.result b/mysql-test/suite/innodb/r/innodb_mysql.result index 51beed66f0b..ff325b454ad 100644 --- a/mysql-test/suite/innodb/r/innodb_mysql.result +++ b/mysql-test/suite/innodb/r/innodb_mysql.result @@ -720,7 +720,8 @@ CREATE TABLE t2 (a int, b int, primary key (a)); BEGIN; INSERT INTO t2 values(100,100); CREATE TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1; -ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +Warnings: +Note 1050 Table 't2' already exists SELECT * from t2; a b 100 100 @@ -738,7 +739,8 @@ CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a)); BEGIN; INSERT INTO t2 values(100,100); CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1; -ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +Warnings: +Note 1050 Table 't2' already exists SELECT * from t2; a b 100 100 @@ -746,7 +748,8 @@ COMMIT; BEGIN; INSERT INTO t2 values(101,101); CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1; -ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +Warnings: +Note 1050 Table 't2' already exists SELECT * from t2; a b 100 100 diff --git a/mysql-test/suite/rpl/r/rpl_create_if_not_exists.result b/mysql-test/suite/rpl/r/rpl_create_if_not_exists.result index fc53aca5136..0d3fc1b79a7 100644 --- a/mysql-test/suite/rpl/r/rpl_create_if_not_exists.result +++ b/mysql-test/suite/rpl/r/rpl_create_if_not_exists.result @@ -26,7 +26,6 @@ SHOW TABLES in mysqltest; Tables_in_mysqltest t t1 -t2 SHOW EVENTS in mysqltest; Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation mysqltest e root@localhost SYSTEM ONE TIME # NULL NULL NULL NULL SLAVESIDE_DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci @@ -65,3 +64,65 @@ c1 DROP TABLE t1; DROP TABLE t2; DROP TABLE t3; +# WL#5370 +# The behavior of statement 'CREATE TABLE SELECT IF NOT EXISTS' is changed. +# After the worklog, it will insert nothing and the statement will not be +# binlogged if the table already exists. +# After the worklog, some bugs will disappear automotically. +stop slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +start slave; + +# Case 1: BUG#47132 +call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.*"); +CREATE TABLE t1 (id int); +CREATE TABLE t2 (id int); +INSERT INTO t1 VALUES (1), (1); +INSERT INTO t2 VALUES (2), (2); +CREATE VIEW v1 AS SELECT id FROM t2; +CREATE TABLE IF NOT EXISTS v1(a int, b int) SELECT id, id FROM t1; +Warnings: +Note 1050 Table 'v1' already exists +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +SHOW CREATE TABLE v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t2`.`id` AS `id` from `t2` latin1 latin1_swedish_ci +SELECT * FROM t2; +id +2 +2 +SELECT * FROM v1; +id +2 +2 +DROP VIEW v1; +CREATE TEMPORARY TABLE tt1 AS SELECT id FROM t2; +CREATE TEMPORARY TABLE IF NOT EXISTS tt1(a int, b int) SELECT id, id FROM t1; +Warnings: +Note 1050 Table 'tt1' already exists +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +SELECT * FROM t2; +id +2 +2 +SELECT * FROM tt1; +id +2 +2 +DROP TEMPORARY TABLE tt1; + +# Case 1: BUG#47132 +# RBR breaks on CREATE TABLE IF EXISTS <existing VIEW> AS SELECT +CREATE VIEW v1 AS SELECT 1 as a; +CREATE TABLE IF NOT EXISTS v1 SELECT 2 as a; +Warnings: +Note 1050 Table 'v1' already exists +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +DROP VIEW v1; +DROP TABLE t1, t2; diff --git a/mysql-test/suite/rpl/r/rpl_create_tmp_table_if_not_exists.result b/mysql-test/suite/rpl/r/rpl_create_tmp_table_if_not_exists.result index 8d0b61cc6d8..7459a977cf8 100644 --- a/mysql-test/suite/rpl/r/rpl_create_tmp_table_if_not_exists.result +++ b/mysql-test/suite/rpl/r/rpl_create_tmp_table_if_not_exists.result @@ -19,4 +19,3 @@ master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS t master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS tmp1 LIKE tmp master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS tmp1 LIKE tmp master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS tmp2 SELECT * FROM tmp -master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS tmp2 SELECT * FROM tmp diff --git a/mysql-test/suite/rpl/t/rpl_create_if_not_exists.test b/mysql-test/suite/rpl/t/rpl_create_if_not_exists.test index 114f71af873..6b459bb9c76 100644 --- a/mysql-test/suite/rpl/t/rpl_create_if_not_exists.test +++ b/mysql-test/suite/rpl/t/rpl_create_if_not_exists.test @@ -120,4 +120,57 @@ DROP TABLE t1; DROP TABLE t2; DROP TABLE t3; +--echo # WL#5370 +--echo # The behavior of statement 'CREATE TABLE SELECT IF NOT EXISTS' is changed. +--echo # After the worklog, it will insert nothing and the statement will not be +--echo # binlogged if the table already exists. +--echo # After the worklog, some bugs will disappear automotically. +source include/master-slave-reset.inc; + +--echo +--echo # Case 1: BUG#47132 +connection master; +call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.*"); + +CREATE TABLE t1 (id int); +CREATE TABLE t2 (id int); +INSERT INTO t1 VALUES (1), (1); +INSERT INTO t2 VALUES (2), (2); + +CREATE VIEW v1 AS SELECT id FROM t2; +--let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1) +CREATE TABLE IF NOT EXISTS v1(a int, b int) SELECT id, id FROM t1; +--source include/show_binlog_events.inc + +SHOW CREATE TABLE v1; +SELECT * FROM t2; +SELECT * FROM v1; +DROP VIEW v1; + +# the warning only happens on SBR, so we disable it. +--disable_warnings +CREATE TEMPORARY TABLE tt1 AS SELECT id FROM t2; +--enable_warnings + +--let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1) +CREATE TEMPORARY TABLE IF NOT EXISTS tt1(a int, b int) SELECT id, id FROM t1; +--source include/show_binlog_events.inc +SELECT * FROM t2; +SELECT * FROM tt1; +DROP TEMPORARY TABLE tt1; + +--echo +--echo # Case 1: BUG#47132 +--echo # RBR breaks on CREATE TABLE IF EXISTS <existing VIEW> AS SELECT +CREATE VIEW v1 AS SELECT 1 as a; +--let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1) +CREATE TABLE IF NOT EXISTS v1 SELECT 2 as a; +--source include/show_binlog_events.inc +sync_slave_with_master; + +connection master; +DROP VIEW v1; + +DROP TABLE t1, t2; + source include/master-slave-end.inc; diff --git a/mysql-test/t/auto_increment.test b/mysql-test/t/auto_increment.test index 076e32eb22c..42751b8ce06 100644 --- a/mysql-test/t/auto_increment.test +++ b/mysql-test/t/auto_increment.test @@ -334,7 +334,7 @@ INSERT INTO t1 VALUES (1), (1); CREATE TABLE t2 ( a INT AUTO_INCREMENT KEY ); --error ER_DUP_ENTRY -CREATE TABLE IF NOT EXISTS t2 AS SELECT a FROM t1; +INSERT INTO t2 SELECT a FROM t1; UPDATE t2 SET a = 2; diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test index 887ae4da404..8181b873029 100644 --- a/mysql-test/t/create.test +++ b/mysql-test/t/create.test @@ -5,6 +5,7 @@ --disable_warnings drop table if exists t1,t2,t3,t4,t5; drop database if exists mysqltest; +drop view if exists v1; --enable_warnings create table t1 (b char(0)); @@ -232,7 +233,6 @@ drop table t1; create table t1 select 1,2,3; create table if not exists t1 select 1,2; ---error 1136 create table if not exists t1 select 1,2,3,4; create table if not exists t1 select 1; select * from t1; @@ -248,8 +248,6 @@ insert into t1 values (1,1); create table if not exists t1 select 2; select * from t1; create table if not exists t1 select 3 as 'a',4 as 'b'; ---error ER_DUP_ENTRY -create table if not exists t1 select 3 as 'a',3 as 'b'; show warnings; show status like "Opened_tables"; select * from t1; @@ -517,7 +515,7 @@ drop table t1,t2; # an improper fix is present. # create table t1 (a int); ---error 1093 +--error ER_TABLE_EXISTS_ERROR create table t1 select * from t1; --error ER_WRONG_OBJECT create table t2 union = (t1) select * from t1; @@ -730,7 +728,6 @@ create table t1 select coalesce('a' collate latin1_swedish_ci,'b' collate latin1 create table t1 (primary key(a)) select "b" as b; # Error in select_create::prepare() which is not related to table creation create table t1 (a int); ---error ER_WRONG_VALUE_COUNT_ON_ROW create table if not exists t1 select 1 as a, 2 as b; drop table t1; # Finally error which happens during insert @@ -742,20 +739,13 @@ create table t1 (i int); create table t1 select 1 as i; create table if not exists t1 select 1 as i; select * from t1; -# Error which is detected after successfull table open. ---error ER_UPDATE_TABLE_USED +# After WL#5370, it just generates a warning that the table already exists. create table if not exists t1 select * from t1; select * from t1; +drop table t1; # Error before select_create::prepare() --error ER_CANT_AGGREGATE_2COLLATIONS create table t1 select coalesce('a' collate latin1_swedish_ci,'b' collate latin1_bin); -select * from t1; -# Error which happens during insertion of rows -alter table t1 add primary key (i); ---error ER_DUP_ENTRY -create table if not exists t1 (select 2 as i) union all (select 2 as i); -select * from t1; -drop table t1; # Base vs temporary tables dillema (a.k.a. bug#24508 "Inconsistent @@ -1229,11 +1219,9 @@ INSERT IGNORE INTO t1 (b) VALUES (5); CREATE TABLE IF NOT EXISTS t2 (a INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY) SELECT a FROM t1; --error 1062 -CREATE TABLE IF NOT EXISTS t2 (a INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY) - SELECT a FROM t1; +INSERT INTO t2 SELECT a FROM t1; --error 1062 -CREATE TABLE IF NOT EXISTS t2 (a INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY) - SELECT a FROM t1; +INSERT INTO t2 SELECT a FROM t1; DROP TABLE t1, t2; @@ -1273,7 +1261,7 @@ CREATE TEMPORARY TABLE t2 (primary key (a)) select * from t1; drop table if exists t2; CREATE TABLE t2 (a int, b int, primary key (a)); --error ER_DUP_ENTRY -CREATE TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1; +INSERT INTO t2 select * from t1; SELECT * from t2; TRUNCATE table t2; --error ER_DUP_ENTRY @@ -1283,11 +1271,7 @@ drop table t2; CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a)); --error ER_DUP_ENTRY -CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1; -SELECT * from t2; -TRUNCATE table t2; ---error ER_DUP_ENTRY -INSERT INTO t2 select * from t1; +INSERT INTO t2 SELECT * FROM t1; SELECT * from t2; drop table t1,t2; @@ -1657,11 +1641,7 @@ END ; | --delimiter ; --error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG -CREATE TABLE IF NOT EXISTS t1 ( - `pk` INTEGER NOT NULL AUTO_INCREMENT , - `int` INTEGER , - PRIMARY KEY ( `pk` ) -) SELECT `pk` , `int_key` FROM B ; +INSERT INTO t1 (pk, int_key) SELECT `pk` , `int_key` FROM B ; --delimiter | --error ER_NOT_SUPPORTED_YET @@ -1675,7 +1655,6 @@ END ;| DROP TABLE t1; DROP TABLE B; - --echo # --echo # Bug #47107 assert in notify_shared_lock on incorrect --echo # CREATE TABLE , HANDLER @@ -1798,3 +1777,243 @@ show create table t1; show create table t2; set @@sql_mode= @old_mode; drop tables t1, t2; +# +# Bug#47132 CREATE TABLE.. SELECT.. data not inserted if table +# is view over multiple tables +# + +CREATE TABLE t1 (id int); +CREATE TABLE t2 (id int); +INSERT INTO t1 VALUES (1), (1); +INSERT INTO t2 VALUES (2), (2); + +CREATE VIEW v1 AS SELECT id FROM t2; +CREATE TABLE IF NOT EXISTS v1(a int, b int) SELECT id, id FROM t1; +SHOW CREATE TABLE v1; +SELECT * FROM t2; +SELECT * FROM v1; +DROP VIEW v1; + +CREATE TEMPORARY TABLE tt1 AS SELECT id FROM t2; +CREATE TEMPORARY TABLE IF NOT EXISTS tt1(a int, b int) SELECT id, id FROM t1; +SELECT * FROM t2; +SELECT * FROM tt1; +DROP TEMPORARY TABLE tt1; + +DROP TABLE t1, t2; + + +--echo # +--echo # WL#5370 "Changing 'CREATE TABLE IF NOT EXISTS ... SELECT' +--echo # behaviour. +--echo # + +--echo # +--echo # 1. Basic case: a base table. +--echo # + +create table if not exists t1 (a int) select 1 as a; +select * from t1; +--error ER_TABLE_EXISTS_ERROR +create table t1 (a int) select 2 as a; +select * from t1; +--echo # Produces an essential warning ER_TABLE_EXISTS. +create table if not exists t1 (a int) select 2 as a; +--echo # No new data in t1. +select * from t1; +drop table t1; + +--echo # +--echo # 2. A temporary table. +--echo # + +create temporary table if not exists t1 (a int) select 1 as a; +select * from t1; +--error ER_TABLE_EXISTS_ERROR +create temporary table t1 (a int) select 2 as a; +select * from t1; +--echo # An essential warning. +create temporary table if not exists t1 (a int) select 2 as a; +--echo # No new data in t1. +select * from t1; +drop temporary table t1; + +--echo # +--echo # 3. Creating a base table in presence of a temporary table. +--echo # + +create table t1 (a int); +--echo # Create a view for convenience of querying t1 shadowed by a temp. +create view v1 as select a from t1; +drop table t1; +create temporary table t1 (a int) select 1 as a; +create table if not exists t1 (a int) select 2 as a; +select * from t1; +select * from v1; +--echo # Note: an essential warning. +create table if not exists t1 (a int) select 3 as a; +select * from t1; +select * from v1; +drop temporary table t1; +select * from t1; +drop view v1; +drop table t1; + +--echo # +--echo # 4. Creating a temporary table in presence of a base table. +--echo # + +create table t1 (a int) select 1 as a; +create temporary table if not exists t1 select 2 as a; +select * from t1; +--echo # Note: an essential warning. +create temporary table if not exists t1 select 3 as a; +select * from t1; +drop temporary table t1; +select * from t1; +drop table t1; + +--echo # +--echo # 5. Creating a base table in presence of an updatable view. +--echo # +create table t2 (a int unique); +create view t1 as select a from t2; +insert into t1 (a) values (1); +--error ER_TABLE_EXISTS_ERROR +create table t1 (a int); +--echo # Note: an essential warning. +create table if not exists t1 (a int); +--error ER_TABLE_EXISTS_ERROR +create table t1 (a int) select 2 as a; +select * from t1; +--echo # Note: an essential warning. +create table if not exists t1 (a int) select 2 as a; +select * from t1; +select * from t2; +create temporary table if not exists t1 (a int) select 3 as a; +select * from t1; +select * from t2; +--echo # Note: an essential warning. +create temporary table if not exists t1 (a int) select 4 as a; +select * from t1; +select * from t2; +drop temporary table t1; + +--echo # +--echo # Repeating the test with a non-updatable view. +--echo # +drop view t1; +create view t1 as select a + 5 as a from t2; +--error ER_NON_INSERTABLE_TABLE +insert into t1 (a) values (1); +--error ER_NONUPDATEABLE_COLUMN +update t1 set a=3 where a=2; + +--error ER_TABLE_EXISTS_ERROR +create table t1 (a int); +--echo # Note: an essential warning. +create table if not exists t1 (a int); +--error ER_TABLE_EXISTS_ERROR +create table t1 (a int) select 2 as a; +select * from t1; +--echo # Note: an essential warning. +create table if not exists t1 (a int) select 2 as a; +select * from t1; +select * from t2; +create temporary table if not exists t1 (a int) select 3 as a; +select * from t1; +select * from t2; +--echo # Note: an essential warning. +create temporary table if not exists t1 (a int) select 4 as a; +select * from t1; +select * from t2; +drop temporary table t1; +drop view t1; +drop table t2; + +--echo # +--echo # Repeating the test with a view select a constant number +--echo # +create view t1 as select 1 as a; +--error ER_NON_INSERTABLE_TABLE +insert into t1 (a) values (1); +--error ER_NON_UPDATABLE_TABLE +update t1 set a=3 where a=2; + +--error ER_TABLE_EXISTS_ERROR +create table t1 (a int); +--echo # Note: an essential warning. +create table if not exists t1 (a int); +--error ER_TABLE_EXISTS_ERROR +create table t1 (a int) select 2 as a; +select * from t1; +--echo # Note: an essential warning. +create table if not exists t1 (a int) select 2 as a; +select * from t1; +create temporary table if not exists t1 (a int) select 3 as a; +select * from t1; +--echo # Note: an essential warning. +create temporary table if not exists t1 (a int) select 4 as a; +select * from t1; +drop temporary table t1; +drop view t1; + + +--echo # +--echo # 6. Test of unique_table(). +--echo # + +create table t1 (a int) select 1 as a; +create temporary table if not exists t1 (a int) select * from t1; +--error ER_CANT_REOPEN_TABLE +create temporary table if not exists t1 (a int) select * from t1; +select * from t1; +drop temporary table t1; +select * from t1; +drop table t1; +create temporary table t1 (a int) select 1 as a; +create table if not exists t1 (a int) select * from t1; +create table if not exists t1 (a int) select * from t1; +select * from t1; +drop temporary table t1; +select * from t1; +drop table t1; +--error ER_NO_SUCH_TABLE +create table if not exists t1 (a int) select * from t1; + +--echo # +--echo # 7. Test of non-matching columns, REPLACE and IGNORE. +--echo # + +create table t1 (a int) select 1 as b, 2 as c; +select * from t1; +drop table t1; +create table if not exists t1 (a int, b date, c date) select 1 as b, 2 as c; +select * from t1; +drop table t1; +set @@session.sql_mode='STRICT_ALL_TABLES'; +--error ER_TRUNCATED_WRONG_VALUE +create table if not exists t1 (a int, b date, c date) select 1 as b, 2 as c; +--error ER_NO_SUCH_TABLE +select * from t1; +--error ER_TRUNCATED_WRONG_VALUE +create table if not exists t1 (a int, b date, c date) + replace select 1 as b, 2 as c; +--error ER_NO_SUCH_TABLE +select * from t1; + +create table if not exists t1 (a int, b date, c date) + ignore select 1 as b, 2 as c; +select * from t1; +set @@session.sql_mode=default; +drop table t1; + +create table if not exists t1 (a int unique, b int) + replace select 1 as a, 1 as b union select 1 as a, 2 as b; +select * from t1; +drop table t1; +create table if not exists t1 (a int unique, b int) + ignore select 1 as a, 1 as b union select 1 as a, 2 as b; +select * from t1; +drop table t1; +--echo # diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test index 31bc8a5e881..b6ad3324d19 100644 --- a/mysql-test/t/merge.test +++ b/mysql-test/t/merge.test @@ -579,7 +579,7 @@ DROP TABLE tm1, t1, t2; # CREATE TABLE t1(c1 INT); CREATE TABLE t2 (c1 INT) ENGINE=MERGE UNION=(t1) INSERT_METHOD=FIRST; ---error ER_UPDATE_TABLE_USED +# After WL#5370, it just generates a warning that the table already exists CREATE TABLE IF NOT EXISTS t1 SELECT * FROM t2; DROP TABLE t1, t2; diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test index 2eb086cace5..3e4c3660f88 100644 --- a/mysql-test/t/trigger.test +++ b/mysql-test/t/trigger.test @@ -1751,7 +1751,7 @@ create trigger t1_ai after insert on t1 for each row set @a := 7; create table t2 (j int); insert into t2 values (1), (2); set @a:=""; -create table if not exists t1 select * from t2; +insert into t1 select * from t2; select * from t1; select @a; # Let us check that trigger that involves table also works ok. @@ -1759,7 +1759,7 @@ drop trigger t1_bi; drop trigger t1_ai; create table t3 (isave int); create trigger t1_bi before insert on t1 for each row insert into t3 values (new.i); -create table if not exists t1 select * from t2; +insert into t1 select * from t2; select * from t1; select * from t3; drop table t1, t2, t3; @@ -1975,7 +1975,7 @@ select * from t1_op_log; truncate t1; truncate t1_op_log; -create table if not exists t1 +insert into t1 select NULL, "CREATE TABLE ... SELECT, inserting a new key"; set @id=last_insert_id(); @@ -1984,7 +1984,7 @@ select * from t1; select * from t1_op_log; truncate t1_op_log; -create table if not exists t1 replace +replace into t1 select @id, "CREATE TABLE ... REPLACE SELECT, deleting a duplicate key"; select * from t1; @@ -2114,7 +2114,7 @@ select * from t1_op_log; truncate t1; truncate t1_op_log; -create table if not exists v1 +insert into v1 select NULL, "CREATE TABLE ... SELECT, inserting a new key"; set @id=last_insert_id(); @@ -2123,7 +2123,7 @@ select * from t1; select * from t1_op_log; truncate t1_op_log; -create table if not exists v1 replace +replace into v1 select @id, "CREATE TABLE ... REPLACE SELECT, deleting a duplicate key"; select * from t1; diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index ab2745ceeef..93dc4cad50c 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -253,7 +253,7 @@ SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a desc LIMIT 1; create temporary table t1 select a from t1 union select a from t2; drop temporary table t1; ---error 1093 +--error ER_TABLE_EXISTS_ERROR create table t1 select a from t1 union select a from t2; --error 1054 select a from t1 union select a from t2 order by t2.a; |