MySQL Bugs: #4805: UNION with parentheses around component SELECTs confuses parser within subquery

Bug #4805	UNION with parentheses around component SELECTs confuses parser within subquery
Submitted:	29 Jul 2004 16:15	Modified:	29 Sep 2008 16:51
Reporter:	Dean Ellis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.4	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	affects_connectors

Description:
Unions with parentheses are the component Selects, used within a subquery, cause the parser to throw an error regarding derived tables needing aliases.

How to repeat:
USE test;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 ( a int );
(SELECT a FROM t1) UNION ALL (SELECT a FROM t1) ORDER BY a;
SELECT * FROM ((SELECT a FROM t1) UNION ALL (SELECT a FROM t1) ORDER BY a) AS t2;
DROP TABLE t1;

Suggested fix:
n/a

I truly tried all the tricks in the parser, but none worked.

This is a second case of a bug that could not be fixed with current parser.

Multiple braces just confuse it.

This will have to wait for a new parser to be implemented, which is something that is being worked on.

The same issue was reported again for 5.0 as BUG#14654

A much more basic test case for MySQL 5.0.24a-community-nt on Windows XP:

mysql> (select 1 union select 2);
ERROR 1064 (42000)

See related Bug#25734

Using 5.0.27 on Fedora 6. . . the case (SELECT 1 UNION SELECT 2) still gives 1064, whereas removing the parens allows the query to succeed. Is this considered a bug?

Can't repeat. I believe this was fixed in 5.0.

mysql> USE test;
Database changed
mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> CREATE TABLE t1 ( a int );
Query OK, 0 rows affected (0.01 sec)

mysql> (SELECT a FROM t1) UNION ALL (SELECT a FROM t1) ORDER BY a;
Empty set (0.00 sec)

mysql> SELECT * FROM ((SELECT a FROM t1) UNION ALL (SELECT a FROM t1) ORDER BY a) AS t2;
Empty set (0.00 sec)

mysql> DROP TABLE t1;
Query OK, 0 rows affected (0.00 sec)