Bug #18302 Sorting CONCAT(char,int) does not sort correctly

• Submitted: 17 Mar 2006 11:08
• Modified: 12 May 2006 8:29
• Reporter: Kai Ruhnau
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.19
• OS: Linux (Gentoo Linux)
• CPU Architecture: Any

[17 Mar 2006 11:08] Kai Ruhnau

Description:
When concatenating a string with an integer, sorting breaks.

mysql> SET NAMES utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT string_value  FROM sorting_test ORDER BY 1;
+--------------+
| string_value |
+--------------+
| aa           |
| äz          |
| ba           |
+--------------+
3 rows in set (0.00 sec)

mysql> SELECT CONCAT(string_value,int_value)  FROM sorting_test ORDER BY 1;
+--------------------------------+
| CONCAT(string_value,int_value) |
+--------------------------------+
| aa1                            |
| ba2                            |
| äz3                           |
+--------------------------------+
3 rows in set (0.00 sec)

When the integer is explicitly castet into a string, sorting works again.

mysql> SELECT CONCAT(string_value,CONVERT(int_value,CHAR(10)))  FROM sorting_test ORDER BY 1;
+--------------------------------------------------+
| CONCAT(string_value,CONVERT(int_value,CHAR(10))) |
+--------------------------------------------------+
| aa1                                              |
| äz3                                             |
| ba2                                              |
+--------------------------------------------------+
3 rows in set (0.00 sec)

How to repeat:
Please use the attached file.

[17 Mar 2006 11:08] Kai Ruhnau

Testdata

Attachment: sorting_test.sql (application/octet-stream, text), 330 bytes.

[17 Mar 2006 11:11] Kai Ruhnau

Switching both console and MySQL to latin1:

mysql> SELECT CONCAT(string_value)  FROM sorting_test ORDER BY 1;
+----------------------+
| CONCAT(string_value) |
+----------------------+
| aa                   |
| äz                   |
| ba                   |
+----------------------+
3 rows in set (0.00 sec)

mysql> SELECT CONCAT(string_value,int_value)  FROM sorting_test ORDER BY 1;
+--------------------------------+
| CONCAT(string_value,int_value) |
+--------------------------------+
| aa1                            |
| ba2                            |
| Àz3                           |
+--------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT CONCAT(string_value,CONVERT(int_value,CHAR(10)))  FROM sorting_test ORDER BY 1;
+--------------------------------------------------+
| CONCAT(string_value,CONVERT(int_value,CHAR(10))) |
+--------------------------------------------------+
| aa1                                              |
| äz3                                              |
| ba2                                              |
+--------------------------------------------------+
3 rows in set (0.00 sec)

[17 Mar 2006 14:43] Kai Ruhnau

My last comment can be misinterpreted:

I did not change the data.
I changed xterm to expect latin1 characters and issued a 

SET NAMES latin1

in the MySQL console.

[12 May 2006 8:29] Valeriy Kravchuk

Thank you for a problem report. Sorry for a long delay with it. It is not a bug. According to the manual (http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html):

" If you use a number in string context, the number automatically is converted to a BINARY string.

mysql> SELECT CONCAT('hello you ',2);
        -> 'hello you 2'
"

So, the results you got are explained by the fact that it is BINARY string. When you CONVERT to char(10), it is converted to the default craracter set, and your default collation is used.