Mysql Question posted in
The official MySQL documentation can be found here.

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`

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

Tags:

2

Answers


  1. 0

    MySQL does not support LIMIT in subqueries for certain subquery operators:

    mysql> SELECT * FROM t1
       WHERE s1 IN (SELECT s2 FROM t2 ORDER BY s1 LIMIT 1);
ERROR 1235 (42000): This version of MySQL doesn't yet support
 'LIMIT & IN/ALL/ANY/SOME subquery'

    Manual

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search