MySQL Bugs: #5852: ORDER BY doesn't recognize column names with UNIONs in select statements

Bug #5852	ORDER BY doesn't recognize column names with UNIONs in select statements
Submitted:	1 Oct 2004 17:43	Modified:	11 Oct 2004 20:05
Reporter:	Wayne Witzke	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0.20a	OS:	Windows (Windows 2000 Server)
Assigned to:		CPU Architecture:	Any

Description:
I've found that the Windows 2000 release 4.0.20a does not follow the behavior of the documentation for mySQL when trying to use UNION and ORDER BY.  I get error messages when I try.

How to repeat:
When I used a statement like this:

(SELECT Event.Date, TableA.Name, TableB.Name FROM Event, TableA, TableB WHERE Event.TableAID = TableA.ID AND Event.TableBID = TableB.ID) UNION (SELECT Event.Date, TableA.Name, '' FROM Event, TableA WHERE Event.TableAID = TableA.ID AND Event.TableBID IS NULL) ORDER BY Event.Date;

I get the message:

ERROR 1054: Unknown column 'Event.Date' in 'ORDER BY'

If I try the same statement on an identical database on a Linux release 4.0.17, I get the result set that I expected to get.

If I do the UNION without the ORDER BY clause, the statement works.

I know that, from reading the bug fixes for releases between 4.0.17 and 4.0.20a that there were issues related to UNION and lower_case_file_system/ lower_case_table_names...  I have lower_case_file_system OFF and lower_case_table_names set to 2 in 4.0.20a for Windows 2000.  For Linux, the lower_case_file_system flag doesn't even exist, and the lower_case_table_names is set to OFF.

Suggested fix:
The only fix that I can think of is to do the sorting of the result set in code.  For instance, in Perl, you'd do something like this:

use DBI;

my $dbh = DBI->connect(etc,etc,etc);

my $rows = $dbh->selectall_arrayref(qq{(SELECT Event.Date, TableA.Name, TableB.Name FROM Event, TableA, TableB WHERE Event.TableAID = TableA.ID AND Event.TableBID = TableB.ID) UNION (SELECT Event.Date, TableA.Name, '' FROM Event, TableA WHERE Event.TableAID = TableA.ID AND Event.TableBID IS NULL)});

@$rows = sort { $$a[0] cmp $$b[0] } @$rows;

Of course, this may not work properly in this case, because Perl's cmp does a strict alphabetic ordering, and that might not be sufficient to order by Date properly (haven't looked into it).

Or, you could try rolling back to 4.0.17, but I don't have a high degree of confidence in that working (considering the bug fixes mentioned between 4.0.17 and 4.0.20a).

You could also fix this with a Left Outer Join followed by a series of joins :-)

This is  a known deficiency of 4.0.

You must omit table name and it will work.

It had to be done that way in order to avoid some bugs.

This is fully fixed in 4.1.

Please do not submit the same bug more than once. An existing
bug report already describes this very problem. Even if you feel
that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments
to the original bug instead.

Thank you for your interest in MySQL.

Additional info:

duplicate of http://bugs.mysql.com/4098