Bug #64524 Incorrect order when quoting an ORDER BY aliased column.

• Submitted: 2 Mar 2012 13:48
• Modified: 2 Mar 2012 14:21
• Reporter: Van Stokes
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.5.19 x64
• OS: Linux (Ubuntu 11.10 x86_64)
• CPU Architecture: Any
• Tags: alias, order by, quote

[2 Mar 2012 13:48] Van Stokes

Description:
This select statement:

SELECT
	( SELECT actnum FROM frtfwd WHERE fusrnm = q.loginname ) AS 'Acct',
	( SELECT actnam FROM frtfwd WHERE fusrnm = q.loginname ) AS 'Acct Name',
	( SELECT locnam FROM sched WHERE schnum = q.orgsched ) AS 'Origin Name',
	( SELECT prtnam FROM ports WHERE prtnum = q.dstprt ) AS 'Dest Name',
	( SELECT IF( wtp = 'E', ROUND( wgt / 2.2041 ), wgt ) FROM quotation_package WHERE quotationid = q.id) AS 'KGS',
	( SELECT IF( mtp = 'E', ROUND( msr / 35.314 ), msr ) FROM quotation_package WHERE quotationid = q.id) AS 'CBM',
	( SELECT econsp FROM frtfwd WHERE fusrnm = q.loginname) AS 'Sales Code' ,
	q.quotdate AS 'Date'
FROM quotation_master q
WHERE q.quotdate BETWEEN "2012-03-01" AND "2012-03-31"
ORDER BY 'Acct Name'

does not produce the same result as this statement.

SELECT
	( SELECT actnum FROM frtfwd WHERE fusrnm = q.loginname ) AS 'Acct',
	( SELECT actnam FROM frtfwd WHERE fusrnm = q.loginname ) AS 'Acct Name',
	( SELECT locnam FROM sched WHERE schnum = q.orgsched ) AS 'Origin Name',
	( SELECT prtnam FROM ports WHERE prtnum = q.dstprt ) AS 'Dest Name',
	( SELECT IF( wtp = 'E', ROUND( wgt / 2.2041 ), wgt ) FROM quotation_package WHERE quotationid = q.id) AS 'KGS',
	( SELECT IF( mtp = 'E', ROUND( msr / 35.314 ), msr ) FROM quotation_package WHERE quotationid = q.id) AS 'CBM',
	( SELECT econsp FROM frtfwd WHERE fusrnm = q.loginname) AS 'Sales Code' ,
	q.quotdate AS 'Date'
FROM quotation_master q
WHERE q.quotdate BETWEEN "2012-03-01" AND "2012-03-31"
ORDER BY `Acct Name`

Note how the ORDER BY column name (an alias) is quoted. The latter, ORDER BY `Acct Name`, works correctly.

How to repeat:
See above. Basically, alias a column and use a single(') or double quote(") to quote the order by column instead of the back ticks (`).

Suggested fix:
Allow use of the single and double quotes.

[2 Mar 2012 14:21] Valeriy Kravchuk

Looks like a duplicate of bug #29199. 'name' is considered as string constant, not alias/name/identified, thus the results.

Moreover, our manual clearly explains this at http://dev.mysql.com/doc/refman/5.5/en/problems-with-alias.html:

"In the select list of a query, a quoted column alias can be specified using identifier or string quoting characters:

SELECT 1 AS `one`, 2 AS 'two';

Elsewhere in the statement, quoted references to the alias must use identifier quoting or the reference is treated as a string literal. For example, this statement groups by the values in column id, referenced using the alias `a`:

SELECT id AS 'a', COUNT(*) AS cnt FROM tbl_name
  GROUP BY `a`;

But this statement groups by the literal string 'a' and will not work as expected:

SELECT id AS 'a', COUNT(*) AS cnt FROM tbl_name
  GROUP BY 'a';
"