Bug #4873 Union with aliases

• Submitted: 3 Aug 2004 14:58
• Modified: 5 Aug 2004 21:57
• Reporter: Lay András
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.0.20
• OS: Linux (Linux)
• CPU Architecture: Any

[3 Aug 2004 14:58] Lay András

Description:
On 4.0.18 works union with aliases in order by, but on 4.0.20 not.

How to repeat:
Here is the simple table:

mysql> select * from lufi;
+----+------+
| id | duma |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
+----+------+
4 rows in set (0.00 sec)

On 4.0.18 version works:

mysql> select version();
+----------------+
| version()      |
+----------------+
| 4.0.18-Max-log |
+----------------+
1 row in set (0.00 sec)
 
mysql> (select l.* from lufi l where l.id<3)union(select l.* from lufi l where l.id>2)order by l.duma;
+----+------+
| id | duma |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
+----+------+
4 rows in set (0.00 sec)

But on 4.0.20 not:

mysql> select version();
+---------------------+
| version()           |
+---------------------+
| 4.0.20-standard-log |
+---------------------+
1 row in set (0.00 sec)
 
mysql> (select l.* from lufi l where l.id<3)union(select l.* from lufi l where l.id>2)order by l.duma;
ERROR 1054: Unknown column 'l.duma' in 'ORDER BY'

[5 Aug 2004 21:57] Hartmut Holzgraefe

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:

Changed in release 4.0.19:

Non-standard behavior of UNION statements has changed to the standard ones. So far, a table name in the ORDER BY clause was tolerated. From now on a proper error message is issued (Bug #3064).

http://dev.mysql.com/doc/mysql/en/News-4.0.19.html