Range Partition Pruning on TO_DAYS Problem

[Dominic Edwards]

Using MySQL 5.1.4 (version issue!?) I have added two range partitions (the second dynamically using an alter statement) to a table using the TO_DAYS function and everything appears to be fine apart from when it comes to verifying the pruning of the partitions with some queries I am running. The explain partitions statements I get back are scanning both partitions when I would have expected only one to be scanned.

I am not sure whether the partitioning has been set up incorrectly or whether the select statements I am using are not optomised.

Can anybody see what is probably a basic oversight here. Code below:

1. CREATE STATEMENT:

-- ----------------------------
-- Table structure for `parameterfact`
-- ----------------------------
DROP TABLE IF EXISTS `parameterfact`;
CREATE TABLE `parameterfact` (
`TimeIdentifier` double(20,0) DEFAULT NULL,
`DateIdentifier` double(20,0) DEFAULT NULL,
`ProjectIdentifier` double(20,0) DEFAULT NULL,
`SourceIdentifier` double(20,0) DEFAULT NULL,
`SatelliteIdentifier` double(20,0) DEFAULT NULL,
`ReceiverIdentifier` double(20,0) DEFAULT NULL,
`ParameterDefinitionIdentifier` double(20,0) DEFAULT NULL,
`Value` float(40,0) DEFAULT NULL,
`Period` double(20,0) DEFAULT NULL,
`IsUpperBound` tinyint(1) DEFAULT NULL,
`IsLowerBound` tinyint(1) DEFAULT NULL,
`FactDate` date NOT NULL DEFAULT '1970-01-01',
`FactTime` time DEFAULT NULL,
KEY `TimeIdentifier` (`TimeIdentifier`),
KEY `DateIdentifier` (`DateIdentifier`),
KEY `ProjectIdentifier` (`ProjectIdentifier`),
KEY `SourceIdentifier` (`SourceIdentifier`),
KEY `SatelliteIdentifier` (`SatelliteIdentifier`),
KEY `ReceiverIdentifier` (`ReceiverIdentifier`),
KEY `ParameterDefinitionIdentifier` (`ParameterDefinitionIdentifier`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY RANGE( TO_DAYS(FactDate) ) (
PARTITION p20090801 VALUES LESS THAN (TO_DAYS('2009-08-01')) ENGINE = InnoDB) #2009-08-01
;

2. ALTER STATEMENET:

ALTER TABLE parameterfact ADD PARTITION (PARTITION p20091112 VALUES LESS THAN (TO_DAYS('2009-11-12')) ENGINE = InnoDB);

3. EXPLAIN PARTITIONS STATEMENTS:

EXPLAIN PARTITIONS select * from parameterfact where FactDate BETWEEN '2009-08-02' AND '2009-11-02';

EXPLAIN PARTITIONS select * from parameterfact where FactDate >= '2009-08-08';

Both statements return both partitions when I would have expected only the p20091112 partition to be scanned. Interestingly if the operator is changed from '>=' to just '=' in the latter statement only the p20091112 partition is scanned as I would expect.

I have a feeling I have missed something obvious here but I can't see what it might be.

Any help greatly appreciated.

D.E.