character set correctness

[apollodafoni]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table t(a varchar(10) collate utf8_general_ci, b char(10) collate utf8_general_ci);
insert into t values ('a', 'á'),('aa', 'A '),('b', 'B'),('c', 'c'),(' ', ''); 
select * from t t1 where (t1.a, t1.b) in (select b, a from t t2); 

utf8mb4_general_ci, utf8_unicode_ci, utf8mb4_unicode_ci has the same problem.

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

+------+------+
| a | b |
+------+------+
| a | á |
| b | B |
| c | c |
| | |
+------+------+

3. What did you see instead (Required)

+------+------+
| a | b |
+------+------+
| a | á |
| aa | A |
| b | B |
| c | c |
| | |
+------+------+

4. What is your TiDB version? (Required)

Release Version: v8.3.0-alpha
Edition: Community
Git Commit Hash: 7cdf918
Git Branch: heads/refs/tags/v8.3.0-alpha
UTC Build Time: 2024-07-28 11:48:31
GoVersion: go1.21.10
Race Enabled: false
Check Table Before Drop: false
Store: tikv

[apollodafoni]

/impact wrong-result

[apollodafoni]

/component sql-infra

[ti-chi-bot]

@apollodafoni: The label(s) component/sql-infra cannot be applied, because the repository doesn't have them.

In response to this:

/component sql-infra

Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the ti-community-infra/tichi repository.

[apollodafoni]

/sig sql-infra

[apollodafoni]

/severity major

[xhebox]

ref #53127, problem of hash join v2 @windtalker