Debian Codegraph @

mysql-test/suite/parts/r/partition_reverse_scan_icp.result

raw
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;