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.
