MySQL Bugs: #66843: IFNULL() giving "Illegal mix of collations" error while IF() does not.

Bug #66843	IFNULL() giving "Illegal mix of collations" error while IF() does not.
Submitted:	17 Sep 2012 11:48	Modified:	20 Sep 2012 18:57
Reporter:	Ulrik Johansson	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.5.27	OS:	Windows (windows 7 x64)
Assigned to:		CPU Architecture:	Any

Description:
When switching from mysql 5.1.57 to 5.5.27 we noticed a strange error from a statement involving an IFNULL() function.

Here is a simple query that still produces the error in 5.5.27, but works fine in 5.1.57:
SELECT * FROM test WHERE '2012-06-30' < IFNULL(date1, date2)

The error returned is:
[Err] 1267 - Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,NUMERIC) for operation '<'

If I switch the IFNULL()-statement for an IF()-statement like the following, everything works again:
SELECT * FROM test WHERE '2012-06-30' < IF(date1 <> NULL, date1, date2)

I've tried running these statements both through PHP/PDO, as well as through Navicat and the Mysql command line client, and the result is the same.

How to repeat:
Create a new database with charset utf-8 and collaction utf8_swedish_ci
Create a table with 2 fields of type DATE.

Run a select statement with a date string and an ifnull function (using now() doesn't reproduce the error)
SELECT * FROM test WHERE '2012-06-30' < IFNULL(date1, date2)

Suggested fix:
At the moment we can circumvent the problem by using IF() instead of IFNULL()

'Sideline comment' only: 

I don't think this " ..IF(date1 <> NULL, date1, date2 .." in the workaround will work as expected. In my understanding it should be " .. IF(date1 IS NOT NULL, date1, date2 .. "

Peter 
(not a MySQL/Oracle person)

Thanks for the tip Peter, I've changed my workaround now. IF() still seems to work fine, while IFNULL() does not.

Thank you for the report.

Please send us output of SHOW CREATE TABLE test and SHOW VARIABLES LIKE '%char%'; SHOW VARIABLES LIKE '%col%';

Debug information as requested

Attachment: debug info.txt (text/plain), 751 bytes.

Thank you for the feedback.

This is not a bug: you have collation_connection set to utf8_general_ci while table uses utf_swedish_ci. DATE columns use collations in 5.5.