MySQL Bugs: #28886: Union, Sort and Enum

Bug #28886	Union, Sort and Enum
Submitted:	4 Jun 2007 22:01	Modified:	10 Apr 2014 15:41
Reporter:	Sherif Zaroubi	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.1, 5.5, 5.6, 5.7	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Union sort enum order by

Description:
There seems to be a problem with Unions when sorting enum fields. The sorting seems as it's being done on the "enum value" not the "enum index".

How to repeat:
I have a query as so:

select * from (
    
    (select enumField from tbl_inner1)
     UNION
    (select enumField from tbl_inner2)
     

) as tbl1 

order by enumField

Assuming that enumField is of type ENUM('c','b','a') (notice that if I sort the num on the values it should sort (a,b,c) but if I sort on the indices I should get (c,b,a))

The result of that query is that the rows are going to be mis-sorted. It looks like the UNION operation is not remembering that enumField is an ENUM but treats it as a string. Therefore the sort will be a string sort on the enum not an int sort on it's index.

Suggested fix:
Re-write the query to "bubble up" the index of the enum

select * from (
    
    (select enumField, enumField+0 as enumIndex from tbl_inner1)
     UNION
    (select enumField, enumField+0 as enumIndex from tbl_inner2)
     

) as tbl1 

order by enumIndex (<--- notice sort on the enumIndex)

Thank you for the bug report. The server version you are reporting is pretty
older could you please upgrade to most recent released version and try again.
If you continue with the same behavior with the new version please provide
the complete test script with create table and data insert statements, the
result obtained and the expected resulted. Thanks in advance.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I can confirm that this bug is still in version 5.0.75.

"mysqld --version" on my Ubuntu 9.04 system says:

mysqld  Ver 5.0.75-0ubuntu10 for debian-linux-gnu on i486 ((Ubuntu))

The described workaround (generating an enum index field in each SELECT and ordering on this index instead of on the original enum value) also appears to work for me.

This still appears to be a problem on 5.6.12

Also it is on Linux as well as windows

This is indeed a bug. Sorting by ENUM works just fine in any query that does not utilize UNIONs. 

However, with any UNIONs the sort is wrong. Only if converted to the integer will sorting by ENUM work in UNIONs.

Here is a very descriptive test case:

mysql> create table e1 (id int auto_increment, enumcol enum ('c','b','a'), primary key (id));
Query OK, 0 rows affected (0.15 sec)

mysql> create table e2 (id int auto_increment, enumcol enum ('c','b','a'), primary key (id));
Query OK, 0 rows affected (0.05 sec)

mysql> insert into e1 (enumcol) values ('b'),('c'),('a');
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into e2 (enumcol) values ('c'),('b'),('a');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from ( (select enumcol, enumcol + 0 as enumindex from e1) UNION ALL (select enumcol, enumcol + 0 as enumindex from e2)) as tbl order by enumcol;
+---------+-----------+
| enumcol | enumindex |
+---------+-----------+
| a       |         3 |
| a       |         3 |
| b       |         2 |
| b       |         2 |
| c       |         1 |
| c       |         1 |
+---------+-----------+
6 rows in set (0.09 sec)

mysql> select * from ( (select enumcol, enumcol + 0 as enumindex from e1) UNION (select enumcol, enumcol + 0 as enumindex from e2)) as tbl order by enumcol;
+---------+-----------+
| enumcol | enumindex |
+---------+-----------+
| a       |         3 |
| b       |         2 |
| c       |         1 |
+---------+-----------+
3 rows in set (0.00 sec)

mysql> select * from ( (select enumcol, enumcol + 0 as enumindex from e1) UNION (select enumcol, enumcol + 0 as enumindex from e2)) as tbl order by enumindex;
+---------+-----------+
| enumcol | enumindex |
+---------+-----------+
| c       |         1 |
| b       |         2 |
| a       |         3 |
+---------+-----------+
3 rows in set (0.00 sec)

mysql> (select enumcol, enumcol + 0 as enumindex from e1) UNION (select enumcol, enumcol + 0 as enumindex from e2) order by enumcol;
+---------+-----------+
| enumcol | enumindex |
+---------+-----------+
| a       |         3 |
| b       |         2 |
| c       |         1 |
+---------+-----------+
3 rows in set (0.00 sec)

mysql> select enumcol, enumcol + 0 as enumindex from e1 order by enumcol;
+---------+-----------+
| enumcol | enumindex |
+---------+-----------+
| c       |         1 |
| b       |         2 |
| a       |         3 |
+---------+-----------+
3 rows in set (0.01 sec)

This is a very clearly described bug report.

http://bugs.mysql.com/bug.php?id=72305 duplicate of this one.