MySQL Forums
Forum List  »  MySQL Workbench

Send to SQL Editor -> Select All Statement Field Ordering 8.0.21
Posted by: William Lob
Date: July 27, 2020 06:49AM

When using the 'Workbench > Send Select All' statement, Workbench somehow retains the original field order of the moved field in the generated SQL code, even though the field was actually verified as moved in the real table.

In MySQLWorkbench 8.0.21, I observe the following:

1. CREATE TABLE bma with 41 fields, mostly text
2. LOAD DATA INFILE to table bma populate 645 rows
3. ALTER TABLE bma to add 10 columns, three inserted in the middle of the field list (around the 20th place) using AFTER, and six more at the end, AFTER the last field, which is originally and must remain `ROW_ID.
4. Restore `ROW_ID` as last field with a MODIFY COLUMN ROW_ID INT AFTER the most recent of the six inserts at bottom of field list.

The table appears correctly modified with ROW_ID as last field for all of these tests, both directly executing SQL and using Workbench tools:
* SELECT * FROM bma;
* Workbench > Alter Table...
* Workbench Navigator expand "Columns", and ROW_ID shows as the last field

BUT...If I use Workbench > Send to SQL Editor > Select All Statement

the ROW_ID that was moved to end appears in its original order, above the six fields added in the ALTER TABLE statement.

I have tried closing and restarting Workbench, It is repeatable across DROP TABLEs and rerunning the CREATE, LOAD, and ALTERs. Also repeatable after stopping/restarting server instance. Also tried moving the MODIFY COLUMN `ROW_ID` AFTER `last field` to its own separate statement, not part of the ALTER with several ADD COLUMNS in front of it. None fixed the issue.

`ROW_ID` is an INT. All other fields are VARCHAR.

Minor severity, as this is just a quirk in the Workbench SQL code editor's trying-to-be-helpful toolkit. The actual ALTER TABLE ...MODIFY COLUMN...AFTER SQL appears to be working just fine.

Options: ReplyQuote

Written By
Send to SQL Editor -> Select All Statement Field Ordering 8.0.21
July 27, 2020 06:49AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.