diff options
Diffstat (limited to 'mysql-test/t/create.test')
| -rw-r--r-- | mysql-test/t/create.test | 484 |
1 files changed, 460 insertions, 24 deletions
diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test index 692721b5955..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)); @@ -102,6 +103,22 @@ create table t1 (`` int); create table t1 (i int, index `` (i)); # +# CREATE TABLE under LOCK TABLES +# +# We don't allow creation of non-temporary tables under LOCK TABLES +# as following meta-data locking protocol in this case can lead to +# deadlock. +create table t1 (i int); +lock tables t1 read; +--error ER_TABLE_NOT_LOCKED +create table t2 (j int); +# OTOH creating of temporary table should be OK +create temporary table t2 (j int); +drop temporary table t2; +unlock tables; +drop table t1; + +# # Test of CREATE ... SELECT with indexes # @@ -216,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; @@ -232,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; @@ -315,6 +329,26 @@ drop table t3; drop database mysqltest; # +# CREATE TABLE LIKE under LOCK TABLES +# +# Similarly to ordinary CREATE TABLE we don't allow creation of +# non-temporary tables under LOCK TABLES. Also we require source +# table to be locked. +create table t1 (i int); +create table t2 (j int); +lock tables t1 read; +--error ER_TABLE_NOT_LOCKED +create table t3 like t1; +# OTOH creating of temporary table should be OK +create temporary table t3 like t1; +drop temporary table t3; +# Source table should be locked +--error ER_TABLE_NOT_LOCKED +create temporary table t3 like t2; +unlock tables; +drop tables t1, t2; + +# # Test default table type # SET SESSION storage_engine="heap"; @@ -481,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; @@ -683,8 +717,8 @@ drop table t1; # Error during open_and_lock_tables() of tables --error ER_NO_SUCH_TABLE create table t1 select * from t2; -# Rather special error which also caught during open tables pahse ---error ER_UPDATE_TABLE_USED +# A special case which is also caught during open tables pahse +--error ER_NO_SUCH_TABLE create table t1 select * from t1; # Error which happens before select_create::prepare() --error ER_CANT_AGGREGATE_2COLLATIONS @@ -694,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 @@ -706,16 +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; +# 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 @@ -1189,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; @@ -1233,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 @@ -1243,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; @@ -1581,3 +1605,415 @@ create table t3 (a int) row_format=page; --echo --echo End of 5.1 tests + + +########################################################################### + +--echo +--echo # -- +--echo # -- Bug #43054 Assertion `!table->auto_increment_field_not_null' +--echo # -- failed when redefining trigger +--echo + +#--disable_abort_on_error + +CREATE TABLE B ( + pk INTEGER AUTO_INCREMENT, + int_key INTEGER NOT NULL, + PRIMARY KEY (pk), + KEY (int_key) +); + +INSERT IGNORE INTO B VALUES ('9', '9'); + +CREATE TABLE IF NOT EXISTS t1 ( + `pk` INTEGER NOT NULL AUTO_INCREMENT , + `int` INTEGER , + PRIMARY KEY ( `pk` ) +) SELECT `pk` , `int_key` FROM B ; + +--delimiter | + +CREATE TRIGGER f BEFORE INSERT ON t1 FOR EACH ROW +BEGIN + INSERT INTO t1 ( `int` ) VALUES (4 ),( 8 ),( 2 ) ; +END ; | + +--delimiter ; +--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG +INSERT INTO t1 (pk, int_key) SELECT `pk` , `int_key` FROM B ; + +--delimiter | +--error ER_NOT_SUPPORTED_YET +CREATE TRIGGER f BEFORE INSERT ON t1 FOR EACH ROW +BEGIN + UPDATE A SET `pk`=1 WHERE `pk`=0 ; +END ;| + +--delimiter ; + +DROP TABLE t1; +DROP TABLE B; + +--echo # +--echo # Bug #47107 assert in notify_shared_lock on incorrect +--echo # CREATE TABLE , HANDLER +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1(f1 integer); + +--echo # The following CREATE TABLEs before gave an assert. + +HANDLER t1 OPEN AS A; +--error ER_TABLE_EXISTS_ERROR +CREATE TABLE t1 SELECT 1 AS f2; + +HANDLER t1 OPEN AS A; +--error ER_TABLE_EXISTS_ERROR +CREATE TABLE t1(f1 integer); + +CREATE TABLE t2(f1 integer); +HANDLER t1 OPEN AS A; +--error ER_TABLE_EXISTS_ERROR +CREATE TABLE t1 LIKE t2; + +DROP TABLE t2; +DROP TABLE t1; + +--echo # +--echo # Bug #48800 CREATE TABLE t...SELECT fails if t is a +--echo # temporary table +--echo # + +CREATE TEMPORARY TABLE t1 (a INT); +CREATE TABLE t1 (a INT); + +CREATE TEMPORARY TABLE t2 (a INT); +CREATE VIEW t2 AS SELECT 1; + +CREATE TABLE t3 (a INT); +CREATE TEMPORARY TABLE t3 SELECT 1; + +CREATE TEMPORARY TABLE t4 (a INT); +CREATE TABLE t4 AS SELECT 1; + +DROP TEMPORARY TABLE t1, t2, t3, t4; +DROP TABLE t1, t3, t4; +DROP VIEW t2; + +--echo # +--echo # Bug #49193 CREATE TABLE reacts differently depending +--echo # on whether data is selected or not +--echo # + +CREATE TEMPORARY TABLE t2 (ID INT); +INSERT INTO t2 VALUES (1),(2),(3); + +# Case 1 -- did not fail +CREATE TEMPORARY TABLE t1 (ID INT); +CREATE TABLE IF NOT EXISTS t1 (ID INT); +INSERT INTO t1 SELECT * FROM t2; +SELECT * FROM t1; +DROP TEMPORARY TABLE t1; +SELECT * FROM t1; + +DROP TABLE t1; + +# Case 2 -- The DROP TABLE t1 failed with +# Table 'test.t1' doesn't exist in the SELECT * +# as the (permanent) table was not created +CREATE TEMPORARY TABLE t1 (ID INT); +CREATE TABLE IF NOT EXISTS t1 SELECT * FROM t2; +SELECT * FROM t1; +DROP TEMPORARY TABLE t1; +SELECT * FROM t1; + +DROP TABLE t1; + +# Case 3 -- The CREATE TABLE failed with +# Table 't1' already exists +CREATE TEMPORARY TABLE t1 (ID INT); +CREATE TABLE t1 SELECT * FROM t2; +SELECT * FROM t1; +DROP TEMPORARY TABLE t1; +SELECT * FROM t1; + +DROP TABLE t1; + +DROP TEMPORARY TABLE t2; + + +--echo # +--echo # Bug #22909 "Using CREATE ... LIKE is possible to create field +--echo # with invalid default value" +--echo # +--echo # Altough original bug report suggests to use older version of MySQL +--echo # for producing .FRM with invalid defaults we use sql_mode to achieve +--echo # the same effect. +--disable_warnings +drop tables if exists t1, t2; +--enable_warnings +--echo # Attempt to create table with invalid default should fail in normal mode +--error ER_INVALID_DEFAULT +create table t1 (dt datetime default '2008-02-31 00:00:00'); +set @old_mode= @@sql_mode; +set @@sql_mode='ALLOW_INVALID_DATES'; +--echo # The same should be possible in relaxed mode +create table t1 (dt datetime default '2008-02-31 00:00:00'); +set @@sql_mode= @old_mode; +--echo # In normal mode attempt to create copy of table with invalid +--echo # default should fail +--error ER_INVALID_DEFAULT +create table t2 like t1; +set @@sql_mode='ALLOW_INVALID_DATES'; +--echo # But should work in relaxed mode +create table t2 like t1; +--echo # Check that table definitions match +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 # |