MySQL Bugs: #72383: Pruning includes first partition when query range crosses month boundary

Bug #72383	Pruning includes first partition when query range crosses month boundary
Submitted:	18 Apr 2014 15:36	Modified:	9 Jun 2014 13:04
Reporter:	Marty Couvillon	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.5.37	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When table is partition using TO_DAYS and the range of dates in the query cross a month boundary, the first partition is not pruned even though it is not in the range of the query.

This bug is very similar to Bug #49754, which was closed as fixed.  The test case for that bug was fixed, but that test case did not cross a month boundary.

How to repeat:
CREATE TABLE partition_date_test (dt DATE PRIMARY KEY)
PARTITION BY RANGE (TO_DAYS(dt)) (
PARTITION day_20091028 VALUES LESS THAN (734074),
PARTITION day_20091029 VALUES LESS THAN (734075),
PARTITION day_20091030 VALUES LESS THAN (734076),
PARTITION day_20091031 VALUES LESS THAN (734077),
PARTITION day_20091101 VALUES LESS THAN (734078),
PARTITION day_20091102 VALUES LESS THAN (734079)
);
INSERT INTO partition_date_test VALUES ('2009-10-28'),('2009-10-29'),('2009-10-30'),('2009-10-31'),('2009-11-01'),('2009-11-02');

mysql> explain partitions select * from partition_date_test where dt between '2009-10-31' and '2009-11-02';
+----+-------------+---------------------+-----------------------------------------------------+
| id | select_type | table               | partitions                                          |
+----+-------------+---------------------+-----------------------------------------------------+
|  1 | SIMPLE      | partition_date_test | day_20091028,day_20091031,day_20091101,day_20091102 |.....
+----+-------------+---------------------+-----------------------------------------------------+

Query range crosses a month boundary, but day_20091028 should not be in the list of partitions

mysql> explain partitions select * from partition_date_test where dt between '2009-11-01' and '2009-11-02';
+----+-------------+---------------------+---------------------------+
| id | select_type | table               | partitions                |
+----+-------------+---------------------+---------------------------+
|  1 | SIMPLE      | partition_date_test | day_20091101,day_20091102 |.....
+----+-------------+---------------------+---------------------------+

The correct partitions are pruned after moving the first date in the query one day ahead so the query does not cross a month boundary.

Thank you for the report.

Verified as described.

Thank you for taking the time to write to us, but this is not a bug.

If I alter your test a bit, it is easier to show why.
The TO_DAYS() function will return NULL for dates with zero parts (like 2009-00-00 or 2009-11-00) and NULL will always be placed in the first partition.
So to also be able to include the rows with '00' day part, we must also search in the first partition.

So either create a special first partition which should be empty unless there are zero-dates in the table (to avoid performance penalty for searching in the first partition). Or try PARTITION BY RANGE COLUMNS which handles date columns natively:
http://dev.mysql.com/doc/refman/5.5/en/partitioning-columns.html

CREATE TABLE partition_date_test (dt DATE PRIMARY KEY)
PARTITION BY RANGE (TO_DAYS(dt)) (
PARTITION first VALUES LESS THAN (TO_DAYS('2000-01-01')),
PARTITION day_20091030 VALUES LESS THAN (TO_DAYS('2009-10-31')),
PARTITION day_20091031 VALUES LESS THAN (TO_DAYS('2009-11-01')),
PARTITION day_20091101 VALUES LESS THAN (TO_DAYS('2009-11-02')),
PARTITION day_20091102 VALUES LESS THAN (TO_DAYS('2009-11-03'))
);
INSERT INTO partition_date_test VALUES ('2009-10-28'),('2009-10-29'),('2009-10-30'),('2009-10-31'),('2009-11-01'),('2009-11-02');
INSERT INTO partition_date_test VALUES ('2009-11-00');
select * from partition_date_test where dt between '2009-10-31' and '2009-11-01';
dt
2009-10-31
2009-11-00
2009-11-01
SELECT * from partition_date_test where dt = '2009-11-00';
dt
2009-11-00
EXPLAIN PARTITIONS SELECT * from partition_date_test where dt = '2009-11-00';
id      select_type     table   partitions      type    possible_keys   key    key_len  ref     rows    Extra
1       SIMPLE  partition_date_test     first   system  PRIMARY NULL    NULL   NULL     1
SELECT TO_DAYS('2009-11-00');
TO_DAYS('2009-11-00')
NULL