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

[11 Oct 2007 17:13] Rolf Heckendorn
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:

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');

  dtField1 AS "Field 1", dtField2
  "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.
[11 Oct 2007 17:34] MySQL Verification Team
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)
[11 Oct 2007 18:08] Paul DuBois
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";


[11 Oct 2007 18:48] Rolf Heckendorn
Got it! Thank you.
[2 Feb 2008 11:56] sss zzzz
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