SET SESSION DEFAULT_STORAGE_ENGINE = InnoDB; drop table if exists t1,t2,t3,t1m,t1i,t2m,t2i,t4; drop procedure if exists p1; create table t1 ( c_id int(11) not null default '0', org_id int(11) default null, unique key contacts$c_id (c_id), key contacts$org_id (org_id) ); insert into t1 values (2,null),(120,null),(141,null),(218,7), (128,1), (151,2),(234,2),(236,2),(243,2),(255,2),(259,2),(232,3),(235,3),(238,3), (246,3),(253,3),(269,3),(285,3),(291,3),(293,3),(131,4),(230,4),(231,4); create table t2 ( slai_id int(11) not null default '0', owner_tbl int(11) default null, owner_id int(11) default null, sla_id int(11) default null, inc_web int(11) default null, inc_email int(11) default null, inc_chat int(11) default null, inc_csr int(11) default null, inc_total int(11) default null, time_billed int(11) default null, activedate timestamp null default null, expiredate timestamp null default null, state int(11) default null, sla_set int(11) default null, unique key t2$slai_id (slai_id), key t2$owner_id (owner_id), key t2$sla_id (sla_id) ); insert into t2(slai_id, owner_tbl, owner_id, sla_id) values (1,3,1,1), (3,3,10,2), (4,3,3,6), (5,3,2,5), (6,3,8,3), (7,3,9,7), (8,3,6,8), (9,3,4,9), (10,3,5,10), (11,3,11,11), (12,3,7,12); flush tables; select si.slai_id from t1 c join t2 si on ((si.owner_tbl = 3 and si.owner_id = c.org_id) or ( si.owner_tbl = 2 and si.owner_id = c.c_id)) where c.c_id = 218 and expiredate is null; slai_id 12 select * from t1 where org_id is null; c_id org_id 2 NULL 120 NULL 141 NULL select si.slai_id from t1 c join t2 si on ((si.owner_tbl = 3 and si.owner_id = c.org_id) or ( si.owner_tbl = 2 and si.owner_id = c.c_id)) where c.c_id = 218 and expiredate is null; slai_id 12 drop table t1, t2; CREATE TABLE t1 (a int, b int, KEY b (b)); CREATE TABLE t2 (a int, b int, PRIMARY KEY (a,b)); CREATE TABLE t3 (a int, b int, c int, PRIMARY KEY (a), UNIQUE KEY b (b,c), KEY a (a,b,c)); INSERT INTO t1 VALUES (1, 1); INSERT INTO t1 SELECT a + 1, b + 1 FROM t1; INSERT INTO t1 SELECT a + 2, b + 2 FROM t1; INSERT INTO t2 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8); INSERT INTO t2 SELECT a + 1, b FROM t2; DELETE FROM t2 WHERE a = 1 AND b < 2; INSERT INTO t3 VALUES (1,1,1),(2,1,2); INSERT INTO t3 SELECT a + 2, a + 2, 3 FROM t3; INSERT INTO t3 SELECT a + 4, a + 4, 3 FROM t3; SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2) ORDER BY t1.b LIMIT 2; b a 1 1 2 2 SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2) ORDER BY t1.b LIMIT 5; b a 1 1 2 2 2 2 3 3 3 3 DROP TABLE t1, t2, t3; CREATE TABLE `t1` (`id1` INT) ; INSERT INTO `t1` (`id1`) VALUES (1),(5),(2); CREATE TABLE `t2` ( `id1` INT, `id2` INT NOT NULL, `id3` INT, `id4` INT NOT NULL, UNIQUE (`id2`,`id4`), KEY (`id1`) ); INSERT INTO `t2`(`id1`,`id2`,`id3`,`id4`) VALUES (1,1,1,0), (1,1,2,1), (5,1,2,2), (6,1,2,3), (1,2,2,2), (1,2,1,1); SELECT `id1` FROM `t1` WHERE `id1` NOT IN (SELECT `id1` FROM `t2` WHERE `id2` = 1 AND `id3` = 2); id1 2 DROP TABLE t1, t2; create table t1 (c1 int) engine=innodb; connect con1,localhost,root,,; connect con2,localhost,root,,; connection con2; handler t1 open; handler t1 read first; c1 disconnect con2; connection con1; Before and after comparison 0 connection default; drop table t1; disconnect con1; CREATE TABLE t1(c1 TEXT, UNIQUE (c1(1)), cnt INT DEFAULT 1) ENGINE=INNODB CHARACTER SET UTF8; INSERT INTO t1 (c1) VALUES ('1a'); SELECT * FROM t1; c1 cnt 1a 1 INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1; SELECT * FROM t1; c1 cnt 1a 2 DROP TABLE t1; CREATE TABLE t1(c1 VARCHAR(2), UNIQUE (c1(1)), cnt INT DEFAULT 1) ENGINE=INNODB CHARACTER SET UTF8; INSERT INTO t1 (c1) VALUES ('1a'); SELECT * FROM t1; c1 cnt 1a 1 INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1; SELECT * FROM t1; c1 cnt 1a 2 DROP TABLE t1; CREATE TABLE t1(c1 CHAR(2), UNIQUE (c1(1)), cnt INT DEFAULT 1) ENGINE=INNODB CHARACTER SET UTF8; INSERT INTO t1 (c1) VALUES ('1a'); SELECT * FROM t1; c1 cnt 1a 1 INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1; SELECT * FROM t1; c1 cnt 1a 2 DROP TABLE t1; CREATE TABLE t1 ( a1 decimal(10,0) DEFAULT NULL, a2 blob, a3 time DEFAULT NULL, a4 blob, a5 char(175) DEFAULT NULL, a6 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', a7 tinyblob, INDEX idx (a6,a7(239),a5) ) ENGINE=InnoDB; EXPLAIN SELECT a4 FROM t1 WHERE a6=NULL AND a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables EXPLAIN SELECT t1.a4 FROM t1, t1 t WHERE t.a6=t.a6 AND t1.a6=NULL AND t1.a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables DROP TABLE t1; create table t1m (a int) engine = MEMORY; create table t1i (a int); create table t2m (a int) engine = MEMORY; create table t2i (a int); insert into t2m values (5); insert into t2i values (5); select min(a) from t1i; min(a) NULL select min(7) from t1i; min(7) NULL select min(7) from DUAL; min(7) 7 explain select min(7) from t2i join t1i; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1i ALL NULL NULL NULL NULL 0 1 SIMPLE t2i ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) select min(7) from t2i join t1i; min(7) NULL select max(a) from t1i; max(a) NULL select max(7) from t1i; max(7) NULL select max(7) from DUAL; max(7) 7 explain select max(7) from t2i join t1i; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1i ALL NULL NULL NULL NULL 0 1 SIMPLE t2i ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) select max(7) from t2i join t1i; max(7) NULL select 1, min(a) from t1i where a=99; 1 min(a) 1 NULL select 1, min(a) from t1i where 1=99; 1 min(a) 1 NULL select 1, min(1) from t1i where a=99; 1 min(1) 1 NULL select 1, min(1) from t1i where 1=99; 1 min(1) 1 NULL select 1, max(a) from t1i where a=99; 1 max(a) 1 NULL select 1, max(a) from t1i where 1=99; 1 max(a) 1 NULL select 1, max(1) from t1i where a=99; 1 max(1) 1 NULL select 1, max(1) from t1i where 1=99; 1 max(1) 1 NULL explain select count(*), min(7), max(7) from t1m, t1i; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1m system NULL NULL NULL NULL 0 Const row not found 1 SIMPLE t1i ALL NULL NULL NULL NULL 0 select count(*), min(7), max(7) from t1m, t1i; count(*) min(7) max(7) 0 NULL NULL explain select count(*), min(7), max(7) from t1m, t2i; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1m system NULL NULL NULL NULL 0 Const row not found 1 SIMPLE t2i ALL NULL NULL NULL NULL 1 select count(*), min(7), max(7) from t1m, t2i; count(*) min(7) max(7) 0 NULL NULL explain select count(*), min(7), max(7) from t2m, t1i; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2m system NULL NULL NULL NULL 1 1 SIMPLE t1i ALL NULL NULL NULL NULL 0 select count(*), min(7), max(7) from t2m, t1i; count(*) min(7) max(7) 0 NULL NULL drop table t1m, t1i, t2m, t2i; create table t1 ( a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' ' ) ENGINE = MEMORY; insert into t1 (a1, a2, b, c, d) values ('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), ('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), ('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), ('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), ('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), ('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), ('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), ('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), ('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), ('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), ('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), ('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'), ('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'), ('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'), ('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'), ('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'), ('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), ('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), ('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), ('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), ('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), ('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), ('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), ('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), ('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), ('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), ('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), ('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'), ('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'), ('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'), ('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'), ('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'); create table t4 ( pk_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 ' ' ); insert into t4 (a1, a2, b, c, d, dummy) select * from t1; create index idx12672_0 on t4 (a1); create index idx12672_1 on t4 (a1,a2,b,c); create index idx12672_2 on t4 (a1,a2,b); analyze table t4; Table Op Msg_type Msg_text test.t4 analyze status Engine-independent statistics collected test.t4 analyze status OK select distinct a1 from t4 where pk_col not in (1,2,3,4); a1 a b c d drop table t1,t4; DROP TABLE IF EXISTS t2, t1; CREATE TABLE t1 (i INT NOT NULL PRIMARY KEY) ENGINE= InnoDB; CREATE TABLE t2 ( i INT NOT NULL, FOREIGN KEY (i) REFERENCES t1 (i) ON DELETE NO ACTION ) ENGINE= InnoDB; INSERT INTO t1 VALUES (1); INSERT INTO t2 VALUES (1); DELETE IGNORE FROM t1 WHERE i = 1; Warnings: Warning 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) SELECT * FROM t1, t2; i i 1 1 DROP TABLE t2, t1; End of 4.1 tests. create table t1 ( a varchar(30), b varchar(30), primary key(a), key(b) ); select distinct a from t1; a drop table t1; create table t1(a int, key(a)); insert into t1 values(1); select a, count(a) from t1 group by a with rollup; a count(a) 1 1 NULL 1 drop table t1; create table t1 (f1 int, f2 char(1), primary key(f1,f2)) stats_persistent=0; insert into t1 values ( 1,"e"),(2,"a"),( 3,"c"),(4,"d"); alter table t1 drop primary key, add primary key (f2, f1); explain select distinct f1 a, f1 b from t1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 5 NULL 4 Using index; Using temporary explain select distinct f1, f2 from t1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 5 NULL 4 Using index drop table t1; CREATE TABLE t1 (id int(11) NOT NULL PRIMARY KEY, name varchar(20), INDEX (name)); CREATE TABLE t2 (id int(11) NOT NULL PRIMARY KEY, fkey int(11)); ALTER TABLE t2 ADD FOREIGN KEY (fkey) REFERENCES t2(id); INSERT INTO t1 VALUES (1,'A1'),(2,'A2'),(3,'B'); INSERT INTO t2 VALUES (1,1),(2,2),(3,2),(4,3),(5,3); EXPLAIN SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id WHERE t1.name LIKE 'A%'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY,name name 23 NULL 2 Using where; Using index 1 SIMPLE t2 ref fkey fkey 5 test.t1.id 1 Using index EXPLAIN SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id WHERE t1.name LIKE 'A%' OR FALSE; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY,name name 23 NULL 2 Using where; Using index 1 SIMPLE t2 ref fkey fkey 5 test.t1.id 1 Using index DROP TABLE t1,t2; CREATE TABLE t1 ( id int NOT NULL, name varchar(20) NOT NULL, dept varchar(20) NOT NULL, age tinyint(3) unsigned NOT NULL, PRIMARY KEY (id), INDEX (name,dept) ) ENGINE=InnoDB STATS_PERSISTENT=0; INSERT INTO t1(id, dept, age, name) VALUES (3987, 'cs1', 10, 'rs1'), (3988, 'cs2', 20, 'rs1'), (3995, 'cs3', 10, 'rs2'), (3996, 'cs4', 20, 'rs2'), (4003, 'cs5', 10, 'rs3'), (4004, 'cs6', 20, 'rs3'), (4011, 'cs7', 10, 'rs4'), (4012, 'cs8', 20, 'rs4'), (4019, 'cs9', 10, 'rs5'), (4020, 'cs10', 20, 'rs5'),(4027, 'cs11', 10, 'rs6'),(4028, 'cs12', 20, 'rs6'); set @tmp_uss=@@use_stat_tables, @tmp_occ=@@optimizer_use_condition_selectivity; set use_stat_tables='preferably', optimizer_use_condition_selectivity=4; analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK flush tables; EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref name name 22 const 2 Using where; Using index SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; name dept rs5 cs10 rs5 cs9 DELETE FROM t1; # Masking (#) number in "rows" column of the following EXPLAIN output, as it may vary (bug#47746). EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref name name 22 const # Using where; Using index SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; name dept DROP TABLE t1; set use_stat_tables=@tmp_uss, optimizer_use_condition_selectivity=@tmp_occ; drop table if exists t1; show variables like 'innodb_rollback_on_timeout'; Variable_name Value innodb_rollback_on_timeout OFF create table t1 (a int unsigned not null primary key) engine = innodb; insert into t1 values (1); commit; connect con1,localhost,root,,; connect con2,localhost,root,,; connection con2; begin work; insert into t1 values (2); select * from t1; a 1 2 connection con1; begin work; insert into t1 values (5); select * from t1; a 1 5 insert into t1 values (2); ERROR HY000: Lock wait timeout exceeded; try restarting transaction select * from t1; a 1 5 commit; connection con2; select * from t1; a 1 2 commit; connection default; select * from t1; a 1 2 5 drop table t1; disconnect con1; disconnect con2; set @save_qcache_size=@@global.query_cache_size; set @save_qcache_type=@@global.query_cache_type; set global query_cache_size=10*1024*1024; set global query_cache_type=1; connect con1,localhost,root,,; connection con1; drop table if exists `test`; Warnings: Note 1051 Unknown table 'test.test' CREATE TABLE `test` (`test1` varchar(3) NOT NULL, `test2` varchar(4) NOT NULL,PRIMARY KEY (`test1`)) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `test` (`test1`, `test2`) VALUES ('tes', '5678'); disconnect con1; connect con2,localhost,root,,; connection con2; select * from test; test1 test2 tes 5678 INSERT INTO `test` (`test1`, `test2`) VALUES ('tes', '1234') ON DUPLICATE KEY UPDATE `test2` = '1234'; select * from test; test1 test2 tes 1234 flush tables; select * from test; test1 test2 tes 1234 disconnect con2; connection default; drop table test; set global query_cache_type=@save_qcache_type; set global query_cache_size=@save_qcache_size; drop table if exists t1; show variables like 'innodb_rollback_on_timeout'; Variable_name Value innodb_rollback_on_timeout OFF create table t1 (a int unsigned not null primary key) engine = innodb; insert into t1 values (1); commit; connect con1,localhost,root,,; connect con2,localhost,root,,; connection con2; begin work; insert into t1 values (2); select * from t1; a 1 2 connection con1; begin work; insert into t1 values (5); select * from t1; a 1 5 insert into t1 values (2); ERROR HY000: Lock wait timeout exceeded; try restarting transaction select * from t1; a 1 5 commit; connection con2; select * from t1; a 1 2 commit; connection default; select * from t1; a 1 2 5 drop table t1; disconnect con1; disconnect con2; create table t1( id int auto_increment, c char(1) not null, counter int not null default 1, primary key (id), unique key (c) ) engine=innodb; insert into t1 (id, c) values (NULL, 'a'), (NULL, 'a') on duplicate key update id = values(id), counter = counter + 1; select * from t1; id c counter 2 a 2 insert into t1 (id, c) values (NULL, 'b') on duplicate key update id = values(id), counter = counter + 1; select * from t1; id c counter 2 a 2 3 b 1 truncate table t1; insert into t1 (id, c) values (NULL, 'a'); select * from t1; id c counter 1 a 1 insert into t1 (id, c) values (NULL, 'b'), (NULL, 'b') on duplicate key update id = values(id), c = values(c), counter = counter + 1; select * from t1; id c counter 1 a 1 3 b 2 insert into t1 (id, c) values (NULL, 'a') on duplicate key update id = values(id), c = values(c), counter = counter + 1; select * from t1; id c counter 3 b 2 4 a 2 drop table t1; CREATE TABLE t1( id int AUTO_INCREMENT PRIMARY KEY, stat_id int NOT NULL, acct_id int DEFAULT NULL, INDEX idx1 (stat_id, acct_id), INDEX idx2 (acct_id) ) ENGINE=MyISAM; CREATE TABLE t2( id int AUTO_INCREMENT PRIMARY KEY, stat_id int NOT NULL, acct_id int DEFAULT NULL, INDEX idx1 (stat_id, acct_id), INDEX idx2 (acct_id) ) ENGINE=InnoDB STATS_PERSISTENT=0; INSERT INTO t1(stat_id,acct_id) VALUES (1,759), (2,831), (3,785), (4,854), (1,921), (1,553), (2,589), (3,743), (2,827), (2,545), (4,779), (4,783), (1,597), (1,785), (4,832), (1,741), (1,833), (3,788), (2,973), (1,907); INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; Warnings: Warning 1365 Division by 0 Warning 1365 Division by 0 INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; Warnings: Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; Warnings: Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; Warnings: Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; Warnings: Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; Warnings: Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; Warnings: Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; Warnings: Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 UPDATE t1 SET acct_id=785 WHERE MOD(stat_id,2)=0 AND MOD(id,stat_id)=MOD(acct_id,stat_id); OPTIMIZE TABLE t1; Table Op Msg_type Msg_text test.t1 optimize status OK SELECT COUNT(*) FROM t1; COUNT(*) 40960 SELECT COUNT(*) FROM t1 WHERE acct_id=785; COUNT(*) 8702 EXPLAIN SELECT COUNT(*) FROM t1 WHERE stat_id IN (1,3) AND acct_id=785; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range idx1,idx2 idx1 9 NULL 2 Using where; Using index INSERT INTO t2 SELECT * FROM t1; OPTIMIZE TABLE t2; Table Op Msg_type Msg_text test.t2 optimize note Table does not support optimize, doing recreate + analyze instead test.t2 optimize status OK DROP TABLE t1,t2; create table t1(a int) engine=innodb; alter table t1 comment '123'; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci COMMENT='123' drop table t1; CREATE TABLE t1 (a CHAR(2), KEY (a)) ENGINE = InnoDB DEFAULT CHARSET=UTF8; INSERT INTO t1 VALUES ('uk'),('bg'); SELECT * FROM t1 WHERE a = 'uk'; a uk DELETE FROM t1 WHERE a = 'uk'; SELECT * FROM t1 WHERE a = 'uk'; a UPDATE t1 SET a = 'us' WHERE a = 'uk'; SELECT * FROM t1 WHERE a = 'uk'; a CREATE TABLE t2 (a CHAR(2), KEY (a)) ENGINE = InnoDB; INSERT INTO t2 VALUES ('uk'),('bg'); SELECT * FROM t2 WHERE a = 'uk'; a uk DELETE FROM t2 WHERE a = 'uk'; SELECT * FROM t2 WHERE a = 'uk'; a INSERT INTO t2 VALUES ('uk'); UPDATE t2 SET a = 'us' WHERE a = 'uk'; SELECT * FROM t2 WHERE a = 'uk'; a CREATE TABLE t3 (a CHAR(2), KEY (a)) ENGINE = MyISAM; INSERT INTO t3 VALUES ('uk'),('bg'); SELECT * FROM t3 WHERE a = 'uk'; a uk DELETE FROM t3 WHERE a = 'uk'; SELECT * FROM t3 WHERE a = 'uk'; a INSERT INTO t3 VALUES ('uk'); UPDATE t3 SET a = 'us' WHERE a = 'uk'; SELECT * FROM t3 WHERE a = 'uk'; a DROP TABLE t1,t2,t3; create table t1 (a int) engine=innodb; select * from bug29807; ERROR 42S02: Table 'test.bug29807' doesn't exist in engine drop table t1; drop table bug29807; Warnings: Warning 1932 Table 'test.bug29807' doesn't exist in engine CREATE TABLE t1 (a INT) ENGINE=InnoDB; CREATE TABLE t2 (a INT) ENGINE=InnoDB; CONNECT c1,localhost,root,,; CONNECT c2,localhost,root,,; connection c1; SET AUTOCOMMIT=0; INSERT INTO t2 VALUES (1); connection c2; SET AUTOCOMMIT=0; SET @old_lock_wait_timeout= @@lock_wait_timeout; SET lock_wait_timeout= 1; LOCK TABLES t1 READ, t2 READ; ERROR HY000: Lock wait timeout exceeded; try restarting transaction SET @@lock_wait_timeout= @old_lock_wait_timeout; connection c1; COMMIT; INSERT INTO t1 VALUES (1); connection default; SET AUTOCOMMIT=default; disconnect c1; disconnect c2; DROP TABLE t1,t2; CREATE TABLE t1 ( id int NOT NULL auto_increment PRIMARY KEY, b int NOT NULL, c datetime NOT NULL, INDEX idx_b(b), INDEX idx_c(c) ) ENGINE=InnoDB; CREATE TABLE t2 ( b int NOT NULL auto_increment PRIMARY KEY, c datetime NOT NULL ) ENGINE= MyISAM; INSERT INTO t2(c) VALUES ('2007-01-01'); INSERT INTO t2(c) SELECT c FROM t2; INSERT INTO t2(c) SELECT c FROM t2; INSERT INTO t2(c) SELECT c FROM t2; INSERT INTO t2(c) SELECT c FROM t2; INSERT INTO t2(c) SELECT c FROM t2; INSERT INTO t2(c) SELECT c FROM t2; INSERT INTO t2(c) SELECT c FROM t2; INSERT INTO t2(c) SELECT c FROM t2; INSERT INTO t2(c) SELECT c FROM t2; INSERT INTO t2(c) SELECT c FROM t2; INSERT INTO t1(b,c) SELECT b,c FROM t2; UPDATE t2 SET c='2007-01-02'; INSERT INTO t1(b,c) SELECT b,c FROM t2; UPDATE t2 SET c='2007-01-03'; INSERT INTO t1(b,c) SELECT b,c FROM t2; set @@sort_buffer_size=8192; SELECT COUNT(*) FROM t1; COUNT(*) 3072 EXPLAIN SELECT COUNT(*) FROM t1 WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL idx_b,idx_c NULL NULL NULL # Using where SELECT COUNT(*) FROM t1 WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1; COUNT(*) 3072 EXPLAIN SELECT COUNT(*) FROM t1 FORCE INDEX(idx_b, idx_c) WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge idx_b,idx_c idx_c,idx_b 5,4 NULL # Using sort_union(idx_c,idx_b); Using where SELECT COUNT(*) FROM t1 FORCE INDEX(idx_b, idx_c) WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1; COUNT(*) 3072 set @@sort_buffer_size=default; DROP TABLE t1,t2; CREATE TABLE t1 (a int, b int); insert into t1 values (1,1),(1,2); CREATE TABLE t2 (primary key (a)) select * from t1; ERROR 23000: Duplicate entry '1' for key 'PRIMARY' drop table if exists t2; Warnings: Note 1051 Unknown table 'test.t2' CREATE TEMPORARY TABLE t2 (primary key (a)) select * from t1; ERROR 23000: Duplicate entry '1' for key 'PRIMARY' drop table if exists t2; Warnings: Note 1051 Unknown table 'test.t2' CREATE TABLE t2 (a int, b int, primary key (a)); BEGIN; INSERT INTO t2 values(100,100); CREATE TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1; Warnings: Note 1050 Table 't2' already exists SELECT * from t2; a b 100 100 ROLLBACK; SELECT * from t2; a b 100 100 TRUNCATE table t2; INSERT INTO t2 select * from t1; ERROR 23000: Duplicate entry '1' for key 'PRIMARY' SELECT * from t2; a b drop table t2; CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a)); BEGIN; INSERT INTO t2 values(100,100); CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1; Warnings: Note 1050 Table 't2' already exists SELECT * from t2; a b 100 100 COMMIT; BEGIN; INSERT INTO t2 values(101,101); CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1; Warnings: Note 1050 Table 't2' already exists SELECT * from t2; a b 100 100 101 101 ROLLBACK; SELECT * from t2; a b 100 100 TRUNCATE table t2; INSERT INTO t2 select * from t1; ERROR 23000: Duplicate entry '1' for key 'PRIMARY' SELECT * from t2; a b drop table t1,t2; create table t1(f1 varchar(800) binary not null, key(f1)) character set utf8 collate utf8_general_ci; insert into t1 values('aaa'); drop table t1; CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c FLOAT, KEY b(b)) ENGINE = INNODB; INSERT INTO t1 VALUES ( 1 , 1 , 1); INSERT INTO t1 SELECT a + 1 , MOD(a + 1 , 20), 1 FROM t1; INSERT INTO t1 SELECT a + 2 , MOD(a + 2 , 20), 1 FROM t1; INSERT INTO t1 SELECT a + 4 , MOD(a + 4 , 20), 1 FROM t1; INSERT INTO t1 SELECT a + 8 , MOD(a + 8 , 20), 1 FROM t1; INSERT INTO t1 SELECT a + 16, MOD(a + 16, 20), 1 FROM t1; INSERT INTO t1 SELECT a + 32, MOD(a + 32, 20), 1 FROM t1; INSERT INTO t1 SELECT a + 64, MOD(a + 64, 20), 1 FROM t1; EXPLAIN SELECT b, SUM(c) FROM t1 GROUP BY b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL b 5 NULL 128 EXPLAIN SELECT SQL_BIG_RESULT b, SUM(c) FROM t1 GROUP BY b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using filesort DROP TABLE t1; drop table if exists t1; show variables like 'innodb_rollback_on_timeout'; Variable_name Value innodb_rollback_on_timeout OFF create table t1 (a int unsigned not null primary key) engine = innodb; insert into t1 values (1); commit; connect con1,localhost,root,,; connect con2,localhost,root,,; connection con2; begin work; insert into t1 values (2); select * from t1; a 1 2 connection con1; begin work; insert into t1 values (5); select * from t1; a 1 5 insert into t1 values (2); ERROR HY000: Lock wait timeout exceeded; try restarting transaction select * from t1; a 1 5 commit; connection con2; select * from t1; a 1 2 commit; connection default; select * from t1; a 1 2 5 drop table t1; disconnect con1; disconnect con2; drop table if exists t1; create table t1 (a int) engine=innodb; alter table t1 alter a set default 1; drop table t1; Bug#24918 drop table and lock / inconsistent between perm and temp tables Check transactional tables under LOCK TABLES drop table if exists t24918, t24918_tmp, t24918_trans, t24918_trans_tmp, t24918_access; create table t24918_access (id int); create table t24918 (id int) engine=myisam; create temporary table t24918_tmp (id int) engine=myisam; create table t24918_trans (id int) engine=innodb; create temporary table t24918_trans_tmp (id int) engine=innodb; lock table t24918 write, t24918_tmp write, t24918_trans write, t24918_trans_tmp write; drop table t24918; select * from t24918_access; ERROR HY000: Table 't24918_access' was not locked with LOCK TABLES drop table t24918_trans; select * from t24918_access; ERROR HY000: Table 't24918_access' was not locked with LOCK TABLES drop table t24918_trans_tmp; select * from t24918_access; ERROR HY000: Table 't24918_access' was not locked with LOCK TABLES drop table t24918_tmp; select * from t24918_access; ERROR HY000: Table 't24918_access' was not locked with LOCK TABLES unlock tables; drop table t24918_access; CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY bkey (b)) ENGINE=InnoDB; INSERT INTO t1 VALUES (1,2),(3,2),(2,2),(4,2),(5,2),(6,2),(7,2),(8,2); INSERT INTO t1 SELECT a + 8, 2 FROM t1; INSERT INTO t1 SELECT a + 16, 1 FROM t1; EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a; id 1 select_type SIMPLE table t1 type ref possible_keys bkey key bkey key_len 5 ref const rows 16 Extra Using where; Using index SELECT * FROM t1 WHERE b=2 ORDER BY a; a b 1 2 2 2 3 2 4 2 5 2 6 2 7 2 8 2 9 2 10 2 11 2 12 2 13 2 14 2 15 2 16 2 EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a; id 1 select_type SIMPLE table t1 type range possible_keys bkey key bkey key_len 5 ref NULL rows 32 Extra Using where; Using index; Using filesort SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a; a b 1 2 2 2 3 2 4 2 5 2 6 2 7 2 8 2 9 2 10 2 11 2 12 2 13 2 14 2 15 2 16 2 17 1 18 1 19 1 20 1 21 1 22 1 23 1 24 1 25 1 26 1 27 1 28 1 29 1 30 1 31 1 32 1 EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a; id 1 select_type SIMPLE table t1 type range possible_keys bkey key bkey key_len 5 ref NULL rows 32 Extra Using where; Using index SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a; a b 17 1 18 1 19 1 20 1 21 1 22 1 23 1 24 1 25 1 26 1 27 1 28 1 29 1 30 1 31 1 32 1 1 2 2 2 3 2 4 2 5 2 6 2 7 2 8 2 9 2 10 2 11 2 12 2 13 2 14 2 15 2 16 2 CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a), KEY bkey (b,c)) ENGINE=InnoDB; INSERT INTO t2 VALUES (1,1,1),(3,1,1),(2,1,1),(4,1,1); INSERT INTO t2 SELECT a + 4, 1, 1 FROM t2; INSERT INTO t2 SELECT a + 8, 1, 1 FROM t2; EXPLAIN SELECT * FROM t2 WHERE b=1 ORDER BY a; id 1 select_type SIMPLE table t2 type ref possible_keys bkey key bkey key_len 5 ref const rows 16 Extra Using where; Using index; Using filesort SELECT * FROM t2 WHERE b=1 ORDER BY a; a b c 1 1 1 2 1 1 3 1 1 4 1 1 5 1 1 6 1 1 7 1 1 8 1 1 9 1 1 10 1 1 11 1 1 12 1 1 13 1 1 14 1 1 15 1 1 16 1 1 EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a; id 1 select_type SIMPLE table t2 type ref possible_keys bkey key bkey key_len 10 ref const,const rows 16 Extra Using where; Using index SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a; a b c 1 1 1 2 1 1 3 1 1 4 1 1 5 1 1 6 1 1 7 1 1 8 1 1 9 1 1 10 1 1 11 1 1 12 1 1 13 1 1 14 1 1 15 1 1 16 1 1 EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a; id 1 select_type SIMPLE table t2 type ref possible_keys bkey key bkey key_len 10 ref const,const rows 16 Extra Using where; Using index SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a; a b c 1 1 1 2 1 1 3 1 1 4 1 1 5 1 1 6 1 1 7 1 1 8 1 1 9 1 1 10 1 1 11 1 1 12 1 1 13 1 1 14 1 1 15 1 1 16 1 1 EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a; id 1 select_type SIMPLE table t2 type ref possible_keys bkey key bkey key_len 10 ref const,const rows 16 Extra Using where; Using index SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a; a b c 1 1 1 2 1 1 3 1 1 4 1 1 5 1 1 6 1 1 7 1 1 8 1 1 9 1 1 10 1 1 11 1 1 12 1 1 13 1 1 14 1 1 15 1 1 16 1 1 DROP TABLE t1,t2; CREATE TABLE t1 (a INT, PRIMARY KEY (a)) ENGINE=InnoDB; INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8); INSERT INTO t1 SELECT a + 8 FROM t1; INSERT INTO t1 SELECT a + 16 FROM t1; CREATE PROCEDURE p1 () BEGIN DECLARE i INT DEFAULT 50; DECLARE cnt INT; # Continue even in the presence of ER_LOCK_DEADLOCK. DECLARE CONTINUE HANDLER FOR 1213 BEGIN END; START TRANSACTION; ALTER TABLE t1 ENGINE=InnoDB; COMMIT; START TRANSACTION; WHILE (i > 0) DO SET i = i - 1; SELECT COUNT(*) INTO cnt FROM t1 LOCK IN SHARE MODE; END WHILE; COMMIT; END;| CONNECT con1,localhost,root,,; CONNECT con2,localhost,root,,; connection con1; CALL p1(); connection con2; CALL p1(); connection default; CALL p1(); connection con1; connection con2; connection default; disconnect con1; disconnect con2; DROP PROCEDURE p1; DROP TABLE t1; create table t1(a text) engine=innodb default charset=utf8; insert into t1 values('aaa'); set statement sql_mode = 'NO_ENGINE_SUBSTITUTION' for alter table t1 add index(a(1024)); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` text DEFAULT NULL, KEY `a` (`a`(1024)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci drop table t1; CREATE TABLE t1 ( a INT, b INT, KEY (b) ) ENGINE=InnoDB; INSERT INTO t1 VALUES (1,10), (2,10), (2,20), (3,30); START TRANSACTION; SELECT * FROM t1 WHERE b=20 FOR UPDATE; a b 2 20 connect conn2, localhost, root,,test; START TRANSACTION; SELECT * FROM t1 WHERE b=10 ORDER BY A FOR UPDATE; a b 1 10 2 10 ROLLBACK; disconnect conn2; connection default; ROLLBACK; DROP TABLE t1; CREATE TABLE t1( a INT, b INT NOT NULL, c INT NOT NULL, d INT, UNIQUE KEY (c,b) ) engine=innodb; INSERT INTO t1 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4); EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort SELECT c,b,d FROM t1 GROUP BY c,b,d; c b d 1 1 50 3 1 4 3 2 40 EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL; c b d 1 1 50 3 1 4 3 2 40 EXPLAIN SELECT c,b,d FROM t1 ORDER BY c,b,d; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort SELECT c,b,d FROM t1 ORDER BY c,b,d; c b d 1 1 50 3 1 4 3 2 40 EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL c 8 NULL 3 SELECT c,b,d FROM t1 GROUP BY c,b; c b d 1 1 50 3 1 4 3 2 40 EXPLAIN SELECT c,b FROM t1 GROUP BY c,b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL c 8 NULL 3 Using index SELECT c,b FROM t1 GROUP BY c,b; c b 1 1 3 1 3 2 DROP TABLE t1; CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), INDEX b (b)) ENGINE=InnoDB; INSERT INTO t1(a,b) VALUES (1,1), (2,2), (3,2); EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a ASC; id 1 select_type SIMPLE table t1 type ref possible_keys b key b key_len 5 ref const rows 2 Extra Using where; Using index SELECT * FROM t1 WHERE b=2 ORDER BY a ASC; a b 2 2 3 2 EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a DESC; id 1 select_type SIMPLE table t1 type ref possible_keys b key b key_len 5 ref const rows 2 Extra Using where; Using index SELECT * FROM t1 WHERE b=2 ORDER BY a DESC; a b 3 2 2 2 EXPLAIN SELECT * FROM t1 ORDER BY b ASC, a ASC; id 1 select_type SIMPLE table t1 type index possible_keys NULL key b key_len 5 ref NULL rows 3 Extra Using index SELECT * FROM t1 ORDER BY b ASC, a ASC; a b 1 1 2 2 3 2 EXPLAIN SELECT * FROM t1 ORDER BY b DESC, a DESC; id 1 select_type SIMPLE table t1 type index possible_keys NULL key b key_len 5 ref NULL rows 3 Extra Using index SELECT * FROM t1 ORDER BY b DESC, a DESC; a b 3 2 2 2 1 1 EXPLAIN SELECT * FROM t1 ORDER BY b ASC, a DESC; id 1 select_type SIMPLE table t1 type index possible_keys NULL key b key_len 5 ref NULL rows 3 Extra Using index; Using filesort SELECT * FROM t1 ORDER BY b ASC, a DESC; a b 1 1 3 2 2 2 EXPLAIN SELECT * FROM t1 ORDER BY b DESC, a ASC; id 1 select_type SIMPLE table t1 type index possible_keys NULL key b key_len 5 ref NULL rows 3 Extra Using index; Using filesort SELECT * FROM t1 ORDER BY b DESC, a ASC; a b 2 2 3 2 1 1 DROP TABLE t1; # # Bug#27610: ALTER TABLE ROW_FORMAT=... does not rebuild the table. # # - prepare; DROP TABLE IF EXISTS t1; CREATE TABLE t1(c INT) ENGINE = InnoDB ROW_FORMAT = COMPACT; # - initial check; SELECT table_schema, table_name, row_format FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = DATABASE() AND table_name = 't1'; table_schema table_name row_format test t1 Compact # - change ROW_FORMAT and check; ALTER TABLE t1 ROW_FORMAT = REDUNDANT; SELECT table_schema, table_name, row_format FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = DATABASE() AND table_name = 't1'; table_schema table_name row_format test t1 Redundant # - that's it, cleanup. DROP TABLE t1; create table t1(a char(10) not null, unique key aa(a(1)), b char(4) not null, unique key bb(b(4))) engine=innodb; desc t1; Field Type Null Key Default Extra a char(10) NO UNI NULL b char(4) NO PRI NULL show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` char(10) NOT NULL, `b` char(4) NOT NULL, UNIQUE KEY `bb` (`b`), UNIQUE KEY `aa` (`a`(1)) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci drop table t1; CREATE TABLE t1 (id int, type char(6), d int, INDEX idx(id,d)) ENGINE=InnoDB; INSERT INTO t1 VALUES (191, 'member', 1), (NULL, 'member', 3), (NULL, 'member', 4), (201, 'member', 2); EXPLAIN SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL idx NULL NULL NULL 4 Using where; Using filesort SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d; id type d 191 member 1 NULL member 3 NULL member 4 DROP TABLE t1; set @my_innodb_autoextend_increment=@@global.innodb_autoextend_increment; set global innodb_autoextend_increment=8; set global innodb_autoextend_increment=@my_innodb_autoextend_increment; CREATE TABLE t1 (a int, b int, c int, PRIMARY KEY (a), KEY t1_b (b)) ENGINE=InnoDB; INSERT INTO t1 (a,b,c) VALUES (1,1,1), (2,1,1), (3,1,1), (4,1,1); INSERT INTO t1 (a,b,c) SELECT a+4,b,c FROM t1; EXPLAIN SELECT a, b, c FROM t1 WHERE b = 1 ORDER BY a DESC LIMIT 5; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range t1_b t1_b 5 NULL 8 Using where SELECT a, b, c FROM t1 WHERE b = 1 ORDER BY a DESC LIMIT 5; a b c 8 1 1 7 1 1 6 1 1 5 1 1 4 1 1 DROP TABLE t1; DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a char(50)) ENGINE=InnoDB; CREATE INDEX i1 on t1 (a(3)); SELECT * FROM t1 WHERE a = 'abcde'; a DROP TABLE t1; # # BUG #26288: savepoint are not deleted on comit, if the transaction # was otherwise empty # BEGIN; SAVEPOINT s1; COMMIT; RELEASE SAVEPOINT s1; ERROR 42000: SAVEPOINT s1 does not exist BEGIN; SAVEPOINT s2; COMMIT; ROLLBACK TO SAVEPOINT s2; ERROR 42000: SAVEPOINT s2 does not exist BEGIN; SAVEPOINT s3; ROLLBACK; RELEASE SAVEPOINT s3; ERROR 42000: SAVEPOINT s3 does not exist BEGIN; SAVEPOINT s4; ROLLBACK; ROLLBACK TO SAVEPOINT s4; ERROR 42000: SAVEPOINT s4 does not exist CREATE 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; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `f1` int(11) NOT NULL COMMENT 'My ID#', `f2` int(11) DEFAULT NULL, `f3` char(10) DEFAULT 'My ID#', PRIMARY KEY (`f1`), KEY `f2_ref` (`f2`), CONSTRAINT `f2_ref` FOREIGN KEY (`f2`) REFERENCES `t1` (`f1`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1; # # Bug #36995: valgrind error in remove_const during subquery executions # create table t1 (a bit(1) not null,b int) engine=myisam; create table t2 (c int) engine=innodb; set @save_optimizer_switch=@@optimizer_switch; set @@optimizer_switch='in_to_exists=on,materialization=off'; explain select b from t1 where a not in (select max(b) from t1,t2 group by a) group by a; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 2 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 0 Const row not found 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1 set optimizer_switch=@save_optimizer_switch; DROP TABLE t1,t2; End of 5.0 tests CREATE TABLE `t2` ( `k` int(11) NOT NULL auto_increment, `a` int(11) default NULL, `c` int(11) default NULL, PRIMARY KEY (`k`), UNIQUE KEY `idx_1` (`a`) ); insert into t2 ( a ) values ( 6 ) on duplicate key update c = ifnull( c, 0 ) + 1; insert into t2 ( a ) values ( 7 ) on duplicate key update c = ifnull( c, 0 ) + 1; select last_insert_id(); last_insert_id() 2 select * from t2; k a c 1 6 NULL 2 7 NULL insert into t2 ( a ) values ( 6 ) on duplicate key update c = ifnull( c, 0 ) + 1; select last_insert_id(); last_insert_id() 2 select last_insert_id(0); last_insert_id(0) 0 insert into t2 ( a ) values ( 6 ) on duplicate key update c = ifnull( c, 0 ) + 1; select last_insert_id(); last_insert_id() 0 select * from t2; k a c 1 6 2 2 7 NULL insert ignore into t2 values (null,6,1),(10,8,1); Warnings: Warning 1062 Duplicate entry '6' for key 'idx_1' select last_insert_id(); last_insert_id() 0 insert ignore into t2 values (null,6,1),(null,8,1),(null,15,1),(null,20,1); Warnings: Warning 1062 Duplicate entry '6' for key 'idx_1' Warning 1062 Duplicate entry '8' for key 'idx_1' select last_insert_id(); last_insert_id() 11 select * from t2; k a c 1 6 2 2 7 NULL 10 8 1 11 15 1 12 20 1 insert into t2 ( a ) values ( 6 ) on duplicate key update c = ifnull( c, 0 ) + 1, k=last_insert_id(k); select last_insert_id(); last_insert_id() 1 select * from t2; k a c 1 6 3 2 7 NULL 10 8 1 11 15 1 12 20 1 drop table t2; drop table if exists t1, t2; create table t1 (i int); alter table t1 modify i int default 1; alter table t1 modify i int default 2, rename t2; lock table t2 write; alter table t2 modify i int default 3; unlock tables; lock table t2 write; alter table t2 modify i int default 4, rename t1; unlock tables; drop table t1; drop table if exists t1; create table t1 (i int); insert into t1 values (); lock table t1 write; alter table t1 modify i int default 1; insert into t1 values (); select * from t1; i NULL 1 alter table t1 change i c char(10) default "Two"; insert into t1 values (); select * from t1; c NULL 1 Two unlock tables; select * from t1; c NULL 1 Two drop tables t1; create table t1(f1 varchar(5) unique, f2 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); insert into t1(f1) values(1); select @a:=f2 from t1; @a:=f2 # update t1 set f1=1; select @b:=f2 from t1; @b:=f2 # select if(@a=@b,"ok","wrong"); if(@a=@b,"ok","wrong") ok insert into t1(f1) values (1) on duplicate key update f1="1"; select @b:=f2 from t1; @b:=f2 # select if(@a=@b,"ok","wrong"); if(@a=@b,"ok","wrong") ok insert into t1(f1) select f1 from t1 on duplicate key update f1="1"; select @b:=f2 from t1; @b:=f2 # select if(@a=@b,"ok","wrong"); if(@a=@b,"ok","wrong") ok drop table t1; connect con1,localhost,root,,; connect con2,localhost,root,,; SET SESSION AUTOCOMMIT = 0; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; set binlog_format=mixed; connection con1; CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(256)) ENGINE = InnoDB; INSERT INTO t1 VALUES (1,2); # 1. test for locking: BEGIN; UPDATE t1 SET b = 12 WHERE a = 1; affected rows: 1 info: Rows matched: 1 Changed: 1 Warnings: 0 SELECT * FROM t1; a b 1 12 connection con2; UPDATE t1 SET b = 21 WHERE a = 1; ERROR HY000: Lock wait timeout exceeded; try restarting transaction connection con1; SELECT * FROM t1; a b 1 12 ROLLBACK; connection con2; ROLLBACK; connection con1; # 2. test for serialized update: CREATE TABLE t2 (a INT); TRUNCATE t1; INSERT INTO t1 VALUES (1,'init'); CREATE PROCEDURE p1() BEGIN # retry the UPDATE in case it times out the lock before con1 has time # to COMMIT. DECLARE do_retry INT DEFAULT 0; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET do_retry = 1; retry_loop:LOOP UPDATE t1 SET b = CONCAT(b, '+con2') WHERE a = 1; IF do_retry = 0 THEN LEAVE retry_loop; END IF; SET do_retry = 0; END LOOP; INSERT INTO t2 VALUES (); END| BEGIN; UPDATE t1 SET b = CONCAT(b, '+con1') WHERE a = 1; affected rows: 1 info: Rows matched: 1 Changed: 1 Warnings: 0 SELECT * FROM t1; a b 1 init+con1 connection con2; CALL p1;; connection con1; SELECT * FROM t1; a b 1 init+con1 COMMIT; SELECT * FROM t1; a b 1 init+con1 connection con2; SELECT * FROM t1; a b 1 init+con1+con2 COMMIT; connection con1; # 3. test for updated key column: TRUNCATE t1; TRUNCATE t2; INSERT INTO t1 VALUES (1,'init'); BEGIN; UPDATE t1 SET a = 2, b = CONCAT(b, '+con1') WHERE a = 1; affected rows: 1 info: Rows matched: 1 Changed: 1 Warnings: 0 SELECT * FROM t1; a b 2 init+con1 connection con2; CALL p1;; connection con1; SELECT * FROM t1; a b 2 init+con1 COMMIT; SELECT * FROM t1; a b 2 init+con1 connection con2; SELECT * FROM t1; a b 2 init+con1 connection default; disconnect con1; disconnect con2; DROP PROCEDURE p1; DROP TABLE t1, t2; CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, PRIMARY KEY (a,b)) engine=innodb; CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d), CONSTRAINT c2 FOREIGN KEY f2 (c) REFERENCES t1 (a,b) ON UPDATE NO ACTION) engine=innodb; ERROR 42000: Incorrect foreign key definition for 'f2': Key reference and table reference don't match CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d), CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a,b) ON UPDATE NO ACTION) engine=innodb; ERROR 42000: Incorrect foreign key definition for 'c2': Key reference and table reference don't match CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d), CONSTRAINT c1 FOREIGN KEY c2 (c) REFERENCES t1 (a) ON DELETE NO ACTION, CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION) engine=innodb; ALTER TABLE t2 DROP FOREIGN KEY c2; DROP TABLE t2; CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d), FOREIGN KEY (c) REFERENCES t1 (a,k) ON UPDATE NO ACTION) engine=innodb; ERROR 42000: Incorrect foreign key definition for 'foreign key without name': Key reference and table reference don't match CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d), FOREIGN KEY f1 (c) REFERENCES t1 (a,k) ON UPDATE NO ACTION) engine=innodb; ERROR 42000: Incorrect foreign key definition for 'f1': Key reference and table reference don't match CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d), CONSTRAINT c1 FOREIGN KEY f1 (c) REFERENCES t1 (a) ON DELETE NO ACTION, CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION, FOREIGN KEY f3 (c) REFERENCES t1 (a) ON UPDATE NO ACTION, FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION) engine=innodb; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c` int(11) NOT NULL, `d` int(11) NOT NULL, PRIMARY KEY (`c`,`d`), CONSTRAINT `c1` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON DELETE NO ACTION, CONSTRAINT `c2` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON UPDATE NO ACTION, CONSTRAINT `f3` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON UPDATE NO ACTION, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t2; DROP TABLE t1; create table t1 (a int auto_increment primary key) engine=innodb; set statement sql_mode = 'NO_ENGINE_SUBSTITUTION' for alter table t1 order by a; Warnings: Warning 1105 ORDER BY ignored as there is a user-defined clustered index in the table 't1' drop table t1; CREATE TABLE t1 (vid integer NOT NULL, tid integer NOT NULL, idx integer NOT NULL, name varchar(128) NOT NULL, type varchar(128) NULL, PRIMARY KEY(idx, vid, tid), UNIQUE(vid, tid, name) ) ENGINE=InnoDB; INSERT INTO t1 VALUES (1,1,1,'pk',NULL),(2,1,1,'pk',NULL),(3,1,1,'pk',NULL),(4,1,1,'c1',NULL), (5,1,1,'pk',NULL),(1,1,2,'c1',NULL),(2,1,2,'c1',NULL),(3,1,2,'c1',NULL), (4,1,2,'c2',NULL),(5,1,2,'c1',NULL),(2,1,3,'c2',NULL),(3,1,3,'c2',NULL), (4,1,3,'pk',NULL),(5,1,3,'c2',NULL), (2,1,4,'c_extra',NULL),(3,1,4,'c_extra',NULL); EXPLAIN SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE tid = 1 AND vid = 3 ORDER BY idx DESC; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 12 NULL 16 Using where SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE tid = 1 AND vid = 3 ORDER BY idx DESC; vid tid idx name type 3 1 4 c_extra NULL 3 1 3 c2 NULL 3 1 2 c1 NULL 3 1 1 pk NULL DROP TABLE t1; # # Bug #44290: explain crashes for subquery with distinct in # SQL_SELECT::test_quick_select # (reproduced only with InnoDB tables) # CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, KEY (c3), KEY (c2, c3)) ENGINE=InnoDB; INSERT INTO t1 VALUES (1,1,1), (1,1,1), (1,1,2), (1,1,1), (1,1,2); SELECT 1 FROM (SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; 1 1 EXPLAIN SELECT 1 FROM (SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ALL NULL NULL NULL NULL 2 2 DERIVED t1 ref c3,c2 c3 5 const 2 Using where DROP TABLE t1; CREATE TABLE t1 (c1 REAL, c2 REAL, c3 REAL, KEY (c3), KEY (c2, c3)) ENGINE=InnoDB; INSERT INTO t1 VALUES (1,1,1), (1,1,1), (1,1,2), (1,1,1), (1,1,2); SELECT 1 FROM (SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; 1 1 EXPLAIN SELECT 1 FROM (SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ALL NULL NULL NULL NULL 2 2 DERIVED t1 ref c3,c2 c3 9 const 2 Using where DROP TABLE t1; CREATE TABLE t1 (c1 DECIMAL(12,2), c2 DECIMAL(12,2), c3 DECIMAL(12,2), KEY (c3), KEY (c2, c3)) ENGINE=InnoDB; INSERT INTO t1 VALUES (1,1,1), (1,1,1), (1,1,2), (1,1,1), (1,1,2); SELECT 1 FROM (SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; 1 1 EXPLAIN SELECT 1 FROM (SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ALL NULL NULL NULL NULL 2 2 DERIVED t1 ref c3,c2 c3 7 const 2 Using where DROP TABLE t1; End of 5.1 tests # # Bug#43600: Incorrect type conversion caused wrong result. # CREATE TABLE t1 ( a int NOT NULL ) engine= innodb; CREATE TABLE t2 ( a int NOT NULL, b int NOT NULL, filler char(100) DEFAULT NULL, KEY a (a,b) ) engine= innodb; insert into t1 values (0),(1),(2),(3),(4); insert into t2 select A.a + 10 *B.a, 1, 'filler' from t1 A, t1 B; explain select * from t1, t2 where t2.a=t1.a and t2.b + 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 1 SIMPLE t2 ref a a 4 test.t1.a 1 Using index condition select * from t1, t2 where t2.a=t1.a and t2.b + 1; a a b filler 0 0 1 filler 1 1 1 filler 2 2 1 filler 3 3 1 filler 4 4 1 filler drop table t1,t2; # End of test case for the bug#43600 # # Bug#42643: InnoDB does not support replication of TRUNCATE TABLE # # Check that a TRUNCATE TABLE statement, needing an exclusive meta # data lock, waits for a shared metadata lock owned by a concurrent # transaction. # CREATE TABLE t1 (a INT) ENGINE=InnoDB; INSERT INTO t1 VALUES (1),(2),(3); BEGIN; SELECT * FROM t1 ORDER BY a; a 1 2 3 connect con1, localhost, root,,; TRUNCATE TABLE t1;; connection default; SELECT * FROM t1 ORDER BY a; a 1 2 3 ROLLBACK; connection con1; # Reaping TRUNCATE TABLE SELECT * FROM t1; a disconnect con1; connection default; DROP TABLE t1; drop table if exists t1, t2, t3; # # BUG#35850: Performance regression in 5.1.23/5.1.24 # create table t1(a int); insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t2 (a int, b int, pk int, key(a,b), primary key(pk)) engine=innodb; insert into t2 select @a:=A.a+10*(B.a + 10*C.a),@a, @a from t1 A, t1 B, t1 C; # this must use key 'a', not PRIMARY: explain select a from t2 where a=b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index NULL a 10 NULL # Using where; Using index drop table t1, t2; # # Bug #40360: Binlog related errors with binlog off # SET SESSION BINLOG_FORMAT=STATEMENT; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; select @@session.sql_log_bin, @@session.binlog_format, @@session.tx_isolation; @@session.sql_log_bin 1 @@session.binlog_format STATEMENT @@session.tx_isolation READ-COMMITTED CREATE TABLE t1 ( a INT ) ENGINE=InnoDB; INSERT INTO t1 VALUES(1); DROP TABLE t1; # # Bug#37284 Crash in Field_string::type() # DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a char(50)) ENGINE=InnoDB; CREATE INDEX i1 on t1 (a(3)); SELECT * FROM t1 WHERE a = 'abcde'; a DROP TABLE t1; # # Bug #37742: HA_EXTRA_KEYREAD flag is set when key contains only prefix of # requested column # CREATE TABLE foo (a int, b int, c char(10), PRIMARY KEY (c(3)), KEY b (b) ) engine=innodb; CREATE TABLE foo2 (a int, b int, c char(10), PRIMARY KEY (c), KEY b (b) ) engine=innodb; CREATE TABLE bar (a int, b int, c char(10), PRIMARY KEY (c(3)), KEY b (b) ) engine=myisam; INSERT INTO foo VALUES (1,2,'abcdefghij'), (2,3,''), (3,4,'klmnopqrst'), (4,5,'uvwxyz'), (5,6,'meotnsyglt'), (4,5,'asfdewe'); INSERT INTO bar SELECT * FROM foo; INSERT INTO foo2 SELECT * FROM foo; ANALYZE TABLE bar; ANALYZE TABLE foo; ANALYZE TABLE foo2; EXPLAIN SELECT c FROM bar WHERE b>2;; id 1 select_type SIMPLE table bar type ALL possible_keys b key NULL key_len NULL ref NULL rows 6 Extra Using where EXPLAIN SELECT c FROM foo WHERE b>2;; id 1 select_type SIMPLE table foo type ALL possible_keys b key NULL key_len NULL ref NULL rows 6 Extra Using where EXPLAIN SELECT c FROM foo2 WHERE b>2;; id 1 select_type SIMPLE table foo2 type range possible_keys b key b key_len 5 ref NULL rows 5 Extra Using where; Using index EXPLAIN SELECT c FROM bar WHERE c>2;; id 1 select_type SIMPLE table bar type ALL possible_keys PRIMARY key NULL key_len NULL ref NULL rows 6 Extra Using where EXPLAIN SELECT c FROM foo WHERE c>2;; id 1 select_type SIMPLE table foo type ALL possible_keys PRIMARY key NULL key_len NULL ref NULL rows 6 Extra Using where EXPLAIN SELECT c FROM foo2 WHERE c>2;; id 1 select_type SIMPLE table foo2 type index possible_keys PRIMARY key b key_len 5 ref NULL rows 6 Extra Using where; Using index DROP TABLE foo, bar, foo2; # # Bug#41348: INSERT INTO tbl SELECT * FROM temp_tbl overwrites locking type of temp table # DROP TABLE IF EXISTS t1,t3,t2; DROP FUNCTION IF EXISTS f1; CREATE FUNCTION f1() RETURNS VARCHAR(250) BEGIN return 'hhhhhhh' ; END| CREATE TABLE t1 (a VARCHAR(20), b VARCHAR(20), c VARCHAR(20)) ENGINE=INNODB; BEGIN WORK; CREATE TEMPORARY TABLE t2 (a VARCHAR(20), b VARCHAR(20), c varchar(20)) ENGINE=INNODB; CREATE TEMPORARY TABLE t3 LIKE t2; INSERT INTO t1 VALUES ('a','b',NULL),('c','d',NULL),('e','f',NULL); SET @stmt := CONCAT('INSERT INTO t2 SELECT tbl.a, tbl.b, f1()',' FROM t1 tbl'); PREPARE stmt1 FROM @stmt; SET @stmt := CONCAT('INSERT INTO t3', ' SELECT * FROM t2'); PREPARE stmt3 FROM @stmt; EXECUTE stmt1; COMMIT; DEALLOCATE PREPARE stmt1; DEALLOCATE PREPARE stmt3; DROP TABLE t1,t3,t2; DROP FUNCTION f1; # # Bug#37016: TRUNCATE TABLE removes some rows but not all # DROP TABLE IF EXISTS t1,t2; CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB; CREATE TABLE t2 (id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id), FOREIGN KEY (t1_id) REFERENCES t1(id)) ENGINE=INNODB; INSERT INTO t1 VALUES (1),(2); INSERT INTO t2 VALUES (3,2); SET AUTOCOMMIT = 0; START TRANSACTION; TRUNCATE TABLE t1; ERROR 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`)) SELECT * FROM t1; id 1 2 COMMIT; SELECT * FROM t1; id 1 2 START TRANSACTION; TRUNCATE TABLE t1; ERROR 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`)) SELECT * FROM t1; id 1 2 ROLLBACK; SELECT * FROM t1; id 1 2 SET AUTOCOMMIT = 1; START TRANSACTION; SELECT * FROM t1; id 1 2 COMMIT; TRUNCATE TABLE t1; ERROR 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`)) SELECT * FROM t1; id 1 2 DELETE FROM t2 WHERE id = 3; START TRANSACTION; SELECT * FROM t1; id 1 2 TRUNCATE TABLE t1; ERROR 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`)) ROLLBACK; SELECT * FROM t1; id 1 2 TRUNCATE TABLE t2; DROP TABLE t2; DROP TABLE t1; # # Bug#40127 Multiple table DELETE IGNORE hangs on foreign key constraint violation on 5.0 # CREATE TABLE t1 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ) ENGINE=InnoDB; CREATE TABLE t2 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, aid INT UNSIGNED NOT NULL, PRIMARY KEY (id), FOREIGN KEY (aid) REFERENCES t1 (id) ) ENGINE=InnoDB; CREATE TABLE t3 ( bid INT UNSIGNED NOT NULL, FOREIGN KEY (bid) REFERENCES t2 (id) ) ENGINE=InnoDB; CREATE TABLE t4 ( a INT ) ENGINE=InnoDB; CREATE TABLE t5 ( a INT ) ENGINE=InnoDB; INSERT INTO t1 (id) VALUES (1); INSERT INTO t2 (id, aid) VALUES (1, 1),(2,1),(3,1),(4,1); INSERT INTO t3 (bid) VALUES (1); INSERT INTO t4 VALUES (1),(2),(3),(4),(5); INSERT INTO t5 VALUES (1); DELETE t5 FROM t4 LEFT JOIN t5 ON t4.a= t5.a; DELETE t2, t1 FROM t2 INNER JOIN t1 ON (t2.aid = t1.id) WHERE t2.id = 1; ERROR 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`)) DELETE t2, t1 FROM t2 INNER JOIN t1 ON (t2.aid = t1.id) WHERE t2.id = 1; ERROR 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`)) DELETE IGNORE t2, t1 FROM t2 INNER JOIN t1 ON (t2.aid = t1.id) WHERE t2.id = 1; DROP TABLE t3; DROP TABLE t2; DROP TABLE t1; DROP TABLES t4,t5; # Bug#40127 Multiple table DELETE IGNORE hangs on foreign key constraint violation on 5.0 # Testing for any side effects of IGNORE on AFTER DELETE triggers used with # transactional tables. # CREATE TABLE t1 (i INT NOT NULL PRIMARY KEY) ENGINE=InnoDB; CREATE TABLE t2 (a VARCHAR(100)) ENGINE=InnoDB; CREATE TABLE t3 (i INT NOT NULL PRIMARY KEY) ENGINE=InnoDB; CREATE TABLE t4 (i INT NOT NULL PRIMARY KEY, t1i INT, FOREIGN KEY (t1i) REFERENCES t1(i)) ENGINE=InnoDB; CREATE TRIGGER trg AFTER DELETE ON t1 FOR EACH ROW BEGIN SET @b:='EXECUTED TRIGGER'; INSERT INTO t2 VALUES (@b); SET @a:= error_happens_here; END|| SET @b:=""; SET @a:=""; INSERT INTO t1 VALUES (1),(2),(3),(4); INSERT INTO t3 SELECT * FROM t1; ** An error in a trigger causes rollback of the statement. DELETE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i; ERROR 42S22: Unknown column 'error_happens_here' in 'field list' SELECT @a,@b; @a @b EXECUTED TRIGGER SELECT * FROM t2; a SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i; i i 1 1 2 2 3 3 4 4 ** Same happens with the IGNORE option DELETE IGNORE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i; ERROR 42S22: Unknown column 'error_happens_here' in 'field list' SELECT * FROM t2; a SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i; i i 1 1 2 2 3 3 4 4 ** ** The following is an attempt to demonstrate ** error handling inside a row iteration. ** DROP TRIGGER trg; DELETE FROM t1; DELETE FROM t2; DELETE FROM t3; INSERT INTO t1 VALUES (1),(2),(3),(4); INSERT INTO t3 VALUES (1),(2),(3),(4); INSERT INTO t4 VALUES (3,3),(4,4); CREATE TRIGGER trg AFTER DELETE ON t1 FOR EACH ROW BEGIN SET @b:= CONCAT('EXECUTED TRIGGER FOR ROW ',CAST(OLD.i AS CHAR)); INSERT INTO t2 VALUES (@b); END|| ** DELETE is prevented by foreign key constrains but errors are silenced. ** The AFTER trigger isn't fired. DELETE IGNORE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i; ** Tables are modified by best effort: SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i; i i 3 3 4 4 ** The AFTER trigger was only executed on successful rows: SELECT * FROM t2; a EXECUTED TRIGGER FOR ROW 1 EXECUTED TRIGGER FOR ROW 2 DROP TRIGGER trg; ** ** Induce an error midway through an AFTER-trigger ** DELETE FROM t4; DELETE FROM t1; DELETE FROM t3; INSERT INTO t1 VALUES (1),(2),(3),(4); INSERT INTO t3 VALUES (1),(2),(3),(4); CREATE TRIGGER trg AFTER DELETE ON t1 FOR EACH ROW BEGIN SET @a:= @a+1; IF @a > 2 THEN INSERT INTO t4 VALUES (5,5); END IF; END|| SET @a:=0; ** Errors in the trigger causes the statement to abort. DELETE IGNORE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i; ERROR 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`)) SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i; i i 1 1 2 2 3 3 4 4 SELECT * FROM t4; i t1i DROP TRIGGER trg; DROP TABLE t4; DROP TABLE t1; DROP TABLE t2; DROP TABLE t3; # # Bug#43580: Issue with Innodb on multi-table update # CREATE TABLE t1 (a INT, b INT, KEY (a)) ENGINE = INNODB; CREATE TABLE t2 (a INT KEY, b INT, KEY (b)) ENGINE = INNODB; CREATE TABLE t3 (a INT, b INT KEY, KEY (a)) ENGINE = INNODB; CREATE TABLE t4 (a INT KEY, b INT, KEY (b)) ENGINE = INNODB; INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6); INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); INSERT INTO t3 VALUES (1, 101), (2, 102), (3, 103), (4, 104), (5, 105), (6, 106); INSERT INTO t4 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); UPDATE t2 straight_join t1 SET t1.a = t1.a + 100, t2.b = t1.a + 10 WHERE t1.a BETWEEN 2 AND 4 AND t2.a = t1.b; SELECT * FROM t2; a b 1 1 2 12 3 13 4 14 5 5 UPDATE t4 straight_join t3 SET t3.a = t3.a + 100, t4.b = t3.a + 10 WHERE t3.a BETWEEN 2 AND 4 AND t4.a = t3.b - 100; SELECT * FROM t4; a b 1 1 2 12 3 13 4 14 5 5 DROP TABLE t1, t2, t3, t4; # # Bug#44886: SIGSEGV in test_if_skip_sort_order() - # uninitialized variable used as subscript # CREATE TABLE t1 (a INT, b INT, c INT, d INT, PRIMARY KEY (b), KEY (a,c)) ENGINE=InnoDB; INSERT INTO t1 VALUES (1,1,1,0); CREATE TABLE t2 (a INT, b INT, e INT, KEY (e)) ENGINE=InnoDB; INSERT INTO t2 VALUES (1,1,2); CREATE TABLE t3 (a INT, b INT) ENGINE=MyISAM; INSERT INTO t3 VALUES (1, 1); SELECT * FROM t1, t2, t3 WHERE t1.a = t3.a AND (t1.b = t3.b OR t1.d) AND t2.b = t1.b AND t2.e = 2 GROUP BY t1.b; a b c d a b e a b 1 1 1 0 1 1 2 1 1 DROP TABLE t1, t2, t3; # # Bug #45828: Optimizer won't use partial primary key if another # index can prevent filesort # CREATE TABLE `t1` ( c1 int NOT NULL, c2 int NOT NULL, c3 int NOT NULL, PRIMARY KEY (c1,c2), KEY (c3) ) ENGINE=InnoDB; INSERT INTO t1 VALUES (5,2,1246276747); INSERT INTO t1 VALUES (2,1,1246281721); INSERT INTO t1 VALUES (7,3,1246281756); INSERT INTO t1 VALUES (4,2,1246282139); INSERT INTO t1 VALUES (3,1,1246282230); INSERT INTO t1 VALUES (1,0,1246282712); INSERT INTO t1 VALUES (8,3,1246282765); INSERT INTO t1 SELECT c1+10,c2+10,c3+10 FROM t1; INSERT INTO t1 SELECT c1+100,c2+100,c3+100 from t1; INSERT INTO t1 SELECT c1+1000,c2+1000,c3+1000 from t1; INSERT INTO t1 SELECT c1+10000,c2+10000,c3+10000 from t1; INSERT INTO t1 SELECT c1+100000,c2+100000,c3+100000 from t1; INSERT INTO t1 SELECT c1+1000000,c2+1000000,c3+1000000 from t1; ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK SELECT * FROM t1 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3; c1 c2 c3 EXPLAIN SELECT * FROM t1 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref PRIMARY,c3 PRIMARY 4 const 1 Using where; Using filesort EXPLAIN SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref PRIMARY PRIMARY 4 const 1 Using where; Using filesort CREATE TABLE t2 ( c1 int NOT NULL, c2 int NOT NULL, c3 int NOT NULL, KEY (c1,c2), KEY (c3) ) ENGINE=InnoDB; explain SELECT * FROM t2 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ref c1,c3 c1 4 const 1 Using where; Using filesort DROP TABLE t1,t2; # # 36259: Optimizing with ORDER BY # CREATE TABLE t1 ( a INT NOT NULL AUTO_INCREMENT, b INT NOT NULL, c INT NOT NULL, d VARCHAR(5), e INT NOT NULL, PRIMARY KEY (a), KEY i2 (b,c,d) ) ENGINE=InnoDB; INSERT INTO t1 (b,c,d,e) VALUES (1,1,'a',1), (2,2,'b',2); INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1; INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1; INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1; INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1; INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1; INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1; ANALYZE TABLE t1; EXPLAIN SELECT * FROM t1 WHERE b=1 AND c=1 ORDER BY a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref i2 i2 8 const,const 1 Using where; Using filesort EXPLAIN SELECT * FROM t1 FORCE INDEX(i2) WHERE b=1 and c=1 ORDER BY a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref i2 i2 8 const,const 1 Using where; Using filesort EXPLAIN SELECT * FROM t1 FORCE INDEX(PRIMARY) WHERE b=1 AND c=1 ORDER BY a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 4 NULL {checked} Using where DROP TABLE t1; # # Bug #47963: Wrong results when index is used # CREATE TABLE t1( a VARCHAR(5) NOT NULL, b VARCHAR(5) NOT NULL, c DATETIME NOT NULL, KEY (c) ) ENGINE=InnoDB; INSERT INTO t1 VALUES('TEST', 'TEST', '2009-10-09 00:00:00'); SELECT * FROM t1 WHERE a = 'TEST' AND c >= '2009-10-09 00:00:00' AND c <= '2009-10-09 00:00:00'; a b c TEST TEST 2009-10-09 00:00:00 SELECT * FROM t1 WHERE a = 'TEST' AND c >= '2009-10-09 00:00:00.0' AND c <= '2009-10-09 00:00:00.0'; a b c TEST TEST 2009-10-09 00:00:00 SELECT * FROM t1 WHERE a = 'TEST' AND c >= '2009-10-09 00:00:00.0' AND c <= '2009-10-09 00:00:00'; a b c TEST TEST 2009-10-09 00:00:00 SELECT * FROM t1 WHERE a = 'TEST' AND c >= '2009-10-09 00:00:00' AND c <= '2009-10-09 00:00:00.0'; a b c TEST TEST 2009-10-09 00:00:00 SELECT * FROM t1 WHERE a = 'TEST' AND c >= '2009-10-09 00:00:00.000' AND c <= '2009-10-09 00:00:00.000'; a b c TEST TEST 2009-10-09 00:00:00 SELECT * FROM t1 WHERE a = 'TEST' AND c >= '2009-10-09 00:00:00.00' AND c <= '2009-10-09 00:00:00.001'; a b c TEST TEST 2009-10-09 00:00:00 SELECT * FROM t1 WHERE a = 'TEST' AND c >= '2009-10-09 00:00:00.001' AND c <= '2009-10-09 00:00:00.00'; a b c EXPLAIN SELECT * FROM t1 WHERE a = 'TEST' AND c >= '2009-10-09 00:00:00.001' AND c <= '2009-10-09 00:00:00.00'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables DROP TABLE t1; # # Bug #46175: NULL read_view and consistent read assertion # CREATE TABLE t1(a CHAR(13),KEY(a)) ENGINE=innodb; CREATE TABLE t2(b DATETIME,KEY(b)) ENGINE=innodb; INSERT INTO t1 VALUES (),(); INSERT INTO t2 VALUES (),(); CREATE OR REPLACE VIEW v1 AS SELECT 1 FROM t2 WHERE b =(SELECT a FROM t1 LIMIT 1); CONNECT con1, localhost, root,,; connection default; CREATE PROCEDURE p1(num INT) BEGIN DECLARE i INT DEFAULT 0; REPEAT SHOW CREATE VIEW v1; SET i:=i+1; UNTIL i>num END REPEAT; END| # Should not crash # Should not crash disconnect con1; DROP PROCEDURE p1; DROP VIEW v1; DROP TABLE t1,t2; # # Bug #49324: more valgrind errors in test_if_skip_sort_order # CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=innodb ; # should not cause valgrind warnings SELECT 1 FROM t1 JOIN t1 a USING(a) GROUP BY t1.a,t1.a; 1 DROP TABLE t1; # # Bug#50843: Filesort used instead of clustered index led to # performance degradation. # create table t1(f1 int not null primary key, f2 int) engine=innodb; create table t2(f1 int not null, key (f1)) engine=innodb; insert into t1 values (1,1),(2,2),(3,3); insert into t2 values (1),(2),(3); analyze table t1; analyze table t2; explain select t1.* from t1 left join t2 using(f1) group by t1.f1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 4 NULL 3 1 SIMPLE t2 ref f1 f1 4 test.t1.f1 1 Using index drop table t1,t2; # # # Bug #39653: find_shortest_key in sql_select.cc does not consider # clustered primary keys # CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT, d INT, e INT, f INT, KEY (b,c)) ENGINE=INNODB; INSERT INTO t1 VALUES (1,1,1,1,1,1), (2,2,2,2,2,2), (3,3,3,3,3,3), (4,4,4,4,4,4), (5,5,5,5,5,5), (6,6,6,6,6,6), (7,7,7,7,7,7), (8,8,8,8,8,8), (9,9,9,9,9,9), (11,11,11,11,11,11); ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK EXPLAIN SELECT COUNT(*) FROM t1; id 1 select_type SIMPLE table t1 type index possible_keys NULL key b key_len 10 ref NULL rows 10 Extra Using index DROP TABLE t1; # # Bug #49838: DROP INDEX and ADD UNIQUE INDEX for same index may # corrupt definition at engine # CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, KEY k (a,b)) ENGINE=InnoDB; ALTER TABLE t1 DROP INDEX k, ADD UNIQUE INDEX k (a,b); SHOW INDEXES FROM t1;; Table t1 Non_unique 0 Key_name k Seq_in_index 1 Column_name a Collation A Cardinality 0 Sub_part NULL Packed NULL Null Index_type BTREE Comment Index_comment Ignored NO Table t1 Non_unique 0 Key_name k Seq_in_index 2 Column_name b Collation A Cardinality 0 Sub_part NULL Packed NULL Null Index_type BTREE Comment Index_comment Ignored NO DROP TABLE t1; # # Bug #47453: InnoDB incorrectly changes TIMESTAMP columns when # JOINed during an UPDATE # CREATE TABLE t1 (d INT) ENGINE=InnoDB; CREATE TABLE t2 (a INT, b INT, c TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB; # set up our data elements INSERT INTO t1 (d) VALUES (1); INSERT INTO t2 (a,b) VALUES (1,1); SELECT SECOND(c) INTO @bug47453 FROM t2; SELECT SECOND(c)-@bug47453 FROM t1 JOIN t2 ON d=a; SECOND(c)-@bug47453 0 UPDATE t1 JOIN t2 ON d=a SET b=1 WHERE a=1; SELECT SECOND(c)-@bug47453 FROM t1 JOIN t2 ON d=a; SECOND(c)-@bug47453 0 SELECT SLEEP(1); SLEEP(1) 0 UPDATE t1 JOIN t2 ON d=a SET b=1 WHERE a=1; # should be 0 SELECT SECOND(c)-@bug47453 FROM t1 JOIN t2 ON d=a; SECOND(c)-@bug47453 0 DROP TABLE t1, t2; # # Bug #53334: wrong result for outer join with impossible ON condition # (see the same test case for MyISAM in join.test) # CREATE TABLE t1 (id INT PRIMARY KEY); CREATE TABLE t2 (id INT); INSERT INTO t1 VALUES (75); INSERT INTO t1 VALUES (79); INSERT INTO t1 VALUES (78); INSERT INTO t1 VALUES (77); REPLACE INTO t1 VALUES (76); REPLACE INTO t1 VALUES (76); INSERT INTO t1 VALUES (104); INSERT INTO t1 VALUES (103); INSERT INTO t1 VALUES (102); INSERT INTO t1 VALUES (101); INSERT INTO t1 VALUES (105); INSERT INTO t1 VALUES (106); INSERT INTO t1 VALUES (107); INSERT INTO t2 VALUES (107),(75),(1000); SELECT t1.id,t2.id FROM t2 LEFT JOIN t1 ON t1.id>=74 AND t1.id<=0 WHERE t2.id=75 AND t1.id IS NULL; id id NULL 75 EXPLAIN SELECT t1.id,t2.id FROM t2 LEFT JOIN t1 ON t1.id>=74 AND t1.id<=0 WHERE t2.id=75 AND t1.id IS NULL; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY NULL NULL NULL 1 Impossible ON condition 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where DROP TABLE t1,t2; # # Bug#38999 valgrind warnings for update statement in function compare_record() # CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB; INSERT INTO t1 values (1),(2),(3),(4),(5); INSERT INTO t2 values (1); SELECT * FROM t1 WHERE a = 2; a 2 UPDATE t1,t2 SET t1.a = t1.a + 100 WHERE t1.a = 1; DROP TABLE t1,t2; # # Bug #53830: !table || (!table->read_set || bitmap_is_set(table->read_set, field_index)) # CREATE TABLE t1 (a INT, b INT, c INT, d INT, PRIMARY KEY(a,b,c), KEY(b,d)) ENGINE=InnoDB; INSERT INTO t1 VALUES (0, 77, 1, 3); UPDATE t1 SET d = 0 WHERE b = 77 AND c = 25; DROP TABLE t1; # # Bug#50389 Using intersect does not return all rows # CREATE TABLE t1 ( f1 INT(10) NOT NULL, f2 INT(10), f3 INT(10), f4 TINYINT(4), f5 VARCHAR(50), PRIMARY KEY (f1), KEY idx1 (f2,f5,f4), KEY idx2 (f2,f4) ) ENGINE=InnoDB; LOAD DATA INFILE '../../std_data/intersect-bug50389.tsv' INTO TABLE t1; ANALYZE TABLE t1; set @tmp_innodb_mysql= @@optimizer_switch; set optimizer_switch='extended_keys=off'; SELECT * FROM t1 WHERE f1 IN (3305028,3353871,3772880,3346860,4228206,3336022, 3470988,3305175,3329875,3817277,3856380,3796193, 3784744,4180925,4559596,3963734,3856391,4494153) AND f5 = 'abcdefghijklmnopwrst' AND f2 = 1221457 AND f4 = 0 ; f1 f2 f3 f4 f5 3305175 1221457 0 0 abcdefghijklmnopwrst 3329875 1221457 1382427 0 abcdefghijklmnopwrst 3336022 1221457 0 0 abcdefghijklmnopwrst 3346860 1221457 0 0 abcdefghijklmnopwrst 3772880 1221457 0 0 abcdefghijklmnopwrst 3784744 1221457 1382427 0 abcdefghijklmnopwrst 3796193 1221457 0 0 abcdefghijklmnopwrst 4228206 1221457 0 0 abcdefghijklmnopwrst 4494153 1221457 0 0 abcdefghijklmnopwrst 4559596 1221457 0 0 abcdefghijklmnopwrst EXPLAIN SELECT * FROM t1 WHERE f1 IN (3305028,3353871,3772880,3346860,4228206,3336022, 3470988,3305175,3329875,3817277,3856380,3796193, 3784744,4180925,4559596,3963734,3856391,4494153) AND f5 = 'abcdefghijklmnopwrst' AND f2 = 1221457 AND f4 = 0 ; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge PRIMARY,idx1,idx2 idx2,idx1,PRIMARY 7,60,4 NULL 1 Using intersect(idx2,idx1,PRIMARY); Using where set optimizer_switch=@tmp_innodb_mysql; DROP TABLE t1; # # Bug#51431 Wrong sort order after import of dump file # CREATE TABLE t1 ( f1 INT(11) NOT NULL, f2 int(11) NOT NULL, f3 int(11) NOT NULL, f4 tinyint(1) NOT NULL, PRIMARY KEY (f1), UNIQUE KEY (f2, f3), KEY (f4) ) ENGINE=InnoDB STATS_PERSISTENT=0; INSERT INTO t1 VALUES (1,1,991,1), (2,1,992,1), (3,1,993,1), (4,1,994,1), (5,1,995,1), (6,1,996,1), (7,1,997,1), (8,1,998,1), (10,1,999,1), (11,1,9910,1), (16,1,9911,1), (17,1,9912,1), (18,1,9913,1), (19,1,9914,1), (20,1,9915,1), (21,1,9916,1), (22,1,9917,1), (23,1,9918,1), (24,1,9919,1), (25,1,9920,1), (26,1,9921,1), (27,1,9922,1); FLUSH TABLES; SELECT * FROM t1 WHERE f2 = 1 AND f4 = TRUE ORDER BY f1 DESC LIMIT 5; f1 f2 f3 f4 27 1 9922 1 26 1 9921 1 25 1 9920 1 24 1 9919 1 23 1 9918 1 EXPLAIN SELECT * FROM t1 WHERE f2 = 1 AND f4 = TRUE ORDER BY f1 DESC LIMIT 5; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range f2,f4 f4 1 NULL 22 Using where DROP TABLE t1; # # Bug#54117 crash in thr_multi_unlock, temporary table # CREATE TEMPORARY TABLE t1(a INT) ENGINE = InnoDB; LOCK TABLES t1 READ; ALTER TABLE t1 COMMENT 'test'; UNLOCK TABLES; DROP TABLE t1; # # Bug#55656: mysqldump can be slower after bug #39653 fix # CREATE TABLE t1 (a INT , b INT, c INT, d INT, KEY (b), PRIMARY KEY (a,b)) ENGINE=INNODB STATS_PERSISTENT=0; INSERT INTO t1 VALUES (1,1,1,1), (2,2,2,2), (3,3,3,3); EXPLAIN SELECT COUNT(*) FROM t1; id 1 select_type SIMPLE table t1 type index possible_keys NULL key b key_len 4 ref NULL rows 3 Extra Using index DROP INDEX b ON t1; CREATE INDEX b ON t1(a,b); EXPLAIN SELECT COUNT(*) FROM t1; id 1 select_type SIMPLE table t1 type index possible_keys NULL key b key_len 8 ref NULL rows 3 Extra Using index DROP INDEX b ON t1; CREATE INDEX b ON t1(a,b,c); EXPLAIN SELECT COUNT(*) FROM t1; id 1 select_type SIMPLE table t1 type index possible_keys NULL key PRIMARY key_len 8 ref NULL rows 3 Extra Using index DROP INDEX b ON t1; CREATE INDEX b ON t1(a,b,c,d); EXPLAIN SELECT COUNT(*) FROM t1; id 1 select_type SIMPLE table t1 type index possible_keys NULL key PRIMARY key_len 8 ref NULL rows 3 Extra Using index DROP TABLE t1; # # Bug#55826: create table .. select crashes with when KILL_BAD_DATA # is returned # CREATE TABLE t1(a INT) ENGINE=innodb; INSERT INTO t1 VALUES (0); SET SQL_MODE='STRICT_ALL_TABLES'; CREATE TABLE t2 SELECT LEAST((SELECT '' FROM t1),NOW()) FROM `t1`; ERROR 22007: Truncated incorrect datetime value: '' DROP TABLE t1; SET SQL_MODE=DEFAULT; # # Bug#56862 Execution of a query that uses index merge returns a wrong result # CREATE TABLE t1 ( pk int NOT NULL AUTO_INCREMENT PRIMARY KEY, a int, b int, INDEX idx(a)) ENGINE=INNODB; INSERT INTO t1(a,b) VALUES (11, 1100), (2, 200), (1, 100), (14, 1400), (5, 500), (3, 300), (17, 1700), (4, 400), (12, 1200), (8, 800), (6, 600), (18, 1800), (9, 900), (10, 1000), (7, 700), (13, 1300), (15, 1500), (19, 1900), (16, 1600), (20, 2000); INSERT INTO t1(a,b) SELECT a+20, b+2000 FROM t1; INSERT INTO t1(a,b) SELECT a+40, b+4000 FROM t1; INSERT INTO t1(a,b) SELECT a+80, b+8000 FROM t1; INSERT INTO t1(a,b) SELECT a,b FROM t1; INSERT INTO t1(a,b) SELECT a,b FROM t1; INSERT INTO t1(a,b) SELECT a,b FROM t1; INSERT INTO t1(a,b) SELECT a,b FROM t1; INSERT INTO t1(a,b) SELECT a,b FROM t1; INSERT INTO t1(a,b) SELECT a,b FROM t1; INSERT INTO t1(a,b) SELECT a,b FROM t1; INSERT INTO t1(a,b) SELECT a,b FROM t1; INSERT INTO t1 VALUES (1000000, 0, 0); set @optimizer_switch_saved=@@optimizer_switch; SET SESSION optimizer_switch='derived_merge=off'; SET SESSION sort_buffer_size = 1024*36; EXPLAIN SELECT COUNT(*) FROM (SELECT * FROM t1 FORCE INDEX (idx,PRIMARY) WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ALL NULL NULL NULL NULL # 2 DERIVED t1 index_merge PRIMARY,idx idx,PRIMARY 5,4 NULL # Using sort_union(idx,PRIMARY); Using where SELECT COUNT(*) FROM (SELECT * FROM t1 FORCE INDEX (idx,PRIMARY) WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; COUNT(*) 1537 set @@optimizer_switch=@optimizer_switch_saved; SET SESSION sort_buffer_size = DEFAULT; DROP TABLE t1; # # Test for bug #39932 "create table fails if column for FK is in different # case than in corr index". # drop tables if exists t1, t2; create table t1 (pk int primary key) engine=InnoDB; create table t2 (fk int, key x (fk), constraint x foreign key (FK) references t1 (PK)) engine=InnoDB; show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `fk` int(11) DEFAULT NULL, KEY `x` (`fk`), CONSTRAINT `x` FOREIGN KEY (`fk`) REFERENCES `t1` (`pk`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci drop table t2, t1; # # Test for bug #11762012 - "54553: INNODB ASSERTS IN HA_INNOBASE:: # UPDATE_ROW, TEMPORARY TABLE, TABLE LOCK". # DROP TABLE IF EXISTS t1; CREATE TEMPORARY TABLE t1 (c int) ENGINE = InnoDB; INSERT INTO t1 VALUES (1); LOCK TABLES t1 READ; # Even though temporary table was locked for READ we # still allow writes to it to be compatible with MyISAM. # This is possible since due to fact that temporary tables # are specific to connection and therefore locking for them # is irrelevant. UPDATE t1 SET c = 5; UNLOCK TABLES; DROP TEMPORARY TABLE t1; # End of 5.1 tests # # Bug#49604 "6.0 processing compound WHERE clause incorrectly # with Innodb - extra rows" # CREATE TABLE t1 ( c1 INT NOT NULL, c2 INT, PRIMARY KEY (c1), KEY k1 (c2) ) ENGINE=InnoDB; INSERT INTO t1 VALUES (12,1); INSERT INTO t1 VALUES (15,1); INSERT INTO t1 VALUES (16,1); INSERT INTO t1 VALUES (22,1); INSERT INTO t1 VALUES (20,2); CREATE TABLE t2 ( c1 INT NOT NULL, c2 INT, PRIMARY KEY (c1) ) ENGINE=InnoDB; INSERT INTO t2 VALUES (1,2); INSERT INTO t2 VALUES (2,9); SELECT STRAIGHT_JOIN t2.c2, t1.c2, t2.c1 FROM t1 JOIN t2 ON t1.c2 = t2.c1 WHERE t2.c1 IN (2, 1, 6) OR t2.c1 NOT IN (1); c2 c2 c1 2 1 1 2 1 1 2 1 1 2 1 1 9 2 2 DROP TABLE t1, t2; # # Bug#44613 SELECT statement inside FUNCTION takes a shared lock # DROP TABLE IF EXISTS t1; DROP FUNCTION IF EXISTS f1; CREATE TABLE t1(x INT PRIMARY KEY, y INT) ENGINE=innodb; INSERT INTO t1 VALUES (1, 0), (2, 0); CREATE FUNCTION f1(z INT) RETURNS INT READS SQL DATA RETURN (SELECT x FROM t1 WHERE x = z); START TRANSACTION; SELECT f1(1); f1(1) 1 START TRANSACTION; SELECT f1(1); f1(1) 1 UPDATE t1 SET y = 1 WHERE x = 1; COMMIT; disconnect con2; connection default; COMMIT; DROP TABLE t1; DROP FUNCTION f1; # # Bug#42744: Crash when using a join buffer to join a table with a blob # column and an additional column used for duplicate elimination. # CREATE TABLE t1 (a tinyblob) ENGINE=InnoDB; CREATE TABLE t2 (a int PRIMARY KEY, b tinyblob) ENGINE=InnoDB; INSERT INTO t1 VALUES ('1'), (NULL); INSERT INTO t2 VALUES (1, '1'); EXPLAIN SELECT t2.b FROM t1,t2 WHERE t1.a IN (SELECT 1 FROM t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) 1 PRIMARY t2 index NULL PRIMARY 4 NULL 1 Using index; FirstMatch(t1); Using join buffer (incremental, BNL join) SELECT t2.b FROM t1,t2 WHERE t1.a IN (SELECT 1 FROM t2); b 1 DROP TABLE t1,t2; # # Bug#48093: 6.0 Server not processing equivalent IN clauses properly # with Innodb tables # CREATE TABLE t1 ( i int(11) DEFAULT NULL, v1 varchar(1) DEFAULT NULL, v2 varchar(20) DEFAULT NULL, KEY i (i), KEY v (v1,i) ) ENGINE=innodb; INSERT INTO t1 VALUES (1,'f','no'); INSERT INTO t1 VALUES (2,'u','yes-u'); INSERT INTO t1 VALUES (2,'h','yes-h'); INSERT INTO t1 VALUES (3,'d','no'); SELECT v2 FROM t1 WHERE v1 IN ('f', 'd', 'h', 'u' ) AND i = 2; v2 yes-u yes-h # Should not use index_merge EXPLAIN SELECT v2 FROM t1 WHERE v1 IN ('f', 'd', 'h', 'u' ) AND i = 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref i,v i 5 const 2 Using where DROP TABLE t1; # # Bug#54606 innodb fast alter table + pack_keys=0 # prevents adding new indexes # DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a INT, b CHAR(9), c INT, key(b)) ENGINE=InnoDB PACK_KEYS=0; CREATE INDEX a ON t1 (a); CREATE INDEX c on t1 (c); DROP TABLE t1; # # Additional coverage for refactoring which is made as part # of fix for Bug#27480 "Extend CREATE TEMPORARY TABLES privilege # to allow temp table operations". # # Check that OPTIMIZE table works for temporary InnoDB tables. DROP TABLE IF EXISTS t1; CREATE TEMPORARY TABLE t1 (a INT) ENGINE=InnoDB; OPTIMIZE TABLE t1; Table Op Msg_type Msg_text test.t1 optimize note Table does not support optimize, doing recreate + analyze instead test.t1 optimize status OK DROP TABLE t1; # # Bug#11762345 54927: DROPPING AND ADDING AN INDEX IN ONE # COMMAND CAN FAIL IN INNODB PLUGIN 1.0 # DROP TABLE IF EXISTS t1; CREATE TABLE t1 (id int, a int, b int, PRIMARY KEY (id), INDEX a (a)) ENGINE=innodb; ALTER TABLE t1 DROP INDEX a, ADD INDEX a (b, a); ALTER TABLE t1 DROP INDEX a, ADD INDEX (a, b); DROP TABLE t1; End of 6.0 tests