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: | |
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
[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.