Bug #14079 table alias for union-order by clause does not work with newer versions of mysq
Submitted: 17 Oct 2005 17:05 Modified: 17 Oct 2005 18:41
Reporter: Derek Choi Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.8 OS:Linux (Linux)
Assigned to: MySQL Verification Team CPU Architecture:Any

[17 Oct 2005 17:05] Derek Choi
Description:
The following query worked for mysql 4.0.15a (which was simplified to replicate bug):

select ds.deal_id from dealstatus ds union select ds.deal_id from dealstatus ds order by ds.deal_id;

but gave this error for 4.1.8:

ERROR 1109 (42S02): Unknown table 'ds' in order clause

I understand from the documentation that we cannot use the table names to specify order but it does not mention table aliases.  Is this a bug or is this removed from future versions of MySQL?

//

How to repeat:
use mysql server version 4.0.15a and run command :
select ds.deal_id from dealstatus ds union select ds.deal_id from dealstatus ds order by ds.deal_id;

use mysql server version 4.1.8 and run same command
[17 Oct 2005 18:41] MySQL Verification Team
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

Please read:

http://dev.mysql.com/doc/refman/5.0/en/union.html

mysql> select version();
+------------------+
| version()        |
+------------------+
| 4.1.16-debug-log |
+------------------+
1 row in set (0.00 sec)

mysql> select ds.deal_id from dealstatus ds union select ds.deal_id from dealstatus ds order by ds.deal_id;
ERROR 1109 (42S02): Unknown table 'ds' in order clause
mysql> (select ds.deal_id from dealstatus ds order by ds.deal_id) union (select ds.deal_id from dealstatus ds order by ds.deal_id);
Empty set (0.00 sec)
[17 Oct 2005 19:23] Derek Choi
Thank you for the quick response.

So I assume that current 4.1.x and future versions of mysql will not support the query that I stated.  Why is this?  I'm not sure if it part of ANSI SQL but I know that oracle supports this (as well as mysql 4.0.15a which is what we are currently using in our production environment)  

I just want to make sure because if we plan on upgrading to mysql 4.1.x or 5.x in the future, our application will break.