Bug #77837 Generated Columns not supported in any part of Workbench.

• Submitted: 25 Jul 2015 23:27
• Modified: 22 Oct 2016 15:29
• Reporter: Brad Lanier
• Status: Closed
• Category: MySQL Workbench
• Severity: S1 (Critical)
• Version: 6.3.4.0
• OS: Any
• CPU Architecture: Any
• Tags: generated columns

[25 Jul 2015 23:27] Brad Lanier

Description:
Generated columns are not supported in the SQL editor, Table Editor, or Model by either creating a table or reverse engineering a database that already has a table with a generated column.

How to repeat:
1. Ensure target database is at least 5.7.7.
2. Use the SQL editor to create a table with a generated column. The syntax checker shows an error.
3. Attempt to reverse engineer a database having a table with a generated column into a new model. The operation will error out and no table with a generated column will be brought into the model.
4.  Attempt to use the table editor from anywhere in the workbench (Instance tab, Model tab, or ERR tab) to create a table with a generated column.  There is no support to do so.

[26 Jul 2015 6:31] MySQL Verification Team

Hello Brad,

Thank you for the bug report.
Verified as described with WB 6.3.4 and MySQL 5.7.9.

Thanks,
Umesh

[26 Jul 2015 6:34] MySQL Verification Team

// Follow steps from how to repeat in bug report

-- Schema created from CLI

Database changed
mysql> CREATE TABLE sales (
    ->  name VARCHAR(20),
    ->  price_eur DOUBLE,
    ->  amount INT,
    ->  total_eur DOUBLE AS (price_eur * amount),
    ->  total_usd DOUBLE AS (total_eur * xrate),
    ->  xrate DOUBLE);
Query OK, 0 rows affected (0.01 sec)

mysql> show create table sales\G
*************************** 1. row ***************************
       Table: sales
Create Table: CREATE TABLE `sales` (
  `name` varchar(20) DEFAULT NULL,
  `price_eur` double DEFAULT NULL,
  `amount` int(11) DEFAULT NULL,
  `total_eur` double GENERATED ALWAYS AS (price_eur * amount) VIRTUAL,
  `total_usd` double GENERATED ALWAYS AS (total_eur * xrate) VIRTUAL,
  `xrate` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

-- WB SQL Editor shows syntax error

Syntax Error: Unexpected 'AS' (as)

-- reverse eng triggers

ERROR: Line 8: syntax error, unexpected IDENT_QUOTED, expecting ')'. Statement skipped.

[20 Sep 2015 13:11] Daniël van Eeden

With MySQL 5.7.8 and 6.3.4.0 I get this when I try to ALTER a table with a generated column
Syntax error: unexpected 'GENERATED' (identifier)

[10 Dec 2015 2:32] Philip Olson

Posted by developer:
 
Fixed as of the upcoming MySQL Workbench 6.3.6 release, and here's the changelog entry:

Generated columns(a feature added in MySQL 5.7) were not fully supported
in the SQL editor, Table Editor, nor Modeling interface.

Thank you for the bug report.

[22 Oct 2016 15:29] Brad Lanier

This was fixed as of 6.3.6.  Changing status to closed