Partitions outside of date range is included

[Marius S]

Hi,

I have just tried to configure partitions based on date, but it seems that mysql still includes a partition with no relevant data. It will use the relevant
partition but also include the oldest for some reason. Am I doing it wrong?

The version is 5.1.44 (MyISAM)

I first made a few partitions based on "day", which is of type "date"

ALTER TABLE ptest
PARTITION BY RANGE(TO_DAYS(day))
(
PARTITION p1 VALUES LESS THAN (TO_DAYS('2009-08-01')),
PARTITION p2 VALUES LESS THAN (TO_DAYS('2009-11-01')),
PARTITION p3 VALUES LESS THAN (TO_DAYS('2010-02-01')),
PARTITION p4 VALUES LESS THAN (TO_DAYS('2010-05-01'))
);

After a query, I find that it uses the "old" partition, that should not contain any relevant data.

mysql> explain partitions select * from ptest where day between '2010-03-11' and '2010-03-12';
+----+-------------+------------+------------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | ptest | p1,p4 | range | day | day | 3 | NULL | 79 | Using where |
+----+-------------+------------+------------+-------+---------------+------+---------+------+------+-------------+


When I select a single day, it works:

mysql> explain partitions select * from ptest where day = '2010-03-11';
+----+-------------+------------+------------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+-------+------+-------+
| 1 | SIMPLE | ptest | p4 | ref | day | day | 3 | const | 39 | |
+----+-------------+------------+------------+------+---------------+------+---------+-------+------+-------+