Description:
After successfully creating a table with a table editor of a SQL statement, altering the table causes the parentheses () DEFAULT expression designation to be removed. This is evident in both the default display of the table editor and in the code generated when Altering the table with the editor.
How to repeat:
1. Run the following code:
CREATE TABLE `test` (
`id` DECIMAL(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
`id1` DECIMAL(16) NOT NULL DEFAULT (id),
`created` TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
`updated` TIMESTAMP(6) NOT NULL DEFAULT (`created`) ON UPDATE CURRENT_TIMESTAMP(6),
PRIMARY KEY (`id`)
);
2. Refresh the Schemas in the Workbench Navigator.
3. Right click on the just created test table and select ALTER TABLE, or click on the ALTER TABLE Icon.
4. Notice that the DEFAULT expression designation parenthesis () are missing from the `id`, `id1`, and `updated` columns in the Default/Expression field. In addition, the `created` column designation is totally absent from the `updated` column Default/Expression field.
5. Right click on the test table in the Workbench Navigator and select Table Inspector.
6. Click on the DDL tab and observe the following CORRECT DDL code:
CREATE TABLE `test` (
`id` decimal(16,0) NOT NULL DEFAULT (uuid_to_bin(uuid())),
`id1` decimal(16,0) NOT NULL DEFAULT (`id`),
`created` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
`updated` timestamp(6) NOT NULL DEFAULT (`created`) ON UPDATE CURRENT_TIMESTAMP(6),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
7. In the Table Editor, add the text "Comment." to the Comments: field for each of the four columns. Then click on the Apply button.
8. Observe the following generated code:
ALTER TABLE `test`.`test`
CHANGE COLUMN `id` `id` DECIMAL(16,0) NOT NULL DEFAULT uuid_to_bin(uuid()) COMMENT 'Comment.' ,
CHANGE COLUMN `id1` `id1` DECIMAL(16,0) NOT NULL DEFAULT `id` COMMENT 'Comment.' ,
CHANGE COLUMN `created` `created` TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT 'Comment.' ,
CHANGE COLUMN `updated` `updated` TIMESTAMP(6) NOT NULL ON UPDATE CURRENT_TIMESTAMP(6) COMMENT 'Comment.' ;
9. Observe the same missing expression () designations/items missing as outlined in paragraph (4.) above.
10. Click the Apply button in the SQL Script Window.
11. Observe the Script FAILS with the following ERROR:
Operation failed: There was an error while applying the SQL script to the database.
ERROR 1064: 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 'uuid_to_bin(uuid()) COMMENT 'Comment.' ,
CHANGE COLUMN `id1` `id1` DECIMAL(16,0)' at line 2.
12. Correcting line 2 by adding the DEFAULT expression () designation results in similar failure at line 3.
13. Correcting lines 2 and 3 results in the table being generated, but without the correct DEFAULT (`created`) designation for the `updated column.
14. Refreshing the Schemas and opening another table inspector verifies the missing DDL "DEFAULT `created`" for the `updated` column.
Suggested fix:
Fix the Table Editor so that DEFAULT expression designation parenthesis () are not removed.
