1SET STORAGE_ENGINE = MyISAM; 2set @optimizer_switch_save= @@optimizer_switch; 3set optimizer_switch='rowid_filter=off'; 4set optimizer_switch='index_merge_sort_intersection=off'; 5#---------------- Index merge test 1 ------------------------------------------- 6create table t0 7( 8key1 int not null, 9INDEX i1(key1) 10); 11insert into t0(key1) select seq from seq_1_to_1024; 12alter table t0 add key2 int not null, add index i2(key2); 13alter table t0 add key3 int not null, add index i3(key3); 14alter table t0 add key4 int not null, add index i4(key4); 15alter table t0 add key5 int not null, add index i5(key5); 16alter table t0 add key6 int not null, add index i6(key6); 17alter table t0 add key7 int not null, add index i7(key7); 18alter table t0 add key8 int not null, add index i8(key8); 19update t0 set key2=key1,key3=key1,key4=key1,key5=key1,key6=key1,key7=key1,key8=1024-key1; 20analyze table t0; 21Table Op Msg_type Msg_text 22test.t0 analyze status Engine-independent statistics collected 23test.t0 analyze status OK 24explain select * from t0 where key1 < 3 or key1 > 920 and key1 < 924; 25id select_type table type possible_keys key key_len ref rows Extra 261 SIMPLE t0 range i1 i1 4 NULL 5 Using index condition 27explain 28select * from t0 where key1 < 3 or key2 > 920 and key2 < 924; 29id select_type table type possible_keys key key_len ref rows Extra 301 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 5 Using sort_union(i1,i2); Using where 31select * from t0 where key1 < 3 or key2 > 920 and key2 < 924; 32key1 key2 key3 key4 key5 key6 key7 key8 331 1 1 1 1 1 1 1023 342 2 2 2 2 2 2 1022 35921 921 921 921 921 921 921 103 36922 922 922 922 922 922 922 102 37923 923 923 923 923 923 923 101 38select * from t0 where key1=1022; 39key1 key2 key3 key4 key5 key6 key7 key8 401022 1022 1022 1022 1022 1022 1022 2 41explain select * from t0 where key1 < 3 or key2 <4; 42id select_type table type possible_keys key key_len ref rows Extra 431 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 5 Using sort_union(i1,i2); Using where 44explain 45select * from t0 where (key1 > 30 and key1<35) or (key2 >32 and key2 < 40); 46id select_type table type possible_keys key key_len ref rows Extra 471 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 11 Using sort_union(i1,i2); Using where 48select * from t0 where (key1 > 30 and key1<35) or (key2 >32 and key2 < 40); 49key1 key2 key3 key4 key5 key6 key7 key8 5031 31 31 31 31 31 31 993 5132 32 32 32 32 32 32 992 5233 33 33 33 33 33 33 991 5334 34 34 34 34 34 34 990 5435 35 35 35 35 35 35 989 5536 36 36 36 36 36 36 988 5637 37 37 37 37 37 37 987 5738 38 38 38 38 38 38 986 5839 39 39 39 39 39 39 985 59explain select * from t0 ignore index (i2) where key1 < 3 or key2 <4; 60id select_type table type possible_keys key key_len ref rows Extra 611 SIMPLE t0 ALL i1 NULL NULL NULL 1024 Using where 62explain select * from t0 where (key1 < 3 or key2 <4) and key3 = 50; 63id select_type table type possible_keys key key_len ref rows Extra 641 SIMPLE t0 ref i1,i2,i3 i3 4 const 1 Using where 65explain select * from t0 use index (i1,i2) where (key1 < 3 or key2 <4) and key3 = 50; 66id select_type table type possible_keys key key_len ref rows Extra 671 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 5 Using sort_union(i1,i2); Using where 68explain select * from t0 where (key1 > 1 or key2 > 2); 69id select_type table type possible_keys key key_len ref rows Extra 701 SIMPLE t0 ALL i1,i2 NULL NULL NULL 1024 Using where 71explain select * from t0 force index (i1,i2) where (key1 > 1 or key2 > 2); 72id select_type table type possible_keys key key_len ref rows Extra 731 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 1024 Using sort_union(i1,i2); Using where 74explain 75select * from t0 where key1<3 or key2<3 or (key1>5 and key1<8) or 76(key1>10 and key1<12) or (key2>100 and key2<110); 77id select_type table type possible_keys key key_len ref rows Extra 781 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 15 Using sort_union(i1,i2); Using where 79explain select * from t0 where key2 = 45 or key1 <=> null; 80id select_type table type possible_keys key key_len ref rows Extra 811 SIMPLE t0 range i1,i2 i2 4 NULL 1 Using index condition 82explain select * from t0 where key2 = 45 or key1 is not null; 83id select_type table type possible_keys key key_len ref rows Extra 841 SIMPLE t0 ALL i1,i2 NULL NULL NULL 1024 Using where 85explain select * from t0 where key2 = 45 or key1 is null; 86id select_type table type possible_keys key key_len ref rows Extra 871 SIMPLE t0 ref i2 i2 4 const 1 88explain select * from t0 where key2=10 or key3=3 or key4 <=> null; 89id select_type table type possible_keys key key_len ref rows Extra 901 SIMPLE t0 index_merge i2,i3,i4 i2,i3 4,4 NULL 2 Using union(i2,i3); Using where 91explain select * from t0 where key2=10 or key3=3 or key4 is null; 92id select_type table type possible_keys key key_len ref rows Extra 931 SIMPLE t0 index_merge i2,i3 i2,i3 4,4 NULL 2 Using union(i2,i3); Using where 94explain select key1 from t0 where (key1 <=> null) or (key2 < 5) or 95(key3=10) or (key4 <=> null); 96id select_type table type possible_keys key key_len ref rows Extra 971 SIMPLE t0 index_merge i1,i2,i3,i4 i2,i3 4,4 NULL 5 Using sort_union(i2,i3); Using where 98explain select key1 from t0 where (key1 <=> null) or (key1 < 5) or 99(key3=10) or (key4 <=> null); 100id select_type table type possible_keys key key_len ref rows Extra 1011 SIMPLE t0 index_merge i1,i3,i4 i1,i3 4,4 NULL 5 Using sort_union(i1,i3); Using where 102explain select * from t0 where 103(key1 < 3 or key2 < 3) and (key3 < 4 or key4 < 4) and (key5 < 5 or key6 < 5); 104id select_type table type possible_keys key key_len ref rows Extra 1051 SIMPLE t0 index_merge i1,i2,i3,i4,i5,i6 i1,i2 4,4 NULL 4 Using sort_union(i1,i2); Using where 106explain 107select * from t0 where (key1 < 3 or key2 < 6) and (key1 < 7 or key3 < 4); 108id select_type table type possible_keys key key_len ref rows Extra 1091 SIMPLE t0 index_merge i1,i2,i3 i1,i2 4,4 NULL 7 Using sort_union(i1,i2); Using where 110select * from t0 where (key1 < 3 or key2 < 6) and (key1 < 7 or key3 < 4); 111key1 key2 key3 key4 key5 key6 key7 key8 1121 1 1 1 1 1 1 1023 1132 2 2 2 2 2 2 1022 1143 3 3 3 3 3 3 1021 1154 4 4 4 4 4 4 1020 1165 5 5 5 5 5 5 1019 117explain select * from t0 where 118(key1 < 3 or key2 < 3) and (key3 < 4 or key4 < 4) 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 i5,i6 4,4 NULL 2 Using sort_union(i5,i6); Using where 121explain select * from t0 where 122(key1 < 3 or key2 < 3) and (key3 < 100); 123id select_type table type possible_keys key key_len ref rows Extra 1241 SIMPLE t0 index_merge i1,i2,i3 i1,i2 4,4 NULL 4 Using sort_union(i1,i2); 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 index_merge i1,i2,i3 i1,i2 4,4 NULL 4 Using sort_union(i1,i2); Using where 129explain select * from t0 where 130((key1 < 4 or key2 < 4) and (key2 <5 or key3 < 4)) 131or 132key2 > 5; 133id select_type table type possible_keys key key_len ref rows Extra 1341 SIMPLE t0 ALL i1,i2,i3 NULL NULL NULL 1024 Using where 135explain select * from t0 where 136((key1 < 4 or key2 < 4) and (key2 <5 or key3 < 4)) 137or 138key1 < 7; 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 8 Using sort_union(i1,i2); Using where 141select * from t0 where 142((key1 < 4 or key2 < 4) and (key2 <5 or key3 < 4)) 143or 144key1 < 7; 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 1505 5 5 5 5 5 5 1019 1516 6 6 6 6 6 6 1018 152select count(*) from t0 where 153((key1 < 4 or key2 < 4) and (key3 <5 or key5 < 4)) 154or 155((key5 < 5 or key6 < 6) and (key7 <7 or key8 < 4)); 156count(*) 1575 158explain select * from t0 where 159((key1 < 4 or key2 < 4) and (key3 <5 or key5 < 4)) 160or 161((key5 < 5 or key6 < 6) and (key7 <7 or key8 < 4)); 162id select_type table type possible_keys key key_len ref rows Extra 1631 SIMPLE t0 index_merge i1,i2,i3,i5,i6,i7,i8 i1,i2,i5,i6 4,4,4,4 NULL 15 Using sort_union(i1,i2,i5,i6); Using where 164explain select * from t0 where 165((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4)) 166or 167((key7 <7 or key8 < 4) and (key5 < 5 or key6 < 6)); 168id select_type table type possible_keys key key_len ref rows Extra 1691 SIMPLE t0 index_merge i1,i2,i3,i5,i6,i7,i8 i3,i5,i7,i8 4,4,4,4 NULL 16 Using sort_union(i3,i5,i7,i8); Using where 170explain select * from t0 where 171((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4)) 172or 173((key3 <7 or key5 < 2) and (key5 < 5 or key6 < 6)); 174id select_type table type possible_keys key key_len ref rows Extra 1751 SIMPLE t0 index_merge i1,i2,i3,i5,i6 i3,i5 4,4 NULL 9 Using sort_union(i3,i5); Using where 176explain select * from t0 where 177((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4)) 178or 179(((key3 <7 and key7 < 6) or key5 < 2) and (key5 < 5 or key6 < 6)); 180id select_type table type possible_keys key key_len ref rows Extra 1811 SIMPLE t0 index_merge i1,i2,i3,i5,i6,i7 i3,i5 4,4 NULL 9 Using sort_union(i3,i5); Using where 182explain select * from t0 where 183((key3 < 4 or key5 < 4) and (key1 < 4 or key2 < 4)) 184or 185((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6)); 186id select_type table type possible_keys key key_len ref rows Extra 1871 SIMPLE t0 ALL i1,i2,i3,i5,i6 NULL NULL NULL 1024 Using where 188explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where 189((key3 < 4 or key5 < 4) and (key1 < 4 or key2 < 4)) 190or 191((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6)); 192id select_type table type possible_keys key key_len ref rows Extra 1931 SIMPLE t0 index_merge i1,i2,i3,i5,i6 i3,i5 4,4 NULL 1024 Using sort_union(i3,i5); Using where 194explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where 195((key3 < 5 or key5 < 4) and (key1 < 4 or key2 < 4)) 196or 197((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6)); 198id select_type table type possible_keys key key_len ref rows Extra 1991 SIMPLE t0 ALL i1,i2,i3,i5,i6 NULL NULL NULL 1024 Using where 200explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where 201((key3 < 10 or key5 < 4) and (key1 < 4 or key2 < 4)) 202or 203((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6)); 204id select_type table type possible_keys key key_len ref rows Extra 2051 SIMPLE t0 ALL i1,i2,i3,i5,i6 NULL NULL NULL 1024 Using where 206select * from t0 where key1 < 5 or key8 < 4 order by key1; 207key1 key2 key3 key4 key5 key6 key7 key8 2081 1 1 1 1 1 1 1023 2092 2 2 2 2 2 2 1022 2103 3 3 3 3 3 3 1021 2114 4 4 4 4 4 4 1020 2121021 1021 1021 1021 1021 1021 1021 3 2131022 1022 1022 1022 1022 1022 1022 2 2141023 1023 1023 1023 1023 1023 1023 1 2151024 1024 1024 1024 1024 1024 1024 0 216explain 217select * from t0 where key1 < 5 or key8 < 4 order by key1; 218id select_type table type possible_keys key key_len ref rows Extra 2191 SIMPLE t0 index_merge i1,i8 i1,i8 4,4 NULL 7 Using sort_union(i1,i8); Using where; Using filesort 220create table t2 like t0; 221insert into t2 select * from t0; 222alter table t2 add index i1_3(key1, key3); 223alter table t2 add index i2_3(key2, key3); 224alter table t2 drop index i1; 225alter table t2 drop index i2; 226alter table t2 add index i321(key3, key2, key1); 227explain select key3 from t2 where key1 = 100 or key2 = 100; 228id select_type table type possible_keys key key_len ref rows Extra 2291 SIMPLE t2 index_merge i1_3,i2_3 i1_3,i2_3 4,4 NULL 2 Using sort_union(i1_3,i2_3); Using where 230explain select key3 from t2 where key1 < 500 or key2 < 500; 231id select_type table type possible_keys key key_len ref rows Extra 2321 SIMPLE t2 index i1_3,i2_3 i321 12 NULL 1024 Using where; Using index 233explain select key7 from t2 where key1 <100 or key2 < 100; 234id select_type table type possible_keys key key_len ref rows Extra 2351 SIMPLE t2 index_merge i1_3,i2_3 i1_3,i2_3 4,4 NULL 186 Using sort_union(i1_3,i2_3); Using where 236create table t4 ( 237key1a int not null, 238key1b int not null, 239key2 int not null, 240key2_1 int not null, 241key2_2 int not null, 242key3 int not null, 243index i1a (key1a, key1b), 244index i1b (key1b, key1a), 245index i2_1(key2, key2_1), 246index i2_2(key2, key2_1) 247); 248Warnings: 249Note 1831 Duplicate index `i2_2`. This is deprecated and will be disallowed in a future release 250insert into t4 select seq,seq,seq div 10, seq % 10, seq % 10, seq from seq_1_to_1024; 251select * from t4 where key1a = 3 or key1b = 4; 252key1a key1b key2 key2_1 key2_2 key3 2533 3 0 3 3 3 2544 4 0 4 4 4 255explain select * from t4 where key1a = 3 or key1b = 4; 256id select_type table type possible_keys key key_len ref rows Extra 2571 SIMPLE t4 index_merge i1a,i1b i1a,i1b 4,4 NULL 2 Using sort_union(i1a,i1b); Using where 258explain select * from t4 where key2 = 1 and (key2_1 = 1 or key3 = 5); 259id select_type table type possible_keys key key_len ref rows Extra 2601 SIMPLE t4 ref i2_1,i2_2 i2_1 4 const 9 Using where 261explain select * from t4 where key2 = 1 and (key2_1 = 1 or key2_2 = 5); 262id select_type table type possible_keys key key_len ref rows Extra 2631 SIMPLE t4 ref i2_1,i2_2 i2_1 4 const 9 Using where 264explain select * from t4 where key2_1 = 1 or key2_2 = 5; 265id select_type table type possible_keys key key_len ref rows Extra 2661 SIMPLE t4 ALL NULL NULL NULL NULL 1024 Using where 267create table t1 like t0; 268insert into t1 select * from t0; 269explain select * from t0 left join t1 on (t0.key1=t1.key1) 270where t0.key1=3 or t0.key2=4; 271id select_type table type possible_keys key key_len ref rows Extra 2721 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where 2731 SIMPLE t1 ref i1 i1 4 test.t0.key1 1 274select * from t0 left join t1 on (t0.key1=t1.key1) 275where t0.key1=3 or t0.key2=4; 276key1 key2 key3 key4 key5 key6 key7 key8 key1 key2 key3 key4 key5 key6 key7 key8 2773 3 3 3 3 3 3 1021 3 3 3 3 3 3 3 1021 2784 4 4 4 4 4 4 1020 4 4 4 4 4 4 4 1020 279explain 280select * from t0,t1 where (t0.key1=t1.key1) and ( t0.key1=3 or t0.key2=4); 281id select_type table type possible_keys key key_len ref rows Extra 2821 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where 2831 SIMPLE t1 ref i1 i1 4 test.t0.key1 1 284explain 285select * from t0,t1 where (t0.key1=t1.key1) and 286(t0.key1=3 or t0.key2=4) and t1.key1<200; 287id select_type table type possible_keys key key_len ref rows Extra 2881 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where 2891 SIMPLE t1 ref i1 i1 4 test.t0.key1 1 290explain 291select * from t0,t1 where (t0.key1=t1.key1) and 292(t0.key1=3 or t0.key2<4) and t1.key1=2; 293id select_type table type possible_keys key key_len ref rows Extra 2941 SIMPLE t0 ref i1,i2 i1 4 const 1 Using where 2951 SIMPLE t1 ref i1 i1 4 const 1 296explain select * from t0,t1 where t0.key1 = 5 and 297(t1.key1 = t0.key1 or t1.key8 = t0.key1); 298id select_type table type possible_keys key key_len ref rows Extra 2991 SIMPLE t0 ref i1 i1 4 const 1 3001 SIMPLE t1 index_merge i1,i8 i1,i8 4,4 NULL 2 Using union(i1,i8); Using where; Using join buffer (flat, BNL join) 301explain select * from t0,t1 where t0.key1 < 3 and 302(t1.key1 = t0.key1 or t1.key8 = t0.key1); 303id select_type table type possible_keys key key_len ref rows Extra 3041 SIMPLE t0 range i1 i1 4 NULL 2 Using index condition 3051 SIMPLE t1 ALL i1,i8 NULL NULL NULL 1024 Range checked for each record (index map: 0x81) 306explain select * from t1 where key1=3 or key2=4 307union select * from t1 where key1<4 or key3=5; 308id select_type table type possible_keys key key_len ref rows Extra 3091 PRIMARY t1 index_merge i1,i2 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where 3102 UNION t1 index_merge i1,i3 i1,i3 4,4 NULL 4 Using sort_union(i1,i3); Using where 311NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL 312set @tmp_optimizer_switch=@@optimizer_switch; 313set optimizer_switch='derived_merge=off,derived_with_keys=off'; 314explain select * from (select * from t1 where key1 = 3 or key2 =3) as Z where key8 >5; 315id select_type table type possible_keys key key_len ref rows Extra 3161 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using where 3172 DERIVED t1 index_merge i1,i2,i8 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where 318set optimizer_switch=@tmp_optimizer_switch; 319create table t3 like t0; 320insert into t3 select * from t0; 321alter table t3 add key9 int not null, add index i9(key9); 322alter table t3 add keyA int not null, add index iA(keyA); 323alter table t3 add keyB int not null, add index iB(keyB); 324alter table t3 add keyC int not null, add index iC(keyC); 325update t3 set key9=key1,keyA=key1,keyB=key1,keyC=key1; 326explain select * from t3 where 327key1=1 or key2=2 or key3=3 or key4=4 or 328key5=5 or key6=6 or key7=7 or key8=8 or 329key9=9 or keyA=10 or keyB=11 or keyC=12; 330id select_type table type possible_keys key key_len ref rows Extra 3311 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 12 Using union(i1,i2,i3,i4,i5,i6,i7,i8,i9,iA,iB,iC); Using where 332select * from t3 where 333key1=1 or key2=2 or key3=3 or key4=4 or 334key5=5 or key6=6 or key7=7 or key8=8 or 335key9=9 or keyA=10 or keyB=11 or keyC=12; 336key1 key2 key3 key4 key5 key6 key7 key8 key9 keyA keyB keyC 3371 1 1 1 1 1 1 1023 1 1 1 1 3382 2 2 2 2 2 2 1022 2 2 2 2 3393 3 3 3 3 3 3 1021 3 3 3 3 3404 4 4 4 4 4 4 1020 4 4 4 4 3415 5 5 5 5 5 5 1019 5 5 5 5 3426 6 6 6 6 6 6 1018 6 6 6 6 3437 7 7 7 7 7 7 1017 7 7 7 7 3449 9 9 9 9 9 9 1015 9 9 9 9 34510 10 10 10 10 10 10 1014 10 10 10 10 34611 11 11 11 11 11 11 1013 11 11 11 11 34712 12 12 12 12 12 12 1012 12 12 12 12 3481016 1016 1016 1016 1016 1016 1016 8 1016 1016 1016 1016 349explain select * from t0 where key1 < 3 or key2 < 4; 350id select_type table type possible_keys key key_len ref rows Extra 3511 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 5 Using sort_union(i1,i2); Using where 352select * from t0 where key1 < 3 or key2 < 4; 353key1 key2 key3 key4 key5 key6 key7 key8 3541 1 1 1 1 1 1 1023 3552 2 2 2 2 2 2 1022 3563 3 3 3 3 3 3 1021 357update t0 set key8=123 where key1 < 3 or key2 < 4; 358select * from t0 where key1 < 3 or key2 < 4; 359key1 key2 key3 key4 key5 key6 key7 key8 3601 1 1 1 1 1 1 123 3612 2 2 2 2 2 2 123 3623 3 3 3 3 3 3 123 363delete from t0 where key1 < 3 or key2 < 4; 364select * from t0 where key1 < 3 or key2 < 4; 365key1 key2 key3 key4 key5 key6 key7 key8 366select count(*) from t0; 367count(*) 3681021 369drop table t4; 370create table t4 (a int); 371insert into t4 values (1),(4),(3); 372set @save_join_buffer_size=@@join_buffer_size; 373set join_buffer_size= 4096; 374explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) 375from t0 as A force index(i1,i2), t0 as B force index (i1,i2) 376where (A.key1 < 500000 or A.key2 < 3) 377and (B.key1 < 500000 or B.key2 < 3); 378id select_type table type possible_keys key key_len ref rows Extra 3791 SIMPLE A index_merge i1,i2 i1,i2 4,4 NULL 1010 Using sort_union(i1,i2); Using where 3801 SIMPLE B index_merge i1,i2 i1,i2 4,4 NULL 1010 Using sort_union(i1,i2); Using where; Using join buffer (flat, BNL join) 381select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) 382from t0 as A force index(i1,i2), t0 as B force index (i1,i2) 383where (A.key1 < 500000 or A.key2 < 3) 384and (B.key1 < 500000 or B.key2 < 3); 385max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) 38610240 387update t0 set key1=1; 388explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) 389from t0 as A force index(i1,i2), t0 as B force index (i1,i2) 390where (A.key1 = 1 or A.key2 = 1) 391and (B.key1 = 1 or B.key2 = 1); 392id select_type table type possible_keys key key_len ref rows Extra 3931 SIMPLE A index_merge i1,i2 i1,i2 4,4 NULL 1021 Using union(i1,i2); Using where 3941 SIMPLE B index_merge i1,i2 i1,i2 4,4 NULL 1021 Using union(i1,i2); Using where; Using join buffer (flat, BNL join) 395select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) 396from t0 as A force index(i1,i2), t0 as B force index (i1,i2) 397where (A.key1 = 1 or A.key2 = 1) 398and (B.key1 = 1 or B.key2 = 1); 399max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) 4008194 401alter table t0 add filler1 char(200), add filler2 char(200), add filler3 char(200); 402update t0 set key2=1, key3=1, key4=1, key5=1,key6=1,key7=1 where key7 < 500; 403explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) 404from t0 as A straight_join t0 as B 405where (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) 406and (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); 407id select_type table type possible_keys key key_len ref rows Extra 4081 SIMPLE A index_merge i1,i2,i3,i4,i5,i6,i7?,i8 i2,i3,i4,i5,i6,i7?,i8 X NULL # Using union(intersect(i2,i3,i4,i5,i6,i7?),i8); Using where 4091 SIMPLE B index_merge i1,i2,i3,i4,i5,i6,i7?,i8 i2,i3,i4,i5,i6,i7?,i8 X NULL # Using union(intersect(i2,i3,i4,i5,i6,i7?),i8); Using where; Using join buffer (flat, BNL join) 410select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) 411from t0 as A straight_join t0 as B 412where (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) 413and (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); 414max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) 4158186 416set join_buffer_size= @save_join_buffer_size; 417drop table t0, t1, t2, t3, t4; 418CREATE TABLE t1 ( 419cola char(3) not null, colb char(3) not null, filler char(200), 420key(cola), key(colb) 421); 422INSERT INTO t1 VALUES ('foo','bar', 'ZZ'),('fuz','baz', 'ZZ'); 423OPTIMIZE TABLE t1; 424Table Op Msg_type Msg_text 425test.t1 optimize status OK 426select count(*) from t1; 427count(*) 4288704 429explain select * from t1 WHERE cola = 'foo' AND colb = 'bar'; 430id select_type table type possible_keys key key_len ref rows Extra 4311 SIMPLE t1 index_merge cola,colb cola,colb 3,3 NULL 32 Using intersect(cola,colb); Using where 432explain select * from t1 force index(cola,colb) WHERE cola = 'foo' AND colb = 'bar'; 433id select_type table type possible_keys key key_len ref rows Extra 4341 SIMPLE t1 index_merge cola,colb cola,colb 3,3 NULL 32 Using intersect(cola,colb); Using where 435drop table t1; 436create table t1 ( 437a int, b int, 438filler1 char(200), filler2 char(200), 439key(a),key(b) 440); 441insert into t1 select @v:= seq % 10, @v, 't1', 'filler2' from seq_1_to_1000; 442create table t2 like t1; 443create table t3 ( 444a int, b int, 445filler1 char(200), filler2 char(200), 446key(a),key(b) 447) engine=merge union=(t1,t2); 448explain select * from t1 where a=1 and b=1; 449id select_type table type possible_keys key key_len ref rows Extra 4501 SIMPLE t1 index_merge a,b a,b 5,5 NULL # Using intersect(a,b); Using where 451explain select * from t3 where a=1 and b=1; 452id select_type table type possible_keys key key_len ref rows Extra 4531 SIMPLE t3 index_merge a,b a,b 5,5 NULL # Using intersect(a,b); Using where 454drop table t1, t2, t3; 455CREATE TABLE t1(a INT); 456INSERT INTO t1 VALUES(1); 457CREATE TABLE t2(a INT, b INT, dummy CHAR(16) DEFAULT '', KEY(a), KEY(b)); 458INSERT INTO t2(a,b) VALUES 459(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 460(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 461(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 462(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 463(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 464(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 465(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 466(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 467(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 468(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 469(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 470(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 471(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 472(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 473(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 474(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 475(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 476(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 477(1,2); 478LOCK TABLES t1 WRITE, t2 WRITE; 479INSERT INTO t2(a,b) VALUES(1,2); 480SELECT t2.a FROM t1,t2 WHERE t2.b=2 AND t2.a=1; 481a 4821 4831 484UNLOCK TABLES; 485DROP TABLE t1, t2; 486CREATE TABLE `t1` ( 487`a` int(11) DEFAULT NULL, 488`filler` char(200) DEFAULT NULL, 489`b` int(11) DEFAULT NULL, 490KEY `a` (`a`), 491KEY `b` (`b`) 492) ENGINE=MEMORY DEFAULT CHARSET=latin1; 493insert into t1 values 494(0, 'filler', 0), (1, 'filler', 1), (2, 'filler', 2), (3, 'filler', 3), 495(4, 'filler', 4), (5, 'filler', 5), (6, 'filler', 6), (7, 'filler', 7), 496(8, 'filler', 8), (9, 'filler', 9), (0, 'filler', 0), (1, 'filler', 1), 497(2, 'filler', 2), (3, 'filler', 3), (4, 'filler', 4), (5, 'filler', 5), 498(6, 'filler', 6), (7, 'filler', 7), (8, 'filler', 8), (9, 'filler', 9), 499(10, 'filler', 10), (11, 'filler', 11), (12, 'filler', 12), (13, 'filler', 13), 500(14, 'filler', 14), (15, 'filler', 15), (16, 'filler', 16), (17, 'filler', 17), 501(18, 'filler', 18), (19, 'filler', 19), (4, '5 ', 0), (5, '4 ', 0), 502(4, '4 ', 0), (4, 'qq ', 5), (5, 'qq ', 4), (4, 'zz ', 4); 503create table t2( 504`a` int(11) DEFAULT NULL, 505`filler` char(200) DEFAULT NULL, 506`b` int(11) DEFAULT NULL, 507KEY USING BTREE (`a`), 508KEY USING BTREE (`b`) 509) ENGINE=MEMORY DEFAULT CHARSET=latin1; 510insert into t2 select * from t1; 511must use sort-union rather than union: 512explain select * from t1 where a=4 or b=4; 513id select_type table type possible_keys key key_len ref rows Extra 5141 SIMPLE t1 index_merge a,b a,b 5,5 NULL # Using sort_union(a,b); Using where 515select * from t1 where a=4 or b=4; 516a filler b 5174 4 0 5184 5 0 5194 filler 4 5204 filler 4 5214 qq 5 5224 zz 4 5235 qq 4 524select * from t1 ignore index(a,b) where a=4 or b=4; 525a filler b 5264 4 0 5274 5 0 5284 filler 4 5294 filler 4 5304 qq 5 5314 zz 4 5325 qq 4 533must use union, not sort-union: 534explain select * from t2 where a=2 or b=2; 535id select_type table type possible_keys key key_len ref rows Extra 5361 SIMPLE t2 index_merge a,b a,b 5,5 NULL # Using union(a,b); Using where 537select * from t2 where a=2 or b=2; 538a filler b 5392 filler 2 5402 filler 2 541drop table t1, t2; 542CREATE TABLE t1 (a varchar(8), b set('a','b','c','d','e','f','g','h'), 543KEY b(b), KEY a(a)); 544INSERT INTO t1 VALUES ('y',''), ('z',''); 545SELECT b,a from t1 WHERE (b!='c' AND b!='f' && b!='h') OR 546(a='pure-S') OR (a='DE80337a') OR (a='DE80799'); 547b a 548 y 549 z 550DROP TABLE t1; 551# 552# BUG#40974: Incorrect query results when using clause evaluated using range check 553# 554create table t1 (a int); 555insert into t1 values (1),(2); 556create table t2(a int, b int); 557insert into t2 values (1,1), (2, 1000); 558create table t3 (a int, b int, filler char(100), key(a), key(b)); 559insert into t3 select 1000, 1000,'filler' from seq_1_to_1000; 560insert into t3 values (1,1,'data'); 561insert into t3 values (1,1,'data'); 562The plan should be ALL/ALL/ALL(Range checked for each record (index map: 0x3) 563explain select * from t1 564where exists (select 1 from t2, t3 565where t2.a=t1.a and (t3.a=t2.b or t3.b=t2.b or t3.b=t2.b+1)); 566id select_type table type possible_keys key key_len ref rows Extra 5671 PRIMARY t1 ALL NULL NULL NULL NULL 2 5681 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 5692 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 5702 MATERIALIZED t3 ALL a,b NULL NULL NULL 1002 Range checked for each record (index map: 0x3) 571select * from t1 572where exists (select 1 from t2, t3 573where t2.a=t1.a and (t3.a=t2.b or t3.b=t2.b or t3.b=t2.b+1)); 574a 5751 5762 577drop table t1, t2, t3; 578# 579# BUG#44810: index merge and order by with low sort_buffer_size 580# crashes server! 581# 582CREATE TABLE t1(a VARCHAR(128),b VARCHAR(128),KEY(A),KEY(B)); 583INSERT INTO t1 SELECT REPEAT('a',128),REPEAT('b',128) FROM seq_1_to_64; 584SET SESSION sort_buffer_size=1024*8; 585EXPLAIN 586SELECT * FROM t1 FORCE INDEX(a,b) WHERE a LIKE 'a%' OR b LIKE 'b%' 587ORDER BY a,b; 588id select_type table type possible_keys key key_len ref rows Extra 5891 SIMPLE t1 index_merge a,b a,b 131,131 NULL 64 Using sort_union(a,b); Using where; Using filesort 590SELECT * FROM t1 FORCE INDEX(a,b) WHERE a LIKE 'a%' OR b LIKE 'b%' 591ORDER BY a,b; 592SET SESSION sort_buffer_size=DEFAULT; 593DROP TABLE t1; 594End of 5.0 tests 595#---------------- ROR-index_merge tests ----------------------- 596create table t1 597( 598/* Field names reflect value(rowid) distribution, st=STairs, swt= SaWTooth */ 599st_a int not null default 0, 600swt1a int not null default 0, 601swt2a int not null default 0, 602st_b int not null default 0, 603swt1b int not null default 0, 604swt2b int not null default 0, 605/* fields/keys for row retrieval tests */ 606key1 int, 607key2 int, 608key3 int, 609key4 int, 610/* make rows much bigger then keys */ 611filler1 char (200), 612filler2 char (200), 613filler3 char (200), 614filler4 char (200), 615filler5 char (200), 616filler6 char (200), 617/* order of keys is important */ 618key sta_swt12a(st_a,swt1a,swt2a), 619key sta_swt1a(st_a,swt1a), 620key sta_swt2a(st_a,swt2a), 621key sta_swt21a(st_a,swt2a,swt1a), 622key st_a(st_a), 623key stb_swt1a_2b(st_b,swt1b,swt2a), 624key stb_swt1b(st_b,swt1b), 625key st_b(st_b), 626key(key1), 627key(key2), 628key(key3), 629key(key4) 630) ; 631create table t0 as select * from t1; 632# Printing of many insert into t0 values (....) disabled. 633alter table t1 disable keys; 634# Printing of many insert into t1 select .... from t0 disabled. 635# Printing of many insert into t1 (...) values (....) disabled. 636alter table t1 enable keys; 637select count(*) from t1; 638count(*) 63964801 640explain select key1,key2 from t1 where key1=100 and key2=100; 641id select_type table type possible_keys key key_len ref rows Extra 6421 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL 77 Using intersect(key1,key2); Using where; Using index 643select key1,key2 from t1 where key1=100 and key2=100; 644key1 key2 645100 100 646explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100; 647id select_type table type possible_keys key key_len ref rows Extra 6481 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 154 Using union(intersect(key1,key2),intersect(key3,key4)); Using where 649select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100; 650key1 key2 key3 key4 filler1 651100 100 100 100 key1-key2-key3-key4 652insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, -1, -1, 'key1-key2'); 653insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 100, 100, 'key4-key3'); 654explain select key1,key2,filler1 from t1 where key1=100 and key2=100; 655id select_type table type possible_keys key key_len ref rows Extra 6561 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL 77 Using intersect(key1,key2); Using where 657select key1,key2,filler1 from t1 where key1=100 and key2=100; 658key1 key2 filler1 659100 100 key1-key2-key3-key4 660100 100 key1-key2 661explain select key1,key2 from t1 where key1=100 and key2=100; 662id select_type table type possible_keys key key_len ref rows Extra 6631 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL 77 Using intersect(key1,key2); Using where; Using index 664select key1,key2 from t1 where key1=100 and key2=100; 665key1 key2 666100 100 667100 100 668explain select key1,key2,key3,key4 from t1 where key1=100 and key2=100 or key3=100 and key4=100; 669id select_type table type possible_keys key key_len ref rows Extra 6701 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 154 Using union(intersect(key1,key2),intersect(key3,key4)); Using where 671select key1,key2,key3,key4 from t1 where key1=100 and key2=100 or key3=100 and key4=100; 672key1 key2 key3 key4 673100 100 100 100 674100 100 -1 -1 675-1 -1 100 100 676explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100; 677id select_type table type possible_keys key key_len ref rows Extra 6781 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 154 Using union(intersect(key1,key2),intersect(key3,key4)); Using where 679select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100; 680key1 key2 key3 key4 filler1 681100 100 100 100 key1-key2-key3-key4 682100 100 -1 -1 key1-key2 683-1 -1 100 100 key4-key3 684explain select key1,key2,key3 from t1 where key1=100 and key2=100 and key3=100; 685id select_type table type possible_keys key key_len ref rows Extra 6861 SIMPLE t1 index_merge key1,key2,key3 key1,key2,key3 5,5,5 NULL 2 Using intersect(key1,key2,key3); Using where; Using index 687select key1,key2,key3 from t1 where key1=100 and key2=100 and key3=100; 688key1 key2 key3 689100 100 100 690insert into t1 (key1,key2,key3,key4,filler1) values (101,101,101,101, 'key1234-101'); 691explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=101; 692id select_type table type possible_keys key key_len ref rows Extra 6931 SIMPLE t1 index_merge key1,key2,key3 key1,key2,key3 5,5,5 NULL 83 Using union(intersect(key1,key2),key3); Using where 694select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=101; 695key1 key2 key3 key4 filler1 696100 100 100 100 key1-key2-key3-key4 697100 100 -1 -1 key1-key2 698101 101 101 101 key1234-101 699select key1,key2, filler1 from t1 where key1=100 and key2=100; 700key1 key2 filler1 701100 100 key1-key2-key3-key4 702100 100 key1-key2 703update t1 set filler1='to be deleted' where key1=100 and key2=100; 704update t1 set key1=200,key2=200 where key1=100 and key2=100; 705delete from t1 where key1=200 and key2=200; 706select key1,key2,filler1 from t1 where key2=100 and key2=200; 707key1 key2 filler1 708explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100; 709id select_type table type possible_keys key key_len ref rows Extra 7101 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 154 Using union(intersect(key1,key2),intersect(key3,key4)); Using where 711select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100; 712key1 key2 key3 key4 filler1 713-1 -1 100 100 key4-key3 714delete from t1 where key3=100 and key4=100; 715explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100; 716id select_type table type possible_keys key key_len ref rows Extra 7171 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 154 Using union(intersect(key1,key2),intersect(key3,key4)); Using where 718select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100; 719key1 key2 key3 key4 filler1 720explain select key1,key2 from t1 where key1=100 and key2=100; 721id select_type table type possible_keys key key_len ref rows Extra 7221 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL 77 Using intersect(key1,key2); Using where; Using index 723select key1,key2 from t1 where key1=100 and key2=100; 724key1 key2 725insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-1'); 726insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-2'); 727insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-3'); 728explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200; 729id select_type table type possible_keys key key_len ref rows Extra 7301 SIMPLE t1 index_merge key1,key2,key3,key4 key3,key1,key2,key4 5,5,5,5 NULL 137 Using union(key3,intersect(key1,key2),key4); Using where 731select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200; 732key1 key2 key3 key4 filler1 733100 100 200 200 key1-key2-key3-key4-3 734100 100 200 200 key1-key2-key3-key4-2 735100 100 200 200 key1-key2-key3-key4-1 736insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, -1, 200,'key4'); 737explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200; 738id select_type table type possible_keys key key_len ref rows Extra 7391 SIMPLE t1 index_merge key1,key2,key3,key4 key3,key1,key2,key4 5,5,5,5 NULL 148 Using union(key3,intersect(key1,key2),key4); Using where 740select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200; 741key1 key2 key3 key4 filler1 742100 100 200 200 key1-key2-key3-key4-3 743100 100 200 200 key1-key2-key3-key4-2 744100 100 200 200 key1-key2-key3-key4-1 745-1 -1 -1 200 key4 746insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 200, -1,'key3'); 747explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200; 748id select_type table type possible_keys key key_len ref rows Extra 7491 SIMPLE t1 index_merge key1,key2,key3,key4 key3,key1,key2,key4 5,5,5,5 NULL 159 Using union(key3,intersect(key1,key2),key4); Using where 750select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200; 751key1 key2 key3 key4 filler1 752100 100 200 200 key1-key2-key3-key4-3 753100 100 200 200 key1-key2-key3-key4-2 754100 100 200 200 key1-key2-key3-key4-1 755-1 -1 -1 200 key4 756-1 -1 200 -1 key3 757explain select * from t1 where st_a=1 and st_b=1; 758id select_type table type possible_keys key key_len ref rows Extra 7591 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b st_a,st_b 4,4 NULL 3515 Using intersect(st_a,st_b); Using where 760explain select st_a,st_b from t1 where st_a=1 and st_b=1; 761id select_type table type possible_keys key key_len ref rows Extra 7621 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b st_a,st_b 4,4 NULL 3515 Using intersect(st_a,st_b); Using where; Using index 763explain select st_a from t1 ignore index (st_a) where st_a=1 and st_b=1; 764id select_type table type possible_keys key key_len ref rows Extra 7651 SIMPLE t1 ref sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,stb_swt1a_2b,stb_swt1b,st_b st_b 4 const 15094 Using where 766explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1; 767id select_type table type possible_keys key key_len ref rows Extra 7681 SIMPLE t1 ref sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a sta_swt21a 12 const,const,const 971 769explain select * from t1 where st_b=1 and swt1b=1 and swt2b=1; 770id select_type table type possible_keys key key_len ref rows Extra 7711 SIMPLE t1 ref stb_swt1a_2b,stb_swt1b,st_b stb_swt1a_2b 8 const,const 3879 Using where 772explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1; 773id select_type table type possible_keys key key_len ref rows Extra 7741 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b sta_swt12a,stb_swt1a_2b 12,12 NULL 58 Using intersect(sta_swt12a,stb_swt1a_2b); Using where 775explain select * from t1 ignore index (sta_swt21a, stb_swt1a_2b) 776where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1; 777id select_type table type possible_keys key key_len ref rows Extra 7781 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,st_a,stb_swt1b,st_b sta_swt12a,stb_swt1b 12,8 NULL 58 Using intersect(sta_swt12a,stb_swt1b); Using where 779explain select * from t1 ignore index (sta_swt21a, sta_swt12a, stb_swt1a_2b) 780where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1; 781id select_type table type possible_keys key key_len ref rows Extra 7821 SIMPLE t1 index_merge sta_swt1a,sta_swt2a,st_a,stb_swt1b,st_b sta_swt1a,sta_swt2a,stb_swt1b 8,8,8 NULL 57 Using intersect(sta_swt1a,sta_swt2a,stb_swt1b); Using where 783explain select * from t1 ignore index (sta_swt21a, sta_swt12a, stb_swt1a_2b, stb_swt1b) 784where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1; 785id select_type table type possible_keys key key_len ref rows Extra 7861 SIMPLE t1 index_merge sta_swt1a,sta_swt2a,st_a,st_b sta_swt1a,sta_swt2a,st_b 8,8,4 NULL 223 Using intersect(sta_swt1a,sta_swt2a,st_b); Using where 787explain select * from t1 788where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1; 789id select_type table type possible_keys key key_len ref rows Extra 7901 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b sta_swt12a,stb_swt1a_2b 12,12 NULL 58 Using intersect(sta_swt12a,stb_swt1a_2b); Using where 791explain select * from t1 792where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1; 793id select_type table type possible_keys key key_len ref rows Extra 7941 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b sta_swt1a,stb_swt1b 8,8 NULL 232 Using intersect(sta_swt1a,stb_swt1b); Using where 795explain select st_a from t1 796where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1; 797id select_type table type possible_keys key key_len ref rows Extra 7981 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b sta_swt1a,stb_swt1b 8,8 NULL 232 Using intersect(sta_swt1a,stb_swt1b); Using where; Using index 799explain select st_a from t1 800where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1; 801id select_type table type possible_keys key key_len ref rows Extra 8021 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b sta_swt1a,stb_swt1b 8,8 NULL 232 Using intersect(sta_swt1a,stb_swt1b); Using where; Using index 803drop table t0,t1; 804create table t2 ( 805a char(10), 806b char(10), 807filler1 char(255), 808filler2 char(255), 809key(a(5)), 810key(b(5)) 811); 812select count(a) from t2 where a='BBBBBBBB'; 813count(a) 8144 815select count(a) from t2 where b='BBBBBBBB'; 816count(a) 8174 818expla_or_bin select count(a_or_b) from t2 where a_or_b='AAAAAAAA' a_or_bnd a_or_b='AAAAAAAA'; 819id select_type ta_or_ba_or_ble type possia_or_ble_keys key key_len ref rows Extra_or_b 8201 SIMPLE t2 ref a_or_b,a_or_b a_or_b 6 const 4 Using where 821select count(a) from t2 where a='AAAAAAAA' and b='AAAAAAAA'; 822count(a) 8234 824select count(a) from t2 ignore index(a,b) where a='AAAAAAAA' and b='AAAAAAAA'; 825count(a) 8264 827insert into t2 values ('ab', 'ab', 'uh', 'oh'); 828explain select a from t2 where a='ab'; 829id select_type table type possible_keys key key_len ref rows Extra 8301 SIMPLE t2 ref a a 6 const 1 Using where 831drop table t2; 832CREATE TABLE t1(c1 INT, c2 INT DEFAULT 0, c3 CHAR(255) DEFAULT '', 833KEY(c1), KEY(c2), KEY(c3)); 834INSERT INTO t1(c1) VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0), 835(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0); 836INSERT INTO t1 VALUES(0,0,0); 837CREATE TABLE t2(c1 int); 838INSERT INTO t2 VALUES(1); 839DELETE t1 FROM t1,t2 WHERE t1.c1=0 AND t1.c2=0; 840SELECT * FROM t1; 841c1 c2 c3 842DROP TABLE t1,t2; 843#---------------- Index merge test 2 ------------------------------------------- 844create table t1 845( 846key1 int not null, 847key2 int not null, 848INDEX i1(key1), 849INDEX i2(key2) 850); 851INSERT INTO t1 SELECT seq,200-seq FROM seq_0_to_200; 852explain select * from t1 where key1 < 5 or key2 > 197; 853id select_type table type possible_keys key key_len ref rows Extra 8541 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL 8 Using sort_union(i1,i2); Using where 855select * from t1 where key1 < 5 or key2 > 197; 856key1 key2 8570 200 8581 199 8592 198 8603 197 8614 196 862explain select * from t1 where key1 < 3 or key2 > 195; 863id select_type table type possible_keys key key_len ref rows Extra 8641 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL 8 Using sort_union(i1,i2); Using where 865select * from t1 where key1 < 3 or key2 > 195; 866key1 key2 8670 200 8681 199 8692 198 8703 197 8714 196 872alter table t1 add str1 char (255) not null, 873add zeroval int not null default 0, 874add str2 char (255) not null, 875add str3 char (255) not null; 876update t1 set str1='aaa', str2='bbb', str3=concat(key2, '-', key1 div 2, '_' ,if(key1 mod 2 = 0, 'a', 'A')); 877alter table t1 add primary key (str1, zeroval, str2, str3); 878explain select * from t1 where key1 < 5 or key2 > 197; 879id select_type table type possible_keys key key_len ref rows Extra 8801 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL 8 Using sort_union(i1,i2); Using where 881select * from t1 where key1 < 5 or key2 > 197; 882key1 key2 str1 zeroval str2 str3 8830 200 aaa 0 bbb 200-0_a 8841 199 aaa 0 bbb 199-0_A 8852 198 aaa 0 bbb 198-1_a 8863 197 aaa 0 bbb 197-1_A 8874 196 aaa 0 bbb 196-2_a 888explain select * from t1 where key1 < 3 or key2 > 195; 889id select_type table type possible_keys key key_len ref rows Extra 8901 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL 8 Using sort_union(i1,i2); Using where 891select * from t1 where key1 < 3 or key2 > 195; 892key1 key2 str1 zeroval str2 str3 8930 200 aaa 0 bbb 200-0_a 8941 199 aaa 0 bbb 199-0_A 8952 198 aaa 0 bbb 198-1_a 8963 197 aaa 0 bbb 197-1_A 8974 196 aaa 0 bbb 196-2_a 898drop table t1; 899create table t1 ( 900pk integer not null auto_increment primary key, 901key1 integer, 902key2 integer not null, 903filler char (200), 904index (key1), 905index (key2) 906); 907show warnings; 908Level Code Message 909INSERT INTO t1 (key1, key2, filler) 910SELECT seq/4, seq/8, 'filler-data' FROM seq_30_to_0; 911explain select pk from t1 where key1 = 1 and key2 = 1; 912id select_type table type possible_keys key key_len ref rows Extra 9131 SIMPLE t1 ref key1,key2 key1 5 const 4 Using where 914select pk from t1 where key2 = 1 and key1 = 1; 915pk 91626 91727 918select pk from t1 ignore index(key1,key2) where key2 = 1 and key1 = 1; 919pk 92026 92127 922drop table t1; 923create table t1 ( 924pk int primary key auto_increment, 925key1a int, 926key2a int, 927key1b int, 928key2b int, 929dummy1 int, 930dummy2 int, 931dummy3 int, 932dummy4 int, 933key3a int, 934key3b int, 935filler1 char (200), 936index i1(key1a, key1b), 937index i2(key2a, key2b), 938index i3(key3a, key3b) 939); 940create table t2 (a int); 941insert into t2 values (0),(1),(2),(3),(4),(NULL); 942insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b) 943select A.a, B.a, C.a, D.a, C.a, D.a from t2 A,t2 B,t2 C, t2 D; 944insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b) 945select key1a, key1b, key2a, key2b, key3a, key3b from t1; 946insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b) 947select key1a, key1b, key2a, key2b, key3a, key3b from t1; 948analyze table t1; 949Table Op Msg_type Msg_text 950test.t1 analyze status Engine-independent statistics collected 951test.t1 analyze status OK 952select count(*) from t1; 953count(*) 9545184 955explain select count(*) from t1 where 956key1a = 2 and key1b is null and key2a = 2 and key2b is null; 957id select_type table type possible_keys key key_len ref rows Extra 9581 SIMPLE t1 index_merge i1,i2 i1,i2 10,10 NULL REF Using intersect(i1,i2); Using where; Using index 959select count(*) from t1 where 960key1a = 2 and key1b is null and key2a = 2 and key2b is null; 961count(*) 9624 963explain select count(*) from t1 where 964key1a = 2 and key1b is null and key3a = 2 and key3b is null; 965id select_type table type possible_keys key key_len ref rows Extra 9661 SIMPLE t1 index_merge i1,i3 i1,i3 10,10 NULL REF Using intersect(i1,i3); Using where; Using index 967select count(*) from t1 where 968key1a = 2 and key1b is null and key3a = 2 and key3b is null; 969count(*) 9704 971drop table t1,t2; 972create table t1 ( 973id1 int, 974id2 date , 975index idx2 (id1,id2), 976index idx1 (id2) 977); 978insert into t1 values(1,'20040101'), (2,'20040102'); 979select * from t1 where id1 = 1 and id2= '20040101'; 980id1 id2 9811 2004-01-01 982drop table t1; 983drop view if exists v1; 984CREATE TABLE t1 ( 985`oid` int(11) unsigned NOT NULL auto_increment, 986`fk_bbk_niederlassung` int(11) unsigned NOT NULL, 987`fk_wochentag` int(11) unsigned NOT NULL, 988`uhrzeit_von` time NOT NULL COMMENT 'HH:MM', 989`uhrzeit_bis` time NOT NULL COMMENT 'HH:MM', 990`geloescht` tinyint(4) NOT NULL, 991`version` int(5) NOT NULL, 992PRIMARY KEY (`oid`), 993KEY `fk_bbk_niederlassung` (`fk_bbk_niederlassung`), 994KEY `fk_wochentag` (`fk_wochentag`), 995KEY `ix_version` (`version`) 996) DEFAULT CHARSET=latin1; 997insert into t1 values 998(1, 38, 1, '08:00:00', '13:00:00', 0, 1), 999(2, 38, 2, '08:00:00', '13:00:00', 0, 1), 1000(3, 38, 3, '08:00:00', '13:00:00', 0, 1), 1001(4, 38, 4, '08:00:00', '13:00:00', 0, 1), 1002(5, 38, 5, '08:00:00', '13:00:00', 0, 1), 1003(6, 38, 5, '08:00:00', '13:00:00', 1, 2), 1004(7, 38, 3, '08:00:00', '13:00:00', 1, 2), 1005(8, 38, 1, '08:00:00', '13:00:00', 1, 2), 1006(9, 38, 2, '08:00:00', '13:00:00', 1, 2), 1007(10, 38, 4, '08:00:00', '13:00:00', 1, 2), 1008(11, 38, 1, '08:00:00', '13:00:00', 0, 3), 1009(12, 38, 2, '08:00:00', '13:00:00', 0, 3), 1010(13, 38, 3, '08:00:00', '13:00:00', 0, 3), 1011(14, 38, 4, '08:00:00', '13:00:00', 0, 3), 1012(15, 38, 5, '08:00:00', '13:00:00', 0, 3), 1013(16, 38, 4, '08:00:00', '13:00:00', 0, 4), 1014(17, 38, 5, '08:00:00', '13:00:00', 0, 4), 1015(18, 38, 1, '08:00:00', '13:00:00', 0, 4), 1016(19, 38, 2, '08:00:00', '13:00:00', 0, 4), 1017(20, 38, 3, '08:00:00', '13:00:00', 0, 4), 1018(21, 7, 1, '08:00:00', '13:00:00', 0, 1), 1019(22, 7, 2, '08:00:00', '13:00:00', 0, 1), 1020(23, 7, 3, '08:00:00', '13:00:00', 0, 1), 1021(24, 7, 4, '08:00:00', '13:00:00', 0, 1), 1022(25, 7, 5, '08:00:00', '13:00:00', 0, 1); 1023create view v1 as 1024select 1025zeit1.oid AS oid, 1026zeit1.fk_bbk_niederlassung AS fk_bbk_niederlassung, 1027zeit1.fk_wochentag AS fk_wochentag, 1028zeit1.uhrzeit_von AS uhrzeit_von, 1029zeit1.uhrzeit_bis AS uhrzeit_bis, 1030zeit1.geloescht AS geloescht, 1031zeit1.version AS version 1032from 1033t1 zeit1 1034where 1035(zeit1.version = 1036(select max(zeit2.version) AS `max(version)` 1037 from t1 zeit2 1038where 1039((zeit1.fk_bbk_niederlassung = zeit2.fk_bbk_niederlassung) and 1040(zeit1.fk_wochentag = zeit2.fk_wochentag) and 1041(zeit1.uhrzeit_von = zeit2.uhrzeit_von) and 1042(zeit1.uhrzeit_bis = zeit2.uhrzeit_bis) 1043) 1044) 1045) 1046and (zeit1.geloescht = 0); 1047select * from v1 where oid = 21; 1048oid fk_bbk_niederlassung fk_wochentag uhrzeit_von uhrzeit_bis geloescht version 104921 7 1 08:00:00 13:00:00 0 1 1050drop view v1; 1051drop table t1; 1052CREATE TABLE t1( 1053t_cpac varchar(2) NOT NULL, 1054t_vers varchar(4) NOT NULL, 1055t_rele varchar(2) NOT NULL, 1056t_cust varchar(4) NOT NULL, 1057filler1 char(250) default NULL, 1058filler2 char(250) default NULL, 1059PRIMARY KEY (t_cpac,t_vers,t_rele,t_cust), 1060UNIQUE KEY IX_4 (t_cust,t_cpac,t_vers,t_rele), 1061KEY IX_5 (t_vers,t_rele,t_cust) 1062); 1063insert into t1 values 1064('tm','2.5 ','a ',' ','',''), ('tm','2.5U','a ','stnd','',''), 1065('da','3.3 ','b ',' ','',''), ('da','3.3U','b ','stnd','',''), 1066('tl','7.6 ','a ',' ','',''), ('tt','7.6 ','a ',' ','',''), 1067('bc','B61 ','a ',' ','',''), ('bp','B61 ','a ',' ','',''), 1068('ca','B61 ','a ',' ','',''), ('ci','B61 ','a ',' ','',''), 1069('cp','B61 ','a ',' ','',''), ('dm','B61 ','a ',' ','',''), 1070('ec','B61 ','a ',' ','',''), ('ed','B61 ','a ',' ','',''), 1071('fm','B61 ','a ',' ','',''), ('nt','B61 ','a ',' ','',''), 1072('qm','B61 ','a ',' ','',''), ('tc','B61 ','a ',' ','',''), 1073('td','B61 ','a ',' ','',''), ('tf','B61 ','a ',' ','',''), 1074('tg','B61 ','a ',' ','',''), ('ti','B61 ','a ',' ','',''), 1075('tp','B61 ','a ',' ','',''), ('ts','B61 ','a ',' ','',''), 1076('wh','B61 ','a ',' ','',''), ('bc','B61U','a ','stnd','',''), 1077('bp','B61U','a ','stnd','',''), ('ca','B61U','a ','stnd','',''), 1078('ci','B61U','a ','stnd','',''), ('cp','B61U','a ','stnd','',''), 1079('dm','B61U','a ','stnd','',''), ('ec','B61U','a ','stnd','',''), 1080('fm','B61U','a ','stnd','',''), ('nt','B61U','a ','stnd','',''), 1081('qm','B61U','a ','stnd','',''), ('tc','B61U','a ','stnd','',''), 1082('td','B61U','a ','stnd','',''), ('tf','B61U','a ','stnd','',''), 1083('tg','B61U','a ','stnd','',''), ('ti','B61U','a ','stnd','',''), 1084('tp','B61U','a ','stnd','',''), ('ts','B61U','a ','stnd','',''), 1085('wh','B61U','a ','stnd','',''); 1086show create table t1; 1087Table Create Table 1088t1 CREATE TABLE `t1` ( 1089 `t_cpac` varchar(2) NOT NULL, 1090 `t_vers` varchar(4) NOT NULL, 1091 `t_rele` varchar(2) NOT NULL, 1092 `t_cust` varchar(4) NOT NULL, 1093 `filler1` char(250) DEFAULT NULL, 1094 `filler2` char(250) DEFAULT NULL, 1095 PRIMARY KEY (`t_cpac`,`t_vers`,`t_rele`,`t_cust`), 1096 UNIQUE KEY `IX_4` (`t_cust`,`t_cpac`,`t_vers`,`t_rele`), 1097 KEY `IX_5` (`t_vers`,`t_rele`,`t_cust`) 1098) ENGINE=MyISAM DEFAULT CHARSET=latin1 1099select t_vers,t_rele,t_cust,filler1 from t1 where t_vers = '7.6'; 1100t_vers t_rele t_cust filler1 11017.6 a 11027.6 a 1103select t_vers,t_rele,t_cust,filler1 from t1 where t_vers = '7.6' 1104 and t_rele='a' and t_cust = ' '; 1105t_vers t_rele t_cust filler1 11067.6 a 11077.6 a 1108drop table t1; 1109create table t1 ( 1110pk int(11) not null auto_increment, 1111a int(11) not null default '0', 1112b int(11) not null default '0', 1113c int(11) not null default '0', 1114filler1 datetime, filler2 varchar(15), 1115filler3 longtext, 1116kp1 varchar(4), kp2 varchar(7), 1117kp3 varchar(2), kp4 varchar(4), 1118kp5 varchar(7), 1119filler4 char(1), 1120primary key (pk), 1121key idx1(a,b,c), 1122key idx2(c), 1123key idx3(kp1,kp2,kp3,kp4,kp5) 1124) default charset=latin1; 1125set @fill=NULL; 1126SELECT COUNT(*) FROM t1 WHERE b = 0 AND a = 0 AND c = 13286427 AND 1127kp1='279' AND kp2='ELM0678' AND kp3='6' AND kp4='10' AND kp5 = 'R '; 1128COUNT(*) 11291 1130drop table t1; 1131create table t1 1132( 1133key1 int not null, 1134key2 int not null default 0, 1135key3 int not null default 0 1136); 1137insert into t1(key1) select seq from seq_1_to_1024; 1138alter table t1 add index i2(key2); 1139alter table t1 add index i3(key3); 1140update t1 set key2=key1,key3=key1; 1141insert into t1 select 10000+key1, 10000+key2,10000+key3 from t1; 1142analyze table t1; 1143Table Op Msg_type Msg_text 1144test.t1 analyze status Engine-independent statistics collected 1145test.t1 analyze status OK 1146explain select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40); 1147id select_type table type possible_keys key key_len ref rows Extra 11481 SIMPLE t1 index_merge i2,i3 i3,i2 4,4 NULL REF Using sort_union(i3,i2); Using where 1149select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40); 1150key1 key2 key3 115131 31 31 115232 32 32 115333 33 33 115434 34 34 115535 35 35 115636 36 36 115737 37 37 115838 38 38 115939 39 39 1160drop table t1; 1161# 1162# Bug#56423: Different count with SELECT and CREATE SELECT queries 1163# 1164CREATE TABLE t1 ( 1165a INT, 1166b INT, 1167c INT, 1168d INT, 1169PRIMARY KEY (a), 1170KEY (c), 1171KEY bd (b,d) 1172); 1173INSERT INTO t1 VALUES 1174(1, 0, 1, 0), 1175(2, 1, 1, 1), 1176(3, 1, 1, 1), 1177(4, 0, 1, 1); 1178EXPLAIN 1179SELECT a 1180FROM t1 1181WHERE c = 1 AND b = 1 AND d = 1; 1182id select_type table type possible_keys key key_len ref rows Extra 11831 SIMPLE t1 ref c,bd bd 10 const,const 2 Using where 1184CREATE TABLE t2 ( a INT ) 1185SELECT a 1186FROM t1 1187WHERE c = 1 AND b = 1 AND d = 1; 1188SELECT * FROM t2; 1189a 11902 11913 1192DROP TABLE t1, t2; 1193CREATE TABLE t1( a INT, b INT, KEY(a), KEY(b) ); 1194INSERT INTO t1 VALUES (1, 2), (1, 2), (1, 2), (1, 2); 1195SELECT * FROM t1 FORCE INDEX(a, b) WHERE a = 1 AND b = 2; 1196a b 11971 2 11981 2 11991 2 12001 2 1201DROP TABLE t1; 1202# Code coverage of fix. 1203CREATE TABLE t1 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT); 1204INSERT INTO t1 (b) VALUES (1); 1205UPDATE t1 SET b = 2 WHERE a = 1; 1206SELECT * FROM t1; 1207a b 12081 2 1209CREATE TABLE t2 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b VARCHAR(1) ); 1210INSERT INTO t2 (b) VALUES ('a'); 1211UPDATE t2 SET b = 'b' WHERE a = 1; 1212SELECT * FROM t2; 1213a b 12141 b 1215DROP TABLE t1, t2; 1216#---------------- 2-sweeps read Index merge test 2 ------------------------------- 1217create table t1 ( 1218pk int primary key, 1219key1 int, 1220key2 int, 1221filler char(200), 1222filler2 char(200), 1223index(key1), 1224index(key2) 1225); 1226insert into t1 select seq, seq, seq, 'filler-data', 'filler-data-2' 1227from seq_1000_to_1; 1228select * from t1 where (key1 >= 2 and key1 <= 10) or (pk >= 4 and pk <=8 ); 1229pk key1 key2 filler filler2 123010 10 10 filler-data filler-data-2 12319 9 9 filler-data filler-data-2 12328 8 8 filler-data filler-data-2 12337 7 7 filler-data filler-data-2 12346 6 6 filler-data filler-data-2 12355 5 5 filler-data filler-data-2 12364 4 4 filler-data filler-data-2 12373 3 3 filler-data filler-data-2 12382 2 2 filler-data filler-data-2 1239set @maxv=1000; 1240select * from t1 where 1241(pk < 5) or (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) or (pk > @maxv-10) 1242or key1=18 or key1=60; 1243pk key1 key2 filler filler2 12441000 1000 1000 filler-data filler-data-2 1245999 999 999 filler-data filler-data-2 1246998 998 998 filler-data filler-data-2 1247997 997 997 filler-data filler-data-2 1248996 996 996 filler-data filler-data-2 1249995 995 995 filler-data filler-data-2 1250994 994 994 filler-data filler-data-2 1251993 993 993 filler-data filler-data-2 1252992 992 992 filler-data filler-data-2 1253991 991 991 filler-data filler-data-2 125460 60 60 filler-data filler-data-2 125554 54 54 filler-data filler-data-2 125653 53 53 filler-data filler-data-2 125752 52 52 filler-data filler-data-2 125851 51 51 filler-data filler-data-2 125950 50 50 filler-data filler-data-2 126018 18 18 filler-data filler-data-2 126114 14 14 filler-data filler-data-2 126213 13 13 filler-data filler-data-2 126312 12 12 filler-data filler-data-2 126411 11 11 filler-data filler-data-2 12654 4 4 filler-data filler-data-2 12663 3 3 filler-data filler-data-2 12672 2 2 filler-data filler-data-2 12681 1 1 filler-data filler-data-2 1269select * from t1 where 1270(pk < 5) or (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) or (pk > @maxv-10) 1271or key1 < 3 or key1 > @maxv-11; 1272pk key1 key2 filler filler2 12731000 1000 1000 filler-data filler-data-2 1274999 999 999 filler-data filler-data-2 1275998 998 998 filler-data filler-data-2 1276997 997 997 filler-data filler-data-2 1277996 996 996 filler-data filler-data-2 1278995 995 995 filler-data filler-data-2 1279994 994 994 filler-data filler-data-2 1280993 993 993 filler-data filler-data-2 1281992 992 992 filler-data filler-data-2 1282991 991 991 filler-data filler-data-2 1283990 990 990 filler-data filler-data-2 128454 54 54 filler-data filler-data-2 128553 53 53 filler-data filler-data-2 128652 52 52 filler-data filler-data-2 128751 51 51 filler-data filler-data-2 128850 50 50 filler-data filler-data-2 128914 14 14 filler-data filler-data-2 129013 13 13 filler-data filler-data-2 129112 12 12 filler-data filler-data-2 129211 11 11 filler-data filler-data-2 12934 4 4 filler-data filler-data-2 12943 3 3 filler-data filler-data-2 12952 2 2 filler-data filler-data-2 12961 1 1 filler-data filler-data-2 1297select * from t1 where 1298(pk < 5) or (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) or (pk > @maxv-10) 1299or 1300(key1 < 5) or (key1 > 10 and key1 < 15) or (key1 >= 50 and key1 < 55 ) or (key1 > @maxv-10); 1301pk key1 key2 filler filler2 13021000 1000 1000 filler-data filler-data-2 1303999 999 999 filler-data filler-data-2 1304998 998 998 filler-data filler-data-2 1305997 997 997 filler-data filler-data-2 1306996 996 996 filler-data filler-data-2 1307995 995 995 filler-data filler-data-2 1308994 994 994 filler-data filler-data-2 1309993 993 993 filler-data filler-data-2 1310992 992 992 filler-data filler-data-2 1311991 991 991 filler-data filler-data-2 131254 54 54 filler-data filler-data-2 131353 53 53 filler-data filler-data-2 131452 52 52 filler-data filler-data-2 131551 51 51 filler-data filler-data-2 131650 50 50 filler-data filler-data-2 131714 14 14 filler-data filler-data-2 131813 13 13 filler-data filler-data-2 131912 12 12 filler-data filler-data-2 132011 11 11 filler-data filler-data-2 13214 4 4 filler-data filler-data-2 13223 3 3 filler-data filler-data-2 13232 2 2 filler-data filler-data-2 13241 1 1 filler-data filler-data-2 1325select * from t1 where 1326(pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) 1327or 1328(key1 < 5) or (key1 > @maxv-10); 1329pk key1 key2 filler filler2 13301000 1000 1000 filler-data filler-data-2 1331999 999 999 filler-data filler-data-2 1332998 998 998 filler-data filler-data-2 1333997 997 997 filler-data filler-data-2 1334996 996 996 filler-data filler-data-2 1335995 995 995 filler-data filler-data-2 1336994 994 994 filler-data filler-data-2 1337993 993 993 filler-data filler-data-2 1338992 992 992 filler-data filler-data-2 1339991 991 991 filler-data filler-data-2 134054 54 54 filler-data filler-data-2 134153 53 53 filler-data filler-data-2 134252 52 52 filler-data filler-data-2 134351 51 51 filler-data filler-data-2 134450 50 50 filler-data filler-data-2 134514 14 14 filler-data filler-data-2 134613 13 13 filler-data filler-data-2 134712 12 12 filler-data filler-data-2 134811 11 11 filler-data filler-data-2 13494 4 4 filler-data filler-data-2 13503 3 3 filler-data filler-data-2 13512 2 2 filler-data filler-data-2 13521 1 1 filler-data filler-data-2 1353drop table t1; 1354#---------------- Clustered PK ROR-index_merge tests ----------------------------- 1355create table t1 1356( 1357pk1 int not null, 1358pk2 int not null, 1359key1 int not null, 1360key2 int not null, 1361pktail1ok int not null, 1362pktail2ok int not null, 1363pktail3bad int not null, 1364pktail4bad int not null, 1365pktail5bad int not null, 1366pk2copy int not null, 1367badkey int not null, 1368filler1 char (200), 1369filler2 char (200), 1370key (key1), 1371key (key2), 1372/* keys with tails from CPK members */ 1373key (pktail1ok, pk1), 1374key (pktail2ok, pk1, pk2), 1375key (pktail3bad, pk2, pk1), 1376key (pktail4bad, pk1, pk2copy), 1377key (pktail5bad, pk1, pk2, pk2copy), 1378primary key (pk1, pk2) 1379); 1380explain select * from t1 where pk1 = 1 and pk2 < 80 and key1=0; 1381id select_type table type possible_keys key key_len ref rows Extra 13821 SIMPLE t1 range PRIMARY,key1 PRIMARY 8 NULL 7 Using index condition; Using where 1383select * from t1 where pk1 = 1 and pk2 < 80 and key1=0; 1384pk1 pk2 key1 key2 pktail1ok pktail2ok pktail3bad pktail4bad pktail5bad pk2copy badkey filler1 filler2 13851 10 0 0 0 0 0 0 0 10 0 filler-data-10 filler2 13861 11 0 0 0 0 0 0 0 11 0 filler-data-11 filler2 13871 12 0 0 0 0 0 0 0 12 0 filler-data-12 filler2 13881 13 0 0 0 0 0 0 0 13 0 filler-data-13 filler2 13891 14 0 0 0 0 0 0 0 14 0 filler-data-14 filler2 13901 15 0 0 0 0 0 0 0 15 0 filler-data-15 filler2 13911 16 0 0 0 0 0 0 0 16 0 filler-data-16 filler2 13921 17 0 0 0 0 0 0 0 17 0 filler-data-17 filler2 13931 18 0 0 0 0 0 0 0 18 0 filler-data-18 filler2 13941 19 0 0 0 0 0 0 0 19 0 filler-data-19 filler2 1395explain select pk1,pk2 from t1 where key1 = 10 and key2=10 and 2*pk1+1 < 2*96+1; 1396id select_type table type possible_keys key key_len ref rows Extra 13971 SIMPLE t1 index_merge key1,key2 key1,key2 4,4 NULL 1 Using intersect(key1,key2); Using where 1398select pk1,pk2 from t1 where key1 = 10 and key2=10 and 2*pk1+1 < 2*96+1; 1399pk1 pk2 140095 59 140195 58 140295 57 140395 56 140495 55 140595 54 140695 53 140795 52 140895 51 140995 50 1410explain select * from t1 where badkey=1 and key1=10; 1411id select_type table type possible_keys key key_len ref rows Extra 14121 SIMPLE t1 ref key1 key1 4 const 91 Using where 1413set @tmp_index_merge_ror_cpk=@@optimizer_switch; 1414set optimizer_switch='extended_keys=off'; 1415explain select * from t1 where pk1 < 7500 and key1 = 10; 1416id select_type table type possible_keys key key_len ref rows Extra 14171 SIMPLE t1 ref PRIMARY,key1 key1 4 const ROWS Using where 1418set optimizer_switch=@tmp_index_merge_ror_cpk; 1419explain select * from t1 where pktail1ok=1 and key1=10; 1420id select_type table type possible_keys key key_len ref rows Extra 14211 SIMPLE t1 ref key1,pktail1ok pktail1ok 4 const 76 Using where 1422explain select * from t1 where pktail2ok=1 and key1=10; 1423id select_type table type possible_keys key key_len ref rows Extra 14241 SIMPLE t1 ref key1,pktail2ok pktail2ok 4 const 82 Using where 1425explain select * from t1 where (pktail2ok=1 and pk1< 50000) or key1=10; 1426id select_type table type possible_keys key key_len ref rows Extra 14271 SIMPLE t1 index_merge PRIMARY,key1,pktail2ok pktail2ok,key1 8,4 NULL 173 Using sort_union(pktail2ok,key1); Using where 1428explain select * from t1 where pktail3bad=1 and key1=10; 1429id select_type table type possible_keys key key_len ref rows Extra 14301 SIMPLE t1 ref key1,pktail3bad pktail3bad 4 const 73 Using where 1431explain select * from t1 where pktail4bad=1 and key1=10; 1432id select_type table type possible_keys key key_len ref rows Extra 14331 SIMPLE t1 ref key1,pktail4bad pktail4bad 4 const 82 Using where 1434explain select * from t1 where pktail5bad=1 and key1=10; 1435id select_type table type possible_keys key key_len ref rows Extra 14361 SIMPLE t1 ref key1,pktail5bad pktail5bad 4 const 69 Using where 1437explain select pk1,pk2,key1,key2 from t1 where key1 = 10 and key2=10 limit 10; 1438id select_type table type possible_keys key key_len ref rows Extra 14391 SIMPLE t1 index_merge key1,key2 key1,key2 4,4 NULL 1 Using intersect(key1,key2); Using where 1440select pk1,pk2,key1,key2 from t1 where key1 = 10 and key2=10 limit 10; 1441pk1 pk2 key1 key2 1442104 49 10 10 1443104 48 10 10 1444104 47 10 10 1445104 46 10 10 1446104 45 10 10 1447104 44 10 10 1448104 43 10 10 1449104 42 10 10 1450104 41 10 10 1451104 40 10 10 1452drop table t1; 1453create table t1 1454( 1455RUNID varchar(22), 1456SUBMITNR varchar(5), 1457ORDERNR char(1), 1458PROGRAMM varchar(8), 1459TESTID varchar(4), 1460UCCHECK char(1), 1461ETEXT varchar(80), 1462ETEXT_TYPE char(1), 1463INFO char(1), 1464SEVERITY tinyint(3), 1465TADIRFLAG char(1), 1466PRIMARY KEY (RUNID,SUBMITNR,ORDERNR,PROGRAMM,TESTID,UCCHECK), 1467KEY `TVERM~KEY` (PROGRAMM,TESTID,UCCHECK) 1468) DEFAULT CHARSET=latin1; 1469update t1 set `ETEXT` = '', `ETEXT_TYPE`='', `INFO`='', `SEVERITY`='', `TADIRFLAG`='' 1470WHERE 1471`RUNID`= '' AND `SUBMITNR`= '' AND `ORDERNR`='' AND `PROGRAMM`='' AND 1472`TESTID`='' AND `UCCHECK`=''; 1473drop table t1; 1474# 1475# Bug#50402 Optimizer producing wrong results when using Index Merge on InnoDB 1476# 1477CREATE TABLE t1 (f1 INT, PRIMARY KEY (f1)); 1478INSERT INTO t1 VALUES (2); 1479CREATE TABLE t2 (f1 INT, f2 INT, f3 char(1), 1480PRIMARY KEY (f1), KEY (f2), KEY (f3) ); 1481INSERT INTO t2 VALUES (1, 1, 'h'), (2, 3, 'h'), (3, 2, ''), (4, 2, ''); 1482SELECT t1.f1 FROM t1 1483WHERE (SELECT COUNT(*) FROM t2 WHERE t2.f3 = 'h' AND t2.f2 = t1.f1) = 0 AND t1.f1 = 2; 1484f1 14852 1486EXPLAIN SELECT t1.f1 FROM t1 1487WHERE (SELECT COUNT(*) FROM t2 WHERE t2.f3 = 'h' AND t2.f2 = t1.f1) = 0 AND t1.f1 = 2; 1488id select_type table type possible_keys key key_len ref rows Extra 14891 PRIMARY t1 system PRIMARY NULL NULL NULL 1 14902 SUBQUERY t2 ref f2,f3 f2 5 const 2 Using where 1491DROP TABLE t1,t2; 1492create table t0 (a int); 1493insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 1494create table t1 (a int, b int, c int, filler char(100), 1495key(a), key(b), key(c)); 1496insert into t1 select 1497A.a * B.a*10 + C.a*100, 1498A.a * B.a*10 + C.a*100, 1499A.a, 1500'filler' 1501from t0 A, t0 B, t0 C; 1502This should use union: 1503explain select * from t1 where a=1 or b=1; 1504id select_type table type possible_keys key key_len ref rows Extra 15051 SIMPLE t1 index_merge a,b a,b 5,5 NULL 2 Using union(a,b); Using where 1506This should use ALL: 1507set optimizer_switch='default,index_merge=off,rowid_filter=off'; 1508explain select * from t1 where a=1 or b=1; 1509id select_type table type possible_keys key key_len ref rows Extra 15101 SIMPLE t1 ALL a,b NULL NULL NULL 1000 Using where 1511This should use sort-union: 1512set optimizer_switch='default,index_merge_union=off,rowid_filter=off'; 1513explain select * from t1 where a=1 or b=1; 1514id select_type table type possible_keys key key_len ref rows Extra 15151 SIMPLE t1 index_merge a,b a,b 5,5 NULL 2 Using sort_union(a,b); Using where 1516This will use sort-union: 1517set optimizer_switch=default; 1518explain select * from t1 where a<1 or b <1; 1519id select_type table type possible_keys key key_len ref rows Extra 15201 SIMPLE t1 index_merge a,b a,b 5,5 NULL 38 Using sort_union(a,b); Using where 1521This should use ALL: 1522set optimizer_switch='default,index_merge_sort_union=off,rowid_filter=off'; 1523explain select * from t1 where a<1 or b <1; 1524id select_type table type possible_keys key key_len ref rows Extra 15251 SIMPLE t1 ALL a,b NULL NULL NULL 1000 Using where 1526This should use ALL: 1527set optimizer_switch='default,index_merge=off,rowid_filter=off'; 1528explain select * from t1 where a<1 or b <1; 1529id select_type table type possible_keys key key_len ref rows Extra 15301 SIMPLE t1 ALL a,b NULL NULL NULL 1000 Using where 1531This will use sort-union: 1532set optimizer_switch='default,index_merge_union=off,rowid_filter=off'; 1533explain select * from t1 where a<1 or b <1; 1534id select_type table type possible_keys key key_len ref rows Extra 15351 SIMPLE t1 index_merge a,b a,b 5,5 NULL 38 Using sort_union(a,b); Using where 1536alter table t1 add d int, add key(d); 1537update t1 set d=a; 1538This will use sort_union: 1539set optimizer_switch=default; 1540explain select * from t1 where (a=3 or b in (1,2)) and (c=3 or d=4); 1541id select_type table type possible_keys key key_len ref rows Extra 15421 SIMPLE t1 index_merge a,b,c,d a,b 5,5 NULL 3 Using sort_union(a,b); Using where 1543And if we disable sort_union, union: 1544set optimizer_switch='default,index_merge_sort_union=off,rowid_filter=off'; 1545explain select * from t1 where (a=3 or b in (1,2)) and (c=3 or d=4); 1546id select_type table type possible_keys key key_len ref rows Extra 15471 SIMPLE t1 index_merge a,b,c,d c,d 5,5 NULL 100 Using union(c,d); Using where 1548drop table t1; 1549create table t1 ( 1550a int, b int, c int, 1551filler1 char(200), filler2 char(200), 1552key(a),key(b),key(c) 1553); 1554insert into t1 1555select A.a+10*B.a, A.a+10*B.a, A.a+10*B.a+100*C.a, 'foo', 'bar' 1556from t0 A, t0 B, t0 C, t0 D where D.a<5; 1557This should be intersect: 1558set optimizer_switch=default; 1559explain select * from t1 where a=10 and b=10; 1560id select_type table type possible_keys key key_len ref rows Extra 15611 SIMPLE t1 index_merge a,b a,b 5,5 NULL 1 Using intersect(a,b); Using where 1562No intersect when index_merge is disabled: 1563set optimizer_switch='default,index_merge=off,rowid_filter=off'; 1564explain select * from t1 where a=10 and b=10; 1565id select_type table type possible_keys key key_len ref rows Extra 15661 SIMPLE t1 ref a,b a 5 const 49 Using where 1567No intersect if it is disabled: 1568set optimizer_switch='default,index_merge_sort_intersection=off,index_merge_intersection=off,rowid_filter=off'; 1569explain select * from t1 where a=10 and b=10; 1570id select_type table type possible_keys key key_len ref rows Extra 15711 SIMPLE t1 ref a,b a 5 const 49 Using where 1572Do intersect when union was disabled 1573set optimizer_switch='default,index_merge_union=off,rowid_filter=off'; 1574explain select * from t1 where a=10 and b=10; 1575id select_type table type possible_keys key key_len ref rows Extra 15761 SIMPLE t1 index_merge a,b a,b 5,5 NULL 1 Using intersect(a,b); Using where 1577Do intersect when sort_union was disabled 1578set optimizer_switch='default,index_merge_sort_union=off,rowid_filter=off'; 1579explain select * from t1 where a=10 and b=10; 1580id select_type table type possible_keys key key_len ref rows Extra 15811 SIMPLE t1 index_merge a,b a,b 5,5 NULL 1 Using intersect(a,b); Using where 1582This will use intersection inside a union: 1583set optimizer_switch=default; 1584explain select * from t1 where a=10 and b=10 or c=10; 1585id select_type table type possible_keys key key_len ref rows Extra 15861 SIMPLE t1 index_merge a,b,c a,b,c 5,5,5 NULL 6 Using union(intersect(a,b),c); Using where 1587Should be only union left: 1588set optimizer_switch='default,index_merge_intersection=off,rowid_filter=off'; 1589explain select * from t1 where a=10 and b=10 or c=10; 1590id select_type table type possible_keys key key_len ref rows Extra 15911 SIMPLE t1 index_merge a,b,c a,c 5,5 NULL 54 Using union(a,c); Using where 1592This will switch to sort-union (intersection will be gone, too, 1593that's a known limitation: 1594set optimizer_switch='default,index_merge_union=off,rowid_filter=off'; 1595explain select * from t1 where a=10 and b=10 or c=10; 1596id select_type table type possible_keys key key_len ref rows Extra 15971 SIMPLE t1 index_merge a,b,c a,c 5,5 NULL 54 Using sort_union(a,c); Using where 1598set optimizer_switch=default; 1599drop table t0, t1; 1600# 1601# BUG#834514 Assertion `!table || (!table->read_set || bitmap_is_set(...' with aggregates 1602# 1603CREATE TABLE t1 ( a int , b int, c int, KEY (b), PRIMARY KEY (a)) ; 1604INSERT INTO t1 VALUES (1,4,0),(5,0,0),(6,7,0),(7,7,0),(8,1,0),(9,7,0),(10,1,0); 1605CREATE TABLE t2 ( b int, c int, KEY (c,b)) ; 1606INSERT INTO t2 VALUES (7,0),(1,0),(7,0),(1,0); 1607CREATE TABLE t3 ( a int ) ; 1608SELECT COUNT(DISTINCT t2.b), CONCAT(t1.c) 1609FROM t1, t2 1610WHERE (t2.c = t1.c) 1611AND ( 1612t1.b IN ( 4 ) 1613OR t1.a = 137 1614AND EXISTS ( SELECT a FROM t3 ) 1615) 1616GROUP BY 2; 1617COUNT(DISTINCT t2.b) CONCAT(t1.c) 16182 0 1619DROP TABLE t1,t2,t3; 1620# 1621# MDEV-4556 Server crashes in SEL_ARG::rb_insert with index_merge+index_merge_sort_union, FORCE INDEX 1622# 1623CREATE TABLE t1 ( 1624pk int, 1625code char(2), 1626population_rate int, 1627area_rate int, 1628primary key (pk), 1629index (code), 1630key (population_rate), 1631key (area_rate) 1632); 1633INSERT INTO t1 VALUES (1,'WI',20, 23), (2, 'WA', 13, 18); 1634EXPLAIN 1635SELECT * FROM t1 FORCE INDEX ( PRIMARY, population_rate, area_rate, code ) 1636WHERE pk = 1 OR population_rate = 1 OR ( area_rate IN ( 1,2 ) OR area_rate IS NULL ) 1637AND (population_rate = 25 OR area_rate BETWEEN 2 AND 25 OR code BETWEEN 'MA' AND 'TX'); 1638id select_type table type possible_keys key key_len ref rows Extra 16391 SIMPLE t1 index_merge PRIMARY,code,population_rate,area_rate PRIMARY,population_rate,area_rate,code 4,5,5,3 NULL 2 Using sort_union(PRIMARY,population_rate,area_rate,code); Using where 1640SELECT * FROM t1 FORCE INDEX ( PRIMARY, population_rate, area_rate, code ) 1641WHERE pk = 1 OR population_rate = 1 OR ( area_rate IN ( 1,2 ) OR area_rate IS NULL ) 1642AND (population_rate = 25 OR area_rate BETWEEN 2 AND 25 OR code BETWEEN 'MA' AND 'TX'); 1643pk code population_rate area_rate 16441 WI 20 23 1645DROP TABLE t1; 1646# 1647# MDEV-5069: Server crashes in SEL_ARG::increment_use_count with index_merge+index_merge_sort_union, FORCE INDEX 1648# 1649CREATE TABLE t1 ( 1650c1 varchar(64), 1651i int, 1652pk integer auto_increment, 1653c2 varchar(64), 1654index (c1), 1655index (i), 1656primary key (pk), 1657key (c2)) ENGINE=myisam; 1658ALTER TABLE t1 DISABLE KEYS; 1659INSERT INTO t1 VALUES 1660('West Virginia', 6121, NULL, 'California'),('Georgia', 60177, NULL, 'Arkansas'), 1661('Delaware', 90, NULL, 'Oregon'),('Wyoming', 7, NULL, 'Missouri'),( 1662'Delaware', 2, NULL, 'Utah'),('Wisconsin', 0, NULL, 'Iowa'), 1663('Kansas', 0, NULL, 'Florida'),('Ohio', 34358, NULL, 'Colorado'), 1664('Maine', 118, NULL, 'Texas'),('Mississippi', 0, NULL, 'Georgia'), 1665('Tennessee', 4, NULL, 'N/A'),('Georgia', 0, NULL, 'New Hampshire'), 1666('Wyoming', 2, NULL, 'N/A'),('Florida', 0, NULL, 'Arizona'), 1667('Rhode Island', -24002, NULL, 'Michigan'),('Alabama', 142, NULL, 'Indiana'), 1668('Colorado', 0, NULL, 'Louisiana'),('Michigan', 21194, NULL, 'Louisiana'), 1669('Oklahoma', 31475, NULL, 'Alabama'),('Pennsylvania', 0, NULL, 'Oklahoma'), 1670('Texas', 0, NULL, 'Texas'),('West Virginia', 5, NULL, 'Utah'), 1671('Florida', 49653, NULL, 'Kentucky'),('Tennessee', 19075, NULL, 'Oregon'), 1672('Maine', 3, NULL, 'Kansas, Kentucky, Iowa'),('Iowa', 1, NULL, 'South Dakota'), 1673('Kansas', -4037, NULL, 'Virginia'),('Delaware', 22550, NULL, 'Utah'), 1674('Illinois', 14634, NULL, 'South Carolina, Colorado'), 1675('Kansas', 6, NULL, 'South Dakota'),('Delaware', 9, NULL, ''), 1676('', 0, NULL, 'Utah, Delaware, Florida, Georgia, Nevada'), 1677('Colorado', 8, NULL, 'Montana'),('Maryland', 2689, NULL, 'Hawaii'), 1678('Florida', -12306, NULL, 'Delaware'), 1679('Indiana', 38567, NULL, 'Iowa, Minnesota, Maine'), 1680('Oklahoma', 9, NULL, 'Delaware, Kansas, Oregon, Nebraska, Maryland, Minnesota'), 1681('Tennessee', 12460, NULL, NULL),('Kentucky', 0, NULL, 'Ohio'), 1682('Nevada', 7, NULL, 'Vermont, Oregon, Oklahoma, Montana'), 1683('Nebraska', 61966, NULL, 'Nevada'),('Alaska', 131, NULL, 'Louisiana, Maine'), 1684('Wisconsin', 4, NULL, 'Nevada'),('South Carolina', 0, NULL, 'Washington'), 1685('West Virginia', 51314, NULL, 'Ohio'),('Louisiana', 0, NULL, ''), 1686('Pennsylvania', 0, NULL, 'Iowa, Idaho'),('Arkansas', 14010, NULL, 'Indiana'), 1687('Wyoming', -15514, NULL, 'Maine'),('Georgia', 0, NULL, 'N/A'), 1688('Kentucky', 1, NULL, 'Idaho'),('Wyoming', 60249, NULL, 'Indiana, Iowa'), 1689('Pennsylvania', 69, NULL, 'W'), 1690('New Mexico', 11480, NULL, 'Florida, Georgia, Hawaii'), 1691('South Carolina', 9, NULL, 'Iowa'),('Virginia', 0, NULL, 'Connecticut'), 1692('Mississippi', 19749, NULL, 'Rhode Island'),('Illinois', 5, NULL, 'Virginia'), 1693('Texas', -1749, NULL, 'Tennessee'),('Arizona', 28, NULL, 'California'), 1694('Florida', 62151, NULL, 'Kansas'),('California', 172, NULL, 'SC'), 1695('New Jersey', 0, NULL, 'North Carolina'),('Wyoming', 4, NULL, 'I'), 1696('Kansas', 10683, NULL, 'California'),('Arkansas', -14275, NULL, 'K'), 1697('Arizona', 5, NULL, 'California, Delaware, Rhode Island, Maryland'), 1698('Florida', 0, NULL, 'T'),('Alaska', 241, NULL, 'Virginia'); 1699ALTER TABLE t1 ENABLE KEYS; 1700EXPLAIN 1701SELECT * FROM t1 FORCE KEY (PRIMARY , i , c1 , c2) 1702WHERE pk = 255 OR i = 22 OR (pk IN (1 , 136) AND c2 IN ('c' , 'w') AND (c1 1703NOT BETWEEN 'e' AND 'i' OR c2 > 'g')) OR (pk is not null and (pk <1 or pk>1)) ; 1704id select_type table type possible_keys key key_len ref rows Extra 17051 SIMPLE t1 ALL PRIMARY,c1,i,c2 NULL NULL NULL 69 Using where 1706DROP TABLE t1; 1707set optimizer_switch= @optimizer_switch_save; 1708# 1709# MDEV-21932: ROR union with index_merge_sort_union=off 1710# 1711create table t0 (a int); 1712insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 1713insert into t0 select a+10 from t0; 1714insert into t0 select a+20 from t0; 1715insert into t0 select a+40 from t0; 1716insert into t0 select a+80 from t0; 1717insert into t0 select a+160 from t0; 1718delete from t0 where a > 300; 1719create table t1 ( 1720f1 int, f2 int, f3 int, f4 int, 1721primary key (f1), key (f3), key(f4) 1722) engine=myisam; 1723insert into t1 select a+100, a+100, a+100, a+100 from t0; 1724insert into t1 VALUES (9,0,2,6), (9930,0,0,NULL); 1725analyze table t1; 1726Table Op Msg_type Msg_text 1727test.t1 analyze status Engine-independent statistics collected 1728test.t1 analyze status OK 1729set optimizer_switch='index_merge_sort_union=off'; 1730set optimizer_switch='index_merge_union=on'; 1731explain select * from t1 1732where (( f3 = 1 or f1 = 7 ) and f1 < 10) or 1733(f3 between 2 and 2 and ( f3 = 1 or f4 < 7 )); 1734id select_type table type possible_keys key key_len ref rows Extra 17351 SIMPLE t1 index_merge PRIMARY,f3,f4 f3,PRIMARY,f3 5,4,5 NULL 3 Using union(f3,PRIMARY,f3); Using where 1736select * from t1 1737where (( f3 = 1 or f1 = 7 ) and f1 < 10) or 1738(f3 between 2 and 2 and ( f3 = 1 or f4 < 7 )); 1739f1 f2 f3 f4 17409 0 2 6 1741insert into t1 values (52,0,1,0),(53,0,1,0); 1742insert into t1 values (50,0,1,0),(51,0,1,0); 1743insert into t1 values (48,0,1,0),(49,0,1,0); 1744insert into t1 values (46,0,1,0),(47,0,1,0); 1745insert into t1 values (44,0,1,0),(45,0,1,0); 1746analyze table t1; 1747Table Op Msg_type Msg_text 1748test.t1 analyze status Engine-independent statistics collected 1749test.t1 analyze status OK 1750explain select * from t1 1751where (( f3 = 1 or f1 = 7 ) and f1 < 10) or 1752(f3 between 2 and 2 and ( f3 = 1 or f4 < 7 )); 1753id select_type table type possible_keys key key_len ref rows Extra 17541 SIMPLE t1 index_merge PRIMARY,f3,f4 f3,PRIMARY,f3 5,4,5 NULL 13 Using union(f3,PRIMARY,f3); Using where 1755select * from t1 1756where (( f3 = 1 or f1 = 7 ) and f1 < 10) or 1757(f3 between 2 and 2 and ( f3 = 1 or f4 < 7 )); 1758f1 f2 f3 f4 17599 0 2 6 1760drop table t0,t1; 1761set optimizer_switch= @optimizer_switch_save; 1762# 1763# MDEV-16695: Estimate for rows of derived tables is very high when we are using index_merge union 1764# 1765create table t0 1766( 1767key1 int not null, 1768INDEX i1(key1) 1769); 1770insert into t0 select * from seq_1_to_1024; 1771alter table t0 add key2 int not null, add index i2(key2); 1772alter table t0 add key3 int not null, add index i3(key3); 1773alter table t0 add key8 int not null, add index i8(key8); 1774update t0 set key2=key1,key3=key1,key8=1024-key1; 1775analyze table t0; 1776Table Op Msg_type Msg_text 1777test.t0 analyze status Engine-independent statistics collected 1778test.t0 analyze status OK 1779set @optimizer_switch_save=@@optimizer_switch; 1780set optimizer_switch='derived_merge=off,derived_with_keys=off'; 1781explain select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5; 1782id select_type table type possible_keys key key_len ref rows Extra 17831 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using where 17842 DERIVED t0 index_merge i1,i2,i8 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where 1785select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5; 1786key1 key2 key3 key8 17873 3 3 1021 1788set optimizer_use_condition_selectivity=2; 1789explain select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5; 1790id select_type table type possible_keys key key_len ref rows Extra 17911 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using where 17922 DERIVED t0 index_merge i1,i2,i8 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where 1793select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5; 1794key1 key2 key3 key8 17953 3 3 1021 1796set @@optimizer_switch= @optimizer_switch_save; 1797drop table t0; 1798# End of 10.1 tests 1799