[MDEV-17013] Illegal mix of collations latin1_swedish_ci and utf8mb4_general_ci Created: 2018-08-18  Updated: 2018-08-21  Resolved: 2018-08-20

Status: Closed
Project: MariaDB Server
Component/s: Character Sets
Affects Version/s: 10.1.35
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Olaf van der Spek Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Debian


 Description   

latin1 is a subset of utf8 so the comparison is well defined.

CREATE TABLE `t` (
 
  `a` varchar(255) CHARACTER SET latin1 NOT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
 
 
SELECT * FROM t WHERE a = 'ā';
 
 
 
// ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '='



 Comments   
Comment by Alice Sherepa [ 2018-08-20 ]

https://dev.mysql.com/doc/refman/8.0/en/charset-collation-coercibility.html
(to resolve ambiguities: use the collation with the lowest coercibility value)

SELECT * FROM t WHERE a = 'ā'; - two strings are compared:

1) t.a value (latin1, latin1_swedish_ci, coercibility=2);
2) string literal 'ā' ( connection's character set - utf8,utf8mb4_general_ci, coercibility=4).

So the server is trying to convert the second string to latin1, but it fails.

Generated at Thu Feb 08 08:33:15 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.