MySQL Bugs: #29925: Syntax of ALTER TABLE ... PARTITION BY not clear.

Bug #29925	Syntax of ALTER TABLE ... PARTITION BY not clear.
Submitted:	20 Jul 2007 8:59	Modified:	25 Jul 2007 15:35
Reporter:	Horst Hunger	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any
Assigned to:	Jon Stephens	CPU Architecture:	Any

Description:
The following syntax, especially the alter_specification "PARTITION BY partition_option" leads to unexpected ALTER statements like
"ALTER TABLE t1 PARTITION BY PARTITION BY hash(...);"
as "partition_options" also start with "PARTITION BY".
Excepted is "ALTER TABLE t1 PARTITION BY hash(...);"

ALTER [IGNORE] TABLE tbl_name
    alter_specification [, alter_specification] ...

alter_specification:
    table_option ...
  | ADD [COLUMN] column_definition [FIRST | AFTER col_name ]
   ...
  | PARTITION BY partition_options
  | ADD PARTITION (partition_definition)
  | DROP PARTITION partition_names

How to repeat:
execute 
create table t1 (c1 int, c2 char(20));
alter table t1 partition by partition by hash (c1);

Suggested fix:
ALTER [IGNORE] TABLE tbl_name
    alter_specification [, alter_specification] ...

alter_specification:
    table_option ...
  | ADD [COLUMN] column_definition [FIRST | AFTER col_name ]
   ...
  | partition_options
  | ADD PARTITION (partition_definition)
  | DROP PARTITION partition_names

Thank you for the report.

Verified as describd.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Made it clear in 5.1/5.2 CREATE TABLE Syntax that PARTITION BY is not part of partition_options but rather precedes it.