Bug #7516 Wrong sort order when ORDER BY includes an ENUM column
Submitted: 23 Dec 2004 20:26 Modified: 26 Nov 2005 13:02
Reporter: Andrei Nazarenko Email Updates:
Status: Can't repeat Impact on me:
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:4.0.25 and 4.1.10a OS:Linux (Linux (SuSE 8.2))
Assigned to: CPU Architecture:Any

[23 Dec 2004 20:26] Andrei Nazarenko
When "ORDER BY" is used with more than one column and one of the columns is of type "ENUM", the rows are ordered correctly only for the first column, but not the rest.

How to repeat:
There are more columns in the original table, but these should be enough to reproduce the problem.

CREATE TABLE `problem_case` (
  `objid` int(10) unsigned NOT NULL default '0',
  `case_type` varchar(50) NOT NULL default '',
  `id` mediumint(8) unsigned NOT NULL default '0',
  `title` varchar(80) NOT NULL default '',
  `date_open` datetime NOT NULL default '0000-00-00 00:00:00',
  `date_modify` datetime NOT NULL default '0000-00-00 00:00:00',
  `history` text NOT NULL,
  `status` enum('Open','Closed') NOT NULL default 'Closed',
  PRIMARY KEY  (`objid`),
  UNIQUE KEY `id` (`id`),
  KEY `status` (`status`),
  FULLTEXT KEY `title` (`title`,`history`)

Now I would like to select the rows as follows. First give me all rows with the status 'Open' and then all 'Closed' rows sorted by their date of modification in descending order:

     UNIX_TIMESTAMP(date_modify) as dstamp,
     status ASC,
     date_modify DESC

After executing this query I get back "Open" rows first and "Closed" rows afterwards, which is correct. However, the "date_modify DESC" condition seems not to be followed at all. In fact, I get different results almost every time I execute the same query and sometimes it just sticks to the same pattern, ending up in a correct order, after which it "stays correct" for the subsequent queries.

Suggested fix:
I tried to replace the ENUM field with a simple VARCHAR and it worked as expected immediately.
[24 Dec 2004 16:29] Hartmut Holzgraefe
Can you please add a working dump and query that shows the problem?
This would help us to verify the problem quickly ...
[10 Feb 2005 18:29] Andrei Nazarenko
This is a dump to demonstrate the ORDER BY ENUM

Attachment: testcase.sql.gz (application/x-gzip, text), 5.38 KiB.

[10 Feb 2005 18:34] Andrei Nazarenko
I have attached a table dump to demonstrate the problem I am experiencing.

Executing the query:

SELECT * FROM `testcase` ORDER BY `status` ASC, `date_modify` DESC;

gives absolutely wrong result for the 'date_modify' field.

If you modify the table structure using: 

ALTER TABLE `testcase` CHANGE `status` `status` VARCHAR( 6 ) DEFAULT 'Closed' NOT NULL;

and execute similar query:

SELECT * FROM `testcase` ORDER BY `status` DESC, `date_modify` DESC;

everything is the way it should be.

Thanks for your time!
[11 Feb 2005 22:37] Jorge del Conde

There seems to be something wrong with the test-case that you uploaded.  After uncompressing the file, testcase.sql looks like a binary file.  Did you use mysqldump to create this dump ?  Can you re-upload it ?

[11 Feb 2005 22:46] Andrei Nazarenko
This is a testcase SQL which is ZIP'd instead of GZIP'd

Attachment: testcase.sql.zip (application/zip, text), 5.33 KiB.

[11 Feb 2005 22:48] Andrei Nazarenko
There is nothing wrong with the first attachment.
It is a feature of Linux's gzip command, which creates an archive inside of archive.
The testcase.sql is itself an archive, which you can extract again.
Don't ask me why it works like that - it's Linux :-)
(And by the way when I uncompress this on Linux machine, I do not need to do double uncompressing)

Anyay, I have uploaded the same file compressed with only simple ZIP.
Hope it helps. :)
[11 Feb 2005 23:39] Jorge del Conde
I was unable to reproduce this bug.  Is there any other information you can give us so that we can reproduce it ?

Also, can you please upload the output of your query ?

Thanks !
[12 Feb 2005 0:08] Andrei Nazarenko
Result dumps as described in comment from the 10th February

Attachment: results_output.zip (application/x-zip-compressed, text), 12.72 KiB.

[12 Feb 2005 0:09] Andrei Nazarenko
I have attached another ZIP file with the MySQL ouput, as requested.

Additionally, I have included my.cnf config file and the output of SHOW VARIABLES.

I am ready to provide you with any other info you might need.
[12 Feb 2005 0:25] Jorge del Conde

In my tests, I get the same results as the ones you provided in results_VARCHAR.txt.  My my status field is defined as:

`status` enum('Open','Closed') NOT NULL default 'Closed',
[12 Feb 2005 1:37] Andrei Nazarenko

First of all, a question: did you check all the rows of your result, or just the top ones with the status='Open' ?   Sometimes, everything seems ok at the top, but towards the end it starts to get messy.

Now some more explanation...

When originally discovered this issue, I knew it wouldn't be easy to reproduce. The behaviour seemed really spontaneous. Sometimes giving mostly the correct results with a few exceptions, and sometimes "stuck" on the wrong output forever.

I have just done a few more tests on my Linux box and here is what I have discovered. One SURE way [on my machine] to reprocude the problem:

* I restart MySQL server using "/etc/init.d/mysql restart" command
* I open MySQL connection from the command line using: mysql -u {...} -p{...}
* I execute commands USE {db}; SELECT {....};
* I get CORRECT(!) results all the time, no matter how many times I execute the same SELECT statement.
* Now, without leaving the open connection I open another (2nd, simultaneous) connection with the same user_id and specify wrong password for it. The login fails.
* In my first connection I execute the same SELECT again and ... the last 20 or so rows are in the wrong order!

It seems that the simultaneous connection is doing bad things...

Also, instead of failing the second login, one could login simultaneously (with the same account!) and do some arbitrary queries. This also spoils the original SELECT with ENUMs, but depending on which queries were executed the order of rows is different.

How about that?
[14 Feb 2005 0:32] Andrei Nazarenko
I have just found this:


It seems that it is an issue which has been known for a while (opened in 2003, updated in 2004) and... without a fix so far.

I can also confirm that I saw similar behaviour on Windows platform, but it was for a much bigger set of data and not possible to reproduce on demand. :(

Could I perhaps compile MySQL with some debugging options enabled and thus provide you with the detailed internal information about the query execution?

Could you please give me detailed information on how to do it?

[15 Feb 2005 13:50] Sergei Golubchik
Yes, it's possible to generate a debug trace (with --debug switch) - you need a mysqld binary with the debugging enabled, either get it from our download pages or build yourself.

But debug trace is *HUGE* and as you cannot easily repeat the bug but need to execute the same query over and over you can easily end up with multi-GB trace.

I'd suggest another approach - try to create a repeatable test case. May be a perl (or shell, or whatever) script that executes your query repeatedly (and reports when the results are not sorted properly), opens another connection in parallel and so on...
[15 Feb 2005 16:19] Andrei Nazarenko
A trace file obtained during the failing query

Attachment: mysqld.trace.zip (application/zip, text), 26.80 KiB.

[15 Feb 2005 16:27] Andrei Nazarenko
* Used "mysql-debug-4.0.23-pc-linux-i686.tar.gz"
* Started the server with "/usr/local/mysql/mysqld --debug -u mysql"
* Logged on as the user "sqladmin" (the "root" account)
* Changed DB to "thd_sandbox"
* Executed query "select * from testcase order by status asc, date_modify desc"
* The query result was correct
* Opened new connection to MySQL sever as the user "sqladmin" without specifying a password (the login failed)
* In the first connection executed the same query the 2nd time.
* The query result was garbled
* Stopped trace by killing the server with TERM signal

Note: there were no other queries/logons performed at the same time by other users. The only commands processed by the server are those described above.

Hope this helps to solve 2 year old mystery :-)
[22 Mar 2005 9:37] Andrei Nazarenko
Just to let you know that the problem is present on the latest versions of both 4.0.x and 4.1.x branches.
[13 Apr 2005 22:44] Jorge del Conde
Thanks for the bug report.  I was able to verify it w/4.0.24
[30 May 2005 14:00] Andrei Nazarenko
The bug is still present in the latest released 4.0.x version, i.e. 4.0.24.
So far I could not reproduce it in 4.1.12 -- has there been a fix for it?
I did not see it on the list of bugs fixed for 4.1.12
[25 Jul 2005 14:58] Andrei Nazarenko
It does not feel nice to be ignored. Anyway, here is the last attempt to draw attention to this very annoying bug.

* The bug is still present in 4.0.25  (using standard MySQL, i.e not Max).
* The bug does not seem to appear in 4.1.13 anymore, although there was never an "official fix" for it. (Am I doomed to test each new version to see if the bug is still there or not?)
* Here is how I reproduce it on SuSE 8.2 Intel 2xCPU's (4 logical CPUs with Hyperthreading enabled):
   - Restart MySQL 4.0.25 (fresh start, no connections from clients)
   - Logged on as the superuser using "mysql" command line client
   - Changed DB to where my testcase table is (see the attachment "testcase")
   - Executed query "select * from testcase order by status asc, date_modify desc;"
   - The query result was correct
   - While still being logged to MySQL, opened new connection to MySQL sever as the superuser again (from new terminal session) without specifying
a password (the login failed)
   - In the first connection executed the same query the 2nd time.
   - The query result is now incorrect
   - There were no other queries/logons performed at the same time by other
users. The only commands processed by the server are those described above.
[22 Aug 2005 17:57] Peter Volk
I can can duplicate the bug on 5.0.11, 4.1.13 and 4.0.23 on windows as well. Here is how you can replicate the bug form my files: 

Load the my datatbase dump (peter.sql).

mysql> SELECT DISTINCT field_11 FROM tbl_main_table ORDER BY field_11

this will give you the following on all versions:

| field_11        |
|                 |
| Loeffler-Peters |
| Busche          |
| Dittmann        |
| Donohue         |
| Erber           |
| Heins           |
| Geidel          |
| Herguth         |
| Krause          |
| Steudel         |
| Birnstein       |
| Wagner          |
| Horn            |
| Helmholz        |
| Bennung         |
16 rows in set (0.00 sec)

Obviously the order is wrong but it is order by the internal id from the table for the field. My guess is that the translation from the id to the actual varchar values takes place befor the dataset goes through the order by operator. 

I'll be doing some codewlaking to see if I can find something since the bug has been open for a while.

[29 Aug 2005 14:41] Valeriy Kravchuk

I see no "peter.sql" among the files attached to this bug report.


as for bug http://bugs.mysql.com/bug.php?id=967, I can't repeat it now:

mysql> select version();
| version()      |
| 5.0.11-beta-nt |
1 row in set (0.01 sec)

mysql> CREATE TABLE Test(
    ->   Type enum('a','b') NOT NULL,
    ->   Length tinyint unsigned NOT NULL
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO Test VALUES ('a', 1);
Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO Test VALUES ('a', 2);
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO Test VALUES ('a', 3);
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO Test VALUES ('b', 3);
Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO Test VALUES ('b', 2);
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO Test VALUES ('b', 1);
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM Test ORDER BY Type, Length;
| Type | Length |
| a    |      1 |
| a    |      2 |
| a    |      3 |
| b    |      1 |
| b    |      2 |
| b    |      3 |
6 rows in set (0.00 sec)

(it is not repeatable in 4.1.13 as well)

So, do you people see the same repetable bug on latest 4.1.x versions of MySQL?
[29 Aug 2005 14:51] Andrei Nazarenko
Valeriy, to answer your question, please read carefully what I wrote on 30 May and 25 Jul. Also, note that it was not me who opened bug #967. I just found it while searching through the buglist. Finally, the bug was never reported for 5.0.x version, so I am not sure why you test it there.
[29 Aug 2005 15:01] Peter Volk
Hi All,

this is not a bug its a feature,

see http://dev.mysql.com/doc/mysql/en/enum.html
and http://bugs.mysql.com/bug.php?id=12746

I reported the bug under a higher prio. and the reaction is correct. The Bug described here is not a Bug its a feature :). The sorting of enum fields is by the index of the element. since the ENUM field is not specified in the SQL Standart it is a MySql custom Datatype and they can sort how ever they whant to. 

A way to do a workaround is this:


this will sort it correctly.

[29 Aug 2005 15:09] Andrei Nazarenko
Peter, sorry, but you have totally confused the matters here.
Your "bug/feature" has nothing to do with the one reported by me.
Please read the very first post here carefully and see it for yourself.

You are trying to sort the ENUM-typed records. They will be sorted by the order which corresponds to the one that was defined during table creation / alteration. For example, if you defined your field as ENUM ('b','c','d','a'), that *IS* the correct ascending order for sorting, and yes that *IS* a feature (and a very useful one!).

The bug originally opened here is NOT about this.
[1 Sep 2005 14:52] Valeriy Kravchuk
Tried to repeat on latest 4.0.26-BK, on my Fedora Core box, but with no luck.

I used your test case and sequence of actions described in your message of 25 Jul 16:58.

[openxs@Fedora 4.0]$ uname -a
Linux Fedora 2.4.22-1.2115.nptl #1 Wed Oct 29 15:42:51 EST 2003 i686 i686 i386 GNU/Linux
[openxs@Fedora 4.0]$ ps -ef | grep mysqld
openxs    9135  8916  0 17:33 pts/2    00:00:00 grep mysqld
[openxs@Fedora 4.0]$ bin/mysqld_safe &
[1] 9136
[openxs@Fedora 4.0]$ Starting mysqld daemon with databases from /home/openxs/dbs

[openxs@Fedora 4.0]$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.26

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select status, date_modify from testcase order by status asc, date_modify desc;


| Closed | 2000-05-11 00:00:58 |
| Closed | 2000-05-10 23:59:08 |
| Closed | 2000-05-08 16:32:37 |
| Closed | 2000-05-04 15:38:54 |
| Closed | 2000-05-04 15:37:28 |
| Closed | 2000-05-04 15:31:57 |
| Closed | 2000-05-04 15:30:36 |
299 rows in set (0.00 sec)

The results were ordered correctly. Then, in the other terminal:

[openxs@Fedora 4.0]$ bin/mysql -uroot -pwrong
ERROR 1045: Access denied for user: 'root@localhost' (Using password: YES)

Then, in the original terminal I repeated the select:

| Closed | 2000-05-11 00:00:58 |
| Closed | 2000-05-10 23:59:08 |
| Closed | 2000-05-08 16:32:37 |
| Closed | 2000-05-04 15:38:54 |
| Closed | 2000-05-04 15:37:28 |
| Closed | 2000-05-04 15:31:57 |
| Closed | 2000-05-04 15:30:36 |
299 rows in set (0.00 sec)

mysql> select version();
| version() |
| 4.0.26    |
1 row in set (0.00 sec)

The results were still ordered correctly.

Different queries from the other terminal also changed nothing. Sorry.
[2 Sep 2005 0:30] Andrei Nazarenko
Maybe I am digging in a completely wrong direction, but my machine has 4 CPU's (2 physical + 2 hyperthreaded). Could this be a difference in our configurations?

Also I am not sure if I am convinced by your "bin/mysqld_safe&" command. Is that "safe" flavour of MySQL the same as the standard one?

Also, please note that one of your colleagues,  Jorge del Conde *was* able to reproduce it. See the note from  [14 Apr 0:44].
[2 Sep 2005 12:46] Peter Volk
I've tested it with the testcase here and I could not repeat it. I was testing on a win2000 with 2 dual core CPUs (so virtualy 4) on my production server with 25q/s (just incase it is a sideeffect of something) version: 4.0.23.

[21 Sep 2005 7:39] Sergei Golubchik
mysqld_safe is a convenience script to start mysqld. take a look.
[25 Nov 2005 23:06] Peter Volk
Hi everyone this is a douplicate bug to bug#14237 the problem is the index on the enum field.
[26 Nov 2005 13:02] Andrei Nazarenko
I do not see how it is a duplicate. It *may* be related (which needs investigation) but not duplicate at all.