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
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 range PRIMARY PRIMARY 4 NULL 9 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 range PRIMARY PRIMARY 4 NULL 9 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 range PRIMARY PRIMARY 4 NULL 9 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
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
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
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
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
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 range PRIMARY PRIMARY 4 NULL 8 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 range PRIMARY PRIMARY 4 NULL 8 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 range PRIMARY PRIMARY 4 NULL 8 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
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
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
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
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 range a a 4 NULL 3 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 range a a 4 NULL 3 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 4 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
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 range a a 4 NULL 3 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 range a a 4 NULL 3 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 4 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 4 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 where; 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 5 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 5 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 5 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 range a a 4 NULL 3 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 range a a 4 NULL 3 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 range a a 4 NULL 3 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
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 4 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
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 where; 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 5 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 5 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 5 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 4 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
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 4 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
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 where; 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 5 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 5 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 5 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 4 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
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 12 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 12 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 12 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 12 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 12 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 12 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 12 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 12 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 12 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 12 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 12 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 12 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 12 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 12 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 12 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 12 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 12 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 12 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 12 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 12 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 12 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 12 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 12 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 12 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 12 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 12 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 12 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 12 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 12 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 12 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 12 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 12 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 12 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 12 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 12 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 12 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 12 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 12 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 12 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 12 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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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);
2721
update t1 set a=100 where a=5;
2722
show status like 'Handler_read_rnd_next';
2724
Handler_read_rnd_next 0
2726
update t1 set a=100 where a+1=5+1;
2727
show status like 'Handler_read_rnd_next';
2729
Handler_read_rnd_next 10
2731
delete from t1 where a=5;
2732
show status like 'Handler_read_rnd_next';
2734
Handler_read_rnd_next 0
2736
delete from t1 where a+1=5+1;
2737
show status like 'Handler_read_rnd_next';
2739
Handler_read_rnd_next 10
2740
create table t2 like t1;
2741
insert into t2 select * from t2;
2743
update t1,t2 set t1.a=1000, t2.a=1000 where t1.a=5 and t2.a=5;
2744
show status like 'Handler_read_rnd_next';
2746
Handler_read_rnd_next 0
2748
delete t1,t2 from t1, t2 where t1.a=5 and t2.a=5;
2749
show status like 'Handler_read_rnd_next';
2751
Handler_read_rnd_next 0
2754
`a` int(11) default NULL
2756
INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
2758
`a` int(11) default NULL,
2761
insert into t2 select A.a + 10*(B.a + 10* C.a) from t1 A, t1 B, t1 C ;
2762
insert into t1 select a from t2;
2765
`a` int(11) default NULL,
2766
`b` int(11) default NULL
2768
PARTITION BY RANGE (a) (
2769
PARTITION p0 VALUES LESS THAN (200),
2770
PARTITION p1 VALUES LESS THAN (400),
2771
PARTITION p2 VALUES LESS THAN (600),
2772
PARTITION p3 VALUES LESS THAN (800),
2773
PARTITION p4 VALUES LESS THAN (1001));
2774
insert into t2 select a,1 from t1 where a < 200;
2775
insert into t2 select a,2 from t1 where a >= 200 and a < 400;
2776
insert into t2 select a,3 from t1 where a >= 400 and a < 600;
2777
insert into t2 select a,4 from t1 where a >= 600 and a < 800;
2778
insert into t2 select a,5 from t1 where a >= 800 and a < 1001;
2779
explain partitions select * from t2;
2780
id select_type table partitions type possible_keys key key_len ref rows Extra
2781
1 SIMPLE t2 p0,p1,p2,p3,p4 ALL NULL NULL NULL NULL 1010
2782
explain partitions select * from t2 where a < 801 and a > 200;
2783
id select_type table partitions type possible_keys key key_len ref rows Extra
2784
1 SIMPLE t2 p1,p2,p3,p4 ALL NULL NULL NULL NULL 800 Using where
2785
explain partitions select * from t2 where a < 801 and a > 800;
2786
id select_type table partitions type possible_keys key key_len ref rows Extra
2787
1 SIMPLE t2 p4 ALL NULL NULL NULL NULL 200 Using where
2788
explain partitions select * from t2 where a > 600;
2789
id select_type table partitions type possible_keys key key_len ref rows Extra
2790
1 SIMPLE t2 p3,p4 ALL NULL NULL NULL NULL 400 Using where
2791
explain partitions select * from t2 where a > 600 and b = 1;
2792
id select_type table partitions type possible_keys key key_len ref rows Extra
2793
1 SIMPLE t2 p3,p4 ALL NULL NULL NULL NULL 400 Using where
2794
explain partitions select * from t2 where a > 600 and b = 4;
2795
id select_type table partitions type possible_keys key key_len ref rows Extra
2796
1 SIMPLE t2 p3,p4 ALL NULL NULL NULL NULL 400 Using where
2797
explain partitions select * from t2 where a > 600 and b = 5;
2798
id select_type table partitions type possible_keys key key_len ref rows Extra
2799
1 SIMPLE t2 p3,p4 ALL NULL NULL NULL NULL 400 Using where
2800
explain partitions select * from t2 where b = 5;
2801
id select_type table partitions type possible_keys key key_len ref rows Extra
2802
1 SIMPLE t2 p0,p1,p2,p3,p4 ALL NULL NULL NULL NULL 1010 Using where
2804
update t2 set b = 100 where b = 6;
2805
show status like 'Handler_read_rnd_next';
2807
Handler_read_rnd_next 1015
2809
update t2 set a = 1002 where a = 1001;
2810
show status like 'Handler_read_rnd_next';
2812
Handler_read_rnd_next 0
2814
update t2 set b = 6 where a = 600;
2815
show status like 'Handler_read_rnd_next';
2817
Handler_read_rnd_next 201
2819
update t2 set b = 6 where a > 600 and a < 800;
2820
show status like 'Handler_read_rnd_next';
2822
Handler_read_rnd_next 201
2824
delete from t2 where a > 600;
2825
show status like 'Handler_read_rnd_next';
2827
Handler_read_rnd_next 402
2830
`a` int(11) default NULL,
2831
`b` int(11) default NULL,
2834
PARTITION BY RANGE (a) (
2835
PARTITION p0 VALUES LESS THAN (200),
2836
PARTITION p1 VALUES LESS THAN (400),
2837
PARTITION p2 VALUES LESS THAN (600),
2838
PARTITION p3 VALUES LESS THAN (800),
2839
PARTITION p4 VALUES LESS THAN (1001));
2840
insert into t2 select a,1 from t1 where a < 100;
2841
insert into t2 select a,2 from t1 where a >= 200 and a < 300;
2842
insert into t2 select a,3 from t1 where a >= 300 and a < 400;
2843
insert into t2 select a,4 from t1 where a >= 400 and a < 500;
2844
insert into t2 select a,5 from t1 where a >= 500 and a < 600;
2845
insert into t2 select a,6 from t1 where a >= 600 and a < 700;
2846
insert into t2 select a,7 from t1 where a >= 700 and a < 800;
2847
insert into t2 select a,8 from t1 where a >= 800 and a < 900;
2848
insert into t2 select a,9 from t1 where a >= 900 and a < 1001;
2849
explain partitions select * from t2;
2850
id select_type table partitions type possible_keys key key_len ref rows Extra
2851
1 SIMPLE t2 p0,p1,p2,p3,p4 ALL NULL NULL NULL NULL 910
2852
explain partitions select * from t2 where a = 101;
2853
id select_type table partitions type possible_keys key key_len ref rows Extra
2854
1 SIMPLE t2 p0 ALL NULL NULL NULL NULL 110 Using where
2855
explain partitions select * from t2 where a = 550;
2856
id select_type table partitions type possible_keys key key_len ref rows Extra
2857
1 SIMPLE t2 p2 ALL NULL NULL NULL NULL 200 Using where
2858
explain partitions select * from t2 where a = 833;
2859
id select_type table partitions type possible_keys key key_len ref rows Extra
2860
1 SIMPLE t2 p4 ALL NULL NULL NULL NULL 200 Using where
2861
explain partitions select * from t2 where (a = 100 OR a = 900);
2862
id select_type table partitions type possible_keys key key_len ref rows Extra
2863
1 SIMPLE t2 p0,p4 ALL NULL NULL NULL NULL 310 Using where
2864
explain partitions select * from t2 where (a > 100 AND a < 600);
2865
id select_type table partitions type possible_keys key key_len ref rows Extra
2866
1 SIMPLE t2 p0,p1,p2 ALL NULL NULL NULL NULL 510 Using where
2867
explain partitions select * from t2 where b = 4;
2868
id select_type table partitions type possible_keys key key_len ref rows Extra
2869
1 SIMPLE t2 p0,p1,p2,p3,p4 ref b b 5 const 76 Using where
2870
explain extended select * from t2 where b = 6;
2871
id select_type table type possible_keys key key_len ref rows filtered Extra
2872
1 SIMPLE t2 ref b b 5 const 76 100.00 Using where
2874
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` = 6)
2875
explain partitions select * from t2 where b = 6;
2876
id select_type table partitions type possible_keys key key_len ref rows Extra
2877
1 SIMPLE t2 p0,p1,p2,p3,p4 ref b b 5 const 76 Using where
2878
explain extended select * from t2 where b in (1,3,5);
2879
id select_type table type possible_keys key key_len ref rows filtered Extra
2880
1 SIMPLE t2 ALL b NULL NULL NULL 910 40.66 Using where
2882
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` in (1,3,5))
2883
explain partitions select * from t2 where b in (1,3,5);
2884
id select_type table partitions type possible_keys key key_len ref rows Extra
2885
1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where
2886
explain extended select * from t2 where b in (2,4,6);
2887
id select_type table type possible_keys key key_len ref rows filtered Extra
2888
1 SIMPLE t2 ALL b NULL NULL NULL 910 25.05 Using where
2890
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` in (2,4,6))
2891
explain partitions select * from t2 where b in (2,4,6);
2892
id select_type table partitions type possible_keys key key_len ref rows Extra
2893
1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where
2894
explain extended select * from t2 where b in (7,8,9);
2895
id select_type table type possible_keys key key_len ref rows filtered Extra
2896
1 SIMPLE t2 ALL b NULL NULL NULL 910 36.70 Using where
2898
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` in (7,8,9))
2899
explain partitions select * from t2 where b in (7,8,9);
2900
id select_type table partitions type possible_keys key key_len ref rows Extra
2901
1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where
2902
explain extended select * from t2 where b > 5;
2903
id select_type table type possible_keys key key_len ref rows filtered Extra
2904
1 SIMPLE t2 ALL b NULL NULL NULL 910 44.84 Using where
2906
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` > 5)
2907
explain partitions select * from t2 where b > 5;
2908
id select_type table partitions type possible_keys key key_len ref rows Extra
2909
1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where
2910
explain extended select * from t2 where b > 5 and b < 8;
2911
id select_type table type possible_keys key key_len ref rows filtered Extra
2912
1 SIMPLE t2 ALL b NULL NULL NULL 910 22.09 Using where
2914
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where ((`test`.`t2`.`b` > 5) and (`test`.`t2`.`b` < 8))
2915
explain partitions select * from t2 where b > 5 and b < 8;
2916
id select_type table partitions type possible_keys key key_len ref rows Extra
2917
1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where
2918
explain extended select * from t2 where b > 5 and b < 7;
2919
id select_type table type possible_keys key key_len ref rows filtered Extra
2920
1 SIMPLE t2 range b b 5 NULL 76 100.00 Using where
2922
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where ((`test`.`t2`.`b` > 5) and (`test`.`t2`.`b` < 7))
2923
explain partitions select * from t2 where b > 5 and b < 7;
2924
id select_type table partitions type possible_keys key key_len ref rows Extra
2925
1 SIMPLE t2 p0,p1,p2,p3,p4 range b b 5 NULL 76 Using where
2926
explain extended select * from t2 where b > 0 and b < 5;
2927
id select_type table type possible_keys key key_len ref rows filtered Extra
2928
1 SIMPLE t2 ALL b NULL NULL NULL 910 41.65 Using where
2930
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where ((`test`.`t2`.`b` > 0) and (`test`.`t2`.`b` < 5))
2931
explain partitions select * from t2 where b > 0 and b < 5;
2932
id select_type table partitions type possible_keys key key_len ref rows Extra
2933
1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where
2935
update t2 set a = 111 where b = 10;
2936
show status like 'Handler_read_rnd_next';
2938
Handler_read_rnd_next 0
2939
show status like 'Handler_read_key';
2943
update t2 set a = 111 where b in (5,6);
2944
show status like 'Handler_read_rnd_next';
2946
Handler_read_rnd_next 915
2947
show status like 'Handler_read_key';
2951
update t2 set a = 222 where b = 7;
2952
show status like 'Handler_read_rnd_next';
2954
Handler_read_rnd_next 0
2955
show status like 'Handler_read_key';
2959
delete from t2 where b = 7;
2960
show status like 'Handler_read_rnd_next';
2962
Handler_read_rnd_next 0
2963
show status like 'Handler_read_key';
2967
delete from t2 where b > 5;
2968
show status like 'Handler_read_rnd_next';
2970
Handler_read_rnd_next 1215
2971
show status like 'Handler_read_key';
2974
show status like 'Handler_read_prev';
2977
show status like 'Handler_read_next';
2981
delete from t2 where b < 5 or b > 3;
2982
show status like 'Handler_read_rnd_next';
2984
Handler_read_rnd_next 1215
2985
show status like 'Handler_read_key';
2988
show status like 'Handler_read_prev';
2991
show status like 'Handler_read_next';
2995
create table t1 ( f_int1 mediumint, f_int2 integer)
2996
partition by list(mod(f_int1,4)) (
2997
partition p_3 values in (-3),
2998
partition p_2 values in (-2),
2999
partition p_1 values in (-1),
3000
partition p0 values in (0),
3001
partition p1 values in (1),
3002
partition p2 values in (2),
3003
partition p3 values in (3)
3005
insert into t1 values (9, 9), (8, 8), (7, 7), (6, 6), (5, 5),
3006
(4, 4), (3, 3), (2, 2), (1, 1);
3007
select * from t1 where f_int1 between 5 and 15 order by f_int1;
3015
create table t1 (f_int1 integer) partition by list(abs(mod(f_int1,2)))
3016
subpartition by hash(f_int1) subpartitions 2
3018
partition part1 values in (0),
3019
partition part2 values in (1),
3020
partition part4 values in (null)
3022
insert into t1 set f_int1 = null;
3023
select * from t1 where f_int1 is null;
3026
explain partitions select * from t1 where f_int1 is null;
3027
id select_type table partitions type possible_keys key key_len ref rows Extra
3028
1 SIMPLE t1 part4_part4sp0 system NULL NULL NULL NULL 1
3030
create table t1 (a int not null, b int not null)
3031
partition by list(a)
3032
subpartition by hash(b) subpartitions 4
3034
partition p0 values in (1),
3035
partition p1 values in (2),
3036
partition p2 values in (3)
3038
insert into t1 values (1,1),(1,2),(1,3),(1,4),
3039
(2,1),(2,2),(2,3),(2,4);
3040
explain partitions select * from t1 where a=1 AND (b=1 OR b=2);
3041
id select_type table partitions type possible_keys key key_len ref rows Extra
3042
1 SIMPLE t1 p0_p0sp1,p0_p0sp2 ALL NULL NULL NULL NULL 2 Using where
3044
create table t1 (a int, b int not null)
3045
partition by list(a)
3046
subpartition by hash(b) subpartitions 2
3048
partition p0 values in (1),
3049
partition p1 values in (2),
3050
partition p2 values in (3),
3051
partition pn values in (NULL)
3053
insert into t1 values (1,1),(1,2),(1,3),(1,4),
3054
(2,1),(2,2),(2,3),(2,4), (NULL,1);
3055
explain partitions select * from t1 where a IS NULL AND (b=1 OR b=2);
3056
id select_type table partitions type possible_keys key key_len ref rows Extra
3057
1 SIMPLE t1 pn_pnsp0,pn_pnsp1 system NULL NULL NULL NULL 1
3058
explain partitions select * from t1 where (a IS NULL or a < 1) AND (b=1 OR b=2);
3059
id select_type table partitions type possible_keys key key_len ref rows Extra
3060
1 SIMPLE t1 pn_pnsp0,pn_pnsp1 system NULL NULL NULL NULL 1
3061
explain partitions select * from t1 where (a IS NULL or a < 2) AND (b=1 OR b=2);
3062
id select_type table partitions type possible_keys key key_len ref rows Extra
3063
1 SIMPLE t1 p0_p0sp0,p0_p0sp1,pn_pnsp0,pn_pnsp1 ALL NULL NULL NULL NULL 5 Using where
3064
explain partitions select * from t1 where (a IS NULL or a <= 1) AND (b=1 OR b=2);
3065
id select_type table partitions type possible_keys key key_len ref rows Extra
3066
1 SIMPLE t1 p0_p0sp0,p0_p0sp1,pn_pnsp0,pn_pnsp1 ALL NULL NULL NULL NULL 5 Using where
3068
create table t1 ( a int) partition by list (MOD(a, 10))
3069
( partition p0 values in (0), partition p1 values in (1),
3070
partition p2 values in (2), partition p3 values in (3),
3071
partition p4 values in (4), partition p5 values in (5),
3072
partition p6 values in (6), partition pn values in (NULL)
3074
insert into t1 values (NULL), (0),(1),(2),(3),(4),(5),(6);
3075
explain partitions select * from t1 where a is null or a < 2;
3076
id select_type table partitions type possible_keys key key_len ref rows Extra
3077
1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,pn ALL NULL NULL NULL NULL 8 Using where
3079
create table t1 (s1 int) partition by list (s1)
3080
(partition p1 values in (0),
3081
partition p2 values in (1),
3082
partition p3 values in (null));
3083
insert into t1 values (0),(1),(null);
3084
select count(*) from t1 where s1 < 0 or s1 is null;
3087
explain partitions select count(*) from t1 where s1 < 0 or s1 is null;
3088
id select_type table partitions type possible_keys key key_len ref rows Extra
3089
1 SIMPLE t1 p3 system NULL NULL NULL NULL 1
3091
create table t1 (a char(32) primary key)
3094
insert into t1 values ('na');
3098
select * from t1 where a like 'n%';
3102
create table t1 (s1 varchar(15)) partition by key (s1);
3103
select * from t1 where s1 = 0 or s1 is null;
3105
insert into t1 values ('aa'),('bb'),('0');
3106
explain partitions select * from t1 where s1 = 0 or s1 is null;
3107
id select_type table partitions type possible_keys key key_len ref rows Extra
3108
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 3 Using where
3110
create table t2 (a int, b int)
3111
partition by LIST(a)
3112
subpartition by HASH(b) subpartitions 40
3113
( partition p_0_long_partition_name values in(1),
3114
partition p_1_long_partition_name values in(2));
3115
insert into t2 values (1,1),(2,2);
3116
explain partitions select * from t2;
3120
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
3129
create table t1 (s1 int);
3130
explain partitions select 1 from t1 union all select 2;
3131
id select_type table partitions type possible_keys key key_len ref rows Extra
3132
1 PRIMARY t1 NULL system NULL NULL NULL NULL 0 const row not found
3133
2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
3134
NULL UNION RESULT <union1,2> NULL ALL NULL NULL NULL NULL NULL
3136
create table t1 (a bigint unsigned not null) partition by range(a) (
3137
partition p0 values less than (10),
3138
partition p1 values less than (100),
3139
partition p2 values less than (1000),
3140
partition p3 values less than (18446744073709551000),
3141
partition p4 values less than (18446744073709551614)
3143
insert into t1 values (5),(15),(105),(1005);
3144
insert into t1 values (18446744073709551000+1);
3145
insert into t1 values (18446744073709551614-1);
3146
explain partitions select * from t1 where a < 10;
3147
id select_type table partitions type possible_keys key key_len ref rows Extra
3148
1 SIMPLE t1 p0 system NULL NULL NULL NULL 1
3149
explain partitions select * from t1
3150
where a >= 18446744073709551000-1 and a <= 18446744073709551000+1;
3151
id select_type table partitions type possible_keys key key_len ref rows Extra
3152
1 SIMPLE t1 p3,p4 ALL NULL NULL NULL NULL 3 Using where
3153
explain partitions select * from t1
3154
where a between 18446744073709551001 and 18446744073709551002;
3155
id select_type table partitions type possible_keys key key_len ref rows Extra
3156
1 SIMPLE t1 p4 ALL NULL NULL NULL NULL 2 Using where
3157
explain partitions select * from t1 where a = 18446744073709551000;
3158
id select_type table partitions type possible_keys key key_len ref rows Extra
3159
1 SIMPLE t1 p4 ALL NULL NULL NULL NULL 2 Using where
3160
explain partitions select * from t1 where a = 18446744073709551613;
3161
id select_type table partitions type possible_keys key key_len ref rows Extra
3162
1 SIMPLE t1 p4 ALL NULL NULL NULL NULL 2 Using where
3163
explain partitions select * from t1 where a = 18446744073709551614;
3164
id select_type table partitions type possible_keys key key_len ref rows Extra
3165
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
3167
create table t1 (a int)
3168
partition by range(a) (
3169
partition p0 values less than (64),
3170
partition p1 values less than (128),
3171
partition p2 values less than (255)
3173
create table t2 (a int)
3174
partition by range(a+0) (
3175
partition p0 values less than (64),
3176
partition p1 values less than (128),
3177
partition p2 values less than (255)
3179
insert into t1 values (0x20), (0x20), (0x41), (0x41), (0xFE), (0xFE);
3180
insert into t2 values (0x20), (0x20), (0x41), (0x41), (0xFE), (0xFE);
3181
explain partitions select * from t1 where a=0;
3182
id select_type table partitions type possible_keys key key_len ref rows Extra
3183
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 2 Using where
3184
explain partitions select * from t2 where a=0;
3185
id select_type table partitions type possible_keys key key_len ref rows Extra
3186
1 SIMPLE t2 p0 ALL NULL NULL NULL NULL 2 Using where
3187
explain partitions select * from t1 where a=0xFE;
3188
id select_type table partitions type possible_keys key key_len ref rows Extra
3189
1 SIMPLE t1 p2 ALL NULL NULL NULL NULL 2 Using where
3190
explain partitions select * from t2 where a=0xFE;
3191
id select_type table partitions type possible_keys key key_len ref rows Extra
3192
1 SIMPLE t2 p2 ALL NULL NULL NULL NULL 2 Using where
3193
explain partitions select * from t1 where a > 0xFE AND a <= 0xFF;
3194
id select_type table partitions type possible_keys key key_len ref rows Extra
3195
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
3196
explain partitions select * from t2 where a > 0xFE AND a <= 0xFF;
3197
id select_type table partitions type possible_keys key key_len ref rows Extra
3198
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
3199
explain partitions select * from t1 where a >= 0xFE AND a <= 0xFF;
3200
id select_type table partitions type possible_keys key key_len ref rows Extra
3201
1 SIMPLE t1 p2 ALL NULL NULL NULL NULL 2 Using where
3202
explain partitions select * from t2 where a >= 0xFE AND a <= 0xFF;
3203
id select_type table partitions type possible_keys key key_len ref rows Extra
3204
1 SIMPLE t2 p2 ALL NULL NULL NULL NULL 2 Using where
3205
explain partitions select * from t1 where a < 64 AND a >= 63;
3206
id select_type table partitions type possible_keys key key_len ref rows Extra
3207
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 2 Using where
3208
explain partitions select * from t2 where a < 64 AND a >= 63;
3209
id select_type table partitions type possible_keys key key_len ref rows Extra
3210
1 SIMPLE t2 p0 ALL NULL NULL NULL NULL 2 Using where
3211
explain partitions select * from t1 where a <= 64 AND a >= 63;
3212
id select_type table partitions type possible_keys key key_len ref rows Extra
3213
1 SIMPLE t1 p0,p1 ALL NULL NULL NULL NULL 4 Using where
3214
explain partitions select * from t2 where a <= 64 AND a >= 63;
3215
id select_type table partitions type possible_keys key key_len ref rows Extra
3216
1 SIMPLE t2 p0,p1 ALL NULL NULL NULL NULL 4 Using where
3219
create table t1(a bigint unsigned not null) partition by range(a+0) (
3220
partition p1 values less than (10),
3221
partition p2 values less than (20),
3222
partition p3 values less than (2305561538531885056),
3223
partition p4 values less than (2305561538531950591)
3225
insert into t1 values (9),(19),(0xFFFF0000FFFF000-1), (0xFFFF0000FFFFFFF-1);
3226
insert into t1 values (9),(19),(0xFFFF0000FFFF000-1), (0xFFFF0000FFFFFFF-1);
3227
explain partitions select * from t1 where
3228
a >= 2305561538531885056-10 and a <= 2305561538531885056-8;
3229
id select_type table partitions type possible_keys key key_len ref rows Extra
3230
1 SIMPLE t1 p3 ALL NULL NULL NULL NULL 4 Using where
3231
explain partitions select * from t1 where
3232
a > 0xFFFFFFFFFFFFFFEC and a < 0xFFFFFFFFFFFFFFEE;
3233
id select_type table partitions type possible_keys key key_len ref rows Extra
3234
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
3235
explain partitions select * from t1 where a>=0 and a <= 0xFFFFFFFFFFFFFFFF;
3236
id select_type table partitions type possible_keys key key_len ref rows Extra
3237
1 SIMPLE t1 p1,p2,p3,p4 ALL NULL NULL NULL NULL 8 Using where
3239
create table t1 (a bigint) partition by range(a+0) (
3240
partition p1 values less than (-1000),
3241
partition p2 values less than (-10),
3242
partition p3 values less than (10),
3243
partition p4 values less than (1000)
3245
insert into t1 values (-15),(-5),(5),(15),(-15),(-5),(5),(15);
3246
explain partitions select * from t1 where a>-2 and a <=0;
3247
id select_type table partitions type possible_keys key key_len ref rows Extra
3248
1 SIMPLE t1 p3 ALL NULL NULL NULL NULL 4 Using where
3250
CREATE TABLE t1 ( recdate DATETIME NOT NULL )
3251
PARTITION BY RANGE( TO_DAYS(recdate) ) (
3252
PARTITION p0 VALUES LESS THAN ( TO_DAYS('2007-03-08') ),
3253
PARTITION p1 VALUES LESS THAN ( TO_DAYS('2007-04-01') )
3255
INSERT INTO t1 VALUES ('2007-03-01 12:00:00');
3256
INSERT INTO t1 VALUES ('2007-03-07 12:00:00');
3257
INSERT INTO t1 VALUES ('2007-03-08 12:00:00');
3258
INSERT INTO t1 VALUES ('2007-03-15 12:00:00');
3260
explain partitions select * from t1 where recdate < '2007-03-08 00:00:00';
3261
id select_type table partitions type possible_keys key key_len ref rows Extra
3262
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 2 Using where
3264
CREATE TABLE t1 ( recdate DATETIME NOT NULL )
3265
PARTITION BY RANGE( YEAR(recdate) ) (
3266
PARTITION p0 VALUES LESS THAN (2006),
3267
PARTITION p1 VALUES LESS THAN (2007)
3269
INSERT INTO t1 VALUES ('2005-03-01 12:00:00');
3270
INSERT INTO t1 VALUES ('2005-03-01 12:00:00');
3271
INSERT INTO t1 VALUES ('2006-03-01 12:00:00');
3272
INSERT INTO t1 VALUES ('2006-03-01 12:00:00');
3274
explain partitions select * from t1 where recdate < '2006-01-01 00:00:00';
3275
id select_type table partitions type possible_keys key key_len ref rows Extra
3276
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 2 Using where
3279
# BUG#33730 Full table scan instead selected partitions for query more than 10 partitions
3281
create table t0 (a int);
3282
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
3283
create table t1 (a int)
3284
partition by range(a+0) (
3285
partition p0 values less than (64),
3286
partition p1 values less than (128),
3287
partition p2 values less than (255)
3289
insert into t1 select A.a + 10*B.a from t0 A, t0 B;
3290
explain partitions select * from t1 where a between 10 and 13;
3291
id select_type table partitions type possible_keys key key_len ref rows Extra
3292
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 64 Using where
3293
explain partitions select * from t1 where a between 10 and 10+33;
3294
id select_type table partitions type possible_keys key key_len ref rows Extra
3295
1 SIMPLE t1 p0,p1,p2 ALL NULL NULL NULL NULL 100 Using where