MySQL Bugs: #12746: Order By with enum fieds returns invalid order

Bug #12746	Order By with enum fieds returns invalid order
Submitted:	23 Aug 2005 5:21	Modified:	23 Aug 2005 12:25
Reporter:	Peter Volk (Basic Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.11, 4.1.13, 4.0.25	OS:	Any (Any)
Assigned to:		CPU Architecture:	Any

Description:
When you have a table that contains an enum field and the enums are not alphabeticaly ordered then an order by statement orders the result by the indexes of the enum values

How to repeat:
1. Load dump peter.sql into a database
2. execute: SELECT DISTINCT field_11 FROM tbl_main_table ORDER BY field_11

this will return the following result:
mysql> select Distinct field_11 FROM tbl_main_Table ORDER BY field_11;
+-----------------+
| field_11        |
+-----------------+
|                 |
| Loeffler-Peters |   <-Wrong
| Busche          |
| Dittmann        |
| Donohue         |
| Erber           |
| Heins           |
| Geidel          |    <-Wrong
| Herguth         |
| Krause          |
| Steudel         |
| Birnstein       |   <-Wrong
| Wagner          |
| Horn            |   <-Wrong
| Helmholz        |  <-Wrong
| Bennung         | <-Wrong
+-----------------+
16 rows in set (0.08 sec)

This also works with MyIsam tables

!! no workaround availeble !!

Suggested fix:
It seem like that the enum fields are not translated early enough from an indexed field to a real named field. The Order by operator only sees the indexes but not the real names.

the initial sql dump for the test

Attachment: peter.sql (text/plain), 93.34 KiB.

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

From the MySQL manual:

ENUM values are sorted according to the order in which the enumeration members were listed in the column specification. (In other words, ENUM values are sorted according to their index numbers.) For example, 'a' sorts before 'b' for ENUM('a', 'b'), but 'b' sorts before 'a' for ENUM('b', 'a'). The empty string sorts before non-empty strings, and NULL values sort before all other enumeration values. To prevent unexpected results, specify the ENUM list in alphabetical order. You can also use GROUP BY CAST(col AS VARCHAR) or GROUP BY CONCAT(col) to make sure that the column is sorted lexically rather than by index number.