MySQL Bugs: #29199: Order by alias does not work when alias is in ''

Bug #29199	Order by alias does not work when alias is in ''
Submitted:	19 Jun 2007 8:36	Modified:	22 Jun 2007 4:41
Reporter:	Justin Zhang	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S2 (Serious)
Version:	5.0.41	OS:	MacOS
Assigned to:		CPU Architecture:	Any
Tags:	alias, order by

Description:
when doing sorting by column's alias, the result does not sort properly if the alias is quoted in '', 

e.g. the following query does not work
select id, testint, testdecimal, testString as 'TestCol', log_time from test1 order by 'TestCol';

but the following query works

select id, testint, testdecimal, testString as 'TestCol', log_time from test1 order by TestCol;

tested working fine in 5.0.22-Debian_0ubuntu6.06.2-log and earlier 5.0.1X on mac osx

How to repeat:
step 1: 

CREATE TABLE `test1` (
`id` INT( 32 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`testint` INT NOT NULL ,
`testdecimal` DECIMAL( 10, 2 ) NOT NULL ,
`testString` VARCHAR( 255 ) NOT NULL ,
`log_time` DATETIME NOT NULL
) TYPE = innodb CHARACTER SET utf8 COLLATE utf8_general_ci;

insert into test1 (testint, testdecimal, testString, log_time) values (1,1.1,'EEE', now());
insert into test1 (testint, testdecimal, testString, log_time) values (2,2.1,'FFF', now());
insert into test1 (testint, testdecimal, testString, log_time) values (3,3.1,'DDD', now());
insert into test1 (testint, testdecimal, testString, log_time) values (4,4.1,'CCC', now());
insert into test1 (testint, testdecimal, testString, log_time) values (5,5.1,'BBB', now());
insert into test1 (testint, testdecimal, testString, log_time) values (6,6.1,'AAA', now());

now test : not working with "order by 'TestCol'", but works in 2nd case: "order by TestCol;"

mysql> select id, testint, testdecimal, testString as 'TestCol', log_time from test1 order by 'TestCol';
+----+---------+-------------+---------+---------------------+
| id | testint | testdecimal | TestCol | log_time            |
+----+---------+-------------+---------+---------------------+
|  1 |       1 |        1.10 | EEE     | 2007-06-19 10:00:58 | 
|  2 |       2 |        2.10 | FFF     | 2007-06-19 10:00:58 | 
|  3 |       3 |        3.10 | DDD     | 2007-06-19 10:00:58 | 
|  4 |       4 |        4.10 | CCC     | 2007-06-19 10:00:58 | 
|  5 |       5 |        5.10 | BBB     | 2007-06-19 10:00:58 | 
|  6 |       6 |        6.10 | AAA     | 2007-06-19 10:00:58 | 
+----+---------+-------------+---------+---------------------+

mysql> select id, testint, testdecimal, testString as 'TestCol', log_time from test1 order by TestCol;
+----+---------+-------------+---------+---------------------+
| id | testint | testdecimal | TestCol | log_time            |
+----+---------+-------------+---------+---------------------+
|  6 |      61 |        6.11 | AAA     | 2007-06-19 10:00:58 | 
|  5 |       5 |        5.10 | BBB     | 2007-06-19 10:00:58 | 
|  4 |       4 |        4.10 | CCC     | 2007-06-19 10:00:58 | 
|  3 |       3 |        3.10 | DDD     | 2007-06-19 10:00:58 | 
|  1 |       1 |        1.10 | EEE     | 2007-06-19 10:00:58 | 
|  2 |       2 |        2.10 | FFF     | 2007-06-19 10:00:58 | 
+----+---------+-------------+---------+---------------------+

Suggested fix:
not sure, seems to be parser error when it deal with alias

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please read carefully about Database, table, index, column, and alias identifiers at http://dev.mysql.com/doc/refman/5.0/en/identifiers.html

Are you saying that in the current and future version of mysql , I can no longer use "'" as identifier, but have to use "`"?

it is rather confusing as why it was okay in earlier version but not now.

though I now agree that it is not a bug, but since "'" is allowed in earlier version of mysql but suddenly disallow in the new version, it is very user unfriendly as I am sure I am not the only one uses that .
where is the backward compatibility?

SQL standard allows to enclose column aliases in double quotation marks (in SELECT part and in ORDER BY part). This is to support spaces in alias names.

In earlier versions MySQL accepted this standard behaviour, in actual versions not.

So maybe this is a bug anyway, for MySQL aims to be near SQL standards...

Just to try out:

CREATE TABLE tdtaTest
(
dtField1 varchar(100),
dtField2 varchar(100)
);

INSERT INTO tdtaTest (dtField1,dtField2) VALUES ('cc1','cc2');
INSERT INTO tdtaTest (dtField1,dtField2) VALUES ('bb1','bb2');
INSERT INTO tdtaTest (dtField1,dtField2) VALUES ('aa1','aa2');

SELECT
  dtField1 AS "Field 1", dtField2
FROM
  tdtaTest
ORDER BY
  "Field 1";

And the output will be unsorted:

+---------+----------+
| Field 1 | dtField2 |
+---------+----------+
| cc1     | cc2      |
| bb1     | bb2      |
| aa1     | aa2      |
+---------+----------+

I too faced this bug, especially when I wanted to use an alias with a space and use the same in order by clause. But found another way that we can use the backtick (`) character to enclose the alias instead of quotes.
Hope it helps.