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
Triage: Needs Triage: D2 (Serious)

[16 Mar 2011 17:26] Ian Hobson
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`)
[16 Mar 2011 17:34] Valeriy Kravchuk
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.
[16 Mar 2011 21:02] Ian Hobson
I use WAMP and can only update to 5.1.41. which has the same behaviour.
[17 Mar 2011 4:09] Valeriy Kravchuk
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?
[17 Mar 2011 9:21] Ian Hobson
-> 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.
[17 Mar 2011 9:30] Ian Hobson
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.
[17 Mar 2011 9:40] Valeriy Kravchuk
Thank you for the clarification. Now I got it. Looks like a bug.