MySQL Bugs: #16344: Illegal mix of collations for operation 'IN'

Bug #16344	Illegal mix of collations for operation 'IN'
Submitted:	10 Jan 2006 16:04	Modified:	10 Feb 2006 16:23
Reporter:	Hitesh Bechar	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.9	OS:	Solaris (Solaris)
Assigned to:		CPU Architecture:	Any

Description:
Using MySQL JDBC connector v3.1.10 with a character encoding parameter of UTF-8 to connect to MySQL v4.1.9 on Solaris, the following problem is occuring.

A query with '=' and 'or' run against varchar columns returns fine:

SELECT DISTINCT ObjectId
FROM mainDetails
WHERE type =  'router' OR type = 'switch';

But changing it to use IN does not:

SELECT DISTINCT ObjectId
FROM mainDetails
WHERE type IN ( 'router', 'switch' );

Instead it returns the error message "Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation ' IN '".

This is not explicit to just programatically connecting to the MySQL database. This problem can also be reproduced using MySQL Query Browser v1.1.10 on Windows XP to connect to MySQL v4.1.9 on Solaris. I am assuming that this is because the Query browser connects to the MySQL database using the charset encoding of UTF-8 as well.

How to repeat:
With the following settings...

show VARIABLES LIKE "character%";
'character_set_client', 'utf8'
'character_set_connection', 'utf8'
'character_set_database', 'latin1'
'character_set_results', 'utf8'
'character_set_server', 'latin1'
'character_set_system', 'utf8'

show VARIABLES LIKE "collation%";
'collation_connection', 'utf8_general_ci'
'collation_database', 'latin1_swedish_ci'
'collation_server', 'latin1_swedish_ci'

Enter any query using the IN clause against a varchar column with charset set to latin1.

Thank you for a problem report. Please, try to repeat with newer server version, 4.1.16, and inform about the results. Your 4.1.9 is really old. Many bugs were fixed already.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".