Bug #7360 Subquery containing a union incorrectly gives error 1248

• Submitted: 16 Dec 2004 20:29
• Modified: 8 Feb 2005 13:15
• Reporter: Andy Crain
• Status: Duplicate
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.1.7
• OS: Windows (WinXP)
• Assigned to: Assigned Account
• CPU Architecture: Any

[16 Dec 2004 20: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 4: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 13: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 13: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