Bug #76330 Auto generated columns have different order of options than regular columns
Submitted: 15 Mar 2015 21:27
Reporter: Mario Beck Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server: DDL Severity:S4 (Feature request)
Version:5.7.6 OS:Any
Assigned to: CPU Architecture:Any
Tags: auto generated columns

[15 Mar 2015 21:27] Mario Beck
Description:
When defining auto generated columns the order of options differs from the order of options for regular columns. This is annoying and confusing.
E.g. key definitions in auto generated columns:
UNIQUE KEY is defined before the comment, PRIMARY KEY is defined after the comment.
In regular columns the key definition is always before the comment.

NULL/NOT NULL must be after comment option in auto generated columns but must be before comment otpion in regular column definitions.

The BNF in the documentation allows to define UNIQUE KEY and PRIMARY KEY simultaneously for one column. This also works in the server but it is useless.

The BNF in the documentation does not reflect that key definitions require a STORED column.

How to repeat:
CREATE TABLE t (i int);
ALTER TABLE t ADD COLUMN w int AS (42) STORED UNIQUE KEY PRIMARY KEY;
Works fine but it is strange and different than with regular columns.

Suggested fix:
Change the column definition for auto generated columns to:

data_type [[NOT] NULL] [GENERATED ALWAYS] AS (expression) [VIRTUAL | STORED [UNIQUE [KEY] | [PRIMARY] KEY]]   [COMMENT comment]

This should be changed in documentation and parser. See also http://bugs.mysql.com/bug.php?id=76328