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
Triage: Needs Triage: D3 (Medium)

[5 Jun 2012 15:13] Mike Reid
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.
[5 Jun 2012 15:51] Valeriy Kravchuk
Thank you for the problem report.
[8 Jun 2012 17:13] Mike Reid
Updated OS to "Any" -- Confirmed this is impacting Windows as well.
[30 Sep 2013 9:24] Stefan Thurnherr
Can confirm that this bug is still present in Workbench 6.0.7 (Windows).
[16 Apr 2014 17:48] risman hidayat
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
[1 Jun 2014 23:25] Reuben Helms
Still occurs on Windows, MySQL Workbench 6.1, Version 6.1.4.11773 build 1454.
[1 Sep 2015 10:43] Maurizio Tortorici
Still remove comments on Workbench 6.3.3.0
[14 Oct 2015 14:15] Kevin Finkenbinder
Still an issue with 6.3.4.0 (Build 828).  Makes the beautifier practically worthless!
[16 Feb 2016 22:20] Tiberiu Oprisiu
How has this not been fixed as of yet? (Version 6.3.5)
[21 Feb 2017 17:22] James Bachelder
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.
[17 Aug 2017 14:29] Randy Schreck
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...
[17 Aug 2017 14:43] Mike Reid
Bumped impacted version from 5.2.40 to current stable 6.3.9
[15 Dec 2017 7:36] Yin Huei Ooi
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?
[27 Sep 2018 16:14] Brad Lane
Comments are still removed. 6 years later.
[13 Feb 18:50] Dave San
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.
[2 Oct 0:02] Luis Gonzalez
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.