MySQL Bugs: #11016: table fields with no collation corrupt operations with collated fields

Bug #11016	table fields with no collation corrupt operations with collated fields
Submitted:	1 Jun 2005 12:41	Modified:	21 Aug 2008 5:15
Reporter:	jean-pierre michaud	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S4 (Feature request)
Version:	MySQL 4.1.10-standard	OS:	Linux (Linux)
Assigned to:	Alexander Barkov	CPU Architecture:	Any

Description:
bug found in this query:
SELECT forumid
FROM fora_forum
WHERE FIND_IN_SET('16', parentlist)

mysql error: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_unicode_ci,IMPLICIT) for operation 'find_in_set'

mysql error number: 1267

forumid is INT(10) no collation, parentlist is VARCHAR(250), with collation at utf8 ...

DEBUG:
ALTER TABLE `fora_forum` CHANGE `parentlist` `parentlist` VARCHAR( 250 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL 

logically, fields with no collate would be compatible with fields that have collates, not the opposite... isn't it ?!

How to repeat:
simply hit a field with no collate to a field with a different collate than latin1/or the one standard in your installation...

Suggested fix:
simply make the unset fields compatible with fields with alternate collates, instead of forcing them to be uncompatible.

select user from mysql.db where find_in_set(user, "blah");

This statement displays the error.  The user would like to see one collation to take precedence 
as we do for comparisons:

select user from mysql.db where user = "blah";

Thank you for your bug report. This issue was fixed in 5.0 a few months ago.
Please upgeade.

There are no problems with mysql-5.0.51

mysql> create table t1 (forumid int(10), parentlist varchar(250) character set utf8 collate utf8_unicode_ci);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t1 values (1, '11,12,13,14,15,16,17,18,19,20');
Query OK, 1 row affected (0.00 sec)

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

mysql> select forumid from t1 where find_in_set('16', parentlist);
+---------+
| forumid |
+---------+
|       1 | 
+---------+
1 row in set (0.03 sec)