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:
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

[5 Jun 2012 15:13] Mike Reid
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


select host, User from mysql.db 
-- 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:

    host, User
    Select_priv in ('Y' , 'N');

Expected Result:

    host, User
-- 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:

    User = User
    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 Build 1170
[1 Jun 2014 23:25] Reuben Helms
Still occurs on Windows, MySQL Workbench 6.1, Version build 1454.
[1 Sep 2015 10:43] Maurizio Tortorici
Still remove comments on Workbench
[14 Oct 2015 14:15] Kevin Finkenbinder
Still an issue with (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 2019 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 2019 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.
[15 Sep 2023 9:07] Michael Pazda
Still not fixed in workbench 8.0.31