diff options
Diffstat (limited to 'mysql-test/t/create.test')
| -rw-r--r-- | mysql-test/t/create.test | 383 |
1 files changed, 376 insertions, 7 deletions
diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test index 2ec416cfc87..5ffa1b93929 100644 --- a/mysql-test/t/create.test +++ b/mysql-test/t/create.test @@ -41,8 +41,11 @@ create table t1 (ordid int(8) not null auto_increment, ord varchar(50) not null -- error 1049 create table not_existing_database.test (a int); ---error 1103 create table `a/a` (a int); +show create table `a/a`; +create table t1 like `a/a`; +drop table `a/a`; +drop table `t1`; --error 1103 create table `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa` (aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa int); --error 1059 @@ -150,7 +153,7 @@ drop table if exists t1,t2; CREATE TABLE t1 (a int not null); INSERT INTO t1 values (1),(2),(1); ---error 1062 +--error ER_DUP_ENTRY CREATE TABLE t2 (primary key(a)) SELECT * FROM t1; --error 1146 SELECT * from t2; @@ -223,13 +226,16 @@ drop table t1; # Test create table if not exists with duplicate key error # +flush status; create table t1 (a int not null, b int, primary key (a)); 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 1062 +--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; drop table t1; @@ -300,12 +306,10 @@ create table t3 like t1; create table t3 like mysqltest.t3; --error 1049 create table non_existing_database.t1 like t1; ---error 1051 +--error ER_NO_SUCH_TABLE create table t3 like non_existing_table; --error 1050 create temporary table t3 like t1; ---error 1103 -create table t3 like `a/a`; drop table t1, t2, t3; drop table t3; drop database mysqltest; @@ -479,7 +483,7 @@ drop table t1,t2; create table t1 (a int); --error 1093 create table t1 select * from t1; ---error 1093 +--error ER_WRONG_OBJECT create table t2 union = (t1) select * from t1; flush tables with read lock; unlock tables; @@ -1213,3 +1217,368 @@ DROP VIEW v1; DROP TABLE t1,t2; --echo End of 5.0 tests + +# +# Test of behaviour with CREATE ... SELECT +# + +CREATE TABLE t1 (a int, b int); +insert into t1 values (1,1),(1,2); +--error ER_DUP_ENTRY +CREATE TABLE t2 (primary key (a)) select * from t1; +# This should give warning +drop table if exists t2; +--error ER_DUP_ENTRY +CREATE TEMPORARY TABLE t2 (primary key (a)) select * from t1; +# This should give warning +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; +SELECT * from t2; +TRUNCATE table t2; +--error ER_DUP_ENTRY +INSERT INTO t2 select * from t1; +SELECT * from t2; +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; +SELECT * from t2; +drop table t1,t2; + + +# +# Test incorrect database names +# + +--error 1102 +CREATE DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa; +--error 1102 +DROP DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa; + +# TODO: enable these tests when RENAME DATABASE is implemented. +# --error 1049 +# RENAME DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa TO a; +# --error 1102 +# RENAME DATABASE mysqltest TO aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa; +# create database mysqltest; +# --error 1102 +# RENAME DATABASE mysqltest TO aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa; +# drop database mysqltest; + +--error 1102 +USE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa; +--error 1102 +SHOW CREATE DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa; + +# +# Bug#21432 Database/Table name limited to 64 bytes, not chars, problems with multi-byte +# +set names utf8; + +create database имя_базы_в_кодировке_утф8_длиной_больше_чем_45; +use имя_базы_в_кодировке_утф8_длиной_больше_чем_45; +select database(); +use test; + +select SCHEMA_NAME from information_schema.schemata +where schema_name='имя_базы_в_кодировке_утф8_длиной_больше_чем_45'; + +drop database имя_базы_в_кодировке_утф8_длиной_больше_чем_45; +create table имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48 +( + имя_поля_в_кодировке_утф8_длиной_больше_чем_45 int, + index имя_индекса_в_кодировке_утф8_длиной_больше_чем_48 (имя_поля_в_кодировке_утф8_длиной_больше_чем_45) +); + +create view имя_вью_кодировке_утф8_длиной_больше_чем_42 as +select имя_поля_в_кодировке_утф8_длиной_больше_чем_45 +from имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48; + +# database, table, field, key, view +select * from имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48; + +select TABLE_NAME from information_schema.tables where +table_schema='test'; + +select COLUMN_NAME from information_schema.columns where +table_schema='test'; + +select INDEX_NAME from information_schema.statistics where +table_schema='test'; + +select TABLE_NAME from information_schema.views where +table_schema='test'; + +show create table имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48; +show create view имя_вью_кодировке_утф8_длиной_больше_чем_42; + +create trigger имя_триггера_в_кодировке_утф8_длиной_больше_чем_49 +before insert on имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48 for each row set @a:=1; +select TRIGGER_NAME from information_schema.triggers where +trigger_schema='test'; +drop trigger имя_триггера_в_кодировке_утф8_длиной_больше_чем_49; +--error 1059 +create trigger +очень_очень_очень_очень_очень_очень_очень_очень_длинная_строка_66 +before insert on имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48 for each row set @a:=1; +--error 1059 +drop trigger очень_очень_очень_очень_очень_очень_очень_очень_длинная_строка_66; + +create procedure имя_процедуры_в_кодировке_утф8_длиной_больше_чем_50() +begin +end; +select ROUTINE_NAME from information_schema.routines where +routine_schema='test'; +drop procedure имя_процедуры_в_кодировке_утф8_длиной_больше_чем_50; +--error 1059 +create procedure очень_очень_очень_очень_очень_очень_очень_очень_длинная_строка_66() +begin +end; + +create function имя_функции_в_кодировке_утф8_длиной_больше_чем_49() + returns int +return 0; +select ROUTINE_NAME from information_schema.routines where +routine_schema='test'; +drop function имя_функции_в_кодировке_утф8_длиной_больше_чем_49; +--error 1059 +create function очень_очень_очень_очень_очень_очень_очень_очень_длинная_строка_66() + returns int +return 0; + +drop view имя_вью_кодировке_утф8_длиной_больше_чем_42; +drop table имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48; +set names default; + +# +# Bug#21136 CREATE TABLE SELECT within CREATE TABLE SELECT causes server crash +# + +--disable_warnings +drop table if exists t1,t2,t3; +drop function if exists f1; +--enable_warnings + +--delimiter | +create function f1() returns int +begin + declare res int; + create temporary table t3 select 1 i; + set res:= (select count(*) from t1); + drop temporary table t3; + return res; +end| +--delimiter ; +create table t1 as select 1; +create table t2 as select f1() from t1; +drop table t1,t2; +drop function f1; + +# +# Bug#25629 CREATE TABLE LIKE does not work with INFORMATION_SCHEMA +# +create table t1 like information_schema.processlist; +show create table t1; +drop table t1; +create temporary table t1 like information_schema.processlist; +show create table t1; +drop table t1; +create table t1 like information_schema.character_sets; +show create table t1; +drop table t1; + +########################################################################### + +--echo +--echo # -- +--echo # -- Bug#21380: DEFAULT definition not always transfered by CREATE +--echo # -- TABLE/SELECT to the new table. +--echo # -- +--echo + + +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t2; +--enable_warnings + +--echo + +CREATE TABLE t1( + c1 INT DEFAULT 12 COMMENT 'column1', + c2 INT NULL COMMENT 'column2', + c3 INT NOT NULL COMMENT 'column3', + c4 VARCHAR(255) CHARACTER SET utf8 NOT NULL DEFAULT 'a', + c5 VARCHAR(255) COLLATE utf8_unicode_ci NULL DEFAULT 'b', + c6 VARCHAR(255)) + COLLATE latin1_bin; + +--echo + +SHOW CREATE TABLE t1; + +--echo + +CREATE TABLE t2 AS SELECT * FROM t1; + +--echo + +SHOW CREATE TABLE t2; + +--echo + +DROP TABLE t2; +DROP TABLE t1; + +--echo +--echo # -- End of test case for Bug#21380. + +########################################################################### + +--echo +--echo # -- +--echo # -- Bug#18834: ALTER TABLE ADD INDEX on table with two timestamp fields +--echo # -- +--echo + +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t2; +DROP TABLE IF EXISTS t3; +--enable_warnings + +--echo + +CREATE TABLE t1(c1 TIMESTAMP, c2 TIMESTAMP); + +--echo + +SET sql_mode = NO_ZERO_DATE; + +--echo +--error ER_INVALID_DEFAULT +CREATE TABLE t2(c1 TIMESTAMP, c2 TIMESTAMP DEFAULT 0); + +--echo +--error ER_INVALID_DEFAULT +CREATE TABLE t2(c1 TIMESTAMP, c2 TIMESTAMP); + +--echo +--echo # -- Check that NULL column still can be created. +CREATE TABLE t2(c1 TIMESTAMP NULL); + +--echo +--echo # -- Check ALTER TABLE. +--error ER_INVALID_DEFAULT +ALTER TABLE t1 ADD INDEX(c1); + +--echo +--echo # -- Check DATETIME. +SET sql_mode = ''; + +--echo + +CREATE TABLE t3(c1 DATETIME NOT NULL); +INSERT INTO t3 VALUES (0); + +--echo +SET sql_mode = TRADITIONAL; + +--echo +--error ER_TRUNCATED_WRONG_VALUE +ALTER TABLE t3 ADD INDEX(c1); + +--echo +--echo # -- Cleanup. + +SET sql_mode = ''; +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; + +--echo +--echo # -- End of Bug#18834. + +########################################################################### + +--echo +--echo # -- +--echo # -- Bug#34274: Invalid handling of 'DEFAULT 0' for YEAR data type. +--echo # -- +--echo + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +--echo +CREATE TABLE t1(c1 YEAR DEFAULT 2008, c2 YEAR DEFAULT 0); + +--echo +SHOW CREATE TABLE t1; + +--echo +INSERT INTO t1 VALUES(); + +--echo +SELECT * FROM t1; + +--echo +ALTER TABLE t1 MODIFY c1 YEAR DEFAULT 0; + +--echo +SHOW CREATE TABLE t1; + +--echo +INSERT INTO t1 VALUES(); + +--echo +SELECT * FROM t1; + +--echo +DROP TABLE t1; + +--echo +--echo # -- End of Bug#34274 + +########################################################################### + +# +# Bug#40104 regression with table names? +# +create table `me:i`(id int); +drop table `me:i`; + +########################################################################### + +# +# Bug#45829 CREATE TABLE TRANSACTIONAL PAGE_CHECKSUM ROW_FORMAT=PAGE accepted, does nothing +# + +--echo +--echo # -- +--echo # -- Bug#45829: CREATE TABLE TRANSACTIONAL PAGE_CHECKSUM ROW_FORMAT=PAGE accepted, does nothing +--echo # -- +--echo + +--disable_warnings +drop table if exists t1,t2,t3; +--enable_warnings +--error ER_PARSE_ERROR +create table t1 (a int) transactional=0; +--error ER_PARSE_ERROR +create table t2 (a int) page_checksum=1; +--error ER_PARSE_ERROR +create table t3 (a int) row_format=page; +--echo +--echo # -- End of Bug#45829 + +--echo +--echo End of 5.1 tests |