Bug #76329 COLLATE option not accepted in generated column definition
Submitted: 15 Mar 2015 21:07 Modified: 14 Jul 2015 14:45
Reporter: Mario Beck Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Parser Severity:S3 (Non-critical)
Version:5.7.6, 5.7.8, 8.0.0 OS:Any
Assigned to: Gleb Shchepa CPU Architecture:Any
Tags: auto generated columns, collate

[15 Mar 2015 21:07] Mario Beck
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.
[16 Mar 2015 5:37] Umesh Shastry
Hello Mario Beck,

Thank you for the report and test case.

Thanks,
Umesh
[16 Mar 2015 5:37] Umesh Shastry
// 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
[13 Jul 2015 16:34] Paul Dubois
Noted in 5.7.8, 5.8.0 changelogs.

The data type for generated columns did not permit the COLLATE
attribute.
[14 Jul 2015 14:45] Mario Beck
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.
[15 Jul 2015 16:31] Paul Dubois
"did not permit" = past tense.

Revised changelog entry:

The data type for generated columns now permits the COLLATE
attribute.
[18 Jun 2016 21:24] Omer Barnir
Posted by developer:
 
Reported version value updated to reflect release name change from 5.8 to 8.0