error in concat with NULL -- Illegal mix of collations

[Dmitriy Samborskiy]

1. How to reproduce:

DROP FUNCTION IF EXISTS `test_func`;
CREATE FUNCTION `test_func` () RETURNS INT RETURN NULL;
SET @v = test_func();
-- Following command emits error
-- ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT)
-- for operation 'concat'
SELECT CONCAT('абвгд', @v);
-- NOTE: Cyrillic letters are used in the concatenation

-- Note also that following commands are OK:

SELECT CONCAT('абвгд', test_func());
SELECT CONCAT('абвгд', NULL);

2. Percona server version, OS version:

Server version: 5.5.30-30.2-log Percona Server (GPL), Release rel30.2, Revision 502

# uname -a
Linux 2.6.32-358.6.1.el6.x86_64 #1 SMP Tue Apr 23 19:29:00 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux

3. Server settings:

mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

mysql> show variables like 'collation%';
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+----------------------+-----------------+

[Valerii Kravchuk (valerii-kravchuk)]

Perco0na Server is affected the same way as upstream:

[openxs@centos p5.1]$ mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.5.30-30.2 Percona Server (GPL), Release rel30.2, Revision 500

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Database changed
mysql> DROP FUNCTION IF EXISTS `test_func`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE FUNCTION `test_func` () RETURNS INT RETURN NULL;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @v = test_func();
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CONCAT('абвгд', @v);
ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'concat'

[Valerii Kravchuk (valerii-kravchuk)]

5.1.x is not affected.

There are several older reports for 5.5+ that may have similar root cause, like http://bugs.mysql.com/bug.php?id=60101 ...

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1361