|
I create a partitioned table using TO_SECONDS as a range function:
SET sql_mode='allow_invalid_dates';
|
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a DATE, KEY(a))
|
PARTITION BY RANGE (TO_SECONDS(a))
|
(PARTITION `p0001-01-01` VALUES LESS THAN (TO_SECONDS('0001-01-02')),
|
PARTITION `p1001-01-01` VALUES LESS THAN (TO_SECONDS('1001-01-02')),
|
PARTITION `p2001-01-01` VALUES LESS THAN (TO_SECONDS('2001-01-02')),
|
PARTITION `pmax` VALUES LESS THAN (MAXVALUE));
|
|
INSERT INTO t1 VALUES ('0001-01-01');
|
INSERT INTO t1 VALUES ('0001-02-30');
|
INSERT INTO t1 VALUES ('1001-01-01');
|
INSERT INTO t1 VALUES ('1001-02-30');
|
INSERT INTO t1 VALUES ('2001-01-01');
|
INSERT INTO t1 VALUES ('2001-02-30');
|
Now I want to search for '1001-02-30':
EXPLAIN PARTITIONS for this value says:
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a='1001-02-30';
|
+------+-------------+-------+-------------+------+---------------+------+---------+-------+------+-------------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------------+------+---------------+------+---------+-------+------+-------------+
|
| 1 | SIMPLE | t1 | p0001-01-01 | ref | a | a | 4 | const | 1 | Using index |
|
+------+-------------+-------+-------------+------+---------------+------+---------+-------+------+-------------+
|
1 row in set (0.00 sec)
|
It's going to search in the smallest possible partition,
probably because '1001-02-30' is an invalid date,
while TO_SECONDS() normally expects a valid date only.
Now do actual search:
SELECT * FROM t1 WHERE a='1001-02-30';
|
Empty set (0.00 sec)
|
Oops.
Let's check which partition it's actually in:
SELECT * FROM t1 PARTITION(`p2001-01-01`);
|
+------------+
|
| a |
|
+------------+
|
| 1001-02-30 |
|
| 2001-01-01 |
|
+------------+
|
2 rows in set (0.00 sec)
|
Oops. Why?
Let's check if it works without partitions:
ALTER TABLE t1 REMOVE PARTITIONING;
|
SELECT * FROM t1 WHERE a='1001-02-30';
|
+------------+
|
| a |
|
+------------+
|
| 1001-02-30 |
|
+------------+
|
1 row in set (0.00 sec)
|
It does.
Partitioning should be fixed to return the same
row set for the same WHERE clause.
|