Bug #31006 problem of concat() and concat_ws() with german "Umlaute" and int fields

• Submitted: 13 Sep 2007 20:08
• Modified: 15 Oct 2007 8:40
• Reporter: Helmut Zeilinger
• Status: Duplicate
• Category: MySQL Server: Data Types
• Severity: S3 (Non-critical)
• Version: 5.0.44 Gentoo Linux mysql-5.0.44-r1
• OS: Linux (2.6.21-gentoo-r4)
• Assigned to: Assigned Account
• CPU Architecture: Any
• Tags: character set, concatenation, Latin1, type conversion, Unicode

[13 Sep 2007 20:08] Helmut Zeilinger

Description:
The query:
"select pk, name, concat(pk,name) from test_table;"
results in
+------+------+-----------------+
| pk   | name | concat(pk,name) |
+------+------+-----------------+
|    1 | ä    | 1À             |
+------+------+-----------------+

expected was "1ä" for the concatenation.
However
"select pk, name, concat(cast(pk as char),name) from test_table;"
results in
+------+------+-------------------------------+
| pk   | name | concat(cast(pk as char),name) |
+------+------+-------------------------------+
|    1 | ä    | 1ä                            |
+------+------+-------------------------------+

which is the correct (expected) result.

The query
"select concat(1,'ä');"
also works correct.

Table definition:

CREATE TABLE `test_table` (
  `pk` int(11) default NULL,
  `name` varchar(100) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |

Characterset settings:
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |

How to repeat:
see above

Suggested fix:
-

[14 Sep 2007 14:17] MySQL Verification Team

Thank you for the bug report.

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

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `test_table` (
    ->   `pk` int(11) default NULL,
    ->   `name` varchar(100) default NULL
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into test_table values (1,'ä');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_table values (1,'a');
Query OK, 1 row affected (0.00 sec)

mysql> select pk, name, concat(pk,name) from test_table;
+------+------+-----------------+
| pk   | name | concat(pk,name) |
+------+------+-----------------+
|    1 | ä    | 1ä             | 
|    1 | a    | 1a              | 
+------+------+-----------------+
2 rows in set (0.00 sec)

mysql>

[15 Oct 2007 8:40] Alexander Barkov

This problem is a duplicate of http://bugs.mysql.com/bug.php?id=8204

As a workaround you need to use explicit conversion from number
to char():

mysql> select pk, name, concat(pk,name), concat((cast(pk as char)),name) from test_table;
+------+------+-----------------+---------------------------------+
| pk   | name | concat(pk,name) | concat((cast(pk as char)),name) |
+------+------+-----------------+---------------------------------+
|    1 | ä    | 1ä             | 1ä                              |
|    1 | a    | 1a              | 1a                              |
+------+------+-----------------+---------------------------------+
2 rows in set (0.00 sec)

Marking this bug as duplicate to 8204.