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 12:24	Modified:	24 Mar 2004 7:52
Reporter:	Nathan Jensen
Status:	Closed
Category:	Server	Severity:	S1 (Critical)
Version:	4.0	OS:	Microsoft Windows (Windows)
Assigned to:	Sinisa Milivojevic	Target Version:

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