MySQL Bugs: #60311: SQL Reformatter voids unions when there are subqueries

Bug #60311	SQL Reformatter voids unions when there are subqueries
Submitted:	3 Mar 2011 8:59	Modified:	7 Feb 2012 22:21
Reporter:	Mikiya Okuno	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S3 (Non-critical)
Version:	5.2.31	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
"Reformat SQL Query" causes an output which the first SELECTs do not exist when there are subqueries in later SELECTs.

How to repeat:
Use "Plugins > Utilities > Reformat SQL Query" against the query:

select a from x union select b from y where a in (select c from z)

results in:

select   
        b
from
    y 
    where
    a in (select 
                c
        from
            z
    )

Reformatting the query:

select a from x where a in (select b from y) union select c from z

results in an error.

Suggested fix:
n/a

Now I can see that SQL reformatter doesn't break SQL syntax on 5.2.32. However, its layout is still terrible.

The query "select a from x union select b from y where a in (select c from z)"

causes the following output:

select 
    `a`
from
    `x`union select 
    `b`
from
    `y`
where
    `a` in (select 
        `c`
    from
        `z`)

The query "select a from x where a in (select b from y) union select c from z"

causes the following output:

select 
    `a`
from
    `x`
where
    `a` in (select 
        `b`
    from
        `y`)union select 
    `c`
from
    `z`

This isn't easy to read.

Does something like below code look ok? Assuming real SQL would be longer than below. Any suggestions, if not?

select 
    `a`
from
    `x`
where
    `a` in 
        (select 
            `b`
        from
            `y`)
union select 
    `c`
from
    `z`

Looks very nice :)

Fixed as of 5.2.38:

"Plugins", "Utilities", "Reformat SQL Query" would mangle queries by
removing the first "SELECT" statement if a
subquery clause was present