Bug #14237 | Distinct breaks order of results with enum fields | ||
---|---|---|---|
Submitted: | 23 Oct 2005 12:55 | Modified: | 25 May 2006 10:32 |
Reporter: | Peter Volk (Basic Quality Contributor) | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S1 (Critical) |
Version: | ALL | OS: | Any (ALL) |
Assigned to: | Georgi Kodinov | CPU Architecture: | Any |
[23 Oct 2005 12:55]
Peter Volk
[23 Oct 2005 12:56]
Peter Volk
Create file to repeat the bug
Attachment: create.sql (application/octet-stream, text), 77.60 KiB.
[23 Oct 2005 12:59]
Peter Volk
Also when changing the ENUM filed to a VARCHAR(255) field the correct orcer is returned
[23 Oct 2005 13:11]
Vasily Kishkin
Thanks for the bug report. I was able to reproduce the bug on Windows.
[25 Oct 2005 18:37]
Peter Volk
Also repeatable on Linux
[25 Oct 2005 20:28]
Peter Volk
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.
[25 Oct 2005 21:50]
Peter Volk
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
[17 Nov 2005 8:09]
Vasily Kishkin
I've got same results on Linux Suse 9.3.
[25 May 2006 10:32]
Georgi Kodinov
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.