summaryrefslogtreecommitdiffstats
path: root/mysql-test/main/key.result
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 18:00:34 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 18:00:34 +0000
commit3f619478f796eddbba6e39502fe941b285dd97b1 (patch)
treee2c7b5777f728320e5b5542b6213fd3591ba51e2 /mysql-test/main/key.result
parentInitial commit. (diff)
downloadmariadb-3f619478f796eddbba6e39502fe941b285dd97b1.tar.xz
mariadb-3f619478f796eddbba6e39502fe941b285dd97b1.zip
Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'mysql-test/main/key.result')
-rw-r--r--mysql-test/main/key.result691
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;