1set global innodb_support_xa=default; 2set session innodb_support_xa=default; 3SET SESSION DEFAULT_STORAGE_ENGINE = InnoDB; 4SET SESSION DEFAULT_TMP_STORAGE_ENGINE = InnoDB; 5drop table if exists t1,t2,t3,t1m,t1i,t2m,t2i,t4; 6drop procedure if exists p1; 7create table t1 ( 8c_id int(11) not null default '0', 9org_id int(11) default null, 10unique key contacts$c_id (c_id), 11key contacts$org_id (org_id) 12); 13insert into t1 values 14(2,null),(120,null),(141,null),(218,7), (128,1), 15(151,2),(234,2),(236,2),(243,2),(255,2),(259,2),(232,3),(235,3),(238,3), 16(246,3),(253,3),(269,3),(285,3),(291,3),(293,3),(131,4),(230,4),(231,4); 17create table t2 ( 18slai_id int(11) not null default '0', 19owner_tbl int(11) default null, 20owner_id int(11) default null, 21sla_id int(11) default null, 22inc_web int(11) default null, 23inc_email int(11) default null, 24inc_chat int(11) default null, 25inc_csr int(11) default null, 26inc_total int(11) default null, 27time_billed int(11) default null, 28activedate timestamp null default null, 29expiredate timestamp null default null, 30state int(11) default null, 31sla_set int(11) default null, 32unique key t2$slai_id (slai_id), 33key t2$owner_id (owner_id), 34key t2$sla_id (sla_id) 35); 36insert into t2(slai_id, owner_tbl, owner_id, sla_id) values 37(1,3,1,1), (3,3,10,2), (4,3,3,6), (5,3,2,5), (6,3,8,3), (7,3,9,7), 38(8,3,6,8), (9,3,4,9), (10,3,5,10), (11,3,11,11), (12,3,7,12); 39flush tables; 40select si.slai_id 41from t1 c join t2 si on 42((si.owner_tbl = 3 and si.owner_id = c.org_id) or 43( si.owner_tbl = 2 and si.owner_id = c.c_id)) 44where 45c.c_id = 218 and expiredate is null; 46slai_id 4712 48select * from t1 where org_id is null; 49c_id org_id 502 NULL 51120 NULL 52141 NULL 53select si.slai_id 54from t1 c join t2 si on 55((si.owner_tbl = 3 and si.owner_id = c.org_id) or 56( si.owner_tbl = 2 and si.owner_id = c.c_id)) 57where 58c.c_id = 218 and expiredate is null; 59slai_id 6012 61drop table t1, t2; 62CREATE TABLE t1 (a int, b int, KEY b (b)); 63CREATE TABLE t2 (a int, b int, PRIMARY KEY (a,b)); 64CREATE TABLE t3 (a int, b int, c int, PRIMARY KEY (a), 65UNIQUE KEY b (b,c), KEY a (a,b,c)); 66INSERT INTO t1 VALUES (1, 1); 67INSERT INTO t1 SELECT a + 1, b + 1 FROM t1; 68INSERT INTO t1 SELECT a + 2, b + 2 FROM t1; 69INSERT INTO t2 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8); 70INSERT INTO t2 SELECT a + 1, b FROM t2; 71DELETE FROM t2 WHERE a = 1 AND b < 2; 72INSERT INTO t3 VALUES (1,1,1),(2,1,2); 73INSERT INTO t3 SELECT a + 2, a + 2, 3 FROM t3; 74INSERT INTO t3 SELECT a + 4, a + 4, 3 FROM t3; 75SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE 76t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2) 77ORDER BY t1.b LIMIT 2; 78b a 791 1 802 2 81SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE 82t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2) 83ORDER BY t1.b LIMIT 5; 84b a 851 1 862 2 872 2 883 3 893 3 90DROP TABLE t1, t2, t3; 91CREATE TABLE `t1` (`id1` INT) ; 92INSERT INTO `t1` (`id1`) VALUES (1),(5),(2); 93CREATE TABLE `t2` ( 94`id1` INT, 95`id2` INT NOT NULL, 96`id3` INT, 97`id4` INT NOT NULL, 98UNIQUE (`id2`,`id4`), 99KEY (`id1`) 100); 101INSERT INTO `t2`(`id1`,`id2`,`id3`,`id4`) VALUES 102(1,1,1,0), 103(1,1,2,1), 104(5,1,2,2), 105(6,1,2,3), 106(1,2,2,2), 107(1,2,1,1); 108SELECT `id1` FROM `t1` WHERE `id1` NOT IN (SELECT `id1` FROM `t2` WHERE `id2` = 1 AND `id3` = 2); 109id1 1102 111DROP TABLE t1, t2; 112create table t1 (c1 int) engine=innodb; 113handler t1 open; 114handler t1 read first; 115c1 116Before and after comparison 1170 118drop table t1; 119CREATE TABLE t1(c1 TEXT, UNIQUE (c1(1)), cnt INT DEFAULT 1) 120ENGINE=INNODB CHARACTER SET UTF8; 121INSERT INTO t1 (c1) VALUES ('1a'); 122SELECT * FROM t1; 123c1 cnt 1241a 1 125INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1; 126SELECT * FROM t1; 127c1 cnt 1281a 2 129DROP TABLE t1; 130CREATE TABLE t1(c1 VARCHAR(2), UNIQUE (c1(1)), cnt INT DEFAULT 1) 131ENGINE=INNODB CHARACTER SET UTF8; 132INSERT INTO t1 (c1) VALUES ('1a'); 133SELECT * FROM t1; 134c1 cnt 1351a 1 136INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1; 137SELECT * FROM t1; 138c1 cnt 1391a 2 140DROP TABLE t1; 141CREATE TABLE t1(c1 CHAR(2), UNIQUE (c1(1)), cnt INT DEFAULT 1) 142ENGINE=INNODB CHARACTER SET UTF8; 143INSERT INTO t1 (c1) VALUES ('1a'); 144SELECT * FROM t1; 145c1 cnt 1461a 1 147INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1; 148SELECT * FROM t1; 149c1 cnt 1501a 2 151DROP TABLE t1; 152CREATE TABLE t1 ( 153a1 decimal(10,0) DEFAULT NULL, 154a2 blob, 155a3 time DEFAULT NULL, 156a4 blob, 157a5 char(175) DEFAULT NULL, 158a6 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', 159a7 tinyblob, 160INDEX idx (a6,a7(239),a5) 161) ENGINE=InnoDB; 162EXPLAIN SELECT a4 FROM t1 WHERE 163a6=NULL AND 164a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS'; 165id select_type table type possible_keys key key_len ref rows Extra 1661 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 167EXPLAIN SELECT t1.a4 FROM t1, t1 t WHERE 168t.a6=t.a6 AND t1.a6=NULL AND 169t1.a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS'; 170id select_type table type possible_keys key key_len ref rows Extra 1711 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 172DROP TABLE t1; 173create table t1m (a int) engine = MEMORY; 174create table t1i (a int); 175create table t2m (a int) engine = MEMORY; 176create table t2i (a int); 177insert into t2m values (5); 178insert into t2i values (5); 179select min(a) from t1i; 180min(a) 181NULL 182select min(7) from t1i; 183min(7) 184NULL 185select min(7) from DUAL; 186min(7) 1877 188explain select min(7) from t2i join t1i; 189id select_type table type possible_keys key key_len ref rows Extra 1901 SIMPLE t2i ALL NULL NULL NULL NULL 1 NULL 1911 SIMPLE t1i ALL NULL NULL NULL NULL 1 Using join buffer (Block Nested Loop) 192select min(7) from t2i join t1i; 193min(7) 194NULL 195select max(a) from t1i; 196max(a) 197NULL 198select max(7) from t1i; 199max(7) 200NULL 201select max(7) from DUAL; 202max(7) 2037 204explain select max(7) from t2i join t1i; 205id select_type table type possible_keys key key_len ref rows Extra 2061 SIMPLE t2i ALL NULL NULL NULL NULL 1 NULL 2071 SIMPLE t1i ALL NULL NULL NULL NULL 1 Using join buffer (Block Nested Loop) 208select max(7) from t2i join t1i; 209max(7) 210NULL 211select 1, min(a) from t1i where a=99; 2121 min(a) 2131 NULL 214select 1, min(a) from t1i where 1=99; 2151 min(a) 2161 NULL 217select 1, min(1) from t1i where a=99; 2181 min(1) 2191 NULL 220select 1, min(1) from t1i where 1=99; 2211 min(1) 2221 NULL 223select 1, max(a) from t1i where a=99; 2241 max(a) 2251 NULL 226select 1, max(a) from t1i where 1=99; 2271 max(a) 2281 NULL 229select 1, max(1) from t1i where a=99; 2301 max(1) 2311 NULL 232select 1, max(1) from t1i where 1=99; 2331 max(1) 2341 NULL 235explain select count(*), min(7), max(7) from t1m, t1i; 236id select_type table type possible_keys key key_len ref rows Extra 2371 SIMPLE t1m system NULL NULL NULL NULL 0 const row not found 2381 SIMPLE t1i ALL NULL NULL NULL NULL 1 NULL 239select count(*), min(7), max(7) from t1m, t1i; 240count(*) min(7) max(7) 2410 NULL NULL 242explain select count(*), min(7), max(7) from t1m, t2i; 243id select_type table type possible_keys key key_len ref rows Extra 2441 SIMPLE t1m system NULL NULL NULL NULL 0 const row not found 2451 SIMPLE t2i ALL NULL NULL NULL NULL 1 NULL 246select count(*), min(7), max(7) from t1m, t2i; 247count(*) min(7) max(7) 2480 NULL NULL 249explain select count(*), min(7), max(7) from t2m, t1i; 250id select_type table type possible_keys key key_len ref rows Extra 2511 SIMPLE t2m system NULL NULL NULL NULL 1 NULL 2521 SIMPLE t1i ALL NULL NULL NULL NULL 1 NULL 253select count(*), min(7), max(7) from t2m, t1i; 254count(*) min(7) max(7) 2550 NULL NULL 256drop table t1m, t1i, t2m, t2i; 257create table t1 ( 258a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' ' 259) ENGINE = MEMORY; 260insert into t1 (a1, a2, b, c, d) values 261('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), 262('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), 263('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), 264('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), 265('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), 266('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), 267('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), 268('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), 269('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), 270('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), 271('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), 272('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'), 273('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'), 274('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'), 275('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'), 276('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'), 277('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), 278('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), 279('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), 280('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), 281('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), 282('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), 283('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), 284('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), 285('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), 286('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), 287('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), 288('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'), 289('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'), 290('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'), 291('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'), 292('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'); 293create table t4 ( 294pk_col int auto_increment primary key, a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' ' 295); 296insert into t4 (a1, a2, b, c, d, dummy) select * from t1; 297create index idx12672_0 on t4 (a1); 298create index idx12672_1 on t4 (a1,a2,b,c); 299create index idx12672_2 on t4 (a1,a2,b); 300analyze table t4; 301Table Op Msg_type Msg_text 302test.t4 analyze status OK 303select distinct a1 from t4 where pk_col not in (1,2,3,4); 304a1 305a 306b 307c 308d 309drop table t1,t4; 310DROP TABLE IF EXISTS t2, t1; 311CREATE TABLE t1 (i INT NOT NULL PRIMARY KEY) ENGINE= InnoDB; 312CREATE TABLE t2 ( 313i INT NOT NULL, 314FOREIGN KEY (i) REFERENCES t1 (i) ON DELETE NO ACTION 315) ENGINE= InnoDB; 316INSERT INTO t1 VALUES (1); 317INSERT INTO t2 VALUES (1); 318DELETE IGNORE FROM t1 WHERE i = 1; 319Warnings: 320Error 1451 Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`i`) REFERENCES `t1` (`i`) ON DELETE NO ACTION) 321SELECT * FROM t1, t2; 322i i 3231 1 324DROP TABLE t2, t1; 325End of 4.1 tests. 326create table t1 ( 327a varchar(30), b varchar(30), primary key(a), key(b) 328); 329select distinct a from t1; 330a 331drop table t1; 332create table t1(a int, key(a)); 333insert into t1 values(1); 334select a, count(a) from t1 group by a with rollup; 335a count(a) 3361 1 337NULL 1 338drop table t1; 339create table t1 (f1 int, f2 char(1), primary key(f1,f2)) stats_persistent=0; 340insert into t1 values ( 1,"e"),(2,"a"),( 3,"c"),(4,"d"); 341alter table t1 drop primary key, add primary key (f2, f1); 342explain select distinct f1 a, f1 b from t1; 343id select_type table type possible_keys key key_len ref rows Extra 3441 SIMPLE t1 index PRIMARY PRIMARY 5 NULL 4 Using index; Using temporary 345explain select distinct f1, f2 from t1; 346id select_type table type possible_keys key key_len ref rows Extra 3471 SIMPLE t1 index PRIMARY PRIMARY 5 NULL 4 Using index 348drop table t1; 349CREATE TABLE t1 (id int(11) NOT NULL PRIMARY KEY, name varchar(20), 350INDEX (name)); 351CREATE TABLE t2 (id int(11) NOT NULL PRIMARY KEY, fkey int(11)); 352ALTER TABLE t2 ADD FOREIGN KEY (fkey) REFERENCES t2(id); 353INSERT INTO t1 VALUES (1,'A1'),(2,'A2'),(3,'B'); 354INSERT INTO t2 VALUES (1,1),(2,2),(3,2),(4,3),(5,3); 355EXPLAIN 356SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id 357WHERE t1.name LIKE 'A%'; 358id select_type table type possible_keys key key_len ref rows Extra 3591 SIMPLE t1 index PRIMARY,name name 23 NULL 3 Using where; Using index 3601 SIMPLE t2 ref fkey fkey 5 test.t1.id 1 Using index 361EXPLAIN 362SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id 363WHERE t1.name LIKE 'A%' OR FALSE; 364id select_type table type possible_keys key key_len ref rows Extra 3651 SIMPLE t2 index NULL fkey 5 NULL 5 Using index 3661 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.fkey 1 Using where 367DROP TABLE t1,t2; 368CREATE TABLE t1 ( 369id int NOT NULL, 370name varchar(20) NOT NULL, 371dept varchar(20) NOT NULL, 372age tinyint(3) unsigned NOT NULL, 373PRIMARY KEY (id), 374INDEX (name,dept) 375) ENGINE=InnoDB STATS_PERSISTENT=0; 376INSERT INTO t1(id, dept, age, name) VALUES 377(3987, 'cs1', 10, 'rs1'), (3988, 'cs2', 20, 'rs1'), (3995, 'cs3', 10, 'rs2'), 378(3996, 'cs4', 20, 'rs2'), (4003, 'cs5', 10, 'rs3'), (4004, 'cs6', 20, 'rs3'), 379(4011, 'cs7', 10, 'rs4'), (4012, 'cs8', 20, 'rs4'), (4019, 'cs9', 10, 'rs5'), 380(4020, 'cs10', 20, 'rs5'),(4027, 'cs11', 10, 'rs6'),(4028, 'cs12', 20, 'rs6'); 381EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; 382id select_type table type possible_keys key key_len ref rows Extra 3831 SIMPLE t1 range name name 44 NULL 2 Using where; Using index for group-by 384SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; 385name dept 386rs5 cs10 387rs5 cs9 388DELETE FROM t1; 389SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; 390name dept 391DROP TABLE t1; 392drop table if exists t1; 393show variables like 'innodb_rollback_on_timeout'; 394Variable_name Value 395innodb_rollback_on_timeout OFF 396create table t1 (a int unsigned not null primary key) engine = innodb; 397insert into t1 values (1); 398commit; 399begin work; 400insert into t1 values (2); 401select * from t1; 402a 4031 4042 405begin work; 406insert into t1 values (5); 407select * from t1; 408a 4091 4105 411insert into t1 values (2); 412ERROR HY000: Lock wait timeout exceeded; try restarting transaction 413select * from t1; 414a 4151 4165 417commit; 418select * from t1; 419a 4201 4212 422commit; 423select * from t1; 424a 4251 4262 4275 428drop table t1; 429set @save_qcache_size=@@global.query_cache_size; 430set @save_qcache_type=@@global.query_cache_type; 431set global query_cache_size=10*1024*1024; 432set global query_cache_type=1; 433drop table if exists `test`; 434Warnings: 435Note 1051 Unknown table 'test.test' 436CREATE TABLE `test` (`test1` varchar(3) NOT NULL, 437`test2` varchar(4) NOT NULL,PRIMARY KEY (`test1`)) 438ENGINE=InnoDB DEFAULT CHARSET=latin1; 439INSERT INTO `test` (`test1`, `test2`) VALUES ('tes', '5678'); 440select * from test; 441test1 test2 442tes 5678 443INSERT INTO `test` (`test1`, `test2`) VALUES ('tes', '1234') 444ON DUPLICATE KEY UPDATE `test2` = '1234'; 445select * from test; 446test1 test2 447tes 1234 448flush tables; 449select * from test; 450test1 test2 451tes 1234 452drop table test; 453set global query_cache_type=@save_qcache_type; 454set global query_cache_size=@save_qcache_size; 455drop table if exists t1; 456show variables like 'innodb_rollback_on_timeout'; 457Variable_name Value 458innodb_rollback_on_timeout OFF 459create table t1 (a int unsigned not null primary key) engine = innodb; 460insert into t1 values (1); 461commit; 462begin work; 463insert into t1 values (2); 464select * from t1; 465a 4661 4672 468begin work; 469insert into t1 values (5); 470select * from t1; 471a 4721 4735 474insert into t1 values (2); 475ERROR HY000: Lock wait timeout exceeded; try restarting transaction 476select * from t1; 477a 4781 4795 480commit; 481select * from t1; 482a 4831 4842 485commit; 486select * from t1; 487a 4881 4892 4905 491drop table t1; 492create table t1( 493id int auto_increment, 494c char(1) not null, 495counter int not null default 1, 496primary key (id), 497unique key (c) 498) engine=innodb; 499insert into t1 (id, c) values 500(NULL, 'a'), 501(NULL, 'a') 502on duplicate key update id = values(id), counter = counter + 1; 503select * from t1; 504id c counter 5052 a 2 506insert into t1 (id, c) values 507(NULL, 'b') 508on duplicate key update id = values(id), counter = counter + 1; 509select * from t1; 510id c counter 5112 a 2 5123 b 1 513truncate table t1; 514insert into t1 (id, c) values (NULL, 'a'); 515select * from t1; 516id c counter 5171 a 1 518insert into t1 (id, c) values (NULL, 'b'), (NULL, 'b') 519on duplicate key update id = values(id), c = values(c), counter = counter + 1; 520select * from t1; 521id c counter 5221 a 1 5233 b 2 524insert into t1 (id, c) values (NULL, 'a') 525on duplicate key update id = values(id), c = values(c), counter = counter + 1; 526select * from t1; 527id c counter 5283 b 2 5294 a 2 530drop table t1; 531CREATE TABLE t1( 532id int AUTO_INCREMENT PRIMARY KEY, 533stat_id int NOT NULL, 534acct_id int DEFAULT NULL, 535INDEX idx1 (stat_id, acct_id), 536INDEX idx2 (acct_id) 537) ENGINE=MyISAM; 538CREATE TABLE t2( 539id int AUTO_INCREMENT PRIMARY KEY, 540stat_id int NOT NULL, 541acct_id int DEFAULT NULL, 542INDEX idx1 (stat_id, acct_id), 543INDEX idx2 (acct_id) 544) ENGINE=InnoDB STATS_PERSISTENT=0; 545INSERT INTO t1(stat_id,acct_id) VALUES 546(1,759), (2,831), (3,785), (4,854), (1,921), 547(1,553), (2,589), (3,743), (2,827), (2,545), 548(4,779), (4,783), (1,597), (1,785), (4,832), 549(1,741), (1,833), (3,788), (2,973), (1,907); 550INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; 551INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; 552INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; 553INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; 554INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; 555INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; 556INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; 557INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; 558INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; 559INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; 560INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; 561UPDATE t1 SET acct_id=785 562WHERE MOD(stat_id,2)=0 AND MOD(id,stat_id)=MOD(acct_id,stat_id); 563OPTIMIZE TABLE t1; 564Table Op Msg_type Msg_text 565test.t1 optimize status OK 566SELECT COUNT(*) FROM t1; 567COUNT(*) 56840960 569SELECT COUNT(*) FROM t1 WHERE acct_id=785; 570COUNT(*) 5718702 572EXPLAIN SELECT COUNT(*) FROM t1 WHERE stat_id IN (1,3) AND acct_id=785; 573id select_type table type possible_keys key key_len ref rows Extra 5741 SIMPLE t1 range idx1,idx2 idx1 9 NULL 2 Using where; Using index 575INSERT INTO t2 SELECT * FROM t1; 576OPTIMIZE TABLE t2; 577Table Op Msg_type Msg_text 578test.t2 optimize note Table does not support optimize, doing recreate + analyze instead 579test.t2 optimize status OK 580EXPLAIN SELECT COUNT(*) FROM t2 WHERE stat_id IN (1,3) AND acct_id=785; 581id select_type table type possible_keys key key_len ref rows Extra 5821 SIMPLE t2 range idx1,idx2 idx1 9 NULL 2 Using where; Using index 583DROP TABLE t1,t2; 584create table t1(a int) engine=innodb; 585alter table t1 comment '123'; 586show create table t1; 587Table Create Table 588t1 CREATE TABLE `t1` ( 589 `a` int(11) DEFAULT NULL 590) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='123' 591drop table t1; 592CREATE TABLE t1 (a CHAR(2), KEY (a)) ENGINE = InnoDB DEFAULT CHARSET=UTF8; 593INSERT INTO t1 VALUES ('uk'),('bg'); 594SELECT * FROM t1 WHERE a = 'uk'; 595a 596uk 597DELETE FROM t1 WHERE a = 'uk'; 598SELECT * FROM t1 WHERE a = 'uk'; 599a 600UPDATE t1 SET a = 'us' WHERE a = 'uk'; 601SELECT * FROM t1 WHERE a = 'uk'; 602a 603CREATE TABLE t2 (a CHAR(2), KEY (a)) ENGINE = InnoDB; 604INSERT INTO t2 VALUES ('uk'),('bg'); 605SELECT * FROM t2 WHERE a = 'uk'; 606a 607uk 608DELETE FROM t2 WHERE a = 'uk'; 609SELECT * FROM t2 WHERE a = 'uk'; 610a 611INSERT INTO t2 VALUES ('uk'); 612UPDATE t2 SET a = 'us' WHERE a = 'uk'; 613SELECT * FROM t2 WHERE a = 'uk'; 614a 615CREATE TABLE t3 (a CHAR(2), KEY (a)) ENGINE = MyISAM; 616INSERT INTO t3 VALUES ('uk'),('bg'); 617SELECT * FROM t3 WHERE a = 'uk'; 618a 619uk 620DELETE FROM t3 WHERE a = 'uk'; 621SELECT * FROM t3 WHERE a = 'uk'; 622a 623INSERT INTO t3 VALUES ('uk'); 624UPDATE t3 SET a = 'us' WHERE a = 'uk'; 625SELECT * FROM t3 WHERE a = 'uk'; 626a 627DROP TABLE t1,t2,t3; 628create table t1 (a int) engine=innodb; 629select * from bug29807; 630ERROR 42S02: Table 'test.bug29807' doesn't exist 631drop table t1; 632drop table bug29807; 633ERROR 42S02: Unknown table 'test.bug29807' 634create table bug29807 (a int); 635drop table bug29807; 636CREATE TABLE t1 (a INT) ENGINE=InnoDB; 637CREATE TABLE t2 (a INT) ENGINE=InnoDB; 638switch to connection c1 639SET AUTOCOMMIT=0; 640INSERT INTO t2 VALUES (1); 641switch to connection c2 642SET AUTOCOMMIT=0; 643LOCK TABLES t1 READ, t2 READ; 644ERROR HY000: Lock wait timeout exceeded; try restarting transaction 645switch to connection c1 646COMMIT; 647INSERT INTO t1 VALUES (1); 648switch to connection default 649SET AUTOCOMMIT=default; 650DROP TABLE t1,t2; 651CREATE TABLE t1 ( 652id int NOT NULL auto_increment PRIMARY KEY, 653b int NOT NULL, 654c datetime NOT NULL, 655INDEX idx_b(b), 656INDEX idx_c(c) 657) ENGINE=InnoDB; 658CREATE TABLE t2 ( 659b int NOT NULL auto_increment PRIMARY KEY, 660c datetime NOT NULL 661) ENGINE= MyISAM; 662INSERT INTO t2(c) VALUES ('2007-01-01'); 663INSERT INTO t2(c) SELECT c FROM t2; 664INSERT INTO t2(c) SELECT c FROM t2; 665INSERT INTO t2(c) SELECT c FROM t2; 666INSERT INTO t2(c) SELECT c FROM t2; 667INSERT INTO t2(c) SELECT c FROM t2; 668INSERT INTO t2(c) SELECT c FROM t2; 669INSERT INTO t2(c) SELECT c FROM t2; 670INSERT INTO t2(c) SELECT c FROM t2; 671INSERT INTO t2(c) SELECT c FROM t2; 672INSERT INTO t2(c) SELECT c FROM t2; 673INSERT INTO t1(b,c) SELECT b,c FROM t2; 674UPDATE t2 SET c='2007-01-02'; 675INSERT INTO t1(b,c) SELECT b,c FROM t2; 676UPDATE t2 SET c='2007-01-03'; 677INSERT INTO t1(b,c) SELECT b,c FROM t2; 678set @@sort_buffer_size=8192; 679Warnings: 680Warning 1292 Truncated incorrect sort_buffer_size value: '8192' 681SELECT COUNT(*) FROM t1; 682COUNT(*) 6833072 684EXPLAIN 685SELECT COUNT(*) FROM t1 686WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1; 687id select_type table type possible_keys key key_len ref rows Extra 6881 SIMPLE t1 ALL idx_b,idx_c NULL NULL NULL # Using where 689SELECT COUNT(*) FROM t1 690WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1; 691COUNT(*) 6923072 693EXPLAIN 694SELECT COUNT(*) FROM t1 FORCE INDEX(idx_b, idx_c) 695WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1; 696id select_type table type possible_keys key key_len ref rows Extra 6971 SIMPLE t1 index_merge idx_b,idx_c idx_c,idx_b 5,4 NULL # Using sort_union(idx_c,idx_b); Using where 698SELECT COUNT(*) FROM t1 FORCE INDEX(idx_b, idx_c) 699WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1; 700COUNT(*) 7013072 702set @@sort_buffer_size=default; 703DROP TABLE t1,t2; 704CREATE TABLE t1 (a int, b int); 705insert into t1 values (1,1),(1,2); 706CREATE TABLE t2 (primary key (a)) select * from t1; 707ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 708drop table if exists t2; 709Warnings: 710Note 1051 Unknown table 'test.t2' 711CREATE TEMPORARY TABLE t2 (primary key (a)) select * from t1; 712ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 713drop table if exists t2; 714Warnings: 715Note 1051 Unknown table 'test.t2' 716CREATE TABLE t2 (a int, b int, primary key (a)); 717BEGIN; 718INSERT INTO t2 values(100,100); 719CREATE TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1; 720Warnings: 721Note 1050 Table 't2' already exists 722SELECT * from t2; 723a b 724100 100 725ROLLBACK; 726SELECT * from t2; 727a b 728100 100 729TRUNCATE table t2; 730INSERT INTO t2 select * from t1; 731ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 732SELECT * from t2; 733a b 734drop table t2; 735CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a)); 736BEGIN; 737INSERT INTO t2 values(100,100); 738CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1; 739Warnings: 740Note 1050 Table 't2' already exists 741SELECT * from t2; 742a b 743100 100 744COMMIT; 745BEGIN; 746INSERT INTO t2 values(101,101); 747CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1; 748Warnings: 749Note 1050 Table 't2' already exists 750SELECT * from t2; 751a b 752100 100 753101 101 754ROLLBACK; 755SELECT * from t2; 756a b 757100 100 758TRUNCATE table t2; 759INSERT INTO t2 select * from t1; 760ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 761SELECT * from t2; 762a b 763drop table t1,t2; 764create table t1(f1 varchar(800) binary not null, key(f1)) 765character set utf8 collate utf8_general_ci; 766Warnings: 767Warning 1071 Specified key was too long; max key length is 767 bytes 768insert into t1 values('aaa'); 769drop table t1; 770CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c FLOAT, KEY b(b)) ENGINE = INNODB; 771INSERT INTO t1 VALUES ( 1 , 1 , 1); 772INSERT INTO t1 SELECT a + 1 , MOD(a + 1 , 20), 1 FROM t1; 773INSERT INTO t1 SELECT a + 2 , MOD(a + 2 , 20), 1 FROM t1; 774INSERT INTO t1 SELECT a + 4 , MOD(a + 4 , 20), 1 FROM t1; 775INSERT INTO t1 SELECT a + 8 , MOD(a + 8 , 20), 1 FROM t1; 776INSERT INTO t1 SELECT a + 16, MOD(a + 16, 20), 1 FROM t1; 777INSERT INTO t1 SELECT a + 32, MOD(a + 32, 20), 1 FROM t1; 778INSERT INTO t1 SELECT a + 64, MOD(a + 64, 20), 1 FROM t1; 779EXPLAIN SELECT b, SUM(c) FROM t1 GROUP BY b; 780id select_type table type possible_keys key key_len ref rows Extra 7811 SIMPLE t1 index b b 5 NULL 128 NULL 782EXPLAIN SELECT SQL_BIG_RESULT b, SUM(c) FROM t1 GROUP BY b; 783id select_type table type possible_keys key key_len ref rows Extra 7841 SIMPLE t1 ALL b NULL NULL NULL 128 Using filesort 785DROP TABLE t1; 786drop table if exists t1; 787show variables like 'innodb_rollback_on_timeout'; 788Variable_name Value 789innodb_rollback_on_timeout OFF 790create table t1 (a int unsigned not null primary key) engine = innodb; 791insert into t1 values (1); 792commit; 793begin work; 794insert into t1 values (2); 795select * from t1; 796a 7971 7982 799begin work; 800insert into t1 values (5); 801select * from t1; 802a 8031 8045 805insert into t1 values (2); 806ERROR HY000: Lock wait timeout exceeded; try restarting transaction 807select * from t1; 808a 8091 8105 811commit; 812select * from t1; 813a 8141 8152 816commit; 817select * from t1; 818a 8191 8202 8215 822drop table t1; 823drop table if exists t1; 824create table t1 (a int) engine=innodb; 825alter table t1 alter a set default 1; 826drop table t1; 827 828Bug#24918 drop table and lock / inconsistent between 829perm and temp tables 830 831Check transactional tables under LOCK TABLES 832 833drop table if exists t24918, t24918_tmp, t24918_trans, t24918_trans_tmp, 834t24918_access; 835create table t24918_access (id int); 836create table t24918 (id int) engine=myisam; 837create temporary table t24918_tmp (id int) engine=myisam; 838create table t24918_trans (id int) engine=innodb; 839create temporary table t24918_trans_tmp (id int) engine=innodb; 840lock table t24918 write, t24918_tmp write, t24918_trans write, t24918_trans_tmp write; 841drop table t24918; 842select * from t24918_access; 843ERROR HY000: Table 't24918_access' was not locked with LOCK TABLES 844drop table t24918_trans; 845select * from t24918_access; 846ERROR HY000: Table 't24918_access' was not locked with LOCK TABLES 847drop table t24918_trans_tmp; 848select * from t24918_access; 849ERROR HY000: Table 't24918_access' was not locked with LOCK TABLES 850drop table t24918_tmp; 851select * from t24918_access; 852ERROR HY000: Table 't24918_access' was not locked with LOCK TABLES 853unlock tables; 854drop table t24918_access; 855CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY bkey (b)) ENGINE=InnoDB; 856INSERT INTO t1 VALUES (1,2),(3,2),(2,2),(4,2),(5,2),(6,2),(7,2),(8,2); 857INSERT INTO t1 SELECT a + 8, 2 FROM t1; 858INSERT INTO t1 SELECT a + 16, 1 FROM t1; 859EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a; 860id 1 861select_type SIMPLE 862table t1 863type ref 864possible_keys bkey 865key bkey 866key_len 5 867ref const 868rows 16 869Extra Using where; Using index 870SELECT * FROM t1 WHERE b=2 ORDER BY a; 871a b 8721 2 8732 2 8743 2 8754 2 8765 2 8776 2 8787 2 8798 2 8809 2 88110 2 88211 2 88312 2 88413 2 88514 2 88615 2 88716 2 888EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a; 889id 1 890select_type SIMPLE 891table t1 892type index 893possible_keys bkey 894key PRIMARY 895key_len 4 896ref NULL 897rows 32 898Extra Using where 899SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a; 900a b 9011 2 9022 2 9033 2 9044 2 9055 2 9066 2 9077 2 9088 2 9099 2 91010 2 91111 2 91212 2 91313 2 91414 2 91515 2 91616 2 91717 1 91818 1 91919 1 92020 1 92121 1 92222 1 92323 1 92424 1 92525 1 92626 1 92727 1 92828 1 92929 1 93030 1 93131 1 93232 1 933EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a; 934id 1 935select_type SIMPLE 936table t1 937type index 938possible_keys bkey 939key bkey 940key_len 5 941ref NULL 942rows 32 943Extra Using where; Using index 944SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a; 945a b 94617 1 94718 1 94819 1 94920 1 95021 1 95122 1 95223 1 95324 1 95425 1 95526 1 95627 1 95728 1 95829 1 95930 1 96031 1 96132 1 9621 2 9632 2 9643 2 9654 2 9665 2 9676 2 9687 2 9698 2 9709 2 97110 2 97211 2 97312 2 97413 2 97514 2 97615 2 97716 2 978CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a), KEY bkey (b,c)) 979ENGINE=InnoDB; 980INSERT INTO t2 VALUES (1,1,1),(3,1,1),(2,1,1),(4,1,1); 981INSERT INTO t2 SELECT a + 4, 1, 1 FROM t2; 982INSERT INTO t2 SELECT a + 8, 1, 1 FROM t2; 983EXPLAIN SELECT * FROM t2 WHERE b=1 ORDER BY a; 984id 1 985select_type SIMPLE 986table t2 987type ref 988possible_keys bkey 989key bkey 990key_len 5 991ref const 992rows 16 993Extra Using where; Using index; Using filesort 994SELECT * FROM t2 WHERE b=1 ORDER BY a; 995a b c 9961 1 1 9972 1 1 9983 1 1 9994 1 1 10005 1 1 10016 1 1 10027 1 1 10038 1 1 10049 1 1 100510 1 1 100611 1 1 100712 1 1 100813 1 1 100914 1 1 101015 1 1 101116 1 1 1012EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a; 1013id 1 1014select_type SIMPLE 1015table t2 1016type ref 1017possible_keys bkey 1018key bkey 1019key_len 10 1020ref const,const 1021rows 16 1022Extra Using where; Using index 1023SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a; 1024a b c 10251 1 1 10262 1 1 10273 1 1 10284 1 1 10295 1 1 10306 1 1 10317 1 1 10328 1 1 10339 1 1 103410 1 1 103511 1 1 103612 1 1 103713 1 1 103814 1 1 103915 1 1 104016 1 1 1041EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a; 1042id 1 1043select_type SIMPLE 1044table t2 1045type ref 1046possible_keys bkey 1047key bkey 1048key_len 10 1049ref const,const 1050rows 16 1051Extra Using where; Using index 1052SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a; 1053a b c 10541 1 1 10552 1 1 10563 1 1 10574 1 1 10585 1 1 10596 1 1 10607 1 1 10618 1 1 10629 1 1 106310 1 1 106411 1 1 106512 1 1 106613 1 1 106714 1 1 106815 1 1 106916 1 1 1070EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a; 1071id 1 1072select_type SIMPLE 1073table t2 1074type ref 1075possible_keys bkey 1076key bkey 1077key_len 10 1078ref const,const 1079rows 16 1080Extra Using where; Using index 1081SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a; 1082a b c 10831 1 1 10842 1 1 10853 1 1 10864 1 1 10875 1 1 10886 1 1 10897 1 1 10908 1 1 10919 1 1 109210 1 1 109311 1 1 109412 1 1 109513 1 1 109614 1 1 109715 1 1 109816 1 1 1099DROP TABLE t1,t2; 1100CREATE TABLE t1 (a INT, PRIMARY KEY (a)) ENGINE=InnoDB; 1101INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8); 1102INSERT INTO t1 SELECT a + 8 FROM t1; 1103INSERT INTO t1 SELECT a + 16 FROM t1; 1104CREATE PROCEDURE p1 () 1105BEGIN 1106DECLARE i INT DEFAULT 50; 1107DECLARE cnt INT; 1108# Continue even in the presence of ER_LOCK_DEADLOCK. 1109DECLARE CONTINUE HANDLER FOR 1213 BEGIN END; 1110START TRANSACTION; 1111ALTER TABLE t1 ENGINE=InnoDB; 1112COMMIT; 1113START TRANSACTION; 1114WHILE (i > 0) DO 1115SET i = i - 1; 1116SELECT COUNT(*) INTO cnt FROM t1 LOCK IN SHARE MODE; 1117END WHILE; 1118COMMIT; 1119END;| 1120CALL p1(); 1121CALL p1(); 1122CALL p1(); 1123DROP PROCEDURE p1; 1124DROP TABLE t1; 1125create table t1(a text) engine=innodb default charset=utf8; 1126insert into t1 values('aaa'); 1127alter table t1 add index(a(1024)); 1128Warnings: 1129Warning 1071 Specified key was too long; max key length is 767 bytes 1130show create table t1; 1131Table Create Table 1132t1 CREATE TABLE `t1` ( 1133 `a` text, 1134 KEY `a` (`a`(255)) 1135) ENGINE=InnoDB DEFAULT CHARSET=utf8 1136drop table t1; 1137CREATE TABLE t1 ( 1138a INT, 1139b INT, 1140KEY (b) 1141) ENGINE=InnoDB; 1142INSERT INTO t1 VALUES (1,10), (2,10), (2,20), (3,30); 1143START TRANSACTION; 1144SELECT * FROM t1 WHERE b=20 FOR UPDATE; 1145a b 11462 20 1147START TRANSACTION; 1148SELECT * FROM t1 WHERE b=10 ORDER BY A FOR UPDATE; 1149a b 11501 10 11512 10 1152ROLLBACK; 1153ROLLBACK; 1154DROP TABLE t1; 1155CREATE TABLE t1( 1156a INT, 1157b INT NOT NULL, 1158c INT NOT NULL, 1159d INT, 1160UNIQUE KEY (c,b) 1161) engine=innodb; 1162INSERT INTO t1 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4); 1163EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d; 1164id select_type table type possible_keys key key_len ref rows Extra 11651 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort 1166SELECT c,b,d FROM t1 GROUP BY c,b,d; 1167c b d 11681 1 50 11693 1 4 11703 2 40 1171EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL; 1172id select_type table type possible_keys key key_len ref rows Extra 11731 SIMPLE t1 ALL NULL NULL NULL NULL 3 NULL 1174SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL; 1175c b d 11761 1 50 11773 1 4 11783 2 40 1179EXPLAIN SELECT c,b,d FROM t1 ORDER BY c,b,d; 1180id select_type table type possible_keys key key_len ref rows Extra 11811 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort 1182SELECT c,b,d FROM t1 ORDER BY c,b,d; 1183c b d 11841 1 50 11853 1 4 11863 2 40 1187EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b; 1188id select_type table type possible_keys key key_len ref rows Extra 11891 SIMPLE t1 index c c 8 NULL 3 NULL 1190SELECT c,b,d FROM t1 GROUP BY c,b; 1191c b d 11921 1 50 11933 1 4 11943 2 40 1195EXPLAIN SELECT c,b FROM t1 GROUP BY c,b; 1196id select_type table type possible_keys key key_len ref rows Extra 11971 SIMPLE t1 index c c 8 NULL 3 Using index 1198SELECT c,b FROM t1 GROUP BY c,b; 1199c b 12001 1 12013 1 12023 2 1203DROP TABLE t1; 1204CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), INDEX b (b)) ENGINE=InnoDB; 1205INSERT INTO t1(a,b) VALUES (1,1), (2,2), (3,2); 1206EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a ASC; 1207id 1 1208select_type SIMPLE 1209table t1 1210type ref 1211possible_keys b 1212key b 1213key_len 5 1214ref const 1215rows 2 1216Extra Using where; Using index 1217SELECT * FROM t1 WHERE b=2 ORDER BY a ASC; 1218a b 12192 2 12203 2 1221EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a DESC; 1222id 1 1223select_type SIMPLE 1224table t1 1225type ref 1226possible_keys b 1227key b 1228key_len 5 1229ref const 1230rows 2 1231Extra Using where; Using index 1232SELECT * FROM t1 WHERE b=2 ORDER BY a DESC; 1233a b 12343 2 12352 2 1236EXPLAIN SELECT * FROM t1 ORDER BY b ASC, a ASC; 1237id 1 1238select_type SIMPLE 1239table t1 1240type index 1241possible_keys NULL 1242key b 1243key_len 5 1244ref NULL 1245rows 3 1246Extra Using index 1247SELECT * FROM t1 ORDER BY b ASC, a ASC; 1248a b 12491 1 12502 2 12513 2 1252EXPLAIN SELECT * FROM t1 ORDER BY b DESC, a DESC; 1253id 1 1254select_type SIMPLE 1255table t1 1256type index 1257possible_keys NULL 1258key b 1259key_len 5 1260ref NULL 1261rows 3 1262Extra Using index 1263SELECT * FROM t1 ORDER BY b DESC, a DESC; 1264a b 12653 2 12662 2 12671 1 1268EXPLAIN SELECT * FROM t1 ORDER BY b ASC, a DESC; 1269id 1 1270select_type SIMPLE 1271table t1 1272type index 1273possible_keys NULL 1274key b 1275key_len 5 1276ref NULL 1277rows 3 1278Extra Using index; Using filesort 1279SELECT * FROM t1 ORDER BY b ASC, a DESC; 1280a b 12811 1 12823 2 12832 2 1284EXPLAIN SELECT * FROM t1 ORDER BY b DESC, a ASC; 1285id 1 1286select_type SIMPLE 1287table t1 1288type index 1289possible_keys NULL 1290key b 1291key_len 5 1292ref NULL 1293rows 3 1294Extra Using index; Using filesort 1295SELECT * FROM t1 ORDER BY b DESC, a ASC; 1296a b 12972 2 12983 2 12991 1 1300DROP TABLE t1; 1301 1302# 1303# Bug#27610: ALTER TABLE ROW_FORMAT=... does not rebuild the table. 1304# 1305 1306# - prepare; 1307 1308DROP TABLE IF EXISTS t1; 1309 1310CREATE TABLE t1(c INT) 1311ENGINE = InnoDB 1312ROW_FORMAT = COMPACT; 1313 1314# - initial check; 1315 1316SELECT table_schema, table_name, row_format 1317FROM INFORMATION_SCHEMA.TABLES 1318WHERE table_schema = DATABASE() AND table_name = 't1'; 1319table_schema table_name row_format 1320test t1 Compact 1321 1322# - change ROW_FORMAT and check; 1323 1324ALTER TABLE t1 ROW_FORMAT = REDUNDANT; 1325 1326SELECT table_schema, table_name, row_format 1327FROM INFORMATION_SCHEMA.TABLES 1328WHERE table_schema = DATABASE() AND table_name = 't1'; 1329table_schema table_name row_format 1330test t1 Redundant 1331 1332# - that's it, cleanup. 1333 1334DROP TABLE t1; 1335create table t1(a char(10) not null, unique key aa(a(1)), 1336b char(4) not null, unique key bb(b(4))) engine=innodb; 1337desc t1; 1338Field Type Null Key Default Extra 1339a char(10) NO UNI NULL 1340b char(4) NO PRI NULL 1341show create table t1; 1342Table Create Table 1343t1 CREATE TABLE `t1` ( 1344 `a` char(10) NOT NULL, 1345 `b` char(4) NOT NULL, 1346 UNIQUE KEY `bb` (`b`), 1347 UNIQUE KEY `aa` (`a`(1)) 1348) ENGINE=InnoDB DEFAULT CHARSET=latin1 1349drop table t1; 1350CREATE TABLE t1 (id int, type char(6), d int, INDEX idx(id,d)) ENGINE=InnoDB; 1351INSERT INTO t1 VALUES 1352(191, 'member', 1), (NULL, 'member', 3), (NULL, 'member', 4), (201, 'member', 2); 1353EXPLAIN SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d; 1354id select_type table type possible_keys key key_len ref rows Extra 13551 SIMPLE t1 ALL idx NULL NULL NULL 4 Using where; Using filesort 1356SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d; 1357id type d 1358191 member 1 1359NULL member 3 1360NULL member 4 1361DROP TABLE t1; 1362set @my_innodb_autoextend_increment=@@global.innodb_autoextend_increment; 1363set global innodb_autoextend_increment=8; 1364set global innodb_autoextend_increment=@my_innodb_autoextend_increment; 1365set @my_innodb_commit_concurrency=@@global.innodb_commit_concurrency; 1366set global innodb_commit_concurrency=0; 1367set global innodb_commit_concurrency=@my_innodb_commit_concurrency; 1368CREATE TABLE t1 (a int, b int, c int, PRIMARY KEY (a), KEY t1_b (b)) 1369ENGINE=InnoDB; 1370INSERT INTO t1 (a,b,c) VALUES (1,1,1), (2,1,1), (3,1,1), (4,1,1); 1371INSERT INTO t1 (a,b,c) SELECT a+4,b,c FROM t1; 1372EXPLAIN SELECT a, b, c FROM t1 WHERE b = 1 ORDER BY a DESC LIMIT 5; 1373id select_type table type possible_keys key key_len ref rows Extra 13741 SIMPLE t1 range t1_b t1_b 5 NULL 8 Using index condition 1375SELECT a, b, c FROM t1 WHERE b = 1 ORDER BY a DESC LIMIT 5; 1376a b c 13778 1 1 13787 1 1 13796 1 1 13805 1 1 13814 1 1 1382DROP TABLE t1; 1383DROP TABLE IF EXISTS t1; 1384CREATE TABLE t1 (a char(50)) ENGINE=InnoDB; 1385CREATE INDEX i1 on t1 (a(3)); 1386SELECT * FROM t1 WHERE a = 'abcde'; 1387a 1388DROP TABLE t1; 1389# 1390# BUG #26288: savepoint are not deleted on comit, if the transaction 1391# was otherwise empty 1392# 1393BEGIN; 1394SAVEPOINT s1; 1395COMMIT; 1396RELEASE SAVEPOINT s1; 1397ERROR 42000: SAVEPOINT s1 does not exist 1398BEGIN; 1399SAVEPOINT s2; 1400COMMIT; 1401ROLLBACK TO SAVEPOINT s2; 1402ERROR 42000: SAVEPOINT s2 does not exist 1403BEGIN; 1404SAVEPOINT s3; 1405ROLLBACK; 1406RELEASE SAVEPOINT s3; 1407ERROR 42000: SAVEPOINT s3 does not exist 1408BEGIN; 1409SAVEPOINT s4; 1410ROLLBACK; 1411ROLLBACK TO SAVEPOINT s4; 1412ERROR 42000: SAVEPOINT s4 does not exist 1413CREATE TABLE t1 (f1 INTEGER PRIMARY KEY COMMENT 'My ID#', f2 INTEGER DEFAULT NULL, f3 CHAR(10) DEFAULT 'My ID#', CONSTRAINT f2_ref FOREIGN KEY (f2) REFERENCES t1 (f1)) ENGINE=INNODB; 1414SHOW CREATE TABLE t1; 1415Table Create Table 1416t1 CREATE TABLE `t1` ( 1417 `f1` int(11) NOT NULL COMMENT 'My ID#', 1418 `f2` int(11) DEFAULT NULL, 1419 `f3` char(10) DEFAULT 'My ID#', 1420 PRIMARY KEY (`f1`), 1421 KEY `f2_ref` (`f2`), 1422 CONSTRAINT `f2_ref` FOREIGN KEY (`f2`) REFERENCES `t1` (`f1`) 1423) ENGINE=InnoDB DEFAULT CHARSET=latin1 1424DROP TABLE t1; 1425# 1426# Bug #36995: valgrind error in remove_const during subquery executions 1427# 1428create table t1 (a bit(1) not null,b int) engine=myisam; 1429create table t2 (c int) engine=innodb; 1430explain 1431select b from t1 where a not in (select b from t1,t2 group by a) group by a; 1432id select_type table type possible_keys key key_len ref rows Extra 14331 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 14342 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 1435DROP TABLE t1,t2; 1436End of 5.0 tests 1437CREATE TABLE `t2` ( 1438`k` int(11) NOT NULL auto_increment, 1439`a` int(11) default NULL, 1440`c` int(11) default NULL, 1441PRIMARY KEY (`k`), 1442UNIQUE KEY `idx_1` (`a`) 1443); 1444insert into t2 ( a ) values ( 6 ) on duplicate key update c = 1445ifnull( c, 14460 ) + 1; 1447insert into t2 ( a ) values ( 7 ) on duplicate key update c = 1448ifnull( c, 14490 ) + 1; 1450select last_insert_id(); 1451last_insert_id() 14522 1453select * from t2; 1454k a c 14551 6 NULL 14562 7 NULL 1457insert into t2 ( a ) values ( 6 ) on duplicate key update c = 1458ifnull( c, 14590 ) + 1; 1460select last_insert_id(); 1461last_insert_id() 14622 1463select last_insert_id(0); 1464last_insert_id(0) 14650 1466insert into t2 ( a ) values ( 6 ) on duplicate key update c = 1467ifnull( c, 14680 ) + 1; 1469select last_insert_id(); 1470last_insert_id() 14710 1472select * from t2; 1473k a c 14741 6 2 14752 7 NULL 1476insert ignore into t2 values (null,6,1),(10,8,1); 1477select last_insert_id(); 1478last_insert_id() 14790 1480insert ignore into t2 values (null,6,1),(null,8,1),(null,15,1),(null,20,1); 1481select last_insert_id(); 1482last_insert_id() 148311 1484select * from t2; 1485k a c 14861 6 2 14872 7 NULL 148810 8 1 148911 15 1 149012 20 1 1491insert into t2 ( a ) values ( 6 ) on duplicate key update c = 1492ifnull( c, 14930 ) + 1, k=last_insert_id(k); 1494select last_insert_id(); 1495last_insert_id() 14961 1497select * from t2; 1498k a c 14991 6 3 15002 7 NULL 150110 8 1 150211 15 1 150312 20 1 1504drop table t2; 1505drop table if exists t1, t2; 1506create table t1 (i int); 1507alter table t1 modify i int default 1; 1508alter table t1 modify i int default 2, rename t2; 1509lock table t2 write; 1510alter table t2 modify i int default 3; 1511unlock tables; 1512lock table t2 write; 1513alter table t2 modify i int default 4, rename t1; 1514unlock tables; 1515drop table t1; 1516drop table if exists t1; 1517create table t1 (i int); 1518insert into t1 values (); 1519lock table t1 write; 1520alter table t1 modify i int default 1; 1521insert into t1 values (); 1522select * from t1; 1523i 1524NULL 15251 1526alter table t1 change i c char(10) default "Two"; 1527insert into t1 values (); 1528select * from t1; 1529c 1530NULL 15311 1532Two 1533unlock tables; 1534select * from t1; 1535c 1536NULL 15371 1538Two 1539drop tables t1; 1540create table t1(f1 varchar(5) unique, f2 timestamp NOT NULL DEFAULT 1541CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); 1542insert into t1(f1) values(1); 1543select @a:=f2 from t1; 1544@a:=f2 1545# 1546update t1 set f1=1; 1547select @b:=f2 from t1; 1548@b:=f2 1549# 1550select if(@a=@b,"ok","wrong"); 1551if(@a=@b,"ok","wrong") 1552ok 1553insert into t1(f1) values (1) on duplicate key update f1="1"; 1554select @b:=f2 from t1; 1555@b:=f2 1556# 1557select if(@a=@b,"ok","wrong"); 1558if(@a=@b,"ok","wrong") 1559ok 1560insert into t1(f1) select f1 from t1 on duplicate key update f1="1"; 1561select @b:=f2 from t1; 1562@b:=f2 1563# 1564select if(@a=@b,"ok","wrong"); 1565if(@a=@b,"ok","wrong") 1566ok 1567drop table t1; 1568SET SESSION AUTOCOMMIT = 0; 1569SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 1570set binlog_format=mixed; 1571# Switch to connection con1 1572CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(256)) 1573ENGINE = InnoDB; 1574INSERT INTO t1 VALUES (1,2); 1575# 1. test for locking: 1576BEGIN; 1577UPDATE t1 SET b = 12 WHERE a = 1; 1578affected rows: 1 1579info: Rows matched: 1 Changed: 1 Warnings: 0 1580SELECT * FROM t1; 1581a b 15821 12 1583# Switch to connection con2 1584UPDATE t1 SET b = 21 WHERE a = 1; 1585ERROR HY000: Lock wait timeout exceeded; try restarting transaction 1586# Switch to connection con1 1587SELECT * FROM t1; 1588a b 15891 12 1590ROLLBACK; 1591# Switch to connection con2 1592ROLLBACK; 1593# Switch to connection con1 1594# 2. test for serialized update: 1595CREATE TABLE t2 (a INT); 1596TRUNCATE t1; 1597INSERT INTO t1 VALUES (1,'init'); 1598CREATE PROCEDURE p1() 1599BEGIN 1600UPDATE t1 SET b = CONCAT(b, '+con2') WHERE a = 1; 1601INSERT INTO t2 VALUES (); 1602END| 1603BEGIN; 1604UPDATE t1 SET b = CONCAT(b, '+con1') WHERE a = 1; 1605affected rows: 1 1606info: Rows matched: 1 Changed: 1 Warnings: 0 1607SELECT * FROM t1; 1608a b 16091 init+con1 1610# Switch to connection con2 1611CALL p1;; 1612# Switch to connection con1 1613SELECT * FROM t1; 1614a b 16151 init+con1 1616COMMIT; 1617SELECT * FROM t1; 1618a b 16191 init+con1 1620# Switch to connection con2 1621SELECT * FROM t1; 1622a b 16231 init+con1+con2 1624COMMIT; 1625# Switch to connection con1 1626# 3. test for updated key column: 1627TRUNCATE t1; 1628TRUNCATE t2; 1629INSERT INTO t1 VALUES (1,'init'); 1630BEGIN; 1631UPDATE t1 SET a = 2, b = CONCAT(b, '+con1') WHERE a = 1; 1632affected rows: 1 1633info: Rows matched: 1 Changed: 1 Warnings: 0 1634SELECT * FROM t1; 1635a b 16362 init+con1 1637# Switch to connection con2 1638CALL p1;; 1639# Switch to connection con1 1640SELECT * FROM t1; 1641a b 16422 init+con1 1643COMMIT; 1644SELECT * FROM t1; 1645a b 16462 init+con1 1647# Switch to connection con2 1648SELECT * FROM t1; 1649a b 16502 init+con1 1651DROP PROCEDURE p1; 1652DROP TABLE t1, t2; 1653CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, PRIMARY KEY (a,b)) engine=innodb; 1654CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d), 1655CONSTRAINT c2 FOREIGN KEY f2 (c) REFERENCES t1 (a,b) ON UPDATE NO ACTION) engine=innodb; 1656ERROR 42000: Incorrect foreign key definition for 'f2': Key reference and table reference don't match 1657CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d), 1658CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a,b) ON UPDATE NO ACTION) engine=innodb; 1659ERROR 42000: Incorrect foreign key definition for 'c2': Key reference and table reference don't match 1660CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d), 1661CONSTRAINT c1 FOREIGN KEY c2 (c) REFERENCES t1 (a) ON DELETE NO ACTION, 1662CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION) engine=innodb; 1663ALTER TABLE t2 DROP FOREIGN KEY c2; 1664DROP TABLE t2; 1665CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d), 1666FOREIGN KEY (c) REFERENCES t1 (a,k) ON UPDATE NO ACTION) engine=innodb; 1667ERROR 42000: Incorrect foreign key definition for 'foreign key without name': Key reference and table reference don't match 1668CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d), 1669FOREIGN KEY f1 (c) REFERENCES t1 (a,k) ON UPDATE NO ACTION) engine=innodb; 1670ERROR 42000: Incorrect foreign key definition for 'f1': Key reference and table reference don't match 1671CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d), 1672CONSTRAINT c1 FOREIGN KEY f1 (c) REFERENCES t1 (a) ON DELETE NO ACTION, 1673CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION, 1674FOREIGN KEY f3 (c) REFERENCES t1 (a) ON UPDATE NO ACTION, 1675FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION) engine=innodb; 1676SHOW CREATE TABLE t2; 1677Table Create Table 1678t2 CREATE TABLE `t2` ( 1679 `c` int(11) NOT NULL, 1680 `d` int(11) NOT NULL, 1681 PRIMARY KEY (`c`,`d`), 1682 CONSTRAINT `c1` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON DELETE NO ACTION, 1683 CONSTRAINT `c2` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON UPDATE NO ACTION, 1684 CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON UPDATE NO ACTION, 1685 CONSTRAINT `t2_ibfk_2` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON UPDATE NO ACTION 1686) ENGINE=InnoDB DEFAULT CHARSET=latin1 1687DROP TABLE t2; 1688DROP TABLE t1; 1689create table t1 (a int auto_increment primary key) engine=innodb; 1690alter table t1 order by a; 1691Warnings: 1692Warning 1105 ORDER BY ignored as there is a user-defined clustered index in the table 't1' 1693drop table t1; 1694CREATE TABLE t1 1695(vid integer NOT NULL, 1696tid integer NOT NULL, 1697idx integer NOT NULL, 1698name varchar(128) NOT NULL, 1699type varchar(128) NULL, 1700PRIMARY KEY(idx, vid, tid), 1701UNIQUE(vid, tid, name) 1702) ENGINE=InnoDB; 1703INSERT INTO t1 VALUES 1704(1,1,1,'pk',NULL),(2,1,1,'pk',NULL),(3,1,1,'pk',NULL),(4,1,1,'c1',NULL), 1705(5,1,1,'pk',NULL),(1,1,2,'c1',NULL),(2,1,2,'c1',NULL),(3,1,2,'c1',NULL), 1706(4,1,2,'c2',NULL),(5,1,2,'c1',NULL),(2,1,3,'c2',NULL),(3,1,3,'c2',NULL), 1707(4,1,3,'pk',NULL),(5,1,3,'c2',NULL), 1708(2,1,4,'c_extra',NULL),(3,1,4,'c_extra',NULL); 1709EXPLAIN SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE tid = 1 AND vid = 3 ORDER BY idx DESC; 1710id select_type table type possible_keys key key_len ref rows Extra 17111 SIMPLE t1 index NULL PRIMARY 12 NULL 16 Using where 1712SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE tid = 1 AND vid = 3 ORDER BY idx DESC; 1713vid tid idx name type 17143 1 4 c_extra NULL 17153 1 3 c2 NULL 17163 1 2 c1 NULL 17173 1 1 pk NULL 1718DROP TABLE t1; 1719# 1720# Bug #44290: explain crashes for subquery with distinct in 1721# SQL_SELECT::test_quick_select 1722# (reproduced only with InnoDB tables) 1723# 1724CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, KEY (c3), KEY (c2, c3)) 1725ENGINE=InnoDB; 1726INSERT INTO t1 VALUES (1,1,1), (1,1,1), (1,1,2), (1,1,1), (1,1,2); 1727SELECT 1 FROM (SELECT COUNT(DISTINCT c1) 1728FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; 17291 17301 1731EXPLAIN 1732SELECT 1 FROM (SELECT COUNT(DISTINCT c1) 1733FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; 1734id select_type table type possible_keys key key_len ref rows Extra 17351 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 NULL 17362 DERIVED t1 ref c3,c2 c3 5 const 2 Using where; Using filesort 1737DROP TABLE t1; 1738CREATE TABLE t1 (c1 REAL, c2 REAL, c3 REAL, KEY (c3), KEY (c2, c3)) 1739ENGINE=InnoDB; 1740INSERT INTO t1 VALUES (1,1,1), (1,1,1), (1,1,2), (1,1,1), (1,1,2); 1741SELECT 1 FROM (SELECT COUNT(DISTINCT c1) 1742FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; 17431 17441 1745EXPLAIN 1746SELECT 1 FROM (SELECT COUNT(DISTINCT c1) 1747FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; 1748id select_type table type possible_keys key key_len ref rows Extra 17491 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 NULL 17502 DERIVED t1 ref c3,c2 c3 9 const 2 Using where; Using filesort 1751DROP TABLE t1; 1752CREATE TABLE t1 (c1 DECIMAL(12,2), c2 DECIMAL(12,2), c3 DECIMAL(12,2), 1753KEY (c3), KEY (c2, c3)) 1754ENGINE=InnoDB; 1755INSERT INTO t1 VALUES (1,1,1), (1,1,1), (1,1,2), (1,1,1), (1,1,2); 1756SELECT 1 FROM (SELECT COUNT(DISTINCT c1) 1757FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; 17581 17591 1760EXPLAIN 1761SELECT 1 FROM (SELECT COUNT(DISTINCT c1) 1762FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; 1763id select_type table type possible_keys key key_len ref rows Extra 17641 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 NULL 17652 DERIVED t1 ref c3,c2 c3 7 const 2 Using where; Using filesort 1766DROP TABLE t1; 1767End of 5.1 tests 1768# 1769# Bug#43600: Incorrect type conversion caused wrong result. 1770# 1771CREATE TABLE t1 ( 1772a int NOT NULL 1773) engine= innodb; 1774CREATE TABLE t2 ( 1775a int NOT NULL, 1776b int NOT NULL, 1777filler char(100) DEFAULT NULL, 1778KEY a (a,b) 1779) engine= innodb; 1780insert into t1 values (0),(1),(2),(3),(4); 1781insert into t2 select A.a + 10 *B.a, 1, 'filler' from t1 A, t1 B; 1782explain select * from t1, t2 where t2.a=t1.a and t2.b + 1; 1783id select_type table type possible_keys key key_len ref rows Extra 17841 SIMPLE t1 ALL NULL NULL NULL NULL 5 NULL 17851 SIMPLE t2 ref a a 4 test.t1.a 1 Using index condition 1786select * from t1, t2 where t2.a=t1.a and t2.b + 1; 1787a a b filler 17880 0 1 filler 17891 1 1 filler 17902 2 1 filler 17913 3 1 filler 17924 4 1 filler 1793drop table t1,t2; 1794# End of test case for the bug#43600 1795# 1796# Bug#42643: InnoDB does not support replication of TRUNCATE TABLE 1797# 1798# Check that a TRUNCATE TABLE statement, needing an exclusive meta 1799# data lock, waits for a shared metadata lock owned by a concurrent 1800# transaction. 1801# 1802CREATE TABLE t1 (a INT) ENGINE=InnoDB; 1803INSERT INTO t1 VALUES (1),(2),(3); 1804BEGIN; 1805SELECT * FROM t1 ORDER BY a; 1806a 18071 18082 18093 1810# Connection con1 1811TRUNCATE TABLE t1;; 1812# Connection default 1813SELECT * FROM t1 ORDER BY a; 1814a 18151 18162 18173 1818ROLLBACK; 1819# Connection con1 1820# Reaping TRUNCATE TABLE 1821SELECT * FROM t1; 1822a 1823# Disconnect con1 1824# Connection default 1825DROP TABLE t1; 1826drop table if exists t1, t2, t3; 1827# 1828# BUG#35850: Performance regression in 5.1.23/5.1.24 1829# 1830create table t1(a int); 1831insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 1832create table t2 (a int, b int, pk int, key(a,b), primary key(pk)) engine=innodb; 1833insert into t2 select @a:=A.a+10*(B.a + 10*C.a),@a, @a from t1 A, t1 B, t1 C; 1834# this must use key 'a', not PRIMARY: 1835explain select a from t2 where a=b; 1836id select_type table type possible_keys key key_len ref rows Extra 18371 SIMPLE t2 index NULL a 10 NULL # Using where; Using index 1838drop table t1, t2; 1839# 1840# Bug #40360: Binlog related errors with binlog off 1841# 1842SET SESSION BINLOG_FORMAT=STATEMENT; 1843SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 1844select @@session.sql_log_bin, @@session.binlog_format, @@session.tx_isolation; 1845@@session.sql_log_bin 1 1846@@session.binlog_format STATEMENT 1847@@session.tx_isolation READ-COMMITTED 1848CREATE TABLE t1 ( a INT ) ENGINE=InnoDB; 1849INSERT INTO t1 VALUES(1); 1850DROP TABLE t1; 1851# 1852# Bug#37284 Crash in Field_string::type() 1853# 1854DROP TABLE IF EXISTS t1; 1855CREATE TABLE t1 (a char(50)) ENGINE=InnoDB; 1856CREATE INDEX i1 on t1 (a(3)); 1857SELECT * FROM t1 WHERE a = 'abcde'; 1858a 1859DROP TABLE t1; 1860# 1861# Bug #37742: HA_EXTRA_KEYREAD flag is set when key contains only prefix of 1862# requested column 1863# 1864CREATE TABLE foo (a int, b int, c char(10), 1865PRIMARY KEY (c(3)), 1866KEY b (b) 1867) engine=innodb; 1868CREATE TABLE foo2 (a int, b int, c char(10), 1869PRIMARY KEY (c), 1870KEY b (b) 1871) engine=innodb; 1872CREATE TABLE bar (a int, b int, c char(10), 1873PRIMARY KEY (c(3)), 1874KEY b (b) 1875) engine=myisam; 1876INSERT INTO foo VALUES 1877(1,2,'abcdefghij'), (2,3,''), (3,4,'klmnopqrst'), 1878(4,5,'uvwxyz'), (5,6,'meotnsyglt'), (4,5,'asfdewe'); 1879INSERT INTO bar SELECT * FROM foo; 1880INSERT INTO foo2 SELECT * FROM foo; 1881ANALYZE TABLE bar; 1882ANALYZE TABLE foo; 1883ANALYZE TABLE foo2; 1884EXPLAIN SELECT c FROM bar WHERE b>2;; 1885id 1 1886select_type SIMPLE 1887table bar 1888type ALL 1889possible_keys b 1890key NULL 1891key_len NULL 1892ref NULL 1893rows 6 1894Extra Using where 1895EXPLAIN SELECT c FROM foo WHERE b>2;; 1896id 1 1897select_type SIMPLE 1898table foo 1899type ALL 1900possible_keys b 1901key NULL 1902key_len NULL 1903ref NULL 1904rows 6 1905Extra Using where 1906EXPLAIN SELECT c FROM foo2 WHERE b>2;; 1907id 1 1908select_type SIMPLE 1909table foo2 1910type range 1911possible_keys b 1912key b 1913key_len 5 1914ref NULL 1915rows 5 1916Extra Using where; Using index 1917EXPLAIN SELECT c FROM bar WHERE c>2;; 1918id 1 1919select_type SIMPLE 1920table bar 1921type ALL 1922possible_keys PRIMARY 1923key NULL 1924key_len NULL 1925ref NULL 1926rows 6 1927Extra Using where 1928EXPLAIN SELECT c FROM foo WHERE c>2;; 1929id 1 1930select_type SIMPLE 1931table foo 1932type ALL 1933possible_keys PRIMARY 1934key NULL 1935key_len NULL 1936ref NULL 1937rows 6 1938Extra Using where 1939EXPLAIN SELECT c FROM foo2 WHERE c>2;; 1940id 1 1941select_type SIMPLE 1942table foo2 1943type index 1944possible_keys PRIMARY 1945key b 1946key_len 5 1947ref NULL 1948rows 6 1949Extra Using where; Using index 1950DROP TABLE foo, bar, foo2; 1951# 1952# Bug#41348: INSERT INTO tbl SELECT * FROM temp_tbl overwrites locking type of temp table 1953# 1954DROP TABLE IF EXISTS t1,t3,t2; 1955DROP FUNCTION IF EXISTS f1; 1956CREATE FUNCTION f1() RETURNS VARCHAR(250) 1957BEGIN 1958return 'hhhhhhh' ; 1959END| 1960CREATE TABLE t1 (a VARCHAR(20), b VARCHAR(20), c VARCHAR(20)) ENGINE=INNODB; 1961BEGIN WORK; 1962CREATE TEMPORARY TABLE t2 (a VARCHAR(20), b VARCHAR(20), c varchar(20)) ENGINE=INNODB; 1963CREATE TEMPORARY TABLE t3 LIKE t2; 1964INSERT INTO t1 VALUES ('a','b',NULL),('c','d',NULL),('e','f',NULL); 1965SET @stmt := CONCAT('INSERT INTO t2 SELECT tbl.a, tbl.b, f1()',' FROM t1 tbl'); 1966PREPARE stmt1 FROM @stmt; 1967SET @stmt := CONCAT('INSERT INTO t3', ' SELECT * FROM t2'); 1968PREPARE stmt3 FROM @stmt; 1969EXECUTE stmt1; 1970COMMIT; 1971DEALLOCATE PREPARE stmt1; 1972DEALLOCATE PREPARE stmt3; 1973DROP TABLE t1,t3,t2; 1974DROP FUNCTION f1; 1975# 1976# Bug#37016: TRUNCATE TABLE removes some rows but not all 1977# 1978DROP TABLE IF EXISTS t1,t2; 1979CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB; 1980CREATE TABLE t2 (id INT PRIMARY KEY, 1981t1_id INT, INDEX par_ind (t1_id), 1982FOREIGN KEY (t1_id) REFERENCES t1(id)) ENGINE=INNODB; 1983INSERT INTO t1 VALUES (1),(2); 1984INSERT INTO t2 VALUES (3,2); 1985SET AUTOCOMMIT = 0; 1986START TRANSACTION; 1987TRUNCATE TABLE t1; 1988ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `test`.`t1` (`id`)) 1989SELECT * FROM t1; 1990id 19911 19922 1993COMMIT; 1994SELECT * FROM t1; 1995id 19961 19972 1998START TRANSACTION; 1999TRUNCATE TABLE t1; 2000ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `test`.`t1` (`id`)) 2001SELECT * FROM t1; 2002id 20031 20042 2005ROLLBACK; 2006SELECT * FROM t1; 2007id 20081 20092 2010SET AUTOCOMMIT = 1; 2011START TRANSACTION; 2012SELECT * FROM t1; 2013id 20141 20152 2016COMMIT; 2017TRUNCATE TABLE t1; 2018ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `test`.`t1` (`id`)) 2019SELECT * FROM t1; 2020id 20211 20222 2023DELETE FROM t2 WHERE id = 3; 2024START TRANSACTION; 2025SELECT * FROM t1; 2026id 20271 20282 2029TRUNCATE TABLE t1; 2030ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `test`.`t1` (`id`)) 2031ROLLBACK; 2032SELECT * FROM t1; 2033id 20341 20352 2036TRUNCATE TABLE t2; 2037DROP TABLE t2; 2038DROP TABLE t1; 2039# 2040# Bug#40127 Multiple table DELETE IGNORE hangs on foreign key constraint violation on 5.0 2041# 2042CREATE TABLE t1 ( 2043id INT UNSIGNED NOT NULL AUTO_INCREMENT, 2044PRIMARY KEY (id) 2045) ENGINE=InnoDB; 2046CREATE TABLE t2 ( 2047id INT UNSIGNED NOT NULL AUTO_INCREMENT, 2048aid INT UNSIGNED NOT NULL, 2049PRIMARY KEY (id), 2050FOREIGN KEY (aid) REFERENCES t1 (id) 2051) ENGINE=InnoDB; 2052CREATE TABLE t3 ( 2053bid INT UNSIGNED NOT NULL, 2054FOREIGN KEY (bid) REFERENCES t2 (id) 2055) ENGINE=InnoDB; 2056CREATE TABLE t4 ( 2057a INT 2058) ENGINE=InnoDB; 2059CREATE TABLE t5 ( 2060a INT 2061) ENGINE=InnoDB; 2062INSERT INTO t1 (id) VALUES (1); 2063INSERT INTO t2 (id, aid) VALUES (1, 1),(2,1),(3,1),(4,1); 2064INSERT INTO t3 (bid) VALUES (1); 2065INSERT INTO t4 VALUES (1),(2),(3),(4),(5); 2066INSERT INTO t5 VALUES (1); 2067DELETE t5 FROM t4 LEFT JOIN t5 ON t4.a= t5.a; 2068DELETE t2, t1 FROM t2 INNER JOIN t1 ON (t2.aid = t1.id) WHERE t2.id = 1; 2069ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t3`, CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`bid`) REFERENCES `t2` (`id`)) 2070DELETE t2, t1 FROM t2 INNER JOIN t1 ON (t2.aid = t1.id) WHERE t2.id = 1; 2071ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t3`, CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`bid`) REFERENCES `t2` (`id`)) 2072DELETE IGNORE t2, t1 FROM t2 INNER JOIN t1 ON (t2.aid = t1.id) WHERE t2.id = 1; 2073DROP TABLE t3; 2074DROP TABLE t2; 2075DROP TABLE t1; 2076DROP TABLES t4,t5; 2077# Bug#40127 Multiple table DELETE IGNORE hangs on foreign key constraint violation on 5.0 2078# Testing for any side effects of IGNORE on AFTER DELETE triggers used with 2079# transactional tables. 2080# 2081CREATE TABLE t1 (i INT NOT NULL PRIMARY KEY) ENGINE=InnoDB; 2082CREATE TABLE t2 (a VARCHAR(100)) ENGINE=InnoDB; 2083CREATE TABLE t3 (i INT NOT NULL PRIMARY KEY) ENGINE=InnoDB; 2084CREATE TABLE t4 (i INT NOT NULL PRIMARY KEY, t1i INT, 2085FOREIGN KEY (t1i) REFERENCES t1(i)) 2086ENGINE=InnoDB; 2087CREATE TRIGGER trg AFTER DELETE ON t1 FOR EACH ROW 2088BEGIN 2089SET @b:='EXECUTED TRIGGER'; 2090INSERT INTO t2 VALUES (@b); 2091SET @a:= error_happens_here; 2092END|| 2093SET @b:=""; 2094SET @a:=""; 2095INSERT INTO t1 VALUES (1),(2),(3),(4); 2096INSERT INTO t3 SELECT * FROM t1; 2097** An error in a trigger causes rollback of the statement. 2098DELETE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i; 2099ERROR 42S22: Unknown column 'error_happens_here' in 'field list' 2100SELECT @a,@b; 2101@a @b 2102 EXECUTED TRIGGER 2103SELECT * FROM t2; 2104a 2105SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i; 2106i i 21071 1 21082 2 21093 3 21104 4 2111** Same happens with the IGNORE option 2112DELETE IGNORE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i; 2113ERROR 42S22: Unknown column 'error_happens_here' in 'field list' 2114SELECT * FROM t2; 2115a 2116SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i; 2117i i 21181 1 21192 2 21203 3 21214 4 2122** 2123** The following is an attempt to demonstrate 2124** error handling inside a row iteration. 2125** 2126DROP TRIGGER trg; 2127DELETE FROM t1; 2128DELETE FROM t2; 2129DELETE FROM t3; 2130INSERT INTO t1 VALUES (1),(2),(3),(4); 2131INSERT INTO t3 VALUES (1),(2),(3),(4); 2132INSERT INTO t4 VALUES (3,3),(4,4); 2133CREATE TRIGGER trg AFTER DELETE ON t1 FOR EACH ROW 2134BEGIN 2135SET @b:= CONCAT('EXECUTED TRIGGER FOR ROW ',CAST(OLD.i AS CHAR)); 2136INSERT INTO t2 VALUES (@b); 2137END|| 2138** DELETE is prevented by foreign key constrains but errors are silenced. 2139** The AFTER trigger isn't fired. 2140DELETE IGNORE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i; 2141** Tables are modified by best effort: 2142SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i; 2143i i 21443 3 21454 4 2146** The AFTER trigger was only executed on successful rows: 2147SELECT * FROM t2; 2148a 2149EXECUTED TRIGGER FOR ROW 1 2150EXECUTED TRIGGER FOR ROW 2 2151DROP TRIGGER trg; 2152** 2153** Induce an error midway through an AFTER-trigger 2154** 2155DELETE FROM t4; 2156DELETE FROM t1; 2157DELETE FROM t3; 2158INSERT INTO t1 VALUES (1),(2),(3),(4); 2159INSERT INTO t3 VALUES (1),(2),(3),(4); 2160CREATE TRIGGER trg AFTER DELETE ON t1 FOR EACH ROW 2161BEGIN 2162SET @a:= @a+1; 2163IF @a > 2 THEN 2164INSERT INTO t4 VALUES (5,5); 2165END IF; 2166END|| 2167SET @a:=0; 2168** Errors in the trigger causes the statement to abort. 2169DELETE IGNORE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i; 2170ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t4`, CONSTRAINT `t4_ibfk_1` FOREIGN KEY (`t1i`) REFERENCES `t1` (`i`)) 2171SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i; 2172i i 21731 1 21742 2 21753 3 21764 4 2177SELECT * FROM t4; 2178i t1i 2179DROP TRIGGER trg; 2180DROP TABLE t4; 2181DROP TABLE t1; 2182DROP TABLE t2; 2183DROP TABLE t3; 2184# 2185# Bug#43580: Issue with Innodb on multi-table update 2186# 2187CREATE TABLE t1 (a INT, b INT, KEY (a)) ENGINE = INNODB; 2188CREATE TABLE t2 (a INT KEY, b INT, KEY (b)) ENGINE = INNODB; 2189CREATE TABLE t3 (a INT, b INT KEY, KEY (a)) ENGINE = INNODB; 2190CREATE TABLE t4 (a INT KEY, b INT, KEY (b)) ENGINE = INNODB; 2191INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6); 2192INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); 2193INSERT INTO t3 VALUES (1, 101), (2, 102), (3, 103), (4, 104), (5, 105), (6, 106); 2194INSERT INTO t4 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); 2195UPDATE t1, t2 SET t1.a = t1.a + 100, t2.b = t1.a + 10 2196WHERE t1.a BETWEEN 2 AND 4 AND t2.a = t1.b; 2197SELECT * FROM t2; 2198a b 21991 1 22002 12 22013 13 22024 14 22035 5 2204UPDATE t3, t4 SET t3.a = t3.a + 100, t4.b = t3.a + 10 2205WHERE t3.a BETWEEN 2 AND 4 AND t4.a = t3.b - 100; 2206SELECT * FROM t4; 2207a b 22081 1 22092 12 22103 13 22114 14 22125 5 2213DROP TABLE t1, t2, t3, t4; 2214# 2215# Bug#44886: SIGSEGV in test_if_skip_sort_order() - 2216# uninitialized variable used as subscript 2217# 2218CREATE TABLE t1 (a INT, b INT, c INT, d INT, PRIMARY KEY (b), KEY (a,c)) 2219ENGINE=InnoDB; 2220INSERT INTO t1 VALUES (1,1,1,0); 2221CREATE TABLE t2 (a INT, b INT, e INT, KEY (e)) ENGINE=InnoDB; 2222INSERT INTO t2 VALUES (1,1,2); 2223CREATE TABLE t3 (a INT, b INT) ENGINE=MyISAM; 2224INSERT INTO t3 VALUES (1, 1); 2225SELECT * FROM t1, t2, t3 2226WHERE t1.a = t3.a AND (t1.b = t3.b OR t1.d) AND t2.b = t1.b AND t2.e = 2 2227GROUP BY t1.b; 2228a b c d a b e a b 22291 1 1 0 1 1 2 1 1 2230DROP TABLE t1, t2, t3; 2231# 2232# Bug #45828: Optimizer won't use partial primary key if another 2233# index can prevent filesort 2234# 2235CREATE TABLE `t1` ( 2236c1 int NOT NULL, 2237c2 int NOT NULL, 2238c3 int NOT NULL, 2239PRIMARY KEY (c1,c2), 2240KEY (c3) 2241) ENGINE=InnoDB; 2242INSERT INTO t1 VALUES (5,2,1246276747); 2243INSERT INTO t1 VALUES (2,1,1246281721); 2244INSERT INTO t1 VALUES (7,3,1246281756); 2245INSERT INTO t1 VALUES (4,2,1246282139); 2246INSERT INTO t1 VALUES (3,1,1246282230); 2247INSERT INTO t1 VALUES (1,0,1246282712); 2248INSERT INTO t1 VALUES (8,3,1246282765); 2249INSERT INTO t1 SELECT c1+10,c2+10,c3+10 FROM t1; 2250INSERT INTO t1 SELECT c1+100,c2+100,c3+100 from t1; 2251INSERT INTO t1 SELECT c1+1000,c2+1000,c3+1000 from t1; 2252INSERT INTO t1 SELECT c1+10000,c2+10000,c3+10000 from t1; 2253INSERT INTO t1 SELECT c1+100000,c2+100000,c3+100000 from t1; 2254INSERT INTO t1 SELECT c1+1000000,c2+1000000,c3+1000000 from t1; 2255SELECT * FROM t1 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3; 2256c1 c2 c3 2257EXPLAIN SELECT * FROM t1 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3; 2258id select_type table type possible_keys key key_len ref rows Extra 22591 SIMPLE t1 ref PRIMARY,c3 PRIMARY 4 const 1 Using where; Using filesort 2260EXPLAIN SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3; 2261id select_type table type possible_keys key key_len ref rows Extra 22621 SIMPLE t1 ref PRIMARY PRIMARY 4 const 1 Using where; Using filesort 2263CREATE TABLE t2 ( 2264c1 int NOT NULL, 2265c2 int NOT NULL, 2266c3 int NOT NULL, 2267KEY (c1,c2), 2268KEY (c3) 2269) ENGINE=InnoDB; 2270explain SELECT * FROM t2 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3; 2271id select_type table type possible_keys key key_len ref rows Extra 22721 SIMPLE t2 ref c1,c3 c1 4 const 1 Using where; Using filesort 2273DROP TABLE t1,t2; 2274# 2275# 36259: Optimizing with ORDER BY 2276# 2277CREATE TABLE t1 ( 2278a INT NOT NULL AUTO_INCREMENT, 2279b INT NOT NULL, 2280c INT NOT NULL, 2281d VARCHAR(5), 2282e INT NOT NULL, 2283PRIMARY KEY (a), KEY i2 (b,c,d) 2284) ENGINE=InnoDB; 2285INSERT INTO t1 (b,c,d,e) VALUES (1,1,'a',1), (2,2,'b',2); 2286INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1; 2287INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1; 2288INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1; 2289INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1; 2290INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1; 2291INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1; 2292ANALYZE TABLE t1; 2293EXPLAIN SELECT * FROM t1 WHERE b=1 AND c=1 ORDER BY a; 2294id select_type table type possible_keys key key_len ref rows Extra 22951 SIMPLE t1 ref i2 i2 8 const,const 1 Using where; Using filesort 2296EXPLAIN SELECT * FROM t1 FORCE INDEX(i2) WHERE b=1 and c=1 ORDER BY a; 2297id select_type table type possible_keys key key_len ref rows Extra 22981 SIMPLE t1 ref i2 i2 8 const,const 1 Using where; Using filesort 2299EXPLAIN SELECT * FROM t1 FORCE INDEX(PRIMARY) WHERE b=1 AND c=1 ORDER BY a; 2300id select_type table type possible_keys key key_len ref rows Extra 23011 SIMPLE t1 index NULL PRIMARY 4 NULL {checked} Using where 2302DROP TABLE t1; 2303# 2304# Bug #47963: Wrong results when index is used 2305# 2306CREATE TABLE t1( 2307a VARCHAR(5) NOT NULL, 2308b VARCHAR(5) NOT NULL, 2309c DATETIME NOT NULL, 2310KEY (c) 2311) ENGINE=InnoDB; 2312INSERT INTO t1 VALUES('TEST', 'TEST', '2009-10-09 00:00:00'); 2313SELECT * FROM t1 WHERE a = 'TEST' AND 2314c >= '2009-10-09 00:00:00' AND c <= '2009-10-09 00:00:00'; 2315a b c 2316TEST TEST 2009-10-09 00:00:00 2317SELECT * FROM t1 WHERE a = 'TEST' AND 2318c >= '2009-10-09 00:00:00.0' AND c <= '2009-10-09 00:00:00.0'; 2319a b c 2320TEST TEST 2009-10-09 00:00:00 2321SELECT * FROM t1 WHERE a = 'TEST' AND 2322c >= '2009-10-09 00:00:00.0' AND c <= '2009-10-09 00:00:00'; 2323a b c 2324TEST TEST 2009-10-09 00:00:00 2325SELECT * FROM t1 WHERE a = 'TEST' AND 2326c >= '2009-10-09 00:00:00' AND c <= '2009-10-09 00:00:00.0'; 2327a b c 2328TEST TEST 2009-10-09 00:00:00 2329SELECT * FROM t1 WHERE a = 'TEST' AND 2330c >= '2009-10-09 00:00:00.000' AND c <= '2009-10-09 00:00:00.000'; 2331a b c 2332TEST TEST 2009-10-09 00:00:00 2333SELECT * FROM t1 WHERE a = 'TEST' AND 2334c >= '2009-10-09 00:00:00.00' AND c <= '2009-10-09 00:00:00.001'; 2335a b c 2336TEST TEST 2009-10-09 00:00:00 2337SELECT * FROM t1 WHERE a = 'TEST' AND 2338c >= '2009-10-09 00:00:00.001' AND c <= '2009-10-09 00:00:00.00'; 2339a b c 2340EXPLAIN SELECT * FROM t1 WHERE a = 'TEST' AND 2341c >= '2009-10-09 00:00:00.001' AND c <= '2009-10-09 00:00:00.00'; 2342id select_type table type possible_keys key key_len ref rows Extra 23431 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 2344DROP TABLE t1; 2345# 2346# Bug #46175: NULL read_view and consistent read assertion 2347# 2348CREATE TABLE t1(a CHAR(13),KEY(a)) ENGINE=innodb; 2349CREATE TABLE t2(b DATETIME,KEY(b)) ENGINE=innodb; 2350INSERT INTO t1 VALUES (),(); 2351INSERT INTO t2 VALUES (),(); 2352CREATE OR REPLACE VIEW v1 AS SELECT 1 FROM t2 2353WHERE b =(SELECT a FROM t1 LIMIT 1); 2354CREATE PROCEDURE p1(num INT) 2355BEGIN 2356DECLARE i INT DEFAULT 0; 2357REPEAT 2358SHOW CREATE VIEW v1; 2359SET i:=i+1; 2360UNTIL i>num END REPEAT; 2361END| 2362# Should not crash 2363# Should not crash 2364DROP PROCEDURE p1; 2365DROP VIEW v1; 2366DROP TABLE t1,t2; 2367# 2368# Bug #49324: more valgrind errors in test_if_skip_sort_order 2369# 2370CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=innodb ; 2371# should not cause valgrind warnings 2372SELECT 1 FROM t1 JOIN t1 a USING(a) GROUP BY t1.a,t1.a; 23731 2374DROP TABLE t1; 2375# 2376# Bug#50843: Filesort used instead of clustered index led to 2377# performance degradation. 2378# 2379create table t1(f1 int not null primary key, f2 int) engine=innodb; 2380create table t2(f1 int not null, key (f1)) engine=innodb; 2381insert into t1 values (1,1),(2,2),(3,3); 2382insert into t2 values (1),(2),(3); 2383analyze table t1; 2384analyze table t2; 2385explain select t1.* from t1 left join t2 using(f1) group by t1.f1; 2386id select_type table type possible_keys key key_len ref rows Extra 23871 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 3 NULL 23881 SIMPLE t2 ref f1 f1 4 test.t1.f1 1 Using index 2389drop table t1,t2; 2390# 2391# 2392# Bug #39653: find_shortest_key in sql_select.cc does not consider 2393# clustered primary keys 2394# 2395CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT, d INT, e INT, f INT, 2396KEY (b,c)) ENGINE=INNODB; 2397INSERT INTO t1 VALUES (1,1,1,1,1,1), (2,2,2,2,2,2), (3,3,3,3,3,3), 2398(4,4,4,4,4,4), (5,5,5,5,5,5), (6,6,6,6,6,6), 2399(7,7,7,7,7,7), (8,8,8,8,8,8), (9,9,9,9,9,9), 2400(11,11,11,11,11,11); 2401ANALYZE TABLE t1; 2402Table Op Msg_type Msg_text 2403test.t1 analyze status OK 2404EXPLAIN SELECT COUNT(*) FROM t1; 2405id 1 2406select_type SIMPLE 2407table t1 2408type index 2409possible_keys NULL 2410key b 2411key_len 10 2412ref NULL 2413rows 10 2414Extra Using index 2415DROP TABLE t1; 2416# 2417# Bug #49838: DROP INDEX and ADD UNIQUE INDEX for same index may 2418# corrupt definition at engine 2419# 2420CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, KEY k (a,b)) 2421ENGINE=InnoDB; 2422ALTER TABLE t1 DROP INDEX k, ADD UNIQUE INDEX k (a,b); 2423SHOW INDEXES FROM t1;; 2424Table t1 2425Non_unique 0 2426Key_name k 2427Seq_in_index 1 2428Column_name a 2429Collation A 2430Cardinality 0 2431Sub_part NULL 2432Packed NULL 2433Null 2434Index_type BTREE 2435Comment 2436Index_comment 2437Table t1 2438Non_unique 0 2439Key_name k 2440Seq_in_index 2 2441Column_name b 2442Collation A 2443Cardinality 0 2444Sub_part NULL 2445Packed NULL 2446Null 2447Index_type BTREE 2448Comment 2449Index_comment 2450DROP TABLE t1; 2451# 2452# Bug #47453: InnoDB incorrectly changes TIMESTAMP columns when 2453# JOINed during an UPDATE 2454# 2455CREATE TABLE t1 (d INT) ENGINE=InnoDB; 2456CREATE TABLE t2 (a INT, b INT, 2457c TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 2458ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB; 2459# set up our data elements 2460INSERT INTO t1 (d) VALUES (1); 2461INSERT INTO t2 (a,b) VALUES (1,1); 2462SELECT SECOND(c) INTO @bug47453 FROM t2; 2463SELECT SECOND(c)-@bug47453 FROM t1 JOIN t2 ON d=a; 2464SECOND(c)-@bug47453 24650 2466UPDATE t1 JOIN t2 ON d=a SET b=1 WHERE a=1; 2467SELECT SECOND(c)-@bug47453 FROM t1 JOIN t2 ON d=a; 2468SECOND(c)-@bug47453 24690 2470SELECT SLEEP(1); 2471SLEEP(1) 24720 2473UPDATE t1 JOIN t2 ON d=a SET b=1 WHERE a=1; 2474# should be 0 2475SELECT SECOND(c)-@bug47453 FROM t1 JOIN t2 ON d=a; 2476SECOND(c)-@bug47453 24770 2478DROP TABLE t1, t2; 2479# 2480# Bug #53334: wrong result for outer join with impossible ON condition 2481# (see the same test case for MyISAM in join.test) 2482# 2483CREATE TABLE t1 (id INT PRIMARY KEY); 2484CREATE TABLE t2 (id INT); 2485INSERT INTO t1 VALUES (75); 2486INSERT INTO t1 VALUES (79); 2487INSERT INTO t1 VALUES (78); 2488INSERT INTO t1 VALUES (77); 2489REPLACE INTO t1 VALUES (76); 2490REPLACE INTO t1 VALUES (76); 2491INSERT INTO t1 VALUES (104); 2492INSERT INTO t1 VALUES (103); 2493INSERT INTO t1 VALUES (102); 2494INSERT INTO t1 VALUES (101); 2495INSERT INTO t1 VALUES (105); 2496INSERT INTO t1 VALUES (106); 2497INSERT INTO t1 VALUES (107); 2498INSERT INTO t2 VALUES (107),(75),(1000); 2499SELECT t1.id,t2.id FROM t2 LEFT JOIN t1 ON t1.id>=74 AND t1.id<=0 2500WHERE t2.id=75 AND t1.id IS NULL; 2501id id 2502NULL 75 2503EXPLAIN SELECT t1.id,t2.id FROM t2 LEFT JOIN t1 ON t1.id>=74 AND t1.id<=0 2504WHERE t2.id=75 AND t1.id IS NULL; 2505id select_type table type possible_keys key key_len ref rows Extra 25061 SIMPLE t1 const PRIMARY NULL NULL NULL 1 Impossible ON condition 25071 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where 2508DROP TABLE t1,t2; 2509# 2510# Bug#38999 valgrind warnings for update statement in function compare_record() 2511# 2512CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; 2513CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB; 2514INSERT INTO t1 values (1),(2),(3),(4),(5); 2515INSERT INTO t2 values (1); 2516SELECT * FROM t1 WHERE a = 2; 2517a 25182 2519UPDATE t1,t2 SET t1.a = t1.a + 100 WHERE t1.a = 1; 2520DROP TABLE t1,t2; 2521# 2522# Bug #53830: !table || (!table->read_set || bitmap_is_set(table->read_set, field_index)) 2523# 2524CREATE TABLE t1 (a INT, b INT, c INT, d INT, 2525PRIMARY KEY(a,b,c), KEY(b,d)) 2526ENGINE=InnoDB; 2527INSERT INTO t1 VALUES (0, 77, 1, 3); 2528UPDATE t1 SET d = 0 WHERE b = 77 AND c = 25; 2529DROP TABLE t1; 2530# 2531# Bug#50389 Using intersect does not return all rows 2532# 2533CREATE TABLE t1 ( 2534f1 INT(10) NOT NULL, 2535f2 INT(10), 2536f3 INT(10), 2537f4 TINYINT(4), 2538f5 VARCHAR(50), 2539PRIMARY KEY (f1), 2540KEY idx1 (f2,f5,f4), 2541KEY idx2 (f2,f4) 2542) ENGINE=InnoDB; 2543LOAD DATA INFILE '../../std_data/intersect-bug50389.tsv' INTO TABLE t1; 2544ANALYZE TABLE t1; 2545SELECT * FROM t1 WHERE f1 IN 2546(3305028,3353871,3772880,3346860,4228206,3336022, 25473470988,3305175,3329875,3817277,3856380,3796193, 25483784744,4180925,4559596,3963734,3856391,4494153) 2549AND f5 = 'abcdefghijklmnopwrst' AND f2 = 1221457 AND f4 = 0 ; 2550f1 f2 f3 f4 f5 25513305175 1221457 0 0 abcdefghijklmnopwrst 25523329875 1221457 1382427 0 abcdefghijklmnopwrst 25533336022 1221457 0 0 abcdefghijklmnopwrst 25543346860 1221457 0 0 abcdefghijklmnopwrst 25553772880 1221457 0 0 abcdefghijklmnopwrst 25563784744 1221457 1382427 0 abcdefghijklmnopwrst 25573796193 1221457 0 0 abcdefghijklmnopwrst 25584228206 1221457 0 0 abcdefghijklmnopwrst 25594494153 1221457 0 0 abcdefghijklmnopwrst 25604559596 1221457 0 0 abcdefghijklmnopwrst 2561EXPLAIN SELECT * FROM t1 WHERE f1 IN 2562(3305028,3353871,3772880,3346860,4228206,3336022, 25633470988,3305175,3329875,3817277,3856380,3796193, 25643784744,4180925,4559596,3963734,3856391,4494153) 2565AND f5 = 'abcdefghijklmnopwrst' AND f2 = 1221457 AND f4 = 0 ; 2566id select_type table type possible_keys key key_len ref rows Extra 25671 SIMPLE t1 range PRIMARY,idx1,idx2 idx1 64 NULL 18 Using index condition 2568DROP TABLE t1; 2569# 2570# Bug#51431 Wrong sort order after import of dump file 2571# 2572CREATE TABLE t1 ( 2573f1 INT(11) NOT NULL, 2574f2 int(11) NOT NULL, 2575f3 int(11) NOT NULL, 2576f4 tinyint(1) NOT NULL, 2577PRIMARY KEY (f1), 2578UNIQUE KEY (f2, f3), 2579KEY (f4) 2580) ENGINE=InnoDB STATS_PERSISTENT=0; 2581INSERT INTO t1 VALUES 2582(1,1,991,1), (2,1,992,1), (3,1,993,1), (4,1,994,1), (5,1,995,1), 2583(6,1,996,1), (7,1,997,1), (8,1,998,1), (10,1,999,1), (11,1,9910,1), 2584(16,1,9911,1), (17,1,9912,1), (18,1,9913,1), (19,1,9914,1), (20,1,9915,1), 2585(21,1,9916,1), (22,1,9917,1), (23,1,9918,1), (24,1,9919,1), (25,1,9920,1), 2586(26,1,9921,1), (27,1,9922,1); 2587FLUSH TABLES; 2588SELECT * FROM t1 WHERE f2 = 1 AND f4 = TRUE 2589ORDER BY f1 DESC LIMIT 5; 2590f1 f2 f3 f4 259127 1 9922 1 259226 1 9921 1 259325 1 9920 1 259424 1 9919 1 259523 1 9918 1 2596EXPLAIN SELECT * FROM t1 WHERE f2 = 1 AND f4 = TRUE 2597ORDER BY f1 DESC LIMIT 5; 2598id select_type table type possible_keys key key_len ref rows Extra 25991 SIMPLE t1 range f2,f4 f4 1 NULL 22 Using index condition; Using where 2600DROP TABLE t1; 2601# 2602# Bug#54117 crash in thr_multi_unlock, temporary table 2603# 2604CREATE TEMPORARY TABLE t1(a INT) ENGINE = InnoDB; 2605LOCK TABLES t1 READ; 2606ALTER TABLE t1 COMMENT 'test'; 2607UNLOCK TABLES; 2608DROP TABLE t1; 2609# 2610# Bug#55656: mysqldump can be slower after bug #39653 fix 2611# 2612CREATE TABLE t1 (a INT , b INT, c INT, d INT, 2613KEY (b), PRIMARY KEY (a,b)) ENGINE=INNODB STATS_PERSISTENT=0; 2614INSERT INTO t1 VALUES (1,1,1,1), (2,2,2,2), (3,3,3,3); 2615EXPLAIN SELECT COUNT(*) FROM t1; 2616id 1 2617select_type SIMPLE 2618table t1 2619type index 2620possible_keys NULL 2621key b 2622key_len 4 2623ref NULL 2624rows 3 2625Extra Using index 2626DROP INDEX b ON t1; 2627CREATE INDEX b ON t1(a,b); 2628EXPLAIN SELECT COUNT(*) FROM t1; 2629id 1 2630select_type SIMPLE 2631table t1 2632type index 2633possible_keys NULL 2634key b 2635key_len 8 2636ref NULL 2637rows 3 2638Extra Using index 2639DROP INDEX b ON t1; 2640CREATE INDEX b ON t1(a,b,c); 2641EXPLAIN SELECT COUNT(*) FROM t1; 2642id 1 2643select_type SIMPLE 2644table t1 2645type index 2646possible_keys NULL 2647key b 2648key_len 13 2649ref NULL 2650rows 3 2651Extra Using index 2652DROP INDEX b ON t1; 2653CREATE INDEX b ON t1(a,b,c,d); 2654EXPLAIN SELECT COUNT(*) FROM t1; 2655id 1 2656select_type SIMPLE 2657table t1 2658type index 2659possible_keys NULL 2660key PRIMARY 2661key_len 8 2662ref NULL 2663rows 3 2664Extra Using index 2665DROP TABLE t1; 2666# 2667# Bug#55826: create table .. select crashes with when KILL_BAD_DATA 2668# is returned 2669# 2670CREATE TABLE t1(a INT) ENGINE=innodb; 2671INSERT INTO t1 VALUES (0); 2672SET SQL_MODE='STRICT_ALL_TABLES'; 2673CREATE TABLE t2 2674SELECT LEAST((SELECT '' FROM t1),NOW()) FROM `t1`; 2675ERROR 22007: Incorrect datetime value: '' for column 'NOW()' at row 2 2676DROP TABLE t1; 2677SET SQL_MODE=DEFAULT; 2678# 2679# Bug#56862 Moved to innodb_16k.test 2680# 2681# 2682# Test for bug #39932 "create table fails if column for FK is in different 2683# case than in corr index". 2684# 2685drop tables if exists t1, t2; 2686create table t1 (pk int primary key) engine=InnoDB; 2687create table t2 (fk int, key x (fk), 2688constraint x foreign key (FK) references t1 (PK)) engine=InnoDB; 2689show create table t2; 2690Table Create Table 2691t2 CREATE TABLE `t2` ( 2692 `fk` int(11) DEFAULT NULL, 2693 KEY `x` (`fk`), 2694 CONSTRAINT `x` FOREIGN KEY (`fk`) REFERENCES `t1` (`pk`) 2695) ENGINE=InnoDB DEFAULT CHARSET=latin1 2696drop table t2, t1; 2697# 2698# Test for bug #11762012 - "54553: INNODB ASSERTS IN HA_INNOBASE:: 2699# UPDATE_ROW, TEMPORARY TABLE, TABLE LOCK". 2700# 2701DROP TABLE IF EXISTS t1; 2702CREATE TEMPORARY TABLE t1 (c int) ENGINE = InnoDB; 2703INSERT INTO t1 VALUES (1); 2704LOCK TABLES t1 READ; 2705# Even though temporary table was locked for READ we 2706# still allow writes to it to be compatible with MyISAM. 2707# This is possible since due to fact that temporary tables 2708# are specific to connection and therefore locking for them 2709# is irrelevant. 2710UPDATE t1 SET c = 5; 2711UNLOCK TABLES; 2712DROP TEMPORARY TABLE t1; 2713# End of 5.1 tests 2714# 2715# Bug#49604 "6.0 processing compound WHERE clause incorrectly 2716# with Innodb - extra rows" 2717# 2718CREATE TABLE t1 ( 2719c1 INT NOT NULL, 2720c2 INT, 2721PRIMARY KEY (c1), 2722KEY k1 (c2) 2723) ENGINE=InnoDB; 2724INSERT INTO t1 VALUES (12,1); 2725INSERT INTO t1 VALUES (15,1); 2726INSERT INTO t1 VALUES (16,1); 2727INSERT INTO t1 VALUES (22,1); 2728INSERT INTO t1 VALUES (20,2); 2729CREATE TABLE t2 ( 2730c1 INT NOT NULL, 2731c2 INT, 2732PRIMARY KEY (c1) 2733) ENGINE=InnoDB; 2734INSERT INTO t2 VALUES (1,2); 2735INSERT INTO t2 VALUES (2,9); 2736SELECT STRAIGHT_JOIN t2.c2, t1.c2, t2.c1 2737FROM t1 JOIN t2 ON t1.c2 = t2.c1 2738WHERE t2.c1 IN (2, 1, 6) OR t2.c1 NOT IN (1); 2739c2 c2 c1 27402 1 1 27412 1 1 27422 1 1 27432 1 1 27449 2 2 2745DROP TABLE t1, t2; 2746# 2747# Bug#44613 SELECT statement inside FUNCTION takes a shared lock 2748# 2749DROP TABLE IF EXISTS t1; 2750DROP FUNCTION IF EXISTS f1; 2751CREATE TABLE t1(x INT PRIMARY KEY, y INT) ENGINE=innodb; 2752INSERT INTO t1 VALUES (1, 0), (2, 0); 2753CREATE FUNCTION f1(z INT) RETURNS INT READS SQL DATA 2754RETURN (SELECT x FROM t1 WHERE x = z); 2755# Connection default 2756START TRANSACTION; 2757SELECT f1(1); 2758f1(1) 27591 2760# Connection con2 2761START TRANSACTION; 2762SELECT f1(1); 2763f1(1) 27641 2765UPDATE t1 SET y = 1 WHERE x = 1; 2766COMMIT; 2767# Connection default 2768COMMIT; 2769DROP TABLE t1; 2770DROP FUNCTION f1; 2771# 2772# Bug#42744: Crash when using a join buffer to join a table with a blob 2773# column and an additional column used for duplicate elimination. 2774# 2775CREATE TABLE t1 (a tinyblob) ENGINE=InnoDB; 2776CREATE TABLE t2 (a int PRIMARY KEY, b tinyblob) ENGINE=InnoDB; 2777INSERT INTO t1 VALUES ('1'), (NULL); 2778INSERT INTO t2 VALUES (1, '1'); 2779EXPLAIN 2780SELECT t2.b FROM t1,t2 WHERE t1.a IN (SELECT 1 FROM t2); 2781id select_type table type possible_keys key key_len ref rows Extra 27821 SIMPLE t2 ALL NULL NULL NULL NULL 1 Start temporary 27831 SIMPLE t2 index NULL PRIMARY 4 NULL 1 Using index; Using join buffer (Block Nested Loop) 27841 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (Block Nested Loop) 2785SELECT t2.b FROM t1,t2 WHERE t1.a IN (SELECT 1 FROM t2); 2786b 27871 2788DROP TABLE t1,t2; 2789# 2790# Bug#48093: 6.0 Server not processing equivalent IN clauses properly 2791# with Innodb tables 2792# 2793CREATE TABLE t1 ( 2794i int(11) DEFAULT NULL, 2795v1 varchar(1) DEFAULT NULL, 2796v2 varchar(20) DEFAULT NULL, 2797KEY i (i), 2798KEY v (v1,i) 2799) ENGINE=innodb; 2800INSERT INTO t1 VALUES (1,'f','no'); 2801INSERT INTO t1 VALUES (2,'u','yes-u'); 2802INSERT INTO t1 VALUES (2,'h','yes-h'); 2803INSERT INTO t1 VALUES (3,'d','no'); 2804 2805SELECT v2 2806FROM t1 2807WHERE v1 IN ('f', 'd', 'h', 'u' ) AND i = 2; 2808v2 2809yes-u 2810yes-h 2811 2812# Should not use index_merge 2813EXPLAIN 2814SELECT v2 2815FROM t1 2816WHERE v1 IN ('f', 'd', 'h', 'u' ) AND i = 2; 2817id select_type table type possible_keys key key_len ref rows Extra 28181 SIMPLE t1 ref i,v i 5 const 2 Using where 2819DROP TABLE t1; 2820# 2821# Bug#54606 innodb fast alter table + pack_keys=0 2822# prevents adding new indexes 2823# 2824DROP TABLE IF EXISTS t1; 2825CREATE TABLE t1 (a INT, b CHAR(9), c INT, key(b)) 2826ENGINE=InnoDB 2827PACK_KEYS=0; 2828CREATE INDEX a ON t1 (a); 2829CREATE INDEX c on t1 (c); 2830DROP TABLE t1; 2831# 2832# Additional coverage for refactoring which is made as part 2833# of fix for Bug#27480 "Extend CREATE TEMPORARY TABLES privilege 2834# to allow temp table operations". 2835# 2836# Check that OPTIMIZE table works for temporary InnoDB tables. 2837DROP TABLE IF EXISTS t1; 2838CREATE TEMPORARY TABLE t1 (a INT) ENGINE=InnoDB; 2839OPTIMIZE TABLE t1; 2840Table Op Msg_type Msg_text 2841test.t1 optimize note Table does not support optimize, doing recreate + analyze instead 2842test.t1 optimize status OK 2843DROP TABLE t1; 2844# 2845# Bug#11762345 54927: DROPPING AND ADDING AN INDEX IN ONE 2846# COMMAND CAN FAIL IN INNODB PLUGIN 1.0 2847# 2848DROP TABLE IF EXISTS t1; 2849CREATE TABLE t1 (id int, a int, b int, PRIMARY KEY (id), 2850INDEX a (a)) ENGINE=innodb; 2851ALTER TABLE t1 DROP INDEX a, ADD INDEX a (b, a); 2852ALTER TABLE t1 DROP INDEX a, ADD INDEX (a, b); 2853DROP TABLE t1; 2854End of 6.0 tests 2855