MySQL Bugs: #65523: Beautify Query only works on SELECT statements, removes SQL Comments.

Bug #65523	Beautify Query only works on SELECT statements, removes SQL Comments.
Submitted:	5 Jun 2012 15:13	Modified:	17 Aug 2017 14:43
Reporter:	Mike Reid	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S3 (Non-critical)
Version:	6.3.9	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	beautify, comments, delete, format, query, removed, SQL, UPDATE

Description:
The "Beautify Query" (SQL Formatting) command appears to only work on SELECT statements. If your SELECT statements contain any SQL Comments, they are completely removed.
Further, if you try to apply it to complex (or even simple) UPDATE / DELETE statements, nothing happens.

How to repeat:
=====  SQL COMMENT EXAMPLE =====  

1. Open MySQL Workbench and connect to a database
2. Enter an SQL Query containing an SQL Comment

e.g. 

select host, User from mysql.db 
where 
-- host = '%' and 
Select_priv in ('Y' , 'N');

3. Run "Beautify Query" (Command + B) and watch as the resulting formatted SQL has removed the SQL Comment:

select 
    host, User
from
    mysql.db
where
    Select_priv in ('Y' , 'N');

Expected Result:

select 
    host, User
from
    mysql.db
where
-- host = '%' and 
    Select_priv in ('Y' , 'N');

=====  NON-SELECT EXAMPLE =====  

1. Open MySQL Workbench and connect to a database
2. Enter a DELETE or UPDATE SQL statement

e.g. update mysql.db set User = User where Select_priv in ('Y','N');

3. Run "Beautify Query" (Command + B) and watch as nothing happens.

Expected Result:

update
    mysql.db
set
    User = User
where
    Select_priv in ('Y' , 'N');

Suggested fix:
Ideally "Beautify Query" would NOT remove SQL comments, but just ignore them instead. Perhaps provide an option to optionally retain them via a Workbench preference?

It would be nice if it also worked with all types of SQL: UPDATE, DELETE, CREATE, SELECT, ANALYZE, EXPLAIN, etc.

Thank you for the problem report.

Updated OS to "Any" -- Confirmed this is impacting Windows as well.

Can confirm that this bug is still present in Workbench 6.0.7 (Windows).

I just wanted to add that this issue is still present in the Mac version running on OS 10.9.2

6.0.9.11421 Build 1170

Still occurs on Windows, MySQL Workbench 6.1, Version 6.1.4.11773 build 1454.

Still remove comments on Workbench 6.3.3.0

Still an issue with 6.3.4.0 (Build 828).  Makes the beautifier practically worthless!

How has this not been fixed as of yet? (Version 6.3.5)

Bug 65523, 68000, and 68504 are all duplicates and still not fixed in MySQL Workbench 6.3 Version 6.3.8 build 1228 CE (64 bits) running on Win 10.

Just a note that this applies to ALL comment type. I use # and ## quite often; both are removed. I've also used /* */ from time to time and it's removed as well. 5-year old bug...

Bumped impacted version from 5.2.40 to current stable 6.3.9

Still remove comments on Workbench 6.3.10. 

What is the reason of remove comment if this is not a bug?
and if it's a bug, why it hasn't been fix after 5 years?

Comments are still removed. 6 years later.

No mention of INSERT not being beautified, so I'm adding it here. It simply will not beautify an INSERT statement even if it runs perfectly fine.

Can confirm on version 8.0.17. Beautify removes comments within a statement, and will not format INSERT statements. Comments outside of statements are unaffected.

Still not fixed in workbench 8.0.31