Bug #15795 Unknown column 'xxxx' in 'order clause' when using alias table/column
Submitted: 16 Dec 2005 0:00 Modified: 19 Dec 2005 18:12
Reporter: Dave Juntgen Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.16 OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[16 Dec 2005 0:00] Dave Juntgen
Description:
Upgrading to MySQL 4.1 from 4.0 caluses ORDER BY on `table_alias`.`column_alias`to fail.  The work around is to remove the alias table from the order by.

How to repeat:
CREATE TABLE foo (a char(10),b char(10));

--WORKS:

SELECT alias_table.a AS alias_column FROM foo AS alias_table ORDER BY alias_column;

--DOES NOT WORK:

SELECT alias_table.a AS alias_column FROM foo AS alias_table ORDER BY alias_table.alias_column;

Suggested fix:
N/A
[16 Dec 2005 11:47] Valeriy Kravchuk
Thank you for a problem report. Yes, everything works just as you described:

mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.17    |
+-----------+
1 row in set (0.01 sec)

mysql> CREATE TABLE foo (a char(10),b char(10));
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT alias_table.a AS alias_column FROM foo AS alias_table ORDER BY
    -> alias_column;
Empty set (0.05 sec)

mysql> SELECT alias_table.a AS alias_column FROM foo AS alias_table ORDER BY alias_table.alias_column;
ERROR 1054 (42S22): Unknown column 'alias_table.alias_column' in 'order clause'
mysql> SELECT alias_table.a AS alias_column FROM foo AS alias_table ORDER BY alias_table.a;
Empty set (0.00 sec)

But I think it is not a bug, because the manual (http://dev.mysql.com/doc/refman/4.1/en/select.html) clearly describes:

"Columns selected for output can be referred to in ORDER BY and GROUP BY  clauses using column names, column aliases, or column positions."

Column name (even with table_name.) works, column alias also works. Why do you think your syntax should work? It is not explicitely listed in the manual.
[16 Dec 2005 13:11] Dave Juntgen
This syntax should work becuase it worked in MySQL 4.0.
[16 Dec 2005 14:59] Dave Juntgen
C program that dumps out the MYSQL_FIELD structure for a given field # on a the returned result.

Attachment: dave.c (text/plain), 1.77 KiB.

[19 Dec 2005 3:01] Dave Juntgen
If this isn't a bug then why does the example work in MySQL 4.0?
[19 Dec 2005 8:01] Valeriy Kravchuk
Sorry, but this behaviour was explicitely changed in 4.1.4, and it is documented (http://dev.mysql.com/doc/refman/4.1/en/news-4-1-4.html):

"Support of usage of column aliases qualified by table name or alias in ORDER BY and GROUP BY was dropped. For example the following query SELECT a AS b FROM t1 ORDER BY t1.b is not allowed. One should use SELECT a AS b FROM t1 ORDER BY t1.a or SELECT a AS b FROM t1 ORDER BY b instead. This was non-standard (since aliases are defined on query level not on table level) and caused problems with some queries."

There are many incompatible changes among versions 4.0 and 4.1. I beieve, they are all documented. This is just one of them. Previous behaviour was non-standard. I am sorry, but I can't change it.
[19 Dec 2005 18:12] Dave Juntgen
I appoligize, I searched all over the MySQL site before submitting this bug report.