MySQL Bugs: #7516: Wrong sort order when ORDER BY includes an ENUM column

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:	None
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

Description:
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`)
) TYPE=MyISAM;

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:

SELECT DISTINCT
     objid,
     UNIX_TIMESTAMP(date_modify) as dstamp,
     status
FROM
     problem_case
ORDER BY
     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.

Can you please add a working dump and query that shows the problem?
This would help us to verify the problem quickly ...

This is a dump to demonstrate the ORDER BY ENUM

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

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!

Hi!

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 ?

Thanks!

This is a testcase SQL which is ZIP'd instead of GZIP'd

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

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. :)

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 !

Result dumps as described in comment from the 10th February

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

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.

Andrei,

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',

Jorge,

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?

I have just found this:

http://bugs.mysql.com/bug.php?id=967

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?

Thanx

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...

A trace file obtained during the failing query

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

* 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 :-)

Just to let you know that the problem is present on the latest versions of both 4.0.x and 4.1.x branches.

Thanks for the bug report.  I was able to verify it w/4.0.24

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

It does not feel nice to be ignored. Anyway, here is the last attempt to draw attention to this very annoying bug.

Facts:
* 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.

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.

Peter

Peter,

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

Andrei,

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>
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>
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?

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.

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:

ORDER BY CAST(field AS CHAR)

this will sort it correctly.

Greetings
Peter

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.

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
/4.0/var

[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.

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].

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.

Peter

mysqld_safe is a convenience script to start mysqld. take a look.

Hi everyone this is a douplicate bug to bug#14237 the problem is the index on the enum field.

I do not see how it is a duplicate. It *may* be related (which needs investigation) but not duplicate at all.