Bug #60101 COALESCE with cp1251 tables causes [Err] 1267 - Illegal mix of collations
Submitted: 10 Feb 2011 19:12 Modified: 15 May 2013 16:29
Reporter: Anton Kozhenikov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Charsets Severity:S2 (Serious)
Version:5.5.8, 5.5.9 OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Tags: COALESCE, COERCIBLE, Illegal mix of collations, latin1_swedish_ci, regression
Triage: Needs Triage: D2 (Serious)

[10 Feb 2011 19:12] Anton Kozhenikov
Description:
Simple query with COALESCE on cp1251 charset table will produce 1267 error. 

How to repeat:
Create a simple table in database

CREATE TABLE `test` (
  `test` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;

Then make a query

SELECT
	COALESCE(
		IF(pr.test1 = 1, 1, NULL),
		test2
	)

FROM test pr

You will receive
[Err] 1267 - Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (cp1251_general_ci,IMPLICIT) for operation 'coalesce'

OS makes no sense, workaround with 'character-set*' variables either.
[10 Feb 2011 19:14] Anton Kozhenikov
For table structure I mean

CREATE TABLE `test` (
  `test1` int(11) DEFAULT NULL,
  `test2` varchar(255) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;

Sorry
[10 Feb 2011 20:41] Aleksandr Semyonov
Yes, I see this problem on my Mysql server to and it is very bad, so bad...
[11 Feb 2011 7:19] Valeriy Kravchuk
Verified on Windows XP:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3312 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.5.8 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 `test` (
    ->   `test1` int(11) DEFAULT NULL,
    ->   `test2` varchar(255) DEFAULT NULL
    -> ) ENGINE=MyISAM DEFAULT CHARSET=cp1251;
Query OK, 0 rows affected (0.08 sec)

mysql> SELECT
    ->  COALESCE(
    ->          IF(pr.test1 = 1, 1, NULL),
    ->          test2
    ->  )
    ->
    -> FROM test pr
    ->
    -> ;
ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and
(cp1251_general_ci,IMPLICIT) for operation 'coalesce'
mysql> show variables like 'char%';
+--------------------------+----------------------------------------------------
-----+
| 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.03 sec)

mysql> set names cp1251;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    ->  COALESCE(
    ->          IF(pr.test1 = 1, 1, NULL),
    ->          test2
    ->  )
    ->
    -> FROM test pr
    ->
    -> ;
ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and
(cp1251_general_ci,IMPLICIT) for operation 'coalesce'
mysql> exit
Bye

Note that it works with 5.1.54:

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 40
Server version: 5.1.54-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 `test` (
    ->   `test1` int(11) DEFAULT NULL,
    ->   `test2` varchar(255) DEFAULT NULL
    -> ) ENGINE=MyISAM DEFAULT CHARSET=cp1251;
Query OK, 0 rows affected (0.53 sec)

mysql> SELECT
    ->  COALESCE(
    ->          IF(pr.test1 = 1, 1, NULL),
    ->          test2
    ->  )
    ->
    -> FROM test pr
    ->
    -> ;
Empty set (0.13 sec)

So, we have a regression bug here.
[11 Feb 2011 9:02] Alexander Barkov
A patch has been committed:

http://lists.mysql.com/commits/131104
[15 May 2013 16:29] Sinisa Milivojevic
This bug was totally resolved back in 2011 ...

Sorry for small delay ....
[3 Jun 2013 15:17] Paul Dubois
Noted in 5.5.11, 5.6.2 changelogs.

Queries that used COALESCE() with cp1251 strings could result in an
"illegal mix of collations" error.