MySQL Bugs: #49754: Partitioning by RANGE with TO_DAYS always includes first partition when pruning

Bug #49754	Partitioning by RANGE with TO_DAYS always includes first partition when pruning
Submitted:	17 Dec 2009 0:17	Modified:	16 Jan 2013 11:33
Reporter:	Matthew Montgomery	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S1 (Critical)
Version:	5.1.41	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Regardless of the range in the BETWEEN clause a table partitioned by RANGE using TO_DAYS function always includes the first partition in the table when pruning.

How to repeat:
CREATE TABLE partition_date_test (dt DATE PRIMARY KEY)
PARTITION BY RANGE (TO_DAYS(dt)) (
PARTITION day_20091015 VALUES LESS THAN (734061),
PARTITION day_20091016 VALUES LESS THAN (734062),
PARTITION day_20091017 VALUES LESS THAN (734063),
PARTITION day_20091018 VALUES LESS THAN (734064),
PARTITION day_20091019 VALUES LESS THAN (734065),
PARTITION day_20091020 VALUES LESS THAN (734066),
PARTITION max VALUES LESS THAN MAXVALUE);
INSERT INTO partition_date_test VALUES ('2009-10-15'),('2009-10-16'),('2009-10-17'),('2009-10-18'),('2009-10-19'),('2009-10-20'),('2009-10-21');
explain partitions select * from partition_date_test where dt between '2009-10-17' and '2009-10-19';
+----+-------------+---------------------+-----------------------------------------------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------+-----------------------------------------------------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | partition_date_test | day_20091015,day_20091017,day_20091018,day_20091019 | index | PRIMARY | PRIMARY | 3 | NULL | 4 | Using where; Using index |
+----+-------------+---------------------+-----------------------------------------------------+-------+---------------+---------+---------+------+------+--------------------------+

Notice partition day_20091015 in the partition list.

This is a regression from previous versions... 

mysql> use test; 
Database changed
mysql> CREATE TABLE partition_date_test (dt DATE PRIMARY KEY)
    -> PARTITION BY RANGE (TO_DAYS(dt)) (
    -> PARTITION day_20091015 VALUES LESS THAN (734061),
    -> PARTITION day_20091016 VALUES LESS THAN (734062),
    -> PARTITION day_20091017 VALUES LESS THAN (734063),
    -> PARTITION day_20091018 VALUES LESS THAN (734064),
    -> PARTITION day_20091019 VALUES LESS THAN (734065),
    -> PARTITION day_20091020 VALUES LESS THAN (734066),
    -> PARTITION max VALUES LESS THAN MAXVALUE);
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO partition_date_test VALUES ('2009-10-15'),('2009-10-16'),('2009-10-17'),('2009-10-18'),('2009-10-19'),('2009-10-20'),('2009-10-21');
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> explain partitions select * from partition_date_test where dt between '2009-10-17' and '2009-10-19';
+----+-------------+---------------------+----------------------------------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table               | partitions                             | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+---------------------+----------------------------------------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | partition_date_test | day_20091017,day_20091018,day_20091019 | index | PRIMARY       | PRIMARY | 3       | NULL |    3 | Using where; Using index |
+----+-------------+---------------------+----------------------------------------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> select version(); 
+------------+
| version()  |
+------------+
| 5.1.33-log |
+------------+
1 row in set (0.00 sec)

This is not a bug, since TO_DAYS() returns NULL for invalid dates, it needs to scan the first partition as well (since that holds all NULL values) for ranges.

for more info see bug#20577.

A performance workaround is to create a specific partition to hold all NULL values (like '... LESS THAN (0)'), which also would catch all bad dates.

Bug #50775 was marked as duplicate of this one.

I don't understand.  If the column can not contain NULL values like in this case where the column is a PRIMARY KEY which by nature is NOT NULL UNIQUE, why does the first partition have to be scanned?

Never mind, I understand.  The table may contain invalid dates which would have been stuffed into the first partition due to the partitioning function.

I am sorry this is a bug for 2 reasons :

1. the interval used by the query could not contain invalid dates. 

example :

 myTime between '2012-11-07 00:00:00' and '2012-11-07 23:59:00'

2. the table could not contain invalid dates per sql_mode : 

set sql_mode='STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE';

Fixed in 5.1+, documented in the 5.1.69, 5.5.31, 5.6.11, and 5.7.1 changelogs as
follows:

        A query on a table partitioned by range and using TO_DAYS() as a
        partitioing function always included the first partition of the
        table when pruning. This happened regardless of the range
        employed in the BETWEEN clause of such a query.

Closed.