Bug #1203 wrong sort order in select with character-set latin1_de
Submitted: 5 Sep 2003 5:02 Modified: 12 Dec 2003 1:18
Reporter: Michael Brunnbauer Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.0.12 OS:Linux (Linux 2.4.21)
Assigned to: CPU Architecture:Any

[5 Sep 2003 5:02] Michael Brunnbauer
Description:
The sorting order is wrong in certain select statements and can change from
query to query (for the same select).

If I restart the server, the first query always seems to have the correct sorting order but the problem comes back at the second query or later.

It seems the problem cannot be reproduced without character-set latin1_de

How to repeat:
create table test (
type1 enum('X','Y'),
name varchar(255) binary not null,
type2 enum('A','B','C')
);

insert into test values ('X','aa','A');
insert into test values ('X','ba','A');
insert into test values ('X','ca','A');
insert into test values ('X','ab','B');
insert into test values ('X','bb','B');
insert into test values ('X','cb','B');
insert into test values ('Y','aa','A');
insert into test values ('Y','ba','A');
insert into test values ('Y','ca','A');
insert into test values ('Y','ab','B');
insert into test values ('Y','bb','B');
insert into test values ('Y','cb','B');

select type2,name from test where type1='X' order by type2,name;

+-------+------+
| type2 | name |
+-------+------+
| A     | ba   |
| A     | ca   |
| A     | aa   |
| B     | bb   |
| B     | cb   |
| B     | ab   |
+-------+------+

Suggested fix:
dunno
[5 Sep 2003 8:32] Boyd Gerber
I have verified this behavior
[8 Sep 2003 2:14] Alexander Barkov
I can't reproduce this in the latest BK mysql-4.0. 
I started "mysqld --default-character-set=latin1_de".
Then I created the above table and inserted the data.
Running the query several times returned me the same results every time.
[10 Sep 2003 8:01] Boyd Gerber
Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 
http://www.mysql.com/downloads/

This is fixed in the latest release.
[26 Sep 2003 2:41] Michael Brunnbauer
The Bug is NOT fixed - I still have the same behavior with MySQL-4.0.15a.
Please reopen the Bug.
[1 Oct 2003 4:43] Alexander Keremidarski
The result I get with 4.0.15 started with is

+-------+------+
| type2 | name |
+-------+------+
| A     | aa   |
| A     | ba   |
| A     | ca   |
| B     | ab   |
| B     | bb   |
| B     | cb   |
+-------+------+

Which seems correct.
Can you provide some more info? For example is there any index on table you experience bug with?

If yes you may need to recreate index as any change in character sets affects indexes before 4.1
[2 Oct 2003 11:27] Miguel Solorzano
I had tested with the last bk 4.0 tree and got the same result of salle:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.16-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
...........
mysql> select type2,name from test where type1='X' order by type2,name;
+-------+------+
| type2 | name |
+-------+------+
| A     | aa   |
| A     | ba   |
| A     | ca   |
| B     | ab   |
| B     | bb   |
| B     | cb   |
+-------+------+
6 rows in set (0.08 sec)
[16 Oct 2003 2:51] [ name withheld ]
one additional remark:
I have the exact same problem with "4.0.15-standard" (Linux binary
on Debian woody) : Sorting by ENUM-field and then by some numerical
field fails with latin1_de. Using german1 works though.

But maybe this is not only a Charset problem - both the person who
opened this bug and I use ENUM fields to sort on.  Bug #967 , which
is still unresolved, reports problems with that.
[17 Oct 2003 9:06] Brian Aker
Could you try the MySQL binary distribution and see if you get the same results?
[3 Nov 2003 16:01] [ name withheld ]
I want to report the same problem with sorting of ENUM fields in combination with numeric fields.

The ORDER BY enum,numeric results in different sorting orders. 
Sometimes correct, sometimes wrong.
I couldn't get behind, which effects mysql to build a correct or incorrect order.

One thing I found out is, that BINARY order works correctly.

With the given example

"select type2,name from test where type1='X' order by BINARY type2,name;"

always produces the expected result!

This one seems to work too:
"select type2,name from test where type1='X' order by CONCAT(type2),name;"

My configuration:
FreeBSD 5.1-RELEASE-p10
mysql  Ver 12.22 Distrib 4.0.16, for portbld-freebsd5.1 (i386)
character_set: latin1_de
[12 Nov 2003 1:18] Sergei Golubchik
this is an unrelated - and documented - issue.
When you do "ORDER BY enum" data are ordered accoring to *numeric* value of the field.
If you convert it to string first with "CONCAT(enum)" or "BINARY enum" - then, of course, ORDER BY is done by expression result - that is by string value of the enum.
[20 Nov 2003 2:29] [ name withheld ]
[quote]
this is an unrelated - and documented - issue.
When you do "ORDER BY enum" data are ordered accoring to *numeric* value of the
field.
[/quote]

If it is a wanted and documented issue, please explain the following notices:

1. if ORDER BY sorts ENUM-Fields with numerical values, why is there a difference between "latin1_de" and "german1" encoding. "german1" produces the expected results, "latin1_de" not!

2. how come, that the sort results differ from time to time with the same query?
With "latin1_de" you get sometimes right (expected) results and sometimes wrong.
But I can't follow the rules behind that.
[14 Feb 2005 22:54] Bugs System
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".