Bug #74333 create table output not accepted for virtual columns

• Submitted: 11 Oct 2014 18:46
• Modified: 15 Dec 2014 15:33
• Reporter: Daniël van Eeden (OCA)
• Status: Closed
• Category: MySQL Server: Parser
• Severity: S3 (Non-critical)
• Version: 5.7.5-labs-preview
• OS: Any
• CPU Architecture: Any
• Tags: column, generated, virtual

[11 Oct 2014 18:46] Daniël van Eeden

Description:
The output of SHOW CREATE TABLE is not accepted as valid SQL if it contains a generated column.

How to repeat:
mysql> create table t1 (id int, idx2 int as (id * 2));
Query OK, 0 rows affected (0.01 sec)

mysql [localhost] {msandbox} (test) > show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `idx2` VIRTUAL int(11) AS (id * 2)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `t1` (
    ->   `id` int(11) DEFAULT NULL,
    ->   `idx2` VIRTUAL int(11) AS (id * 2)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
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 'VIRTUAL int(11) AS (id * 2)
) ENGINE=InnoDB DEFAULT CHARSET=latin1' at line 3

Suggested fix:
make sure generated sql is accepted

[13 Oct 2014 7:44] MySQL Verification Team

Hello Daniël,

Thank you for the report and test case.

Thanks,
Umesh

[13 Oct 2014 7:44] MySQL Verification Team

//

mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.7.5                        |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.7.5-labs-preview-log       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | linux-el6                    |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)

mysql> create table t1 (id int, idx2 int as (id * 2));
Query OK, 0 rows affected (0.03 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `idx2` VIRTUAL int(11) AS (id * 2)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> CREATE TABLE `t1` (
    ->   `id` int(11) DEFAULT NULL,
    ->   `idx2` VIRTUAL int(11) AS (id * 2)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
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 'VIRTUAL int(11) AS (id * 2)
) ENGINE=InnoDB DEFAULT CHARSET=latin1' at line 3

[15 Dec 2014 15:33] Erlend Dahl

This has now been fixed on the feature tree for WL#411.