MySQL Bugs: #60494: Order By produces wrong order

Bug #60494	Order By produces wrong order
Submitted:	16 Mar 2011 17:26	Modified:	17 Mar 2011 9:40
Reporter:	Ian Hobson	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.1.36-community, 5.1.57	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The sql statement used is 

SELECT styleNo , right( concat( '######', trim( styleNo ) ) , 6 ) AS keyn 
FROM `styles` ORDER BY keyn

StyleNo is varchar(6) and contains various, mostly numeric strings - "123", "4567" and smilar. Some have a leadig "A", "B" or "Z". 

The orderby does not produce the correct order unless the number of hashes is extended to 8. 

I am using ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci on MySQL 5.1.36-community under 64 bit Windows 7. 

How to repeat:
Create a database with a varchar(6) field called styleNo. 

Populate in non-ascending order.  If you need a dump of the file, just ask - it is only 75 records. 

Run the select statement above. The results will not be in the correct order unless the number of #s is at least 8 - even though the keyn values would be correct with '###' or longer. 

Suggested fix:
I've avoided the problem by using 

ORDER BY LENGTH(TRIM(`styleNo`)),TRIM(`styleNo`)

Please, check if the problem is still repeatable with a newer version, 5.1.56. If it is, please, send a dump of data for the table.

I use WAMP and can only update to 5.1.41. which has the same behaviour.

Sorry, I do not see anything wrong on current version:

macbook-pro:5.1 openxs$ bin/mysql -uroot test < ~/Downloads/styles.sql 
macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.1.57-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT styleNo , right( concat( '######', trim( styleNo ) ) , 6 ) AS keyn 
    -> FROM `styles` ORDER BY keyn
    -> ;
+---------+--------+
| styleNo | keyn   |
+---------+--------+
| 101     | ###101 |
| 180     | ###180 |
| 105     | ###105 |
| 104     | ###104 |
| 102     | ###102 |
| 123     | ###123 |
| 124     | ###124 |
| 432     | ###432 |
| 1004    | ##1004 |
| 1001    | ##1001 |
| 1002    | ##1002 |
| 1003    | ##1003 |
| 1006    | ##1006 |
| 1234    | ##1234 |
| 1231    | ##1231 |
| 1255    | ##1255 |
| 1288    | ##1288 |
| 2005    | ##2005 |
| 2006    | ##2006 |
| 2007    | ##2007 |
| 2008    | ##2008 |
| 2002    | ##2002 |
| 2009    | ##2009 |
| 2312    | ##2312 |
| 2323    | ##2323 |
| 2343    | ##2343 |
| 3213    | ##3213 |
| 3212    | ##3212 |
| 3421    | ##3421 |
| 4321    | ##4321 |
| 4324    | ##4324 |
| 5432    | ##5432 |
| 6023    | ##6023 |
| 6022    | ##6022 |
| 6025    | ##6025 |
| 6026    | ##6026 |
| 6028    | ##6028 |
| 6024    | ##6024 |
| 6027    | ##6027 |
| 6021    | ##6021 |
| 7000    | ##7000 |
| 12344   | #12344 |
| 12345   | #12345 |
| 23332   | #23332 |
| 32423   | #32423 |
| 33332   | #33332 |
| 60001   | #60001 |
| 60002   | #60002 |
|  60003  | #60003 |
|  60004  | #60004 |
| 60005   | #60005 |
|  60006  | #60006 |
| 123422  | 123422 |
| 123423  | 123423 |
| 123432  | 123432 |
| 123455  | 123455 |
| 123456  | 123456 |
| 123457  | 123457 |
| 123458  | 123458 |
| 123459  | 123459 |
| 123460  | 123460 |
| 123461  | 123461 |
| 123462  | 123462 |
| 123463  | 123463 |
| 123464  | 123464 |
| 123465  | 123465 |
| 123466  | 123466 |
| 123467  | 123467 |
| 32123A  | 32123A |
+---------+--------+
69 rows in set (0.05 sec)

Am I missing something?

-> FROM `styles` ORDER BY keyn
    -> ;

Because of the "order by keyn" then the order should be ###101, ###102, ###104, ###105, ###123, ###124, ###432 etc.. 

As you can see, it is different. 

If you change the number of hashes passed into the concat statement, the order changes, so  "Order by keyn" is not even consistent.

It looks as if it is sorting only by the first 4 characters if the string of hashes is 6 long, by the first 5 characters if there are 7 hashes, and by all 6 if there are 8.  Might be an off-by-two error setting up the sort key.

Thank you for the clarification. Now I got it. Looks like a bug.