Bug #76329 COLLATE option not accepted in generated column definition

Description:
When defining a generated column and the datatype is CHAR or VARCHAR (TEXT probably as well?) it is possible to define a character set but it is not possible to define a collation.

How to repeat:
CREATE TABLE bla (i int);
ALTER TABLE bla ADD COLUMN t CHAR(42) CHARACTER SET utf8 COLLATE utf8_bin GENERATED ALWAYS AS ("foo bar");

Same statement without the COLLATE option works fine.

Suggested fix:
The interpreter should follow the documentation and accept the COLLATE option for character datatypes.

Hello Mario Beck,

Thank you for the report and test case.

Thanks,
Umesh

// 5.7.8

root@localhost [test] > show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| innodb_version          | 5.7.8                                                   |
| protocol_version        | 10                                                      |
| slave_type_conversions  |                                                         |
| version                 | 5.7.8-rc-enterprise-commercial-advanced                 |
| version_comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64                                                  |
| version_compile_os      | Linux                                                   |
+-------------------------+---------------------------------------------------------+

root@localhost [test] > CREATE TABLE bla (i int);
Query OK, 0 rows affected (0.00 sec)

root@localhost [test] > ALTER TABLE bla ADD COLUMN t CHAR(42) CHARACTER SET utf8 COLLATE utf8_bin GENERATED ALWAYS AS ("foo bar");
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 'GENERATED ALWAYS AS ("foo bar")' at line 1
root@localhost [test] >

// 5.8.0
commit: a9992220039b548fc6529ecbeae75f4afcc048fc
date: 2015-03-14 06:45:26 +0100
build-date: 2015-03-14 17:51:23 +0100
short: a999222
branch: mysql-trunk

MySQL source 5.8.0
root@localhost [test] > CREATE TABLE bla (i int);
Query OK, 0 rows affected (0.00 sec)

root@localhost [test] > ALTER TABLE bla ADD COLUMN t CHAR(42) CHARACTER SET utf8 COLLATE utf8_bin GENERATED ALWAYS AS ("foo bar");
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 'GENERATED ALWAYS AS ("foo bar")' at line 1

Noted in 5.7.8, 5.8.0 changelogs.

The data type for generated columns did not permit the COLLATE
attribute.

I don't think that changing the docs is an adequate fix for the problem. I do not consider this "closed".
We need the COLLATE option for auto-generated columns to enable specific use cases, e.g. http://mablomy.blogspot.de/2015/03/auto-generated-columns-in-mysql-57-two.html
This should at least remain as an RFE.

"did not permit" = past tense.

Revised changelog entry:

The data type for generated columns now permits the COLLATE
attribute.

Posted by developer:
 
Reported version value updated to reflect release name change from 5.8 to 8.0