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

Bug #22457	Column alias in ORDER BY works, but not if in an expression
Submitted:	18 Sep 2006 23:33	Modified:	14 Dec 2006 4:51
Reporter:	Bill Karwin (Candidate Quality Contributor) (SCA)
Status:	Closed
Category:	Server	Severity:	S3 (Non-critical)
Version:	5.0.26-BK, 4.1.21, 5.0.24a	OS:	Linux (Linux, Windows XP)
Assigned to:	Georgi Kodinov	Target Version:

Description:
This may be related to bug #11694, which was marked fixed as of 4.1.16 and 5.0.16.   The
test cases in that bug are still fixed.  However, related faulty behavior is observed in
MySQL 4.1.21 and MySQL 5.0.24a.

One can create an alias for an expression in the select list:

SELECT <expr> AS alias_name ...

One can use the alias in ORDER BY clauses:

... ORDER BY alias_name

However, one cannot use the alias_name in an expression in the ORDER BY clause, if the
alias is for an expression in the select-list other than a simple column name.

How to repeat:
CREATE TABLE `foo` ( `i` int(10), `c` varchar(10) );
INSERT INTO `foo` (`i`) VALUES (10, 'ten'), (20, 'twenty');

The following two queries work as expected:

SELECT `i` AS num FROM `foo` ORDER BY num;
SELECT `i` AS num FROM `foo` ORDER BY 30 - `i`;
SELECT `c` AS word FROM `foo` ORDER BY word;
SELECT `c` AS word FROM `foo` ORDER BY UPPER(word);

The following queries give error "Unknown column 'xxx' in 'order clause'":

SELECT `i` + 1 AS num FROM `foo` ORDER BY 30 - num;
SELECT CONCAT(`c`, 'x') AS word FROM `foo` ORDER BY UPPER(word);

Suggested fix:
Column aliases should work in ORDER BY, GROUP BY, and HAVING clauses per the
documentation.  This should be supported whether the column aliases are for bare columns,
or are themselves for expressions.

To make the steps to reproduce more clear, I'll include the following.  The following also
works correctly; the alias is for a plain column in the select-list, not an expression:

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

Thank you for a bug report. Verified just as described with 5.0.26-BK on Linux:

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.26-debug |
+--------------+
1 row in set (0.00 sec)

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

mysql> INSERT INTO `foo` VALUES (10, 'ten'), (20, 'twenty');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

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

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

mysql> SELECT `c` AS word FROM `foo` ORDER BY word;
+--------+
| word   |
+--------+
| ten    |
| twenty |
+--------+
2 rows in set (0.00 sec)

mysql> SELECT `c` AS word FROM `foo` ORDER BY UPPER(word);
+--------+
| word   |
+--------+
| ten    |
| twenty |
+--------+
2 rows in set (0.01 sec)

mysql> SELECT `i` + 1 AS num FROM `foo` ORDER BY 30 - num;
ERROR 1054 (42S22): Unknown column 'num' in 'order clause'
mysql> SELECT CONCAT(`c`, 'x') AS word FROM `foo` ORDER BY UPPER(word);
ERROR 1054 (42S22): Unknown column 'word' in 'order clause'
mysql> SELECT `i` AS num FROM `foo` ORDER BY 30 - num;
+------+
| num  |
+------+
|   20 |
|   10 |
+------+
2 rows in set (0.01 sec)
mysql> SELECT `i` + 1 AS num FROM `foo` ORDER BY num;
+------+
| num  |
+------+
|   11 |
|   21 |
+------+
2 rows in set (0.00 sec)

Even if error is expected behaviour (although, it does not look consistent for me - see
last example), error message is misleading. So, it is a bug.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/14726

ChangeSet@1.2538, 2006-11-02 10:48:23+02:00, gkodinov@macbook.gmz +3 -0
  Bug #22457: Column alias in ORDER BY works, but not if in an expression
   The parser is allocating Item_field for references by name in ORDER BY
   expressions. The however may point not only to Item_field in the select
   list (or to a table column) but also to an arbitrary Item. This causing
   Item_field::fix_fields to throw an error about missing column.
   Fixed by substituting the Item_field for the reference with an Item_ref
   when not pointing to Item_field.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/14815

ChangeSet@1.2538, 2006-11-03 18:48:16+02:00, gkodinov@macbook.gmz +3 -0
  Bug #22457: Column alias in ORDER BY works, but not if in an expression
   The parser is allocating Item_field for references by name in ORDER BY
   expressions. Such expressions however may point not only to Item_field 
   in the select list (or to a table column) but also to an arbitrary Item. 
   This causes Item_field::fix_fields to throw an error about missing 
   column.
   The fix substitutes Item_field for the reference with an Item_ref when 
   not pointing to Item_field.

Pushed in 4.1.23/5.0.32/5.1.14-beta

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 bug fix. More information about accessing the source trees is
available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix for 4.1.23, 5.0.32, and 5.1.14.