diff options
Diffstat (limited to 'mysql-test/main/key.result')
| -rw-r--r-- | mysql-test/main/key.result | 691 |
1 files changed, 691 insertions, 0 deletions
diff --git a/mysql-test/main/key.result b/mysql-test/main/key.result new file mode 100644 index 00000000..2e2c8d89 --- /dev/null +++ b/mysql-test/main/key.result @@ -0,0 +1,691 @@ +SET SQL_WARNINGS=1; +CREATE TABLE t1 ( +ID CHAR(32) NOT NULL, +name CHAR(32) NOT NULL, +value CHAR(255), +INDEX indexIDname (ID(8),name(8)) +) ; +INSERT INTO t1 VALUES +('keyword','indexdir','/export/home/local/www/database/indexes/keyword'); +INSERT INTO t1 VALUES ('keyword','urlprefix','text/ /text'); +INSERT INTO t1 VALUES ('keyword','urlmap','/text/ /'); +INSERT INTO t1 VALUES ('keyword','attr','personal employee company'); +INSERT INTO t1 VALUES +('emailgids','indexdir','/export/home/local/www/database/indexes/emailgids'); +INSERT INTO t1 VALUES ('emailgids','urlprefix','text/ /text'); +INSERT INTO t1 VALUES ('emailgids','urlmap','/text/ /'); +INSERT INTO t1 VALUES ('emailgids','attr','personal employee company'); +SELECT value FROM t1 WHERE ID='emailgids' AND name='attr'; +value +personal employee company +drop table t1; +CREATE TABLE t1 ( +price int(5) DEFAULT '0' NOT NULL, +area varchar(40) DEFAULT '' NOT NULL, +type varchar(40) DEFAULT '' NOT NULL, +transityes enum('Y','N') DEFAULT 'Y' NOT NULL, +shopsyes enum('Y','N') DEFAULT 'Y' NOT NULL, +schoolsyes enum('Y','N') DEFAULT 'Y' NOT NULL, +petsyes enum('Y','N') DEFAULT 'Y' NOT NULL, +KEY price (price,area,type,transityes,shopsyes,schoolsyes,petsyes) +); +INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','N','N','N','N'); +INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','N','N','N','N'); +INSERT IGNORE INTO t1 VALUES (900,'Vancouver','Shared/Roomate','','','',''); +Warnings: +Warning 1265 Data truncated for column 'transityes' at row 1 +Warning 1265 Data truncated for column 'shopsyes' at row 1 +Warning 1265 Data truncated for column 'schoolsyes' at row 1 +Warning 1265 Data truncated for column 'petsyes' at row 1 +INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','Y','Y','Y','Y'); +INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','Y','Y','Y','Y'); +INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','Y','Y','Y','Y'); +INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','Y','Y','Y','Y'); +INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','Y','Y','Y','Y'); +SELECT * FROM t1 WHERE area='Vancouver' and transityes='y' and schoolsyes='y' and ( ((type='1 Bedroom' or type='Studio/Bach') and (price<=500)) or ((type='2 Bedroom') and (price<=550)) or ((type='Shared/Roomate') and (price<=300)) or ((type='Room and Board') and (price<=500)) ) and price <= 400; +price area type transityes shopsyes schoolsyes petsyes +drop table t1; +CREATE TABLE t1 (program enum('signup','unique','sliding') not null, type enum('basic','sliding','signup'), sites set('mt'), PRIMARY KEY (program)); +ALTER TABLE t1 modify program enum('signup','unique','sliding'); +drop table t1; +CREATE TABLE t1 ( +name varchar(50) DEFAULT '' NOT NULL, +author varchar(50) DEFAULT '' NOT NULL, +category decimal(10,0) DEFAULT '0' NOT NULL, +email varchar(50), +password varchar(50), +proxy varchar(50), +bitmap varchar(20), +msg varchar(255), +urlscol varchar(127), +urlhttp varchar(127), +timeout decimal(10,0), +nbcnx decimal(10,0), +creation decimal(10,0), +livinguntil decimal(10,0), +lang decimal(10,0), +type decimal(10,0), +subcat decimal(10,0), +subtype decimal(10,0), +reg char(1), +scs varchar(255), +capacity decimal(10,0), +userISP varchar(50), +CCident varchar(50) DEFAULT '' NOT NULL, +PRIMARY KEY (name,author,category) +); +INSERT INTO t1 VALUES +('patnom','patauteur',0,'p.favre@cryo-networks.fr',NULL,NULL,'#p2sndnq6ae5g1u6t','essai salut','scol://195.242.78.119:patauteur.patnom',NULL,NULL,NULL,950036174,-882087474,NULL,3,0,3,'1','Pub/patnom/futur_divers.scs',NULL,'pat','CC1'); +INSERT INTO t1 VALUES +('LeNomDeMonSite','Marc',0,'m.barilley@cryo-networks.fr',NULL,NULL,NULL,NULL,'scol://195.242.78.119:Marc.LeNomDeMonSite',NULL,NULL,NULL,950560434,-881563214,NULL,3,0,3,'1','Pub/LeNomDeMonSite/domus_hibere.scs',NULL,'Marq','CC1'); +select * from t1 where name='patnom' and author='patauteur' and category=0; +name author category email password proxy bitmap msg urlscol urlhttp timeout nbcnx creation livinguntil lang type subcat subtype reg scs capacity userISP CCident +patnom patauteur 0 p.favre@cryo-networks.fr NULL NULL #p2sndnq6ae5g1u6t essai salut scol://195.242.78.119:patauteur.patnom NULL NULL NULL 950036174 -882087474 NULL 3 0 3 1 Pub/patnom/futur_divers.scs NULL pat CC1 +drop table t1; +create table t1 +( +name_id int not null auto_increment, +name blob, +INDEX name_idx (name(5)), +primary key (name_id) +); +INSERT t1 VALUES(NULL,'/'); +INSERT t1 VALUES(NULL,'[T,U]_axpby'); +SELECT * FROM t1 WHERE name='[T,U]_axpy'; +name_id name +SELECT * FROM t1 WHERE name='[T,U]_axpby'; +name_id name +2 [T,U]_axpby +create table t2 +( +name_id int not null auto_increment, +name char(255) binary, +INDEX name_idx (name(5)), +primary key (name_id) +); +INSERT t2 select * from t1; +SELECT * FROM t2 WHERE name='[T,U]_axpy'; +name_id name +SELECT * FROM t2 WHERE name='[T,U]_axpby'; +name_id name +2 [T,U]_axpby +CREATE TABLE t3 SELECT * FROM t2 WHERE name='[T,U]_axpby'; +SELECT * FROM t2 WHERE name='[T,U]_axpby'; +name_id name +2 [T,U]_axpby +drop table t1,t2,t3; +create table t1 +( +SEQNO numeric(12 ) not null, +MOTYPEID numeric(12 ) not null, +MOINSTANCEID numeric(12 ) not null, +ATTRID numeric(12 ) not null, +VALUE varchar(120) not null, +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 'PRIMARY' +drop table t1; +CREATE TABLE t1 ( +a tinytext NOT NULL, +b tinyint(3) unsigned NOT NULL default '0', +PRIMARY KEY (a(32),b) +) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('a',1),('a',2); +SELECT * FROM t1 WHERE a='a' AND b=2; +a b +a 2 +SELECT * FROM t1 WHERE a='a' AND b in (2); +a b +a 2 +SELECT * FROM t1 WHERE a='a' AND b in (1,2); +a b +a 1 +a 2 +drop table t1; +create table t1 (a int not null unique, b int unique, c int, d int not null primary key, key(c), e int not null unique); +show keys from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 PRIMARY 1 d A 0 NULL NULL BTREE NO +t1 0 a 1 a A 0 NULL NULL BTREE NO +t1 0 e 1 e A 0 NULL NULL BTREE NO +t1 0 b 1 b A NULL NULL NULL YES BTREE NO +t1 1 c 1 c A NULL NULL NULL YES BTREE NO +drop table t1; +CREATE TABLE t1 (c CHAR(10) NOT NULL,i INT NOT NULL AUTO_INCREMENT, +UNIQUE (c,i)); +INSERT IGNORE INTO t1 (c) VALUES (NULL),(NULL); +Warnings: +Warning 1048 Column 'c' cannot be null +Warning 1048 Column 'c' cannot be null +SELECT * FROM t1; +c i + 1 + 2 +INSERT INTO t1 (c) VALUES ('a'),('a'); +SELECT * FROM t1; +c i + 1 + 2 +a 1 +a 2 +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (c CHAR(10) NULL, i INT NOT NULL AUTO_INCREMENT, +UNIQUE (c,i)); +INSERT INTO t1 (c) VALUES (NULL),(NULL); +SELECT * FROM t1; +c i +NULL 1 +NULL 2 +INSERT INTO t1 (c) VALUES ('a'),('a'); +SELECT * FROM t1; +c i +NULL 1 +NULL 2 +a 1 +a 2 +drop table t1; +create table t1 (i int, a char(200), b text, unique (a), unique (b(300))) charset utf8; +insert ignore t1 values (1, repeat('a',210), repeat('b', 310)); +Warnings: +Warning 1265 Data truncated for column 'a' at row 1 +insert ignore t1 values (2, repeat(0xD0B1,215), repeat(0xD0B1, 310)); +Warnings: +Warning 1265 Data truncated for column 'a' at row 1 +select i, length(a), length(b), char_length(a), char_length(b) from t1; +i length(a) length(b) char_length(a) char_length(b) +1 200 310 200 310 +2 400 620 200 310 +select i from t1 where a=repeat(_utf8 'a',200); +i +1 +select i from t1 where a=repeat(_utf8 0xD0B1,200); +i +2 +select i from t1 where b=repeat(_utf8 'b',310); +i +1 +drop table t1; +CREATE TABLE t1 (id int unsigned auto_increment, name char(50), primary key (id)) engine=myisam; +insert into t1 (name) values ('a'), ('b'),('c'),('d'),('e'),('f'),('g'); +explain select 1 from t1 where id =2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index +explain select 1 from t1 where id =2 or id=3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using where; Using index +explain select name from t1 where id =2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 +ALTER TABLE t1 DROP PRIMARY KEY, ADD INDEX (id); +explain select 1 from t1 where id =2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref id id 4 const 1 Using index +drop table t1; +CREATE TABLE t1 (numeropost mediumint(8) unsigned NOT NULL default '0', numreponse int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (numeropost,numreponse), UNIQUE KEY numreponse (numreponse)); +INSERT INTO t1 (numeropost,numreponse) VALUES ('1','1'),('1','2'),('2','3'),('2','4'); +SELECT numeropost FROM t1 WHERE numreponse='1'; +numeropost +1 +EXPLAIN SELECT numeropost FROM t1 WHERE numreponse='1'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const numreponse numreponse 4 const 1 Using index +FLUSH TABLES; +SELECT numeropost FROM t1 WHERE numreponse='1'; +numeropost +1 +drop table t1; +create table t1 (c varchar(30) character set utf8, t text character set utf8, unique (c(2)), unique (t(3))) engine=myisam; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c` varchar(30) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, + `t` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, + UNIQUE KEY `c` (`c`(2)), + UNIQUE KEY `t` (`t`(3)) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +insert t1 values ('cccc', 'tttt'), +(0xD0B1212223D0B1D0B1D0B1D0B1D0B1, 0xD0B1D0B1212223D0B1D0B1D0B1D0B1), +(0xD0B1222123D0B1D0B1D0B1D0B1D0B1, 0xD0B1D0B1222123D0B1D0B1D0B1D0B1); +insert t1 (c) values ('cc22'); +ERROR 23000: Duplicate entry 'cc' for key 'c' +insert t1 (t) values ('ttt22'); +ERROR 23000: Duplicate entry 'ttt' for key 't' +insert t1 (c) values (0xD0B1212322D0B1D0B1D0B1D0B1D0B1); +ERROR 23000: Duplicate entry '\0431!' for key 'c' +insert t1 (t) values (0xD0B1D0B1212322D0B1D0B1D0B1D0B1); +ERROR 23000: Duplicate entry '\0431\0431!' for key 't' +select c from t1 where c='cccc'; +c +cccc +select t from t1 where t='tttt'; +t +tttt +select c from t1 where c=0xD0B1212223D0B1D0B1D0B1D0B1D0B1; +c +?!"#????? +select t from t1 where t=0xD0B1D0B1212223D0B1D0B1D0B1D0B1; +t +??!"#???? +drop table t1; +DROP TABLE IF EXISTS t1; +Warnings: +Note 1051 Unknown table 'test.t1' +CREATE TABLE t1 ( +c1 int, +c2 varbinary(240), +UNIQUE KEY (c1), +KEY (c2) +) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,'\Z\Z\Z\Z'); +INSERT INTO t1 VALUES (2,'\Z\Z\Z\Z\Z\Z'); +INSERT INTO t1 VALUES (3,'\Z\Z\Z\Z'); +select c1 from t1 where c2='\Z\Z\Z\Z'; +c1 +1 +3 +DELETE FROM t1 WHERE (c1 = 1); +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +select c1 from t1 where c2='\Z\Z\Z\Z'; +c1 +3 +DELETE FROM t1 WHERE (c1 = 3); +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +select c1 from t1 where c2='\Z\Z\Z\Z'; +c1 +truncate table t1; +insert into t1 values(1,"aaaa"),(2,"aaab"),(3,"aaac"),(4,"aaccc"); +delete from t1 where c1=3; +delete from t1 where c1=1; +delete from t1 where c1=4; +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +drop table t1; +create table t1 (c char(10), index (c(0))); +ERROR HY000: Key part 'c' length cannot be 0 +create table t1 (c char(10), index (c,c)); +ERROR 42S21: Duplicate column name 'c' +create table t1 (c1 char(10), c2 char(10), index (c1,c2,c1)); +ERROR 42S21: Duplicate column name 'c1' +create table t1 (c1 char(10), c2 char(10), index (c1,c1,c2)); +ERROR 42S21: Duplicate column name 'c1' +create table t1 (c1 char(10), c2 char(10), index (c2,c1,c1)); +ERROR 42S21: Duplicate column name 'c1' +create table t1 (c1 char(10), c2 char(10)); +alter table t1 add key (c1,c1); +ERROR 42S21: Duplicate column name 'c1' +alter table t1 add key (c2,c1,c1); +ERROR 42S21: Duplicate column name 'c1' +alter table t1 add key (c1,c2,c1); +ERROR 42S21: Duplicate column name 'c1' +alter table t1 add key (c1,c1,c2); +ERROR 42S21: Duplicate column name 'c1' +drop table t1; +create table t1 ( +i1 INT NOT NULL, +i2 INT NOT NULL, +UNIQUE i1idx (i1), +UNIQUE i2idx (i2)); +desc t1; +Field Type Null Key Default Extra +i1 int(11) NO PRI NULL +i2 int(11) NO UNI NULL +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `i1` int(11) NOT NULL, + `i2` int(11) NOT NULL, + UNIQUE KEY `i1idx` (`i1`), + UNIQUE KEY `i2idx` (`i2`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +drop table t1; +create table t1 ( +c1 int, +c2 varchar(20) not null, +primary key (c1), +key (c2(10)) +) engine=myisam; +insert into t1 values (1,''); +insert into t1 values (2,' \t\tTest String'); +insert into t1 values (3,' \n\tTest String'); +update t1 set c2 = 'New Test String' where c1 = 1; +select * from t1; +c1 c2 +1 New Test String +2 Test String +3 + Test String +drop table t1; +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, + KEY `a` (`a`,`b`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +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, + KEY `a` (`a`,`b`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +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, + KEY `a` (`a`,`b`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +drop table t1; +create table t1 (a int not null primary key, b varchar(20) not null unique); +desc t1; +Field Type Null Key Default Extra +a int(11) NO PRI NULL +b varchar(20) NO UNI NULL +drop table t1; +create table t1 (a int not null primary key, b int not null unique); +desc t1; +Field Type Null Key Default Extra +a int(11) NO PRI NULL +b int(11) NO UNI NULL +drop table t1; +create table t1 (a int not null primary key, b varchar(20) not null, unique (b(10))); +desc t1; +Field Type Null Key Default Extra +a int(11) NO PRI NULL +b varchar(20) NO UNI NULL +drop table t1; +create table t1 (a int not null primary key, b varchar(20) not null, c varchar(20) not null, unique(b(10),c(10))); +desc t1; +Field Type Null Key Default Extra +a int(11) NO PRI NULL +b varchar(20) NO MUL NULL +c varchar(20) NO NULL +drop table t1; +create table t1 ( +c1 int, +c2 char(12), +c3 varchar(123), +c4 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_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)); +Warnings: +Note 1831 Duplicate index `i1`. This is deprecated and will be disallowed in a future release +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` int(11) DEFAULT NULL, + `c2` char(12) NOT NULL, + `c3` varchar(123) NOT NULL, + `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 COLLATE=latin1_swedish_ci +alter table t1 drop index c1; +alter table t1 add index (c1); +Warnings: +Note 1831 Duplicate index `c1`. This is deprecated and will be disallowed in a future release +alter table t1 add index (c1); +Warnings: +Note 1831 Duplicate index `c1_2`. This is deprecated and will be disallowed in a future release +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); +Warnings: +Note 1831 Duplicate index `i6`. This is deprecated and will be disallowed in a future release +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 INDEX `PRIMARY`; check that it exists +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` int(11) NOT NULL, + `c2` char(12) NOT NULL, + `c3` varchar(123) NOT NULL, + `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 COLLATE=latin1_swedish_ci +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; +create table t1(a int not null, key aa(a), +b char(10) not null, unique key bb(b(1)), +c char(4) not null, unique key cc(c)); +desc t1; +Field Type Null Key Default Extra +a int(11) NO MUL NULL +b char(10) NO UNI NULL +c char(4) NO PRI NULL +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL, + `b` char(10) NOT NULL, + `c` char(4) NOT NULL, + UNIQUE KEY `cc` (`c`), + UNIQUE KEY `bb` (`b`(1)), + KEY `aa` (`a`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +drop table t1; +create table t1(a int not null, key aa(a), +b char(10) not null, unique key bb(b(1)), +c char(4) not null); +desc t1; +Field Type Null Key Default Extra +a int(11) NO MUL NULL +b char(10) NO UNI NULL +c char(4) NO NULL +alter table t1 add unique key cc(c); +desc t1; +Field Type Null Key Default Extra +a int(11) NO MUL NULL +b char(10) NO UNI NULL +c char(4) NO PRI NULL +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL, + `b` char(10) NOT NULL, + `c` char(4) NOT NULL, + UNIQUE KEY `cc` (`c`), + UNIQUE KEY `bb` (`b`(1)), + KEY `aa` (`a`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +drop table t1; +End of 5.0 tests +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (a INT PRIMARY KEY AUTO_INCREMENT); +INSERT INTO t1 VALUES (), (), (); +SELECT 1 AS c1 +FROM t1 +ORDER BY ( +SELECT 1 AS c2 +FROM t1 +GROUP BY GREATEST(LAST_INSERT_ID(), t1.a) ASC +LIMIT 1); +c1 +1 +1 +1 +DROP TABLE t1; +CREATE TABLE t1 (a INT, b INT, INDEX (a,b)); +INSERT INTO t1 (a, b) +VALUES +(1,1), (1,2), (1,3), (1,4), (1,5), +(2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6); +ANALYZE table t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE +(SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY t1 range a a 5 NULL 5 Using where; Using index +SELECT 1 as RES FROM t1 AS t1_outer WHERE +(SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12; +RES +DROP TABLE t1; +# +# Bug#18144: Cost with FORCE/USE index seems incorrect in some cases. +# +# We are interested in showing that the cost for the last plan is higher +# than for the preceding two plans. +# +CREATE TABLE t1( a INT, b INT, KEY( a ) ); +INSERT INTO t1 values (1, 2), (1, 3), (2, 3), (2, 4), (3, 4), (3, 5); +EXPLAIN SELECT a, SUM( b ) FROM t1 GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort +SHOW STATUS LIKE 'Last_query_cost'; +Variable_name Value +Last_query_cost 9.212184 +EXPLAIN SELECT a, SUM( b ) FROM t1 USE INDEX( a ) GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort +SHOW STATUS LIKE 'Last_query_cost'; +Variable_name Value +Last_query_cost 9.212184 +EXPLAIN SELECT a, SUM( b ) FROM t1 FORCE INDEX( a ) GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL a 5 NULL 6 +SHOW STATUS LIKE 'Last_query_cost'; +Variable_name Value +Last_query_cost 14.199000 +DROP TABLE t1; +# +# MDEV-21480: Unique key using ref access though eq_ref access can be used +# +create table t1(a int, b int,c int, primary key(a), unique key(b,c)); +insert into t1 select seq, seq, seq from seq_1_to_10; +create table t2(a int, b int,c int); +insert into t2 select seq, seq, seq+1 from seq_1_to_100; +EXPLAIN SELECT t1.c, t2.c FROM t1, t2 WHERE t1.b=t2.a and t1.c=t2.b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 100 Using where +1 SIMPLE t1 eq_ref b b 10 test.t2.a,test.t2.b 1 Using index +SELECT t1.c, t2.c FROM t1, t2 WHERE t1.b=t2.a and t1.c=t2.b; +c c +1 2 +2 3 +3 4 +4 5 +5 6 +6 7 +7 8 +8 9 +9 10 +10 11 +alter table t1 drop PRIMARY KEY; +alter table t1 add PRIMARY KEY(b,c); +EXPLAIN SELECT t1.c, t2.c FROM t1, t2 WHERE t1.b=t2.a and t1.c=t2.b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 100 Using where +1 SIMPLE t1 eq_ref PRIMARY,b PRIMARY 8 test.t2.a,test.t2.b 1 Using index +SELECT t1.c, t2.c FROM t1, t2 WHERE t1.b=t2.a and t1.c=t2.b; +c c +1 2 +2 3 +3 4 +4 5 +5 6 +6 7 +7 8 +8 9 +9 10 +10 11 +drop table t1,t2; +# +# MDEV-13756 Implement descending index: KEY (a DESC, b ASC) +# +create table t1 (a int, b int, key(a), key(a desc)); +drop table t1; |