Bug #79848 Comparison of datetime string to time fails with ERROR 1267

• Submitted: 5 Jan 2016 12:20
• Modified: 9 Dec 2019 20:31
• Reporter: Su Dylan
• Status: Closed
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.7.8, 5.6.28, 5.7.10
• OS: Any
• CPU Architecture: Any

[5 Jan 2016 12:20] Su Dylan

Description:
Output:
=======
mysql> select '2016-01-05 09:31:38' < time '09:42:15';
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,COERCIBLE) and (latin1_swedish_ci,NUMERIC) for operation '<'

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.8-rc  |
+-----------+
1 row in set (0.00 sec)

Problem:
========
The sql is expected to succeed.

How to repeat:
select '2016-01-05 09:31:38' < time '09:42:15';

Suggested fix:
select '2016-01-05 09:31:38' < time '09:42:15' succeeds.

[6 Jan 2016 11:45] MySQL Verification Team

Hello Su Dylan,

Thank you for the report and test case.
Observed that 5.6.28/5.7.10 affected.

Thanks,
Umesh

[9 Dec 2019 20:31] Roy Lyseng

Posted by developer:
 
Fixed in 5.7.29