Bug #77867 ORDER BY in ALTER TABLE syntax should be moved out of alter_list_item

• Submitted: 29 Jul 2015 2:30
• Modified: 30 Jul 2015 14:09
• Reporter: Sorawee Porncharoenwase
• Status: Verified
• Category: MySQL Server: Parser
• Severity: S4 (Feature request)
• OS: Any
• CPU Architecture: Any

[29 Jul 2015 2:30] Sorawee Porncharoenwase

Description:
ORDER BY follows by comma separated items. This makes bison confused because alter specification (alter_list_item) is comma separated as well. Thus:

mysql> ALTER TABLE mytable ENABLE KEYS, ORDER BY id;
Query OK, 0 rows affected

But

mysql> ALTER TABLE mytable ORDER BY id, ENABLE KEYS;
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 'KEYS' at line 1

This could be fixed by changing the grammar -- moving alter_order_clause out of alter_list_item -- so that ORDER BY could appear only once at the end of the statement. This would reduce the conflict that is happening as well.

How to repeat:
mysql> ALTER TABLE mytable ENABLE KEYS, ORDER BY id;
Query OK, 0 rows affected

But

mysql> ALTER TABLE mytable ORDER BY id, ENABLE KEYS;
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 'KEYS' at line 1

[30 Jul 2015 14:09] MySQL Verification Team

I completely understand your logic. However, there is a very good workaround for this problem, namely, to pay attention to the manner in which ALTER is written. Also, ENABLE KEYS is working only on MyISAM, where we do not do new features, although this is not important.

However, this is a veritable and justified feature request, and hence your report is verified.