1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
|
#
# Partition pruning tests. Currently we only detect which partitions to
# prune, so the test is EXPLAINs.
#
-- source include/have_partition.inc
--disable_warnings
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
--enable_warnings
# Check if we can infer from condition on partition fields that
# no records will match.
create table t1 ( a int not null) partition by hash(a) partitions 2;
insert into t1 values (1),(2),(3);
explain select * from t1 where a=5 and a=6;
drop table t1;
# Simple HASH partitioning
create table t1 (
a int(11) not null
) partition by hash (a) partitions 2;
insert into t1 values (1),(2),(3);
explain partitions select * from t1 where a=1;
explain partitions select * from t1 where a=2;
explain partitions select * from t1 where a=1 or a=2;
# Partitioning over several fields
create table t2 (
a int not null,
b int not null
) partition by key(a,b) partitions 2;
insert into t2 values (1,1),(2,2),(3,3);
explain partitions select * from t2 where a=1;
explain partitions select * from t2 where b=1;
explain partitions select * from t2 where a=1 and b=1;
# RANGE(expr) partitioning
create table t3 (
a int
)
partition by range (a*1) (
partition p0 values less than (10),
partition p1 values less than (20)
);
insert into t3 values (5),(15);
explain partitions select * from t3 where a=11;
explain partitions select * from t3 where a=10;
explain partitions select * from t3 where a=20;
explain partitions select * from t3 where a=30;
# LIST(expr) partitioning
create table t4 (a int not null, b int not null) partition by LIST (a+b) (
partition p0 values in (12),
partition p1 values in (14)
);
insert into t4 values (10,2), (10,4);
# empty OR one
explain partitions select * from t4 where (a=10 and b=1) or (a=10 and b=2);
# empty OR one OR empty
explain partitions select * from t4
where (a=10 and b=1) or (a=10 and b=2) or (a=10 and b = 3);
# one OR empty OR one
explain partitions select * from t4 where (a=10 and b=2) or (a=10 and b=3)
or (a=10 and b = 4);
# empty OR full
explain partitions select * from t4 where (a=10 and b=1) or a=11;
# one OR full
explain partitions select * from t4 where (a=10 and b=2) or a=11;
drop table t1, t2, t3, t4;
# LIST(expr)/HASH subpartitioning.
create table t5 (a int not null, b int not null,
c int not null, d int not null)
partition by LIST(a+b) subpartition by HASH (c+d) subpartitions 2
(
partition p0 values in (12),
partition p1 values in (14)
);
insert into t5 values (10,2,0,0), (10,4,0,0), (10,2,0,1), (10,4,0,1);
explain partitions select * from t5;
# empty OR one OR empty
explain partitions select * from t5
where (a=10 and b=1) or (a=10 and b=2) or (a=10 and b = 3);
# one OR empty OR one
explain partitions select * from t5 where (a=10 and b=2) or (a=10 and b=3)
or (a=10 and b = 4);
# conditions on subpartitions only
explain partitions select * from t5 where (c=1 and d=1);
explain partitions select * from t5 where (c=2 and d=1);
# mixed partition/subpartitions.
explain partitions select * from t5 where (a=10 and b=2 and c=1 and d=1) or
(c=2 and d=1);
# same as above
explain partitions select * from t5 where (a=10 and b=2 and c=1 and d=1) or
(b=2 and c=2 and d=1);
# LIST(field) partitioning, interval analysis.
create table t6 (a int not null) partition by LIST(a) (
partition p1 values in (1),
partition p3 values in (3),
partition p5 values in (5),
partition p7 values in (7),
partition p9 values in (9)
);
insert into t6 values (1),(3),(5);
explain partitions select * from t6 where a < 1;
explain partitions select * from t6 where a <= 1;
explain partitions select * from t6 where a > 9;
explain partitions select * from t6 where a >= 9;
explain partitions select * from t6 where a > 0 and a < 5;
explain partitions select * from t6 where a > 5 and a < 12;
explain partitions select * from t6 where a > 3 and a < 8 ;
explain partitions select * from t6 where a >= 0 and a <= 5;
explain partitions select * from t6 where a >= 5 and a <= 12;
explain partitions select * from t6 where a >= 3 and a <= 8;
explain partitions select * from t6 where a > 3 and a < 5;
# RANGE(field) partitioning, interval analysis.
create table t7 (a int not null) partition by RANGE(a) (
partition p10 values less than (10),
partition p30 values less than (30),
partition p50 values less than (50),
partition p70 values less than (70),
partition p90 values less than (90)
);
insert into t7 values (10),(30),(50);
# leftmost intervals
explain partitions select * from t7 where a < 5;
explain partitions select * from t7 where a < 10;
explain partitions select * from t7 where a <= 10;
explain partitions select * from t7 where a = 10;
#rightmost intervals
explain partitions select * from t7 where a < 90;
explain partitions select * from t7 where a = 90;
explain partitions select * from t7 where a > 90;
explain partitions select * from t7 where a >= 90;
# misc intervals
explain partitions select * from t7 where a > 11 and a < 29;
# LIST(monontonic_func) partitioning
create table t8 (a date not null) partition by RANGE(YEAR(a)) (
partition p0 values less than (1980),
partition p1 values less than (1990),
partition p2 values less than (2000)
);
insert into t8 values ('1985-05-05'),('1995-05-05');
explain partitions select * from t8 where a < '1980-02-02';
# LIST(strict_monotonic_func) partitioning
create table t9 (a date not null) partition by RANGE(TO_DAYS(a)) (
partition p0 values less than (732299), -- 2004-12-19
partition p1 values less than (732468), -- 2005-06-06
partition p2 values less than (732664) -- 2005-12-19
);
insert into t9 values ('2005-05-05'), ('2005-04-04');
explain partitions select * from t9 where a < '2004-12-19';
explain partitions select * from t9 where a <= '2004-12-19';
drop table t5,t6,t7,t8,t9;
# Test the case where we can't create partitioning 'index'
create table t1 (a enum('a','b','c','d') default 'a')
partition by hash (ascii(a)) partitions 2;
insert into t1 values ('a'),('b'),('c');
explain partitions select * from t1 where a='b';
drop table t1;
|
