MySQL Bugs: #62745: ALTER TABLE clauses have ordering restrictions

Bug #62745	ALTER TABLE clauses have ordering restrictions
Submitted:	14 Oct 2011 20:54	Modified:	10 Nov 2011 18:56
Reporter:	Rick James	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	5.1.48, 5.1.60	OS:	Linux (2.6.18-164.2.1.el5)
Assigned to:		CPU Architecture:	Any
Tags:	alter, engine

Description:
In this, notice that one ordering of the clauses fails with a syntax error; the other works.

mysql> CREATE TABLE foo (i INT, PRIMARY KEY(i)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE foo
    ->     DROP PRIMARY KEY,
    ->     ORDER BY i,
    ->     ENGINE=MyISAM;
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 '=MyISAM' at line 4
mysql> ALTER TABLE foo
    ->     DROP PRIMARY KEY,
    ->     ENGINE=MyISAM,
    ->     ORDER BY i;
Query OK, 0 rows affected (0.29 sec)
Records: 0  Duplicates: 0  Warnings: 0

The BNF in 
http://dev.mysql.com/doc/refman/5.1/en/alter-table.html
clearly states that the order is not important.

How to repeat:
CREATE TABLE foo (i INT, PRIMARY KEY(i)) ENGINE=InnoDB;
ALTER TABLE foo
    DROP PRIMARY KEY,
    ORDER BY i,
    ENGINE=MyISAM;   # Fails
ALTER TABLE foo
    DROP PRIMARY KEY,
    ENGINE=MyISAM,
    ORDER BY i;      # Succeeds

Suggested fix:
Either:
* Fix the ALTER TABLE doc, or
* Fix the code to really allow arbitrary order of ALTER clauses.

Thank you for the bug report, Verified just as described with 5.1.60 also:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.60 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE foo (i INT, PRIMARY KEY(i)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.11 sec)

mysql> ALTER TABLE foo
    ->     DROP PRIMARY KEY,
    ->     ORDER BY i,
    ->     ENGINE=MyISAM; 
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 '=MyISAM' at line 4
mysql> ALTER TABLE foo
    ->     DROP PRIMARY KEY,
    ->     ENGINE=MyISAM,
    ->     ORDER BY i;  
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

I checked 5.0.90 and 5.5.16 and it is the same.

It's a grammar ambiguiity. Will update ORDER BY description to indicate that it should be last.