diff options
Diffstat (limited to 'mysql-test/r/key.result')
| -rw-r--r-- | mysql-test/r/key.result | 146 |
1 files changed, 131 insertions, 15 deletions
diff --git a/mysql-test/r/key.result b/mysql-test/r/key.result index eea884e4294..e348a387252 100644 --- a/mysql-test/r/key.result +++ b/mysql-test/r/key.result @@ -127,7 +127,7 @@ primary key (SEQNO, MOTYPEID, MOINSTANCEID, ATTRID, VALUE ) INSERT INTO t1 VALUES (1, 1, 1, 1, 'a'); INSERT INTO t1 VALUES (1, 1, 1, 1, 'b'); INSERT INTO t1 VALUES (1, 1, 1, 1, 'a'); -ERROR 23000: Duplicate entry '1-1-1-1-a' for key 1 +ERROR 23000: Duplicate entry '1-1-1-1-a' for key 'PRIMARY' drop table t1; CREATE TABLE t1 ( a tinytext NOT NULL, @@ -159,8 +159,8 @@ CREATE TABLE t1 (c CHAR(10) NOT NULL,i INT NOT NULL AUTO_INCREMENT, UNIQUE (c,i)); INSERT INTO t1 (c) VALUES (NULL),(NULL); Warnings: -Warning 1263 Column was set to data type implicit default; NULL supplied for NOT NULL column 'c' at row 1 -Warning 1263 Column was set to data type implicit default; NULL supplied for NOT NULL column 'c' at row 2 +Warning 1048 Column 'c' cannot be null +Warning 1048 Column 'c' cannot be null SELECT * FROM t1; c i 1 @@ -242,8 +242,8 @@ create table t1 (c varchar(30) character set utf8, t text character set utf8, un show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c` varchar(30) character set utf8 default NULL, - `t` text character set utf8, + `c` varchar(30) CHARACTER SET utf8 DEFAULT NULL, + `t` text CHARACTER SET utf8, UNIQUE KEY `c` (`c`(2)), UNIQUE KEY `t` (`t`(3)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 @@ -251,13 +251,13 @@ insert t1 values ('cccc', 'tttt'), (0xD0B1212223D0B1D0B1D0B1D0B1D0B1, 0xD0B1D0B1212223D0B1D0B1D0B1D0B1), (0xD0B1222123D0B1D0B1D0B1D0B1D0B1, 0xD0B1D0B1222123D0B1D0B1D0B1D0B1); insert t1 (c) values ('cc22'); -ERROR 23000: Duplicate entry 'cc22' for key 1 +ERROR 23000: Duplicate entry 'cc22' for key 'c' insert t1 (t) values ('ttt22'); -ERROR 23000: Duplicate entry 'ttt22' for key 2 +ERROR 23000: Duplicate entry 'ttt22' for key 't' insert t1 (c) values (0xD0B1212322D0B1D0B1D0B1D0B1D0B1); -ERROR 23000: Duplicate entry 'б!#"Ð' for key 1 +ERROR 23000: Duplicate entry 'б!#"Ð' for key 'c' insert t1 (t) values (0xD0B1D0B1212322D0B1D0B1D0B1D0B1); -ERROR 23000: Duplicate entry 'бб!#"б' for key 2 +ERROR 23000: Duplicate entry 'бб!#"б' for key 't' select c from t1 where c='cccc'; c cccc @@ -368,24 +368,24 @@ create table t1 (a varchar(10), b varchar(10), key(a(10),b(10))); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` varchar(10) default NULL, - `b` varchar(10) default NULL, + `a` varchar(10) DEFAULT NULL, + `b` varchar(10) DEFAULT NULL, KEY `a` (`a`,`b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 alter table t1 modify b varchar(20); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` varchar(10) default NULL, - `b` varchar(20) default NULL, + `a` varchar(10) DEFAULT NULL, + `b` varchar(20) DEFAULT NULL, KEY `a` (`a`,`b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 alter table t1 modify a varchar(20); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` varchar(20) default NULL, - `b` varchar(20) default NULL, + `a` varchar(20) DEFAULT NULL, + `b` varchar(20) DEFAULT NULL, KEY `a` (`a`,`b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; @@ -414,3 +414,119 @@ a int(11) NO PRI b varchar(20) NO MUL c varchar(20) NO drop table t1; +create table t1 ( +c1 int, +c2 char(12), +c3 varchar(123), +c4 timestamp, +index (c1), +index i1 (c1), +index i2 (c2), +index i3 (c3), +unique i4 (c4), +index i5 (c1, c2, c3, c4), +primary key (c2, c3), +index (c2, c4)); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` int(11) DEFAULT NULL, + `c2` char(12) NOT NULL DEFAULT '', + `c3` varchar(123) NOT NULL DEFAULT '', + `c4` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + PRIMARY KEY (`c2`,`c3`), + UNIQUE KEY `i4` (`c4`), + KEY `c1` (`c1`), + KEY `i1` (`c1`), + KEY `i2` (`c2`), + KEY `i3` (`c3`), + KEY `i5` (`c1`,`c2`,`c3`,`c4`), + KEY `c2` (`c2`,`c4`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +alter table t1 drop index c1; +alter table t1 add index (c1); +alter table t1 add index (c1); +alter table t1 drop index i3; +alter table t1 add index i3 (c3); +alter table t1 drop index i2, drop index i4; +alter table t1 add index i2 (c2), add index i4 (c4); +alter table t1 drop index i2, drop index i4, add index i6 (c2, c4); +alter table t1 add index i2 (c2), add index i4 (c4), drop index i6; +alter table t1 drop index i2, drop index i4, add unique i4 (c4); +alter table t1 add index i2 (c2), drop index i4, add index i4 (c4); +alter table t1 drop index c2, add index (c2(4),c3(7)); +alter table t1 drop index c2, add index (c2(4),c3(7)); +alter table t1 add primary key (c1, c2), drop primary key; +alter table t1 drop primary key; +alter table t1 add primary key (c1, c2), drop primary key; +ERROR 42000: Can't DROP 'PRIMARY'; check that column/key exists +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` int(11) NOT NULL DEFAULT '0', + `c2` char(12) NOT NULL DEFAULT '', + `c3` varchar(123) NOT NULL DEFAULT '', + `c4` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + KEY `i1` (`c1`), + KEY `i5` (`c1`,`c2`,`c3`,`c4`), + KEY `c1` (`c1`), + KEY `c1_2` (`c1`), + KEY `i3` (`c3`), + KEY `i2` (`c2`), + KEY `i4` (`c4`), + KEY `c2` (`c2`(4),`c3`(7)) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +insert into t1 values(1, 'a', 'a', NULL); +insert into t1 values(1, 'b', 'b', NULL); +alter table t1 drop index i3, drop index i2, drop index i1; +alter table t1 add index i3 (c3), add index i2 (c2), add unique index i1 (c1); +ERROR 23000: Duplicate entry '1' for key 'i1' +drop table t1; +CREATE TABLE t1( a TINYINT, KEY(a) ) ENGINE=MyISAM; +INSERT INTO t1 VALUES( 1 ); +ALTER TABLE t1 DISABLE KEYS; +EXPLAIN SELECT MAX(a) FROM t1 FORCE INDEX(a); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 +drop table t1; +CREATE TABLE t1 ( +a INTEGER auto_increment PRIMARY KEY, +b INTEGER NOT NULL, +c INTEGER NOT NULL, +d CHAR(64) +); +CREATE TABLE t2 ( +a INTEGER auto_increment PRIMARY KEY, +b INTEGER NOT NULL, +c SMALLINT NOT NULL, +d DATETIME NOT NULL, +e SMALLINT NOT NULL, +f INTEGER NOT NULL, +g INTEGER NOT NULL, +h SMALLINT NOT NULL, +i INTEGER NOT NULL, +j INTEGER NOT NULL, +UNIQUE INDEX (b), +INDEX (b, d, e, f, g, h, i, j, c), +INDEX (c) +); +INSERT INTO t2 VALUES +(NULL, 1, 254, '1000-01-01 00:00:00', 257, 0, 0, 0, 0, 0), +(NULL, 2, 1, '2004-11-30 12:00:00', 1, 0, 0, 0, 0, 0), +(NULL, 3, 1, '2004-11-30 12:00:00', 1, 0, 0, 2, -21600, 0), +(NULL, 4, 1, '2004-11-30 12:00:00', 1, 0, 0, 2, -10800, 0), +(NULL, 5, 1, '2004-11-30 12:00:00', 1, 0, 0, 5, -10800, 0), +(NULL, 6, 1, '2004-11-30 12:00:00', 102, 0, 0, 0, 0, 0), +(NULL, 7, 1, '2004-11-30 12:00:00', 105, 2, 0, 0, 0, 0), +(NULL, 8, 1, '2004-11-30 12:00:00', 105, 10, 0, 0, 0, 0); +INSERT INTO t1 (b, c, d) VALUES +(3388000, -553000, NULL), +(3388000, -553000, NULL); +SELECT * +FROM t2 c JOIN t1 pa ON c.b = pa.a +WHERE c.c = 1 +ORDER BY c.b, c.d +; +a b c d e f g h i j a b c d +2 2 1 2004-11-30 12:00:00 1 0 0 0 0 0 2 3388000 -553000 NULL +DROP TABLE t1, t2; |