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
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.45
• OS: Any
• CPU Architecture: Any
• Tags: column alias, non SQL standard, order by, quotation marks

[11 Oct 2007 17:13] Rolf Heckendorn

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.

[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";

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

[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