Bug #1743 Cannot Use Column Aliases in Expressions in ORDER BY
Submitted: 3 Nov 2003 17:13 Modified: 21 Feb 2009 22:56
Reporter: Dean Ellis Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version:4.0.17 OS:Any
Assigned to: CPU Architecture:Any

[3 Nov 2003 17:13] Dean Ellis
Description:
Expressions/Functions in the ORDER BY clause do not allow column aliases.

This may be a feature request rather than a bug.

How to repeat:
CREATE TABLE testtable( a int );
INSERT INTO testtable VALUES (1),(2),(3);

-- Can ORDER BY the alias
SELECT a AS b FROM testtable ORDER BY b;

-- Cannot ORDER BY the alias in an expression or function call
SELECT a AS b FROM testtable ORDER BY b + 0;

Suggested fix:
n/a
[17 Nov 2003 7:40] Sergei Golubchik
let it be a feature request.
MySQL has this behaviour for ages, and it's not that easy to change.

Meanwhile we'll clarify this in the manual.
[21 Feb 2009 22:56] Peter Gulutzan
With MySQL Version 5.1 I see no error.

mysql> CREATE TABLE testtable( a int );
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO testtable VALUES (1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT a AS b FROM testtable ORDER BY b;
+------+
| b    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql> SELECT a AS b FROM testtable ORDER BY b + 0;
+------+
| b    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)