Alright, this is annoying, came across a problem with an old query today to do with differing charsets. The issue was this:
ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,NUMERIC) for operation '<='
which was being generated from something as simple as this:
SELECT '23:59:59' <= CURRENT_TIME();
Has anyone else had this strange issue? From what I have read, it's only apparent in MySQL 5.5+ and not in the previous versions. Looking around MySQL's documentation site, they did change some code relating to date, but reverted it in 5.5.21 (which I am running). Can somebody run this test case on 5.1- and see if the issue is still present?
Below is a reproducible test case for this problem:
SET NAMES 'latin1';
SELECT '23:59:59' <= CURRENT_TIME();
SET NAMES 'utf8';
SELECT '23:59:59' <= CURRENT_TIME();
.. and here are the results from my dev machine:
mysql> SET NAMES 'latin1';
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'version';
+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| version | 5.5.21-log |
+---------------+------------+ 1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'collation_connection';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
+----------------------+-------------------+
1 row in set (0.00 sec)
mysql> SELECT '23:59:59' <= CURRENT_TIME();
+------------------------------+
| '23:59:59' <= CURRENT_TIME() |
+------------------------------+
| 0 |
+------------------------------+
1 row in set (0.00 sec)
mysql> SET NAMES 'utf8';
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'collation_connection';
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
+----------------------+-----------------+
1 row in set (0.00 sec)
mysql> SELECT '23:59:59' <= CURRENT_TIME();
ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,NUMERIC) for operation '<='