MySQL Bugs: #25734: union and parentheses

Bug #25734	union and parentheses
Submitted:	20 Jan 2007 16:34	Modified:	13 Sep 2017 15:34
Reporter:	Nikolas Garofil	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S2 (Serious)
Version:	5.0.30-Debian_3-log	OS:	Linux (Debian)
Assigned to:	Marc ALFF	CPU Architecture:	Any
Tags:	bfsm_2007_04_05, error, parantheses, query, UNION

Description:
Suppose 'foo' is a column of table 'bar'

Query's of the form "select foo from bar where foo in ((select foo from bar) union (select foo from bar))" do not work. (ERROR 1064 (42000))

When I remove the innner parantheses from the first part of the union like this "select foo from bar where foo in (select foo from bar union (select foo from bar))" then it works

When I only keep everything between the outer parentheses like this "(select foo from bar) union (select foo from bar)" then it also works

The error also happens in other querys that look a lot like my query, but this is the shortest form i could found that would generate the error.

I know that the query is a strange and useless query because it is exactly the same as "select distinct foo from bar" but it should work

How to repeat:
select foo from bar where foo in ((select foo from bar) union (select foo from bar));

Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.

Same cause as in the bug #21904, so I marked it as duplicate

Duplicates bug #21904

Re-Opening the report.

The problem in Bug#21904 is different from the one found here.
- in Bug#21904, a IN ((subselect)) is parsed correctly, but executed
as a scalar subquery where it should be executed as a table subquery.
- in this report, the IN clause is not of the form IN ((subselect)),
but of the form IN(subselect), with a subselect that contains a join.

The parser fails to read the subselect properly (ERROR 1064), so the root
cause affecting this report is different from Bug#21904.

There is an issue with parsing subselects that starts with a parenthesis.

See related bug#14654

See related bug#21614

Bug#21614 "UNION in views" was marked a duplicate of this bug.

See related Bug#4805

I will be working to this bug soon.

Bug #39589 was marked as duplicate of this one.

How is the progress of this bug? It's serious and there's no response for last 2 years! Why won't fix?

I've come across it on 5.1.39 while doing something like this:

SELECT 'bbb'
  , (
     (SELECT 'yyy' AS str)
     UNION
     (SELECT 'xxx' AS str)
     ORDER BY str LIMIT 1
) AS 'column';

Thanks for your reply.

Set back to "Verified" as nobody explained why it is "Won't fix"

This is not just a linux bug but a windows bug too. If possible, can someone change the OS of this report?

This bug also exists in MySQL 5.5.19 on Windows.
Please fix it.

Works:

SELECT app.GGUID, app.keyword
FROM   teamcrm1.APPOINTMENT0 AS app
WHERE  app.GGUID IN (
   SELECT rel.GUID2 FROM teamcrm1.TableRelation AS rel
   UNION
   (SELECT rel.GUID1 FROM teamcrm1.TableRelation AS rel)
);

Does not work:

SELECT app.GGUID, app.keyword
FROM   teamcrm1.APPOINTMENT0 AS app
WHERE  app.GGUID IN (
   (SELECT rel.GUID2 FROM teamcrm1.TableRelation AS rel)
   UNION
   (SELECT rel.GUID1 FROM teamcrm1.TableRelation AS rel)
);

SQL with the extra parentheses is generated by eclipselink UNIONs so there is no obvious workaround

Already fixed in trunk.

I'm very pleased to hear that this has been fixed but it is not obvious from the bug status!

Fixed by WL#8083 in 8.0.0.

Posted by developer:
 
Fixed in 8.0.0.

The parser now accepts parentheses around query expressions. For example,
(SELECT ... UNION SELECT ...) is permitted.