| 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: | |
| 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 | ||
[16 Mar 2015 5:37]
MySQL Verification Team
Hello Mario Beck, Thank you for the report and test case. Thanks, Umesh
[16 Mar 2015 5:37]
MySQL Verification Team
// 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
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.