Bug #92908 MySQL Workbench 8.0.13 doesn't support expressions as key parts
Submitted: 23 Oct 2018 18:37 Modified: 18 May 2019 10:57
Reporter: Georgi Sotirov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Workbench Severity:S3 (Non-critical)
Version:8.0.13 OS:Windows (Windows 10 Pro)
Assigned to: CPU Architecture:x86

[23 Oct 2018 18:37] Georgi Sotirov
Description:
Apparently, MySQL Workbench's new version doesn't well support the new functional indexes. I found two problems:

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

See also bug 92900 I registered earlier.

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

CREATE TABLE func_index (
  id        INT        NOT NULL AUTO_INCREMENT,
  ipaddr4   INT UNSIGNED /* 4 bytes */,

  PRIMARY KEY (id),
  UNIQUE INDEX id_UNIQUE (id ASC) VISIBLE,
  INDEX func_idx ((INET_NTOA(ipaddr4)))
);

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

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 functional index.
[23 Oct 2018 18:43] Georgi Sotirov
Workbench 8.0.13 displays error in the editor on functional index

Attachment: wb-8.0.13-func_index-edit_error.png (image/png, text), 45.92 KiB.

[23 Oct 2018 18:44] Georgi Sotirov
Workbench 8.0.13 displays error on attempt to alter a table with functional index

Attachment: wb-8.0.13-func_index-alter.png (image/png, text), 55.32 KiB.

[8 Mar 2019 21:05] 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.

Thank you for the bug report.
[18 May 2019 10:57] Georgi Sotirov
I confirm the errors in SQL Editor and when opening a table for altering are fixed.
However, now that I'm able to open the table editor in Workbench 8.0.16 and go to tab Indexes, I do not see any useful information about the functional index (1). I would expect to see the index expression and be able to modify it, but I see no such possibilities in the user interface. Perhaps for functional indexes there should be virtual column(s) with the expressions in section Index Columns, because functional key parts could be mixed with nonfunctional in composite indexes (see https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-functional-key-part... ).
Also Visible check is not properly checked for functional indexes (2) and if I check it and click on Apply I normally get the message "No changes detected".

Please, reopen the bug and fix these two additional problems.