Bug #58585 Collation bug when using if in where clause on string field
Submitted: 30 Nov 2010 8:20 Modified: 30 Nov 2010 23:07
Reporter: Kristof Van Cleemput Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0,5.1 (All?) OS:Any
Assigned to: CPU Architecture:Any
Tags: bug, collate, IF, Illegal mix of collations, missing autocasting, where

[30 Nov 2010 8:20] Kristof Van Cleemput
Description:
engine MYISAM

missing autocasting for if's in where clauses.

It's a bug because the same thing in the select clause works without casting to char (I know it's something completely different with the "=" but it still feels a lot like a bug)
And I see a lot of these exceptions in threads on the net. Just search for the error and you'll see.

How to repeat:
create table with a char in it but change collation to something else like latin1_general_ci
=> CHARACTER SET latin1 COLLATE latin1_general_ci

As example I take table person and column name

now do something like: SELECT if(p.name = "xxxx",null,p.name) as name
from person p where 1=1 
and  if(p.name = "xxxx",null,p.name) = "X"

this gives:
 Error Code: 1267, SQL State: HY000]  Illegal mix of collations (latin1_general_ci,COERCIBLE) and (latin1_swedish_ci,COERCIBLE) for operation '='

Suggested fix:
There is a workaround (cast to char) but this casting should be done automatically
Workaround:
SELECT if(p.name = "xxxx",null,p.name) as name
from person p where 1=1 
and CAST(if(p.name = "xxx",null,p.name) AS CHAR)  ="X"
[30 Nov 2010 8:23] Kristof Van Cleemput
Better Synopsis
[30 Nov 2010 23:07] Sveta Smirnova
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Read at http://dev.mysql.com/doc/refman/5.1/en/charset-collation-expressions.html and http://dev.mysql.com/doc/refman/5.1/en/string-comparison-functions.html#function_strcmp about collations and string comparison.