MySQL Bugs: #19625: CREATE TABLE .... PARTITION BY order not per docs

Bug #19625	CREATE TABLE .... PARTITION BY order not per docs
Submitted:	8 May 2006 23:22	Modified:	12 May 2006 23:33
Reporter:	Brian Morin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.1.9/5.1BK	OS:	Linux (Linux)
Assigned to:	Jon Stephens	CPU Architecture:	Any

Description:
Per the docs the CREATE TABLE syntax should be:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [partition_options]
    [table_options] [select_statement]

[partition_options] is before [table_options]

However, this doesn't work:

DROP TABLE IF EXISTS `order_good`;

CREATE TABLE `order_good` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY  (`id`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY HASH(id);

But this does:

DROP TABLE IF EXISTS `order_bad`;

CREATE TABLE `order_bad` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY  (`id`)
) PARTITION BY HASH(id) ENGINE=ndbcluster DEFAULT CHARSET=latin1;

How to repeat:
With a fresh NDP Cluster database run:

DROP TABLE IF EXISTS `order_good`;

CREATE TABLE `order_good` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY  (`id`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY HASH(id);

DROP TABLE IF EXISTS `order_bad`;

CREATE TABLE `order_bad` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY  (`id`)
) PARTITION BY HASH(id) ENGINE=ndbcluster DEFAULT CHARSET=latin1;

The second create table call fails with a syntax error.

Suggested fix:
Either update the docs or fix the parser.

Changing Category to Cluster.

Thank you for the bug report.

mysql> CREATE TABLE `order_bad` (
    ->   `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY  (`id`)
    -> ) PARTITION BY HASH(id) ENGINE=MyISAM DEFAULT CHARSET=latin1;
ERROR 1064 (42000): You have an error in your SQL

mysql> CREATE TABLE `order_good` (
    ->   `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY HASH(id);
Query OK, 0 rows affected (0.02 sec)

This is a documentation bug.
The proper order is
[table_options][partition_options]

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
product(s).

Additional info:

Corrected error in CREATE TABLE Syntax.

(Note that the examples shown in the Partitioning chapter were correct.)