Bug #118722 ERROR 1267 (HY000): Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8mb4_0900_ai_ci,COERCIBLE) for operation '='

• Submitted: 26 Jul 11:08
• Modified: 29 Jul 4:09
• Reporter: fent allen
• Status: Analyzing
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 8.0.41
• OS: Linux
• Assigned to: MySQL Verification Team
• CPU Architecture: ARM

[26 Jul 11:08] fent allen

Description:
When inserting Chinese characters into a Latin1 table, an error occurs when querying the Chinese characters.

How to repeat:
drop table if exists tab_charset_latin1_004;
create table tab_charset_latin1_004 (
    id int auto_increment primary key,
    char_col01 char(100) character set latin1 collate latin1_bin,
    char_col02 char(100) character set latin1 collate latin1_danish_ci,
    char_col03 char(100) character set latin1 collate latin1_general_ci,
    varchar_col01 varchar(255) character set latin1 collate latin1_bin,
    varchar_col02 varchar(255) character set latin1 collate latin1_danish_ci,
    varchar_col03 varchar(255) character set latin1 collate latin1_general_ci,
    text_col01 text character set latin1 collate latin1_bin,
    text_col02 text character set latin1 collate latin1_danish_ci,
    text_col03 text character set latin1 collate latin1_general_ci,
    tinytext_col01 tinytext character set latin1 collate latin1_bin,
    tinytext_col02 tinytext character set latin1 collate latin1_danish_ci,
    tinytext_col03 tinytext character set latin1 collate latin1_general_ci,
    blob_col blob,
    tinyblob_col tinyblob,
    json_col json
) engine=dstore default charset=latin1;
alter table tab_charset_latin1_004 add index idx_multi_column (char_col01(50), varchar_col01(100));

insert into tab_charset_latin1_004 (char_col01, char_col02, char_col03, varchar_col01, varchar_col02, varchar_col03, text_col01, text_col02, text_col03, tinytext_col01, tinytext_col02, tinytext_col03, blob_col, tinyblob_col, json_col) values ('hello', 'world', 'test', 'mysql', 'utf8mb4', 'life', 'this is a text', 'another text', 'example', 'tiny text 1', 'tiny text 2', 'tiny text 3', 'blob data','tinyblob data', '{"name": "john", "age": 30}');
insert into tab_charset_latin1_004 (char_col01, char_col02, char_col03, varchar_col01, varchar_col02, varchar_col03, text_col01, text_col02, text_col03, tinytext_col01, tinytext_col02, tinytext_col03, blob_col, tinyblob_col, json_col)values ('测试', '测试', '测试', '这是一个测试
字符串', '这是一个测试字符串', '这是一个测试字符串',  '这是一个测试文本', '这是一个测试文本', '这是一个测试文本','这是一个短文本', '这是一个短文本', '这是一个短文本',null, null, '{"key": "value"}');
insert into tab_charset_latin1_004 (char_col01, char_col02, char_col03, varchar_col01, varchar_col02, varchar_col03, text_col01, text_col02, text_col03, tinytext_col01, tinytext_col02, tinytext_col03, blob_col, tinyblob_col, json_col)values  ('αβγ', 'αβγ', 'αβγ', '测试@#$%^&*()_+特殊符号', '测试@#$%^&*()_+特殊符号', '测试@#$%^&*()_+特殊符号', '带特殊符号的文本: ~`!@#$%^&*()_+-=[]{}|;:\'",.<>/?', '带特殊符号的文本: ~`!@#$%^&*()_+-=[]{}|;:\'",.<>/?', '带特殊符号的文本:~`!@#$%^&*()_+-=[]{}|;:\'",.<>/?', '短特殊文本: !@#', '短特殊文本: !@#', '短特殊文本: !@#', null, null, '{"key": "value with @ # $"}');

select * from tab_charset_latin1_004;
select * from tab_charset_latin1_004 where char_col01 = "hello" or varchar_col01 = "测试@#$%^&*()_+特殊符号";
ERROR 1267 (HY000): Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8mb4_0900_ai_ci,COERCIBLE) for operation '='