Bug #17540 Partitioning by range doesn't work with dates
Submitted: 18 Feb 2006 3:52 Modified: 3 Apr 2006 14:48
Reporter: Jeremy Cole (Basic Quality Contributor) (OCA) Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Partitions Severity:S2 (Serious)
Version:5.1.6/5.1.7 BK OS:Linux (Fedora Core 4)
Assigned to: Assigned Account CPU Architecture:Any

[18 Feb 2006 3:52] Jeremy Cole
Description:
Partitioning by RANGE doesn't work with DATE fields or DATETIME fields.

How to repeat:
CREATE TABLE t (
  d DATE NOT NULL
)
PARTITION BY RANGE(d) (
  PARTITION p0 VALUES LESS THAN ('2005-06-02')
);

Produces the following error:

ERROR 1064 (42000) at line 1: VALUES value must be of same type as partition function near ')
)' at line 5
[18 Feb 2006 17:19] MySQL Verification Team
Thank you for the bug report.

miguel@hegel:~/dbs/5.1> bin/mysql -uroot db1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.1.8-beta-debug

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

mysql> CREATE TABLE t (
    ->   d DATE NOT NULL
    -> )
    -> PARTITION BY RANGE(d) (
    ->   PARTITION p0 VALUES LESS THAN ('2005-06-02')
    -> );
ERROR 1064 (42000): VALUES value must be of same type as partition function near ')
)' at line 5
[3 Apr 2006 14:48] Peter Gulutzan
This is an example of what was reported in
Bug#13436 Partitions: most data types don't work,
which is marked "To be fixed later".
The version-5.1 MySQL Reference Manual currently says,
in section 17.5. Restrictions and Limitations on Partitioning:
"A partitioning key must be either an integer column or an
 expression that resolves to an integer."