Description:
The following SQL sometimes failes with ER_CANT_AGGREGATE_2COLLATIONS for the SELECT statement:
CREATE TABLE t1 (c1 CHAR(10) CHARSET latin1);
INSERT INTO t1 VALUES ('Muller'), ('Müller');
SELECT * FROM t1 WHERE c1 = _utf8 '<literal>';
The error message suggests that using different collations on both sides of '=' is illegal here. In fact it isn't. But sometimes the *values* may make that operation illegal. We should have better error messages here.
Example 1: if the UTF8 string contains a character that is not available in latin1, then something like ER_AUTO_CONVERT would be more appropriate.
Example 2: if the UTF8 string is malformed (illegal byte sequence; i.e. 0xD0 0xC9), then ER_INVALID_CHARACTER_STRING would be more appropriate.
How to repeat:
Compile the attached test case (C program) and run it:
$gcc -o test test.c `mysql_config --cflags --libs`
$./test
OK: mysql= mysql_init(NULL)
OK: mysql_options(mysql, MYSQL_SET_CHARSET_NAME, "latin1")
OK: mysql_real_connect(mysql, "localhost", "root", NULL, "test", 0, NULL, 0)
OK: mysql_query(mysql, DROP)
OK: mysql_query(mysql, CREATE)
OK: mysql_query(mysql, INSERT)
OK: mysql_query(mysql, SELECT1)
OK: res= mysql_store_result(mysql)
OK: mysql_num_rows(res) == 1
OK: mysql= mysql_init(NULL)
OK: mysql_options(mysql, MYSQL_SET_CHARSET_NAME, "utf8")
OK: mysql_real_connect(mysql, "localhost", "root", NULL, "test", 0, NULL, 0)
OK: mysql_query(mysql, SELECT2)
OK: res= mysql_store_result(mysql)
OK: mysql_num_rows(res) == 1
OK: mysql_query(mysql, SELECT3)
Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COER
CIBLE) for operation '='
OK: mysql_query(mysql, SELECT4)
Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COER
CIBLE) for operation '='
OK: mysql_query(mysql, DROP)
Description: The following SQL sometimes failes with ER_CANT_AGGREGATE_2COLLATIONS for the SELECT statement: CREATE TABLE t1 (c1 CHAR(10) CHARSET latin1); INSERT INTO t1 VALUES ('Muller'), ('Müller'); SELECT * FROM t1 WHERE c1 = _utf8 '<literal>'; The error message suggests that using different collations on both sides of '=' is illegal here. In fact it isn't. But sometimes the *values* may make that operation illegal. We should have better error messages here. Example 1: if the UTF8 string contains a character that is not available in latin1, then something like ER_AUTO_CONVERT would be more appropriate. Example 2: if the UTF8 string is malformed (illegal byte sequence; i.e. 0xD0 0xC9), then ER_INVALID_CHARACTER_STRING would be more appropriate. How to repeat: Compile the attached test case (C program) and run it: $gcc -o test test.c `mysql_config --cflags --libs` $./test OK: mysql= mysql_init(NULL) OK: mysql_options(mysql, MYSQL_SET_CHARSET_NAME, "latin1") OK: mysql_real_connect(mysql, "localhost", "root", NULL, "test", 0, NULL, 0) OK: mysql_query(mysql, DROP) OK: mysql_query(mysql, CREATE) OK: mysql_query(mysql, INSERT) OK: mysql_query(mysql, SELECT1) OK: res= mysql_store_result(mysql) OK: mysql_num_rows(res) == 1 OK: mysql= mysql_init(NULL) OK: mysql_options(mysql, MYSQL_SET_CHARSET_NAME, "utf8") OK: mysql_real_connect(mysql, "localhost", "root", NULL, "test", 0, NULL, 0) OK: mysql_query(mysql, SELECT2) OK: res= mysql_store_result(mysql) OK: mysql_num_rows(res) == 1 OK: mysql_query(mysql, SELECT3) Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COER CIBLE) for operation '=' OK: mysql_query(mysql, SELECT4) Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COER CIBLE) for operation '=' OK: mysql_query(mysql, DROP)