Bug #84346 Generated Columns not supported in MySQLWorkbench Community
Submitted: 27 Dec 2016 11:35 Modified: 27 Dec 2016 23:53
Reporter: Andre Rippstein Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Workbench: SQL Editor Severity:S1 (Critical)
Version:6.3.8. build 1228 CE OS:Any
Assigned to: CPU Architecture:Any
Tags: GENERATED_ALWAYS

[27 Dec 2016 11:35] Andre Rippstein
Description:
MySQLWorkbench Community Edition does not allow to use 'GENERATED ALWAYS' syntax while creating a column in a table. The response is:

Error Code: 1064. 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 (concat(first_name,' ',last_name)),     email VARCHAR(100) N' at line 5	0.012 sec

I found a similar bug (closed) at http://bugs.mysql.com/bug.php?id=77837 
According this bug the problem has been fixed in version 6.3.6. Unfortunately the problem is not fixed in the current community edition build 6.3.8. build 1228 CE
Can you please check if this bug has been fixed for the community edition of the product also?

How to repeat:
CREATE TABLE contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    fullname varchar(101) GENERATED ALWAYS AS (concat(first_name,' ',last_name)),
    email VARCHAR(100) NOT NULL
);

Server Version: 5.6.33 MySQL Community Server (GPL)
Compiled for: linux-glibc2.5   (x86_64)
[27 Dec 2016 11:39] Andre Rippstein
Screen shot of MySQLWorkbench showing the error

Attachment: Screen Shot 2016-12-27 at 12.10.44.png (image/png, text), 474.63 KiB.

[27 Dec 2016 16:39] Miguel Solorzano
Thank you for the bug report. Yes not supported for your server version:

https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html

"As of MySQL 5.7.6, CREATE TABLE supports the specification of generated columns. Values of a generated column are computed from an expression included in the column definition. "

miguel@ural:~/dbs $ ./56c
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.36-debug Source distribution PULL: 2016-DEC-25

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.6 > use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql 5.6 > CREATE TABLE contacts (
    ->     id INT AUTO_INCREMENT PRIMARY KEY,
    ->     first_name VARCHAR(50) NOT NULL,
    ->     last_name VARCHAR(50) NOT NULL,
    ->     fullname varchar(101) GENERATED ALWAYS AS (concat(first_name,' ',last_name)),
    ->     email VARCHAR(100) NOT NULL
    -> );
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 (concat(first_name,' ',last_name)),
    email VARCHAR(100) N' at line 5
mysql 5.6 > exit
Bye
miguel@ural:~/dbs $ ./57c
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.18-debug-log Source distribution PULL: 2016-DEC-25

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql 5.7 > CREATE TABLE contacts (
    ->     id INT AUTO_INCREMENT PRIMARY KEY,
    ->     first_name VARCHAR(50) NOT NULL,
    ->     last_name VARCHAR(50) NOT NULL,
    ->     fullname varchar(101) GENERATED ALWAYS AS (concat(first_name,' ',last_name)),
    ->     email VARCHAR(100) NOT NULL
    -> );
Query OK, 0 rows affected (0,33 sec)

mysql 5.7 >
[27 Dec 2016 23:53] Andre Rippstein
Thank you!