MySQL Bugs: #3064: get "Unknown Table" error when using ORDER BY with UNION

Bug #3064	get "Unknown Table" error when using ORDER BY with UNION
Submitted:	4 Mar 2004 11:24	Modified:	24 Mar 2004 6:52
Reporter:	Nathan Jensen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.0	OS:	Windows (Windows)
Assigned to:		CPU Architecture:	Any

Description:
I get an "Unknown Table" error when using ORDER BY with UNION in the following query:

SELECT t.* 
FROM Topics t 
JOIN Folders f ON t.FolderID = f.FolderID 
JOIN Projects p ON f.ProjectID = p.ProjectID 
WHERE p.ProjectID = 3 
UNION SELECT t.* 
FROM Topics t 
WHERE ProjectID = 3 
ORDER BY t.CreateDate DESC

If I omit the ORDER BY, it works fine.

How to repeat:
see descr.

Suggested fix:
see descr.

Verifying against 4.1.2/Linux, with a similar issue in 4.0.19.

The question really is what the final ORDER BY is sorting.  Documentation implies that it should be the second SELECT as parentheses have not been used to seperate the ORDER BY from the individual SELECT, although it seems to be getting applied to either the total set or the first SELECT (depending on which version is used).

USE mysql;
SELECT host FROM user AS b UNION SELECT host FROM user ORDER BY user.host;
SELECT host FROM user AS b UNION SELECT host FROM user ORDER BY b.host;

In 4.0.19, the first query fails with unknown table; second succeeds.
In 4.1.2, both fail with unknown table.

Whether or not there is a behavioral/technical bug here, the documentation for UNION needs to better cover this topic.

ChangeSet info:

hangeSet 1.1730.1.31 2004/03/22 16:35:15 Sinisa@sinisa.nasamreza.org
  union.result:

Patch pushed in 4.0.19