| 1 |
--echo # |
| 2 |
--echo # Bug #24929748 REVERSE SCAN ON A PARTITIONED TABLE DOES ICP CHECK INCORRECTLY, CAUSING SLOWDOWN |
| 3 |
--echo # |
| 4 |
|
| 5 |
create table t1 ( |
| 6 |
date_col datetime not null, |
| 7 |
col1 int, |
| 8 |
filler varchar(255), |
| 9 |
key(date_col, col1) |
| 10 |
) engine=innodb |
| 11 |
partition by range columns(date_col) |
| 12 |
( |
| 13 |
partition p0 values less than ('2015-01-01 00:00:00'), |
| 14 |
partition p1 values less than ('2016-01-01 00:00:00'), |
| 15 |
partition p2 values less than ('2017-01-01 00:00:00'), |
| 16 |
partition p3 values less than (MAXVALUE) |
| 17 |
); |
| 18 |
create table t2(a int); |
| 19 |
insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); |
| 20 |
|
| 21 |
insert into t1 select date_add('2014-01-01', interval (A.a+10*B.a) day),123456, repeat('raw-data', 5) |
| 22 |
from t2 A, t2 B, t2 C; |
| 23 |
|
| 24 |
insert into t1 select date_add('2015-01-01', interval (A.a+10*B.a) day), 123456,repeat('raw-data', 5) |
| 25 |
from t2 A, t2 B, t2 C; |
| 26 |
|
| 27 |
set @cnt=1; |
| 28 |
select * from t1 force index(date_col) where |
| 29 |
col1 + 10000000 > (@cnt:=@cnt+1) and date_col >= date('2014-04-01') AND date_col < date('2014-04-01' + interval 1 day) |
| 30 |
ORDER BY date_col DESC; |
| 31 |
--skip_if_hypergraph # Does not support indexes yet, so will do a full table scan. |
| 32 |
select @cnt; |
| 33 |
|
| 34 |
#Mutiple partittion scan |
| 35 |
|
| 36 |
#delete some records in p1 ,then only it will go to QUICK_SELECT_DESC with ICP |
| 37 |
delete from t1 where date_col>='2015-01-01' and date_col<='2016-01-06' limit 500; |
| 38 |
|
| 39 |
#Range condition spans multiple partitions |
| 40 |
set @cnt=1; |
| 41 |
--skip_if_hypergraph # Depends on the non-deterministic DELETE statement above. |
| 42 |
select * from t1 force index(date_col) where |
| 43 |
col1 + 10000000 > (@cnt:=@cnt+1) and date_col >= date('2014-04-06') AND date_col < date('2015-02-10' + interval 1 day) |
| 44 |
ORDER BY date_col DESC; |
| 45 |
--skip_if_hypergraph # Does not support indexes yet, so will do a full table scan. |
| 46 |
select @cnt; |
| 47 |
|
| 48 |
drop table t1,t2; |
| 49 |
|
| 50 |
#Multi range index read |
| 51 |
|
| 52 |
create table t1 ( f1 int ,f2 int, key(f2)) engine=innodb |
| 53 |
partition by range columns (f2) |
| 54 |
( |
| 55 |
partition p0 values less than (10000), |
| 56 |
partition p1 values less than (20000), |
| 57 |
partition p2 values less than (30000), |
| 58 |
PARTITION p3 VALUES LESS THAN (MAXVALUE) |
| 59 |
); |
| 60 |
--disable_query_log |
| 61 |
let $i=0; |
| 62 |
set @cnt=1; |
| 63 |
while ($i<20000) |
| 64 |
{ |
| 65 |
insert into t1 values (123456,@cnt); |
| 66 |
inc $i; |
| 67 |
set @cnt = @cnt + 1; |
| 68 |
} |
| 69 |
--enable_query_log |
| 70 |
select * from t1 where (f2>1000 and f2 <1002) or ( f2> 3000 and f2 < 3003) or (f2> 10001 and f2 < 10004) order by f2 desc; |
| 71 |
drop table t1; |