1
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
3
# Bug#53806: Wrong estimates for range query in partitioned MyISAM table
4
# Bug#46754: 'rows' field doesn't reflect partition pruning
6
CREATE TABLE t1 (a INT PRIMARY KEY)
7
PARTITION BY RANGE (a) (
8
PARTITION p0 VALUES LESS THAN (1),
9
PARTITION p1 VALUES LESS THAN (2),
10
PARTITION p2 VALUES LESS THAN (3),
11
PARTITION p3 VALUES LESS THAN (4),
12
PARTITION p4 VALUES LESS THAN (5),
13
PARTITION p5 VALUES LESS THAN (6),
14
PARTITION max VALUES LESS THAN MAXVALUE);
15
INSERT INTO t1 VALUES (-1),(0),(1),(2),(3),(4),(5),(6),(7),(8);
16
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1;
17
id select_type table partitions type possible_keys key key_len ref rows Extra
19
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 7;
20
id select_type table partitions type possible_keys key key_len ref rows Extra
21
# # # # # # # # # 10 #
22
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1;
23
id select_type table partitions type possible_keys key key_len ref rows Extra
27
# Bug#49742: Partition Pruning not working correctly for RANGE
29
CREATE TABLE t1 (a INT PRIMARY KEY)
30
PARTITION BY RANGE (a) (
31
PARTITION p0 VALUES LESS THAN (1),
32
PARTITION p1 VALUES LESS THAN (2),
33
PARTITION p2 VALUES LESS THAN (3),
34
PARTITION p3 VALUES LESS THAN (4),
35
PARTITION p4 VALUES LESS THAN (5),
36
PARTITION p5 VALUES LESS THAN (6),
37
PARTITION max VALUES LESS THAN MAXVALUE);
38
INSERT INTO t1 VALUES (-1),(0),(1),(2),(3),(4),(5),(6),(7),(8);
39
SELECT * FROM t1 WHERE a < 1;
43
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 1;
44
id select_type table partitions type possible_keys key key_len ref rows Extra
45
1 SIMPLE t1 p0 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index
46
SELECT * FROM t1 WHERE a < 2;
51
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 2;
52
id select_type table partitions type possible_keys key key_len ref rows Extra
53
1 SIMPLE t1 p0,p1 index PRIMARY PRIMARY 4 NULL 3 Using where; Using index
54
SELECT * FROM t1 WHERE a < 3;
60
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 3;
61
id select_type table partitions type possible_keys key key_len ref rows Extra
62
1 SIMPLE t1 p0,p1,p2 index PRIMARY PRIMARY 4 NULL 4 Using where; Using index
63
SELECT * FROM t1 WHERE a < 4;
70
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 4;
71
id select_type table partitions type possible_keys key key_len ref rows Extra
72
1 SIMPLE t1 p0,p1,p2,p3 index PRIMARY PRIMARY 4 NULL 5 Using where; Using index
73
SELECT * FROM t1 WHERE a < 5;
81
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 5;
82
id select_type table partitions type possible_keys key key_len ref rows Extra
83
1 SIMPLE t1 p0,p1,p2,p3,p4 index PRIMARY PRIMARY 4 NULL 6 Using where; Using index
84
SELECT * FROM t1 WHERE a < 6;
93
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 6;
94
id select_type table partitions type possible_keys key key_len ref rows Extra
95
1 SIMPLE t1 p0,p1,p2,p3,p4,p5 index PRIMARY PRIMARY 4 NULL 7 Using where; Using index
96
SELECT * FROM t1 WHERE a < 7;
106
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 7;
107
id select_type table partitions type possible_keys key key_len ref rows Extra
108
1 SIMPLE t1 p0,p1,p2,p3,p4,p5,max index PRIMARY PRIMARY 4 NULL 10 Using where; Using index
109
SELECT * FROM t1 WHERE a <= 1;
114
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1;
115
id select_type table partitions type possible_keys key key_len ref rows Extra
116
1 SIMPLE t1 p0,p1 index PRIMARY PRIMARY 4 NULL 3 Using where; Using index
117
SELECT * FROM t1 WHERE a <= 2;
123
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 2;
124
id select_type table partitions type possible_keys key key_len ref rows Extra
125
1 SIMPLE t1 p0,p1,p2 index PRIMARY PRIMARY 4 NULL 4 Using where; Using index
126
SELECT * FROM t1 WHERE a <= 3;
133
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 3;
134
id select_type table partitions type possible_keys key key_len ref rows Extra
135
1 SIMPLE t1 p0,p1,p2,p3 index PRIMARY PRIMARY 4 NULL 5 Using where; Using index
136
SELECT * FROM t1 WHERE a <= 4;
144
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 4;
145
id select_type table partitions type possible_keys key key_len ref rows Extra
146
1 SIMPLE t1 p0,p1,p2,p3,p4 index PRIMARY PRIMARY 4 NULL 6 Using where; Using index
147
SELECT * FROM t1 WHERE a <= 5;
156
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 5;
157
id select_type table partitions type possible_keys key key_len ref rows Extra
158
1 SIMPLE t1 p0,p1,p2,p3,p4,p5 index PRIMARY PRIMARY 4 NULL 7 Using where; Using index
159
SELECT * FROM t1 WHERE a <= 6;
169
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 6;
170
id select_type table partitions type possible_keys key key_len ref rows Extra
171
1 SIMPLE t1 p0,p1,p2,p3,p4,p5,max index PRIMARY PRIMARY 4 NULL 10 Using where; Using index
172
SELECT * FROM t1 WHERE a <= 7;
183
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 7;
184
id select_type table partitions type possible_keys key key_len ref rows Extra
185
1 SIMPLE t1 p0,p1,p2,p3,p4,p5,max index PRIMARY PRIMARY 4 NULL 10 Using where; Using index
186
SELECT * FROM t1 WHERE a = 1;
189
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 1;
190
id select_type table partitions type possible_keys key key_len ref rows Extra
191
1 SIMPLE t1 p1 system PRIMARY NULL NULL NULL 1 NULL
192
SELECT * FROM t1 WHERE a = 2;
195
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 2;
196
id select_type table partitions type possible_keys key key_len ref rows Extra
197
1 SIMPLE t1 p2 system PRIMARY NULL NULL NULL 1 NULL
198
SELECT * FROM t1 WHERE a = 3;
201
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 3;
202
id select_type table partitions type possible_keys key key_len ref rows Extra
203
1 SIMPLE t1 p3 system PRIMARY NULL NULL NULL 1 NULL
204
SELECT * FROM t1 WHERE a = 4;
207
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 4;
208
id select_type table partitions type possible_keys key key_len ref rows Extra
209
1 SIMPLE t1 p4 system PRIMARY NULL NULL NULL 1 NULL
210
SELECT * FROM t1 WHERE a = 5;
213
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 5;
214
id select_type table partitions type possible_keys key key_len ref rows Extra
215
1 SIMPLE t1 p5 system PRIMARY NULL NULL NULL 1 NULL
216
SELECT * FROM t1 WHERE a = 6;
219
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 6;
220
id select_type table partitions type possible_keys key key_len ref rows Extra
221
1 SIMPLE t1 max const PRIMARY PRIMARY 4 const 1 Using index
222
SELECT * FROM t1 WHERE a = 7;
225
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 7;
226
id select_type table partitions type possible_keys key key_len ref rows Extra
227
1 SIMPLE t1 max const PRIMARY PRIMARY 4 const 1 Using index
228
SELECT * FROM t1 WHERE a >= 1;
238
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 1;
239
id select_type table partitions type possible_keys key key_len ref rows Extra
240
1 SIMPLE t1 p1,p2,p3,p4,p5,max index PRIMARY PRIMARY 4 NULL 8 Using where; Using index
241
SELECT * FROM t1 WHERE a >= 2;
250
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 2;
251
id select_type table partitions type possible_keys key key_len ref rows Extra
252
1 SIMPLE t1 p2,p3,p4,p5,max index PRIMARY PRIMARY 4 NULL 7 Using where; Using index
253
SELECT * FROM t1 WHERE a >= 3;
261
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 3;
262
id select_type table partitions type possible_keys key key_len ref rows Extra
263
1 SIMPLE t1 p3,p4,p5,max index PRIMARY PRIMARY 4 NULL 6 Using where; Using index
264
SELECT * FROM t1 WHERE a >= 4;
271
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 4;
272
id select_type table partitions type possible_keys key key_len ref rows Extra
273
1 SIMPLE t1 p4,p5,max index PRIMARY PRIMARY 4 NULL 5 Using where; Using index
274
SELECT * FROM t1 WHERE a >= 5;
280
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 5;
281
id select_type table partitions type possible_keys key key_len ref rows Extra
282
1 SIMPLE t1 p5,max index PRIMARY PRIMARY 4 NULL 4 Using where; Using index
283
SELECT * FROM t1 WHERE a >= 6;
288
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 6;
289
id select_type table partitions type possible_keys key key_len ref rows Extra
290
1 SIMPLE t1 max index PRIMARY PRIMARY 4 NULL 3 Using where; Using index
291
SELECT * FROM t1 WHERE a >= 7;
295
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 7;
296
id select_type table partitions type possible_keys key key_len ref rows Extra
297
1 SIMPLE t1 max index PRIMARY PRIMARY 4 NULL 3 Using where; Using index
298
SELECT * FROM t1 WHERE a > 1;
307
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 1;
308
id select_type table partitions type possible_keys key key_len ref rows Extra
309
1 SIMPLE t1 p2,p3,p4,p5,max index PRIMARY PRIMARY 4 NULL 7 Using where; Using index
310
SELECT * FROM t1 WHERE a > 2;
318
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 2;
319
id select_type table partitions type possible_keys key key_len ref rows Extra
320
1 SIMPLE t1 p3,p4,p5,max index PRIMARY PRIMARY 4 NULL 6 Using where; Using index
321
SELECT * FROM t1 WHERE a > 3;
328
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 3;
329
id select_type table partitions type possible_keys key key_len ref rows Extra
330
1 SIMPLE t1 p4,p5,max index PRIMARY PRIMARY 4 NULL 5 Using where; Using index
331
SELECT * FROM t1 WHERE a > 4;
337
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 4;
338
id select_type table partitions type possible_keys key key_len ref rows Extra
339
1 SIMPLE t1 p5,max index PRIMARY PRIMARY 4 NULL 4 Using where; Using index
340
SELECT * FROM t1 WHERE a > 5;
345
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 5;
346
id select_type table partitions type possible_keys key key_len ref rows Extra
347
1 SIMPLE t1 max index PRIMARY PRIMARY 4 NULL 3 Using where; Using index
348
SELECT * FROM t1 WHERE a > 6;
352
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 6;
353
id select_type table partitions type possible_keys key key_len ref rows Extra
354
1 SIMPLE t1 max index PRIMARY PRIMARY 4 NULL 3 Using where; Using index
355
SELECT * FROM t1 WHERE a > 7;
358
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 7;
359
id select_type table partitions type possible_keys key key_len ref rows Extra
360
1 SIMPLE t1 max index PRIMARY PRIMARY 4 NULL 3 Using where; Using index
362
CREATE TABLE t1 (a INT PRIMARY KEY)
363
PARTITION BY RANGE (a) (
364
PARTITION p0 VALUES LESS THAN (1),
365
PARTITION p1 VALUES LESS THAN (2),
366
PARTITION p2 VALUES LESS THAN (3),
367
PARTITION p3 VALUES LESS THAN (4),
368
PARTITION p4 VALUES LESS THAN (5),
369
PARTITION max VALUES LESS THAN MAXVALUE);
370
INSERT INTO t1 VALUES (-1),(0),(1),(2),(3),(4),(5),(6),(7);
371
SELECT * FROM t1 WHERE a < 1;
375
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 1;
376
id select_type table partitions type possible_keys key key_len ref rows Extra
377
1 SIMPLE t1 p0 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index
378
SELECT * FROM t1 WHERE a < 2;
383
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 2;
384
id select_type table partitions type possible_keys key key_len ref rows Extra
385
1 SIMPLE t1 p0,p1 index PRIMARY PRIMARY 4 NULL 3 Using where; Using index
386
SELECT * FROM t1 WHERE a < 3;
392
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 3;
393
id select_type table partitions type possible_keys key key_len ref rows Extra
394
1 SIMPLE t1 p0,p1,p2 index PRIMARY PRIMARY 4 NULL 4 Using where; Using index
395
SELECT * FROM t1 WHERE a < 4;
402
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 4;
403
id select_type table partitions type possible_keys key key_len ref rows Extra
404
1 SIMPLE t1 p0,p1,p2,p3 index PRIMARY PRIMARY 4 NULL 5 Using where; Using index
405
SELECT * FROM t1 WHERE a < 5;
413
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 5;
414
id select_type table partitions type possible_keys key key_len ref rows Extra
415
1 SIMPLE t1 p0,p1,p2,p3,p4 index PRIMARY PRIMARY 4 NULL 6 Using where; Using index
416
SELECT * FROM t1 WHERE a < 6;
425
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 6;
426
id select_type table partitions type possible_keys key key_len ref rows Extra
427
1 SIMPLE t1 p0,p1,p2,p3,p4,max index PRIMARY PRIMARY 4 NULL 9 Using where; Using index
428
SELECT * FROM t1 WHERE a <= 1;
433
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1;
434
id select_type table partitions type possible_keys key key_len ref rows Extra
435
1 SIMPLE t1 p0,p1 index PRIMARY PRIMARY 4 NULL 3 Using where; Using index
436
SELECT * FROM t1 WHERE a <= 2;
442
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 2;
443
id select_type table partitions type possible_keys key key_len ref rows Extra
444
1 SIMPLE t1 p0,p1,p2 index PRIMARY PRIMARY 4 NULL 4 Using where; Using index
445
SELECT * FROM t1 WHERE a <= 3;
452
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 3;
453
id select_type table partitions type possible_keys key key_len ref rows Extra
454
1 SIMPLE t1 p0,p1,p2,p3 index PRIMARY PRIMARY 4 NULL 5 Using where; Using index
455
SELECT * FROM t1 WHERE a <= 4;
463
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 4;
464
id select_type table partitions type possible_keys key key_len ref rows Extra
465
1 SIMPLE t1 p0,p1,p2,p3,p4 index PRIMARY PRIMARY 4 NULL 6 Using where; Using index
466
SELECT * FROM t1 WHERE a <= 5;
475
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 5;
476
id select_type table partitions type possible_keys key key_len ref rows Extra
477
1 SIMPLE t1 p0,p1,p2,p3,p4,max index PRIMARY PRIMARY 4 NULL 9 Using where; Using index
478
SELECT * FROM t1 WHERE a <= 6;
488
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 6;
489
id select_type table partitions type possible_keys key key_len ref rows Extra
490
1 SIMPLE t1 p0,p1,p2,p3,p4,max index PRIMARY PRIMARY 4 NULL 9 Using where; Using index
491
SELECT * FROM t1 WHERE a = 1;
494
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 1;
495
id select_type table partitions type possible_keys key key_len ref rows Extra
496
1 SIMPLE t1 p1 system PRIMARY NULL NULL NULL 1 NULL
497
SELECT * FROM t1 WHERE a = 2;
500
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 2;
501
id select_type table partitions type possible_keys key key_len ref rows Extra
502
1 SIMPLE t1 p2 system PRIMARY NULL NULL NULL 1 NULL
503
SELECT * FROM t1 WHERE a = 3;
506
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 3;
507
id select_type table partitions type possible_keys key key_len ref rows Extra
508
1 SIMPLE t1 p3 system PRIMARY NULL NULL NULL 1 NULL
509
SELECT * FROM t1 WHERE a = 4;
512
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 4;
513
id select_type table partitions type possible_keys key key_len ref rows Extra
514
1 SIMPLE t1 p4 system PRIMARY NULL NULL NULL 1 NULL
515
SELECT * FROM t1 WHERE a = 5;
518
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 5;
519
id select_type table partitions type possible_keys key key_len ref rows Extra
520
1 SIMPLE t1 max const PRIMARY PRIMARY 4 const 1 Using index
521
SELECT * FROM t1 WHERE a = 6;
524
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 6;
525
id select_type table partitions type possible_keys key key_len ref rows Extra
526
1 SIMPLE t1 max const PRIMARY PRIMARY 4 const 1 Using index
527
SELECT * FROM t1 WHERE a >= 1;
536
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 1;
537
id select_type table partitions type possible_keys key key_len ref rows Extra
538
1 SIMPLE t1 p1,p2,p3,p4,max index PRIMARY PRIMARY 4 NULL 7 Using where; Using index
539
SELECT * FROM t1 WHERE a >= 2;
547
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 2;
548
id select_type table partitions type possible_keys key key_len ref rows Extra
549
1 SIMPLE t1 p2,p3,p4,max index PRIMARY PRIMARY 4 NULL 6 Using where; Using index
550
SELECT * FROM t1 WHERE a >= 3;
557
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 3;
558
id select_type table partitions type possible_keys key key_len ref rows Extra
559
1 SIMPLE t1 p3,p4,max index PRIMARY PRIMARY 4 NULL 5 Using where; Using index
560
SELECT * FROM t1 WHERE a >= 4;
566
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 4;
567
id select_type table partitions type possible_keys key key_len ref rows Extra
568
1 SIMPLE t1 p4,max index PRIMARY PRIMARY 4 NULL 4 Using where; Using index
569
SELECT * FROM t1 WHERE a >= 5;
574
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 5;
575
id select_type table partitions type possible_keys key key_len ref rows Extra
576
1 SIMPLE t1 max index PRIMARY PRIMARY 4 NULL 3 Using where; Using index
577
SELECT * FROM t1 WHERE a >= 6;
581
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 6;
582
id select_type table partitions type possible_keys key key_len ref rows Extra
583
1 SIMPLE t1 max index PRIMARY PRIMARY 4 NULL 3 Using where; Using index
584
SELECT * FROM t1 WHERE a > 1;
592
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 1;
593
id select_type table partitions type possible_keys key key_len ref rows Extra
594
1 SIMPLE t1 p2,p3,p4,max index PRIMARY PRIMARY 4 NULL 6 Using where; Using index
595
SELECT * FROM t1 WHERE a > 2;
602
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 2;
603
id select_type table partitions type possible_keys key key_len ref rows Extra
604
1 SIMPLE t1 p3,p4,max index PRIMARY PRIMARY 4 NULL 5 Using where; Using index
605
SELECT * FROM t1 WHERE a > 3;
611
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 3;
612
id select_type table partitions type possible_keys key key_len ref rows Extra
613
1 SIMPLE t1 p4,max index PRIMARY PRIMARY 4 NULL 4 Using where; Using index
614
SELECT * FROM t1 WHERE a > 4;
619
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 4;
620
id select_type table partitions type possible_keys key key_len ref rows Extra
621
1 SIMPLE t1 max index PRIMARY PRIMARY 4 NULL 3 Using where; Using index
622
SELECT * FROM t1 WHERE a > 5;
626
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 5;
627
id select_type table partitions type possible_keys key key_len ref rows Extra
628
1 SIMPLE t1 max index PRIMARY PRIMARY 4 NULL 3 Using where; Using index
629
SELECT * FROM t1 WHERE a > 6;
632
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 6;
633
id select_type table partitions type possible_keys key key_len ref rows Extra
634
1 SIMPLE t1 max index PRIMARY PRIMARY 4 NULL 3 Using where; Using index
636
# test of RANGE and index
637
CREATE TABLE t1 (a DATE, KEY(a))
638
PARTITION BY RANGE (TO_DAYS(a))
639
(PARTITION `pNULL` VALUES LESS THAN (0),
640
PARTITION `p0001-01-01` VALUES LESS THAN (366 + 1),
641
PARTITION `p1001-01-01` VALUES LESS THAN (TO_DAYS('1001-01-01') + 1),
642
PARTITION `p2001-01-01` VALUES LESS THAN (TO_DAYS('2001-01-01') + 1));
643
INSERT INTO t1 VALUES ('0000-00-00'), ('0000-01-02'), ('0001-01-01'),
644
('1001-00-00'), ('1001-01-01'), ('1002-00-00'), ('2001-01-01');
645
SELECT * FROM t1 WHERE a < '1001-01-01';
651
SELECT * FROM t1 WHERE a <= '1001-01-01';
658
SELECT * FROM t1 WHERE a >= '1001-01-01';
663
SELECT * FROM t1 WHERE a > '1001-01-01';
667
SELECT * FROM t1 WHERE a = '1001-01-01';
670
SELECT * FROM t1 WHERE a < '1001-00-00';
675
SELECT * FROM t1 WHERE a <= '1001-00-00';
681
SELECT * FROM t1 WHERE a >= '1001-00-00';
687
SELECT * FROM t1 WHERE a > '1001-00-00';
692
SELECT * FROM t1 WHERE a = '1001-00-00';
695
# Disabling warnings for the invalid date
696
SELECT * FROM t1 WHERE a < '1999-02-31';
704
SELECT * FROM t1 WHERE a <= '1999-02-31';
712
SELECT * FROM t1 WHERE a >= '1999-02-31';
715
SELECT * FROM t1 WHERE a > '1999-02-31';
718
SELECT * FROM t1 WHERE a = '1999-02-31';
720
SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00';
728
SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01';
735
SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00';
740
SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01';
745
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-01-01';
746
id select_type table partitions type possible_keys key key_len ref rows Extra
747
1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 index a a 4 NULL 6 Using where; Using index
748
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-01-01';
749
id select_type table partitions type possible_keys key key_len ref rows Extra
750
1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 index a a 4 NULL 6 Using where; Using index
751
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-01-01';
752
id select_type table partitions type possible_keys key key_len ref rows Extra
753
1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 3 Using where; Using index
754
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-01-01';
755
id select_type table partitions type possible_keys key key_len ref rows Extra
756
1 SIMPLE t1 pNULL,p2001-01-01 range a a 4 NULL 3 Using where; Using index
757
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-01-01';
758
id select_type table partitions type possible_keys key key_len ref rows Extra
759
1 SIMPLE t1 p1001-01-01 system a NULL NULL NULL 1 NULL
760
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-00-00';
761
id select_type table partitions type possible_keys key key_len ref rows Extra
762
1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 index a a 4 NULL 6 Using where; Using index
763
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-00-00';
764
id select_type table partitions type possible_keys key key_len ref rows Extra
765
1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 index a a 4 NULL 6 Using where; Using index
766
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-00-00';
767
id select_type table partitions type possible_keys key key_len ref rows Extra
768
1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 3 Using where; Using index
769
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-00-00';
770
id select_type table partitions type possible_keys key key_len ref rows Extra
771
1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 3 Using where; Using index
772
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-00-00';
773
id select_type table partitions type possible_keys key key_len ref rows Extra
774
1 SIMPLE t1 pNULL ref a a 4 const 1 Using index
775
# Disabling warnings for the invalid date
776
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1999-02-31';
777
id select_type table partitions type possible_keys key key_len ref rows Extra
778
1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 range a a 4 NULL 4 Using where; Using index
779
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1999-02-31';
780
id select_type table partitions type possible_keys key key_len ref rows Extra
781
1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 range a a 4 NULL 4 Using where; Using index
782
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1999-02-31';
783
id select_type table partitions type possible_keys key key_len ref rows Extra
784
1 SIMPLE t1 pNULL,p2001-01-01 index a a 4 NULL 4 Using where; Using index
785
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1999-02-31';
786
id select_type table partitions type possible_keys key key_len ref rows Extra
787
1 SIMPLE t1 pNULL,p2001-01-01 index a a 4 NULL 4 Using where; Using index
788
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1999-02-31';
789
id select_type table partitions type possible_keys key key_len ref rows Extra
790
1 SIMPLE t1 pNULL ref a a 4 const 1 Using where; Using index
791
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00';
792
id select_type table partitions type possible_keys key key_len ref rows Extra
793
1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 range a a 4 NULL 4 Using where; Using index
794
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01';
795
id select_type table partitions type possible_keys key key_len ref rows Extra
796
1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 index a a 4 NULL 6 Using where; Using index
797
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00';
798
id select_type table partitions type possible_keys key key_len ref rows Extra
799
1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 index a a 4 NULL 5 Using where; Using index
800
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01';
801
id select_type table partitions type possible_keys key key_len ref rows Extra
802
1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 index a a 4 NULL 6 Using where; Using index
804
ALTER TABLE t1 DROP KEY a;
805
SELECT * FROM t1 WHERE a < '1001-01-01';
811
SELECT * FROM t1 WHERE a <= '1001-01-01';
818
SELECT * FROM t1 WHERE a >= '1001-01-01';
823
SELECT * FROM t1 WHERE a > '1001-01-01';
827
SELECT * FROM t1 WHERE a = '1001-01-01';
830
SELECT * FROM t1 WHERE a < '1001-00-00';
835
SELECT * FROM t1 WHERE a <= '1001-00-00';
841
SELECT * FROM t1 WHERE a >= '1001-00-00';
847
SELECT * FROM t1 WHERE a > '1001-00-00';
852
SELECT * FROM t1 WHERE a = '1001-00-00';
855
# Disabling warnings for the invalid date
856
SELECT * FROM t1 WHERE a < '1999-02-31';
864
SELECT * FROM t1 WHERE a <= '1999-02-31';
872
SELECT * FROM t1 WHERE a >= '1999-02-31';
875
SELECT * FROM t1 WHERE a > '1999-02-31';
878
SELECT * FROM t1 WHERE a = '1999-02-31';
880
SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00';
888
SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01';
895
SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00';
900
SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01';
905
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-01-01';
906
id select_type table partitions type possible_keys key key_len ref rows Extra
907
1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 ALL NULL NULL NULL NULL 6 Using where
908
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-01-01';
909
id select_type table partitions type possible_keys key key_len ref rows Extra
910
1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 ALL NULL NULL NULL NULL 6 Using where
911
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-01-01';
912
id select_type table partitions type possible_keys key key_len ref rows Extra
913
1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 ALL NULL NULL NULL NULL 5 Using where
914
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-01-01';
915
id select_type table partitions type possible_keys key key_len ref rows Extra
916
1 SIMPLE t1 pNULL,p2001-01-01 ALL NULL NULL NULL NULL 4 Using where
917
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-01-01';
918
id select_type table partitions type possible_keys key key_len ref rows Extra
919
1 SIMPLE t1 p1001-01-01 system NULL NULL NULL NULL 1 NULL
920
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-00-00';
921
id select_type table partitions type possible_keys key key_len ref rows Extra
922
1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 ALL NULL NULL NULL NULL 6 Using where
923
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-00-00';
924
id select_type table partitions type possible_keys key key_len ref rows Extra
925
1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 ALL NULL NULL NULL NULL 6 Using where
926
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-00-00';
927
id select_type table partitions type possible_keys key key_len ref rows Extra
928
1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 ALL NULL NULL NULL NULL 5 Using where
929
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-00-00';
930
id select_type table partitions type possible_keys key key_len ref rows Extra
931
1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 ALL NULL NULL NULL NULL 5 Using where
932
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-00-00';
933
id select_type table partitions type possible_keys key key_len ref rows Extra
934
1 SIMPLE t1 pNULL ALL NULL NULL NULL NULL 3 Using where
935
# Disabling warnings for the invalid date
936
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1999-02-31';
937
id select_type table partitions type possible_keys key key_len ref rows Extra
938
1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 ALL NULL NULL NULL NULL 7 Using where
939
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1999-02-31';
940
id select_type table partitions type possible_keys key key_len ref rows Extra
941
1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 ALL NULL NULL NULL NULL 7 Using where
942
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1999-02-31';
943
id select_type table partitions type possible_keys key key_len ref rows Extra
944
1 SIMPLE t1 pNULL,p2001-01-01 ALL NULL NULL NULL NULL 4 Using where
945
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1999-02-31';
946
id select_type table partitions type possible_keys key key_len ref rows Extra
947
1 SIMPLE t1 pNULL,p2001-01-01 ALL NULL NULL NULL NULL 4 Using where
948
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1999-02-31';
949
id select_type table partitions type possible_keys key key_len ref rows Extra
950
1 SIMPLE t1 pNULL ALL NULL NULL NULL NULL 3 Using where
951
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00';
952
id select_type table partitions type possible_keys key key_len ref rows Extra
953
1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 ALL NULL NULL NULL NULL 7 Using where
954
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01';
955
id select_type table partitions type possible_keys key key_len ref rows Extra
956
1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 ALL NULL NULL NULL NULL 6 Using where
957
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00';
958
id select_type table partitions type possible_keys key key_len ref rows Extra
959
1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 ALL NULL NULL NULL NULL 5 Using where
960
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01';
961
id select_type table partitions type possible_keys key key_len ref rows Extra
962
1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 ALL NULL NULL NULL NULL 6 Using where
964
# test of LIST and index
965
CREATE TABLE t1 (a DATE, KEY(a))
966
PARTITION BY LIST (TO_DAYS(a))
967
(PARTITION `p0001-01-01` VALUES IN (TO_DAYS('0001-01-01')),
968
PARTITION `p2001-01-01` VALUES IN (TO_DAYS('2001-01-01')),
969
PARTITION `pNULL` VALUES IN (NULL),
970
PARTITION `p0000-01-02` VALUES IN (TO_DAYS('0000-01-02')),
971
PARTITION `p1001-01-01` VALUES IN (TO_DAYS('1001-01-01')));
972
INSERT INTO t1 VALUES ('0000-00-00'), ('0000-01-02'), ('0001-01-01'),
973
('1001-00-00'), ('1001-01-01'), ('1002-00-00'), ('2001-01-01');
974
SELECT * FROM t1 WHERE a < '1001-01-01';
980
SELECT * FROM t1 WHERE a <= '1001-01-01';
987
SELECT * FROM t1 WHERE a >= '1001-01-01';
992
SELECT * FROM t1 WHERE a > '1001-01-01';
996
SELECT * FROM t1 WHERE a = '1001-01-01';
999
SELECT * FROM t1 WHERE a < '1001-00-00';
1004
SELECT * FROM t1 WHERE a <= '1001-00-00';
1010
SELECT * FROM t1 WHERE a >= '1001-00-00';
1016
SELECT * FROM t1 WHERE a > '1001-00-00';
1021
SELECT * FROM t1 WHERE a = '1001-00-00';
1024
# Disabling warnings for the invalid date
1025
SELECT * FROM t1 WHERE a < '1999-02-31';
1033
SELECT * FROM t1 WHERE a <= '1999-02-31';
1041
SELECT * FROM t1 WHERE a >= '1999-02-31';
1044
SELECT * FROM t1 WHERE a > '1999-02-31';
1047
SELECT * FROM t1 WHERE a = '1999-02-31';
1049
SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00';
1057
SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01';
1064
SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00';
1069
SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01';
1074
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-01-01';
1075
id select_type table partitions type possible_keys key key_len ref rows Extra
1076
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 range a a 4 NULL 3 Using where; Using index
1077
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-01-01';
1078
id select_type table partitions type possible_keys key key_len ref rows Extra
1079
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 3 Using where; Using index
1080
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-01-01';
1081
id select_type table partitions type possible_keys key key_len ref rows Extra
1082
1 SIMPLE t1 p2001-01-01,pNULL,p1001-01-01 range a a 4 NULL 4 Using where; Using index
1083
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-01-01';
1084
id select_type table partitions type possible_keys key key_len ref rows Extra
1085
1 SIMPLE t1 p2001-01-01,pNULL range a a 4 NULL 3 Using where; Using index
1086
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-01-01';
1087
id select_type table partitions type possible_keys key key_len ref rows Extra
1088
1 SIMPLE t1 p1001-01-01 system a NULL NULL NULL 1 NULL
1089
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-00-00';
1090
id select_type table partitions type possible_keys key key_len ref rows Extra
1091
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 range a a 4 NULL 3 Using where; Using index
1092
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-00-00';
1093
id select_type table partitions type possible_keys key key_len ref rows Extra
1094
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 range a a 4 NULL 3 Using where; Using index
1095
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-00-00';
1096
id select_type table partitions type possible_keys key key_len ref rows Extra
1097
1 SIMPLE t1 p2001-01-01,pNULL,p1001-01-01 range a a 4 NULL 4 Using where; Using index
1098
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-00-00';
1099
id select_type table partitions type possible_keys key key_len ref rows Extra
1100
1 SIMPLE t1 p2001-01-01,pNULL,p1001-01-01 range a a 4 NULL 4 Using where; Using index
1101
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-00-00';
1102
id select_type table partitions type possible_keys key key_len ref rows Extra
1103
1 SIMPLE t1 pNULL ref a a 4 const 1 Using index
1104
# Disabling warnings for the invalid date
1105
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1999-02-31';
1106
id select_type table partitions type possible_keys key key_len ref rows Extra
1107
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index
1108
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1999-02-31';
1109
id select_type table partitions type possible_keys key key_len ref rows Extra
1110
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index
1111
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1999-02-31';
1112
id select_type table partitions type possible_keys key key_len ref rows Extra
1113
1 SIMPLE t1 p2001-01-01,pNULL index a a 4 NULL 4 Using where; Using index
1114
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1999-02-31';
1115
id select_type table partitions type possible_keys key key_len ref rows Extra
1116
1 SIMPLE t1 p2001-01-01,pNULL index a a 4 NULL 4 Using where; Using index
1117
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1999-02-31';
1118
id select_type table partitions type possible_keys key key_len ref rows Extra
1119
1 SIMPLE t1 pNULL ref a a 4 const 1 Using where; Using index
1120
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00';
1121
id select_type table partitions type possible_keys key key_len ref rows Extra
1122
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index
1123
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01';
1124
id select_type table partitions type possible_keys key key_len ref rows Extra
1125
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 3 Using where; Using index
1126
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00';
1127
id select_type table partitions type possible_keys key key_len ref rows Extra
1128
1 SIMPLE t1 pNULL,p1001-01-01 index a a 4 NULL 4 Using where; Using index
1129
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01';
1130
id select_type table partitions type possible_keys key key_len ref rows Extra
1131
1 SIMPLE t1 p0001-01-01,pNULL,p1001-01-01 range a a 4 NULL 3 Using where; Using index
1132
# test without index
1133
ALTER TABLE t1 DROP KEY a;
1134
SELECT * FROM t1 WHERE a < '1001-01-01';
1140
SELECT * FROM t1 WHERE a <= '1001-01-01';
1147
SELECT * FROM t1 WHERE a >= '1001-01-01';
1152
SELECT * FROM t1 WHERE a > '1001-01-01';
1156
SELECT * FROM t1 WHERE a = '1001-01-01';
1159
SELECT * FROM t1 WHERE a < '1001-00-00';
1164
SELECT * FROM t1 WHERE a <= '1001-00-00';
1170
SELECT * FROM t1 WHERE a >= '1001-00-00';
1176
SELECT * FROM t1 WHERE a > '1001-00-00';
1181
SELECT * FROM t1 WHERE a = '1001-00-00';
1184
# Disabling warnings for the invalid date
1185
SELECT * FROM t1 WHERE a < '1999-02-31';
1193
SELECT * FROM t1 WHERE a <= '1999-02-31';
1201
SELECT * FROM t1 WHERE a >= '1999-02-31';
1204
SELECT * FROM t1 WHERE a > '1999-02-31';
1207
SELECT * FROM t1 WHERE a = '1999-02-31';
1209
SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00';
1217
SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01';
1224
SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00';
1229
SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01';
1234
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-01-01';
1235
id select_type table partitions type possible_keys key key_len ref rows Extra
1236
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 ALL NULL NULL NULL NULL 5 Using where
1237
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-01-01';
1238
id select_type table partitions type possible_keys key key_len ref rows Extra
1239
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 ALL NULL NULL NULL NULL 6 Using where
1240
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-01-01';
1241
id select_type table partitions type possible_keys key key_len ref rows Extra
1242
1 SIMPLE t1 p2001-01-01,pNULL,p1001-01-01 ALL NULL NULL NULL NULL 5 Using where
1243
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-01-01';
1244
id select_type table partitions type possible_keys key key_len ref rows Extra
1245
1 SIMPLE t1 p2001-01-01,pNULL ALL NULL NULL NULL NULL 4 Using where
1246
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-01-01';
1247
id select_type table partitions type possible_keys key key_len ref rows Extra
1248
1 SIMPLE t1 p1001-01-01 system NULL NULL NULL NULL 1 NULL
1249
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-00-00';
1250
id select_type table partitions type possible_keys key key_len ref rows Extra
1251
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 ALL NULL NULL NULL NULL 5 Using where
1252
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-00-00';
1253
id select_type table partitions type possible_keys key key_len ref rows Extra
1254
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 ALL NULL NULL NULL NULL 5 Using where
1255
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-00-00';
1256
id select_type table partitions type possible_keys key key_len ref rows Extra
1257
1 SIMPLE t1 p2001-01-01,pNULL,p1001-01-01 ALL NULL NULL NULL NULL 5 Using where
1258
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-00-00';
1259
id select_type table partitions type possible_keys key key_len ref rows Extra
1260
1 SIMPLE t1 p2001-01-01,pNULL,p1001-01-01 ALL NULL NULL NULL NULL 5 Using where
1261
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-00-00';
1262
id select_type table partitions type possible_keys key key_len ref rows Extra
1263
1 SIMPLE t1 pNULL ALL NULL NULL NULL NULL 3 Using where
1264
# Disabling warnings for the invalid date
1265
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1999-02-31';
1266
id select_type table partitions type possible_keys key key_len ref rows Extra
1267
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 ALL NULL NULL NULL NULL 6 Using where
1268
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1999-02-31';
1269
id select_type table partitions type possible_keys key key_len ref rows Extra
1270
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 ALL NULL NULL NULL NULL 6 Using where
1271
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1999-02-31';
1272
id select_type table partitions type possible_keys key key_len ref rows Extra
1273
1 SIMPLE t1 p2001-01-01,pNULL ALL NULL NULL NULL NULL 4 Using where
1274
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1999-02-31';
1275
id select_type table partitions type possible_keys key key_len ref rows Extra
1276
1 SIMPLE t1 p2001-01-01,pNULL ALL NULL NULL NULL NULL 4 Using where
1277
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1999-02-31';
1278
id select_type table partitions type possible_keys key key_len ref rows Extra
1279
1 SIMPLE t1 pNULL ALL NULL NULL NULL NULL 3 Using where
1280
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00';
1281
id select_type table partitions type possible_keys key key_len ref rows Extra
1282
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 ALL NULL NULL NULL NULL 6 Using where
1283
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01';
1284
id select_type table partitions type possible_keys key key_len ref rows Extra
1285
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 ALL NULL NULL NULL NULL 6 Using where
1286
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00';
1287
id select_type table partitions type possible_keys key key_len ref rows Extra
1288
1 SIMPLE t1 pNULL,p1001-01-01 ALL NULL NULL NULL NULL 4 Using where
1289
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01';
1290
id select_type table partitions type possible_keys key key_len ref rows Extra
1291
1 SIMPLE t1 p0001-01-01,pNULL,p1001-01-01 ALL NULL NULL NULL NULL 5 Using where
1293
# TO_SECONDS, test of LIST and index
1294
CREATE TABLE t1 (a DATE, KEY(a))
1295
PARTITION BY LIST (TO_SECONDS(a))
1296
(PARTITION `p0001-01-01` VALUES IN (TO_SECONDS('0001-01-01')),
1297
PARTITION `p2001-01-01` VALUES IN (TO_SECONDS('2001-01-01')),
1298
PARTITION `pNULL` VALUES IN (NULL),
1299
PARTITION `p0000-01-02` VALUES IN (TO_SECONDS('0000-01-02')),
1300
PARTITION `p1001-01-01` VALUES IN (TO_SECONDS('1001-01-01')));
1301
INSERT INTO t1 VALUES ('0000-00-00'), ('0000-01-02'), ('0001-01-01'),
1302
('1001-00-00'), ('1001-01-01'), ('1002-00-00'), ('2001-01-01');
1303
SELECT * FROM t1 WHERE a < '1001-01-01';
1309
SELECT * FROM t1 WHERE a <= '1001-01-01';
1316
SELECT * FROM t1 WHERE a >= '1001-01-01';
1321
SELECT * FROM t1 WHERE a > '1001-01-01';
1325
SELECT * FROM t1 WHERE a = '1001-01-01';
1328
SELECT * FROM t1 WHERE a < '1001-00-00';
1333
SELECT * FROM t1 WHERE a <= '1001-00-00';
1339
SELECT * FROM t1 WHERE a >= '1001-00-00';
1345
SELECT * FROM t1 WHERE a > '1001-00-00';
1350
SELECT * FROM t1 WHERE a = '1001-00-00';
1353
# Disabling warnings for the invalid date
1354
SELECT * FROM t1 WHERE a < '1999-02-31';
1362
SELECT * FROM t1 WHERE a <= '1999-02-31';
1370
SELECT * FROM t1 WHERE a >= '1999-02-31';
1373
SELECT * FROM t1 WHERE a > '1999-02-31';
1376
SELECT * FROM t1 WHERE a = '1999-02-31';
1378
SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00';
1386
SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01';
1393
SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00';
1398
SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01';
1403
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-01-01';
1404
id select_type table partitions type possible_keys key key_len ref rows Extra
1405
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 range a a 4 NULL 3 Using where; Using index
1406
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-01-01';
1407
id select_type table partitions type possible_keys key key_len ref rows Extra
1408
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 3 Using where; Using index
1409
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-01-01';
1410
id select_type table partitions type possible_keys key key_len ref rows Extra
1411
1 SIMPLE t1 p2001-01-01,pNULL,p1001-01-01 range a a 4 NULL 4 Using where; Using index
1412
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-01-01';
1413
id select_type table partitions type possible_keys key key_len ref rows Extra
1414
1 SIMPLE t1 p2001-01-01,pNULL range a a 4 NULL 3 Using where; Using index
1415
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-01-01';
1416
id select_type table partitions type possible_keys key key_len ref rows Extra
1417
1 SIMPLE t1 p1001-01-01 system a NULL NULL NULL 1 NULL
1418
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-00-00';
1419
id select_type table partitions type possible_keys key key_len ref rows Extra
1420
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 range a a 4 NULL 3 Using where; Using index
1421
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-00-00';
1422
id select_type table partitions type possible_keys key key_len ref rows Extra
1423
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 range a a 4 NULL 3 Using where; Using index
1424
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-00-00';
1425
id select_type table partitions type possible_keys key key_len ref rows Extra
1426
1 SIMPLE t1 p2001-01-01,pNULL,p1001-01-01 range a a 4 NULL 4 Using where; Using index
1427
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-00-00';
1428
id select_type table partitions type possible_keys key key_len ref rows Extra
1429
1 SIMPLE t1 p2001-01-01,pNULL,p1001-01-01 range a a 4 NULL 4 Using where; Using index
1430
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-00-00';
1431
id select_type table partitions type possible_keys key key_len ref rows Extra
1432
1 SIMPLE t1 pNULL ref a a 4 const 1 Using index
1433
# Disabling warnings for the invalid date
1434
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1999-02-31';
1435
id select_type table partitions type possible_keys key key_len ref rows Extra
1436
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index
1437
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1999-02-31';
1438
id select_type table partitions type possible_keys key key_len ref rows Extra
1439
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index
1440
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1999-02-31';
1441
id select_type table partitions type possible_keys key key_len ref rows Extra
1442
1 SIMPLE t1 p2001-01-01,pNULL index a a 4 NULL 4 Using where; Using index
1443
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1999-02-31';
1444
id select_type table partitions type possible_keys key key_len ref rows Extra
1445
1 SIMPLE t1 p2001-01-01,pNULL index a a 4 NULL 4 Using where; Using index
1446
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1999-02-31';
1447
id select_type table partitions type possible_keys key key_len ref rows Extra
1448
1 SIMPLE t1 pNULL ref a a 4 const 1 Using where; Using index
1449
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00';
1450
id select_type table partitions type possible_keys key key_len ref rows Extra
1451
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index
1452
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01';
1453
id select_type table partitions type possible_keys key key_len ref rows Extra
1454
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 3 Using where; Using index
1455
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00';
1456
id select_type table partitions type possible_keys key key_len ref rows Extra
1457
1 SIMPLE t1 pNULL,p1001-01-01 index a a 4 NULL 4 Using where; Using index
1458
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01';
1459
id select_type table partitions type possible_keys key key_len ref rows Extra
1460
1 SIMPLE t1 p0001-01-01,pNULL,p1001-01-01 range a a 4 NULL 3 Using where; Using index
1461
# test without index
1462
ALTER TABLE t1 DROP KEY a;
1463
SELECT * FROM t1 WHERE a < '1001-01-01';
1469
SELECT * FROM t1 WHERE a <= '1001-01-01';
1476
SELECT * FROM t1 WHERE a >= '1001-01-01';
1481
SELECT * FROM t1 WHERE a > '1001-01-01';
1485
SELECT * FROM t1 WHERE a = '1001-01-01';
1488
SELECT * FROM t1 WHERE a < '1001-00-00';
1493
SELECT * FROM t1 WHERE a <= '1001-00-00';
1499
SELECT * FROM t1 WHERE a >= '1001-00-00';
1505
SELECT * FROM t1 WHERE a > '1001-00-00';
1510
SELECT * FROM t1 WHERE a = '1001-00-00';
1513
# Disabling warnings for the invalid date
1514
SELECT * FROM t1 WHERE a < '1999-02-31';
1522
SELECT * FROM t1 WHERE a <= '1999-02-31';
1530
SELECT * FROM t1 WHERE a >= '1999-02-31';
1533
SELECT * FROM t1 WHERE a > '1999-02-31';
1536
SELECT * FROM t1 WHERE a = '1999-02-31';
1538
SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00';
1546
SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01';
1553
SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00';
1558
SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01';
1563
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-01-01';
1564
id select_type table partitions type possible_keys key key_len ref rows Extra
1565
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 ALL NULL NULL NULL NULL 5 Using where
1566
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-01-01';
1567
id select_type table partitions type possible_keys key key_len ref rows Extra
1568
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 ALL NULL NULL NULL NULL 6 Using where
1569
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-01-01';
1570
id select_type table partitions type possible_keys key key_len ref rows Extra
1571
1 SIMPLE t1 p2001-01-01,pNULL,p1001-01-01 ALL NULL NULL NULL NULL 5 Using where
1572
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-01-01';
1573
id select_type table partitions type possible_keys key key_len ref rows Extra
1574
1 SIMPLE t1 p2001-01-01,pNULL ALL NULL NULL NULL NULL 4 Using where
1575
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-01-01';
1576
id select_type table partitions type possible_keys key key_len ref rows Extra
1577
1 SIMPLE t1 p1001-01-01 system NULL NULL NULL NULL 1 NULL
1578
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-00-00';
1579
id select_type table partitions type possible_keys key key_len ref rows Extra
1580
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 ALL NULL NULL NULL NULL 5 Using where
1581
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-00-00';
1582
id select_type table partitions type possible_keys key key_len ref rows Extra
1583
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 ALL NULL NULL NULL NULL 5 Using where
1584
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-00-00';
1585
id select_type table partitions type possible_keys key key_len ref rows Extra
1586
1 SIMPLE t1 p2001-01-01,pNULL,p1001-01-01 ALL NULL NULL NULL NULL 5 Using where
1587
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-00-00';
1588
id select_type table partitions type possible_keys key key_len ref rows Extra
1589
1 SIMPLE t1 p2001-01-01,pNULL,p1001-01-01 ALL NULL NULL NULL NULL 5 Using where
1590
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-00-00';
1591
id select_type table partitions type possible_keys key key_len ref rows Extra
1592
1 SIMPLE t1 pNULL ALL NULL NULL NULL NULL 3 Using where
1593
# Disabling warnings for the invalid date
1594
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1999-02-31';
1595
id select_type table partitions type possible_keys key key_len ref rows Extra
1596
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 ALL NULL NULL NULL NULL 6 Using where
1597
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1999-02-31';
1598
id select_type table partitions type possible_keys key key_len ref rows Extra
1599
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 ALL NULL NULL NULL NULL 6 Using where
1600
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1999-02-31';
1601
id select_type table partitions type possible_keys key key_len ref rows Extra
1602
1 SIMPLE t1 p2001-01-01,pNULL ALL NULL NULL NULL NULL 4 Using where
1603
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1999-02-31';
1604
id select_type table partitions type possible_keys key key_len ref rows Extra
1605
1 SIMPLE t1 p2001-01-01,pNULL ALL NULL NULL NULL NULL 4 Using where
1606
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1999-02-31';
1607
id select_type table partitions type possible_keys key key_len ref rows Extra
1608
1 SIMPLE t1 pNULL ALL NULL NULL NULL NULL 3 Using where
1609
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00';
1610
id select_type table partitions type possible_keys key key_len ref rows Extra
1611
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 ALL NULL NULL NULL NULL 6 Using where
1612
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01';
1613
id select_type table partitions type possible_keys key key_len ref rows Extra
1614
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 ALL NULL NULL NULL NULL 6 Using where
1615
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00';
1616
id select_type table partitions type possible_keys key key_len ref rows Extra
1617
1 SIMPLE t1 pNULL,p1001-01-01 ALL NULL NULL NULL NULL 4 Using where
1618
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01';
1619
id select_type table partitions type possible_keys key key_len ref rows Extra
1620
1 SIMPLE t1 p0001-01-01,pNULL,p1001-01-01 ALL NULL NULL NULL NULL 5 Using where
1622
# Test with DATETIME column NOT NULL
1624
a int(10) unsigned NOT NULL,
1625
b DATETIME NOT NULL,
1627
) PARTITION BY RANGE (TO_DAYS(b))
1628
(PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')),
1629
PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')),
1630
PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')),
1631
PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')),
1632
PARTITION p20090405 VALUES LESS THAN MAXVALUE);
1633
INSERT INTO t1 VALUES (1, '2009-01-01'), (1, '2009-04-01'), (2, '2009-04-01'),
1634
(1, '2009-04-02'), (2, '2009-04-02'), (1, '2009-04-02 23:59:59'),
1635
(1, '2009-04-03'), (2, '2009-04-03'), (1, '2009-04-04'), (2, '2009-04-04'),
1636
(1, '2009-04-05'), (1, '2009-04-06'), (1, '2009-04-07');
1637
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATETIME);
1638
id select_type table partitions type possible_keys key key_len ref rows Extra
1639
1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 9 NULL 6 Using where; Using index
1640
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATETIME);
1641
id select_type table partitions type possible_keys key key_len ref rows Extra
1642
1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 9 NULL 8 Using where; Using index
1643
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME);
1644
id select_type table partitions type possible_keys key key_len ref rows Extra
1645
1 SIMPLE t1 p20090403 index NULL PRIMARY 9 NULL 2 Using where; Using index
1646
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME);
1647
id select_type table partitions type possible_keys key key_len ref rows Extra
1648
1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 9 NULL 10 Using where; Using index
1649
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME);
1650
id select_type table partitions type possible_keys key key_len ref rows Extra
1651
1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 9 NULL 10 Using where; Using index
1652
EXPLAIN PARTITIONS SELECT * FROM t1
1653
WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME);
1654
id select_type table partitions type possible_keys key key_len ref rows Extra
1655
1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 9 NULL 6 Using where; Using index
1656
EXPLAIN PARTITIONS SELECT * FROM t1
1657
WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME);
1658
id select_type table partitions type possible_keys key key_len ref rows Extra
1659
1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 9 NULL 6 Using where; Using index
1660
EXPLAIN PARTITIONS SELECT * FROM t1
1661
WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME);
1662
id select_type table partitions type possible_keys key key_len ref rows Extra
1663
1 SIMPLE t1 p20090402 index NULL PRIMARY 9 NULL 3 Using where; Using index
1664
EXPLAIN PARTITIONS SELECT * FROM t1
1665
WHERE b >= CAST('2009-04-02 23:59:59' AS DATETIME);
1666
id select_type table partitions type possible_keys key key_len ref rows Extra
1667
1 SIMPLE t1 p20090401,p20090402,p20090403,p20090404,p20090405 index NULL PRIMARY 9 NULL 13 Using where; Using index
1668
EXPLAIN PARTITIONS SELECT * FROM t1
1669
WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME);
1670
id select_type table partitions type possible_keys key key_len ref rows Extra
1671
1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 9 NULL 10 Using where; Using index
1672
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE);
1673
id select_type table partitions type possible_keys key key_len ref rows Extra
1674
1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 9 NULL 6 Using where; Using index
1675
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE);
1676
id select_type table partitions type possible_keys key key_len ref rows Extra
1677
1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 9 NULL 8 Using where; Using index
1678
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE);
1679
id select_type table partitions type possible_keys key key_len ref rows Extra
1680
1 SIMPLE t1 p20090403 index NULL PRIMARY 9 NULL 2 Using where; Using index
1681
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE);
1682
id select_type table partitions type possible_keys key key_len ref rows Extra
1683
1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 9 NULL 10 Using where; Using index
1684
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE);
1685
id select_type table partitions type possible_keys key key_len ref rows Extra
1686
1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 9 NULL 10 Using where; Using index
1687
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00';
1688
id select_type table partitions type possible_keys key key_len ref rows Extra
1689
1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 9 NULL 6 Using where; Using index
1690
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00';
1691
id select_type table partitions type possible_keys key key_len ref rows Extra
1692
1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 9 NULL 8 Using where; Using index
1693
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00';
1694
id select_type table partitions type possible_keys key key_len ref rows Extra
1695
1 SIMPLE t1 p20090403 index NULL PRIMARY 9 NULL 2 Using where; Using index
1696
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00';
1697
id select_type table partitions type possible_keys key key_len ref rows Extra
1698
1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 9 NULL 10 Using where; Using index
1699
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00';
1700
id select_type table partitions type possible_keys key key_len ref rows Extra
1701
1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 9 NULL 10 Using where; Using index
1702
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59';
1703
id select_type table partitions type possible_keys key key_len ref rows Extra
1704
1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 9 NULL 6 Using where; Using index
1705
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59';
1706
id select_type table partitions type possible_keys key key_len ref rows Extra
1707
1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 9 NULL 6 Using where; Using index
1708
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59';
1709
id select_type table partitions type possible_keys key key_len ref rows Extra
1710
1 SIMPLE t1 p20090402 index NULL PRIMARY 9 NULL 3 Using where; Using index
1711
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-02 23:59:59';
1712
id select_type table partitions type possible_keys key key_len ref rows Extra
1713
1 SIMPLE t1 p20090401,p20090402,p20090403,p20090404,p20090405 index NULL PRIMARY 9 NULL 13 Using where; Using index
1714
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59';
1715
id select_type table partitions type possible_keys key key_len ref rows Extra
1716
1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 9 NULL 10 Using where; Using index
1717
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03';
1718
id select_type table partitions type possible_keys key key_len ref rows Extra
1719
1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 9 NULL 6 Using where; Using index
1720
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03';
1721
id select_type table partitions type possible_keys key key_len ref rows Extra
1722
1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 9 NULL 8 Using where; Using index
1723
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03';
1724
id select_type table partitions type possible_keys key key_len ref rows Extra
1725
1 SIMPLE t1 p20090403 index NULL PRIMARY 9 NULL 2 Using where; Using index
1726
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03';
1727
id select_type table partitions type possible_keys key key_len ref rows Extra
1728
1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 9 NULL 10 Using where; Using index
1729
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03';
1730
id select_type table partitions type possible_keys key key_len ref rows Extra
1731
1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 9 NULL 10 Using where; Using index
1732
EXPLAIN PARTITIONS SELECT * FROM t1
1733
WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME);
1734
id select_type table partitions type possible_keys key key_len ref rows Extra
1735
1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 9 NULL 8 Using where; Using index
1736
EXPLAIN PARTITIONS SELECT * FROM t1
1737
WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME);
1738
id select_type table partitions type possible_keys key key_len ref rows Extra
1739
1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 9 NULL 8 Using where; Using index
1740
EXPLAIN PARTITIONS SELECT * FROM t1
1741
WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME);
1742
id select_type table partitions type possible_keys key key_len ref rows Extra
1743
1 SIMPLE t1 p20090403 index NULL PRIMARY 9 NULL 2 Using where; Using index
1744
EXPLAIN PARTITIONS SELECT * FROM t1
1745
WHERE b >= CAST('2009-04-03 00:00:01' AS DATETIME);
1746
id select_type table partitions type possible_keys key key_len ref rows Extra
1747
1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 9 NULL 10 Using where; Using index
1748
EXPLAIN PARTITIONS SELECT * FROM t1
1749
WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME);
1750
id select_type table partitions type possible_keys key key_len ref rows Extra
1751
1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 9 NULL 10 Using where; Using index
1752
EXPLAIN PARTITIONS SELECT * FROM t1
1753
WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME);
1754
id select_type table partitions type possible_keys key key_len ref rows Extra
1755
1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 9 NULL 6 Using where; Using index
1756
EXPLAIN PARTITIONS SELECT * FROM t1
1757
WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME);
1758
id select_type table partitions type possible_keys key key_len ref rows Extra
1759
1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 9 NULL 6 Using where; Using index
1760
EXPLAIN PARTITIONS SELECT * FROM t1
1761
WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME);
1762
id select_type table partitions type possible_keys key key_len ref rows Extra
1763
1 SIMPLE t1 p20090402 index NULL PRIMARY 9 NULL 3 Using where; Using index
1764
EXPLAIN PARTITIONS SELECT * FROM t1
1765
WHERE b >= CAST('2009-04-02 23:59:58' AS DATETIME);
1766
id select_type table partitions type possible_keys key key_len ref rows Extra
1767
1 SIMPLE t1 p20090401,p20090402,p20090403,p20090404,p20090405 index NULL PRIMARY 9 NULL 13 Using where; Using index
1768
EXPLAIN PARTITIONS SELECT * FROM t1
1769
WHERE b > CAST('2009-04-02 23:59:58' AS DATETIME);
1770
id select_type table partitions type possible_keys key key_len ref rows Extra
1771
1 SIMPLE t1 p20090401,p20090402,p20090403,p20090404,p20090405 index NULL PRIMARY 9 NULL 13 Using where; Using index
1773
# Test with DATE column NOT NULL
1775
a int(10) unsigned NOT NULL,
1778
) PARTITION BY RANGE (TO_DAYS(b))
1779
(PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')),
1780
PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')),
1781
PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')),
1782
PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')),
1783
PARTITION p20090405 VALUES LESS THAN MAXVALUE);
1784
INSERT INTO t1 VALUES (1, '2009-01-01'), (1, '2009-04-01'), (2, '2009-04-01'),
1785
(1, '2009-04-02'), (2, '2009-04-02'), (1, '2009-04-03'), (2, '2009-04-03'),
1786
(1, '2009-04-04'), (2, '2009-04-04'), (1, '2009-04-05'), (1, '2009-04-06'),
1788
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATETIME);
1789
id select_type table partitions type possible_keys key key_len ref rows Extra
1790
1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 5 Using where; Using index
1791
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATETIME);
1792
id select_type table partitions type possible_keys key key_len ref rows Extra
1793
1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 7 NULL 7 Using where; Using index
1794
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME);
1795
id select_type table partitions type possible_keys key key_len ref rows Extra
1796
1 SIMPLE t1 p20090403 index NULL PRIMARY 7 NULL 2 Using where; Using index
1797
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME);
1798
id select_type table partitions type possible_keys key key_len ref rows Extra
1799
1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 10 Using where; Using index
1800
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME);
1801
id select_type table partitions type possible_keys key key_len ref rows Extra
1802
1 SIMPLE t1 p20090401,p20090404,p20090405 index NULL PRIMARY 7 NULL 8 Using where; Using index
1803
EXPLAIN PARTITIONS SELECT * FROM t1
1804
WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME);
1805
id select_type table partitions type possible_keys key key_len ref rows Extra
1806
1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 5 Using where; Using index
1807
EXPLAIN PARTITIONS SELECT * FROM t1
1808
WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME);
1809
id select_type table partitions type possible_keys key key_len ref rows Extra
1810
1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 5 Using where; Using index
1811
EXPLAIN PARTITIONS SELECT * FROM t1
1812
WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME);
1813
id select_type table partitions type possible_keys key key_len ref rows Extra
1814
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
1815
EXPLAIN PARTITIONS SELECT * FROM t1
1816
WHERE b >= CAST('2009-04-02 23:59:59' AS DATETIME);
1817
id select_type table partitions type possible_keys key key_len ref rows Extra
1818
1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 10 Using where; Using index
1819
EXPLAIN PARTITIONS SELECT * FROM t1
1820
WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME);
1821
id select_type table partitions type possible_keys key key_len ref rows Extra
1822
1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 10 Using where; Using index
1823
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE);
1824
id select_type table partitions type possible_keys key key_len ref rows Extra
1825
1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 5 Using where; Using index
1826
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE);
1827
id select_type table partitions type possible_keys key key_len ref rows Extra
1828
1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 7 NULL 7 Using where; Using index
1829
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE);
1830
id select_type table partitions type possible_keys key key_len ref rows Extra
1831
1 SIMPLE t1 p20090403 index NULL PRIMARY 7 NULL 2 Using where; Using index
1832
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE);
1833
id select_type table partitions type possible_keys key key_len ref rows Extra
1834
1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 10 Using where; Using index
1835
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE);
1836
id select_type table partitions type possible_keys key key_len ref rows Extra
1837
1 SIMPLE t1 p20090401,p20090404,p20090405 index NULL PRIMARY 7 NULL 8 Using where; Using index
1838
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00';
1839
id select_type table partitions type possible_keys key key_len ref rows Extra
1840
1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 5 Using where; Using index
1841
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00';
1842
id select_type table partitions type possible_keys key key_len ref rows Extra
1843
1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 7 NULL 7 Using where; Using index
1844
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00';
1845
id select_type table partitions type possible_keys key key_len ref rows Extra
1846
1 SIMPLE t1 p20090403 index NULL PRIMARY 7 NULL 2 Using where; Using index
1847
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00';
1848
id select_type table partitions type possible_keys key key_len ref rows Extra
1849
1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 10 Using where; Using index
1850
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00';
1851
id select_type table partitions type possible_keys key key_len ref rows Extra
1852
1 SIMPLE t1 p20090401,p20090404,p20090405 index NULL PRIMARY 7 NULL 8 Using where; Using index
1853
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59';
1854
id select_type table partitions type possible_keys key key_len ref rows Extra
1855
1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 5 Using where; Using index
1856
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59';
1857
id select_type table partitions type possible_keys key key_len ref rows Extra
1858
1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 5 Using where; Using index
1859
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59';
1860
id select_type table partitions type possible_keys key key_len ref rows Extra
1861
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
1862
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-02 23:59:59';
1863
id select_type table partitions type possible_keys key key_len ref rows Extra
1864
1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 10 Using where; Using index
1865
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59';
1866
id select_type table partitions type possible_keys key key_len ref rows Extra
1867
1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 10 Using where; Using index
1868
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03';
1869
id select_type table partitions type possible_keys key key_len ref rows Extra
1870
1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 5 Using where; Using index
1871
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03';
1872
id select_type table partitions type possible_keys key key_len ref rows Extra
1873
1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 7 NULL 7 Using where; Using index
1874
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03';
1875
id select_type table partitions type possible_keys key key_len ref rows Extra
1876
1 SIMPLE t1 p20090403 index NULL PRIMARY 7 NULL 2 Using where; Using index
1877
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03';
1878
id select_type table partitions type possible_keys key key_len ref rows Extra
1879
1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 10 Using where; Using index
1880
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03';
1881
id select_type table partitions type possible_keys key key_len ref rows Extra
1882
1 SIMPLE t1 p20090401,p20090404,p20090405 index NULL PRIMARY 7 NULL 8 Using where; Using index
1883
EXPLAIN PARTITIONS SELECT * FROM t1
1884
WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME);
1885
id select_type table partitions type possible_keys key key_len ref rows Extra
1886
1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 7 NULL 7 Using where; Using index
1887
EXPLAIN PARTITIONS SELECT * FROM t1
1888
WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME);
1889
id select_type table partitions type possible_keys key key_len ref rows Extra
1890
1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 7 NULL 7 Using where; Using index
1891
EXPLAIN PARTITIONS SELECT * FROM t1
1892
WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME);
1893
id select_type table partitions type possible_keys key key_len ref rows Extra
1894
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
1895
EXPLAIN PARTITIONS SELECT * FROM t1
1896
WHERE b >= CAST('2009-04-03 00:00:01' AS DATETIME);
1897
id select_type table partitions type possible_keys key key_len ref rows Extra
1898
1 SIMPLE t1 p20090401,p20090404,p20090405 index NULL PRIMARY 7 NULL 8 Using where; Using index
1899
EXPLAIN PARTITIONS SELECT * FROM t1
1900
WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME);
1901
id select_type table partitions type possible_keys key key_len ref rows Extra
1902
1 SIMPLE t1 p20090401,p20090404,p20090405 index NULL PRIMARY 7 NULL 8 Using where; Using index
1903
EXPLAIN PARTITIONS SELECT * FROM t1
1904
WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME);
1905
id select_type table partitions type possible_keys key key_len ref rows Extra
1906
1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 5 Using where; Using index
1907
EXPLAIN PARTITIONS SELECT * FROM t1
1908
WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME);
1909
id select_type table partitions type possible_keys key key_len ref rows Extra
1910
1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 5 Using where; Using index
1911
EXPLAIN PARTITIONS SELECT * FROM t1
1912
WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME);
1913
id select_type table partitions type possible_keys key key_len ref rows Extra
1914
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
1915
EXPLAIN PARTITIONS SELECT * FROM t1
1916
WHERE b >= CAST('2009-04-02 23:59:58' AS DATETIME);
1917
id select_type table partitions type possible_keys key key_len ref rows Extra
1918
1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 10 Using where; Using index
1919
EXPLAIN PARTITIONS SELECT * FROM t1
1920
WHERE b > CAST('2009-04-02 23:59:58' AS DATETIME);
1921
id select_type table partitions type possible_keys key key_len ref rows Extra
1922
1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 10 Using where; Using index
1924
# Test with DATETIME column NULL
1926
a int(10) unsigned NOT NULL,
1928
) PARTITION BY RANGE (TO_DAYS(b))
1929
(PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')),
1930
PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')),
1931
PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')),
1932
PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')),
1933
PARTITION p20090405 VALUES LESS THAN MAXVALUE);
1934
INSERT INTO t1 VALUES (1, '2009-01-01'), (1, '2009-04-01'), (2, '2009-04-01'),
1935
(1, '2009-04-02'), (2, '2009-04-02'), (1, '2009-04-02 23:59:59'),
1936
(1, '2009-04-03'), (2, '2009-04-03'), (1, '2009-04-04'), (2, '2009-04-04'),
1937
(1, '2009-04-05'), (1, '2009-04-06'), (1, '2009-04-07');
1938
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATETIME);
1939
id select_type table partitions type possible_keys key key_len ref rows Extra
1940
1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 6 Using where
1941
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATETIME);
1942
id select_type table partitions type possible_keys key key_len ref rows Extra
1943
1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 8 Using where
1944
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME);
1945
id select_type table partitions type possible_keys key key_len ref rows Extra
1946
1 SIMPLE t1 p20090403 ALL NULL NULL NULL NULL 2 Using where
1947
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME);
1948
id select_type table partitions type possible_keys key key_len ref rows Extra
1949
1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where
1950
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME);
1951
id select_type table partitions type possible_keys key key_len ref rows Extra
1952
1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where
1953
EXPLAIN PARTITIONS SELECT * FROM t1
1954
WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME);
1955
id select_type table partitions type possible_keys key key_len ref rows Extra
1956
1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 6 Using where
1957
EXPLAIN PARTITIONS SELECT * FROM t1
1958
WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME);
1959
id select_type table partitions type possible_keys key key_len ref rows Extra
1960
1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 6 Using where
1961
EXPLAIN PARTITIONS SELECT * FROM t1
1962
WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME);
1963
id select_type table partitions type possible_keys key key_len ref rows Extra
1964
1 SIMPLE t1 p20090402 ALL NULL NULL NULL NULL 3 Using where
1965
EXPLAIN PARTITIONS SELECT * FROM t1
1966
WHERE b >= CAST('2009-04-02 23:59:59' AS DATETIME);
1967
id select_type table partitions type possible_keys key key_len ref rows Extra
1968
1 SIMPLE t1 p20090401,p20090402,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 13 Using where
1969
EXPLAIN PARTITIONS SELECT * FROM t1
1970
WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME);
1971
id select_type table partitions type possible_keys key key_len ref rows Extra
1972
1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where
1973
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE);
1974
id select_type table partitions type possible_keys key key_len ref rows Extra
1975
1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 6 Using where
1976
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE);
1977
id select_type table partitions type possible_keys key key_len ref rows Extra
1978
1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 8 Using where
1979
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE);
1980
id select_type table partitions type possible_keys key key_len ref rows Extra
1981
1 SIMPLE t1 p20090403 ALL NULL NULL NULL NULL 2 Using where
1982
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE);
1983
id select_type table partitions type possible_keys key key_len ref rows Extra
1984
1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where
1985
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE);
1986
id select_type table partitions type possible_keys key key_len ref rows Extra
1987
1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where
1988
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00';
1989
id select_type table partitions type possible_keys key key_len ref rows Extra
1990
1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 6 Using where
1991
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00';
1992
id select_type table partitions type possible_keys key key_len ref rows Extra
1993
1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 8 Using where
1994
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00';
1995
id select_type table partitions type possible_keys key key_len ref rows Extra
1996
1 SIMPLE t1 p20090403 ALL NULL NULL NULL NULL 2 Using where
1997
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00';
1998
id select_type table partitions type possible_keys key key_len ref rows Extra
1999
1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where
2000
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00';
2001
id select_type table partitions type possible_keys key key_len ref rows Extra
2002
1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where
2003
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59';
2004
id select_type table partitions type possible_keys key key_len ref rows Extra
2005
1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 6 Using where
2006
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59';
2007
id select_type table partitions type possible_keys key key_len ref rows Extra
2008
1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 6 Using where
2009
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59';
2010
id select_type table partitions type possible_keys key key_len ref rows Extra
2011
1 SIMPLE t1 p20090402 ALL NULL NULL NULL NULL 3 Using where
2012
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-02 23:59:59';
2013
id select_type table partitions type possible_keys key key_len ref rows Extra
2014
1 SIMPLE t1 p20090401,p20090402,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 13 Using where
2015
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59';
2016
id select_type table partitions type possible_keys key key_len ref rows Extra
2017
1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where
2018
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03';
2019
id select_type table partitions type possible_keys key key_len ref rows Extra
2020
1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 6 Using where
2021
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03';
2022
id select_type table partitions type possible_keys key key_len ref rows Extra
2023
1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 8 Using where
2024
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03';
2025
id select_type table partitions type possible_keys key key_len ref rows Extra
2026
1 SIMPLE t1 p20090403 ALL NULL NULL NULL NULL 2 Using where
2027
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03';
2028
id select_type table partitions type possible_keys key key_len ref rows Extra
2029
1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where
2030
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03';
2031
id select_type table partitions type possible_keys key key_len ref rows Extra
2032
1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where
2033
EXPLAIN PARTITIONS SELECT * FROM t1
2034
WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME);
2035
id select_type table partitions type possible_keys key key_len ref rows Extra
2036
1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 8 Using where
2037
EXPLAIN PARTITIONS SELECT * FROM t1
2038
WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME);
2039
id select_type table partitions type possible_keys key key_len ref rows Extra
2040
1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 8 Using where
2041
EXPLAIN PARTITIONS SELECT * FROM t1
2042
WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME);
2043
id select_type table partitions type possible_keys key key_len ref rows Extra
2044
1 SIMPLE t1 p20090403 ALL NULL NULL NULL NULL 2 Using where
2045
EXPLAIN PARTITIONS SELECT * FROM t1
2046
WHERE b >= CAST('2009-04-03 00:00:01' AS DATETIME);
2047
id select_type table partitions type possible_keys key key_len ref rows Extra
2048
1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where
2049
EXPLAIN PARTITIONS SELECT * FROM t1
2050
WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME);
2051
id select_type table partitions type possible_keys key key_len ref rows Extra
2052
1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where
2053
EXPLAIN PARTITIONS SELECT * FROM t1
2054
WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME);
2055
id select_type table partitions type possible_keys key key_len ref rows Extra
2056
1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 6 Using where
2057
EXPLAIN PARTITIONS SELECT * FROM t1
2058
WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME);
2059
id select_type table partitions type possible_keys key key_len ref rows Extra
2060
1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 6 Using where
2061
EXPLAIN PARTITIONS SELECT * FROM t1
2062
WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME);
2063
id select_type table partitions type possible_keys key key_len ref rows Extra
2064
1 SIMPLE t1 p20090402 ALL NULL NULL NULL NULL 3 Using where
2065
EXPLAIN PARTITIONS SELECT * FROM t1
2066
WHERE b >= CAST('2009-04-02 23:59:58' AS DATETIME);
2067
id select_type table partitions type possible_keys key key_len ref rows Extra
2068
1 SIMPLE t1 p20090401,p20090402,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 13 Using where
2069
EXPLAIN PARTITIONS SELECT * FROM t1
2070
WHERE b > CAST('2009-04-02 23:59:58' AS DATETIME);
2071
id select_type table partitions type possible_keys key key_len ref rows Extra
2072
1 SIMPLE t1 p20090401,p20090402,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 13 Using where
2074
# Test with DATE column NULL
2076
a int(10) unsigned NOT NULL,
2078
) PARTITION BY RANGE (TO_DAYS(b))
2079
(PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')),
2080
PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')),
2081
PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')),
2082
PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')),
2083
PARTITION p20090405 VALUES LESS THAN MAXVALUE);
2084
INSERT INTO t1 VALUES (1, '2009-01-01'), (1, '2009-04-01'), (2, '2009-04-01'),
2085
(1, '2009-04-02'), (2, '2009-04-02'), (1, '2009-04-03'), (2, '2009-04-03'),
2086
(1, '2009-04-04'), (2, '2009-04-04'), (1, '2009-04-05'), (1, '2009-04-06'),
2088
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATETIME);
2089
id select_type table partitions type possible_keys key key_len ref rows Extra
2090
1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 5 Using where
2091
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATETIME);
2092
id select_type table partitions type possible_keys key key_len ref rows Extra
2093
1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 7 Using where
2094
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME);
2095
id select_type table partitions type possible_keys key key_len ref rows Extra
2096
1 SIMPLE t1 p20090403 ALL NULL NULL NULL NULL 2 Using where
2097
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME);
2098
id select_type table partitions type possible_keys key key_len ref rows Extra
2099
1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where
2100
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME);
2101
id select_type table partitions type possible_keys key key_len ref rows Extra
2102
1 SIMPLE t1 p20090401,p20090404,p20090405 ALL NULL NULL NULL NULL 8 Using where
2103
EXPLAIN PARTITIONS SELECT * FROM t1
2104
WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME);
2105
id select_type table partitions type possible_keys key key_len ref rows Extra
2106
1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 5 Using where
2107
EXPLAIN PARTITIONS SELECT * FROM t1
2108
WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME);
2109
id select_type table partitions type possible_keys key key_len ref rows Extra
2110
1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 5 Using where
2111
EXPLAIN PARTITIONS SELECT * FROM t1
2112
WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME);
2113
id select_type table partitions type possible_keys key key_len ref rows Extra
2114
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2115
EXPLAIN PARTITIONS SELECT * FROM t1
2116
WHERE b >= CAST('2009-04-02 23:59:59' AS DATETIME);
2117
id select_type table partitions type possible_keys key key_len ref rows Extra
2118
1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where
2119
EXPLAIN PARTITIONS SELECT * FROM t1
2120
WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME);
2121
id select_type table partitions type possible_keys key key_len ref rows Extra
2122
1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where
2123
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE);
2124
id select_type table partitions type possible_keys key key_len ref rows Extra
2125
1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 5 Using where
2126
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE);
2127
id select_type table partitions type possible_keys key key_len ref rows Extra
2128
1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 7 Using where
2129
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE);
2130
id select_type table partitions type possible_keys key key_len ref rows Extra
2131
1 SIMPLE t1 p20090403 ALL NULL NULL NULL NULL 2 Using where
2132
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE);
2133
id select_type table partitions type possible_keys key key_len ref rows Extra
2134
1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where
2135
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE);
2136
id select_type table partitions type possible_keys key key_len ref rows Extra
2137
1 SIMPLE t1 p20090401,p20090404,p20090405 ALL NULL NULL NULL NULL 8 Using where
2138
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00';
2139
id select_type table partitions type possible_keys key key_len ref rows Extra
2140
1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 5 Using where
2141
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00';
2142
id select_type table partitions type possible_keys key key_len ref rows Extra
2143
1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 7 Using where
2144
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00';
2145
id select_type table partitions type possible_keys key key_len ref rows Extra
2146
1 SIMPLE t1 p20090403 ALL NULL NULL NULL NULL 2 Using where
2147
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00';
2148
id select_type table partitions type possible_keys key key_len ref rows Extra
2149
1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where
2150
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00';
2151
id select_type table partitions type possible_keys key key_len ref rows Extra
2152
1 SIMPLE t1 p20090401,p20090404,p20090405 ALL NULL NULL NULL NULL 8 Using where
2153
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59';
2154
id select_type table partitions type possible_keys key key_len ref rows Extra
2155
1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 5 Using where
2156
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59';
2157
id select_type table partitions type possible_keys key key_len ref rows Extra
2158
1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 5 Using where
2159
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59';
2160
id select_type table partitions type possible_keys key key_len ref rows Extra
2161
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2162
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-02 23:59:59';
2163
id select_type table partitions type possible_keys key key_len ref rows Extra
2164
1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where
2165
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59';
2166
id select_type table partitions type possible_keys key key_len ref rows Extra
2167
1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where
2168
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03';
2169
id select_type table partitions type possible_keys key key_len ref rows Extra
2170
1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 5 Using where
2171
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03';
2172
id select_type table partitions type possible_keys key key_len ref rows Extra
2173
1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 7 Using where
2174
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03';
2175
id select_type table partitions type possible_keys key key_len ref rows Extra
2176
1 SIMPLE t1 p20090403 ALL NULL NULL NULL NULL 2 Using where
2177
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03';
2178
id select_type table partitions type possible_keys key key_len ref rows Extra
2179
1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where
2180
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03';
2181
id select_type table partitions type possible_keys key key_len ref rows Extra
2182
1 SIMPLE t1 p20090401,p20090404,p20090405 ALL NULL NULL NULL NULL 8 Using where
2183
EXPLAIN PARTITIONS SELECT * FROM t1
2184
WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME);
2185
id select_type table partitions type possible_keys key key_len ref rows Extra
2186
1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 7 Using where
2187
EXPLAIN PARTITIONS SELECT * FROM t1
2188
WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME);
2189
id select_type table partitions type possible_keys key key_len ref rows Extra
2190
1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 7 Using where
2191
EXPLAIN PARTITIONS SELECT * FROM t1
2192
WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME);
2193
id select_type table partitions type possible_keys key key_len ref rows Extra
2194
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2195
EXPLAIN PARTITIONS SELECT * FROM t1
2196
WHERE b >= CAST('2009-04-03 00:00:01' AS DATETIME);
2197
id select_type table partitions type possible_keys key key_len ref rows Extra
2198
1 SIMPLE t1 p20090401,p20090404,p20090405 ALL NULL NULL NULL NULL 8 Using where
2199
EXPLAIN PARTITIONS SELECT * FROM t1
2200
WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME);
2201
id select_type table partitions type possible_keys key key_len ref rows Extra
2202
1 SIMPLE t1 p20090401,p20090404,p20090405 ALL NULL NULL NULL NULL 8 Using where
2203
EXPLAIN PARTITIONS SELECT * FROM t1
2204
WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME);
2205
id select_type table partitions type possible_keys key key_len ref rows Extra
2206
1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 5 Using where
2207
EXPLAIN PARTITIONS SELECT * FROM t1
2208
WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME);
2209
id select_type table partitions type possible_keys key key_len ref rows Extra
2210
1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 5 Using where
2211
EXPLAIN PARTITIONS SELECT * FROM t1
2212
WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME);
2213
id select_type table partitions type possible_keys key key_len ref rows Extra
2214
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2215
EXPLAIN PARTITIONS SELECT * FROM t1
2216
WHERE b >= CAST('2009-04-02 23:59:58' AS DATETIME);
2217
id select_type table partitions type possible_keys key key_len ref rows Extra
2218
1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where
2219
EXPLAIN PARTITIONS SELECT * FROM t1
2220
WHERE b > CAST('2009-04-02 23:59:58' AS DATETIME);
2221
id select_type table partitions type possible_keys key key_len ref rows Extra
2222
1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where
2224
# For better code coverage of the patch
2226
a int(10) unsigned NOT NULL,
2228
) PARTITION BY RANGE ( TO_DAYS(b) )
2229
(PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')),
2230
PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')),
2231
PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')),
2232
PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')),
2233
PARTITION p20090405 VALUES LESS THAN MAXVALUE);
2234
INSERT INTO t1 VALUES (1, '2009-01-01'), (2, NULL);
2235
# test with an invalid date, which lead to item->null_value is set.
2236
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-99' AS DATETIME);
2237
id select_type table partitions type possible_keys key key_len ref rows Extra
2238
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2240
Warning 1292 Incorrect datetime value: '2009-04-99'
2243
(a INT NOT NULL AUTO_INCREMENT,
2247
PARTITION BY RANGE (to_days(b))
2248
(PARTITION p0 VALUES LESS THAN (733681) COMMENT = 'LESS THAN 2008-10-01',
2249
PARTITION p1 VALUES LESS THAN (733712) COMMENT = 'LESS THAN 2008-11-01',
2250
PARTITION pX VALUES LESS THAN MAXVALUE);
2251
SELECT a,b FROM t1 WHERE b >= '2008-12-01' AND b < '2009-12-00';
2254
create table t1 ( a int not null) partition by hash(a) partitions 2;
2255
insert into t1 values (1),(2),(3);
2256
explain select * from t1 where a=5 and a=6;
2257
id select_type table type possible_keys key key_len ref rows Extra
2258
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
2262
) partition by hash (a) partitions 2;
2263
insert into t1 values (1),(2),(3);
2264
explain partitions select * from t1 where a=1;
2265
id select_type table partitions type possible_keys key key_len ref rows Extra
2266
1 SIMPLE t1 p1 ALL NULL NULL NULL NULL 2 Using where
2267
explain partitions select * from t1 where a=2;
2268
id select_type table partitions type possible_keys key key_len ref rows Extra
2269
1 SIMPLE t1 p0 system NULL NULL NULL NULL 1 NULL
2270
explain partitions select * from t1 where a=1 or a=2;
2271
id select_type table partitions type possible_keys key key_len ref rows Extra
2272
1 SIMPLE t1 p0,p1 ALL NULL NULL NULL NULL 3 Using where
2276
) partition by key(a,b) partitions 2;
2277
insert into t2 values (1,1),(2,2),(3,3);
2278
explain partitions select * from t2 where a=1;
2279
id select_type table partitions type possible_keys key key_len ref rows Extra
2280
1 SIMPLE t2 p0,p1 ALL NULL NULL NULL NULL 3 Using where
2281
explain partitions select * from t2 where b=1;
2282
id select_type table partitions type possible_keys key key_len ref rows Extra
2283
1 SIMPLE t2 p0,p1 ALL NULL NULL NULL NULL 3 Using where
2284
explain partitions select * from t2 where a=1 and b=1;
2285
id select_type table partitions type possible_keys key key_len ref rows Extra
2286
1 SIMPLE t2 p0 ALL NULL NULL NULL NULL 2 Using where
2290
partition by range (a*1) (
2291
partition p0 values less than (10),
2292
partition p1 values less than (20)
2294
insert into t3 values (5),(15);
2295
explain partitions select * from t3 where a=11;
2296
id select_type table partitions type possible_keys key key_len ref rows Extra
2297
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2298
explain partitions select * from t3 where a=10;
2299
id select_type table partitions type possible_keys key key_len ref rows Extra
2300
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2301
explain partitions select * from t3 where a=20;
2302
id select_type table partitions type possible_keys key key_len ref rows Extra
2303
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2304
explain partitions select * from t3 where a=30;
2305
id select_type table partitions type possible_keys key key_len ref rows Extra
2306
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2307
create table t4 (a int not null, b int not null) partition by LIST (a+b) (
2308
partition p0 values in (12),
2309
partition p1 values in (14)
2311
insert into t4 values (10,2), (10,4);
2312
explain partitions select * from t4 where (a=10 and b=1) or (a=10 and b=2);
2313
id select_type table partitions type possible_keys key key_len ref rows Extra
2314
1 SIMPLE t4 p0 system NULL NULL NULL NULL 1 NULL
2315
explain partitions select * from t4
2316
where (a=10 and b=1) or (a=10 and b=2) or (a=10 and b = 3);
2317
id select_type table partitions type possible_keys key key_len ref rows Extra
2318
1 SIMPLE t4 p0 system NULL NULL NULL NULL 1 NULL
2319
explain partitions select * from t4 where (a=10 and b=2) or (a=10 and b=3)
2320
or (a=10 and b = 4);
2321
id select_type table partitions type possible_keys key key_len ref rows Extra
2322
1 SIMPLE t4 p0,p1 ALL NULL NULL NULL NULL 2 Using where
2323
explain partitions select * from t4 where (a=10 and b=1) or a=11;
2324
id select_type table partitions type possible_keys key key_len ref rows Extra
2325
1 SIMPLE t4 p0,p1 ALL NULL NULL NULL NULL 2 Using where
2326
explain partitions select * from t4 where (a=10 and b=2) or a=11;
2327
id select_type table partitions type possible_keys key key_len ref rows Extra
2328
1 SIMPLE t4 p0,p1 ALL NULL NULL NULL NULL 2 Using where
2329
drop table t1, t2, t3, t4;
2330
create table t5 (a int not null, b int not null,
2331
c int not null, d int not null)
2332
partition by LIST(a+b) subpartition by HASH (c+d) subpartitions 2
2334
partition p0 values in (12),
2335
partition p1 values in (14)
2337
insert into t5 values (10,2,0,0), (10,4,0,0), (10,2,0,1), (10,4,0,1);
2338
explain partitions select * from t5;
2339
id select_type table partitions type possible_keys key key_len ref rows Extra
2340
1 SIMPLE t5 p0_p0sp0,p0_p0sp1,p1_p1sp0,p1_p1sp1 ALL NULL NULL NULL NULL 4 NULL
2341
explain partitions select * from t5
2342
where (a=10 and b=1) or (a=10 and b=2) or (a=10 and b = 3);
2343
id select_type table partitions type possible_keys key key_len ref rows Extra
2344
1 SIMPLE t5 p0_p0sp0,p0_p0sp1 ALL NULL NULL NULL NULL 2 Using where
2345
explain partitions select * from t5 where (a=10 and b=2) or (a=10 and b=3)
2346
or (a=10 and b = 4);
2347
id select_type table partitions type possible_keys key key_len ref rows Extra
2348
1 SIMPLE t5 p0_p0sp0,p0_p0sp1,p1_p1sp0,p1_p1sp1 ALL NULL NULL NULL NULL 4 Using where
2349
explain partitions select * from t5 where (c=1 and d=1);
2350
id select_type table partitions type possible_keys key key_len ref rows Extra
2351
1 SIMPLE t5 p0_p0sp0,p1_p1sp0 ALL NULL NULL NULL NULL 2 Using where
2352
explain partitions select * from t5 where (c=2 and d=1);
2353
id select_type table partitions type possible_keys key key_len ref rows Extra
2354
1 SIMPLE t5 p0_p0sp1,p1_p1sp1 ALL NULL NULL NULL NULL 2 Using where
2355
explain partitions select * from t5 where (a=10 and b=2 and c=1 and d=1) or
2357
id select_type table partitions type possible_keys key key_len ref rows Extra
2358
1 SIMPLE t5 p0_p0sp0,p0_p0sp1,p1_p1sp1 ALL NULL NULL NULL NULL 3 Using where
2359
explain partitions select * from t5 where (a=10 and b=2 and c=1 and d=1) or
2360
(b=2 and c=2 and d=1);
2361
id select_type table partitions type possible_keys key key_len ref rows Extra
2362
1 SIMPLE t5 p0_p0sp0,p0_p0sp1,p1_p1sp1 ALL NULL NULL NULL NULL 3 Using where
2363
create table t6 (a int not null) partition by LIST(a) (
2364
partition p1 values in (1),
2365
partition p3 values in (3),
2366
partition p5 values in (5),
2367
partition p7 values in (7),
2368
partition p9 values in (9)
2370
insert into t6 values (1),(3),(5);
2371
explain partitions select * from t6 where a < 1;
2372
id select_type table partitions type possible_keys key key_len ref rows Extra
2373
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2374
explain partitions select * from t6 where a <= 1;
2375
id select_type table partitions type possible_keys key key_len ref rows Extra
2376
1 SIMPLE t6 p1 system NULL NULL NULL NULL 1 NULL
2377
explain partitions select * from t6 where a > 9;
2378
id select_type table partitions type possible_keys key key_len ref rows Extra
2379
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2380
explain partitions select * from t6 where a >= 9;
2381
id select_type table partitions type possible_keys key key_len ref rows Extra
2382
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2383
explain partitions select * from t6 where a > 0 and a < 5;
2384
id select_type table partitions type possible_keys key key_len ref rows Extra
2385
1 SIMPLE t6 p1,p3 ALL NULL NULL NULL NULL 2 Using where
2386
explain partitions select * from t6 where a > 5 and a < 12;
2387
id select_type table partitions type possible_keys key key_len ref rows Extra
2388
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2389
explain partitions select * from t6 where a > 3 and a < 8 ;
2390
id select_type table partitions type possible_keys key key_len ref rows Extra
2391
1 SIMPLE t6 p5,p7 system NULL NULL NULL NULL 1 NULL
2392
explain partitions select * from t6 where a >= 0 and a <= 5;
2393
id select_type table partitions type possible_keys key key_len ref rows Extra
2394
1 SIMPLE t6 p1,p3,p5 ALL NULL NULL NULL NULL 3 Using where
2395
explain partitions select * from t6 where a >= 5 and a <= 12;
2396
id select_type table partitions type possible_keys key key_len ref rows Extra
2397
1 SIMPLE t6 p5,p7,p9 system NULL NULL NULL NULL 1 NULL
2398
explain partitions select * from t6 where a >= 3 and a <= 8;
2399
id select_type table partitions type possible_keys key key_len ref rows Extra
2400
1 SIMPLE t6 p3,p5,p7 ALL NULL NULL NULL NULL 2 Using where
2401
explain partitions select * from t6 where a > 3 and a < 5;
2402
id select_type table partitions type possible_keys key key_len ref rows Extra
2403
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2405
create table t6 (a int unsigned not null) partition by LIST(a) (
2406
partition p1 values in (1),
2407
partition p3 values in (3),
2408
partition p5 values in (5),
2409
partition p7 values in (7),
2410
partition p9 values in (9)
2412
insert into t6 values (1),(3),(5);
2413
explain partitions select * from t6 where a < 1;
2414
id select_type table partitions type possible_keys key key_len ref rows Extra
2415
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2416
explain partitions select * from t6 where a <= 1;
2417
id select_type table partitions type possible_keys key key_len ref rows Extra
2418
1 SIMPLE t6 p1 system NULL NULL NULL NULL 1 NULL
2419
explain partitions select * from t6 where a > 9;
2420
id select_type table partitions type possible_keys key key_len ref rows Extra
2421
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2422
explain partitions select * from t6 where a >= 9;
2423
id select_type table partitions type possible_keys key key_len ref rows Extra
2424
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2425
explain partitions select * from t6 where a > 0 and a < 5;
2426
id select_type table partitions type possible_keys key key_len ref rows Extra
2427
1 SIMPLE t6 p1,p3 ALL NULL NULL NULL NULL 2 Using where
2428
explain partitions select * from t6 where a > 5 and a < 12;
2429
id select_type table partitions type possible_keys key key_len ref rows Extra
2430
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2431
explain partitions select * from t6 where a > 3 and a < 8 ;
2432
id select_type table partitions type possible_keys key key_len ref rows Extra
2433
1 SIMPLE t6 p5,p7 system NULL NULL NULL NULL 1 NULL
2434
explain partitions select * from t6 where a >= 0 and a <= 5;
2435
id select_type table partitions type possible_keys key key_len ref rows Extra
2436
1 SIMPLE t6 p1,p3,p5 ALL NULL NULL NULL NULL 3 Using where
2437
explain partitions select * from t6 where a >= 5 and a <= 12;
2438
id select_type table partitions type possible_keys key key_len ref rows Extra
2439
1 SIMPLE t6 p5,p7,p9 system NULL NULL NULL NULL 1 NULL
2440
explain partitions select * from t6 where a >= 3 and a <= 8;
2441
id select_type table partitions type possible_keys key key_len ref rows Extra
2442
1 SIMPLE t6 p3,p5,p7 ALL NULL NULL NULL NULL 2 Using where
2443
explain partitions select * from t6 where a > 3 and a < 5;
2444
id select_type table partitions type possible_keys key key_len ref rows Extra
2445
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2446
create table t7 (a int not null) partition by RANGE(a) (
2447
partition p10 values less than (10),
2448
partition p30 values less than (30),
2449
partition p50 values less than (50),
2450
partition p70 values less than (70),
2451
partition p90 values less than (90)
2453
insert into t7 values (10),(30),(50);
2454
explain partitions select * from t7 where a < 5;
2455
id select_type table partitions type possible_keys key key_len ref rows Extra
2456
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2457
explain partitions select * from t7 where a < 9;
2458
id select_type table partitions type possible_keys key key_len ref rows Extra
2459
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2460
explain partitions select * from t7 where a <= 9;
2461
id select_type table partitions type possible_keys key key_len ref rows Extra
2462
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2463
explain partitions select * from t7 where a = 9;
2464
id select_type table partitions type possible_keys key key_len ref rows Extra
2465
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2466
explain partitions select * from t7 where a >= 9;
2467
id select_type table partitions type possible_keys key key_len ref rows Extra
2468
1 SIMPLE t7 p10,p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where
2469
explain partitions select * from t7 where a > 9;
2470
id select_type table partitions type possible_keys key key_len ref rows Extra
2471
1 SIMPLE t7 p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where
2472
explain partitions select * from t7 where a < 10;
2473
id select_type table partitions type possible_keys key key_len ref rows Extra
2474
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2475
explain partitions select * from t7 where a <= 10;
2476
id select_type table partitions type possible_keys key key_len ref rows Extra
2477
1 SIMPLE t7 p10,p30 system NULL NULL NULL NULL 1 NULL
2478
explain partitions select * from t7 where a = 10;
2479
id select_type table partitions type possible_keys key key_len ref rows Extra
2480
1 SIMPLE t7 p30 system NULL NULL NULL NULL 1 NULL
2481
explain partitions select * from t7 where a >= 10;
2482
id select_type table partitions type possible_keys key key_len ref rows Extra
2483
1 SIMPLE t7 p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where
2484
explain partitions select * from t7 where a > 10;
2485
id select_type table partitions type possible_keys key key_len ref rows Extra
2486
1 SIMPLE t7 p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where
2487
explain partitions select * from t7 where a < 89;
2488
id select_type table partitions type possible_keys key key_len ref rows Extra
2489
1 SIMPLE t7 p10,p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where
2490
explain partitions select * from t7 where a <= 89;
2491
id select_type table partitions type possible_keys key key_len ref rows Extra
2492
1 SIMPLE t7 p10,p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where
2493
explain partitions select * from t7 where a = 89;
2494
id select_type table partitions type possible_keys key key_len ref rows Extra
2495
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2496
explain partitions select * from t7 where a > 89;
2497
id select_type table partitions type possible_keys key key_len ref rows Extra
2498
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2499
explain partitions select * from t7 where a >= 89;
2500
id select_type table partitions type possible_keys key key_len ref rows Extra
2501
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2502
explain partitions select * from t7 where a < 90;
2503
id select_type table partitions type possible_keys key key_len ref rows Extra
2504
1 SIMPLE t7 p10,p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where
2505
explain partitions select * from t7 where a <= 90;
2506
id select_type table partitions type possible_keys key key_len ref rows Extra
2507
1 SIMPLE t7 p10,p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where
2508
explain partitions select * from t7 where a = 90;
2509
id select_type table partitions type possible_keys key key_len ref rows Extra
2510
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2511
explain partitions select * from t7 where a > 90;
2512
id select_type table partitions type possible_keys key key_len ref rows Extra
2513
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2514
explain partitions select * from t7 where a >= 90;
2515
id select_type table partitions type possible_keys key key_len ref rows Extra
2516
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2517
explain partitions select * from t7 where a > 91;
2518
id select_type table partitions type possible_keys key key_len ref rows Extra
2519
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2520
explain partitions select * from t7 where a > 11 and a < 29;
2521
id select_type table partitions type possible_keys key key_len ref rows Extra
2522
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2524
create table t7 (a int unsigned not null) partition by RANGE(a) (
2525
partition p10 values less than (10),
2526
partition p30 values less than (30),
2527
partition p50 values less than (50),
2528
partition p70 values less than (70),
2529
partition p90 values less than (90)
2531
insert into t7 values (10),(30),(50);
2532
explain partitions select * from t7 where a < 5;
2533
id select_type table partitions type possible_keys key key_len ref rows Extra
2534
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2535
explain partitions select * from t7 where a < 9;
2536
id select_type table partitions type possible_keys key key_len ref rows Extra
2537
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2538
explain partitions select * from t7 where a <= 9;
2539
id select_type table partitions type possible_keys key key_len ref rows Extra
2540
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2541
explain partitions select * from t7 where a = 9;
2542
id select_type table partitions type possible_keys key key_len ref rows Extra
2543
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2544
explain partitions select * from t7 where a >= 9;
2545
id select_type table partitions type possible_keys key key_len ref rows Extra
2546
1 SIMPLE t7 p10,p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where
2547
explain partitions select * from t7 where a > 9;
2548
id select_type table partitions type possible_keys key key_len ref rows Extra
2549
1 SIMPLE t7 p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where
2550
explain partitions select * from t7 where a < 10;
2551
id select_type table partitions type possible_keys key key_len ref rows Extra
2552
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2553
explain partitions select * from t7 where a <= 10;
2554
id select_type table partitions type possible_keys key key_len ref rows Extra
2555
1 SIMPLE t7 p10,p30 system NULL NULL NULL NULL 1 NULL
2556
explain partitions select * from t7 where a = 10;
2557
id select_type table partitions type possible_keys key key_len ref rows Extra
2558
1 SIMPLE t7 p30 system NULL NULL NULL NULL 1 NULL
2559
explain partitions select * from t7 where a >= 10;
2560
id select_type table partitions type possible_keys key key_len ref rows Extra
2561
1 SIMPLE t7 p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where
2562
explain partitions select * from t7 where a > 10;
2563
id select_type table partitions type possible_keys key key_len ref rows Extra
2564
1 SIMPLE t7 p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where
2565
explain partitions select * from t7 where a < 89;
2566
id select_type table partitions type possible_keys key key_len ref rows Extra
2567
1 SIMPLE t7 p10,p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where
2568
explain partitions select * from t7 where a <= 89;
2569
id select_type table partitions type possible_keys key key_len ref rows Extra
2570
1 SIMPLE t7 p10,p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where
2571
explain partitions select * from t7 where a = 89;
2572
id select_type table partitions type possible_keys key key_len ref rows Extra
2573
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2574
explain partitions select * from t7 where a > 89;
2575
id select_type table partitions type possible_keys key key_len ref rows Extra
2576
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2577
explain partitions select * from t7 where a >= 89;
2578
id select_type table partitions type possible_keys key key_len ref rows Extra
2579
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2580
explain partitions select * from t7 where a < 90;
2581
id select_type table partitions type possible_keys key key_len ref rows Extra
2582
1 SIMPLE t7 p10,p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where
2583
explain partitions select * from t7 where a <= 90;
2584
id select_type table partitions type possible_keys key key_len ref rows Extra
2585
1 SIMPLE t7 p10,p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where
2586
explain partitions select * from t7 where a = 90;
2587
id select_type table partitions type possible_keys key key_len ref rows Extra
2588
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2589
explain partitions select * from t7 where a > 90;
2590
id select_type table partitions type possible_keys key key_len ref rows Extra
2591
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2592
explain partitions select * from t7 where a >= 90;
2593
id select_type table partitions type possible_keys key key_len ref rows Extra
2594
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2595
explain partitions select * from t7 where a > 91;
2596
id select_type table partitions type possible_keys key key_len ref rows Extra
2597
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2598
explain partitions select * from t7 where a > 11 and a < 29;
2599
id select_type table partitions type possible_keys key key_len ref rows Extra
2600
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2601
create table t8 (a date not null) partition by RANGE(YEAR(a)) (
2602
partition p0 values less than (1980),
2603
partition p1 values less than (1990),
2604
partition p2 values less than (2000)
2606
insert into t8 values ('1985-05-05'),('1995-05-05');
2607
explain partitions select * from t8 where a < '1980-02-02';
2608
id select_type table partitions type possible_keys key key_len ref rows Extra
2609
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2610
create table t9 (a date not null) partition by RANGE(TO_DAYS(a)) (
2611
partition p0 values less than (732299), -- 2004-12-19
2612
partition p1 values less than (732468), -- 2005-06-06
2613
partition p2 values less than (732664) -- 2005-12-19
2615
insert into t9 values ('2005-05-05'), ('2005-04-04');
2616
explain partitions select * from t9 where a < '2004-12-19';
2617
id select_type table partitions type possible_keys key key_len ref rows Extra
2618
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2619
explain partitions select * from t9 where a <= '2004-12-19';
2620
id select_type table partitions type possible_keys key key_len ref rows Extra
2621
1 SIMPLE t9 p0,p1 ALL NULL NULL NULL NULL 2 Using where
2622
drop table t5,t6,t7,t8,t9;
2626
partition by range (a1) (
2627
partition p0 values less than (3),
2628
partition p1 values less than (6),
2629
partition p2 values less than (9)
2631
insert into t1 values (1),(2),(3);
2632
explain partitions select * from t1 where a1 > 3;
2633
id select_type table partitions type possible_keys key key_len ref rows Extra
2634
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2635
explain partitions select * from t1 where a1 >= 3;
2636
id select_type table partitions type possible_keys key key_len ref rows Extra
2637
1 SIMPLE t1 p1,p2 system NULL NULL NULL NULL 1 NULL
2638
explain partitions select * from t1 where a1 < 3 and a1 > 3;
2639
id select_type table partitions type possible_keys key key_len ref rows Extra
2640
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2642
create table t3 (a int, b int)
2643
partition by list(a) subpartition by hash(b) subpartitions 4 (
2644
partition p0 values in (1),
2645
partition p1 values in (2),
2646
partition p2 values in (3),
2647
partition p3 values in (4)
2649
insert into t3 values (1,1),(2,2),(3,3);
2650
explain partitions select * from t3 where a=2 or b=1;
2651
id select_type table partitions type possible_keys key key_len ref rows Extra
2652
1 SIMPLE t3 p0_p0sp1,p1_p1sp0,p1_p1sp1,p1_p1sp2,p1_p1sp3,p2_p2sp1,p3_p3sp1 ALL NULL NULL NULL NULL 2 Using where
2653
explain partitions select * from t3 where a=4 or b=2;
2654
id select_type table partitions type possible_keys key key_len ref rows Extra
2655
1 SIMPLE t3 p0_p0sp2,p1_p1sp2,p2_p2sp2,p3_p3sp0,p3_p3sp1,p3_p3sp2,p3_p3sp3 system NULL NULL NULL NULL 1 NULL
2656
explain partitions select * from t3 where (a=2 or b=1) and (a=4 or b=2) ;
2657
id select_type table partitions type possible_keys key key_len ref rows Extra
2658
1 SIMPLE t3 p1_p1sp2,p3_p3sp1 system NULL NULL NULL NULL 1 NULL
2660
create table t1 (a int) partition by hash(a) partitions 2;
2661
insert into t1 values (1),(2);
2662
explain partitions select * from t1 where a is null;
2663
id select_type table partitions type possible_keys key key_len ref rows Extra
2664
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2665
explain partitions select * from t1 where a is not null;
2666
id select_type table partitions type possible_keys key key_len ref rows Extra
2667
1 SIMPLE t1 p0,p1 ALL NULL NULL NULL NULL 2 Using where
2669
create table t1 (a int not null, b int not null, key(a), key(b))
2670
partition by hash(a) partitions 4;
2671
insert into t1 values (1,1),(2,2),(3,3),(4,4);
2673
select * from t1 X, t1 Y
2674
where X.b = Y.b and (X.a=1 or X.a=2) and (Y.a=2 or Y.a=3);
2675
id select_type table partitions type possible_keys key key_len ref rows Extra
2676
1 SIMPLE X p1,p2 ALL a,b NULL NULL NULL 2 Using where
2677
1 SIMPLE Y p2,p3 ref a,b b 4 test.X.b 2 Using where
2679
select * from t1 X, t1 Y where X.a = Y.a and (X.a=1 or X.a=2);
2680
id select_type table partitions type possible_keys key key_len ref rows Extra
2681
1 SIMPLE X p1,p2 ALL a NULL NULL NULL 2 Using where
2682
1 SIMPLE Y p1,p2 ref a a 4 test.X.a 2 NULL
2684
create table t1 (a int) partition by hash(a) partitions 20;
2685
insert into t1 values (1),(2),(3);
2686
explain partitions select * from t1 where a > 1 and a < 3;
2687
id select_type table partitions type possible_keys key key_len ref rows Extra
2688
1 SIMPLE t1 p2 system NULL NULL NULL NULL 1 NULL
2689
explain partitions select * from t1 where a >= 1 and a < 3;
2690
id select_type table partitions type possible_keys key key_len ref rows Extra
2691
1 SIMPLE t1 p1,p2 ALL NULL NULL NULL NULL 2 Using where
2692
explain partitions select * from t1 where a > 1 and a <= 3;
2693
id select_type table partitions type possible_keys key key_len ref rows Extra
2694
1 SIMPLE t1 p2,p3 ALL NULL NULL NULL NULL 2 Using where
2695
explain partitions select * from t1 where a >= 1 and a <= 3;
2696
id select_type table partitions type possible_keys key key_len ref rows Extra
2697
1 SIMPLE t1 p1,p2,p3 ALL NULL NULL NULL NULL 3 Using where
2699
create table t1 (a int, b int)
2700
partition by list(a) subpartition by hash(b) subpartitions 20
2702
partition p0 values in (0),
2703
partition p1 values in (1),
2704
partition p2 values in (2),
2705
partition p3 values in (3)
2707
insert into t1 values (1,1),(2,2),(3,3);
2708
explain partitions select * from t1 where b > 1 and b < 3;
2709
id select_type table partitions type possible_keys key key_len ref rows Extra
2710
1 SIMPLE t1 p0_p0sp2,p1_p1sp2,p2_p2sp2,p3_p3sp2 system NULL NULL NULL NULL 1 NULL
2711
explain partitions select * from t1 where b > 1 and b < 3 and (a =1 or a =2);
2712
id select_type table partitions type possible_keys key key_len ref rows Extra
2713
1 SIMPLE t1 p1_p1sp2,p2_p2sp2 system NULL NULL NULL NULL 1 NULL
2715
create table t1 (a int) partition by list(a) (
2716
partition p0 values in (1,2),
2717
partition p1 values in (3,4)
2719
insert into t1 values (1),(1),(2),(2),(3),(4),(3),(4);
2720
# This won't do any table access
2721
explain extended update t1 set a=100 where a=5;
2722
id select_type table type possible_keys key key_len ref rows filtered Extra
2723
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No matching rows after partition pruning
2724
explain partitions update t1 set a=100 where a=5;
2725
id select_type table partitions type possible_keys key key_len ref rows Extra
2726
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No matching rows after partition pruning
2728
update t1 set a=100 where a=5;
2729
show status like 'Handler_read_rnd_next';
2731
Handler_read_rnd_next 0
2732
show status like 'Handler_external_lock';
2734
Handler_external_lock 0
2735
# ... as compared to this, which will scan both partitions
2736
explain extended update t1 set a=100 where a+1=5+1;
2737
id select_type table type possible_keys key key_len ref rows filtered Extra
2738
1 SIMPLE t1 ALL NULL NULL NULL NULL 8 100.00 Using where; Using temporary
2739
explain partitions update t1 set a=100 where a+1=5+1;
2740
id select_type table partitions type possible_keys key key_len ref rows Extra
2741
1 SIMPLE t1 p0,p1 ALL NULL NULL NULL NULL 8 Using where; Using temporary
2743
update t1 set a=100 where a+1=5+1;
2744
show status like 'Handler_read_rnd_next';
2746
Handler_read_rnd_next 10
2747
show status like 'Handler_external_lock';
2749
Handler_external_lock 6
2750
# This will only scan partition p1
2751
explain extended update t1 set a=3 where a=4;
2752
id select_type table type possible_keys key key_len ref rows filtered Extra
2753
1 SIMPLE t1 ALL NULL NULL NULL NULL 4 100.00 Using where; Using temporary
2754
explain partitions update t1 set a=3 where a=4;
2755
id select_type table partitions type possible_keys key key_len ref rows Extra
2756
1 SIMPLE t1 p1 ALL NULL NULL NULL NULL 4 Using where; Using temporary
2758
update t1 set a=3 where a=4;
2759
show status like 'Handler_read_rnd_next';
2761
Handler_read_rnd_next 5
2762
show status like 'Handler_external_lock';
2764
Handler_external_lock 6
2765
# This will only scan partition p1 but with a LIMIT
2766
explain extended update t1 set a=4 where a=3 LIMIT 2;
2767
id select_type table type possible_keys key key_len ref rows filtered Extra
2768
1 SIMPLE t1 ALL NULL NULL NULL NULL 4 100.00 Using where; Using temporary
2769
explain partitions update t1 set a=4 where a=3 LIMIT 2;
2770
id select_type table partitions type possible_keys key key_len ref rows Extra
2771
1 SIMPLE t1 p1 ALL NULL NULL NULL NULL 4 Using where; Using temporary
2773
update t1 set a=4 where a=3 LIMIT 2;
2774
show status like 'Handler_read_rnd_next';
2776
Handler_read_rnd_next 2
2777
show status like 'Handler_external_lock';
2779
Handler_external_lock 6
2790
# Same as above for DELETE:
2791
explain extended delete from t1 where a=5;
2792
id select_type table type possible_keys key key_len ref rows filtered Extra
2793
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No matching rows after partition pruning
2794
explain partitions delete from t1 where a=5;
2795
id select_type table partitions type possible_keys key key_len ref rows Extra
2796
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No matching rows after partition pruning
2798
delete from t1 where a=5;
2799
show status like 'Handler_read_rnd_next';
2801
Handler_read_rnd_next 0
2802
show status like 'Handler_external_lock';
2804
Handler_external_lock 0
2805
explain extended delete from t1 where a+1=5+1;
2806
id select_type table type possible_keys key key_len ref rows filtered Extra
2807
1 SIMPLE t1 ALL NULL NULL NULL NULL 8 100.00 Using where
2808
explain partitions delete from t1 where a+1=5+1;
2809
id select_type table partitions type possible_keys key key_len ref rows Extra
2810
1 SIMPLE t1 p0,p1 ALL NULL NULL NULL NULL 8 Using where
2812
delete from t1 where a+1=5+1;
2813
show status like 'Handler_read_rnd_next';
2815
Handler_read_rnd_next 10
2816
show status like 'Handler_external_lock';
2818
Handler_external_lock 6
2819
# Will only delete from p0
2820
explain extended delete from t1 where a=1;
2821
id select_type table type possible_keys key key_len ref rows filtered Extra
2822
1 SIMPLE t1 ALL NULL NULL NULL NULL 4 100.00 Using where
2823
explain partitions delete from t1 where a=1;
2824
id select_type table partitions type possible_keys key key_len ref rows Extra
2825
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 4 Using where
2827
delete from t1 where a=1;
2828
show status like 'Handler_read_rnd_next';
2830
Handler_read_rnd_next 5
2831
show status like 'Handler_external_lock';
2833
Handler_external_lock 4
2842
insert into t1 values (1), (1);
2843
# Same as above multi-table UPDATE/DELETE
2844
create table t2 like t1;
2845
insert into t2 select * from t1;
2846
explain extended update t1,t2 set t1.a=1000, t2.a=1000 where t1.a=5 and t2.a=5;
2847
id select_type table type possible_keys key key_len ref rows filtered Extra
2848
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2849
explain partitions update t1,t2 set t1.a=1000, t2.a=1000 where t1.a=5 and t2.a=5;
2850
id select_type table partitions type possible_keys key key_len ref rows Extra
2851
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2853
update t1,t2 set t1.a=1000, t2.a=1000 where t1.a=5 and t2.a=5;
2854
show status like 'Handler_read_rnd_next';
2856
Handler_read_rnd_next 0
2857
show status like 'Handler_external_lock';
2859
Handler_external_lock 12
2860
explain extended delete t1,t2 from t1, t2 where t1.a=5 and t2.a=5;
2861
id select_type table type possible_keys key key_len ref rows filtered Extra
2862
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2863
explain partitions delete t1,t2 from t1, t2 where t1.a=5 and t2.a=5;
2864
id select_type table partitions type possible_keys key key_len ref rows Extra
2865
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2867
delete t1,t2 from t1, t2 where t1.a=5 and t2.a=5;
2868
show status like 'Handler_read_rnd_next';
2870
Handler_read_rnd_next 0
2871
show status like 'Handler_external_lock';
2873
Handler_external_lock 4
2874
explain extended delete t1,t2 from t1, t2 where t1.a=t2.a and t2.a=5;
2875
id select_type table type possible_keys key key_len ref rows filtered Extra
2876
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2877
explain partitions delete t1,t2 from t1, t2 where t1.a=t2.a and t2.a=5;
2878
id select_type table partitions type possible_keys key key_len ref rows Extra
2879
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2881
delete t1,t2 from t1, t2 where t1.a=t2.a and t2.a=5;
2882
show status like 'Handler_read_rnd_next';
2884
Handler_read_rnd_next 0
2885
show status like 'Handler_external_lock';
2887
Handler_external_lock 8
2888
explain extended delete t1,t2 from t1, t2 where t1.a=t2.a and t2.a=3;
2889
id select_type table type possible_keys key key_len ref rows filtered Extra
2890
1 SIMPLE t1 ALL NULL NULL NULL NULL 4 100.00 Using where
2891
1 SIMPLE t2 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer (Block Nested Loop)
2892
explain partitions delete t1,t2 from t1, t2 where t1.a=t2.a and t2.a=3;
2893
id select_type table partitions type possible_keys key key_len ref rows Extra
2894
1 SIMPLE t1 p1 ALL NULL NULL NULL NULL 4 Using where
2895
1 SIMPLE t2 p1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (Block Nested Loop)
2897
delete t1,t2 from t1, t2 where t1.a=t2.a and t2.a=3;
2898
show status like 'Handler_read_rnd_next';
2900
Handler_read_rnd_next 15
2901
show status like 'Handler_external_lock';
2903
Handler_external_lock 10
2914
`a` int(11) default NULL
2916
INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
2918
`a` int(11) default NULL,
2921
insert into t2 select A.a + 10*(B.a + 10* C.a) from t1 A, t1 B, t1 C ;
2922
insert into t1 select a from t2;
2925
`a` int(11) default NULL,
2926
`b` int(11) default NULL
2928
PARTITION BY RANGE (a) (
2929
PARTITION p0 VALUES LESS THAN (200),
2930
PARTITION p1 VALUES LESS THAN (400),
2931
PARTITION p2 VALUES LESS THAN (600),
2932
PARTITION p3 VALUES LESS THAN (800),
2933
PARTITION p4 VALUES LESS THAN (1001));
2935
`a` int(11) default NULL,
2936
`b` int(11) default NULL
2938
explain partitions insert into t2 select a,1 from t1 where a < 200;
2939
id select_type table partitions type possible_keys key key_len ref rows Extra
2940
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1010 Using where
2941
insert into t2 select a,1 from t1 where a < 200;
2942
insert into t2 select a,2 from t1 where a >= 200 and a < 400;
2943
insert into t2 select a,3 from t1 where a >= 400 and a < 600;
2944
insert into t2 select a,4 from t1 where a >= 600 and a < 800;
2945
insert into t2 select a,5 from t1 where a >= 800 and a < 1001;
2946
explain partitions select * from t2;
2947
id select_type table partitions type possible_keys key key_len ref rows Extra
2948
1 SIMPLE t2 p0,p1,p2,p3,p4 ALL NULL NULL NULL NULL 1010 NULL
2949
explain partitions insert into t3 select * from t2;
2950
id select_type table partitions type possible_keys key key_len ref rows Extra
2951
1 SIMPLE t2 p0,p1,p2,p3,p4 ALL NULL NULL NULL NULL 1010 NULL
2952
explain extended insert into t3 select * from t2;
2953
id select_type table type possible_keys key key_len ref rows filtered Extra
2954
1 SIMPLE t2 ALL NULL NULL NULL NULL 1010 100.00 NULL
2955
explain partitions select * from t2 where a < 801 and a > 200;
2956
id select_type table partitions type possible_keys key key_len ref rows Extra
2957
1 SIMPLE t2 p1,p2,p3,p4 ALL NULL NULL NULL NULL 800 Using where
2958
explain partitions insert into t3 select * from t2 where a < 801 and a > 200;
2959
id select_type table partitions type possible_keys key key_len ref rows Extra
2960
1 SIMPLE t2 p1,p2,p3,p4 ALL NULL NULL NULL NULL 800 Using where
2961
explain partitions select * from t2 where a < 801 and a > 800;
2962
id select_type table partitions type possible_keys key key_len ref rows Extra
2963
1 SIMPLE t2 p4 ALL NULL NULL NULL NULL 200 Using where
2964
explain partitions insert into t3 select * from t2 where a < 801 and a > 800;
2965
id select_type table partitions type possible_keys key key_len ref rows Extra
2966
1 SIMPLE t2 p4 ALL NULL NULL NULL NULL 200 Using where
2967
explain partitions select * from t2 where a > 600;
2968
id select_type table partitions type possible_keys key key_len ref rows Extra
2969
1 SIMPLE t2 p3,p4 ALL NULL NULL NULL NULL 400 Using where
2970
explain partitions insert into t3 select * from t2 where a > 600;
2971
id select_type table partitions type possible_keys key key_len ref rows Extra
2972
1 SIMPLE t2 p3,p4 ALL NULL NULL NULL NULL 400 Using where
2973
explain partitions select * from t2 where a > 600 and b = 1;
2974
id select_type table partitions type possible_keys key key_len ref rows Extra
2975
1 SIMPLE t2 p3,p4 ALL NULL NULL NULL NULL 400 Using where
2976
explain partitions insert into t3 select * from t2 where a > 600 and b = 1;
2977
id select_type table partitions type possible_keys key key_len ref rows Extra
2978
1 SIMPLE t2 p3,p4 ALL NULL NULL NULL NULL 400 Using where
2979
explain partitions select * from t2 where a > 600 and b = 4;
2980
id select_type table partitions type possible_keys key key_len ref rows Extra
2981
1 SIMPLE t2 p3,p4 ALL NULL NULL NULL NULL 400 Using where
2982
explain partitions insert into t3 select * from t2 where a > 600 and b = 4;
2983
id select_type table partitions type possible_keys key key_len ref rows Extra
2984
1 SIMPLE t2 p3,p4 ALL NULL NULL NULL NULL 400 Using where
2985
explain partitions select * from t2 where a > 600 and b = 5;
2986
id select_type table partitions type possible_keys key key_len ref rows Extra
2987
1 SIMPLE t2 p3,p4 ALL NULL NULL NULL NULL 400 Using where
2988
explain partitions insert into t3 select * from t2 where a > 600 and b = 5;
2989
id select_type table partitions type possible_keys key key_len ref rows Extra
2990
1 SIMPLE t2 p3,p4 ALL NULL NULL NULL NULL 400 Using where
2991
explain partitions select * from t2 where b = 5;
2992
id select_type table partitions type possible_keys key key_len ref rows Extra
2993
1 SIMPLE t2 p0,p1,p2,p3,p4 ALL NULL NULL NULL NULL 1010 Using where
2994
explain partitions insert into t3 select * from t2 where b = 5;
2995
id select_type table partitions type possible_keys key key_len ref rows Extra
2996
1 SIMPLE t2 p0,p1,p2,p3,p4 ALL NULL NULL NULL NULL 1010 Using where
2998
explain extended update t2 set b = 100 where b = 6;
2999
id select_type table type possible_keys key key_len ref rows filtered Extra
3000
1 SIMPLE t2 ALL NULL NULL NULL NULL 1010 100.00 Using where
3001
explain partitions update t2 set b = 100 where b = 6;
3002
id select_type table partitions type possible_keys key key_len ref rows Extra
3003
1 SIMPLE t2 p0,p1,p2,p3,p4 ALL NULL NULL NULL NULL 1010 Using where
3005
update t2 set b = 100 where b = 6;
3006
show status like 'Handler_read_rnd_next';
3008
Handler_read_rnd_next 1015
3009
explain partitions update t2 set a = 1002 where a = 1001;
3010
id select_type table partitions type possible_keys key key_len ref rows Extra
3011
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No matching rows after partition pruning
3013
update t2 set a = 1002 where a = 1001;
3014
show status like 'Handler_read_rnd_next';
3016
Handler_read_rnd_next 0
3017
explain partitions update t2 set b = 6 where a = 600;
3018
id select_type table partitions type possible_keys key key_len ref rows Extra
3019
1 SIMPLE t2 p3 ALL NULL NULL NULL NULL 200 Using where
3021
update t2 set b = 6 where a = 600;
3022
show status like 'Handler_read_rnd_next';
3024
Handler_read_rnd_next 201
3025
explain partitions update t2 set b = 6 where a > 600 and a < 800;
3026
id select_type table partitions type possible_keys key key_len ref rows Extra
3027
1 SIMPLE t2 p3 ALL NULL NULL NULL NULL 200 Using where
3029
update t2 set b = 6 where a > 600 and a < 800;
3030
show status like 'Handler_read_rnd_next';
3032
Handler_read_rnd_next 201
3033
explain extended delete from t2 where a > 600;
3034
id select_type table type possible_keys key key_len ref rows filtered Extra
3035
1 SIMPLE t2 ALL NULL NULL NULL NULL 400 100.00 Using where
3036
explain partitions delete from t2 where a > 600;
3037
id select_type table partitions type possible_keys key key_len ref rows Extra
3038
1 SIMPLE t2 p3,p4 ALL NULL NULL NULL NULL 400 Using where
3040
delete from t2 where a > 600;
3041
show status like 'Handler_read_rnd_next';
3043
Handler_read_rnd_next 402
3046
`a` int(11) default NULL,
3047
`b` int(11) default NULL,
3050
PARTITION BY RANGE (a) (
3051
PARTITION p0 VALUES LESS THAN (200),
3052
PARTITION p1 VALUES LESS THAN (400),
3053
PARTITION p2 VALUES LESS THAN (600),
3054
PARTITION p3 VALUES LESS THAN (800),
3055
PARTITION p4 VALUES LESS THAN (1001));
3056
insert into t2 select a,1 from t1 where a < 100;
3057
insert into t2 select a,2 from t1 where a >= 200 and a < 300;
3058
insert into t2 select a,3 from t1 where a >= 300 and a < 400;
3059
insert into t2 select a,4 from t1 where a >= 400 and a < 500;
3060
insert into t2 select a,5 from t1 where a >= 500 and a < 600;
3061
insert into t2 select a,6 from t1 where a >= 600 and a < 700;
3062
insert into t2 select a,7 from t1 where a >= 700 and a < 800;
3063
insert into t2 select a,8 from t1 where a >= 800 and a < 900;
3064
insert into t2 select a,9 from t1 where a >= 900 and a < 1001;
3065
explain partitions select * from t2;
3066
id select_type table partitions type possible_keys key key_len ref rows Extra
3067
1 SIMPLE t2 p0,p1,p2,p3,p4 ALL NULL NULL NULL NULL 910 NULL
3068
explain partitions select * from t2 where a = 101;
3069
id select_type table partitions type possible_keys key key_len ref rows Extra
3070
1 SIMPLE t2 p0 ALL NULL NULL NULL NULL 110 Using where
3071
explain partitions select * from t2 where a = 550;
3072
id select_type table partitions type possible_keys key key_len ref rows Extra
3073
1 SIMPLE t2 p2 ALL NULL NULL NULL NULL 200 Using where
3074
explain partitions select * from t2 where a = 833;
3075
id select_type table partitions type possible_keys key key_len ref rows Extra
3076
1 SIMPLE t2 p4 ALL NULL NULL NULL NULL 200 Using where
3077
explain partitions select * from t2 where (a = 100 OR a = 900);
3078
id select_type table partitions type possible_keys key key_len ref rows Extra
3079
1 SIMPLE t2 p0,p4 ALL NULL NULL NULL NULL 310 Using where
3080
explain partitions select * from t2 where (a > 100 AND a < 600);
3081
id select_type table partitions type possible_keys key key_len ref rows Extra
3082
1 SIMPLE t2 p0,p1,p2 ALL NULL NULL NULL NULL 510 Using where
3083
explain partitions select * from t2 where b = 4;
3084
id select_type table partitions type possible_keys key key_len ref rows Extra
3085
1 SIMPLE t2 p0,p1,p2,p3,p4 ref b b 5 const 96 NULL
3086
explain extended select * from t2 where b = 6;
3087
id select_type table type possible_keys key key_len ref rows filtered Extra
3088
1 SIMPLE t2 ref b b 5 const 96 100.00 NULL
3090
Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` = 6)
3091
explain partitions select * from t2 where b = 6;
3092
id select_type table partitions type possible_keys key key_len ref rows Extra
3093
1 SIMPLE t2 p0,p1,p2,p3,p4 ref b b 5 const 96 NULL
3094
explain extended select * from t2 where b in (1,3,5);
3095
id select_type table type possible_keys key key_len ref rows filtered Extra
3096
1 SIMPLE t2 ALL b NULL NULL NULL 910 51.65 Using where
3098
Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` in (1,3,5))
3099
explain partitions select * from t2 where b in (1,3,5);
3100
id select_type table partitions type possible_keys key key_len ref rows Extra
3101
1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where
3102
explain extended select * from t2 where b in (2,4,6);
3103
id select_type table type possible_keys key key_len ref rows filtered Extra
3104
1 SIMPLE t2 ALL b NULL NULL NULL 910 31.65 Using where
3106
Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` in (2,4,6))
3107
explain partitions select * from t2 where b in (2,4,6);
3108
id select_type table partitions type possible_keys key key_len ref rows Extra
3109
1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where
3110
explain extended select * from t2 where b in (7,8,9);
3111
id select_type table type possible_keys key key_len ref rows filtered Extra
3112
1 SIMPLE t2 ALL b NULL NULL NULL 910 19.12 Using where
3114
Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` in (7,8,9))
3115
explain partitions select * from t2 where b in (7,8,9);
3116
id select_type table partitions type possible_keys key key_len ref rows Extra
3117
1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where
3118
explain extended select * from t2 where b > 5;
3119
id select_type table type possible_keys key key_len ref rows filtered Extra
3120
1 SIMPLE t2 ALL b NULL NULL NULL 910 29.23 Using where
3122
Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` > 5)
3123
explain partitions select * from t2 where b > 5;
3124
id select_type table partitions type possible_keys key key_len ref rows Extra
3125
1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where
3126
explain extended select * from t2 where b > 5 and b < 8;
3127
id select_type table type possible_keys key key_len ref rows filtered Extra
3128
1 SIMPLE t2 ALL b NULL NULL NULL 910 28.13 Using where
3130
Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where ((`test`.`t2`.`b` > 5) and (`test`.`t2`.`b` < 8))
3131
explain partitions select * from t2 where b > 5 and b < 8;
3132
id select_type table partitions type possible_keys key key_len ref rows Extra
3133
1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where
3134
explain extended select * from t2 where b > 5 and b < 7;
3135
id select_type table type possible_keys key key_len ref rows filtered Extra
3136
1 SIMPLE t2 range b b 5 NULL 96 100.00 Using where
3138
Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where ((`test`.`t2`.`b` > 5) and (`test`.`t2`.`b` < 7))
3139
explain partitions select * from t2 where b > 5 and b < 7;
3140
id select_type table partitions type possible_keys key key_len ref rows Extra
3141
1 SIMPLE t2 p0,p1,p2,p3,p4 range b b 5 NULL 96 Using where
3142
explain extended select * from t2 where b > 0 and b < 5;
3143
id select_type table type possible_keys key key_len ref rows filtered Extra
3144
1 SIMPLE t2 ALL b NULL NULL NULL 910 53.19 Using where
3146
Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where ((`test`.`t2`.`b` > 0) and (`test`.`t2`.`b` < 5))
3147
explain partitions select * from t2 where b > 0 and b < 5;
3148
id select_type table partitions type possible_keys key key_len ref rows Extra
3149
1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where
3150
explain extended update t2 set a = 111 where b = 10;
3151
id select_type table type possible_keys key key_len ref rows filtered Extra
3152
1 SIMPLE t2 range b b 5 const 5 100.00 Using where; Using temporary
3153
explain partitions update t2 set a = 111 where b = 10;
3154
id select_type table partitions type possible_keys key key_len ref rows Extra
3155
1 SIMPLE t2 p0,p1,p2,p3,p4 range b b 5 const 5 Using where; Using temporary
3157
update t2 set a = 111 where b = 10;
3158
show status like 'Handler_read_rnd_next';
3160
Handler_read_rnd_next 0
3161
show status like 'Handler_read_key';
3164
explain partitions update t2 set a = 111 where b in (5,6);
3165
id select_type table partitions type possible_keys key key_len ref rows Extra
3166
1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where; Using temporary
3168
update t2 set a = 111 where b in (5,6);
3169
show status like 'Handler_read_rnd_next';
3171
Handler_read_rnd_next 915
3172
show status like 'Handler_read_key';
3175
explain partitions update t2 set a = 222 where b = 7;
3176
id select_type table partitions type possible_keys key key_len ref rows Extra
3177
1 SIMPLE t2 p0,p1,p2,p3,p4 range b b 5 const 4 Using where; Using temporary
3179
update t2 set a = 222 where b = 7;
3180
show status like 'Handler_read_rnd_next';
3182
Handler_read_rnd_next 0
3183
show status like 'Handler_read_key';
3186
explain extended delete from t2 where b = 7;
3187
id select_type table type possible_keys key key_len ref rows filtered Extra
3188
1 SIMPLE t2 ALL b NULL NULL NULL 910 100.00 Using where
3189
explain partitions delete from t2 where b = 7;
3190
id select_type table partitions type possible_keys key key_len ref rows Extra
3191
1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where
3193
delete from t2 where b = 7;
3194
show status like 'Handler_read_rnd_next';
3196
Handler_read_rnd_next 1215
3197
show status like 'Handler_read_key';
3200
explain partitions delete from t2 where b > 5;
3201
id select_type table partitions type possible_keys key key_len ref rows Extra
3202
1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 810 Using where
3204
delete from t2 where b > 5;
3205
show status like 'Handler_read_rnd_next';
3207
Handler_read_rnd_next 1215
3208
show status like 'Handler_read_key';
3211
show status like 'Handler_read_prev';
3214
show status like 'Handler_read_next';
3217
explain partitions delete from t2 where b < 5 or b > 3;
3218
id select_type table partitions type possible_keys key key_len ref rows Extra
3219
1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 510 Using where
3221
delete from t2 where b < 5 or b > 3;
3222
show status like 'Handler_read_rnd_next';
3224
Handler_read_rnd_next 1215
3225
show status like 'Handler_read_key';
3228
show status like 'Handler_read_prev';
3231
show status like 'Handler_read_next';
3235
create table t1 ( f_int1 mediumint, f_int2 integer)
3236
partition by list(mod(f_int1,4)) (
3237
partition p_3 values in (-3),
3238
partition p_2 values in (-2),
3239
partition p_1 values in (-1),
3240
partition p0 values in (0),
3241
partition p1 values in (1),
3242
partition p2 values in (2),
3243
partition p3 values in (3)
3245
insert into t1 values (9, 9), (8, 8), (7, 7), (6, 6), (5, 5),
3246
(4, 4), (3, 3), (2, 2), (1, 1);
3247
select * from t1 where f_int1 between 5 and 15 order by f_int1;
3255
create table t1 (f_int1 integer) partition by list(abs(mod(f_int1,2)))
3256
subpartition by hash(f_int1) subpartitions 2
3258
partition part1 values in (0),
3259
partition part2 values in (1),
3260
partition part4 values in (null)
3262
insert into t1 set f_int1 = null;
3263
select * from t1 where f_int1 is null;
3266
explain partitions select * from t1 where f_int1 is null;
3267
id select_type table partitions type possible_keys key key_len ref rows Extra
3268
1 SIMPLE t1 part4_part4sp0 system NULL NULL NULL NULL 1 NULL
3270
create table t1 (a int not null, b int not null)
3271
partition by list(a)
3272
subpartition by hash(b) subpartitions 4
3274
partition p0 values in (1),
3275
partition p1 values in (2),
3276
partition p2 values in (3)
3278
insert into t1 values (1,1),(1,2),(1,3),(1,4),
3279
(2,1),(2,2),(2,3),(2,4);
3280
explain partitions select * from t1 where a=1 AND (b=1 OR b=2);
3281
id select_type table partitions type possible_keys key key_len ref rows Extra
3282
1 SIMPLE t1 p0_p0sp1,p0_p0sp2 ALL NULL NULL NULL NULL 2 Using where
3284
create table t1 (a int, b int not null)
3285
partition by list(a)
3286
subpartition by hash(b) subpartitions 2
3288
partition p0 values in (1),
3289
partition p1 values in (2),
3290
partition p2 values in (3),
3291
partition pn values in (NULL)
3293
insert into t1 values (1,1),(1,2),(1,3),(1,4),
3294
(2,1),(2,2),(2,3),(2,4), (NULL,1);
3295
explain partitions select * from t1 where a IS NULL AND (b=1 OR b=2);
3296
id select_type table partitions type possible_keys key key_len ref rows Extra
3297
1 SIMPLE t1 pn_pnsp0,pn_pnsp1 system NULL NULL NULL NULL 1 NULL
3298
explain partitions select * from t1 where (a IS NULL or a < 1) AND (b=1 OR b=2);
3299
id select_type table partitions type possible_keys key key_len ref rows Extra
3300
1 SIMPLE t1 pn_pnsp0,pn_pnsp1 system NULL NULL NULL NULL 1 NULL
3301
explain partitions select * from t1 where (a IS NULL or a < 2) AND (b=1 OR b=2);
3302
id select_type table partitions type possible_keys key key_len ref rows Extra
3303
1 SIMPLE t1 p0_p0sp0,p0_p0sp1,pn_pnsp0,pn_pnsp1 ALL NULL NULL NULL NULL 5 Using where
3304
explain partitions select * from t1 where (a IS NULL or a <= 1) AND (b=1 OR b=2);
3305
id select_type table partitions type possible_keys key key_len ref rows Extra
3306
1 SIMPLE t1 p0_p0sp0,p0_p0sp1,pn_pnsp0,pn_pnsp1 ALL NULL NULL NULL NULL 5 Using where
3308
create table t1 ( a int) partition by list (MOD(a, 10))
3309
( partition p0 values in (0), partition p1 values in (1),
3310
partition p2 values in (2), partition p3 values in (3),
3311
partition p4 values in (4), partition p5 values in (5),
3312
partition p6 values in (6), partition pn values in (NULL)
3314
insert into t1 values (NULL), (0),(1),(2),(3),(4),(5),(6);
3315
explain partitions select * from t1 where a is null or a < 2;
3316
id select_type table partitions type possible_keys key key_len ref rows Extra
3317
1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,pn ALL NULL NULL NULL NULL 8 Using where
3319
create table t1 (s1 int) partition by list (s1)
3320
(partition p1 values in (0),
3321
partition p2 values in (1),
3322
partition p3 values in (null));
3323
insert into t1 values (0),(1),(null);
3324
select count(*) from t1 where s1 < 0 or s1 is null;
3327
explain partitions select count(*) from t1 where s1 < 0 or s1 is null;
3328
id select_type table partitions type possible_keys key key_len ref rows Extra
3329
1 SIMPLE t1 p3 system NULL NULL NULL NULL 1 NULL
3331
create table t1 (a char(32) primary key)
3334
insert into t1 values ('na');
3338
select * from t1 where a like 'n%';
3342
create table t1 (s1 varchar(15)) partition by key (s1);
3343
select * from t1 where s1 = 0 or s1 is null;
3345
insert into t1 values ('aa'),('bb'),('0');
3346
explain partitions select * from t1 where s1 = 0 or s1 is null;
3347
id select_type table partitions type possible_keys key key_len ref rows Extra
3348
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 3 Using where
3350
create table t2 (a int, b int)
3351
partition by LIST(a)
3352
subpartition by HASH(b) subpartitions 40
3353
( partition p_0_long_partition_name values in(1),
3354
partition p_1_long_partition_name values in(2));
3355
insert into t2 values (1,1),(2,2);
3356
explain partitions select * from t2;
3360
partitions p_0_long_partition_name_p_0_long_partition_namesp0,p_0_long_partition_name_p_0_long_partition_namesp1,p_0_long_partition_name_p_0_long_partition_namesp2,p_0_long_partition_name_p_0_long_partition_namesp3,p_0_long_partition_name_p_0_long_partition_namesp4,p_0_long_partition_name_p_0_long_partition_namesp5,p_0_long_partition_name_p_0_long_partition_namesp6,p_0_long_partition_name_p_0_long_partition_namesp7,p_0_long_partition_name_p_0_long_partition_namesp8,p_0_long_partition_name_p_0_long_partition_namesp9,p_0_long_partition_name_p_0_long_partition_namesp10,p_0_long_partition_name_p_0_long_partition_namesp11,p_0_long_partition_name_p_0_long_partition_namesp12,p_0_long_partition_name_p_0_long_partition_namesp13,p_0_long_partition_name_p_0_long_partition_namesp14,p_0_long_partition_name_p_0_long_partition_namesp15,p_0_long_partition_name_p_0_long_partition_namesp16,p_0_long_partition_name_p_0_long_partition_namesp17,p_0_long_partition_name_p_0_long_partition_namesp18,p_0_long_partition_name_p_0_long_partition_namesp19,p_0_long_partition_name_p_0_long_partition_namesp20,p_0_long_partition_name_p_0_long_partition_namesp21,p_0_long_partition_name_p_0_long_partition_namesp22,p_0_long_partition_name_p_0_long_partition_namesp23,p_0_long_partition_name_p_0_long_partition_namesp24,p_0_long_partition_name_p_0_long_partition_namesp25,p_0_long_partition_name_p_0_long_partition_namesp26,p_0_long_partition_name_p_0_long_partition_namesp27,p_0_long_partition_name_p_0_long_partition_namesp28,p_0_long_partition_name_p_0_long_partition_namesp29,p_0_long_partition_name_p_0_long_partition_namesp30,p_0_long_partition_name_p_0_long_partition_namesp31,p_0_long_partition_name_p_0_long_partition_namesp32,p_0_long_partition_name_p_0_long_partition_namesp33,p_0_long_partition_name_p_0_long_partition_namesp34,p_0_long_partition_name_p_0_long_partition_namesp35,p_0_long_partition_name_p_0_long_partition_namesp36,p_0_long_partition_name_p_0_long_partition_namesp37,p_0_long_partition_name_p_0_long_partition_namesp38,p_0_long_partition_name_p_0_long_partition_namesp39,p_1_long_partition_name_p_1_long_partition_namesp0,p_1_long_partition_name_p_1_long_partition_namesp1,p_1_long_partition_name_p_1_long_partition_namesp2,p_1_long_partition_name_p_1_long_partition_namesp3,p_1_long_partition_name_p_1_long_partition_namesp4,p_1_long_partition_name_p_1_long_partition_namesp5,p_1_long_partition_name_p_1_long_partition_namesp6,p_1_long_partition_name_p_1_long_partition_namesp7,p_1_long_partition_name_p_1_long_partition_namesp8,p_1_long_partition_name_p_1_long_partition_namesp9,p_1_long_partition_name_p_1_long_partition_namesp10,p_1_long_partition_name_p_1_long_partition_namesp11,p_1_long_partition_name_p_1_long_partition_namesp12,p_1_long_partition_name_p_1_long_partition_namesp13,p_1_long_partition_name_p_1_long_partition_namesp14,p_1_long_partition_name_p_1_long_partition_namesp15,p_1_long_partition_name_p_1_long_partition_namesp16,p_1_long_partition_name_p_1_long_partition_namesp17,p_1_long_partition_name_p_1_long_partition_namesp18,p_1_long_partition_name_p_1_long_partition_namesp19,p_1_long_partition_name_p_1_long_partition_namesp20,p_1_long_partition_name_p_1_long_partition_namesp21,p_1_long_partition_name_p_1_long_partition_namesp22,p_1_long_partition_name_p_1_long_partition_namesp23,p_1_long_partition_name_p_1_long_partition_namesp24,p_1_long_partition_name_p_1_long_partition_namesp25,p_1_long_partition_name_p_1_long_partition_namesp26,p_1_long_partition_name_p_1_long_partition_namesp27,p_1_long_partition_name_p_1_long_partition_namesp28,p_1_long_partition_name_p_1_long_partition_namesp29,p_1_long_partition_name_p_1_long_partition_namesp30,p_1_long_partition_name_p_1_long_partition_namesp31,p_1_long_partition_name_p_1_long_partition_namesp32,p_1_long_partition_name_p_1_long_partition_namesp33,p_1_long_partition_name_p_1_long_partition_namesp34,p_1_long_partition_name_p_1_long_partition_namesp35,p_1_long_partition_name_p_1_long_partition_namesp36,p_1_long_partition_name_p_1_long_partition_namesp37,p_1_long_partition_name_p_1_long_partition_namesp38,p_1_long_partition_name_p_1_long_partition_namesp39
3369
create table t1 (s1 int);
3370
explain partitions select 1 from t1 union all select 2;
3371
id select_type table partitions type possible_keys key key_len ref rows Extra
3372
1 PRIMARY t1 NULL system NULL NULL NULL NULL 0 const row not found
3373
2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
3374
NULL UNION RESULT <union1,2> NULL ALL NULL NULL NULL NULL NULL Using temporary
3376
create table t1 (a bigint unsigned not null) partition by range(a) (
3377
partition p0 values less than (10),
3378
partition p1 values less than (100),
3379
partition p2 values less than (1000),
3380
partition p3 values less than (18446744073709551000),
3381
partition p4 values less than (18446744073709551614)
3383
insert into t1 values (5),(15),(105),(1005);
3384
insert into t1 values (18446744073709551000+1);
3385
insert into t1 values (18446744073709551614-1);
3386
explain partitions select * from t1 where a < 10;
3387
id select_type table partitions type possible_keys key key_len ref rows Extra
3388
1 SIMPLE t1 p0 system NULL NULL NULL NULL 1 NULL
3389
explain partitions select * from t1
3390
where a >= 18446744073709551000-1 and a <= 18446744073709551000+1;
3391
id select_type table partitions type possible_keys key key_len ref rows Extra
3392
1 SIMPLE t1 p3,p4 ALL NULL NULL NULL NULL 3 Using where
3393
explain partitions select * from t1
3394
where a between 18446744073709551001 and 18446744073709551002;
3395
id select_type table partitions type possible_keys key key_len ref rows Extra
3396
1 SIMPLE t1 p4 ALL NULL NULL NULL NULL 2 Using where
3397
explain partitions select * from t1 where a = 18446744073709551000;
3398
id select_type table partitions type possible_keys key key_len ref rows Extra
3399
1 SIMPLE t1 p4 ALL NULL NULL NULL NULL 2 Using where
3400
explain partitions select * from t1 where a = 18446744073709551613;
3401
id select_type table partitions type possible_keys key key_len ref rows Extra
3402
1 SIMPLE t1 p4 ALL NULL NULL NULL NULL 2 Using where
3403
explain partitions select * from t1 where a = 18446744073709551614;
3404
id select_type table partitions type possible_keys key key_len ref rows Extra
3405
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
3407
create table t1 (a int)
3408
partition by range(a) (
3409
partition p0 values less than (64),
3410
partition p1 values less than (128),
3411
partition p2 values less than (255)
3413
create table t2 (a int)
3414
partition by range(a+0) (
3415
partition p0 values less than (64),
3416
partition p1 values less than (128),
3417
partition p2 values less than (255)
3419
insert into t1 values (0x20), (0x20), (0x41), (0x41), (0xFE), (0xFE);
3420
insert into t2 values (0x20), (0x20), (0x41), (0x41), (0xFE), (0xFE);
3421
explain partitions select * from t1 where a=0;
3422
id select_type table partitions type possible_keys key key_len ref rows Extra
3423
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 2 Using where
3424
explain partitions select * from t2 where a=0;
3425
id select_type table partitions type possible_keys key key_len ref rows Extra
3426
1 SIMPLE t2 p0 ALL NULL NULL NULL NULL 2 Using where
3427
explain partitions select * from t1 where a=0xFE;
3428
id select_type table partitions type possible_keys key key_len ref rows Extra
3429
1 SIMPLE t1 p2 ALL NULL NULL NULL NULL 2 Using where
3430
explain partitions select * from t2 where a=0xFE;
3431
id select_type table partitions type possible_keys key key_len ref rows Extra
3432
1 SIMPLE t2 p2 ALL NULL NULL NULL NULL 2 Using where
3433
explain partitions select * from t1 where a > 0xFE AND a <= 0xFF;
3434
id select_type table partitions type possible_keys key key_len ref rows Extra
3435
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
3436
explain partitions select * from t2 where a > 0xFE AND a <= 0xFF;
3437
id select_type table partitions type possible_keys key key_len ref rows Extra
3438
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
3439
explain partitions select * from t1 where a >= 0xFE AND a <= 0xFF;
3440
id select_type table partitions type possible_keys key key_len ref rows Extra
3441
1 SIMPLE t1 p2 ALL NULL NULL NULL NULL 2 Using where
3442
explain partitions select * from t2 where a >= 0xFE AND a <= 0xFF;
3443
id select_type table partitions type possible_keys key key_len ref rows Extra
3444
1 SIMPLE t2 p2 ALL NULL NULL NULL NULL 2 Using where
3445
explain partitions select * from t1 where a < 64 AND a >= 63;
3446
id select_type table partitions type possible_keys key key_len ref rows Extra
3447
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 2 Using where
3448
explain partitions select * from t2 where a < 64 AND a >= 63;
3449
id select_type table partitions type possible_keys key key_len ref rows Extra
3450
1 SIMPLE t2 p0 ALL NULL NULL NULL NULL 2 Using where
3451
explain partitions select * from t1 where a <= 64 AND a >= 63;
3452
id select_type table partitions type possible_keys key key_len ref rows Extra
3453
1 SIMPLE t1 p0,p1 ALL NULL NULL NULL NULL 4 Using where
3454
explain partitions select * from t2 where a <= 64 AND a >= 63;
3455
id select_type table partitions type possible_keys key key_len ref rows Extra
3456
1 SIMPLE t2 p0,p1 ALL NULL NULL NULL NULL 4 Using where
3459
create table t1(a bigint unsigned not null) partition by range(a+0) (
3460
partition p1 values less than (10),
3461
partition p2 values less than (20),
3462
partition p3 values less than (2305561538531885056),
3463
partition p4 values less than (2305561538531950591)
3465
insert into t1 values (9),(19),(0xFFFF0000FFFF000-1), (0xFFFF0000FFFFFFF-1);
3466
insert into t1 values (9),(19),(0xFFFF0000FFFF000-1), (0xFFFF0000FFFFFFF-1);
3467
explain partitions select * from t1 where
3468
a >= 2305561538531885056-10 and a <= 2305561538531885056-8;
3469
id select_type table partitions type possible_keys key key_len ref rows Extra
3470
1 SIMPLE t1 p3 ALL NULL NULL NULL NULL 4 Using where
3471
explain partitions select * from t1 where
3472
a > 0xFFFFFFFFFFFFFFEC and a < 0xFFFFFFFFFFFFFFEE;
3473
id select_type table partitions type possible_keys key key_len ref rows Extra
3474
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
3475
explain partitions select * from t1 where a>=0 and a <= 0xFFFFFFFFFFFFFFFF;
3476
id select_type table partitions type possible_keys key key_len ref rows Extra
3477
1 SIMPLE t1 p1,p2,p3,p4 ALL NULL NULL NULL NULL 8 Using where
3479
create table t1 (a bigint) partition by range(a+0) (
3480
partition p1 values less than (-1000),
3481
partition p2 values less than (-10),
3482
partition p3 values less than (10),
3483
partition p4 values less than (1000)
3485
insert into t1 values (-15),(-5),(5),(15),(-15),(-5),(5),(15);
3486
explain partitions select * from t1 where a>-2 and a <=0;
3487
id select_type table partitions type possible_keys key key_len ref rows Extra
3488
1 SIMPLE t1 p3 ALL NULL NULL NULL NULL 4 Using where
3490
CREATE TABLE t1 ( recdate DATETIME NOT NULL )
3491
PARTITION BY RANGE( TO_DAYS(recdate) ) (
3492
PARTITION p0 VALUES LESS THAN ( TO_DAYS('2007-03-08') ),
3493
PARTITION p1 VALUES LESS THAN ( TO_DAYS('2007-04-01') )
3495
INSERT INTO t1 VALUES ('2007-03-01 12:00:00');
3496
INSERT INTO t1 VALUES ('2007-03-07 12:00:00');
3497
INSERT INTO t1 VALUES ('2007-03-08 12:00:00');
3498
INSERT INTO t1 VALUES ('2007-03-15 12:00:00');
3500
explain partitions select * from t1 where recdate < '2007-03-08 00:00:00';
3501
id select_type table partitions type possible_keys key key_len ref rows Extra
3502
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 2 Using where
3504
CREATE TABLE t1 ( recdate DATETIME NOT NULL )
3505
PARTITION BY RANGE( YEAR(recdate) ) (
3506
PARTITION p0 VALUES LESS THAN (2006),
3507
PARTITION p1 VALUES LESS THAN (2007)
3509
INSERT INTO t1 VALUES ('2005-03-01 12:00:00');
3510
INSERT INTO t1 VALUES ('2005-03-01 12:00:00');
3511
INSERT INTO t1 VALUES ('2006-03-01 12:00:00');
3512
INSERT INTO t1 VALUES ('2006-03-01 12:00:00');
3514
explain partitions select * from t1 where recdate < '2006-01-01 00:00:00';
3515
id select_type table partitions type possible_keys key key_len ref rows Extra
3516
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 2 Using where
3519
# BUG#33730 Full table scan instead selected partitions for query more than 10 partitions
3521
create table t0 (a int);
3522
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
3523
create table t1 (a int)
3524
partition by range(a+0) (
3525
partition p0 values less than (64),
3526
partition p1 values less than (128),
3527
partition p2 values less than (255)
3529
insert into t1 select A.a + 10*B.a from t0 A, t0 B;
3530
explain partitions select * from t1 where a between 10 and 13;
3531
id select_type table partitions type possible_keys key key_len ref rows Extra
3532
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 64 Using where
3533
explain partitions select * from t1 where a between 10 and 10+33;
3534
id select_type table partitions type possible_keys key key_len ref rows Extra
3535
1 SIMPLE t1 p0,p1,p2 ALL NULL NULL NULL NULL 100 Using where