Bug #43903 Partition pruning doesnt work for table partitioned by timestamp.

• Submitted: 27 Mar 2009 9:54
• Modified: 27 Mar 2009 10:08
• Reporter: Jay Vid
• Status: Not a Bug
• Category: MySQL Server: Partitions
• Severity: S3 (Non-critical)
• Version: 5.1
• OS: Windows
• CPU Architecture: Any
• Tags: Partition pruning, timestamp

[27 Mar 2009 9:54] Jay Vid

Description:
I have a table that is partitioned on a timestamp column using the to_days function. 

Doing selects with the '=' operator do the partition pruning appropriately but doing it with the < or > operator doesnt work. 

Changing the data type of the column to datetime fixes the problem. I currently want to use the capability to use the timestamp data type so that I can use the default current_timestamp 

How to repeat:
***** This doesnt work:
CREATE TABLE test1 (
	id bigint NOT NULL auto_increment,
	event_time timestamp NOT NULL default CURRENT_TIMESTAMP,
	INDEX test1_idx (id)
) type=InnoDB
PARTITION BY LIST (TO_DAYS(event_time)) (
	PARTITION p20090201 VALUES IN (to_days('2009-02-01'))
);

alter table test1 add partition (PARTITION p20090321 VALUES IN (to_days('2009-03-21')));
alter table test1 add partition (PARTITION p20090322 VALUES IN (to_days('2009-03-22')));
alter table test1 add partition (PARTITION p20090323 VALUES IN (to_days('2009-03-23')));
alter table test1 add partition (PARTITION p20090324 VALUES IN (to_days('2009-03-24')));
alter table test1 add partition (PARTITION p20090325 VALUES IN (to_days('2009-03-25')));
alter table test1 add partition (PARTITION p20090326 VALUES IN (to_days('2009-03-26')));
alter table test1 add partition (PARTITION p20090327 VALUES IN (to_days('2009-03-27')));

explain partitions select * from test1 where event_time > '2009-03-22' and event_time < '2009-03-23' \G; 	
explain partitions select * from test1 where event_time = '2009-03-23' \G; 	

****** This works:
CREATE TABLE test2 (
	id bigint NOT NULL auto_increment,
	event_time datetime NOT NULL,
	INDEX test2_idx (id)
) type=InnoDB
PARTITION BY LIST (TO_DAYS(event_time)) (
	PARTITION p20090201 VALUES IN (to_days('2009-02-01'))
);

alter table test2 add partition (PARTITION p20090321 VALUES IN (to_days('2009-03-21')));
alter table test2 add partition (PARTITION p20090322 VALUES IN (to_days('2009-03-22')));
alter table test2 add partition (PARTITION p20090323 VALUES IN (to_days('2009-03-23')));
alter table test2 add partition (PARTITION p20090324 VALUES IN (to_days('2009-03-24')));
alter table test2 add partition (PARTITION p20090325 VALUES IN (to_days('2009-03-25')));
alter table test2 add partition (PARTITION p20090326 VALUES IN (to_days('2009-03-26')));
alter table test2 add partition (PARTITION p20090327 VALUES IN (to_days('2009-03-27')));

explain partitions select * from test2 where event_time > '2009-03-22' and event_time < '2009-03-23' \G; 	
explain partitions select * from test2 where event_time = '2009-03-23' \G; 	

Suggested fix:
Make it work with timestamp.

[27 Mar 2009 10:08] Sveta Smirnova

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please read at http://dev.mysql.com/doc/refman/5.1/en/partitioning-pruning.html:

Pruning can also be applied for tables partitioned on a DATE or DATETIME column when the partitioning expression uses the YEAR() or TO_DAYS() function. 

So it is expected pruning does not work or partially works with TIMESTAMP columns

See bug #24245 for more details.

[27 Mar 2009 10:14] Sveta Smirnova

Additionally explain partitions select * from test1 where event_time = '2009-03-23' \G; works in wrong way, because query "select * from test1 where event_time = '2009-03-23';" returns no result. But this probably same as bug #37532