Debian Codegraph @

mysql-test/suite/parts/t/partition_reverse_scan_icp.test

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