Bug #92900 MySQL Workbench 8.0.13 doesn't support expressions in DEFAULT
Submitted: 23 Oct 2018 13:41 Modified: 18 May 2019 11:11
Reporter: Georgi Sotirov Email Updates:
Status: Closed Impact on me:
Category:MySQL Workbench: SQL Editor Severity:S3 (Non-critical)
Version:8.0.13 OS:Windows (Windows 10 Pro)
Assigned to: CPU Architecture:x86

[23 Oct 2018 13:41] Georgi Sotirov
Apparently, MySQL Workbench doesn't well support the new feature for expressions as default values. I found two problems:

1. SQL Editor displays an error on expression for a BINARY column (see first screenshot). The server accepts the SQL without any error or warning;
2. Trying to alter table by clicking Alter table... in Navigator results in Error Parsing DDL for ... (see second screenshot).

How to repeat:
1. Paste the following in an SQL Editor tab in MySQL Workbench 8.0.13:

CREATE TABLE def_expr (
  id        INT        NOT NULL AUTO_INCREMENT,
  uuid_def  BINARY(16) DEFAULT (uuid_to_bin(uuid())),
  geo_def   POINT      DEFAULT (Point(0,0)),
  json_def  JSON       DEFAULT (JSON_ARRAY()),
  json_def2 JSON       DEFAULT ('[]') /* this works too */,

  PRIMARY KEY (`id`),

2. Notice the error given by editor's parser (see first screenshot).
3. Try to alter the created table from Navigator.

Suggested fix:
SQL Editor shouldn't display error as the syntax of the SQL statement is correct. It should be possible to alter a table with expressions as default values.
[23 Oct 2018 13:44] Georgi Sotirov
Workbench 8.0.13 displays error in the editor on expression in DEFAULT

Attachment: wb-8.0.13-def_expr-edit_error.png (image/png, text), 7.10 KiB.

[23 Oct 2018 13:44] Georgi Sotirov
Workbench 8.0.13 displays error on attempt to alter a table with expressions in DEFAULT

Attachment: wb-8.0.13-def_expr-alter.png (image/png, text), 15.98 KiB.

[23 Oct 2018 13:50] MySQL Verification Team
Thank you for the bug report.
[12 Mar 2019 17:45] Christine Cole
Posted by developer:
Fixed as of the upcoming MySQL Workbench 8.0.16 release, and here's the changelog entry:

The SQL editor and the Alter Table operation both reported errors on
indexes with valid functional key parts and on expressions that were used
as default values.

Thank you for the bug report.
[18 May 2019 11:11] Georgi Sotirov
I confirm the SQL editor and alter table problem are solved. And I see the default column expressions in the table editor.

However, trying to modify the expression always results in error, because the generated SQL (see below) is wrong since the expression is not engulfed int parentheses.

The generates SQL is:

ALTER TABLE `test`.`def_expr` 
CHANGE COLUMN `geo_def` `geo_def` POINT NULL DEFAULT point(42,23) ;

While in fact it should be:

ALTER TABLE `test`.`def_expr` 
CHANGE COLUMN `geo_def` `geo_def` POINT NULL DEFAULT (point(42,23));

Please, reopen the bug and fix.
[25 Dec 2019 9:38] Dror Riov
Happens with 8.0.18
When you try to add Default of 'uuid_to_bin(uuid())' 
you receive a syntax error.

Does work if you do it from the query , but won't show up (in default) when you edit table but does show 'blob' in the column.
create table MyTable (
  uid binary(16) primary key default(uuid_to_bin(uuid())),
  tableName text not null