| 1 |
# |
| 2 |
# Bug #24929748 REVERSE SCAN ON A PARTITIONED TABLE DOES ICP CHECK INCORRECTLY, CAUSING SLOWDOWN |
| 3 |
# |
| 4 |
create table t1 ( |
| 5 |
date_col datetime not null, |
| 6 |
col1 int, |
| 7 |
filler varchar(255), |
| 8 |
key(date_col, col1) |
| 9 |
) engine=innodb |
| 10 |
partition by range columns(date_col) |
| 11 |
( |
| 12 |
partition p0 values less than ('2015-01-01 00:00:00'), |
| 13 |
partition p1 values less than ('2016-01-01 00:00:00'), |
| 14 |
partition p2 values less than ('2017-01-01 00:00:00'), |
| 15 |
partition p3 values less than (MAXVALUE) |
| 16 |
); |
| 17 |
create table t2(a int); |
| 18 |
insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); |
| 19 |
insert into t1 select date_add('2014-01-01', interval (A.a+10*B.a) day),123456, repeat('raw-data', 5) |
| 20 |
from t2 A, t2 B, t2 C; |
| 21 |
insert into t1 select date_add('2015-01-01', interval (A.a+10*B.a) day), 123456,repeat('raw-data', 5) |
| 22 |
from t2 A, t2 B, t2 C; |
| 23 |
set @cnt=1; |
| 24 |
select * from t1 force index(date_col) where |
| 25 |
col1 + 10000000 > (@cnt:=@cnt+1) and date_col >= date('2014-04-01') AND date_col < date('2014-04-01' + interval 1 day) |
| 26 |
ORDER BY date_col DESC; |
| 27 |
date_col col1 filler |
| 28 |
2014-04-01 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 29 |
2014-04-01 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 30 |
2014-04-01 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 31 |
2014-04-01 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 32 |
2014-04-01 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 33 |
2014-04-01 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 34 |
2014-04-01 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 35 |
2014-04-01 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 36 |
2014-04-01 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 37 |
2014-04-01 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 38 |
Warnings: |
| 39 |
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. |
| 40 |
select @cnt; |
| 41 |
@cnt |
| 42 |
11 |
| 43 |
delete from t1 where date_col>='2015-01-01' and date_col<='2016-01-06' limit 500; |
| 44 |
set @cnt=1; |
| 45 |
select * from t1 force index(date_col) where |
| 46 |
col1 + 10000000 > (@cnt:=@cnt+1) and date_col >= date('2014-04-06') AND date_col < date('2015-02-10' + interval 1 day) |
| 47 |
ORDER BY date_col DESC; |
| 48 |
date_col col1 filler |
| 49 |
2014-04-10 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 50 |
2014-04-10 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 51 |
2014-04-10 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 52 |
2014-04-10 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 53 |
2014-04-10 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 54 |
2014-04-10 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 55 |
2014-04-10 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 56 |
2014-04-10 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 57 |
2014-04-10 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 58 |
2014-04-10 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 59 |
2014-04-09 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 60 |
2014-04-09 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 61 |
2014-04-09 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 62 |
2014-04-09 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 63 |
2014-04-09 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 64 |
2014-04-09 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 65 |
2014-04-09 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 66 |
2014-04-09 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 67 |
2014-04-09 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 68 |
2014-04-09 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 69 |
2014-04-08 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 70 |
2014-04-08 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 71 |
2014-04-08 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 72 |
2014-04-08 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 73 |
2014-04-08 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 74 |
2014-04-08 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 75 |
2014-04-08 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 76 |
2014-04-08 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 77 |
2014-04-08 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 78 |
2014-04-08 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 79 |
2014-04-07 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 80 |
2014-04-07 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 81 |
2014-04-07 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 82 |
2014-04-07 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 83 |
2014-04-07 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 84 |
2014-04-07 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 85 |
2014-04-07 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 86 |
2014-04-07 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 87 |
2014-04-07 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 88 |
2014-04-07 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 89 |
2014-04-06 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 90 |
2014-04-06 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 91 |
2014-04-06 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 92 |
2014-04-06 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 93 |
2014-04-06 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 94 |
2014-04-06 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 95 |
2014-04-06 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 96 |
2014-04-06 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 97 |
2014-04-06 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 98 |
2014-04-06 00:00:00 123456 raw-dataraw-dataraw-dataraw-dataraw-data |
| 99 |
Warnings: |
| 100 |
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. |
| 101 |
select @cnt; |
| 102 |
@cnt |
| 103 |
51 |
| 104 |
drop table t1,t2; |
| 105 |
create table t1 ( f1 int ,f2 int, key(f2)) engine=innodb |
| 106 |
partition by range columns (f2) |
| 107 |
( |
| 108 |
partition p0 values less than (10000), |
| 109 |
partition p1 values less than (20000), |
| 110 |
partition p2 values less than (30000), |
| 111 |
PARTITION p3 VALUES LESS THAN (MAXVALUE) |
| 112 |
); |
| 113 |
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; |
| 114 |
f1 f2 |
| 115 |
123456 10003 |
| 116 |
123456 10002 |
| 117 |
123456 3002 |
| 118 |
123456 3001 |
| 119 |
123456 1001 |
| 120 |
drop table t1; |