MySQL Bugs: #73552: ('string' = CAST(0 as TIME)) produces bogus error "Illegal mix of collations"

Bug #73552	('string' = CAST(0 as TIME)) produces bogus error "Illegal mix of collations"
Submitted:	12 Aug 2014 18:56	Modified:	18 Aug 2014 19:47
Reporter:	Arthur O'Dwyer	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.5.8, 5.5.31, 5.5.34,5.6.20	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
CAST(0 AS TIME) is in some contexts incorrectly treated as if it had collection (latin1_swedish_ci,NUMERIC) instead of being of type TIME.

The offending contexts include:
 (*) on the right-hand side of a comparison operator such as =, <, or LIKE

Examples of contexts where CAST(0 AS TIME) is treated *CORRECTLY* include:
 (*) on the left-hand side of a comparison operator
 (*) as an argument to CONCAT
 (*) as an argument to COERCIBILITY or COLLATION or CHARSET

How to repeat:
mysql> select CAST(0 AS TIME) = '00:00:00';
+------------------------------+
| CAST(0 AS TIME) = '00:00:00' |
+------------------------------+
|                            1 |
+------------------------------+

mysql> select '00:00:00' = CAST(0 AS TIME);
ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,NUMERIC) for operation '='

The first result is expected.
The second result (the error message) is unexpected.
We would naturally expect that '=' should be symmetric with respect to the order of its two operands.

Suggested fix:
I'm not sure, and I haven't tested this, but I see that in Item_equal::Item_equal(Item*, Item_field*) you have this line:

  compare_as_dates= f->is_datetime();

but as of MySQL 5.5.37, Item::is_datetime() still does not consider "TIME" to be a datetime type. This probably leads to inconsistent behavior somehow --- since I see that CAST(0 AS <type>) for any other datetime type does not lead to the bad behavior.

[15:18:43][pochita:]~ miguel$ mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 37
Server version: 5.6.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, 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> select CAST(0 AS TIME) = '00:00:00';
+------------------------------+
| CAST(0 AS TIME) = '00:00:00' |
+------------------------------+
|                            1 |
+------------------------------+
1 row in set (0.03 sec)

mysql> select '00:00:00' = CAST(0 AS TIME);
ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,NUMERIC) for operation '='
mysql>

Thank you for the bug report.

The same symptom occurs when LAST_DAY(...) appears on the right-hand side of a comparison operator.

mysql> select last_day('2014-02-01') = 'x';
+------------------------------+
| last_day('2014-02-01') = 'x' |
+------------------------------+
|                            0 |
+------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------+
| Level   | Code | Message                             |
+---------+------+-------------------------------------+
| Warning | 1292 | Truncated incorrect date value: 'x' |
+---------+------+-------------------------------------+

mysql> select 'x' = last_day('2014-02-01');
ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,NUMERIC) for operation '='

The same symptom for a harder-to-rationalize case: mixing DATETIME with the result of DATE_ADD/DATE_SUB also gives the same message.

select DATE_ADD(1, INTERVAL 1 DAY) = CAST(1 as DATETIME);  -- Illegal mix of collations
select DATE_ADD(1, INTERVAL 1 DAY) = CAST(1 as DATE);  -- Illegal mix of collations
select DATE_ADD(1, INTERVAL 1 DAY) = CAST(1 as TIME);  -- Illegal mix of collations

Again, exchanging the operands of '=' produces the expected output (no error).

The same bug also reproduces with NULLIF of a date/time type.

drop database if exists d;
create database d;
create table d.t (val_date DATE, val_int INT);
select NULLIF(val_date, 1) > ADDDATE(val_int, 1) from d.t;