1set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; 2set default_storage_engine=TokuDB; 3create table t1(a int); 4show create table t1; 5Table Create Table 6t1 CREATE TABLE `t1` ( 7 `a` int(11) DEFAULT NULL 8) ENGINE=TokuDB DEFAULT CHARSET=latin1 9insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 10create table t2(a int); 11insert into t2 select A.a + 10*(B.a + 10*C.a) from t1 A, t1 B, t1 C; 12create table t3 ( 13a char(8) not null, b char(8) not null, filler char(200), 14key(a) 15); 16insert into t3 select @a:=concat('c-', 1000+ A.a, '=w'), @a, 'filler' from t2 A; 17insert into t3 select concat('c-', 1000+A.a, '=w'), concat('c-', 2000+A.a, '=w'), 18'filler-1' from t2 A; 19insert into t3 select concat('c-', 1000+A.a, '=w'), concat('c-', 3000+A.a, '=w'), 20'filler-2' from t2 A; 21select a,filler from t3 where a >= 'c-9011=w'; 22a filler 23select a,filler from t3 where a >= 'c-1011=w' and a <= 'c-1015=w'; 24a filler 25c-1011=w filler 26c-1012=w filler 27c-1013=w filler 28c-1014=w filler 29c-1015=w filler 30c-1011=w filler-1 31c-1012=w filler-1 32c-1013=w filler-1 33c-1014=w filler-1 34c-1015=w filler-1 35c-1011=w filler-2 36c-1012=w filler-2 37c-1013=w filler-2 38c-1014=w filler-2 39c-1015=w filler-2 40select a,filler from t3 where (a>='c-1011=w' and a <= 'c-1013=w') or 41(a>='c-1014=w' and a <= 'c-1015=w'); 42a filler 43c-1011=w filler 44c-1012=w filler 45c-1013=w filler 46c-1014=w filler 47c-1015=w filler 48c-1011=w filler-1 49c-1012=w filler-1 50c-1013=w filler-1 51c-1014=w filler-1 52c-1015=w filler-1 53c-1011=w filler-2 54c-1012=w filler-2 55c-1013=w filler-2 56c-1014=w filler-2 57c-1015=w filler-2 58insert into t3 values ('c-1013=z', 'c-1013=z', 'err'); 59insert into t3 values ('a-1014=w', 'a-1014=w', 'err'); 60select a,filler from t3 where (a>='c-1011=w' and a <= 'c-1013=w') or 61(a>='c-1014=w' and a <= 'c-1015=w'); 62a filler 63c-1011=w filler 64c-1012=w filler 65c-1013=w filler 66c-1014=w filler 67c-1015=w filler 68c-1011=w filler-1 69c-1012=w filler-1 70c-1013=w filler-1 71c-1014=w filler-1 72c-1015=w filler-1 73c-1011=w filler-2 74c-1012=w filler-2 75c-1013=w filler-2 76c-1014=w filler-2 77c-1015=w filler-2 78delete from t3 where b in ('c-1013=z', 'a-1014=w'); 79select a,filler from t3 where a='c-1011=w' or a='c-1012=w' or a='c-1013=w' or 80a='c-1014=w' or a='c-1015=w'; 81a filler 82c-1011=w filler 83c-1012=w filler 84c-1013=w filler 85c-1014=w filler 86c-1015=w filler 87c-1011=w filler-1 88c-1012=w filler-1 89c-1013=w filler-1 90c-1014=w filler-1 91c-1015=w filler-1 92c-1011=w filler-2 93c-1012=w filler-2 94c-1013=w filler-2 95c-1014=w filler-2 96c-1015=w filler-2 97insert into t3 values ('c-1013=w', 'del-me', 'inserted'); 98select a,filler from t3 where a='c-1011=w' or a='c-1012=w' or a='c-1013=w' or 99a='c-1014=w' or a='c-1015=w'; 100a filler 101c-1011=w filler 102c-1012=w filler 103c-1013=w filler 104c-1014=w filler 105c-1015=w filler 106c-1011=w filler-1 107c-1012=w filler-1 108c-1013=w filler-1 109c-1014=w filler-1 110c-1015=w filler-1 111c-1011=w filler-2 112c-1012=w filler-2 113c-1013=w filler-2 114c-1014=w filler-2 115c-1015=w filler-2 116c-1013=w inserted 117delete from t3 where b='del-me'; 118alter table t3 add primary key(b); 119select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1018=w') or 120b IN ('c-1019=w', 'c-1020=w', 'c-1021=w', 121'c-1022=w', 'c-1023=w', 'c-1024=w'); 122b filler 123c-1011=w filler 124c-1012=w filler 125c-1013=w filler 126c-1014=w filler 127c-1015=w filler 128c-1016=w filler 129c-1017=w filler 130c-1018=w filler 131c-1019=w filler 132c-1020=w filler 133c-1021=w filler 134c-1022=w filler 135c-1023=w filler 136c-1024=w filler 137select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1020=w') or 138b IN ('c-1021=w', 'c-1022=w', 'c-1023=w'); 139b filler 140c-1011=w filler 141c-1012=w filler 142c-1013=w filler 143c-1014=w filler 144c-1015=w filler 145c-1016=w filler 146c-1017=w filler 147c-1018=w filler 148c-1019=w filler 149c-1020=w filler 150c-1021=w filler 151c-1022=w filler 152c-1023=w filler 153select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1018=w') or 154b IN ('c-1019=w', 'c-1020=w') or 155(b>='c-1021=w' and b<= 'c-1023=w'); 156b filler 157c-1011=w filler 158c-1012=w filler 159c-1013=w filler 160c-1014=w filler 161c-1015=w filler 162c-1016=w filler 163c-1017=w filler 164c-1018=w filler 165c-1019=w filler 166c-1020=w filler 167c-1021=w filler 168c-1022=w filler 169c-1023=w filler 170drop table if exists t4; 171create table t4 (a varchar(10), b int, c char(10), filler char(200), 172key idx1 (a, b, c)); 173insert into t4 (filler) select concat('NULL-', 15-a) from t2 order by a limit 15; 174insert into t4 (a,b,c,filler) 175select 'b-1',NULL,'c-1', concat('NULL-', 15-a) from t2 order by a limit 15; 176insert into t4 (a,b,c,filler) 177select 'b-1',NULL,'c-222', concat('NULL-', 15-a) from t2 order by a limit 15; 178insert into t4 (a,b,c,filler) 179select 'bb-1',NULL,'cc-2', concat('NULL-', 15-a) from t2 order by a limit 15; 180insert into t4 (a,b,c,filler) 181select 'zz-1',NULL,'cc-2', 'filler-data' from t2 order by a limit 500; 182explain 183select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1' 184 or c='no-such-row2'); 185id select_type table type possible_keys key key_len ref rows Extra 1861 SIMPLE t4 range idx1 idx1 29 NULL 16 Using where; Rowid-ordered scan 187select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1' 188 or c='no-such-row2'); 189a b c filler 190NULL NULL NULL NULL-15 191NULL NULL NULL NULL-14 192NULL NULL NULL NULL-13 193NULL NULL NULL NULL-12 194NULL NULL NULL NULL-11 195NULL NULL NULL NULL-10 196NULL NULL NULL NULL-9 197NULL NULL NULL NULL-8 198NULL NULL NULL NULL-7 199NULL NULL NULL NULL-6 200NULL NULL NULL NULL-5 201NULL NULL NULL NULL-4 202NULL NULL NULL NULL-3 203NULL NULL NULL NULL-2 204NULL NULL NULL NULL-1 205explain 206select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2'); 207id select_type table type possible_keys key key_len ref rows Extra 2081 SIMPLE t4 range idx1 idx1 29 NULL 32 Using where; Rowid-ordered scan 209select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2'); 210a b c filler 211b-1 NULL c-1 NULL-15 212b-1 NULL c-1 NULL-14 213b-1 NULL c-1 NULL-13 214b-1 NULL c-1 NULL-12 215b-1 NULL c-1 NULL-11 216b-1 NULL c-1 NULL-10 217b-1 NULL c-1 NULL-9 218b-1 NULL c-1 NULL-8 219b-1 NULL c-1 NULL-7 220b-1 NULL c-1 NULL-6 221b-1 NULL c-1 NULL-5 222b-1 NULL c-1 NULL-4 223b-1 NULL c-1 NULL-3 224b-1 NULL c-1 NULL-2 225b-1 NULL c-1 NULL-1 226bb-1 NULL cc-2 NULL-15 227bb-1 NULL cc-2 NULL-14 228bb-1 NULL cc-2 NULL-13 229bb-1 NULL cc-2 NULL-12 230bb-1 NULL cc-2 NULL-11 231bb-1 NULL cc-2 NULL-10 232bb-1 NULL cc-2 NULL-9 233bb-1 NULL cc-2 NULL-8 234bb-1 NULL cc-2 NULL-7 235bb-1 NULL cc-2 NULL-6 236bb-1 NULL cc-2 NULL-5 237bb-1 NULL cc-2 NULL-4 238bb-1 NULL cc-2 NULL-3 239bb-1 NULL cc-2 NULL-2 240bb-1 NULL cc-2 NULL-1 241select * from t4 ignore index(idx1) where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2'); 242a b c filler 243b-1 NULL c-1 NULL-15 244b-1 NULL c-1 NULL-14 245b-1 NULL c-1 NULL-13 246b-1 NULL c-1 NULL-12 247b-1 NULL c-1 NULL-11 248b-1 NULL c-1 NULL-10 249b-1 NULL c-1 NULL-9 250b-1 NULL c-1 NULL-8 251b-1 NULL c-1 NULL-7 252b-1 NULL c-1 NULL-6 253b-1 NULL c-1 NULL-5 254b-1 NULL c-1 NULL-4 255b-1 NULL c-1 NULL-3 256b-1 NULL c-1 NULL-2 257b-1 NULL c-1 NULL-1 258bb-1 NULL cc-2 NULL-15 259bb-1 NULL cc-2 NULL-14 260bb-1 NULL cc-2 NULL-13 261bb-1 NULL cc-2 NULL-12 262bb-1 NULL cc-2 NULL-11 263bb-1 NULL cc-2 NULL-10 264bb-1 NULL cc-2 NULL-9 265bb-1 NULL cc-2 NULL-8 266bb-1 NULL cc-2 NULL-7 267bb-1 NULL cc-2 NULL-6 268bb-1 NULL cc-2 NULL-5 269bb-1 NULL cc-2 NULL-4 270bb-1 NULL cc-2 NULL-3 271bb-1 NULL cc-2 NULL-2 272bb-1 NULL cc-2 NULL-1 273drop table t1, t2, t3, t4; 274create table t1 (a int, b int not null,unique key (a,b),index(b)); 275insert ignore into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(null,7),(9,9),(8,8),(7,7),(null,9),(null,9),(6,6); 276Warnings: 277Warning 1062 Duplicate entry '6-6' for key 'a' 278create table t2 like t1; 279insert into t2 select * from t1; 280alter table t1 modify b blob not null, add c int not null, drop key a, add unique key (a,b(20),c), drop key b, add key (b(10)); 281select * from t1 where a is null; 282a b c 283NULL 7 0 284NULL 9 0 285NULL 9 0 286select * from t1 where (a is null or a > 0 and a < 3) and b > 7 limit 3; 287a b c 288NULL 9 0 289NULL 9 0 290select * from t1 where a is null and b=9 or a is null and b=7 limit 3; 291a b c 292NULL 7 0 293NULL 9 0 294NULL 9 0 295drop table t1, t2; 296# 297# Bug#41029 "MRR: SELECT FOR UPDATE fails to lock gaps (InnoDB table)" 298# 299connect con1,localhost,root,,; 300connect con2,localhost,root,,; 301connection con1; 302SET AUTOCOMMIT=0; 303CREATE TABLE t1 ( 304dummy INT PRIMARY KEY, 305a INT UNIQUE, 306b INT 307) ENGINE=TokuDB; 308INSERT INTO t1 VALUES (1,1,1),(3,3,3),(5,5,5); 309COMMIT; 310SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; 311SELECT @@tx_isolation; 312@@tx_isolation 313REPEATABLE-READ 314START TRANSACTION; 315EXPLAIN SELECT * FROM t1 WHERE a >= 2 FOR UPDATE; 316id select_type table type possible_keys key key_len ref rows Extra 3171 SIMPLE t1 range a a 5 NULL 2 Using where 318SELECT * FROM t1 WHERE a >= 2 FOR UPDATE; 319dummy a b 3203 3 3 3215 5 5 322connection con2; 323SET AUTOCOMMIT=0; 324SET TOKUDB_LOCK_TIMEOUT=2; 325START TRANSACTION; 326INSERT INTO t1 VALUES (2,2,2); 327ERROR HY000: Lock wait timeout exceeded; try restarting transaction 328ROLLBACK; 329connection con1; 330ROLLBACK; 331DROP TABLE t1; 332connection default; 333disconnect con1; 334disconnect con2; 335