MySQL Bugs: #31541: column aliases in double quotation marks do not work in ORDER BY clause

Bug #31541	column aliases in double quotation marks do not work in ORDER BY clause
Submitted:	11 Oct 2007 17:13	Modified:	11 Oct 2007 18:48
Reporter:	Rolf Heckendorn	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.45	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	column alias, non SQL standard, order by, quotation marks

Description:
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.

How to repeat:
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      |
+---------+----------+

Suggested fix:
Allow double quotation marks for column aliases in ORDER BY again.

Thank you for the bug report. This isn't a bug:

mysql> SELECT
    ->   dtField1 AS `Field 1`, dtField2
    -> FROM
    ->   tdtaTest
    -> ORDER BY
    ->   `Field 1`;
+---------+----------+
| Field 1 | dtField2 |
+---------+----------+
| aa1     | aa2      | 
| bb1     | bb2      | 
| cc1     | cc2      | 
+---------+----------+
3 rows in set (0.00 sec)

Double quotes are not recognized as an identifier-quoting character unless the ANSI_QUOTES SQL mode is enabled.  Precede your query with this statement and you will obtain a different result:

SET sql_mode = "ANSI_QUOTES";

See:

http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html
http://dev.mysql.com/doc/refman/5.0/en/identifiers.html

Got it! Thank you.

It seems that when an alias that consists in double quotation marks it doesn't work in HAVING clause:

select SUM((UnitPrice*Quantity)-(UnitPrice*Quantity*Discount)) as "final price", orderID
from order_details
group by orderID
having "final price">1000