1SET DEFAULT_STORAGE_ENGINE='tokudb'; 2*** Bug #22169 *** 3DROP TABLE IF EXISTS t1; 4create table t1 (a varchar(10), b varchar(255), c varchar(10), d varchar(300), e varchar(10), f varchar(20), key (b) clustering=yes, key (d,a) clustering=yes); 5insert into t1 values ("1","10","100","1000","10000","100000"); 6insert into t1 values ("2","20","200","2000","20000","200000"); 7insert into t1 values ("3","30","300","3000","30000","300000"); 8insert into t1 values ("4","40","400","4000","40000","400000"); 9insert into t1 values ("5","50","500","5000","50000","500000"); 10explain select * from t1; 11id select_type table type possible_keys key key_len ref rows Extra 121 SIMPLE t1 index NULL b NULL NULL NULL; Using index 13select * from t1; 14a b c d e f 151 10 100 1000 10000 100000 162 20 200 2000 20000 200000 173 30 300 3000 30000 300000 184 40 400 4000 40000 400000 195 50 500 5000 50000 500000 20explain select * from t1 where b > "0"; 21id select_type table type possible_keys key key_len ref rows Extra 221 SIMPLE t1 index b b NULL NULL NULL; Using where; Using index 23select * from t1 where b > "0"; 24a b c d e f 251 10 100 1000 10000 100000 262 20 200 2000 20000 200000 273 30 300 3000 30000 300000 284 40 400 4000 40000 400000 295 50 500 5000 50000 500000 30explain select * from t1 where d > "0"; 31id select_type table type possible_keys key key_len ref rows Extra 321 SIMPLE t1 index d d NULL NULL NULL; Using where; Using index 33select * from t1 where d > "0"; 34a b c d e f 351 10 100 1000 10000 100000 362 20 200 2000 20000 200000 373 30 300 3000 30000 300000 384 40 400 4000 40000 400000 395 50 500 5000 50000 500000 40explain select a from t1 where d > "0"; 41id select_type table type possible_keys key key_len ref rows Extra 421 SIMPLE t1 index d d NULL NULL NULL; Using where; Using index 43select a from t1 where d > "0"; 44a 451 462 473 484 495 50select e,f from t1; 51e f 5210000 100000 5320000 200000 5430000 300000 5540000 400000 5650000 500000 57select e,f from t1 where b > "0"; 58e f 5910000 100000 6020000 200000 6130000 300000 6240000 400000 6350000 500000 64select e,f from t1 where d > "0"; 65e f 6610000 100000 6720000 200000 6830000 300000 6940000 400000 7050000 500000 71update t1 set a = a+1, b = b+10; 72explain select * from t1; 73id select_type table type possible_keys key key_len ref rows Extra 741 SIMPLE t1 index NULL b NULL NULL NULL; Using index 75select * from t1; 76a b c d e f 772 20 100 1000 10000 100000 783 30 200 2000 20000 200000 794 40 300 3000 30000 300000 805 50 400 4000 40000 400000 816 60 500 5000 50000 500000 82explain select * from t1 where b > "0"; 83id select_type table type possible_keys key key_len ref rows Extra 841 SIMPLE t1 index b b NULL NULL NULL; Using where; Using index 85select * from t1 where b > "0"; 86a b c d e f 872 20 100 1000 10000 100000 883 30 200 2000 20000 200000 894 40 300 3000 30000 300000 905 50 400 4000 40000 400000 916 60 500 5000 50000 500000 92explain select * from t1 where d > "0"; 93id select_type table type possible_keys key key_len ref rows Extra 941 SIMPLE t1 index d d NULL NULL NULL; Using where; Using index 95select * from t1 where d > "0"; 96a b c d e f 972 20 100 1000 10000 100000 983 30 200 2000 20000 200000 994 40 300 3000 30000 300000 1005 50 400 4000 40000 400000 1016 60 500 5000 50000 500000 102explain select a from t1 where d > "0"; 103id select_type table type possible_keys key key_len ref rows Extra 1041 SIMPLE t1 index d d NULL NULL NULL; Using where; Using index 105select a from t1 where d > "0"; 106a 1072 1083 1094 1105 1116 112select e,f from t1; 113e f 11410000 100000 11520000 200000 11630000 300000 11740000 400000 11850000 500000 119select e,f from t1 where b > "0"; 120e f 12110000 100000 12220000 200000 12330000 300000 12440000 400000 12550000 500000 126select e,f from t1 where d > "0"; 127e f 12810000 100000 12920000 200000 13030000 300000 13140000 400000 13250000 500000 133delete from t1 where b > 35; 134explain select * from t1; 135id select_type table type possible_keys key key_len ref rows Extra 1361 SIMPLE t1 index NULL b NULL NULL NULL; Using index 137select * from t1; 138a b c d e f 1392 20 100 1000 10000 100000 1403 30 200 2000 20000 200000 141explain select * from t1 where b > "0"; 142id select_type table type possible_keys key key_len ref rows Extra 1431 SIMPLE t1 index b b NULL NULL NULL; Using where; Using index 144select * from t1 where b > "0"; 145a b c d e f 1462 20 100 1000 10000 100000 1473 30 200 2000 20000 200000 148explain select * from t1 where d > "0"; 149id select_type table type possible_keys key key_len ref rows Extra 1501 SIMPLE t1 index d d NULL NULL NULL; Using where; Using index 151select * from t1 where d > "0"; 152a b c d e f 1532 20 100 1000 10000 100000 1543 30 200 2000 20000 200000 155explain select a from t1 where d > "0"; 156id select_type table type possible_keys key key_len ref rows Extra 1571 SIMPLE t1 index d d NULL NULL NULL; Using where; Using index 158select a from t1 where d > "0"; 159a 1602 1613 162select e,f from t1; 163e f 16410000 100000 16520000 200000 166select e,f from t1 where b > "0"; 167e f 16810000 100000 16920000 200000 170select e,f from t1 where d > "0"; 171e f 17210000 100000 17320000 200000 174alter table t1 drop index b, drop index d; 175alter table t1 add key d(d,a) clustering=yes, add key b(b) clustering=yes; 176explain select * from t1; 177id select_type table type possible_keys key key_len ref rows Extra 1781 SIMPLE t1 index NULL d NULL NULL NULL; Using index 179select * from t1; 180a b c d e f 1812 20 100 1000 10000 100000 1823 30 200 2000 20000 200000 183explain select * from t1 where b > "0"; 184id select_type table type possible_keys key key_len ref rows Extra 1851 SIMPLE t1 <type> b b NULL NULL NULL; Using where; Using index 186select * from t1 where b > "0"; 187a b c d e f 1882 20 100 1000 10000 100000 1893 30 200 2000 20000 200000 190explain select * from t1 where d > "0"; 191id select_type table type possible_keys key key_len ref rows Extra 1921 SIMPLE t1 <type> d d NULL NULL NULL; Using where; Using index 193select * from t1 where d > "0"; 194a b c d e f 1952 20 100 1000 10000 100000 1963 30 200 2000 20000 200000 197explain select a from t1 where d > "0"; 198id select_type table type possible_keys key key_len ref rows Extra 1991 SIMPLE t1 NULL d d NULL NULL NULL; Using where; Using index 200select a from t1 where d > "0"; 201a 2022 2033 204select e,f from t1; 205e f 20610000 100000 20720000 200000 208select e,f from t1 where b > "0"; 209e f 21010000 100000 21120000 200000 212select e,f from t1 where d > "0"; 213e f 21410000 100000 21520000 200000 216DROP TABLE t1; 217