diff options
Diffstat (limited to 'mysql-test/r/create.result')
| -rw-r--r-- | mysql-test/r/create.result | 215 |
1 files changed, 145 insertions, 70 deletions
diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 29af76ed8df..8dd3cc8f643 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -113,7 +113,7 @@ insert into t1 (b) values ("hello"),("my"),("world"); create table t2 (key (b)) select * from t1; explain select * from t2 where b="world"; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref B B 21 const 1 Using where +1 SIMPLE t2 ref B B 21 const 1 Using index condition select * from t2 where b="world"; a B 3 world @@ -303,14 +303,14 @@ ERROR 42000: Incorrect database name 'db1 ' create table t1(`a ` int); ERROR 42000: Incorrect column name 'a ' create table t1 (a int,); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1 create table t1 (a int,,b int); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'b int)' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'b int)' at line 1 create table t1 (,b int); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'b int)' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'b int)' at line 1 create table t1 (a int, key(a)); create table t2 (b int, foreign key(b) references t1(a), key(b)); -drop table if exists t1,t2; +drop table if exists t2,t1; create table t1(id int not null, name char(20)); insert into t1 values(10,'mysql'),(20,'monty- the creator'); create table t2(id int not null); @@ -1489,10 +1489,10 @@ ERROR 42000: Too many keys specified; max 64 keys allowed drop table t1; create table t1 (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int, c9 int, c10 int, c11 int, c12 int, c13 int, c14 int, c15 int, -c16 int, c17 int); +c16 int, c17 int, c18 int,c19 int,c20 int,c21 int,c22 int,c23 int,c24 int,c25 int,c26 int,c27 int,c28 int,c29 int,c30 int,c31 int,c32 int, c33 int); alter table t1 add key i1 ( -c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16, c17); -ERROR 42000: Too many key parts specified; max 16 parts allowed +c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16, c17,c18,c19,c20,c21,c22,c23,c24,c25,c26,c27,c28,c29,c30,c31,c32,c33); +ERROR 42000: Too many key parts specified; max 32 parts allowed alter table t1 add key a001_long_123456789_123456789_123456789_123456789_123456789_12345 (c1); ERROR 42000: Identifier name 'a001_long_123456789_123456789_123456789_123456789_123456789_12345' is too long @@ -1515,7 +1515,23 @@ t1 CREATE TABLE `t1` ( `c14` int(11) DEFAULT NULL, `c15` int(11) DEFAULT NULL, `c16` int(11) DEFAULT NULL, - `c17` int(11) DEFAULT NULL + `c17` int(11) DEFAULT NULL, + `c18` int(11) DEFAULT NULL, + `c19` int(11) DEFAULT NULL, + `c20` int(11) DEFAULT NULL, + `c21` int(11) DEFAULT NULL, + `c22` int(11) DEFAULT NULL, + `c23` int(11) DEFAULT NULL, + `c24` int(11) DEFAULT NULL, + `c25` int(11) DEFAULT NULL, + `c26` int(11) DEFAULT NULL, + `c27` int(11) DEFAULT NULL, + `c28` int(11) DEFAULT NULL, + `c29` int(11) DEFAULT NULL, + `c30` int(11) DEFAULT NULL, + `c31` int(11) DEFAULT NULL, + `c32` int(11) DEFAULT NULL, + `c33` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; @@ -1546,6 +1562,7 @@ Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 +Handler_read_rnd_deleted 0 Handler_read_rnd_next 7 drop table t1,t2; CREATE TABLE t1(c1 VARCHAR(33), KEY USING BTREE (c1)); @@ -1735,28 +1752,36 @@ show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `ID` bigint(4) NOT NULL DEFAULT '0', - `USER` varchar(16) NOT NULL DEFAULT '', + `USER` varchar(128) NOT NULL DEFAULT '', `HOST` varchar(64) NOT NULL DEFAULT '', `DB` varchar(64) DEFAULT NULL, `COMMAND` varchar(16) NOT NULL DEFAULT '', `TIME` int(7) NOT NULL DEFAULT '0', `STATE` varchar(64) DEFAULT NULL, - `INFO` longtext -) ENGINE=MyISAM DEFAULT CHARSET=utf8 + `INFO` longtext, + `TIME_MS` decimal(22,3) NOT NULL DEFAULT '0.000', + `STAGE` tinyint(2) NOT NULL DEFAULT '0', + `MAX_STAGE` tinyint(2) NOT NULL DEFAULT '0', + `PROGRESS` decimal(7,3) NOT NULL DEFAULT '0.000' +) DEFAULT CHARSET=utf8 drop table t1; create temporary table t1 like information_schema.processlist; show create table t1; Table Create Table t1 CREATE TEMPORARY TABLE `t1` ( `ID` bigint(4) NOT NULL DEFAULT '0', - `USER` varchar(16) NOT NULL DEFAULT '', + `USER` varchar(128) NOT NULL DEFAULT '', `HOST` varchar(64) NOT NULL DEFAULT '', `DB` varchar(64) DEFAULT NULL, `COMMAND` varchar(16) NOT NULL DEFAULT '', `TIME` int(7) NOT NULL DEFAULT '0', `STATE` varchar(64) DEFAULT NULL, - `INFO` longtext -) ENGINE=MyISAM DEFAULT CHARSET=utf8 + `INFO` longtext, + `TIME_MS` decimal(22,3) NOT NULL DEFAULT '0.000', + `STAGE` tinyint(2) NOT NULL DEFAULT '0', + `MAX_STAGE` tinyint(2) NOT NULL DEFAULT '0', + `PROGRESS` decimal(7,3) NOT NULL DEFAULT '0.000' +) DEFAULT CHARSET=utf8 drop table t1; create table t1 like information_schema.character_sets; show create table t1; @@ -1770,52 +1795,6 @@ t1 CREATE TABLE `t1` ( drop table t1; # -- -# -- Bug#21380: DEFAULT definition not always transfered by CREATE -# -- TABLE/SELECT to the new table. -# -- - -DROP TABLE IF EXISTS t1; -DROP TABLE IF EXISTS t2; - -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; - -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `c1` int(11) DEFAULT '12' COMMENT 'column1', - `c2` int(11) DEFAULT NULL COMMENT 'column2', - `c3` int(11) NOT NULL COMMENT 'column3', - `c4` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT 'a', - `c5` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT 'b', - `c6` varchar(255) COLLATE latin1_bin DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin - -CREATE TABLE t2 AS SELECT * FROM t1; - -SHOW CREATE TABLE t2; -Table Create Table -t2 CREATE TABLE `t2` ( - `c1` int(11) DEFAULT '12' COMMENT 'column1', - `c2` int(11) DEFAULT NULL COMMENT 'column2', - `c3` int(11) NOT NULL COMMENT 'column3', - `c4` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT 'a', - `c5` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT 'b', - `c6` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 - -DROP TABLE t2; -DROP TABLE t1; - -# -- End of test case for Bug#21380. - -# -- # -- Bug#18834: ALTER TABLE ADD INDEX on table with two timestamp fields # -- @@ -1907,14 +1886,34 @@ drop table `me:i`; # -- drop table if exists t1,t2,t3; +# Fix modified for MariaDB: we support this syntax create table t1 (a int) transactional=0; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'transactional=0' at line 1 +Warnings: +Warning 1478 Table storage engine 'MyISAM' does not support the create option 'TRANSACTIONAL=1' create table t2 (a int) page_checksum=1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'page_checksum=1' at line 1 create table t3 (a int) row_format=page; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'page' at line 1 +drop table t1,t2,t3; # -- End of Bug#45829 +# new table creation/renaming blocked if old encoded table present +create table `t-1` (a int) engine=myisam; +insert into `t-1` values (1); +show tables; +Tables_in_test +t-1 +flush tables; +convert table files in mysql 5.0 file name encoding +show tables; +Tables_in_test +#mysql50#t-1 +create table `t-1` (a int); +ERROR 42S01: Table '#mysql50#t-1' already exists +create table t1 (a int); +alter table t1 rename `t-1`; +ERROR 42S01: Table '#mysql50#t-1' already exists +rename table t1 to `t-1`; +ERROR 42S01: Table '#mysql50#t-1' already exists +drop table `#mysql50#t-1`, t1; End of 5.1 tests @@ -1944,7 +1943,7 @@ CREATE TRIGGER f BEFORE INSERT ON t1 FOR EACH ROW BEGIN UPDATE A SET `pk`=1 WHERE `pk`=0 ; END ;| -ERROR 42000: This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table' +ERROR 42000: This version of MariaDB doesn't yet support 'multiple triggers with the same action time and event for one table' DROP TABLE t1; DROP TABLE B; # @@ -2063,7 +2062,7 @@ 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; +CREATE TABLE IF NOT EXISTS v1(a int, b int) SELECT id, id as di FROM t1; Warnings: Note 1050 Table 'v1' already exists SHOW CREATE TABLE v1; @@ -2363,8 +2362,8 @@ 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 +Warning 1265 Data truncated for column 'b' at row 1 +Warning 1265 Data truncated for column 'c' at row 1 select * from t1; a b c NULL 0000-00-00 0000-00-00 @@ -2382,8 +2381,8 @@ 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 +Warning 1265 Data truncated for column 'b' at row 1 +Warning 1265 Data truncated for column 'c' at row 1 select * from t1; a b c NULL 0000-00-00 0000-00-00 @@ -2397,8 +2396,84 @@ a b 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; +Warnings: +Warning 1062 Duplicate entry '1' for key 'a' select * from t1; a b 1 1 drop table t1; # +# Checking that CREATE IF NOT EXISTS is not blocked by running SELECT +# +create table t1 (a int, b int) engine=myisam; +create table t2 (a int, b int) engine=myisam; +insert into t1 values (1,1); +lock tables t1 read; +set @@lock_wait_timeout=5; +create table if not exists t1 (a int, b int); +Warnings: +Note 1050 Table 't1' already exists +create table if not exists t1 (a int, b int) select 2,2; +Warnings: +Note 1050 Table 't1' already exists +create table if not exists t1 like t2; +Warnings: +Note 1050 Table 't1' already exists +create table t1 (a int, b int); +ERROR 42S01: Table 't1' already exists +create table t1 (a int, b int) select 2,2; +ERROR 42S01: Table 't1' already exists +create table t1 like t2; +ERROR 42S01: Table 't1' already exists +select * from t1; +a b +1 1 +unlock tables; +drop table t1,t2; +# +# MDEV-6179: dynamic columns functions/cast()/convert() doesn't +# play nice with CREATE/ALTER TABLE +# +create table t1 ( +color char(32) as (COLUMN_GET(dynamic_cols, 1 as char)) persistent, +cl char(32) as (COLUMN_GET(COLUMN_ADD(COLUMN_CREATE(1 , 'blue' as char), 2, 'ttt'), i as char)) persistent, +item_name varchar(32) primary key, -- A common attribute for all items +i int, +dynamic_cols blob -- Dynamic columns will be stored here +); +INSERT INTO t1(item_name, dynamic_cols, i) VALUES +('MariaDB T-shirt', COLUMN_CREATE(1, 'blue', 2, 'XL'), 1); +INSERT INTO t1(item_name, dynamic_cols, i) VALUES +('Thinkpad Laptop', COLUMN_CREATE(1, 'black', 3, 500), 2); +select item_name, color, cl from t1; +item_name color cl +MariaDB T-shirt blue blue +Thinkpad Laptop black ttt +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `color` char(32) AS (COLUMN_GET(dynamic_cols, 1 as char)) PERSISTENT, + `cl` char(32) AS (COLUMN_GET(COLUMN_ADD(COLUMN_CREATE(1 , 'blue' as char), 2, 'ttt'), i as char)) PERSISTENT, + `item_name` varchar(32) NOT NULL, + `i` int(11) DEFAULT NULL, + `dynamic_cols` blob, + PRIMARY KEY (`item_name`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +create table t1 ( +n int, +c char(32) as (convert(cast(n as char), char)) persistent +); +insert into t1(n) values (1),(2),(3); +select * from t1; +n c +1 1 +2 2 +3 3 +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `n` int(11) DEFAULT NULL, + `c` char(32) AS (convert(cast(n as char), char)) PERSISTENT +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; |