MySQL Bugs: #11694: Column alias in ORDER BY works, but not if in an expression

Bug #11694	Column alias in ORDER BY works, but not if in an expression
Submitted:	1 Jul 2005 20:24	Modified:	1 Nov 2005 20:21
Reporter:	Bill Karwin (Candidate Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	4.1.12	OS:	Windows (Windows)
Assigned to:	Timour Katchaounov	CPU Architecture:	Any

Description:
Column aliases are documented as supported in ORDER BY, GROUP BY, and HAVING clauses.  

Columns aliases in ORDER BY clauses work, but only when referenced alone, without being part of an expression.

Expressions work in ORDER BY clauses and the others, but only when avoiding references to column aliases.  Only real column names can be used.

How to repeat:
CREATE TABLE `foo` ( `i` int(10) );
INSERT INTO `foo` (`i`) VALUES (10), (20);

The following two queries work as expected:

SELECT `i` AS BAR FROM `foo` ORDER BY BAR;
SELECT `i` AS BAR FROM `foo` ORDER BY 30 - `i`;

The following query  gives error "Unknown column 'BAR' in 'order clause'":

SELECT `i` AS BAR FROM `foo` ORDER BY 30 - BAR;

Suggested fix:
Support use of column aliases in expressions in ORDER BY, GROUP BY, and HAVING clauses.

In 5.0 everything works fine. Checked with latest 5.0.16 build.

Tested with latest 4.1.16 build - and everything works.

mysql> drop table if exists foo;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE foo ( i int(10) );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO foo (i) VALUES (10), (20);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT i AS BAR FROM foo ORDER BY BAR;
+------+
| BAR  |
+------+
|   10 |
|   20 |
+------+
2 rows in set (0.01 sec)

mysql> SELECT i AS BAR FROM foo ORDER BY 30 - i;
+------+
| BAR  |
+------+
|   20 |
|   10 |
+------+
2 rows in set (0.01 sec)

mysql> SELECT i AS BAR FROM foo ORDER BY 30 - BAR;
+------+
| BAR  |
+------+
|   20 |
|   10 |
+------+
2 rows in set (0.00 sec)

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Noted in 4.1.16, 5.0.15 changelogs.