Bug #27347 Sorting by an aliased quoted column does not sory correctly

• Submitted: 21 Mar 2007 17:24
• Modified: 21 Mar 2007 19:07
• Reporter: Scott Nebor
• Status: Not a Bug
• Category: MySQL Server: General
• Severity: S3 (Non-critical)
• Version: 5.0.33/5.0.37
• OS: Linux (Redhat 9)
• CPU Architecture: Any

[21 Mar 2007 17:24] Scott Nebor

Description:
Sorting by an aliased column that is quoted (i.e. "select Col1 as MyColumn FROM Foo ORDER BY 'MyColumn'" as opposed to "select Col1 as MyColumn FROM Foo ORDER BY MyColumn") does not sort correctly in 5.0.33 and above.  

In versions 5.0.26 and below, this type of query would sort by the aliased column.  In 5.0.33 and above, the results appear as if they were not sorted at all

Our test environment is red hat 9 using mysql 5.0.26 and 5.0.33 on a myIsam table

How to repeat:
Run the following:
CREATE TABLE Foo (Col1 INT (10) UNSIGNED DEFAULT '0');
insert into Foo SET Col1=2;
insert into Foo SET Col1=3;
insert into Foo SET Col1=1;

select Col1 as MyColumn FROM Foo ORDER BY 'MyColumn'

On mysql 5.0.26, you will get the results
MyColumn
1
2
3

On mysql 5.0.33 and above, you will get the results
MyColumn
2
3
1

Please note that if you run the query as follows
select Col1 as MyColumn FROM Foo ORDER BY MyColumn, both versions return the same results.  Different results are only returned when the order by alias column is wrapped in quotes

Suggested fix:
5.0.33 and above should return the following results for the query
"select Col1 as MyColumn FROM Foo ORDER BY 'MyColumn'"

MyColumn
1
2
3

[21 Mar 2007 19:07] Valeriy Kravchuk

Thank you for a problem report. Sorry, but this is not a bug. You should use either `` (MySQL native way) or "" (ANSI SQL way) to quote any identifiers, incuding aliases. You had used '' and, thus, ordered by some string literal. That is why you may got any order, including original insertion order, as in 5.0.37.