Bug #15764 ORDER BY SUBSTRING with negative position sorts incorrectly
Submitted: 14 Dec 2005 23:54 Modified: 18 Jun 2006 21:27
Reporter: Nate Sanden Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.8a-log, 4.1.10 OS:Linux (redhat-linux-gnu)
Assigned to: CPU Architecture:Any

[14 Dec 2005 23:54] Nate Sanden 
Description:
ORDER BY SUBSTRING(somefield, -1)

returns unordered results.

ORDER BY SUBSTRING(somefield, LENGTH(somefield), 1)

seems to work fine however.

How to repeat:
CREATE TABLE `bugreport` (
  `somefield` varchar(20) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- 
-- Dumping data for table `bugreport`
-- 

INSERT INTO `bugreport` VALUES ('php|1');
INSERT INTO `bugreport` VALUES ('perl|2');
INSERT INTO `bugreport` VALUES ('css|4');
INSERT INTO `bugreport` VALUES ('asp|3');

SELECT somefield, SUBSTRING( somefield, -1 ) AS orderfield
FROM `bugreport` 
WHERE 1 
ORDER BY `orderfield` ASC

Suggested fix:
SELECT somefield, SUBSTRING( somefield, LENGTH( somefield ) , 1 ) AS orderfield
FROM `bugreport` 
WHERE 1 
ORDER BY `orderfield` ASC
[15 Dec 2005 12:05] Aleksey Kishkin 
Hi! I was not able to reproduce this bug  on mysql 4.1.16:

mysql> SELECT somefield, SUBSTRING( somefield, -1 ) AS orderfield
    -> FROM `bugreport`
    -> WHERE 1
    -> ORDER BY `orderfield` ASC;
+-----------+------------+
| somefield | orderfield |
+-----------+------------+
| php|1     | 1          |
| perl|2    | 2          |
| asp|3     | 3          |
| css|4     | 4          |
+-----------+------------+
4 rows in set (0.03 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.16    |
+-----------+
1 row in set (0.00 sec)

4.1.10 that you use is pretty old. Could you please update mysql to latest 4.1.16  and check if this bug still present in your environment?
[16 Jan 2006 0:00] 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".
[18 May 2006 21:27] Valeriy Kravchuk 
Please, try to repeat with a newer version, 4.1.19, and inform about the results.
[18 Jun 2006 23:00] 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".