MySQL Bugs: #20131: Partitioning by DATE/DATETIME doesn't work

Bug #20131	Partitioning by DATE/DATETIME doesn't work
Submitted:	29 May 2006 19:24	Modified:	19 Jun 2006 9:48
Reporter:	Jeremy Cole (Basic Quality Contributor) (OCA)	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S4 (Feature request)
Version:	5.1.9	OS:	Any
Assigned to:	Mikael Ronström	CPU Architecture:	Any

Description:
Partitioning by DATE and DATETIME (arguably, one of the more useful things to do with partitioning) do not natively work in 5.1.9.  You must convert the date to an integer first in order to partition.

How to repeat:
CREATE TABLE t (
  d DATETIME
)
PARTITION BY RANGE(d) (
  PARTITION p_200601 VALUES LESS THAN ('2006-02-01 00:00:00'),
  PARTITION p_200602 VALUES LESS THAN ('2006-03-01 00:00:00'),
  PARTITION p_200603 VALUES LESS THAN ('2006-04-01 00:00:00')
);

or

CREATE TABLE t (
  d DATE
)
PARTITION BY RANGE(d) (
  PARTITION p_200601 VALUES LESS THAN ('2006-02-01'),
  PARTITION p_200602 VALUES LESS THAN ('2006-03-01'),
  PARTITION p_200603 VALUES LESS THAN ('2006-04-01')
);

Both result in the same error:

ERROR 1064 (42000): VALUES value must be of same type as partition function near '),

Suggested fix:
Make partitioning understand DATE and DATETIME.

Setting Category to Partitioning.

Thank you for a problem report. Yes, the same problem exists even in latest 51.12-BK build:

openxs@suse:~/dbs/5.1> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.1.12-beta-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE t (
    ->   d DATETIME
    -> )
    -> PARTITION BY RANGE(d) (
    ->   PARTITION p_200601 VALUES LESS THAN ('2006-02-01 00:00:00'),
    ->   PARTITION p_200602 VALUES LESS THAN ('2006-03-01 00:00:00'),
    ->   PARTITION p_200603 VALUES LESS THAN ('2006-04-01 00:00:00')
    -> );
ERROR 1064 (42000): VALUES value must be of same type as partition function near
 '),
  PARTITION p_200602 VALUES LESS THAN ('2006-03-01 00:00:00'),
  PARTITION p_' at line 5
mysql> CREATE TABLE t (
    ->   d DATE
    -> )
    -> PARTITION BY RANGE(d) (
    ->   PARTITION p_200601 VALUES LESS THAN ('2006-02-01'),
    ->   PARTITION p_200602 VALUES LESS THAN ('2006-03-01'),
    ->   PARTITION p_200603 VALUES LESS THAN ('2006-04-01')
    -> );
ERROR 1064 (42000): VALUES value must be of same type as partition function near
 '),
  PARTITION p_200602 VALUES LESS THAN ('2006-03-01'),
  PARTITION p_200603 VA' at line 5

But this limitation is documented (http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations.html):

"A partitioning key must be either an integer column or an expression that resolves to an integer."

So, I think, this is a reasonable feature request, but not a bug, formally.

Raising its priority a bit to make it tractable

I'd like this to be fixed, partitioning by date is a common requirement

This looks like a duplicate of bug#13436 "Partitions: most data types don't work".

Partition by date can be accomplished nicely by using the partition function
to_days(date). This is supported by optimiser so this is more a documentation
effort, I will blog about it. Datetime is supported by the same function and
also year(date) and year(datetime) will work fine. Potentially there could be
a need of a new function to_seconds(datetime) to handle finer granularity
than days.

I think that MySQL 5.1 should be able to use unix_timestamp() as partitioning function instead of to_seconds.

Assume 'created' column is datetime type, 
'partition by range (unix_timestamp(created))' display the error as below,

ERROR 1564 (HY000): This partition function is not allowed

the partition by range on day(d) function doesn't work, where d is a datetime type.

table:

`time`  datetime,
`id`     bigint,

both are p-keys.

alter table summary_201204
partition by range (day(`time`))
subpartition by key(`id`)
subpartitions 5 (
partition p0 values less than (6),
partition p1 values less than (11),
partition p2 values less than (16),
partition p3 values less than (21),
partition p4 values less than (26),
partition p5 values less than (MAXVALUE) );

use explain partition:
explain partitions select * from summary_201204 where `time` < '2012-04-21';
+----+-------------+-----------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+---------------+--------+---------+------+--------+-------------+
| id | select_type | table                 | partitions                                                                                                                                                                                                                                                                    | type  | possible_keys | key    | key_len | ref  | rows   | Extra       |
+----+-------------+-----------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+---------------+--------+---------+------+--------+-------------+
|  1 | SIMPLE      | summary_201204 | p0_p0sp0,p0_p0sp1,p0_p0sp2,p0_p0sp3,p0_p0sp4,p1_p1sp0,p1_p1sp1,p1_p1sp2,p1_p1sp3,p1_p1sp4,p2_p2sp0,p2_p2sp1,p2_p2sp2,p2_p2sp3,p2_p2sp4,p3_p3sp0,p3_p3sp1,p3_p3sp2,p3_p3sp3,p3_p3sp4,p4_p4sp0,p4_p4sp1,p4_p4sp2,p4_p4sp3,p4_p4sp4,p5_p5sp0,p5_p5sp1,p5_p5sp2,p5_p5sp3,p5_p5sp4 | range | sample        | sample | 8       | NULL | 658786 | Using where |
+----+-------------+-----------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+---------------+--------+---------+------+--------+-------------+

Hi Sam,
I can't see what you consider wrong with the day-function.

You select time < '2012-04-21'
This means that the query can be true for rows in all
partitions.

The day-function is not a constantly increasing or decreasing function
which makes it tough to do any partition pruning based on it.

Thanks Mikael,

I see what you saying.

So basically if using day() in partition with a datetime data type column, the corresponding SELECT statement will need to use or conver the datetime column to DATE type in order to stop the pruning.

in this case, instead of using `time` < '2012-04-21', need to use date(`time`) < '2012-04-21' or day(`time`) <21.

very tricky though.

thanks.