MySQL Bugs: #1203: wrong sort order in select with character-set latin1

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

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

I have verified this behavior

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.

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.

The Bug is NOT fixed - I still have the same behavior with MySQL-4.0.15a.
Please reopen the Bug.

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

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)

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.

Could you try the MySQL binary distribution and see if you get the same results?

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

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.

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

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