MySQL Bugs: #13615: "Illegal mix of collations" bug still not fixed.

Bug #13615	"Illegal mix of collations" bug still not fixed.
Submitted:	29 Sep 2005 17:31	Modified:	12 Oct 2005 14:25
Reporter:	Ernie Miller	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S2 (Serious)
Version:	1.1.15	OS:	Windows (Windows XP Professional)
Assigned to:	Mike Lischke	CPU Architecture:	Any

Description:
I noted some old bugs in the DB that reported this problem but they no longer show up as open. This issue is not fixed.

Attempting to select FIND_IN_SET(some_column, 'string1, string2') where the DB is configured to use latin1_swedish_ci and all tables reflect it as appropriate will result in:

Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'find_in_set'

The client is setting its charset to utf8 instead of observing the DB default latin1 encoding, resulting in the comparison against the user-supplied string on the right-hand side being invalid.  The same query works properly in the command-line client and DBD-mysql 3.x under Perl.

How to repeat:
Configure a table to use latin1 encoding.

 select FIND_IN_SET(some_column, 'string1, string2')

Suggested fix:
I'd be happy if my attempts to override the client's charset using the advanced tab of the connection window would be stored and observed, but it doesn't seem to be.

Please note that one could see it as a gotcha, but it is not a real bug. You should keep in mind that our tools strictly work with Unicode where possible, particular for the connection/server charset. So giving a string constant will implicitely set it as Unicode string (utf-8 encoded). If you then give the expression a column with a different charset then you get the mentioned error.

What you can do is either to set the server charset to your desired value:

1) start a transaction in QB
2) set names latin1
3) select FIND_IN_SET(ansi_column, 'string1,string2')
...

or you consequently use Unicode, which is the better alternative, as you can avoid all the charset trouble in the future (for the price to have slightly higher storage requirements).

Mike

I forgot to mention, you have actually a third option. Set the strings charset explicitely to latin1:

select FIND_IN_SET(latin1_column, _latin1'string1,string2')

Note the leading underscore for the charset introducer.

Mike