Bug #80988 Generated Columns do not support NOT NULL
Submitted: 7 Apr 2016 7:35 Modified: 7 Apr 2016 14:30
Reporter: Tony Tsoi Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Workbench Severity:S3 (Non-critical)
Version:6.3.6 OS:Any
Assigned to: CPU Architecture:Any
Tags: generated column, NOT NULL

[7 Apr 2016 7:35] Tony Tsoi
Description:
There are 3 issues: 

1. In create table, even after checked "NOT NULL" on a generated column, the generated CREATE TABLE statement does not generate NOT NULL on the generated column.

2. After I have created a generated column which has NOT NULL properties in database, when I tried to alter the tables using MySQL Workbench, the generated column does not have the NOT NULL property checked.

3. After I have created a generated column which has NOT NULL properties in database, when I tried to alter the tables using MySQL Workbench, the columns after the generated column with NOT NULL will not be displayed.

How to repeat:
For issue 1, create the following table using MySQL Workbench GUI:

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `v1` int(11) DEFAULT NULL,
  `v2` int(11) DEFAULT NULL,
  `v3` char(0) GENERATED ALWAYS AS ((case when (isnull(`v1`) xor isnull(`v2`)) then '' else NULL end)) VIRTUAL NOT NULL,
  `v4` char(0) GENERATED ALWAYS AS ((case when (isnull(`v1`) xor isnull(`v2`)) then '' else NULL end)) VIRTUAL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

then generate the create table statement, the generated statement will not generate NOT NULL property on the generated column.

------------------

For issue 2 and 3, create the following table using SQL query:

CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `v1` int(11) DEFAULT NULL,
  `v2` int(11) DEFAULT NULL,
  `v3` char(0) GENERATED ALWAYS AS ((case when (isnull(`v1`) xor isnull(`v2`)) then '' else NULL end)) VIRTUAL NOT NULL,
  `v4` char(0) GENERATED ALWAYS AS ((case when (isnull(`v1`) xor isnull(`v2`)) then '' else NULL end)) VIRTUAL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

after connected to database alter the table using MySQL Workbench, v3 column does not have the NOT NULL checked and the table does not contain column "v4".

Suggested fix:
For issue 1, the generated create table SQL should includes the NOT NULL property for generated column.

For issue 2, the NOT NULL property should be checked in alter table GUI.

For issue 3, the rest of the column after a NOT NULL generated column should be displayed.
[7 Apr 2016 7:54] Umesh Shastry
Hello Tony,

Thank you for the report test case.
I'm not seeing the first issue, but easier to confirm ii and iii issues.

Thanks,
Umesh
[7 Apr 2016 14:30] Tony Tsoi
Maybe my expression is a bit misleading in "How to repeat" session but the "Description" is still correct.

For the first issue, please create the table using the table design GUI (the one with many check box, don't create using SQL on query tab). The generated script generated in forward engineer will generate "... VIRTUAL" instead of "... VIRTUAL NOT NULL" even the "NOT NULL" check box is checked in the table design.