2 # Partition pruning tests. Currently we only detect which partitions to
3 # prune, so the test is EXPLAINs.
5 -- source include/have_partition.inc
8 drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
12 --echo # Bug#53806: Wrong estimates for range query in partitioned MyISAM table
13 --echo # Bug#46754: 'rows' field doesn't reflect partition pruning
15 CREATE TABLE t1 (a INT PRIMARY KEY)
16 PARTITION BY RANGE (a) (
17 PARTITION p0 VALUES LESS THAN (1),
18 PARTITION p1 VALUES LESS THAN (2),
19 PARTITION p2 VALUES LESS THAN (3),
20 PARTITION p3 VALUES LESS THAN (4),
21 PARTITION p4 VALUES LESS THAN (5),
22 PARTITION p5 VALUES LESS THAN (6),
23 PARTITION max VALUES LESS THAN MAXVALUE);
25 INSERT INTO t1 VALUES (-1),(0),(1),(2),(3),(4),(5),(6),(7),(8);
27 --replace_column 1 # 2 # 3 # 4 # 5 # 6 # 7 # 8 # 9 # 11 #
28 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1;
29 --replace_column 1 # 2 # 3 # 4 # 5 # 6 # 7 # 8 # 9 # 11 #
30 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 7;
31 --replace_column 1 # 2 # 3 # 4 # 5 # 6 # 7 # 8 # 9 # 11 #
32 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1;
36 --echo # Bug#49742: Partition Pruning not working correctly for RANGE
38 CREATE TABLE t1 (a INT PRIMARY KEY)
39 PARTITION BY RANGE (a) (
40 PARTITION p0 VALUES LESS THAN (1),
41 PARTITION p1 VALUES LESS THAN (2),
42 PARTITION p2 VALUES LESS THAN (3),
43 PARTITION p3 VALUES LESS THAN (4),
44 PARTITION p4 VALUES LESS THAN (5),
45 PARTITION p5 VALUES LESS THAN (6),
46 PARTITION max VALUES LESS THAN MAXVALUE);
48 INSERT INTO t1 VALUES (-1),(0),(1),(2),(3),(4),(5),(6),(7),(8);
50 SELECT * FROM t1 WHERE a < 1;
51 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 1;
52 SELECT * FROM t1 WHERE a < 2;
53 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 2;
54 SELECT * FROM t1 WHERE a < 3;
55 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 3;
56 SELECT * FROM t1 WHERE a < 4;
57 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 4;
58 SELECT * FROM t1 WHERE a < 5;
59 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 5;
60 SELECT * FROM t1 WHERE a < 6;
61 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 6;
62 SELECT * FROM t1 WHERE a < 7;
63 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 7;
64 SELECT * FROM t1 WHERE a <= 1;
65 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1;
66 SELECT * FROM t1 WHERE a <= 2;
67 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 2;
68 SELECT * FROM t1 WHERE a <= 3;
69 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 3;
70 SELECT * FROM t1 WHERE a <= 4;
71 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 4;
72 SELECT * FROM t1 WHERE a <= 5;
73 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 5;
74 SELECT * FROM t1 WHERE a <= 6;
75 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 6;
76 SELECT * FROM t1 WHERE a <= 7;
77 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 7;
78 SELECT * FROM t1 WHERE a = 1;
79 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 1;
80 SELECT * FROM t1 WHERE a = 2;
81 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 2;
82 SELECT * FROM t1 WHERE a = 3;
83 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 3;
84 SELECT * FROM t1 WHERE a = 4;
85 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 4;
86 SELECT * FROM t1 WHERE a = 5;
87 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 5;
88 SELECT * FROM t1 WHERE a = 6;
89 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 6;
90 SELECT * FROM t1 WHERE a = 7;
91 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 7;
92 SELECT * FROM t1 WHERE a >= 1;
93 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 1;
94 SELECT * FROM t1 WHERE a >= 2;
95 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 2;
96 SELECT * FROM t1 WHERE a >= 3;
97 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 3;
98 SELECT * FROM t1 WHERE a >= 4;
99 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 4;
100 SELECT * FROM t1 WHERE a >= 5;
101 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 5;
102 SELECT * FROM t1 WHERE a >= 6;
103 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 6;
104 SELECT * FROM t1 WHERE a >= 7;
105 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 7;
106 SELECT * FROM t1 WHERE a > 1;
107 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 1;
108 SELECT * FROM t1 WHERE a > 2;
109 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 2;
110 SELECT * FROM t1 WHERE a > 3;
111 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 3;
112 SELECT * FROM t1 WHERE a > 4;
113 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 4;
114 SELECT * FROM t1 WHERE a > 5;
115 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 5;
116 SELECT * FROM t1 WHERE a > 6;
117 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 6;
118 SELECT * FROM t1 WHERE a > 7;
119 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 7;
122 CREATE TABLE t1 (a INT PRIMARY KEY)
123 PARTITION BY RANGE (a) (
124 PARTITION p0 VALUES LESS THAN (1),
125 PARTITION p1 VALUES LESS THAN (2),
126 PARTITION p2 VALUES LESS THAN (3),
127 PARTITION p3 VALUES LESS THAN (4),
128 PARTITION p4 VALUES LESS THAN (5),
129 PARTITION max VALUES LESS THAN MAXVALUE);
131 INSERT INTO t1 VALUES (-1),(0),(1),(2),(3),(4),(5),(6),(7);
133 SELECT * FROM t1 WHERE a < 1;
134 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 1;
135 SELECT * FROM t1 WHERE a < 2;
136 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 2;
137 SELECT * FROM t1 WHERE a < 3;
138 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 3;
139 SELECT * FROM t1 WHERE a < 4;
140 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 4;
141 SELECT * FROM t1 WHERE a < 5;
142 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 5;
143 SELECT * FROM t1 WHERE a < 6;
144 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 6;
145 SELECT * FROM t1 WHERE a <= 1;
146 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1;
147 SELECT * FROM t1 WHERE a <= 2;
148 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 2;
149 SELECT * FROM t1 WHERE a <= 3;
150 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 3;
151 SELECT * FROM t1 WHERE a <= 4;
152 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 4;
153 SELECT * FROM t1 WHERE a <= 5;
154 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 5;
155 SELECT * FROM t1 WHERE a <= 6;
156 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 6;
157 SELECT * FROM t1 WHERE a = 1;
158 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 1;
159 SELECT * FROM t1 WHERE a = 2;
160 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 2;
161 SELECT * FROM t1 WHERE a = 3;
162 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 3;
163 SELECT * FROM t1 WHERE a = 4;
164 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 4;
165 SELECT * FROM t1 WHERE a = 5;
166 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 5;
167 SELECT * FROM t1 WHERE a = 6;
168 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 6;
169 SELECT * FROM t1 WHERE a >= 1;
170 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 1;
171 SELECT * FROM t1 WHERE a >= 2;
172 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 2;
173 SELECT * FROM t1 WHERE a >= 3;
174 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 3;
175 SELECT * FROM t1 WHERE a >= 4;
176 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 4;
177 SELECT * FROM t1 WHERE a >= 5;
178 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 5;
179 SELECT * FROM t1 WHERE a >= 6;
180 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 6;
181 SELECT * FROM t1 WHERE a > 1;
182 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 1;
183 SELECT * FROM t1 WHERE a > 2;
184 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 2;
185 SELECT * FROM t1 WHERE a > 3;
186 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 3;
187 SELECT * FROM t1 WHERE a > 4;
188 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 4;
189 SELECT * FROM t1 WHERE a > 5;
190 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 5;
191 SELECT * FROM t1 WHERE a > 6;
192 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 6;
196 # Bug#20577: Partitions: use of to_days() function leads to selection failures
198 --let $explain_partitions= 1;
199 --let $verify_without_partitions= 0;
200 --echo # test of RANGE and index
201 CREATE TABLE t1 (a DATE, KEY(a))
202 PARTITION BY RANGE (TO_DAYS(a))
203 (PARTITION `pNULL` VALUES LESS THAN (0),
204 PARTITION `p0001-01-01` VALUES LESS THAN (366 + 1),
205 PARTITION `p1001-01-01` VALUES LESS THAN (TO_DAYS('1001-01-01') + 1),
206 PARTITION `p2001-01-01` VALUES LESS THAN (TO_DAYS('2001-01-01') + 1));
207 if ($verify_without_partitions)
209 ALTER TABLE t1 REMOVE PARTITIONING;
211 INSERT INTO t1 VALUES ('0000-00-00'), ('0000-01-02'), ('0001-01-01'),
212 ('1001-00-00'), ('1001-01-01'), ('1002-00-00'), ('2001-01-01');
213 --source include/partition_date_range.inc
214 --echo # test without index
215 ALTER TABLE t1 DROP KEY a;
216 --source include/partition_date_range.inc
219 --echo # test of LIST and index
220 CREATE TABLE t1 (a DATE, KEY(a))
221 PARTITION BY LIST (TO_DAYS(a))
222 (PARTITION `p0001-01-01` VALUES IN (TO_DAYS('0001-01-01')),
223 PARTITION `p2001-01-01` VALUES IN (TO_DAYS('2001-01-01')),
224 PARTITION `pNULL` VALUES IN (NULL),
225 PARTITION `p0000-01-02` VALUES IN (TO_DAYS('0000-01-02')),
226 PARTITION `p1001-01-01` VALUES IN (TO_DAYS('1001-01-01')));
227 if ($verify_without_partitions)
229 ALTER TABLE t1 REMOVE PARTITIONING;
231 INSERT INTO t1 VALUES ('0000-00-00'), ('0000-01-02'), ('0001-01-01'),
232 ('1001-00-00'), ('1001-01-01'), ('1002-00-00'), ('2001-01-01');
233 --source include/partition_date_range.inc
234 --echo # test without index
235 ALTER TABLE t1 DROP KEY a;
236 --source include/partition_date_range.inc
240 # Bug#46362: Endpoint should be set to false for TO_DAYS(DATE)
241 # There is a problem when comparing DATE with DATETIME.
242 # In pruning it is converted into the field type
243 # and in row evaluation it is converted to longlong
245 --echo # Test with DATETIME column NOT NULL
247 a int(10) unsigned NOT NULL,
250 ) PARTITION BY RANGE (TO_DAYS(b))
251 (PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')),
252 PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')),
253 PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')),
254 PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')),
255 PARTITION p20090405 VALUES LESS THAN MAXVALUE);
256 INSERT INTO t1 VALUES (1, '2009-01-01'), (1, '2009-04-01'), (2, '2009-04-01'),
257 (1, '2009-04-02'), (2, '2009-04-02'), (1, '2009-04-02 23:59:59'),
258 (1, '2009-04-03'), (2, '2009-04-03'), (1, '2009-04-04'), (2, '2009-04-04'),
259 (1, '2009-04-05'), (1, '2009-04-06'), (1, '2009-04-07');
260 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATETIME);
261 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATETIME);
262 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME);
263 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME);
264 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME);
265 EXPLAIN PARTITIONS SELECT * FROM t1
266 WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME);
267 EXPLAIN PARTITIONS SELECT * FROM t1
268 WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME);
269 EXPLAIN PARTITIONS SELECT * FROM t1
270 WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME);
271 EXPLAIN PARTITIONS SELECT * FROM t1
272 WHERE b >= CAST('2009-04-02 23:59:59' AS DATETIME);
273 EXPLAIN PARTITIONS SELECT * FROM t1
274 WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME);
275 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE);
276 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE);
277 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE);
278 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE);
279 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE);
280 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00';
281 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00';
282 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00';
283 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00';
284 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00';
285 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59';
286 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59';
287 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59';
288 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-02 23:59:59';
289 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59';
290 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03';
291 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03';
292 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03';
293 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03';
294 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03';
295 EXPLAIN PARTITIONS SELECT * FROM t1
296 WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME);
297 EXPLAIN PARTITIONS SELECT * FROM t1
298 WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME);
299 EXPLAIN PARTITIONS SELECT * FROM t1
300 WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME);
301 EXPLAIN PARTITIONS SELECT * FROM t1
302 WHERE b >= CAST('2009-04-03 00:00:01' AS DATETIME);
303 EXPLAIN PARTITIONS SELECT * FROM t1
304 WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME);
305 EXPLAIN PARTITIONS SELECT * FROM t1
306 WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME);
307 EXPLAIN PARTITIONS SELECT * FROM t1
308 WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME);
309 EXPLAIN PARTITIONS SELECT * FROM t1
310 WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME);
311 EXPLAIN PARTITIONS SELECT * FROM t1
312 WHERE b >= CAST('2009-04-02 23:59:58' AS DATETIME);
313 EXPLAIN PARTITIONS SELECT * FROM t1
314 WHERE b > CAST('2009-04-02 23:59:58' AS DATETIME);
317 --echo # Test with DATE column NOT NULL
319 a int(10) unsigned NOT NULL,
322 ) PARTITION BY RANGE (TO_DAYS(b))
323 (PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')),
324 PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')),
325 PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')),
326 PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')),
327 PARTITION p20090405 VALUES LESS THAN MAXVALUE);
328 INSERT INTO t1 VALUES (1, '2009-01-01'), (1, '2009-04-01'), (2, '2009-04-01'),
329 (1, '2009-04-02'), (2, '2009-04-02'), (1, '2009-04-03'), (2, '2009-04-03'),
330 (1, '2009-04-04'), (2, '2009-04-04'), (1, '2009-04-05'), (1, '2009-04-06'),
332 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATETIME);
333 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATETIME);
334 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME);
335 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME);
336 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME);
337 EXPLAIN PARTITIONS SELECT * FROM t1
338 WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME);
339 EXPLAIN PARTITIONS SELECT * FROM t1
340 WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME);
341 EXPLAIN PARTITIONS SELECT * FROM t1
342 WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME);
343 EXPLAIN PARTITIONS SELECT * FROM t1
344 WHERE b >= CAST('2009-04-02 23:59:59' AS DATETIME);
345 EXPLAIN PARTITIONS SELECT * FROM t1
346 WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME);
347 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE);
348 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE);
349 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE);
350 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE);
351 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE);
352 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00';
353 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00';
354 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00';
355 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00';
356 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00';
357 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59';
358 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59';
359 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59';
360 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-02 23:59:59';
361 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59';
362 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03';
363 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03';
364 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03';
365 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03';
366 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03';
367 EXPLAIN PARTITIONS SELECT * FROM t1
368 WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME);
369 EXPLAIN PARTITIONS SELECT * FROM t1
370 WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME);
371 EXPLAIN PARTITIONS SELECT * FROM t1
372 WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME);
373 EXPLAIN PARTITIONS SELECT * FROM t1
374 WHERE b >= CAST('2009-04-03 00:00:01' AS DATETIME);
375 EXPLAIN PARTITIONS SELECT * FROM t1
376 WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME);
377 EXPLAIN PARTITIONS SELECT * FROM t1
378 WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME);
379 EXPLAIN PARTITIONS SELECT * FROM t1
380 WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME);
381 EXPLAIN PARTITIONS SELECT * FROM t1
382 WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME);
383 EXPLAIN PARTITIONS SELECT * FROM t1
384 WHERE b >= CAST('2009-04-02 23:59:58' AS DATETIME);
385 EXPLAIN PARTITIONS SELECT * FROM t1
386 WHERE b > CAST('2009-04-02 23:59:58' AS DATETIME);
389 --echo # Test with DATETIME column NULL
391 a int(10) unsigned NOT NULL,
393 ) PARTITION BY RANGE (TO_DAYS(b))
394 (PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')),
395 PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')),
396 PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')),
397 PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')),
398 PARTITION p20090405 VALUES LESS THAN MAXVALUE);
399 INSERT INTO t1 VALUES (1, '2009-01-01'), (1, '2009-04-01'), (2, '2009-04-01'),
400 (1, '2009-04-02'), (2, '2009-04-02'), (1, '2009-04-02 23:59:59'),
401 (1, '2009-04-03'), (2, '2009-04-03'), (1, '2009-04-04'), (2, '2009-04-04'),
402 (1, '2009-04-05'), (1, '2009-04-06'), (1, '2009-04-07');
403 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATETIME);
404 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATETIME);
405 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME);
406 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME);
407 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME);
408 EXPLAIN PARTITIONS SELECT * FROM t1
409 WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME);
410 EXPLAIN PARTITIONS SELECT * FROM t1
411 WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME);
412 EXPLAIN PARTITIONS SELECT * FROM t1
413 WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME);
414 EXPLAIN PARTITIONS SELECT * FROM t1
415 WHERE b >= CAST('2009-04-02 23:59:59' AS DATETIME);
416 EXPLAIN PARTITIONS SELECT * FROM t1
417 WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME);
418 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE);
419 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE);
420 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE);
421 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE);
422 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE);
423 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00';
424 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00';
425 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00';
426 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00';
427 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00';
428 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59';
429 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59';
430 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59';
431 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-02 23:59:59';
432 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59';
433 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03';
434 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03';
435 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03';
436 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03';
437 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03';
438 EXPLAIN PARTITIONS SELECT * FROM t1
439 WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME);
440 EXPLAIN PARTITIONS SELECT * FROM t1
441 WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME);
442 EXPLAIN PARTITIONS SELECT * FROM t1
443 WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME);
444 EXPLAIN PARTITIONS SELECT * FROM t1
445 WHERE b >= CAST('2009-04-03 00:00:01' AS DATETIME);
446 EXPLAIN PARTITIONS SELECT * FROM t1
447 WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME);
448 EXPLAIN PARTITIONS SELECT * FROM t1
449 WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME);
450 EXPLAIN PARTITIONS SELECT * FROM t1
451 WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME);
452 EXPLAIN PARTITIONS SELECT * FROM t1
453 WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME);
454 EXPLAIN PARTITIONS SELECT * FROM t1
455 WHERE b >= CAST('2009-04-02 23:59:58' AS DATETIME);
456 EXPLAIN PARTITIONS SELECT * FROM t1
457 WHERE b > CAST('2009-04-02 23:59:58' AS DATETIME);
460 --echo # Test with DATE column NULL
462 a int(10) unsigned NOT NULL,
464 ) PARTITION BY RANGE (TO_DAYS(b))
465 (PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')),
466 PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')),
467 PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')),
468 PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')),
469 PARTITION p20090405 VALUES LESS THAN MAXVALUE);
470 INSERT INTO t1 VALUES (1, '2009-01-01'), (1, '2009-04-01'), (2, '2009-04-01'),
471 (1, '2009-04-02'), (2, '2009-04-02'), (1, '2009-04-03'), (2, '2009-04-03'),
472 (1, '2009-04-04'), (2, '2009-04-04'), (1, '2009-04-05'), (1, '2009-04-06'),
474 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATETIME);
475 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATETIME);
476 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME);
477 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME);
478 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME);
479 EXPLAIN PARTITIONS SELECT * FROM t1
480 WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME);
481 EXPLAIN PARTITIONS SELECT * FROM t1
482 WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME);
483 EXPLAIN PARTITIONS SELECT * FROM t1
484 WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME);
485 EXPLAIN PARTITIONS SELECT * FROM t1
486 WHERE b >= CAST('2009-04-02 23:59:59' AS DATETIME);
487 EXPLAIN PARTITIONS SELECT * FROM t1
488 WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME);
489 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE);
490 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE);
491 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE);
492 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE);
493 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE);
494 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00';
495 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00';
496 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00';
497 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00';
498 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00';
499 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59';
500 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59';
501 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59';
502 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-02 23:59:59';
503 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59';
504 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03';
505 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03';
506 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03';
507 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03';
508 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03';
509 EXPLAIN PARTITIONS SELECT * FROM t1
510 WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME);
511 EXPLAIN PARTITIONS SELECT * FROM t1
512 WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME);
513 EXPLAIN PARTITIONS SELECT * FROM t1
514 WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME);
515 EXPLAIN PARTITIONS SELECT * FROM t1
516 WHERE b >= CAST('2009-04-03 00:00:01' AS DATETIME);
517 EXPLAIN PARTITIONS SELECT * FROM t1
518 WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME);
519 EXPLAIN PARTITIONS SELECT * FROM t1
520 WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME);
521 EXPLAIN PARTITIONS SELECT * FROM t1
522 WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME);
523 EXPLAIN PARTITIONS SELECT * FROM t1
524 WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME);
525 EXPLAIN PARTITIONS SELECT * FROM t1
526 WHERE b >= CAST('2009-04-02 23:59:58' AS DATETIME);
527 EXPLAIN PARTITIONS SELECT * FROM t1
528 WHERE b > CAST('2009-04-02 23:59:58' AS DATETIME);
531 --echo # For better code coverage of the patch
533 a int(10) unsigned NOT NULL,
535 ) PARTITION BY RANGE ( TO_DAYS(b) )
536 (PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')),
537 PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')),
538 PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')),
539 PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')),
540 PARTITION p20090405 VALUES LESS THAN MAXVALUE);
541 INSERT INTO t1 VALUES (1, '2009-01-01'), (2, NULL);
542 --echo # test with an invalid date, which lead to item->null_value is set.
543 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-99' AS DATETIME);
547 # Bug#40972: some sql execution lead the whole database crashing
549 # Setup so the start is at partition pX and end is at p1
550 # Pruning does handle 'bad' dates differently.
552 (a INT NOT NULL AUTO_INCREMENT,
556 PARTITION BY RANGE (to_days(b))
557 (PARTITION p0 VALUES LESS THAN (733681) COMMENT = 'LESS THAN 2008-10-01',
558 PARTITION p1 VALUES LESS THAN (733712) COMMENT = 'LESS THAN 2008-11-01',
559 PARTITION pX VALUES LESS THAN MAXVALUE);
560 SELECT a,b FROM t1 WHERE b >= '2008-12-01' AND b < '2009-12-00';
563 # Check if we can infer from condition on partition fields that
564 # no records will match.
565 create table t1 ( a int not null) partition by hash(a) partitions 2;
566 insert into t1 values (1),(2),(3);
567 explain select * from t1 where a=5 and a=6;
570 # Simple HASH partitioning
573 ) partition by hash (a) partitions 2;
574 insert into t1 values (1),(2),(3);
576 explain partitions select * from t1 where a=1;
577 explain partitions select * from t1 where a=2;
578 explain partitions select * from t1 where a=1 or a=2;
580 # Partitioning over several fields
584 ) partition by key(a,b) partitions 2;
585 insert into t2 values (1,1),(2,2),(3,3);
587 explain partitions select * from t2 where a=1;
588 explain partitions select * from t2 where b=1;
590 explain partitions select * from t2 where a=1 and b=1;
592 # RANGE(expr) partitioning
596 partition by range (a*1) (
597 partition p0 values less than (10),
598 partition p1 values less than (20)
600 insert into t3 values (5),(15);
602 explain partitions select * from t3 where a=11;
603 explain partitions select * from t3 where a=10;
604 explain partitions select * from t3 where a=20;
606 explain partitions select * from t3 where a=30;
608 # LIST(expr) partitioning
609 create table t4 (a int not null, b int not null) partition by LIST (a+b) (
610 partition p0 values in (12),
611 partition p1 values in (14)
613 insert into t4 values (10,2), (10,4);
616 explain partitions select * from t4 where (a=10 and b=1) or (a=10 and b=2);
618 # empty OR one OR empty
619 explain partitions select * from t4
620 where (a=10 and b=1) or (a=10 and b=2) or (a=10 and b = 3);
622 # one OR empty OR one
623 explain partitions select * from t4 where (a=10 and b=2) or (a=10 and b=3)
627 explain partitions select * from t4 where (a=10 and b=1) or a=11;
630 explain partitions select * from t4 where (a=10 and b=2) or a=11;
632 drop table t1, t2, t3, t4;
634 # LIST(expr)/HASH subpartitioning.
635 create table t5 (a int not null, b int not null,
636 c int not null, d int not null)
637 partition by LIST(a+b) subpartition by HASH (c+d) subpartitions 2
639 partition p0 values in (12),
640 partition p1 values in (14)
643 insert into t5 values (10,2,0,0), (10,4,0,0), (10,2,0,1), (10,4,0,1);
644 explain partitions select * from t5;
646 # empty OR one OR empty
647 explain partitions select * from t5
648 where (a=10 and b=1) or (a=10 and b=2) or (a=10 and b = 3);
650 # one OR empty OR one
651 explain partitions select * from t5 where (a=10 and b=2) or (a=10 and b=3)
654 # conditions on subpartitions only
655 explain partitions select * from t5 where (c=1 and d=1);
656 explain partitions select * from t5 where (c=2 and d=1);
658 # mixed partition/subpartitions.
659 explain partitions select * from t5 where (a=10 and b=2 and c=1 and d=1) or
663 explain partitions select * from t5 where (a=10 and b=2 and c=1 and d=1) or
664 (b=2 and c=2 and d=1);
666 # LIST(field) partitioning, interval analysis.
667 create table t6 (a int not null) partition by LIST(a) (
668 partition p1 values in (1),
669 partition p3 values in (3),
670 partition p5 values in (5),
671 partition p7 values in (7),
672 partition p9 values in (9)
674 insert into t6 values (1),(3),(5);
676 explain partitions select * from t6 where a < 1;
677 explain partitions select * from t6 where a <= 1;
678 explain partitions select * from t6 where a > 9;
679 explain partitions select * from t6 where a >= 9;
681 explain partitions select * from t6 where a > 0 and a < 5;
682 explain partitions select * from t6 where a > 5 and a < 12;
683 explain partitions select * from t6 where a > 3 and a < 8 ;
685 explain partitions select * from t6 where a >= 0 and a <= 5;
686 explain partitions select * from t6 where a >= 5 and a <= 12;
687 explain partitions select * from t6 where a >= 3 and a <= 8;
689 explain partitions select * from t6 where a > 3 and a < 5;
693 create table t6 (a int unsigned not null) partition by LIST(a) (
694 partition p1 values in (1),
695 partition p3 values in (3),
696 partition p5 values in (5),
697 partition p7 values in (7),
698 partition p9 values in (9)
700 insert into t6 values (1),(3),(5);
702 explain partitions select * from t6 where a < 1;
703 explain partitions select * from t6 where a <= 1;
704 explain partitions select * from t6 where a > 9;
705 explain partitions select * from t6 where a >= 9;
707 explain partitions select * from t6 where a > 0 and a < 5;
708 explain partitions select * from t6 where a > 5 and a < 12;
709 explain partitions select * from t6 where a > 3 and a < 8 ;
711 explain partitions select * from t6 where a >= 0 and a <= 5;
712 explain partitions select * from t6 where a >= 5 and a <= 12;
713 explain partitions select * from t6 where a >= 3 and a <= 8;
715 explain partitions select * from t6 where a > 3 and a < 5;
717 # RANGE(field) partitioning, interval analysis.
718 create table t7 (a int not null) partition by RANGE(a) (
719 partition p10 values less than (10),
720 partition p30 values less than (30),
721 partition p50 values less than (50),
722 partition p70 values less than (70),
723 partition p90 values less than (90)
725 insert into t7 values (10),(30),(50);
728 explain partitions select * from t7 where a < 5;
729 explain partitions select * from t7 where a < 9;
730 explain partitions select * from t7 where a <= 9;
731 explain partitions select * from t7 where a = 9;
732 explain partitions select * from t7 where a >= 9;
733 explain partitions select * from t7 where a > 9;
734 explain partitions select * from t7 where a < 10;
735 explain partitions select * from t7 where a <= 10;
736 explain partitions select * from t7 where a = 10;
737 explain partitions select * from t7 where a >= 10;
738 explain partitions select * from t7 where a > 10;
741 explain partitions select * from t7 where a < 89;
742 explain partitions select * from t7 where a <= 89;
743 explain partitions select * from t7 where a = 89;
744 explain partitions select * from t7 where a > 89;
745 explain partitions select * from t7 where a >= 89;
746 explain partitions select * from t7 where a < 90;
747 explain partitions select * from t7 where a <= 90;
748 explain partitions select * from t7 where a = 90;
749 explain partitions select * from t7 where a > 90;
750 explain partitions select * from t7 where a >= 90;
751 explain partitions select * from t7 where a > 91;
754 explain partitions select * from t7 where a > 11 and a < 29;
758 create table t7 (a int unsigned not null) partition by RANGE(a) (
759 partition p10 values less than (10),
760 partition p30 values less than (30),
761 partition p50 values less than (50),
762 partition p70 values less than (70),
763 partition p90 values less than (90)
765 insert into t7 values (10),(30),(50);
768 explain partitions select * from t7 where a < 5;
769 explain partitions select * from t7 where a < 9;
770 explain partitions select * from t7 where a <= 9;
771 explain partitions select * from t7 where a = 9;
772 explain partitions select * from t7 where a >= 9;
773 explain partitions select * from t7 where a > 9;
774 explain partitions select * from t7 where a < 10;
775 explain partitions select * from t7 where a <= 10;
776 explain partitions select * from t7 where a = 10;
777 explain partitions select * from t7 where a >= 10;
778 explain partitions select * from t7 where a > 10;
781 explain partitions select * from t7 where a < 89;
782 explain partitions select * from t7 where a <= 89;
783 explain partitions select * from t7 where a = 89;
784 explain partitions select * from t7 where a > 89;
785 explain partitions select * from t7 where a >= 89;
786 explain partitions select * from t7 where a < 90;
787 explain partitions select * from t7 where a <= 90;
788 explain partitions select * from t7 where a = 90;
789 explain partitions select * from t7 where a > 90;
790 explain partitions select * from t7 where a >= 90;
791 explain partitions select * from t7 where a > 91;
794 explain partitions select * from t7 where a > 11 and a < 29;
796 # LIST(monontonic_func) partitioning
797 create table t8 (a date not null) partition by RANGE(YEAR(a)) (
798 partition p0 values less than (1980),
799 partition p1 values less than (1990),
800 partition p2 values less than (2000)
802 insert into t8 values ('1985-05-05'),('1995-05-05');
804 explain partitions select * from t8 where a < '1980-02-02';
806 # LIST(strict_monotonic_func) partitioning
807 create table t9 (a date not null) partition by RANGE(TO_DAYS(a)) (
808 partition p0 values less than (732299), -- 2004-12-19
809 partition p1 values less than (732468), -- 2005-06-06
810 partition p2 values less than (732664) -- 2005-12-19
812 insert into t9 values ('2005-05-05'), ('2005-04-04');
814 explain partitions select * from t9 where a < '2004-12-19';
815 explain partitions select * from t9 where a <= '2004-12-19';
817 drop table t5,t6,t7,t8,t9;
819 # Test the case where we can't create partitioning 'index'
821 # Not supported after bug#18198 is fixed
823 #create table t1 (a enum('a','b','c','d') default 'a')
824 # partition by hash (ascii(a)) partitions 2;
825 #insert into t1 values ('a'),('b'),('c');
826 #explain partitions select * from t1 where a='b';
830 # Test cases for bugs found in code review:
835 partition by range (a1) (
836 partition p0 values less than (3),
837 partition p1 values less than (6),
838 partition p2 values less than (9)
840 insert into t1 values (1),(2),(3);
841 explain partitions select * from t1 where a1 > 3;
842 explain partitions select * from t1 where a1 >= 3;
844 explain partitions select * from t1 where a1 < 3 and a1 > 3;
848 create table t3 (a int, b int)
849 partition by list(a) subpartition by hash(b) subpartitions 4 (
850 partition p0 values in (1),
851 partition p1 values in (2),
852 partition p2 values in (3),
853 partition p3 values in (4)
855 insert into t3 values (1,1),(2,2),(3,3);
857 explain partitions select * from t3 where a=2 or b=1;
858 explain partitions select * from t3 where a=4 or b=2;
859 explain partitions select * from t3 where (a=2 or b=1) and (a=4 or b=2) ;
863 create table t1 (a int) partition by hash(a) partitions 2;
864 insert into t1 values (1),(2);
865 explain partitions select * from t1 where a is null;
867 # this uses both partitions
868 explain partitions select * from t1 where a is not null;
872 create table t1 (a int not null, b int not null, key(a), key(b))
873 partition by hash(a) partitions 4;
874 insert into t1 values (1,1),(2,2),(3,3),(4,4);
877 select * from t1 X, t1 Y
878 where X.b = Y.b and (X.a=1 or X.a=2) and (Y.a=2 or Y.a=3);
881 select * from t1 X, t1 Y where X.a = Y.a and (X.a=1 or X.a=2);
885 # Tests for "short ranges"
886 create table t1 (a int) partition by hash(a) partitions 20;
887 insert into t1 values (1),(2),(3);
888 explain partitions select * from t1 where a > 1 and a < 3;
889 explain partitions select * from t1 where a >= 1 and a < 3;
890 explain partitions select * from t1 where a > 1 and a <= 3;
891 explain partitions select * from t1 where a >= 1 and a <= 3;
894 create table t1 (a int, b int)
895 partition by list(a) subpartition by hash(b) subpartitions 20
897 partition p0 values in (0),
898 partition p1 values in (1),
899 partition p2 values in (2),
900 partition p3 values in (3)
902 insert into t1 values (1,1),(2,2),(3,3);
904 explain partitions select * from t1 where b > 1 and b < 3;
905 explain partitions select * from t1 where b > 1 and b < 3 and (a =1 or a =2);
908 # Test partition pruning for single-table UPDATE/DELETE.
909 # TODO: Currently we test only "all partitions pruned away" case. Add more
910 # tests when the patch that makes use of partition pruning results at
911 # execution phase is pushed.
913 create table t1 (a int) partition by list(a) (
914 partition p0 values in (1,2),
915 partition p1 values in (3,4)
917 insert into t1 values (1),(1),(2),(2),(3),(4),(3),(4);
919 # This won't do any table access
921 update t1 set a=100 where a=5;
922 show status like 'Handler_read_rnd_next';
924 # ... as compared to this, which will scan both partitions
926 update t1 set a=100 where a+1=5+1;
927 show status like 'Handler_read_rnd_next';
929 # Same as above for DELETE:
931 delete from t1 where a=5;
932 show status like 'Handler_read_rnd_next';
935 delete from t1 where a+1=5+1;
936 show status like 'Handler_read_rnd_next';
938 # Same as above multi-table UPDATE/DELETE
939 create table t2 like t1;
940 insert into t2 select * from t2;
943 update t1,t2 set t1.a=1000, t2.a=1000 where t1.a=5 and t2.a=5;
944 show status like 'Handler_read_rnd_next';
945 # ^ This shows 3 accesses, these are caused by const table reads.
946 # They should vanish when partition pruning results are used.
949 delete t1,t2 from t1, t2 where t1.a=5 and t2.a=5;
950 show status like 'Handler_read_rnd_next';
954 # WL#2986 Tests (Checking if partition pruning results are used at query
958 `a` int(11) default NULL
960 INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
963 `a` int(11) default NULL,
967 insert into t2 select A.a + 10*(B.a + 10* C.a) from t1 A, t1 B, t1 C ;
968 insert into t1 select a from t2;
972 `a` int(11) default NULL,
973 `b` int(11) default NULL
975 PARTITION BY RANGE (a) (
976 PARTITION p0 VALUES LESS THAN (200),
977 PARTITION p1 VALUES LESS THAN (400),
978 PARTITION p2 VALUES LESS THAN (600),
979 PARTITION p3 VALUES LESS THAN (800),
980 PARTITION p4 VALUES LESS THAN (1001));
982 insert into t2 select a,1 from t1 where a < 200;
983 insert into t2 select a,2 from t1 where a >= 200 and a < 400;
984 insert into t2 select a,3 from t1 where a >= 400 and a < 600;
985 insert into t2 select a,4 from t1 where a >= 600 and a < 800;
986 insert into t2 select a,5 from t1 where a >= 800 and a < 1001;
988 explain partitions select * from t2;
989 explain partitions select * from t2 where a < 801 and a > 200;
990 explain partitions select * from t2 where a < 801 and a > 800;
991 explain partitions select * from t2 where a > 600;
992 explain partitions select * from t2 where a > 600 and b = 1;
993 explain partitions select * from t2 where a > 600 and b = 4;
994 explain partitions select * from t2 where a > 600 and b = 5;
995 explain partitions select * from t2 where b = 5;
998 update t2 set b = 100 where b = 6;
999 show status like 'Handler_read_rnd_next';
1001 update t2 set a = 1002 where a = 1001;
1002 show status like 'Handler_read_rnd_next';
1004 update t2 set b = 6 where a = 600;
1005 show status like 'Handler_read_rnd_next';
1007 update t2 set b = 6 where a > 600 and a < 800;
1008 show status like 'Handler_read_rnd_next';
1010 delete from t2 where a > 600;
1011 show status like 'Handler_read_rnd_next';
1015 `a` int(11) default NULL,
1016 `b` int(11) default NULL,
1019 PARTITION BY RANGE (a) (
1020 PARTITION p0 VALUES LESS THAN (200),
1021 PARTITION p1 VALUES LESS THAN (400),
1022 PARTITION p2 VALUES LESS THAN (600),
1023 PARTITION p3 VALUES LESS THAN (800),
1024 PARTITION p4 VALUES LESS THAN (1001));
1026 insert into t2 select a,1 from t1 where a < 100;
1027 insert into t2 select a,2 from t1 where a >= 200 and a < 300;
1028 insert into t2 select a,3 from t1 where a >= 300 and a < 400;
1029 insert into t2 select a,4 from t1 where a >= 400 and a < 500;
1030 insert into t2 select a,5 from t1 where a >= 500 and a < 600;
1031 insert into t2 select a,6 from t1 where a >= 600 and a < 700;
1032 insert into t2 select a,7 from t1 where a >= 700 and a < 800;
1033 insert into t2 select a,8 from t1 where a >= 800 and a < 900;
1034 insert into t2 select a,9 from t1 where a >= 900 and a < 1001;
1036 explain partitions select * from t2;
1038 explain partitions select * from t2 where a = 101;
1039 explain partitions select * from t2 where a = 550;
1040 explain partitions select * from t2 where a = 833;
1041 explain partitions select * from t2 where (a = 100 OR a = 900);
1042 explain partitions select * from t2 where (a > 100 AND a < 600);
1043 explain partitions select * from t2 where b = 4;
1045 explain extended select * from t2 where b = 6;
1046 explain partitions select * from t2 where b = 6;
1048 explain extended select * from t2 where b in (1,3,5);
1049 explain partitions select * from t2 where b in (1,3,5);
1051 explain extended select * from t2 where b in (2,4,6);
1052 explain partitions select * from t2 where b in (2,4,6);
1054 explain extended select * from t2 where b in (7,8,9);
1055 explain partitions select * from t2 where b in (7,8,9);
1057 explain extended select * from t2 where b > 5;
1058 explain partitions select * from t2 where b > 5;
1060 explain extended select * from t2 where b > 5 and b < 8;
1061 explain partitions select * from t2 where b > 5 and b < 8;
1063 explain extended select * from t2 where b > 5 and b < 7;
1064 explain partitions select * from t2 where b > 5 and b < 7;
1066 explain extended select * from t2 where b > 0 and b < 5;
1067 explain partitions select * from t2 where b > 0 and b < 5;
1070 update t2 set a = 111 where b = 10;
1071 show status like 'Handler_read_rnd_next';
1072 show status like 'Handler_read_key';
1074 update t2 set a = 111 where b in (5,6);
1075 show status like 'Handler_read_rnd_next';
1076 show status like 'Handler_read_key';
1078 update t2 set a = 222 where b = 7;
1079 show status like 'Handler_read_rnd_next';
1080 show status like 'Handler_read_key';
1082 delete from t2 where b = 7;
1083 show status like 'Handler_read_rnd_next';
1084 show status like 'Handler_read_key';
1086 delete from t2 where b > 5;
1087 show status like 'Handler_read_rnd_next';
1088 show status like 'Handler_read_key';
1089 show status like 'Handler_read_prev';
1090 show status like 'Handler_read_next';
1092 delete from t2 where b < 5 or b > 3;
1093 show status like 'Handler_read_rnd_next';
1094 show status like 'Handler_read_key';
1095 show status like 'Handler_read_prev';
1096 show status like 'Handler_read_next';
1101 # part1: mediumint columns
1102 create table t1 ( f_int1 mediumint, f_int2 integer)
1103 partition by list(mod(f_int1,4)) (
1104 partition p_3 values in (-3),
1105 partition p_2 values in (-2),
1106 partition p_1 values in (-1),
1107 partition p0 values in (0),
1108 partition p1 values in (1),
1109 partition p2 values in (2),
1110 partition p3 values in (3)
1113 insert into t1 values (9, 9), (8, 8), (7, 7), (6, 6), (5, 5),
1114 (4, 4), (3, 3), (2, 2), (1, 1);
1115 select * from t1 where f_int1 between 5 and 15 order by f_int1;
1119 # part2: bug in pruning code
1120 #create table t1 (a char(10) binary)
1121 #partition by list(ascii(a))
1122 # (partition p1 values in (ascii('a')),
1123 # partition p2 values in (ascii('b')),
1124 # partition p3 values in (ascii('c')),
1125 # partition p4 values in (ascii('d')),
1126 # partition p5 values in (ascii('e')));
1127 #insert into t1 values ('a'),('bb'),('ccc'),('dddd'),('eeEee');
1128 #select * from t1 where a>='a' and a <= 'dddd';
1129 #explain partitions select * from t1 where a>='a' and a <= 'dddd';
1132 # BUG#18659: Assertion failure when subpartitioning is used and partition is
1134 create table t1 (f_int1 integer) partition by list(abs(mod(f_int1,2)))
1135 subpartition by hash(f_int1) subpartitions 2
1137 partition part1 values in (0),
1138 partition part2 values in (1),
1139 partition part4 values in (null)
1141 insert into t1 set f_int1 = null;
1143 select * from t1 where f_int1 is null;
1144 explain partitions select * from t1 where f_int1 is null;
1150 create table t1 (a int not null, b int not null)
1151 partition by list(a)
1152 subpartition by hash(b) subpartitions 4
1154 partition p0 values in (1),
1155 partition p1 values in (2),
1156 partition p2 values in (3)
1158 insert into t1 values (1,1),(1,2),(1,3),(1,4),
1159 (2,1),(2,2),(2,3),(2,4);
1160 explain partitions select * from t1 where a=1 AND (b=1 OR b=2);
1163 create table t1 (a int, b int not null)
1164 partition by list(a)
1165 subpartition by hash(b) subpartitions 2
1167 partition p0 values in (1),
1168 partition p1 values in (2),
1169 partition p2 values in (3),
1170 partition pn values in (NULL)
1172 insert into t1 values (1,1),(1,2),(1,3),(1,4),
1173 (2,1),(2,2),(2,3),(2,4), (NULL,1);
1175 explain partitions select * from t1 where a IS NULL AND (b=1 OR b=2);
1177 explain partitions select * from t1 where (a IS NULL or a < 1) AND (b=1 OR b=2);
1178 explain partitions select * from t1 where (a IS NULL or a < 2) AND (b=1 OR b=2);
1179 explain partitions select * from t1 where (a IS NULL or a <= 1) AND (b=1 OR b=2);
1183 create table t1 ( a int) partition by list (MOD(a, 10))
1184 ( partition p0 values in (0), partition p1 values in (1),
1185 partition p2 values in (2), partition p3 values in (3),
1186 partition p4 values in (4), partition p5 values in (5),
1187 partition p6 values in (6), partition pn values in (NULL)
1189 insert into t1 values (NULL), (0),(1),(2),(3),(4),(5),(6);
1190 explain partitions select * from t1 where a is null or a < 2;
1193 # Testcase from BUG#18751
1194 create table t1 (s1 int) partition by list (s1)
1195 (partition p1 values in (0),
1196 partition p2 values in (1),
1197 partition p3 values in (null));
1199 insert into t1 values (0),(1),(null);
1201 select count(*) from t1 where s1 < 0 or s1 is null;
1202 explain partitions select count(*) from t1 where s1 < 0 or s1 is null;
1205 # No tests for NULLs in RANGE(monotonic_expr()) - they depend on BUG#15447
1209 # BUG#17946 Like searches fail with partitioning
1211 create table t1 (a char(32) primary key)
1214 insert into t1 values ('na');
1216 select * from t1 where a like 'n%';
1220 # BUG#19055 Crashes for varchar_col=NUMBER or varchar_col IS NULL
1221 create table t1 (s1 varchar(15)) partition by key (s1);
1222 select * from t1 where s1 = 0 or s1 is null;
1223 insert into t1 values ('aa'),('bb'),('0');
1224 explain partitions select * from t1 where s1 = 0 or s1 is null;
1228 # BUG#19684: EXPLAIN PARTITIONS produces garbage in 'partitions' column when
1229 # the length of string to be displayed exceeds some limit.
1230 create table t2 (a int, b int)
1231 partition by LIST(a)
1232 subpartition by HASH(b) subpartitions 40
1233 ( partition p_0_long_partition_name values in(1),
1234 partition p_1_long_partition_name values in(2));
1236 insert into t2 values (1,1),(2,2);
1239 explain partitions select * from t2;
1240 --horizontal_results
1244 # BUG#20484 "Partitions: crash with explain and union"
1245 create table t1 (s1 int);
1246 explain partitions select 1 from t1 union all select 2;
1250 # BUG#20257: partition pruning test coverage for BIGINT UNSIGNED
1251 create table t1 (a bigint unsigned not null) partition by range(a) (
1252 partition p0 values less than (10),
1253 partition p1 values less than (100),
1254 partition p2 values less than (1000),
1255 partition p3 values less than (18446744073709551000),
1256 partition p4 values less than (18446744073709551614)
1258 insert into t1 values (5),(15),(105),(1005);
1259 insert into t1 values (18446744073709551000+1);
1260 insert into t1 values (18446744073709551614-1);
1262 explain partitions select * from t1 where a < 10;
1263 explain partitions select * from t1
1264 where a >= 18446744073709551000-1 and a <= 18446744073709551000+1;
1266 explain partitions select * from t1
1267 where a between 18446744073709551001 and 18446744073709551002;
1269 explain partitions select * from t1 where a = 18446744073709551000;
1270 explain partitions select * from t1 where a = 18446744073709551613;
1271 explain partitions select * from t1 where a = 18446744073709551614;
1274 # Test all variants of usage for interval_via_mapping
1275 # and interval_via_walking
1277 # t1 will use interval_via_mapping since it uses a
1278 # monotonic function, whereas t2 will use
1279 # interval_via_walking since the intervals are short
1280 # and the function isn't monotonic (it is, but it isn't
1281 # discovered in this version).
1283 create table t1 (a int)
1284 partition by range(a) (
1285 partition p0 values less than (64),
1286 partition p1 values less than (128),
1287 partition p2 values less than (255)
1290 create table t2 (a int)
1291 partition by range(a+0) (
1292 partition p0 values less than (64),
1293 partition p1 values less than (128),
1294 partition p2 values less than (255)
1297 insert into t1 values (0x20), (0x20), (0x41), (0x41), (0xFE), (0xFE);
1298 insert into t2 values (0x20), (0x20), (0x41), (0x41), (0xFE), (0xFE);
1299 explain partitions select * from t1 where a=0;
1300 explain partitions select * from t2 where a=0;
1301 explain partitions select * from t1 where a=0xFE;
1302 explain partitions select * from t2 where a=0xFE;
1303 explain partitions select * from t1 where a > 0xFE AND a <= 0xFF;
1304 explain partitions select * from t2 where a > 0xFE AND a <= 0xFF;
1305 explain partitions select * from t1 where a >= 0xFE AND a <= 0xFF;
1306 explain partitions select * from t2 where a >= 0xFE AND a <= 0xFF;
1307 explain partitions select * from t1 where a < 64 AND a >= 63;
1308 explain partitions select * from t2 where a < 64 AND a >= 63;
1309 explain partitions select * from t1 where a <= 64 AND a >= 63;
1310 explain partitions select * from t2 where a <= 64 AND a >= 63;
1314 create table t1(a bigint unsigned not null) partition by range(a+0) (
1315 partition p1 values less than (10),
1316 partition p2 values less than (20),
1317 partition p3 values less than (2305561538531885056),
1318 partition p4 values less than (2305561538531950591)
1321 insert into t1 values (9),(19),(0xFFFF0000FFFF000-1), (0xFFFF0000FFFFFFF-1);
1322 insert into t1 values (9),(19),(0xFFFF0000FFFF000-1), (0xFFFF0000FFFFFFF-1);
1324 explain partitions select * from t1 where
1325 a >= 2305561538531885056-10 and a <= 2305561538531885056-8;
1327 explain partitions select * from t1 where
1328 a > 0xFFFFFFFFFFFFFFEC and a < 0xFFFFFFFFFFFFFFEE;
1330 explain partitions select * from t1 where a>=0 and a <= 0xFFFFFFFFFFFFFFFF;
1333 create table t1 (a bigint) partition by range(a+0) (
1334 partition p1 values less than (-1000),
1335 partition p2 values less than (-10),
1336 partition p3 values less than (10),
1337 partition p4 values less than (1000)
1339 insert into t1 values (-15),(-5),(5),(15),(-15),(-5),(5),(15);
1340 explain partitions select * from t1 where a>-2 and a <=0;
1345 # BUG#27927 Partition pruning not optimal with TO_DAYS function
1348 CREATE TABLE t1 ( recdate DATETIME NOT NULL )
1349 PARTITION BY RANGE( TO_DAYS(recdate) ) (
1350 PARTITION p0 VALUES LESS THAN ( TO_DAYS('2007-03-08') ),
1351 PARTITION p1 VALUES LESS THAN ( TO_DAYS('2007-04-01') )
1353 INSERT INTO t1 VALUES ('2007-03-01 12:00:00');
1354 INSERT INTO t1 VALUES ('2007-03-07 12:00:00');
1355 INSERT INTO t1 VALUES ('2007-03-08 12:00:00');
1356 INSERT INTO t1 VALUES ('2007-03-15 12:00:00');
1357 -- echo must use p0 only:
1358 explain partitions select * from t1 where recdate < '2007-03-08 00:00:00';
1361 CREATE TABLE t1 ( recdate DATETIME NOT NULL )
1362 PARTITION BY RANGE( YEAR(recdate) ) (
1363 PARTITION p0 VALUES LESS THAN (2006),
1364 PARTITION p1 VALUES LESS THAN (2007)
1366 INSERT INTO t1 VALUES ('2005-03-01 12:00:00');
1367 INSERT INTO t1 VALUES ('2005-03-01 12:00:00');
1368 INSERT INTO t1 VALUES ('2006-03-01 12:00:00');
1369 INSERT INTO t1 VALUES ('2006-03-01 12:00:00');
1371 -- echo must use p0 only:
1372 explain partitions select * from t1 where recdate < '2006-01-01 00:00:00';