MySQL Bugs: #58347: Workbench Reformat SQL Query creates invalid queries

Bug #58347	Workbench Reformat SQL Query creates invalid queries
Submitted:	20 Nov 2010 14:15	Modified:	13 Dec 2010 14:10
Reporter:	James Bromberger	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	5.2.30	OS:	Windows
Assigned to:	Alfredo Kojima	CPU Architecture:	Any
Tags:	plugins, sql editor

Description:
Hi,

When entring this valid query in the Workbench 5.2.30 CE (6790) query window:

SELECT DomainID, Domains.Domain FROM Domains LEFT OUTER JOIN Ping_Test_Results USING (DomainID) LEFT OUTER JOIN Sites USING (SiteID) WHERE Ping_Test_Results.DomainID IS NULL AND Domains.Ping_Test_Is_Active = TRUE AND Sites.Ping_Test_Is_Active = TRUE

The Plugin -> Utilities -> Reformat SQL produces a query with missing spaces around the word "USING" in the join specification, which makes the query invalid:

SELECT 
        DomainID, Domains.Domain
    FROM
        Domains LEFT OUTER JOIN Ping_Test_ResultsUSING(DomainID)
        LEFT OUTER JOIN SitesUSING(SiteID)
    WHERE
        Ping_Test_Results.DomainID IS NULL 
        AND Domains.Ping_Test_Is_Active = TRUE 
        AND Sites.Ping_Test_Is_Active = TRUE

How to repeat:
Insert a query with a join into the Workbench query window. In the UI, chose Plugins -> Utilities -> Reformat SQL Query.

Suggested fix:
Add spaces before and after the keyword "USING".

Thank you for the report. The bug has been fixed in the repository.

You can fix the bug in your copy of WB by editing sql_reformatter.py and inserting the following lines around line 480, with a newline before and after the code:

    def sym_USING(self, value, children):
        self.out(" %s " % value)

Sweet! Many thanks.

Workbench has, in the last few releases, really picked up. I think I've just fallen in love with it. Well done & thank you.

fix confirmed in repository

An entry has been added to the 5.2.31 changelog:

In the SQL Editor, using the code beautifier on a query containing USING resulted in an invalid query being generated. This was due to missing spaces around USING.