1--source include/have_rocksdb.inc 2 3# 4# Range access test for RocksDB storage engine 5# 6select * from information_schema.engines where engine = 'rocksdb'; 7 8--disable_warnings 9drop table if exists t0,t1,t2,t3,t4,t5; 10--enable_warnings 11create table t0 (a int) engine=myisam; 12insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 13 14create table t1(a int) engine=myisam; 15insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C; 16 17create table t2 ( 18 pk int not null, 19 a int not null, 20 b int not null, 21 primary key(pk), 22 key(a) comment 'rev:cf1' 23) engine=rocksdb; 24 25# 10 pk values for each value of a... 26insert into t2 select A.a, FLOOR(A.a/10), A.a from t1 A; 27 28--echo # 29--echo # HA_READ_KEY_EXACT tests 30--echo # 31 32--echo # Original failure was here: 33--replace_column 9 # 34explain 35select * from t2 force index (a) where a=0; 36select * from t2 force index (a) where a=0; 37 38--echo # The rest are for code coverage: 39--replace_column 9 # 40explain 41select * from t2 force index (a) where a=2; 42select * from t2 force index (a) where a=2; 43 44--replace_column 9 # 45explain 46select * from t2 force index (a) where a=3 and pk=33; 47select * from t2 force index (a) where a=3 and pk=33; 48 49select * from t2 force index (a) where a=99 and pk=99; 50select * from t2 force index (a) where a=0 and pk=0; 51select * from t2 force index (a) where a=-1; 52select * from t2 force index (a) where a=-1 and pk in (101,102); 53select * from t2 force index (a) where a=100 and pk in (101,102); 54 55 56--echo # 57--echo # #36: Range in form tbl.key >= const doesn't work in reverse column family 58--echo # 59--replace_column 9 # 60explain 61select count(*) from t2 force index (a) where a>=0 and a <=1; 62select count(*) from t2 force index (a) where a>=0 and a <=1; 63 64--replace_column 9 # 65explain 66select count(*) from t2 force index (a) where a>=-1 and a <=1; 67select count(*) from t2 force index (a) where a>=-1 and a <=1; 68 69--replace_column 9 # 70explain 71select * from t2 force index (a) where a=0 and pk>=3; 72select * from t2 force index (a) where a=0 and pk>=3; 73 74--echo # Try edge cases where we fall over the end of the table 75create table t3 like t2; 76insert into t3 select * from t2; 77 78select * from t3 where pk>=1000000; 79select * from t2 where pk>=1000000; 80 81--echo # 82--echo # #42: Range in form tbl.key > const doesn't work in reverse column family 83--echo # 84--replace_column 9 # 85explain 86select count(*) from t2 force index (a) where a>0; 87select count(*) from t2 force index (a) where a>0; 88 89--replace_column 9 # 90explain 91select count(*) from t2 force index (a) where a>99; 92select count(*) from t2 force index (a) where a>99; 93 94select * from t2 where pk>1000000; 95select * from t3 where pk>1000000; 96 97--replace_column 9 # 98explain 99select count(*) from t2 force index (a) where a=2 and pk>25; 100select count(*) from t2 force index (a) where a=2 and pk>25; 101 102 103select * from t2 force index (a) where a>-10 and a < 1; 104select * from t3 force index (a) where a>-10 and a < 1; 105 106 107--echo # 108--echo # #46: index_read_map(HA_READ_BEFORE_KEY) does not work in reverse column family 109--echo # 110select max(a) from t2 where a < 2; 111select max(a) from t2 where a < -1; 112 113select max(pk) from t2 where a=3 and pk < 6; 114 115select max(pk) from t2 where pk < 200000; 116select max(pk) from t2 where pk < 20; 117 118select max(a) from t3 where a < 2; 119select max(a) from t3 where a < -1; 120select max(pk) from t3 where pk < 200000; 121select max(pk) from t3 where pk < 20; 122 123select max(pk) from t2 where a=3 and pk < 33; 124select max(pk) from t3 where a=3 and pk < 33; 125 126--echo # 127--echo # #48: index_read_map(HA_READ_PREFIX_LAST) does not work in reverse CF 128--echo # 129 130--echo # Tests for search_flag=HA_READ_PREFIX_LAST_OR_PREV 131--echo # Note: the next explain has "Using index condition" in fb/mysql-5.6 132--echo # but "Using where" in MariaDB because the latter does not 133--echo # support ICP over reverse scans. 134--replace_column 9 # 135explain 136select * from t2 where a between 99 and 2000 order by a desc; 137select * from t2 where a between 99 and 2000 order by a desc; 138 139select max(a) from t2 where a <=10; 140select max(a) from t2 where a <=-4; 141 142select max(pk) from t2 where a=5 and pk <=55; 143select max(pk) from t2 where a=5 and pk <=55555; 144select max(pk) from t2 where a=5 and pk <=0; 145 146select max(pk) from t2 where pk <=-1; 147select max(pk) from t2 where pk <=999999; 148select max(pk) from t3 where pk <=-1; 149select max(pk) from t3 where pk <=999999; 150 151--echo # 152--echo # Tests for search_flag=HA_READ_PREFIX_LAST 153--echo # 154 155create table t4 ( 156 pk int primary key, 157 a int, 158 b int, 159 c int, 160 key(a,b,c) 161) engine=rocksdb; 162 163insert into t4 select pk,pk,pk,pk from t2 where pk < 100; 164 165--replace_column 9 # 166explain 167select * from t4 where a=1 and b in (1) order by c desc; 168select * from t4 where a=1 and b in (1) order by c desc; 169 170--replace_column 9 # 171explain 172select * from t4 where a=5 and b in (4) order by c desc; 173select * from t4 where a=5 and b in (4) order by c desc; 174 175--echo # HA_READ_PREFIX_LAST for reverse-ordered CF 176create table t5 ( 177 pk int primary key, 178 a int, 179 b int, 180 c int, 181 key(a,b,c) comment 'rev:cf2' 182) engine=rocksdb; 183 184insert into t5 select pk,pk,pk,pk from t2 where pk < 100; 185 186--replace_column 9 # 187explain 188select * from t5 where a=1 and b in (1) order by c desc; 189select * from t5 where a=1 and b in (1) order by c desc; 190 191--replace_column 9 # 192explain 193select * from t5 where a=5 and b in (4) order by c desc; 194select * from t5 where a=5 and b in (4) order by c desc; 195 196drop table t0,t1,t2,t3,t4,t5; 197