It raises the error of Invalid latin1 character string

[lilinghai]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table t(a varchar(20))CHARSET=binary;
create table t2(a varchar(20) )CHARSET=latin1;
insert into t values('😉');
insert into t2 values('😉');
select count(*) from t join t2 on t.a=t2.a; 

2. What did you expect to see? (Required)

error when insert or successful when select

3. What did you see instead (Required)

ERROR 1300 (HY000): Invalid latin1 character string: 'F09F9889'

4. What is your TiDB version? (Required)

Release Version: v5.4.0-alpha-278-ga04601477
Edition: Community
Git Commit Hash: a046014
Git Branch: master
UTC Build Time: 2021-12-01 03:36:18
GoVersion: go1.16.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[ChenPeng2013]

https://docs.pingcap.com/tidb/stable/character-set-and-collation

TiDB incorrectly treats latin1 as a subset of utf8. This can lead to unexpected behaviors when you store characters that differ between latin1 and utf8 encodings. It is strongly recommended to the utf8mb4 character set. See TiDB #18955 for more details.

[bb7133]

PTAL @tangenta

[github-actions]

Please check whether the issue should be labeled with 'affects-x.y' or 'fixes-x.y.z', and then remove 'needs-more-info' label.