(SELECT 1 `a`)
UNION
(SELECT 2 `a`)
ORDER BY `a` DESC
LIMIT 1
is a perfectly valid query in MySQL and as far as I can tell the same as
SELECT 1 `a`
UNION
SELECT 2 `a`
ORDER BY `a` DESC
LIMIT 1
However,
SELECT *,
(
(SELECT 1 `a`)
UNION
(SELECT 2 `a`)
ORDER BY `a` DESC
LIMIT 1
) `a`
FROM `customers`
gives an error.
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION (SELECT 2 `a`) ORDER BY `a` DESC LIMIT 1) `a` FROM `customers`
And
SELECT *,
(
SELECT 1 `a`
UNION
SELECT 2 `a`
ORDER BY `a` DESC
LIMIT 1
) `a`
FROM `customers`
is valid.
Can someone explain to me why? Or did I encounter a bug in MySQL?
Addition: This is only in MySQL 5.7. MySQL 8 works fine.
UNION
is indeed implemented differently in MySQL 8.0 as compared to MySQL 5.x.
See https://dev.mysql.com/doc/refman/8.0/en/union.html, section "UNION Handing in MySQL 8.0 Compared to MySQL 5.7"
In MySQL 8.0, the parser rules for
SELECT
andUNION
were refactored to be more consistent (the sameSELECT
syntax applies uniformly in each such context) and reduce duplication. Compared to MySQL 5.7, several user-visible effects resulted from this work, which may require rewriting of certain statements.
Read that documentation page for more details.
Also useful reference: https://dev.mysql.com/doc/refman/8.0/en/parenthesized-query-expressions.html