Bug #85224 Illegal mix of collations for time/varchar

• Submitted: 28 Feb 2017 10:59
• Modified: 20 Mar 2017 16:14
• Reporter: Tor Didriksen
• Status: Closed
• Category: MySQL Server: Charsets
• Severity: S3 (Non-critical)
• Version: 8.0.1
• OS: Any
• CPU Architecture: Any

[28 Feb 2017 10:59] Tor Didriksen

Description:
Snippet from time_truncate_fractional.test

CREATE TABLE t1 (a TIME(6));
INSERT INTO t1 VALUES ('11:22:33');
INSERT INTO t1 VALUES ('11:22:33.123');
INSERT INTO t1 VALUES ('-11:22:33');
INSERT INTO t1 VALUES ('-11:22:33.1234567');
CREATE TABLE t2 (b VARCHAR(20));
INSERT INTO t2 VALUES ('11:22:33.123');
INSERT INTO t2 VALUES ('-11:22:33.123456');
SELECT * FROM t1, t2 WHERE a=b;

How to repeat:
./mtr --mem time_truncate_fractional --charset-for-testdb=utf8mb4 --defaults-file=include/utf8mb4_my.cnf

mysql> select * from t1, t2 where a=b;
ERROR 1271 (HY000): Illegal mix of collations for operation '='

mysql> select * from t1, t2 where b=a;
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (latin1_swedish_ci,NUMERIC) for operation '='

mysql> select * from t1, t2 where convert(a using latin1)=b;
Empty set (0,00 sec)

Suggested fix:
a smarter Arg_comparator::set_cmp_func ??

[20 Mar 2017 16:14] Paul DuBois

Posted by developer:
 
Noted in 8.0.2 changelog.

When the character set of one string comparison operand was a
superset of the character set of the other operand, some comparisons
were disallowed that should be permitted by converting the operand
with the "smaller" character set to the "larger" character set.
utf8mb4 and utf32 are considered to be a superset of any other
encoding.