Description:
Despite of different coercibilities of the operands an illegal mix of collations error occures.
select coercibility(CONCAT_WS(' ', convert(x,char) ,y)) from test;
result is: 1
select coercibility('%h%') from test;
result is: 4
select * from test where CONCAT_WS(' ', convert(x,char) ,y) LIKE '%h%';
results in: An error but should change the collation to the collation of the left operand which is latin1_bin (two different collations of the same characterset with the same coercibility results in a x_bin collation.)
the error-query should behave like this query:
select * from test where CONCAT_WS(' ', convert(x,char) ,y) COLLATE latin1_bin LIKE '%h%';
Hint: There is no error, if instead of a text column a text-literal is used.
select coercibility(CONCAT_WS(' ', Convert(x,char) ,'h'))from test;
result is: 2
select * from test where CONCAT_WS(' ', Convert(x,char) ,'h') LIKE '%h%';
maybe the reason ist that in this case:
select coercibility(CONCAT_WS(' ', Convert(x,char) ,'h'))from test;
results in: latin1_swedish_ci
How to repeat:
create table test (x int, y char(1) character set latin1 collate latin1_general_ci);
insert into test (x,y) values (2,'h');
select * from test where CONCAT_WS(' ', convert(x,char) ,y) LIKE '%h%';
select results in: Error 1267: Illegal mix of collations (latin1_bin,NONE) and (latin1_swedish_ci,COERCIBLE) for operation 'like'
Server config:
collation_connection latin1_swedish_ci
collation_database latin1_general_ci
collation_server latin1_general_ci
Suggested fix:
The collation of the operand with the higher coercibility should be used.
Description: Despite of different coercibilities of the operands an illegal mix of collations error occures. select coercibility(CONCAT_WS(' ', convert(x,char) ,y)) from test; result is: 1 select coercibility('%h%') from test; result is: 4 select * from test where CONCAT_WS(' ', convert(x,char) ,y) LIKE '%h%'; results in: An error but should change the collation to the collation of the left operand which is latin1_bin (two different collations of the same characterset with the same coercibility results in a x_bin collation.) the error-query should behave like this query: select * from test where CONCAT_WS(' ', convert(x,char) ,y) COLLATE latin1_bin LIKE '%h%'; Hint: There is no error, if instead of a text column a text-literal is used. select coercibility(CONCAT_WS(' ', Convert(x,char) ,'h'))from test; result is: 2 select * from test where CONCAT_WS(' ', Convert(x,char) ,'h') LIKE '%h%'; maybe the reason ist that in this case: select coercibility(CONCAT_WS(' ', Convert(x,char) ,'h'))from test; results in: latin1_swedish_ci How to repeat: create table test (x int, y char(1) character set latin1 collate latin1_general_ci); insert into test (x,y) values (2,'h'); select * from test where CONCAT_WS(' ', convert(x,char) ,y) LIKE '%h%'; select results in: Error 1267: Illegal mix of collations (latin1_bin,NONE) and (latin1_swedish_ci,COERCIBLE) for operation 'like' Server config: collation_connection latin1_swedish_ci collation_database latin1_general_ci collation_server latin1_general_ci Suggested fix: The collation of the operand with the higher coercibility should be used.