MySQL Bugs: #60625: Illegal mix of collations

Bug #60625	Illegal mix of collations
Submitted:	24 Mar 2011 18:20	Modified:	28 Apr 2011 16:08
Reporter:	M. Irfan Rasheed Malik	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S3 (Non-critical)
Version:	5.5.10, 5.5.11	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
I am getting error while executing procedure 

[Err] 1267 - Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,NUMERIC) for operation '<'

I have tested our code and it is having this problem with MySQL 5.5.9 and MySQL 5.5.10 both.
I have also tested this code on MySQL 5.1.47 and MySQL 5.1.54, code is working fine on MySQL 5.1.
 

How to repeat:
here is sample code which is causing problem in MySQL 5.5

DROP PROCEDURE IF EXISTS `Tempscript`;
CREATE PROCEDURE `Tempscript`()
BEGIN
	DECLARE v_LastPaymentDate DATETIME DEFAULT NULL;
	SELECT v_LastPaymentDate < NOW();
END;
call Tempscript;

return an exception on while executing call statement:

[Err] 1267 - Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,NUMERIC) for operation '<'

Verified with current mysql-5.5 from bzr on Mac OS X:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.11-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> delimiter //
mysql> CREATE PROCEDURE `Tempscript`()
    -> BEGIN
    -> DECLARE v_LastPaymentDate DATETIME DEFAULT NULL;
    -> SELECT v_LastPaymentDate < NOW();
    -> END;
    -> //
Query OK, 0 rows affected (0.04 sec)

mysql> call Tempscript//
ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,NUMERIC) for operation '<'

I do not understand why at all collations apply to this comparison.  DATES are/should be compared - not strings.

Peter
(not a MySQL person)

The fix for bug#58329 fixes this problem as well.

The patch http://lists.mysql.com/commits/133297
is currently under review.

When this bug will be fixed and when patch for this bug will be pushed to production?
We are unable to move ahead without resolving this issue.
It is really very nice to have update from you guys on this, so that I can update my seniors on this.

Noted in 5.5.12, 5.6.3 changelogs.

Comparison of a DATETIME stored program variable and NOW() led to an
"Illegal mix of collations error" when character_set_connection was
set to utf8. 

CHANGESET - http://lists.mysql.com/commits/135079