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

[4 Nov 2005 19:07] Mark Leith
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));
[10 Nov 2005 13:48] Sergey Petrunya
See also BUG#4805
[20 Nov 2005 3:47] Kolbe Kegel
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) );
[5 May 2006 19:30] Sergey Petrunya
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).
[5 May 2006 19:37] Sergey Petrunya
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.
[19 May 2006 15:41] Bugs System
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
[4 Jun 2006 15:50] Sergey Petrunya
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.
[7 Jun 2006 11:05] Bugs System
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
[18 Aug 2006 11:38] Bugs System
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.
[23 Aug 2006 3:46] Marc ALFF
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
[31 Aug 2006 15:01] Bugs System
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.
[1 Sep 2006 19:09] Marc ALFF
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.
[4 Sep 2006 10:35] Georgi Kodinov
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.
[19 Sep 2006 8:23] Georgi Kodinov
Pushed in 5.0.26/5.1.12-beta
[24 Oct 2006 21:21] Paul DuBois
Noted in 5.0.26, 5.1.12 changelogs.