Bug #101195 Reverse engineering generated table columns miss NOT NULL columns attribute
Submitted: 15 Oct 2020 18:21 Modified: 15 Oct 2020 22:18
Reporter: Oleg Tsvinev Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Workbench: Modeling Severity:S1 (Critical)
Version:8.0.21 OS:Mac OS X (Mojave)
Assigned to: CPU Architecture:x86

[15 Oct 2020 18:21] Oleg Tsvinev
Description:
Reverse engineered database using MySQL Workbench using "Database, Reverse Engineer" misses NOT NULL attribute for generated column.

The column appears correctly in a script generated by mysqldump.

How to repeat:
CREATE TABLE `generated` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `gen` CHAR(32) COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (md5('This is a bug')) STORED NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Using MySQL Workbench, connect to database as root, then use Database, Reverse Engineer. Complete the steps, select table in a EER diagram, use "Copy SQL to Clipboard," paste the text into text editor or browser field. It appears as below, and the " NOT NULL" attribute on the "gen" column is missing:

CREATE TABLE IF NOT EXISTS `bug_report`.`generated` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `gen` CHAR(32) GENERATED ALWAYS AS (md5('This is a bug')) STORED,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci

Additionally, right click on the table in EER diagram, select "Edit" and observe unchecked NN checkbox for the "gen" column.

The table script obtained using mysqldump appears as following:

DROP TABLE IF EXISTS `generated`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `generated` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `gen` char(32) COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (md5('This is a bug')) STORED NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
[15 Oct 2020 22:18] MySQL Verification Team
Thank you for the bug report.