MySQL Bugs: #60101: COALESCE with cp1251 tables causes [Err] 1267

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

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.

For table structure I mean

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

Sorry

Yes, I see this problem on my Mysql server to and it is very bad, so bad...

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.

A patch has been committed:

http://lists.mysql.com/commits/131104

This bug was totally resolved back in 2011 ...

Sorry for small delay ....

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.