MySQL Bugs: #80988: Generated Columns do not support NOT NULL

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

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.

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

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.

This bug still exists in current release 8.0.30 and is annoying when using workbench sync to push model changes to the server.