Bug #51470 subpartition by key need an expression
Submitted: 24 Feb 2010 17:36 Modified: 30 Mar 2010 13:37
Reporter: Cyril SCETBON Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Partitions Severity:S2 (Serious)
Version:5.1, 5.5.1, 5.6.99 OS:Any
Assigned to: CPU Architecture:Any
Tags: key, partitioning
Triage: Triaged: D3 (Medium)

[24 Feb 2010 17:36] Cyril SCETBON
Description:
Hi,

When I try to define a subpartition by key, I have to specify an expression. It's not written in the documentation

How to repeat:
CREATE TABLE t3
(Balance SMALLINT NOT NULL PRIMARY KEY)
PARTITION BY RANGE (Balance)
SUBPARTITION BY KEY()
SUBPARTITIONS 10 (
    PARTITION p0 VALUES LESS THAN (2008),
    PARTITION p1 VALUES LESS THAN (2010),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')
SUBPARTITIONS 10 (
    PARTITION p0 VALUES LESS THAN (2008),
    PARTITION p1 ' at line 4

In this case I want to partition over a value (v<2008, 2008=<v=<2010,v>2010) and after to split each partition into ten subpartitions
[22 Mar 2010 15:35] Cyril SCETBON
any idea ?
[22 Mar 2010 19:18] Sveta Smirnova
Thank you for the report.

Verified as described: page http://dev.mysql.com/doc/refman/5.1/en/partitioning-subpartitions.html contains information about SUBPARTITION BY KEY exists, but has no example of its usage. See bug #45904 for correct syntax.
[30 Mar 2010 13:23] Jon Stephens
This is s Server bug.

The statement is valid, and if you specify the primary key, it works, although this should not be required, since per http://dev.mysql.com/doc/refman/5.1/en/partitioning-key.html, "Beginning with MySQL 5.1.6, KEY takes a list of zero or more column names. Where no column name is specified as the partitioning key, the table's primary key is used, if there is one." Since the table has an explicit primary key, it should be used by KEY(). 

I know of no reason why SUBPARTITION BY KEY should be any different than PARTITION BY KEY in this regard.
[31 Mar 2010 9:50] Jon Stephens
As requested, I've added a note about this issue to the Subpartitioning section, and additional discussion + example in 'Partitioning Limitations' - http://lists.mysql.com/commits/104683