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
Triage: Needs Triage: D2 (Serious)

[24 Mar 2011 18:20] M. Irfan Rasheed Malik
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 '<'
[24 Mar 2011 18:29] Valeriy Kravchuk
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 '<'
[24 Mar 2011 19:47] Peter Laursen
I do not understand why at all collations apply to this comparison.  DATES are/should be compared - not strings.

Peter
(not a MySQL person)
[25 Mar 2011 8:37] Alexander Barkov
The fix for bug#58329 fixes this problem as well.

The patch http://lists.mysql.com/commits/133297
is currently under review.
[20 Apr 2011 7:05] M. Irfan Rasheed Malik
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.
[28 Apr 2011 16:08] Paul Dubois
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