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)	Email Updates:
Status:	Won't fix
Category:	Server: Partition	Severity:	S4 (Feature request)
Version:	5.1.9	OS:	Any
Assigned to:	Mikael Ronstrom	Target Version:
Triage:	D5 (Feature request)

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