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:Microsoft Windows (Windows)
Assigned to: Timour Katchaounov CPU Architecture:Any

[1 Jul 2005 20:24] Bill Karwin
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.
[1 Nov 2005 10:08] Timour Katchaounov
In 5.0 everything works fine. Checked with latest 5.0.16 build.
[1 Nov 2005 12:06] Timour Katchaounov
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)
[1 Nov 2005 12:09] Timour Katchaounov
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
[1 Nov 2005 20:21] Paul Dubois
Noted in 4.1.16, 5.0.15 changelogs.