MySQL Bugs: #51470: subpartition by key need an expression

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

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

any idea ?

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.

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.

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