| Bug #69214 | error in concat with NULL -- Illegal mix of collations | ||
|---|---|---|---|
| Submitted: | 13 May 2013 12:33 | Modified: | 13 May 2013 19:34 |
| Reporter: | Dmitry Samborskiy | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Charsets | Severity: | S2 (Serious) |
| Version: | 5.5.31 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | collation concat | ||
[13 May 2013 13:53]
Valeriy Kravchuk
This is easy to verify:
C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.5.31 MySQL Community Server (GPL)
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> 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 | C:\Program Files\MySQL\MySQL Server 5.5\share\chars
ets\ |
+--------------------------+----------------------------------------------------
-----+
8 rows in set (0.82 sec)
mysql> DROP FUNCTION IF EXISTS `test_func`;
Query OK, 0 rows affected, 1 warning (0.95 sec)
mysql> CREATE FUNCTION `test_func` (x INT) RETURNS INT RETURN x;
Query OK, 0 rows affected (0.16 sec)
mysql> SET @v = test_func(NULL);
Query OK, 0 rows affected (0.15 sec)
mysql> SELECT CONCAT('абвгд', @v);
ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,COERCIBLE) and (l
atin1_swedish_ci,IMPLICIT) for operation 'concat'
mysql> SELECT CONCAT('123', @v);
+-------------------+
| CONCAT('123', @v) |
+-------------------+
| NULL |
+-------------------+
1 row in set (0.00 sec)
mysql> SELECT CONCAT('fff', @v);
+-------------------+
| CONCAT('fff', @v) |
+-------------------+
| NULL |
+-------------------+
1 row in set (0.00 sec)
mysql> SELECT CONCAT('абвгд', test_func(NULL));
+----------------------------------+
| CONCAT('абвгд', test_func(NULL)) |
+----------------------------------+
| NULL |
+----------------------------------+
1 row in set (0.00 sec)
[13 May 2013 13:54]
Valeriy Kravchuk
5.6.11 is also affected.
[13 May 2013 19:34]
MySQL Verification Team
Thank you for the bug report. Not repeatable on 5.0:
[miguel@tikal build]$ 5.0/bin/mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.97-debug Source distribution
Copyright (c) 2000, 2011, 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> set names utf8;
Query OK, 0 rows affected (0.00 sec)
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 | /home/miguel/build/5.0/share/mysql/charsets/ |
+--------------------------+----------------------------------------------+
8 rows in set (0.00 sec)
mysql> DROP FUNCTION IF EXISTS `test_func`;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE FUNCTION `test_func` (x INT) RETURNS INT RETURN x;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @v = test_func(NULL);
Query OK, 0 rows affected (0.00 sec)
mysql> -- The following command emits error
mysql> -- ERROR 1267 (HY000): Illegal mix of collations
mysql> -- (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT)
mysql> -- for operation 'concat'
mysql> SELECT CONCAT('абвгд', @v);
+--------------------------+
| CONCAT('абвгд', @v) |
+--------------------------+
| NULL |
+--------------------------+
1 row in set (0.00 sec)
mysql>
miguel@tikal build]$ 5.5/bin/mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.33-debug Source distribution
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> 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 | /home/miguel/build/5.5/share/charsets/ |
+--------------------------+----------------------------------------+
8 rows in set (0.01 sec)
mysql> DROP FUNCTION IF EXISTS `test_func`;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE FUNCTION `test_func` (x INT) RETURNS INT RETURN x;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @v = test_func(NULL);
Query OK, 0 rows affected (0.01 sec)
mysql> -- The following command emits error
mysql> -- ERROR 1267 (HY000): Illegal mix of collations
mysql> -- (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT)
mysql> -- for operation 'concat'
mysql> SELECT CONCAT('абвгд', @v);
ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'concat'
mysql>
[7 Sep 2013 12:24]
islamcheg qwe
Same with cp1251. Had to downgrade to 5.1 because of this.
Description: (Legal) use of NULL in string concatenation triggers ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) OS version: 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 MySQL Server version and settings: Server version: 5.5.31-log MySQL Community Server (GPL) 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 | +----------------------+-----------------+ How to repeat: DROP FUNCTION IF EXISTS `test_func`; CREATE FUNCTION `test_func` (x INT) RETURNS INT RETURN x; SET @v = test_func(NULL); -- The 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 the following commands are OK: SELECT CONCAT('абвгд', test_func(NULL)); SELECT CONCAT('абвгд', NULL); SET @v = test_func(1); SELECT CONCAT('абвгд', @v);