MySQL Bugs: #14237: Distinct breaks order of results with enum fields

Bug #14237	Distinct breaks order of results with enum fields
Submitted:	23 Oct 2005 14:55	Modified:	25 May 2006 12:32
Reporter:	Peter Volk (Basic Quality Contributor)
Status:	Not a Bug
Category:	Server: Optimizer	Severity:	S1 (Critical)
Version:	ALL	OS:	Any (ALL)
Assigned to:	Georgi Kodinov	Target Version:

Description:
When creating a table with enums and executing a 

SELECT DISTINCT field FROM table ORDER BY CAST(field as CHAR); 

the result is not sorted alphabetically and the DISTINCT is not correctly
evaluated->duplicate entries show up.

When executing 

SELECT field FROM table ORDER BY CAST(field as CHAR); 

The correct order is returned.

This is repeatable on versions: 5.0.13,4.0.23,4.1.15 on Windows. Was not able to repeat
on Linux because I don't have a Linux dev. system ;).

How to repeat:
Create a database and dump the create.sql file into the database.

Then execute:

mysql> SELECT DISTINCT field_11 FROM tbl_main_table ORDER BY CAST(field_11 as CHAR);

+-----------+
| field_11  |
+-----------+
| bennung   | <- Wrong Order 
| Wagner    | <- Wrong Order 
| Dittmann  |
| Donohue   |
| Erber     |
| Heins     |
| Herguth   |
| Geidel    |
| bennung   | <- duplicate ENTRY!!
| Krause    |
| Helmholz  |  <- Wrong Order 
| Steudel   |   as of here pure chaos
| Busche    |
| Birnstein |
| Horn      |
+-----------+
15 rows in set (0.00 sec)

-> Completly wrong resultset

Then execute:

mysql> SELECT field_11 FROM tbl_main_table ORDER BY CAST(field_11 as CHAR);
+-----------+
| field_11  |
+-----------+

bennung
.
.
bennung
Birnstein
.
.
Birnstein
Birnstein
Birnstein
Busche
Busche
.
.
.
Busche
Busche
Dittmann
.
.
.
Dittmann
Donohue
Donohue
.
.
.
Donohue
Donohue
Erber
.
.
.
Erber
Erber
Geidel
.
.
.
Geidel
Heins
.
.
.
Heins
Heins
Helmholz
Helmholz
.
.
.
Helmholz
Helmholz
Herguth
.
.
.
Herguth
Herguth
Horn
Horn
.
.
.
Horn
Horn
Krause
Krause
.
.
.
Krause
Krause
Loeffler-Peters
Loeffler-Peters
.
.
.
Loeffler-Peters
Loeffler-Peters
Steudel
Steudel
.
.
.
Steudel
Steudel
Wagner
Wagner
Wagner

->kompletly correct order.

Suggested fix:
?

Create file to repeat the bug

Attachment: create.sql (application/octet-stream, text), 77.60 KiB.

Also when changing the ENUM filed to a VARCHAR(255) field the correct orcer is returned

Thanks for the bug report. I was able to reproduce the bug on Windows.

Also repeatable on Linux

Hey,

I've trackt the bug down so far that it has to do with the CAST function in the ORDER BY
clause in kombination with the created index. 

when removing the index from the table the order is correct and the douplicate entries
dissapear:

mysql> alter table tbl_main_table drop index field_1_2;
mysql> SELECT DISTINCT field_11 FROM tbl_main_table ORDER BY CAST(field_11 as
CHAR);

+-----------------+
| field_11        |
+-----------------+
| bennung         |
| Birnstein       |
| Busche          |
| Dittmann        |
| Donohue         |
| Erber           |
| Geidel          |
| Heins           |
| Helmholz        |
| Herguth         |
| Horn            |
| Krause          |
| Loeffler-Peters |
| Steudel         |
| Wagner          |
+-----------------+
15 rows in set (0.02 sec)

This is the perfect order

It is also repeatable when creating a table with numbers in the enum and then casting the
enum to DECIMAL. So It seems to be a problem with the cast function in combination with an
indexscan.

ok....It seems to be that the problem accors when the field_type of the field in the
orderby statement is different to the field_type in the order by part.

mysql> select distinct CAST(field_11 as CHAR) from tbl_main_table ORDER BY cast(field_11
as CHAR);

returns the correct values.

Peter

I've got same results on Linux Suse 9.3.

Thank you for taking the time to report a problem.  Unfortunately
you are not using a current version of the product your reported a
problem with -- the problem might already be fixed. Please download
a new version from http://www.mysql.com/downloads/

If you are able to reproduce the bug with one of the latest versions,
please change the version on this bug report to the version you
tested and change the status back to "Open".  Again, thank you for
your continued support of MySQL.

Additional info:

Tested with 5.0.23-BK/4.1.21-BK. All the queries seem to return sets in the correct sort
order.