Bug #7360 Subquery containing a union incorrectly gives error 1248
Submitted: 16 Dec 2004 21:29 Modified: 8 Feb 2005 14:15
Reporter: Andy Crain
Status: Duplicate
Category:Server Severity:S2 (Serious)
Version:4.1.7 OS:Microsoft Windows (WinXP)
Assigned to: Bugs System Target Version:

[16 Dec 2004 21:29] Andy Crain 
Description:
With the following table (both as InnoDB and MyIsam):
CREATE TABLE `foo` (                                                                     
                                                                                          
                                                                                          
                                                                                          
                                                            
`keyword_id` int(11) NOT NULL default '0',                                               
                                                                                          
                                                                                          
                                                                                          
                                                               
`record_id` int(11) NOT NULL default '0'                                                 
                                                                                          
                                                                                          
                                                                                          
                                                             
) ENGINE=InnoDB;
insert into `foo` (keyword_id, record_id) values (5, 1);
insert into `foo` (keyword_id, record_id) values (5, 2);
insert into `foo` (keyword_id, record_id) values (5, 3);
insert into `foo` (keyword_id, record_id) values (6, 1);
insert into `foo` (keyword_id, record_id) values (6, 2);
insert into `foo` (keyword_id, record_id) values (7, 1);
insert into `foo` (keyword_id, record_id) values (8, 1);
insert into `foo` (keyword_id, record_id) values (8, 2);
insert into `foo` (keyword_id, record_id) values (8, 3);

This query:
SELECT record_id
FROM bar T1
WHERE T1.record_id IN (
	SELECT T2.record_id FROM (
		(
			SELECT T3.record_id
			FROM bar T3
			WHERE keyword_id IN (5,6)
		) 
		UNION 
		(
			SELECT T4.record_id
			FROM bar T4
			WHERE keyword_id IN (7,8)
		)
	) AS T2
)

Gives the following error:
Error Code : 1248
"Every derived table must have its own alias"

I get this on similar queries as well. All have a union query as the inner query, so
perhaps this is part of the problem. The query is valid and should produce the following
(and does so in other rdbms):
-----------
record_id
-----------
1
2
3
1
2
1
1
2
3

How to repeat:
see above

Suggested fix:
I don't know
[25 Jan 2005 5:20] Sergey Petrunya 
The provided test case is fixed by fix for BUG#2435.
But still there exist valid queries with braces that produce parse errors.
[8 Feb 2005 14:15] Sergey Petrunya 
The problem is the same as in BUG#2435. Fix for BUG#2435 makes the query in this bug
report to work. Still, MySQL doesn't have full support for brackets + UNIONs. The
existing limitations will be documented.
[10 Nov 2005 14:43] Philip Mather 
I'm having a related problem, I'm attempting nested unions to produce unions of unions.
Yeah, I know that's border line insanity but I appear to be stuck with selecting into a
temp table?

Regards,
   Philip Mather