Bug #58781 ORDER BY use only first 10 chars on hoster side (Using filesort)
Submitted: 7 Dec 2010 10:45 Modified: 7 Dec 2010 18:42
Reporter: Sergey Tomashevsky Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Options Severity:S2 (Serious)
Version:5.1.49-rel11.3 OS:Linux (Linux vh36.hoster.by 2.6.18-294.17.1.el5.lve0.7.40 #1 SMP Sat Oct 9 17:58:50 EEST 2010 x86_64)
Assigned to: CPU Architecture:Any
Tags: Extra: Using filesort

[7 Dec 2010 10:45] Sergey Tomashevsky
Description:
On hoster side (MySQL:5.1.49-rel11.3) ORDER BY use only first 10 chars for sorting.
On local side (denwer MySQL:5.0.45-community-nt) sorting works right.

How to repeat:
Sample table:
-- Structure`bak_vm_product`
CREATE TABLE IF NOT EXISTS `bak_vm_product` (
`product_id` int(11) NOT NULL AUTO_INCREMENT,
`product_name` varchar(64) DEFAULT NULL,
PRIMARY KEY (`product_id`),
KEY `idx_product_name` (`product_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='All products are stored here.' AUTO_INCREMENT=12 ;

-- Dump table `bak_vm_product`
INSERT INTO `bak_vm_product` (`product_id`, `product_name`) VALUES
(7, '12345678992'),
(3, '1234567891'),
(6, '12345678991'),
(5, '1234567892'),
(8, '12345678997'),
(9, '12345678994'),
(10, '1234567897'),
(11, '1234567894');

Query:
SELECT *
FROM `bak_vm_product`
ORDER BY `bak_vm_product`.`product_name` ASC

Result:
1234567891
1234567892
1234567894
1234567897
12345678992
12345678991
12345678997
12345678994

Query:
SELECT *
FROM `bak_vm_product`
ORDER BY `bak_vm_product`.`product_name` DESC

Result:
12345678992
12345678991
12345678997
12345678994
1234567897
1234567894
1234567892
1234567891

Both queries display Extra: "Using filesort"
[7 Dec 2010 11:51] Peter Laursen
You have 

`product_name` varchar(64) DEFAULT NULL

.. accordingly `product_name` is sorted as a string and not as a number.  Looks perfect to me.

Peter
(not a MySQL person)
[7 Dec 2010 12:11] Valeriy Kravchuk
Sorry, but I can not repeat this with current version, 5.1.53:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.53-community MySQL Community Server (GPL)

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> CREATE TABLE IF NOT EXISTS `bak_vm_product` (
    -> `product_id` int(11) NOT NULL AUTO_INCREMENT,
    -> `product_name` varchar(64) DEFAULT NULL,
    -> PRIMARY KEY (`product_id`),
    -> KEY `idx_product_name` (`product_name`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='All products are stored her
e.'
    -> AUTO_INCREMENT=12 ;
Query OK, 0 rows affected (0.22 sec)

mysql> INSERT INTO `bak_vm_product` (`product_id`, `product_name`) VALUES
    -> (7, '12345678992'),
    -> (3, '1234567891'),
    -> (6, '12345678991'),
    -> (5, '1234567892'),
    -> (8, '12345678997'),
    -> (9, '12345678994'),
    -> (10, '1234567897'),
    -> (11, '1234567894');
Query OK, 8 rows affected (0.05 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> SELECT *
    -> FROM `bak_vm_product`
    -> ORDER BY `bak_vm_product`.`product_name` ASC
    -> ;
+------------+--------------+
| product_id | product_name |
+------------+--------------+
|          3 | 1234567891   |
|          5 | 1234567892   |
|         11 | 1234567894   |
|         10 | 1234567897   |
|          6 | 12345678991  |
|          7 | 12345678992  |
|          9 | 12345678994  |
|          8 | 12345678997  |
+------------+--------------+
8 rows in set (0.00 sec)

mysql> explain SELECT *
    -> FROM `bak_vm_product`
    -> ORDER BY `bak_vm_product`.`product_name` ASC;
+----+-------------+----------------+------+---------------+------+---------+---
---+------+----------------+
| id | select_type | table          | type | possible_keys | key  | key_len | re
f  | rows | Extra          |
+----+-------------+----------------+------+---------------+------+---------+---
---+------+----------------+
|  1 | SIMPLE      | bak_vm_product | ALL  | NULL          | NULL | NULL    | NU
LL |    8 | Using filesort |
+----+-------------+----------------+------+---------------+------+---------+---
---+------+----------------+
1 row in set (0.02 sec)

mysql> SELECT *
    -> FROM `bak_vm_product`
    -> ORDER BY `bak_vm_product`.`product_name` DESC;
+------------+--------------+
| product_id | product_name |
+------------+--------------+
|          8 | 12345678997  |
|          9 | 12345678994  |
|          7 | 12345678992  |
|          6 | 12345678991  |
|         10 | 1234567897   |
|         11 | 1234567894   |
|          5 | 1234567892   |
|          3 | 1234567891   |
+------------+--------------+
8 rows in set (0.00 sec)

Please, ask your hoster to upgrade...
[7 Dec 2010 13:07] MySQL Verification Team
maybe hoster has max_length_for_sort_data=10 in my.cnf ?
[7 Dec 2010 16:48] Sergey Tomashevsky
Yes, it's hoster's limitation.

I asked, if they could increase till 100.
They answered: Unfortunately, no. Such restrictions have listed in the Appendix № 1 to the public contract.

:(