MySQL Bugs: #14654: Cannot select from the same table twice within a UNION statement

Bug #14654	Cannot select from the same table twice within a UNION statement
Submitted:	4 Nov 2005 19:07	Modified:	24 Oct 2006 21:21
Reporter:	Mark Leith	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.0.16-BK	OS:	Linux (Linux, Any)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
When trying to select from the same table twice within a UNION statement you get a syntax error.

How to repeat:
DROP TABLE t1;

CREATE TABLE t1 (i INT);

SELECT * FROM t1 WHERE NOT EXISTS ((SELECT i FROM t1) UNION (SELECT i FROM t1));

See also BUG#4805

I don't think bug #4805 is related to this at all. Actually, I think bug #4805 has been resolved as of 4.1.15 and 5.0.15 (but probably some time before that actually).

Bug #14654 (this bug) is occurring because of something else.

This query generates no error:

SELECT * FROM t1 WHERE NOT EXISTS ( SELECT i FROM t1 UNION SELECT i FROM t1 );

But the original query, with parentheses, does:

SELECT * FROM t1 WHERE NOT EXISTS ( (SELECT i FROM t1) UNION (SELECT i FROM t1) );

The following occurred on Wed, Nov 23, 2005:

I've figured out the fix I was trying to create was getting uglier and uglier
(i.e. slow and bug-prone code). On the other hand, Postgres does support the
queries we have trouble with, and they use bison too. So apparrently the fix is
possible but I can't tell how big it will be.

I've given up on this and asked Antony to take over (mail cc'ed to Timothy).

Final status: the bug is still assigned to me. I'll need to study Postgres's appoach and Antony's suggestions to fix it. The fix will not be simple.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6643

George, 
The last patch only makes certain queries to work. For example,
SELECT * FROM t1 
WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1)));
still will not work. I think we'll need to create comprehensive fix here.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/7346

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/10618

ChangeSet@1.2263, 2006-08-18 14:37:48+03:00, gkodinov@macbook.gmz +3 -0
  Bug#14654 : Cannot select from the same table twice within a UNION statement     
   Made the parser to support parenthesis around UNION branches.
   This is done by amending the rules of the parser so it generates the correct
   structure.
   Currently it supports arbitrary subquery/join/parenthesis operations in the 
   EXISTS clause. 
   In the IN/scalar subquery case it will allow adding nested parenthesis only 
   if there is an UNION clause after the parenthesis. Otherwise it will just  
   treat the multiple nested parenthesis as a scalar expression.
   It adds extra lex level for ((SELECT ...) UNION ...) to accommodate for the
   UNION clause.

Reviewed the following patch:
 http://lists.mysql.com/commits/10618
and sent comments by email.

Lot of good work, but some remaining points need attention.
Changing the bug to In Progress

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/11171

ChangeSet@1.2263, 2006-08-31 18:00:25+03:00, gkodinov@macbook.gmz +3 -0
  Bug#14654 : Cannot select from the same table twice within a UNION statement     
   Made the parser to support parenthesis around UNION branches.
   This is done by amending the rules of the parser so it generates the correct
   structure.
   Currently it supports arbitrary subquery/join/parenthesis operations in the 
   EXISTS clause. 
   In the IN/scalar subquery case it will allow adding nested parenthesis only 
   if there is an UNION clause after the parenthesis. Otherwise it will just  
   treat the multiple nested parenthesis as a scalar expression.
   It adds extra lex level for ((SELECT ...) UNION ...) to accommodate for the
   UNION clause.

Reviewed :
http://lists.mysql.com/commits/11171

Patch approved with an action item,
regarding documentation.

Not sure if a 2nd reviewer is needed or not,
so not advancing the bug to "approved" yet.

If only my review is enought, let me know and I will advance the bug.

Note that there is a limitation that remains and will be addressed in the future.
Scalar sub-queries that contain UNION and the first SELECT of that UNION is within parenthesis will not work, e.g :

SELECT ..., ((SELECT ...) UNION ...) ...

Same is true for IN sub-queries, e.g :

SELECT ... FROM .... WHERE ... IN ((SELECT ...) UNION ...) ...

The workaround is not to use parenthesis around the first SELECT, e.g. :

SELECT ((SELECT a FROM t1) UNION (SELECT b FROM t2)) will not work, whereas
SELECT (SELECT a FROM t1 UNION SELECT b FROM t2) will.

Pushed in 5.0.26/5.1.12-beta

Noted in 5.0.26, 5.1.12 changelogs.