1set global rocksdb_force_flush_memtable_now=1; 2#---------------- Index merge test 1 ------------------------------------------- 3SET SESSION DEFAULT_STORAGE_ENGINE = RocksDB; 4drop table if exists t0, t1, t2, t3, t4; 5create table t0 6( 7key1 int not null, 8key2 int not null, 9key3 int not null, 10key4 int not null, 11key5 int not null, 12key6 int not null, 13key7 int not null, 14key8 int not null, 15INDEX i1(key1), 16INDEX i2(key2), 17INDEX i3(key3), 18INDEX i4(key4), 19INDEX i5(key5), 20INDEX i6(key6), 21INDEX i7(key7), 22INDEX i8(key8) 23); 24analyze table t0; 25Table Op Msg_type Msg_text 26test.t0 analyze status Engine-independent statistics collected 27test.t0 analyze status OK 28explain select * from t0 where key1 < 3 or key1 > 1020; 29id select_type table type possible_keys key key_len ref rows Extra 301 SIMPLE t0 range i1 i1 4 NULL 4 Using index condition 31explain 32select * from t0 where key1 < 3 or key2 > 1020; 33id select_type table type possible_keys key key_len ref rows Extra 341 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 4 Using sort_union(i1,i2); Using where 35select * from t0 where key1 < 3 or key2 > 1020; 36key1 key2 key3 key4 key5 key6 key7 key8 371 1 1 1 1 1 1 1023 382 2 2 2 2 2 2 1022 391021 1021 1021 1021 1021 1021 1021 3 401022 1022 1022 1022 1022 1022 1022 2 411023 1023 1023 1023 1023 1023 1023 1 421024 1024 1024 1024 1024 1024 1024 0 43explain select * from t0 where key1 < 2 or key2 <3; 44id select_type table type possible_keys key key_len ref rows Extra 451 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL # Using sort_union(i1,i2); Using where 46explain 47select * from t0 where (key1 > 30 and key1<35) or (key2 >32 and key2 < 40); 48id select_type table type possible_keys key key_len ref rows Extra 491 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL # Using sort_union(i1,i2); Using where 50select * from t0 where (key1 > 30 and key1<35) or (key2 >32 and key2 < 40); 51key1 key2 key3 key4 key5 key6 key7 key8 5231 31 31 31 31 31 31 993 5332 32 32 32 32 32 32 992 5433 33 33 33 33 33 33 991 5534 34 34 34 34 34 34 990 5635 35 35 35 35 35 35 989 5736 36 36 36 36 36 36 988 5837 37 37 37 37 37 37 987 5938 38 38 38 38 38 38 986 6039 39 39 39 39 39 39 985 61explain select * from t0 ignore index (i2) where key1 < 3 or key2 <4; 62id select_type table type possible_keys key key_len ref rows Extra 631 SIMPLE t0 ALL i1 NULL NULL NULL # Using where 64explain select * from t0 where (key1 < 3 or key2 <4) and key3 = 50; 65id select_type table type possible_keys key key_len ref rows Extra 661 SIMPLE t0 ref i1,i2,i3 i3 4 const # Using where 67explain select * from t0 use index (i1,i2) where (key1 < 2 or key2 <3) and key3 = 50; 68id select_type table type possible_keys key key_len ref rows Extra 691 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL # Using sort_union(i1,i2); Using where 70explain select * from t0 where (key1 > 1 or key2 > 2); 71id select_type table type possible_keys key key_len ref rows Extra 721 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL # Using sort_union(i1,i2); Using where 73explain select * from t0 force index (i1,i2) where (key1 > 1 or key2 > 2); 74id select_type table type possible_keys key key_len ref rows Extra 751 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL # Using sort_union(i1,i2); Using where 76explain 77select * from t0 where key1<2 or key2<3 or (key1>5 and key1<7) or 78(key1>10 and key1<12) or (key2>100 and key2<102); 79id select_type table type possible_keys key key_len ref rows Extra 801 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL # Using sort_union(i1,i2); Using where 81explain select * from t0 where key2 = 45 or key1 <=> null; 82id select_type table type possible_keys key key_len ref rows Extra 831 SIMPLE t0 range i1,i2 i2 4 NULL # Using index condition 84explain select * from t0 where key2 = 45 or key1 is not null; 85id select_type table type possible_keys key key_len ref rows Extra 861 SIMPLE t0 ALL i1,i2 NULL NULL NULL # Using where 87explain select * from t0 where key2 = 45 or key1 is null; 88id select_type table type possible_keys key key_len ref rows Extra 891 SIMPLE t0 ref i2 i2 4 const # 90explain select * from t0 where key2=10 or key3=3 or key4 <=> null; 91id select_type table type possible_keys key key_len ref rows Extra 921 SIMPLE t0 index_merge i2,i3,i4 i2,i3 4,4 NULL # Using union(i2,i3); Using where 93explain select * from t0 where key2=10 or key3=3 or key4 is null; 94id select_type table type possible_keys key key_len ref rows Extra 951 SIMPLE t0 index_merge i2,i3 i2,i3 4,4 NULL # Using union(i2,i3); Using where 96explain select key1 from t0 where (key1 <=> null) or (key2 < 2) or 97(key3=10) or (key4 <=> null); 98id select_type table type possible_keys key key_len ref rows Extra 991 SIMPLE t0 index_merge i1,i2,i3,i4 i2,i3 4,4 NULL # Using sort_union(i2,i3); Using where 100explain select key1 from t0 where (key1 <=> null) or (key1 < 5) or 101(key3=10) or (key4 <=> null); 102id select_type table type possible_keys key key_len ref rows Extra 1031 SIMPLE t0 index_merge i1,i3,i4 i1,i3 4,4 NULL # Using sort_union(i1,i3); Using where 104explain select * from t0 where 105(key1 < 2 or key2 < 2) and (key3 < 3 or key4 < 3) and (key5 < 5 or key6 < 5); 106id select_type table type possible_keys key key_len ref rows Extra 1071 SIMPLE t0 index_merge i1,i2,i3,i4,i5,i6 i1,i2 4,4 NULL # Using sort_union(i1,i2); Using where 108explain 109select * from t0 where (key1 < 2 or key2 < 4) and (key1 < 5 or key3 < 3); 110id select_type table type possible_keys key key_len ref rows Extra 1111 SIMPLE t0 index_merge i1,i2,i3 i1,i2 4,4 NULL # Using sort_union(i1,i2); Using where 112select * from t0 where (key1 < 2 or key2 < 4) and (key1 < 5 or key3 < 3); 113key1 key2 key3 key4 key5 key6 key7 key8 1141 1 1 1 1 1 1 1023 1152 2 2 2 2 2 2 1022 1163 3 3 3 3 3 3 1021 117explain select * from t0 where 118(key1 < 3 or key2 < 2) and (key3 < 3 or key4 < 3) and (key5 < 2 or key6 < 2); 119id select_type table type possible_keys key key_len ref rows Extra 1201 SIMPLE t0 index_merge i1,i2,i3,i4,i5,i6 i1,i2 4,4 NULL # Using sort_union(i1,i2); Using where 121explain select * from t0 where 122(key1 < 3 or key2 < 3) and (key3 < 70); 123id select_type table type possible_keys key key_len ref rows Extra 1241 SIMPLE t0 range i1,i2,i3 i3 4 NULL # Using index condition; Using where 125explain select * from t0 where 126(key1 < 3 or key2 < 3) and (key3 < 1000); 127id select_type table type possible_keys key key_len ref rows Extra 1281 SIMPLE t0 range i1,i2,i3 i3 4 NULL # Using index condition; Using where 129explain select * from t0 where 130((key1 < 3 or key2 < 3) and (key2 <4 or key3 < 3)) 131or 132key2 > 4; 133id select_type table type possible_keys key key_len ref rows Extra 1341 SIMPLE t0 index_merge i1,i2,i3 i1,i2 4,4 NULL # Using sort_union(i1,i2); Using where 135explain select * from t0 where 136((key1 < 4 or key2 < 4) and (key2 <4 or key3 < 3)) 137or 138key1 < 5; 139id select_type table type possible_keys key key_len ref rows Extra 1401 SIMPLE t0 index_merge i1,i2,i3 i1,i2 4,4 NULL # Using sort_union(i1,i2); Using where 141select * from t0 where 142((key1 < 4 or key2 < 4) and (key2 <4 or key3 < 3)) 143or 144key1 < 5; 145key1 key2 key3 key4 key5 key6 key7 key8 1461 1 1 1 1 1 1 1023 1472 2 2 2 2 2 2 1022 1483 3 3 3 3 3 3 1021 1494 4 4 4 4 4 4 1020 150explain select * from t0 where 151((key1 < 2 or key2 < 2) and (key3 <4 or key5 < 3)) 152or 153((key5 < 3 or key6 < 3) and (key7 <3 or key8 < 3)); 154id select_type table type possible_keys key key_len ref rows Extra 1551 SIMPLE t0 index_merge i1,i2,i3,i5,i6,i7,i8 i1,i2,i5,i6 4,4,4,4 NULL # Using sort_union(i1,i2,i5,i6); Using where 156explain select * from t0 where 157((key3 <3 or key5 < 4) and (key1 < 3 or key2 < 3)) 158or 159((key7 <5 or key8 < 3) and (key5 < 4 or key6 < 4)); 160id select_type table type possible_keys key key_len ref rows Extra 1611 SIMPLE t0 index_merge i1,i2,i3,i5,i6,i7,i8 i3,i5,i7,i8 4,4,4,4 NULL # Using sort_union(i3,i5,i7,i8); Using where 162explain select * from t0 where 163((key3 <3 or key5 < 4) and (key1 < 3 or key2 < 4)) 164or 165((key3 <4 or key5 < 2) and (key5 < 5 or key6 < 3)); 166id select_type table type possible_keys key key_len ref rows Extra 1671 SIMPLE t0 index_merge i1,i2,i3,i5,i6 i3,i5 4,4 NULL # Using sort_union(i3,i5); Using where 168explain select * from t0 where 169((key3 <4 or key5 < 3) and (key1 < 3 or key2 < 3)) 170or 171(((key3 <5 and key7 < 5) or key5 < 2) and (key5 < 4 or key6 < 4)); 172id select_type table type possible_keys key key_len ref rows Extra 1731 SIMPLE t0 index_merge i1,i2,i3,i5,i6,i7 i3,i5 4,4 NULL # Using sort_union(i3,i5); Using where 174explain select * from t0 where 175((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4)) 176or 177((key3 >5 or key5 < 2) and (key5 < 5 or key6 < 6)); 178id select_type table type possible_keys key key_len ref rows Extra 1791 SIMPLE t0 index_merge i1,i2,i3,i5,i6 i3,i5 4,4 NULL # Using sort_union(i3,i5); Using where 180explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where 181((key3 <3 or key5 < 4) and (key1 < 3 or key2 < 3)) 182or 183((key3 >4 or key5 < 2) and (key5 < 5 or key6 < 4)); 184id select_type table type possible_keys key key_len ref rows Extra 1851 SIMPLE t0 index_merge i1,i2,i3,i5,i6 i3,i5 4,4 NULL # Using sort_union(i3,i5); Using where 186explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where 187((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4)) 188or 189((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6)); 190id select_type table type possible_keys key key_len ref rows Extra 1911 SIMPLE t0 index_merge i1,i2,i3,i5,i6 i3,i5 0,4 NULL # Using sort_union(i3,i5); Using where 192select * from t0 where key1 < 3 or key8 < 2 order by key1; 193key1 key2 key3 key4 key5 key6 key7 key8 1941 1 1 1 1 1 1 1023 1952 2 2 2 2 2 2 1022 1961023 1023 1023 1023 1023 1023 1023 1 1971024 1024 1024 1024 1024 1024 1024 0 198explain 199select * from t0 where key1 < 3 or key8 < 2 order by key1; 200id select_type table type possible_keys key key_len ref rows Extra 2011 SIMPLE t0 index_merge i1,i8 i1,i8 4,4 NULL # Using sort_union(i1,i8); Using where; Using filesort 202create table t2 like t0; 203insert into t2 select * from t0; 204alter table t2 add index i1_3(key1, key3); 205alter table t2 add index i2_3(key2, key3); 206alter table t2 drop index i1; 207alter table t2 drop index i2; 208alter table t2 add index i321(key3, key2, key1); 209explain select key3 from t2 where key1 = 100 or key2 = 100; 210id select_type table type possible_keys key key_len ref rows Extra 2111 SIMPLE t2 index_merge i1_3,i2_3 i1_3,i2_3 4,4 NULL # Using sort_union(i1_3,i2_3); Using where 212explain select key3 from t2 where key1 <100 or key2 < 100; 213id select_type table type possible_keys key key_len ref rows Extra 2141 SIMPLE t2 index_merge i1_3,i2_3 i1_3,i2_3 4,4 NULL # Using sort_union(i1_3,i2_3); Using where 215explain select key7 from t2 where key1 <100 or key2 < 100; 216id select_type table type possible_keys key key_len ref rows Extra 2171 SIMPLE t2 index_merge i1_3,i2_3 i1_3,i2_3 4,4 NULL # Using sort_union(i1_3,i2_3); Using where 218create table t4 ( 219key1a int not null, 220key1b int not null, 221key2 int not null, 222key2_1 int not null, 223key2_2 int not null, 224key3 int not null, 225index i1a (key1a, key1b), 226index i1b (key1b, key1a), 227index i2_1(key2, key2_1), 228index i2_2(key2, key2_1) 229); 230Warnings: 231Note 1831 Duplicate index `i2_2`. This is deprecated and will be disallowed in a future release 232insert into t4 select key1,key1,key1 div 10, key1 % 10, key1 % 10, key1 from t0; 233select * from t4 where key1a = 3 or key1b = 4; 234key1a key1b key2 key2_1 key2_2 key3 2353 3 0 3 3 3 2364 4 0 4 4 4 237explain select * from t4 where key1a = 3 or key1b = 4; 238id select_type table type possible_keys key key_len ref rows Extra 2391 SIMPLE t4 index_merge i1a,i1b i1a,i1b 4,4 NULL 4 Using sort_union(i1a,i1b); Using where 240explain select * from t4 where key2 = 1 and (key2_1 = 1 or key3 = 5); 241id select_type table type possible_keys key key_len ref rows Extra 2421 SIMPLE t4 ref i2_1,i2_2 i2_1 4 const 2 Using where 243explain select * from t4 where key2 = 1 and (key2_1 = 1 or key2_2 = 5); 244id select_type table type possible_keys key key_len ref rows Extra 2451 SIMPLE t4 ref i2_1,i2_2 i2_1 4 const 2 Using where 246explain select * from t4 where key2_1 = 1 or key2_2 = 5; 247id select_type table type possible_keys key key_len ref rows Extra 2481 SIMPLE t4 ALL NULL NULL NULL NULL # Using where 249create table t1 like t0; 250insert into t1 select * from t0; 251explain select * from t0 left join t1 on (t0.key1=t1.key1) 252where t0.key1=3 or t0.key2=4; 253id select_type table type possible_keys key key_len ref rows Extra 2541 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL # Using union(i1,i2); Using where 2551 SIMPLE t1 ref i1 i1 4 test.t0.key1 # 256select * from t0 left join t1 on (t0.key1=t1.key1) 257where t0.key1=3 or t0.key2=4; 258key1 key2 key3 key4 key5 key6 key7 key8 key1 key2 key3 key4 key5 key6 key7 key8 2593 3 3 3 3 3 3 1021 3 3 3 3 3 3 3 1021 2604 4 4 4 4 4 4 1020 4 4 4 4 4 4 4 1020 261explain 262select * from t0,t1 where (t0.key1=t1.key1) and ( t0.key1=3 or t0.key2=4); 263id select_type table type possible_keys key key_len ref rows Extra 2641 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL # Using union(i1,i2); Using where 2651 SIMPLE t1 ref i1 i1 4 test.t0.key1 # 266explain 267select * from t0,t1 where (t0.key1=t1.key1) and 268(t0.key1=3 or t0.key2<4) and t1.key1=2; 269id select_type table type possible_keys key key_len ref rows Extra 2701 SIMPLE t0 ref i1,i2 i1 4 const 2 Using where 2711 SIMPLE t1 ref i1 i1 4 const 2 272explain select * from t0,t1 where t0.key1 = 5 and 273(t1.key1 = t0.key1 or t1.key8 = t0.key1); 274id select_type table type possible_keys key key_len ref rows Extra 2751 SIMPLE t0 ref i1 i1 4 const 2 2761 SIMPLE t1 index_merge i1,i8 i1,i8 4,4 NULL 4 Using union(i1,i8); Using where; Using join buffer (flat, BNL join) 277explain select * from t0,t1 where t0.key1 < 3 and 278(t1.key1 = t0.key1 or t1.key8 = t0.key1); 279id select_type table type possible_keys key key_len ref rows Extra 2801 SIMPLE t0 range i1 i1 4 NULL # Using index condition 2811 SIMPLE t1 ALL i1,i8 NULL NULL NULL # Range checked for each record (index map: 0x81) 282explain select * from t1 where key1=3 or key2=4 283union select * from t1 where key1<4 or key3=5; 284id select_type table type possible_keys key key_len ref rows Extra 2851 PRIMARY t1 index_merge i1,i2 i1,i2 4,4 NULL 4 Using union(i1,i2); Using where 2862 UNION t1 index_merge i1,i3 i1,i3 4,4 NULL 4 Using sort_union(i1,i3); Using where 287NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL 288explain select * from (select * from t1 where key1 = 3 or key2 =3) as Z where key8 >5; 289id select_type table type possible_keys key key_len ref rows Extra 2901 SIMPLE t1 range i1,i2,i8 i8 4 NULL 2 Using index condition; Using where 291create table t3 like t0; 292insert into t3 select * from t0; 293alter table t3 add key9 int not null, add index i9(key9); 294alter table t3 add keyA int not null, add index iA(keyA); 295alter table t3 add keyB int not null, add index iB(keyB); 296alter table t3 add keyC int not null, add index iC(keyC); 297update t3 set key9=key1,keyA=key1,keyB=key1,keyC=key1; 298explain select * from t3 where 299key1=1 or key2=2 or key3=3 or key4=4 or 300key5=5 or key6=6 or key7=7 or key8=8 or 301key9=9 or keyA=10 or keyB=11 or keyC=12; 302id select_type table type possible_keys key key_len ref rows Extra 3031 SIMPLE t3 index_merge i1,i2,i3,i4,i5,i6,i7,i8,i9,iA,iB,iC i1,i2,i3,i4,i5,i6,i7,i8,i9,iA,iB,iC 4,4,4,4,4,4,4,4,4,4,4,4 NULL 24 Using union(i1,i2,i3,i4,i5,i6,i7,i8,i9,iA,iB,iC); Using where 304select * from t3 where 305key1=1 or key2=2 or key3=3 or key4=4 or 306key5=5 or key6=6 or key7=7 or key8=8 or 307key9=9 or keyA=10 or keyB=11 or keyC=12; 308key1 key2 key3 key4 key5 key6 key7 key8 key9 keyA keyB keyC 3091 1 1 1 1 1 1 1023 1 1 1 1 3102 2 2 2 2 2 2 1022 2 2 2 2 3113 3 3 3 3 3 3 1021 3 3 3 3 3124 4 4 4 4 4 4 1020 4 4 4 4 3135 5 5 5 5 5 5 1019 5 5 5 5 3146 6 6 6 6 6 6 1018 6 6 6 6 3157 7 7 7 7 7 7 1017 7 7 7 7 3169 9 9 9 9 9 9 1015 9 9 9 9 31710 10 10 10 10 10 10 1014 10 10 10 10 31811 11 11 11 11 11 11 1013 11 11 11 11 31912 12 12 12 12 12 12 1012 12 12 12 12 3201016 1016 1016 1016 1016 1016 1016 8 1016 1016 1016 1016 321explain select * from t0 where key1 < 3 or key2 < 4; 322id select_type table type possible_keys key key_len ref rows Extra 3231 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 4 Using sort_union(i1,i2); Using where 324select * from t0 where key1 < 3 or key2 < 4; 325key1 key2 key3 key4 key5 key6 key7 key8 3261 1 1 1 1 1 1 1023 3272 2 2 2 2 2 2 1022 3283 3 3 3 3 3 3 1021 329update t0 set key8=123 where key1 < 3 or key2 < 4; 330select * from t0 where key1 < 3 or key2 < 4; 331key1 key2 key3 key4 key5 key6 key7 key8 3321 1 1 1 1 1 1 123 3332 2 2 2 2 2 2 123 3343 3 3 3 3 3 3 123 335delete from t0 where key1 < 3 or key2 < 4; 336select * from t0 where key1 < 3 or key2 < 4; 337key1 key2 key3 key4 key5 key6 key7 key8 338select count(*) from t0; 339count(*) 3401021 341drop table t4; 342create table t4 (a int); 343insert into t4 values (1),(4),(3); 344set @save_join_buffer_size=@@join_buffer_size; 345set join_buffer_size= 4096; 346explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) 347from t0 as A force index(i1,i2), t0 as B force index (i1,i2) 348where (A.key1 < 500000 or A.key2 < 3) 349and (B.key1 < 500000 or B.key2 < 3); 350id select_type table type possible_keys key key_len ref rows Extra 3511 SIMPLE A index_merge i1,i2 i1,i2 4,4 NULL # Using sort_union(i1,i2); Using where 3521 SIMPLE B index_merge i1,i2 i1,i2 4,4 NULL # Using sort_union(i1,i2); Using where; Using join buffer (flat, BNL join) 353select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) 354from t0 as A force index(i1,i2), t0 as B force index (i1,i2) 355where (A.key1 < 500000 or A.key2 < 3) 356and (B.key1 < 500000 or B.key2 < 3); 357max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) 35810240 359update t0 set key1=1; 360explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) 361from t0 as A force index(i1,i2), t0 as B force index (i1,i2) 362where (A.key1 = 1 or A.key2 = 1) 363and (B.key1 = 1 or B.key2 = 1); 364id select_type table type possible_keys key key_len ref rows Extra 3651 SIMPLE A index_merge i1,i2 i1,i2 4,4 NULL # Using union(i1,i2); Using where 3661 SIMPLE B index_merge i1,i2 i1,i2 4,4 NULL # Using union(i1,i2); Using where; Using join buffer (flat, BNL join) 367select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) 368from t0 as A force index(i1,i2), t0 as B force index (i1,i2) 369where (A.key1 = 1 or A.key2 = 1) 370and (B.key1 = 1 or B.key2 = 1); 371max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) 3728194 373alter table t0 add filler1 char(200), add filler2 char(200), add filler3 char(200); 374update t0 set key2=1, key3=1, key4=1, key5=1,key6=1,key7=1 where key7 < 500; 375select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) 376from t0 as A, t0 as B 377where (A.key1 = 1 and A.key2 = 1 and A.key3 = 1 and A.key4=1 and A.key5=1 and A.key6=1 and A.key7 = 1 or A.key8=1) 378and (B.key1 = 1 and B.key2 = 1 and B.key3 = 1 and B.key4=1 and B.key5=1 and B.key6=1 and B.key7 = 1 or B.key8=1); 379max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) 3808186 381set join_buffer_size= @save_join_buffer_size; 382drop table t0, t1, t2, t3, t4; 383CREATE TABLE t1 ( 384cola char(3) not null, colb char(3) not null, filler char(200), 385key(cola), key(colb) 386); 387INSERT INTO t1 VALUES ('foo','bar', 'ZZ'),('fuz','baz', 'ZZ'); 388OPTIMIZE TABLE t1; 389Table Op Msg_type Msg_text 390test.t1 optimize status OK 391select count(*) from t1; 392count(*) 3938704 394explain select * from t1 WHERE cola = 'foo' AND colb = 'bar'; 395id select_type table type possible_keys key key_len ref rows Extra 3961 SIMPLE t1 ref cola,colb cola 3 const # Using index condition; Using where 397explain select * from t1 force index(cola,colb) WHERE cola = 'foo' AND colb = 'bar'; 398id select_type table type possible_keys key key_len ref rows Extra 3991 SIMPLE t1 ref cola,colb cola 3 const # Using index condition; Using where 400drop table t1; 401CREATE TABLE t1(a INT); 402INSERT INTO t1 VALUES(1); 403CREATE TABLE t2(a INT, b INT, dummy CHAR(16) DEFAULT '', KEY(a), KEY(b)); 404INSERT INTO t2(a,b) VALUES 405(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 406(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 407(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 408(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 409(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 410(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 411(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 412(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 413(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 414(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 415(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 416(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 417(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 418(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 419(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 420(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 421(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 422(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 423(1,2); 424LOCK TABLES t1 WRITE, t2 WRITE; 425INSERT INTO t2(a,b) VALUES(1,2); 426SELECT t2.a FROM t1,t2 WHERE t2.b=2 AND t2.a=1; 427a 4281 4291 430UNLOCK TABLES; 431DROP TABLE t1, t2; 432CREATE TABLE `t1` ( 433`a` int(11) DEFAULT NULL, 434`filler` char(200) DEFAULT NULL, 435`b` int(11) DEFAULT NULL, 436KEY `a` (`a`), 437KEY `b` (`b`) 438) ENGINE=MEMORY DEFAULT CHARSET=latin1; 439insert into t1 values 440(0, 'filler', 0), (1, 'filler', 1), (2, 'filler', 2), (3, 'filler', 3), 441(4, 'filler', 4), (5, 'filler', 5), (6, 'filler', 6), (7, 'filler', 7), 442(8, 'filler', 8), (9, 'filler', 9), (0, 'filler', 0), (1, 'filler', 1), 443(2, 'filler', 2), (3, 'filler', 3), (4, 'filler', 4), (5, 'filler', 5), 444(6, 'filler', 6), (7, 'filler', 7), (8, 'filler', 8), (9, 'filler', 9), 445(10, 'filler', 10), (11, 'filler', 11), (12, 'filler', 12), (13, 'filler', 13), 446(14, 'filler', 14), (15, 'filler', 15), (16, 'filler', 16), (17, 'filler', 17), 447(18, 'filler', 18), (19, 'filler', 19), (4, '5 ', 0), (5, '4 ', 0), 448(4, '4 ', 0), (4, 'qq ', 5), (5, 'qq ', 4), (4, 'zz ', 4); 449create table t2( 450`a` int(11) DEFAULT NULL, 451`filler` char(200) DEFAULT NULL, 452`b` int(11) DEFAULT NULL, 453KEY USING BTREE (`a`), 454KEY USING BTREE (`b`) 455) ENGINE=MEMORY DEFAULT CHARSET=latin1; 456insert into t2 select * from t1; 457must use sort-union rather than union: 458explain select * from t1 where a=4 or b=4; 459id select_type table type possible_keys key key_len ref rows Extra 4601 SIMPLE t1 index_merge a,b a,b 5,5 NULL # Using sort_union(a,b); Using where 461select * from t1 where a=4 or b=4; 462a filler b 4634 4 0 4644 5 0 4654 filler 4 4664 filler 4 4674 qq 5 4684 zz 4 4695 qq 4 470select * from t1 ignore index(a,b) where a=4 or b=4; 471a filler b 4724 4 0 4734 5 0 4744 filler 4 4754 filler 4 4764 qq 5 4774 zz 4 4785 qq 4 479must use union, not sort-union: 480explain select * from t2 where a=4 or b=4; 481id select_type table type possible_keys key key_len ref rows Extra 4821 SIMPLE t2 index_merge a,b a,b 5,5 NULL # Using union(a,b); Using where 483select * from t2 where a=4 or b=4; 484a filler b 4854 4 0 4864 5 0 4874 filler 4 4884 filler 4 4894 qq 5 4904 zz 4 4915 qq 4 492drop table t1, t2; 493CREATE TABLE t1 (a varchar(8), b set('a','b','c','d','e','f','g','h'), 494KEY b(b), KEY a(a)); 495INSERT INTO t1 VALUES ('y',''), ('z',''); 496SELECT b,a from t1 WHERE (b!='c' AND b!='f' && b!='h') OR 497(a='pure-S') OR (a='DE80337a') OR (a='DE80799'); 498b a 499 y 500 z 501DROP TABLE t1; 502# 503# BUG#40974: Incorrect query results when using clause evaluated using range check 504# 505create table t0 (a int); 506insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 507create table t1 (a int); 508insert into t1 values (1),(2); 509create table t2(a int, b int); 510insert into t2 values (1,1), (2, 1000); 511create table t3 (a int, b int, filler char(100), key(a), key(b)); 512insert into t3 select 1000, 1000,'filler' from t0 A, t0 B, t0 C; 513insert into t3 values (1,1,'data'); 514insert into t3 values (1,1,'data'); 515The plan should be ALL/ALL/ALL(Range checked for each record (index map: 0x3) 516explain select * from t1 517where exists (select 1 from t2, t3 518where t2.a=t1.a and (t3.a=t2.b or t3.b=t2.b or t3.b=t2.b+1)); 519id select_type table type possible_keys key key_len ref rows Extra 5201 PRIMARY t1 ALL NULL NULL NULL NULL # 5211 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func # 5222 MATERIALIZED t2 ALL NULL NULL NULL NULL # 5232 MATERIALIZED t3 ALL a,b NULL NULL NULL # Range checked for each record (index map: 0x3) 524select * from t1 525where exists (select 1 from t2, t3 526where t2.a=t1.a and (t3.a=t2.b or t3.b=t2.b or t3.b=t2.b+1)); 527a 5281 5292 530drop table t0, t1, t2, t3; 531# 532# BUG#44810: index merge and order by with low sort_buffer_size 533# crashes server! 534# 535CREATE TABLE t1(a VARCHAR(128),b VARCHAR(128),KEY(A),KEY(B)); 536INSERT INTO t1 VALUES (REPEAT('a',128),REPEAT('b',128)); 537INSERT INTO t1 SELECT * FROM t1; 538INSERT INTO t1 SELECT * FROM t1; 539INSERT INTO t1 SELECT * FROM t1; 540INSERT INTO t1 SELECT * FROM t1; 541INSERT INTO t1 SELECT * FROM t1; 542INSERT INTO t1 SELECT * FROM t1; 543EXPLAIN 544SELECT * FROM t1 FORCE INDEX(a,b) WHERE a LIKE 'a%' OR b LIKE 'b%' 545ORDER BY a,b; 546id select_type table type possible_keys key key_len ref rows Extra 5471 SIMPLE t1 index_merge a,b a,b 131,131 NULL # Using sort_union(a,b); Using where; Using filesort 548SELECT * FROM t1 FORCE INDEX(a,b) WHERE a LIKE 'a%' OR b LIKE 'b%' 549ORDER BY a,b; 550SET SESSION sort_buffer_size=DEFAULT; 551DROP TABLE t1; 552End of 5.0 tests 553set global rocksdb_force_flush_memtable_now=1; 554#---------------- ROR-index_merge tests ----------------------- 555SET SESSION DEFAULT_STORAGE_ENGINE = RocksDB; 556drop table if exists t0,t1,t2; 557create table t1 558( 559/* Field names reflect value(rowid) distribution, st=STairs, swt= SaWTooth */ 560st_a int not null default 0, 561swt1a int not null default 0, 562swt2a int not null default 0, 563st_b int not null default 0, 564swt1b int not null default 0, 565swt2b int not null default 0, 566/* fields/keys for row retrieval tests */ 567key1 int, 568key2 int, 569key3 int, 570key4 int, 571/* make rows much bigger then keys */ 572filler1 char (200), 573filler2 char (200), 574filler3 char (200), 575filler4 char (200), 576filler5 char (200), 577filler6 char (200), 578/* order of keys is important */ 579key sta_swt12a(st_a,swt1a,swt2a), 580key sta_swt1a(st_a,swt1a), 581key sta_swt2a(st_a,swt2a), 582key sta_swt21a(st_a,swt2a,swt1a), 583key st_a(st_a), 584key stb_swt1a_2b(st_b,swt1b,swt2a), 585key stb_swt1b(st_b,swt1b), 586key st_b(st_b), 587key(key1), 588key(key2), 589key(key3), 590key(key4) 591) ; 592create table t0 as select * from t1; 593# Printing of many insert into t0 values (....) disabled. 594alter table t1 disable keys; 595Warnings: 596Note 1031 Storage engine ROCKSDB of the table `test`.`t1` doesn't have this option 597# Printing of many insert into t1 select .... from t0 disabled. 598# Printing of many insert into t1 (...) values (....) disabled. 599alter table t1 enable keys; 600Warnings: 601Note 1031 Storage engine ROCKSDB of the table `test`.`t1` doesn't have this option 602select count(*) from t1; 603count(*) 60464801 605explain select key1,key2 from t1 where key1=100 and key2=100; 606id select_type table type possible_keys key key_len ref rows Extra 6071 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL # Using intersect(key1,key2); Using where; Using index 608select key1,key2 from t1 where key1=100 and key2=100; 609key1 key2 610100 100 611select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100; 612key1 key2 key3 key4 filler1 613100 100 100 100 key1-key2-key3-key4 614insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, -1, -1, 'key1-key2'); 615insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 100, 100, 'key4-key3'); 616select key1,key2,filler1 from t1 where key1=100 and key2=100; 617key1 key2 filler1 618100 100 key1-key2-key3-key4 619100 100 key1-key2 620select key1,key2 from t1 where key1=100 and key2=100; 621key1 key2 622100 100 623100 100 624select key1,key2,key3,key4 from t1 where key1=100 and key2=100 or key3=100 and key4=100; 625key1 key2 key3 key4 626100 100 100 100 627100 100 -1 -1 628-1 -1 100 100 629select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100; 630key1 key2 key3 key4 filler1 631100 100 100 100 key1-key2-key3-key4 632100 100 -1 -1 key1-key2 633-1 -1 100 100 key4-key3 634select key1,key2,key3 from t1 where key1=100 and key2=100 and key3=100; 635key1 key2 key3 636100 100 100 637insert into t1 (key1,key2,key3,key4,filler1) values (101,101,101,101, 'key1234-101'); 638select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=101; 639key1 key2 key3 key4 filler1 640100 100 100 100 key1-key2-key3-key4 641100 100 -1 -1 key1-key2 642101 101 101 101 key1234-101 643select key1,key2, filler1 from t1 where key1=100 and key2=100; 644key1 key2 filler1 645100 100 key1-key2-key3-key4 646100 100 key1-key2 647update t1 set filler1='to be deleted' where key1=100 and key2=100; 648update t1 set key1=200,key2=200 where key1=100 and key2=100; 649delete from t1 where key1=200 and key2=200; 650select key1,key2,filler1 from t1 where key2=100 and key2=200; 651key1 key2 filler1 652select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100; 653key1 key2 key3 key4 filler1 654-1 -1 100 100 key4-key3 655delete from t1 where key3=100 and key4=100; 656select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100; 657key1 key2 key3 key4 filler1 658select key1,key2 from t1 where key1=100 and key2=100; 659key1 key2 660insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-1'); 661insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-2'); 662insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-3'); 663select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200; 664key1 key2 key3 key4 filler1 665100 100 200 200 key1-key2-key3-key4-1 666100 100 200 200 key1-key2-key3-key4-2 667100 100 200 200 key1-key2-key3-key4-3 668insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, -1, 200,'key4'); 669select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200; 670key1 key2 key3 key4 filler1 671100 100 200 200 key1-key2-key3-key4-1 672100 100 200 200 key1-key2-key3-key4-2 673100 100 200 200 key1-key2-key3-key4-3 674-1 -1 -1 200 key4 675insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 200, -1,'key3'); 676select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200; 677key1 key2 key3 key4 filler1 678100 100 200 200 key1-key2-key3-key4-1 679100 100 200 200 key1-key2-key3-key4-2 680100 100 200 200 key1-key2-key3-key4-3 681-1 -1 -1 200 key4 682-1 -1 200 -1 key3 683drop table t0,t1; 684create table t2 ( 685a char(10), 686b char(10), 687filler1 char(255), 688filler2 char(255), 689key(a(5)), 690key(b(5)) 691); 692select count(a) from t2 where a='BBBBBBBB'; 693count(a) 6944 695select count(a) from t2 where b='BBBBBBBB'; 696count(a) 6974 698expla_or_bin select count(a_or_b) from t2 where a_or_b='AAAAAAAA' a_or_bnd a_or_b='AAAAAAAA'; 699id select_type ta_or_ba_or_ble type possia_or_ble_keys key key_len ref rows Extra_or_b 7001 SIMPLE t2 ref a_or_b,a_or_b a_or_b 6 const 2 Using where 701select count(a) from t2 where a='AAAAAAAA' and b='AAAAAAAA'; 702count(a) 7034 704select count(a) from t2 ignore index(a,b) where a='AAAAAAAA' and b='AAAAAAAA'; 705count(a) 7064 707insert into t2 values ('ab', 'ab', 'uh', 'oh'); 708explain select a from t2 where a='ab'; 709id select_type table type possible_keys key key_len ref rows Extra 7101 SIMPLE t2 ref a a 6 const 2 Using where 711drop table t2; 712CREATE TABLE t1(c1 INT, c2 INT DEFAULT 0, c3 CHAR(255) DEFAULT '', 713KEY(c1), KEY(c2), KEY(c3)); 714INSERT INTO t1(c1) VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0), 715(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0); 716INSERT INTO t1 VALUES(0,0,0); 717CREATE TABLE t2(c1 int); 718INSERT INTO t2 VALUES(1); 719DELETE t1 FROM t1,t2 WHERE t1.c1=0 AND t1.c2=0; 720SELECT * FROM t1; 721c1 c2 c3 722DROP TABLE t1,t2; 723set global rocksdb_force_flush_memtable_now=1; 724#---------------- Index merge test 2 ------------------------------------------- 725SET SESSION DEFAULT_STORAGE_ENGINE = RocksDB; 726drop table if exists t1,t2; 727create table t1 728( 729key1 int not null, 730key2 int not null, 731INDEX i1(key1), 732INDEX i2(key2) 733); 734explain select * from t1 where key1 < 5 or key2 > 197; 735id select_type table type possible_keys key key_len ref rows Extra 7361 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL 4 Using sort_union(i1,i2); Using where 737select * from t1 where key1 < 5 or key2 > 197; 738key1 key2 7390 200 7401 199 7412 198 7423 197 7434 196 744explain select * from t1 where key1 < 3 or key2 > 195; 745id select_type table type possible_keys key key_len ref rows Extra 7461 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL 4 Using sort_union(i1,i2); Using where 747select * from t1 where key1 < 3 or key2 > 195; 748key1 key2 7490 200 7501 199 7512 198 7523 197 7534 196 754alter table t1 add str1 char (255) not null, 755add zeroval int not null default 0, 756add str2 char (255) not null, 757add str3 char (255) not null; 758update t1 set str1='aaa', str2='bbb', str3=concat(key2, '-', key1 div 2, '_' ,if(key1 mod 2 = 0, 'a', 'A')); 759alter table t1 add primary key (str1, zeroval, str2, str3); 760explain select * from t1 where key1 < 5 or key2 > 197; 761id select_type table type possible_keys key key_len ref rows Extra 7621 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL 4 Using sort_union(i1,i2); Using where 763select * from t1 where key1 < 5 or key2 > 197; 764key1 key2 str1 zeroval str2 str3 7654 196 aaa 0 bbb 196-2_a 7663 197 aaa 0 bbb 197-1_A 7672 198 aaa 0 bbb 198-1_a 7681 199 aaa 0 bbb 199-0_A 7690 200 aaa 0 bbb 200-0_a 770explain select * from t1 where key1 < 3 or key2 > 195; 771id select_type table type possible_keys key key_len ref rows Extra 7721 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL 4 Using sort_union(i1,i2); Using where 773select * from t1 where key1 < 3 or key2 > 195; 774key1 key2 str1 zeroval str2 str3 7754 196 aaa 0 bbb 196-2_a 7763 197 aaa 0 bbb 197-1_A 7772 198 aaa 0 bbb 198-1_a 7781 199 aaa 0 bbb 199-0_A 7790 200 aaa 0 bbb 200-0_a 780drop table t1; 781create table t1 ( 782pk integer not null auto_increment primary key, 783key1 integer, 784key2 integer not null, 785filler char (200), 786index (key1), 787index (key2) 788); 789show warnings; 790Level Code Message 791explain select pk from t1 where key1 = 1 and key2 = 1; 792id select_type table type possible_keys key key_len ref rows Extra 7931 SIMPLE t1 index_merge key1,key2 key2,key1 4,5 NULL 1 Using intersect(key2,key1); Using where; Using index 794select pk from t1 where key2 = 1 and key1 = 1; 795pk 79626 79727 798select pk from t1 ignore index(key1,key2) where key2 = 1 and key1 = 1; 799pk 80026 80127 802drop table t1; 803create table t1 ( 804pk int primary key auto_increment, 805key1a int, 806key2a int, 807key1b int, 808key2b int, 809dummy1 int, 810dummy2 int, 811dummy3 int, 812dummy4 int, 813key3a int, 814key3b int, 815filler1 char (200), 816index i1(key1a, key1b), 817index i2(key2a, key2b), 818index i3(key3a, key3b) 819); 820create table t2 (a int); 821insert into t2 values (0),(1),(2),(3),(4),(NULL); 822insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b) 823select A.a, B.a, C.a, D.a, C.a, D.a from t2 A,t2 B,t2 C, t2 D; 824insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b) 825select key1a, key1b, key2a, key2b, key3a, key3b from t1; 826insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b) 827select key1a, key1b, key2a, key2b, key3a, key3b from t1; 828analyze table t1; 829Table Op Msg_type Msg_text 830test.t1 analyze status Engine-independent statistics collected 831test.t1 analyze status OK 832select count(*) from t1; 833count(*) 8345184 835select count(*) from t1 where 836key1a = 2 and key1b is null and key2a = 2 and key2b is null; 837count(*) 8384 839select count(*) from t1 where 840key1a = 2 and key1b is null and key3a = 2 and key3b is null; 841count(*) 8424 843drop table t1,t2; 844create table t1 ( 845id1 int, 846id2 date , 847index idx2 (id1,id2), 848index idx1 (id2) 849); 850insert into t1 values(1,'20040101'), (2,'20040102'); 851select * from t1 where id1 = 1 and id2= '20040101'; 852id1 id2 8531 2004-01-01 854drop table t1; 855drop view if exists v1; 856CREATE TABLE t1 ( 857`oid` int(11) unsigned NOT NULL auto_increment, 858`fk_bbk_niederlassung` int(11) unsigned NOT NULL, 859`fk_wochentag` int(11) unsigned NOT NULL, 860`uhrzeit_von` time NOT NULL COMMENT 'HH:MM', 861`uhrzeit_bis` time NOT NULL COMMENT 'HH:MM', 862`geloescht` tinyint(4) NOT NULL, 863`version` int(5) NOT NULL, 864PRIMARY KEY (`oid`), 865KEY `fk_bbk_niederlassung` (`fk_bbk_niederlassung`), 866KEY `fk_wochentag` (`fk_wochentag`), 867KEY `ix_version` (`version`) 868) DEFAULT CHARSET=latin1; 869insert into t1 values 870(1, 38, 1, '08:00:00', '13:00:00', 0, 1), 871(2, 38, 2, '08:00:00', '13:00:00', 0, 1), 872(3, 38, 3, '08:00:00', '13:00:00', 0, 1), 873(4, 38, 4, '08:00:00', '13:00:00', 0, 1), 874(5, 38, 5, '08:00:00', '13:00:00', 0, 1), 875(6, 38, 5, '08:00:00', '13:00:00', 1, 2), 876(7, 38, 3, '08:00:00', '13:00:00', 1, 2), 877(8, 38, 1, '08:00:00', '13:00:00', 1, 2), 878(9, 38, 2, '08:00:00', '13:00:00', 1, 2), 879(10, 38, 4, '08:00:00', '13:00:00', 1, 2), 880(11, 38, 1, '08:00:00', '13:00:00', 0, 3), 881(12, 38, 2, '08:00:00', '13:00:00', 0, 3), 882(13, 38, 3, '08:00:00', '13:00:00', 0, 3), 883(14, 38, 4, '08:00:00', '13:00:00', 0, 3), 884(15, 38, 5, '08:00:00', '13:00:00', 0, 3), 885(16, 38, 4, '08:00:00', '13:00:00', 0, 4), 886(17, 38, 5, '08:00:00', '13:00:00', 0, 4), 887(18, 38, 1, '08:00:00', '13:00:00', 0, 4), 888(19, 38, 2, '08:00:00', '13:00:00', 0, 4), 889(20, 38, 3, '08:00:00', '13:00:00', 0, 4), 890(21, 7, 1, '08:00:00', '13:00:00', 0, 1), 891(22, 7, 2, '08:00:00', '13:00:00', 0, 1), 892(23, 7, 3, '08:00:00', '13:00:00', 0, 1), 893(24, 7, 4, '08:00:00', '13:00:00', 0, 1), 894(25, 7, 5, '08:00:00', '13:00:00', 0, 1); 895create view v1 as 896select 897zeit1.oid AS oid, 898zeit1.fk_bbk_niederlassung AS fk_bbk_niederlassung, 899zeit1.fk_wochentag AS fk_wochentag, 900zeit1.uhrzeit_von AS uhrzeit_von, 901zeit1.uhrzeit_bis AS uhrzeit_bis, 902zeit1.geloescht AS geloescht, 903zeit1.version AS version 904from 905t1 zeit1 906where 907(zeit1.version = 908(select max(zeit2.version) AS `max(version)` 909 from t1 zeit2 910where 911((zeit1.fk_bbk_niederlassung = zeit2.fk_bbk_niederlassung) and 912(zeit1.fk_wochentag = zeit2.fk_wochentag) and 913(zeit1.uhrzeit_von = zeit2.uhrzeit_von) and 914(zeit1.uhrzeit_bis = zeit2.uhrzeit_bis) 915) 916) 917) 918and (zeit1.geloescht = 0); 919select * from v1 where oid = 21; 920oid fk_bbk_niederlassung fk_wochentag uhrzeit_von uhrzeit_bis geloescht version 92121 7 1 08:00:00 13:00:00 0 1 922drop view v1; 923drop table t1; 924CREATE TABLE t1( 925t_cpac varchar(2) NOT NULL, 926t_vers varchar(4) NOT NULL, 927t_rele varchar(2) NOT NULL, 928t_cust varchar(4) NOT NULL, 929filler1 char(250) default NULL, 930filler2 char(250) default NULL, 931PRIMARY KEY (t_cpac,t_vers,t_rele,t_cust), 932UNIQUE KEY IX_4 (t_cust,t_cpac,t_vers,t_rele), 933KEY IX_5 (t_vers,t_rele,t_cust) 934); 935insert into t1 values 936('tm','2.5 ','a ',' ','',''), ('tm','2.5U','a ','stnd','',''), 937('da','3.3 ','b ',' ','',''), ('da','3.3U','b ','stnd','',''), 938('tl','7.6 ','a ',' ','',''), ('tt','7.6 ','a ',' ','',''), 939('bc','B61 ','a ',' ','',''), ('bp','B61 ','a ',' ','',''), 940('ca','B61 ','a ',' ','',''), ('ci','B61 ','a ',' ','',''), 941('cp','B61 ','a ',' ','',''), ('dm','B61 ','a ',' ','',''), 942('ec','B61 ','a ',' ','',''), ('ed','B61 ','a ',' ','',''), 943('fm','B61 ','a ',' ','',''), ('nt','B61 ','a ',' ','',''), 944('qm','B61 ','a ',' ','',''), ('tc','B61 ','a ',' ','',''), 945('td','B61 ','a ',' ','',''), ('tf','B61 ','a ',' ','',''), 946('tg','B61 ','a ',' ','',''), ('ti','B61 ','a ',' ','',''), 947('tp','B61 ','a ',' ','',''), ('ts','B61 ','a ',' ','',''), 948('wh','B61 ','a ',' ','',''), ('bc','B61U','a ','stnd','',''), 949('bp','B61U','a ','stnd','',''), ('ca','B61U','a ','stnd','',''), 950('ci','B61U','a ','stnd','',''), ('cp','B61U','a ','stnd','',''), 951('dm','B61U','a ','stnd','',''), ('ec','B61U','a ','stnd','',''), 952('fm','B61U','a ','stnd','',''), ('nt','B61U','a ','stnd','',''), 953('qm','B61U','a ','stnd','',''), ('tc','B61U','a ','stnd','',''), 954('td','B61U','a ','stnd','',''), ('tf','B61U','a ','stnd','',''), 955('tg','B61U','a ','stnd','',''), ('ti','B61U','a ','stnd','',''), 956('tp','B61U','a ','stnd','',''), ('ts','B61U','a ','stnd','',''), 957('wh','B61U','a ','stnd','',''); 958show create table t1; 959Table Create Table 960t1 CREATE TABLE `t1` ( 961 `t_cpac` varchar(2) NOT NULL, 962 `t_vers` varchar(4) NOT NULL, 963 `t_rele` varchar(2) NOT NULL, 964 `t_cust` varchar(4) NOT NULL, 965 `filler1` char(250) DEFAULT NULL, 966 `filler2` char(250) DEFAULT NULL, 967 PRIMARY KEY (`t_cpac`,`t_vers`,`t_rele`,`t_cust`), 968 UNIQUE KEY `IX_4` (`t_cust`,`t_cpac`,`t_vers`,`t_rele`), 969 KEY `IX_5` (`t_vers`,`t_rele`,`t_cust`) 970) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 971select t_vers,t_rele,t_cust,filler1 from t1 where t_vers = '7.6'; 972t_vers t_rele t_cust filler1 9737.6 a 9747.6 a 975select t_vers,t_rele,t_cust,filler1 from t1 where t_vers = '7.6' 976 and t_rele='a' and t_cust = ' '; 977t_vers t_rele t_cust filler1 9787.6 a 9797.6 a 980drop table t1; 981create table t1 ( 982pk int(11) not null auto_increment, 983a int(11) not null default '0', 984b int(11) not null default '0', 985c int(11) not null default '0', 986filler1 datetime, filler2 varchar(15), 987filler3 longtext, 988kp1 varchar(4), kp2 varchar(7), 989kp3 varchar(2), kp4 varchar(4), 990kp5 varchar(7), 991filler4 char(1), 992primary key (pk), 993key idx1(a,b,c), 994key idx2(c), 995key idx3(kp1,kp2,kp3,kp4,kp5) 996) default charset=latin1; 997set @fill=NULL; 998SELECT COUNT(*) FROM t1 WHERE b = 0 AND a = 0 AND c = 13286427 AND 999kp1='279' AND kp2='ELM0678' AND kp3='6' AND kp4='10' AND kp5 = 'R '; 1000COUNT(*) 10011 1002drop table t1; 1003create table t1 1004( 1005key1 int not null, 1006key2 int not null default 0, 1007key3 int not null default 0 1008); 1009insert into t1(key1) values (1),(2),(3),(4),(5),(6),(7),(8); 1010set @d=8; 1011insert into t1 (key1) select key1+@d from t1; 1012set @d=@d*2; 1013insert into t1 (key1) select key1+@d from t1; 1014set @d=@d*2; 1015insert into t1 (key1) select key1+@d from t1; 1016set @d=@d*2; 1017insert into t1 (key1) select key1+@d from t1; 1018set @d=@d*2; 1019insert into t1 (key1) select key1+@d from t1; 1020set @d=@d*2; 1021insert into t1 (key1) select key1+@d from t1; 1022set @d=@d*2; 1023insert into t1 (key1) select key1+@d from t1; 1024set @d=@d*2; 1025alter table t1 add index i2(key2); 1026alter table t1 add index i3(key3); 1027update t1 set key2=key1,key3=key1; 1028select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40); 1029key1 key2 key3 103031 31 31 103132 32 32 103233 33 33 103334 34 34 103435 35 35 103536 36 36 103637 37 37 103738 38 38 103839 39 39 1039drop table t1; 1040# 1041# Bug#56423: Different count with SELECT and CREATE SELECT queries 1042# 1043CREATE TABLE t1 ( 1044a INT, 1045b INT, 1046c INT, 1047d INT, 1048PRIMARY KEY (a), 1049KEY (c), 1050KEY bd (b,d) 1051); 1052INSERT INTO t1 VALUES 1053(1, 0, 1, 0), 1054(2, 1, 1, 1), 1055(3, 1, 1, 1), 1056(4, 0, 1, 1); 1057EXPLAIN 1058SELECT a 1059FROM t1 1060WHERE c = 1 AND b = 1 AND d = 1; 1061id select_type table type possible_keys key key_len ref rows Extra 10621 SIMPLE t1 index_merge c,bd c,bd 5,10 NULL 1 Using intersect(c,bd); Using where; Using index 1063CREATE TABLE t2 ( a INT ) 1064SELECT a 1065FROM t1 1066WHERE c = 1 AND b = 1 AND d = 1; 1067SELECT * FROM t2; 1068a 10692 10703 1071DROP TABLE t1, t2; 1072CREATE TABLE t1( a INT, b INT, KEY(a), KEY(b) ); 1073INSERT INTO t1 VALUES (1, 2), (1, 2), (1, 2), (1, 2); 1074SELECT * FROM t1 FORCE INDEX(a, b) WHERE a = 1 AND b = 2; 1075a b 10761 2 10771 2 10781 2 10791 2 1080DROP TABLE t1; 1081# Code coverage of fix. 1082CREATE TABLE t1 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT); 1083INSERT INTO t1 (b) VALUES (1); 1084UPDATE t1 SET b = 2 WHERE a = 1; 1085SELECT * FROM t1; 1086a b 10871 2 1088CREATE TABLE t2 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b VARCHAR(1) ); 1089INSERT INTO t2 (b) VALUES ('a'); 1090UPDATE t2 SET b = 'b' WHERE a = 1; 1091SELECT * FROM t2; 1092a b 10931 b 1094DROP TABLE t1, t2; 1095# 1096# BUG#13970015: ASSERT `MIN_ENDP || MAX_ENDP' FAILED IN 1097# HANDLER::MULTI_RANGE_READ_INFO_CONST 1098# 1099CREATE TABLE t1 ( 1100pk INT NOT NULL, 1101col_int_key INT NOT NULL, 1102col_varchar_key VARCHAR(1) NOT NULL, 1103PRIMARY KEY (pk), 1104KEY col_int_key (col_int_key), 1105KEY col_varchar_key (col_varchar_key,col_int_key) 1106); 1107INSERT INTO t1 VALUES (1,1,'a'), (2,2,'b'); 1108EXPLAIN 1109SELECT col_int_key 1110FROM t1 1111WHERE col_varchar_key >= 'l' OR 1112(((pk BETWEEN 141 AND 141) OR col_varchar_key <> 'l') 1113AND ((pk BETWEEN 141 AND 141) OR (col_int_key > 141))); 1114id select_type table type possible_keys key key_len ref rows Extra 11151 SIMPLE t1 index PRIMARY,col_int_key,col_varchar_key col_varchar_key 7 NULL 2 Using where; Using index 1116SELECT col_int_key 1117FROM t1 1118WHERE col_varchar_key >= 'l' OR 1119(((pk BETWEEN 141 AND 141) OR col_varchar_key <> 'l') 1120AND ((pk BETWEEN 141 AND 141) OR (col_int_key > 141))); 1121col_int_key 1122DROP TABLE t1; 1123set global rocksdb_force_flush_memtable_now=1; 1124#---------------- 2-sweeps read Index merge test 2 ------------------------------- 1125SET SESSION DEFAULT_STORAGE_ENGINE = RocksDB; 1126drop table if exists t1; 1127create table t1 ( 1128pk int primary key, 1129key1 int, 1130key2 int, 1131filler char(200), 1132filler2 char(200), 1133index(key1), 1134index(key2) 1135); 1136select * from t1 where (key1 >= 2 and key1 <= 10) or (pk >= 4 and pk <=8 ); 1137pk key1 key2 filler filler2 113810 10 10 filler-data filler-data-2 11392 2 2 filler-data filler-data-2 11403 3 3 filler-data filler-data-2 11414 4 4 filler-data filler-data-2 11425 5 5 filler-data filler-data-2 11436 6 6 filler-data filler-data-2 11447 7 7 filler-data filler-data-2 11458 8 8 filler-data filler-data-2 11469 9 9 filler-data filler-data-2 1147set @maxv=1000; 1148select * from t1 where 1149(pk < 5) or (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) or (pk > @maxv-10) 1150or key1=18 or key1=60; 1151pk key1 key2 filler filler2 11521 1 1 filler-data filler-data-2 11531000 1000 1000 filler-data filler-data-2 115411 11 11 filler-data filler-data-2 115512 12 12 filler-data filler-data-2 115613 13 13 filler-data filler-data-2 115714 14 14 filler-data filler-data-2 115818 18 18 filler-data filler-data-2 11592 2 2 filler-data filler-data-2 11603 3 3 filler-data filler-data-2 11614 4 4 filler-data filler-data-2 116250 50 50 filler-data filler-data-2 116351 51 51 filler-data filler-data-2 116452 52 52 filler-data filler-data-2 116553 53 53 filler-data filler-data-2 116654 54 54 filler-data filler-data-2 116760 60 60 filler-data filler-data-2 1168991 991 991 filler-data filler-data-2 1169992 992 992 filler-data filler-data-2 1170993 993 993 filler-data filler-data-2 1171994 994 994 filler-data filler-data-2 1172995 995 995 filler-data filler-data-2 1173996 996 996 filler-data filler-data-2 1174997 997 997 filler-data filler-data-2 1175998 998 998 filler-data filler-data-2 1176999 999 999 filler-data filler-data-2 1177select * from t1 where 1178(pk < 5) or (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) or (pk > @maxv-10) 1179or key1 < 3 or key1 > @maxv-11; 1180pk key1 key2 filler filler2 11811 1 1 filler-data filler-data-2 11821000 1000 1000 filler-data filler-data-2 118311 11 11 filler-data filler-data-2 118412 12 12 filler-data filler-data-2 118513 13 13 filler-data filler-data-2 118614 14 14 filler-data filler-data-2 11872 2 2 filler-data filler-data-2 11883 3 3 filler-data filler-data-2 11894 4 4 filler-data filler-data-2 119050 50 50 filler-data filler-data-2 119151 51 51 filler-data filler-data-2 119252 52 52 filler-data filler-data-2 119353 53 53 filler-data filler-data-2 119454 54 54 filler-data filler-data-2 1195990 990 990 filler-data filler-data-2 1196991 991 991 filler-data filler-data-2 1197992 992 992 filler-data filler-data-2 1198993 993 993 filler-data filler-data-2 1199994 994 994 filler-data filler-data-2 1200995 995 995 filler-data filler-data-2 1201996 996 996 filler-data filler-data-2 1202997 997 997 filler-data filler-data-2 1203998 998 998 filler-data filler-data-2 1204999 999 999 filler-data filler-data-2 1205select * from t1 where 1206(pk < 5) or (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) or (pk > @maxv-10) 1207or 1208(key1 < 5) or (key1 > 10 and key1 < 15) or (key1 >= 50 and key1 < 55 ) or (key1 > @maxv-10); 1209pk key1 key2 filler filler2 12101 1 1 filler-data filler-data-2 12111000 1000 1000 filler-data filler-data-2 121211 11 11 filler-data filler-data-2 121312 12 12 filler-data filler-data-2 121413 13 13 filler-data filler-data-2 121514 14 14 filler-data filler-data-2 12162 2 2 filler-data filler-data-2 12173 3 3 filler-data filler-data-2 12184 4 4 filler-data filler-data-2 121950 50 50 filler-data filler-data-2 122051 51 51 filler-data filler-data-2 122152 52 52 filler-data filler-data-2 122253 53 53 filler-data filler-data-2 122354 54 54 filler-data filler-data-2 1224991 991 991 filler-data filler-data-2 1225992 992 992 filler-data filler-data-2 1226993 993 993 filler-data filler-data-2 1227994 994 994 filler-data filler-data-2 1228995 995 995 filler-data filler-data-2 1229996 996 996 filler-data filler-data-2 1230997 997 997 filler-data filler-data-2 1231998 998 998 filler-data filler-data-2 1232999 999 999 filler-data filler-data-2 1233select * from t1 where 1234(pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) 1235or 1236(key1 < 5) or (key1 > @maxv-10); 1237pk key1 key2 filler filler2 12381 1 1 filler-data filler-data-2 12391000 1000 1000 filler-data filler-data-2 124011 11 11 filler-data filler-data-2 124112 12 12 filler-data filler-data-2 124213 13 13 filler-data filler-data-2 124314 14 14 filler-data filler-data-2 12442 2 2 filler-data filler-data-2 12453 3 3 filler-data filler-data-2 12464 4 4 filler-data filler-data-2 124750 50 50 filler-data filler-data-2 124851 51 51 filler-data filler-data-2 124952 52 52 filler-data filler-data-2 125053 53 53 filler-data filler-data-2 125154 54 54 filler-data filler-data-2 1252991 991 991 filler-data filler-data-2 1253992 992 992 filler-data filler-data-2 1254993 993 993 filler-data filler-data-2 1255994 994 994 filler-data filler-data-2 1256995 995 995 filler-data filler-data-2 1257996 996 996 filler-data filler-data-2 1258997 997 997 filler-data filler-data-2 1259998 998 998 filler-data filler-data-2 1260999 999 999 filler-data filler-data-2 1261drop table t1; 1262set global rocksdb_force_flush_memtable_now=1; 1263#---------------- Clustered PK ROR-index_merge tests ----------------------------- 1264SET SESSION DEFAULT_STORAGE_ENGINE = RocksDB; 1265drop table if exists t1; 1266create table t1 1267( 1268pk1 int not null, 1269pk2 int not null, 1270key1 int not null, 1271key2 int not null, 1272pktail1ok int not null, 1273pktail2ok int not null, 1274pktail3bad int not null, 1275pktail4bad int not null, 1276pktail5bad int not null, 1277pk2copy int not null, 1278badkey int not null, 1279filler1 char (200), 1280filler2 char (200), 1281key (key1), 1282key (key2), 1283/* keys with tails from CPK members */ 1284key (pktail1ok, pk1), 1285key (pktail2ok, pk1, pk2), 1286key (pktail3bad, pk2, pk1), 1287key (pktail4bad, pk1, pk2copy), 1288key (pktail5bad, pk1, pk2, pk2copy), 1289primary key (pk1, pk2) 1290); 1291explain select * from t1 where pk1 = 1 and pk2 < 80 and key1=0; 1292id select_type table type possible_keys key key_len ref rows Extra 12931 SIMPLE t1 range PRIMARY,key1 PRIMARY 8 NULL ROWS Using where 1294select * from t1 where pk1 = 1 and pk2 < 80 and key1=0; 1295pk1 pk2 key1 key2 pktail1ok pktail2ok pktail3bad pktail4bad pktail5bad pk2copy badkey filler1 filler2 12961 10 0 0 0 0 0 0 0 10 0 filler-data-10 filler2 12971 11 0 0 0 0 0 0 0 11 0 filler-data-11 filler2 12981 12 0 0 0 0 0 0 0 12 0 filler-data-12 filler2 12991 13 0 0 0 0 0 0 0 13 0 filler-data-13 filler2 13001 14 0 0 0 0 0 0 0 14 0 filler-data-14 filler2 13011 15 0 0 0 0 0 0 0 15 0 filler-data-15 filler2 13021 16 0 0 0 0 0 0 0 16 0 filler-data-16 filler2 13031 17 0 0 0 0 0 0 0 17 0 filler-data-17 filler2 13041 18 0 0 0 0 0 0 0 18 0 filler-data-18 filler2 13051 19 0 0 0 0 0 0 0 19 0 filler-data-19 filler2 1306explain select pk1,pk2 from t1 where key1 = 10 and key2=10 and 2*pk1+1 < 2*96+1; 1307id select_type table type possible_keys key key_len ref rows Extra 13081 SIMPLE t1 index_merge key1,key2 key1,key2 4,4 NULL 1 Using intersect(key1,key2); Using where; Using index 1309select pk1,pk2 from t1 where key1 = 10 and key2=10 and 2*pk1+1 < 2*96+1; 1310pk1 pk2 131195 50 131295 51 131395 52 131495 53 131595 54 131695 55 131795 56 131895 57 131995 58 132095 59 1321explain select * from t1 where badkey=1 and key1=10; 1322id select_type table type possible_keys key key_len ref rows Extra 13231 SIMPLE t1 ref key1 key1 4 const ROWS Using where 1324explain select * from t1 where pk1 < 7500 and key1 = 10; 1325id select_type table type possible_keys key key_len ref rows Extra 13261 SIMPLE t1 range PRIMARY,key1 PRIMARY 4 NULL ROWS Using where 1327explain select * from t1 where pktail1ok=1 and key1=10; 1328id select_type table type possible_keys key key_len ref rows Extra 13291 SIMPLE t1 ref key1,pktail1ok key1 4 const 2 Using where 1330explain select * from t1 where pktail2ok=1 and key1=10; 1331id select_type table type possible_keys key key_len ref rows Extra 13321 SIMPLE t1 ref key1,pktail2ok key1 4 const 2 Using where 1333explain select * from t1 where (pktail2ok=1 and pk1< 50000) or key1=10; 1334id select_type table type possible_keys key key_len ref rows Extra 13351 SIMPLE t1 index_merge PRIMARY,key1,pktail2ok pktail2ok,key1 8,4 NULL ROWS Using sort_union(pktail2ok,key1); Using where 1336explain select * from t1 where pktail3bad=1 and key1=10; 1337id select_type table type possible_keys key key_len ref rows Extra 13381 SIMPLE t1 ref key1,pktail3bad EITHER_KEY 4 const ROWS Using where 1339explain select * from t1 where pktail4bad=1 and key1=10; 1340id select_type table type possible_keys key key_len ref rows Extra 13411 SIMPLE t1 ref key1,pktail4bad key1 4 const ROWS Using where 1342explain select * from t1 where pktail5bad=1 and key1=10; 1343id select_type table type possible_keys key key_len ref rows Extra 13441 SIMPLE t1 ref key1,pktail5bad key1 4 const ROWS Using where 1345explain select pk1,pk2,key1,key2 from t1 where key1 = 10 and key2=10 limit 10; 1346id select_type table type possible_keys key key_len ref rows Extra 13471 SIMPLE t1 index_merge key1,key2 key1,key2 4,4 NULL 1 Using intersect(key1,key2); Using where; Using index 1348select pk1,pk2,key1,key2 from t1 where key1 = 10 and key2=10 limit 10; 1349pk1 pk2 key1 key2 135095 50 10 10 135195 51 10 10 135295 52 10 10 135395 53 10 10 135495 54 10 10 135595 55 10 10 135695 56 10 10 135795 57 10 10 135895 58 10 10 135995 59 10 10 1360drop table t1; 1361create table t1 1362( 1363RUNID varchar(22), 1364SUBMITNR varchar(5), 1365ORDERNR char(1), 1366PROGRAMM varchar(8), 1367TESTID varchar(4), 1368UCCHECK char(1), 1369ETEXT varchar(80), 1370ETEXT_TYPE char(1), 1371INFO char(1), 1372SEVERITY tinyint(3), 1373TADIRFLAG char(1), 1374PRIMARY KEY (RUNID,SUBMITNR,ORDERNR,PROGRAMM,TESTID,UCCHECK), 1375KEY `TVERM~KEY` (PROGRAMM,TESTID,UCCHECK) 1376) DEFAULT CHARSET=latin1; 1377update t1 set `ETEXT` = '', `ETEXT_TYPE`='', `INFO`='', `SEVERITY`='', `TADIRFLAG`='' 1378WHERE 1379`RUNID`= '' AND `SUBMITNR`= '' AND `ORDERNR`='' AND `PROGRAMM`='' AND 1380`TESTID`='' AND `UCCHECK`=''; 1381drop table t1; 1382# 1383# Bug#50402 Optimizer producing wrong results when using Index Merge on InnoDB 1384# 1385CREATE TABLE t1 (f1 INT, PRIMARY KEY (f1)); 1386INSERT INTO t1 VALUES (2); 1387CREATE TABLE t2 (f1 INT, f2 INT, f3 char(1), 1388PRIMARY KEY (f1), KEY (f2), KEY (f3) ); 1389INSERT INTO t2 VALUES (1, 1, 'h'), (2, 3, 'h'), (3, 2, ''), (4, 2, ''); 1390SELECT t1.f1 FROM t1 1391WHERE (SELECT COUNT(*) FROM t2 WHERE t2.f3 = 'h' AND t2.f2 = t1.f1) = 0 AND t1.f1 = 2; 1392f1 13932 1394EXPLAIN SELECT t1.f1 FROM t1 1395WHERE (SELECT COUNT(*) FROM t2 WHERE t2.f3 = 'h' AND t2.f2 = t1.f1) = 0 AND t1.f1 = 2; 1396id select_type table type possible_keys key key_len ref rows Extra 13971 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 Using index 13982 SUBQUERY t2 index_merge f2,f3 f3,f2 2,5 NULL 1 Using intersect(f3,f2); Using where; Using index 1399DROP TABLE t1,t2; 1400set global rocksdb_force_flush_memtable_now=1; 1401# 1402# Bug#11747423 32254: INDEX MERGE USED UNNECESSARILY 1403# 1404CREATE TABLE t1 ( 1405id INT NOT NULL PRIMARY KEY, 1406id2 INT NOT NULL, 1407id3 INT NOT NULL, 1408KEY (id2), 1409KEY (id3), 1410KEY covering_index (id2,id3) 1411) ENGINE=RocksDB; 1412INSERT INTO t1 VALUES (0, 0, 0), (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4), (5, 5, 5), (6, 6, 6), (7, 7, 7); 1413INSERT INTO t1 SELECT id + 8, id2 + 8, id3 +8 FROM t1; 1414INSERT INTO t1 SELECT id + 16, 7, 0 FROM t1; 1415EXPLAIN SELECT SQL_NO_CACHE count(*) FROM t1 WHERE id2=7 AND id3=0; 1416id select_type table type possible_keys key key_len ref rows Extra 14171 SIMPLE t1 ref id2,id3,covering_index covering_index 8 const,const 2 Using index 1418DROP TABLE t1; 1419