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 7:21	Modified:	23 Aug 2005 14:25
Reporter:	Peter Volk (Basic Quality Contributor)
Status:	Not a Bug
Category:	Server	Severity:	S1 (Critical)
Version:	5.0.11, 4.1.13, 4.0.25	OS:	Any (Any)
Assigned to:		Target Version:

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.