Bug #13751 find_in_set: Illegal mix of collations
Submitted: 4 Oct 2005 17:52 Modified: 20 Oct 2005 20:32
Reporter: Olaf van der Spek (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:* OS:Any (All)
Assigned to: Alexander Barkov CPU Architecture:Any

[4 Oct 2005 17:52] Olaf van der Spek
Description:
I'm not sure, but http://dev.mysql.com/doc/mysql/en/charset-collation-charset.html appears to say this issue has been solved >= 4.1.8.

mysql> select * from forum where find_in_set('-1', childlist);
ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and
(utf8_general_ci,IMPLICIT) for operation 'find_in_set'
mysql>

How to repeat:
set names 'latin1';
drop table if exists forum;
create table forum (childlist varchar(255)) default charset=utf8;
select * from forum where find_in_set('-1', childlist);
[5 Oct 2005 5:05] Jorge del Conde
Thanks for your bug report.  I was able to reproduce this using 5.0.13:

mysql> set names 'latin1';
Query OK, 0 rows affected (0.01 sec)

mysql> drop table if exists forum;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> create table forum (childlist varchar(255)) default charset=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from forum where find_in_set('-1', childlist);
ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and
(utf8_general_ci,IMPLICIT) for operation 'find_in_set'
[5 Oct 2005 5:05] Jorge del Conde
I was also able to reproduce this problem under Linux
[5 Oct 2005 6:02] Olaf van der Spek
4.1.14-nt is affected too.
[7 Oct 2005 4:57] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30797
[14 Oct 2005 7:02] Alexander Barkov
Fixed in 4.1.16.
[14 Oct 2005 8:40] Alexander Barkov
Merged into 5.0.16.
[20 Oct 2005 20:32] Paul DuBois
Noted in 4.1.16, 5.0.15 changelogs.
[16 Nov 2005 0:31] Rex Lorenzo
Is there a work around for this problem? Even though the bug says it is fixed in MySQL 4.1.16, 5.0.15, neither version is out at the moment.
[13 Mar 2006 18:27] Malcolm Holst
Working with 5.0.18 (running on Windows and Linux), still having the same problem!