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 21:33	Modified:	28 Jan 2010 15:46
Reporter:	Bill Karwin (Candidate Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL 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	CPU Architecture:	Any

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.

Even though it's an old topic, I still would like things to be changed back. I realize it won't be done but I just want to know why the change was made. The point is it's not convenient in itself as you have to think out some fake column names to just make MySql group things by select expressions instead of column values. And it gets much more complicated in complex scripts where Sql-queries are not hardcoded but generated accoring to some user selected conditions, so table column names are used as indices in arrays with metadata on searched tables which are looked up by the script. So the same column names are used in multiple places and now changing a select alias (i.e. making it different than the original column name) will lead to non-trivial consequences. I don't want you to understand all intricacies of my situation but my idea is things worked and now they don't and it will take efforts to fix that while it doesn't seem that the change was something that necessary.

What was the idea and the reason behind the change? SQL standard compatibility? Or some practical advantage? I may miss some key point in this - maybe there is a smart coding practice that allows getting the same results even with MySql 5? For now, I just figured out that using the same expressions in Group By as in Select would do the trick but I'm not sure MySql understands me and doesn't do the same thing twice. At least, according to Explain output, it doesn't.

Is it possible that an option is introduced that emulates the old behavior? For ex., for the "Set Option" query, or maybe as a keyword for the select query (like STRAIGHT_FORWARD - Select GROUPBYSELECT IfNull(Id, 0) as Id ...).

Serguei, I don't think any query that worked before fails now because of this patch.  The patch fixes queries that should have worked according to the documentation, but failed.  Any query that worked before should still work.

Can you show an example of a hypothetical query that doesn't work as a result of this patch, and how you would have to change it to keep it working?  You don't have to reveal any private data or intricacies of your project.