mysqlunionmysql-5.7mysql-8.0

Why is MySQL more picky about parentheses in union subqueries than in normal queries


(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.


Solution

  • 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 and UNION were refactored to be more consistent (the same SELECT 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