inner join result is incorrect when new collation is enable

[aytrack]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

1. set new_collations_enabled_on_first_bootstrap = true
2. execute the sqls.

DROP TABLE IF EXISTS `UK_COLLATION7902`;
CREATE TABLE `UK_COLLATION7902` (  `COL1` TINYTEXT COLLATE UTF8MB4_GENERAL_CI,  `COL2` VARCHAR(20) COLLATE UTF8MB4_GENERAL_CI DEFAULT NULL,  `COL3` DATETIME DEFAULT NULL,  `COL4` BIGINT DEFAULT NULL,  `COL5` FLOAT DEFAULT NULL,  UNIQUE KEY `U_COL1` (`COL1`(10)))  DEFAULT CHARSET=UTF8MB4 COLLATE=UTF8MB4_GENERAL_CI;
INSERT INTO `UK_COLLATION7902` VALUES ('AAA', 'BBB', ' 1869-05-20 19:01:32', 100, 20);
SELECT * FROM `UK_COLLATION7902` WHERE COL1 = 'AAA';
SELECT * FROM `UK_COLLATION7902` AS T1 INNER JOIN `UK_COLLATION7902` T2 ON T1.COL1 = T2.COL1 WHERE T1.COL1 = 'AAA';

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

mysql > SELECT * FROM `UK_COLLATION7902` AS T1 INNER JOIN `UK_COLLATION7902` T2 ON T1.COL1 = T2.COL1 WHERE T1.COL1 = 'AAA';
+------+------+---------------------+------+------+------+------+---------------------+------+------+
| COL1 | COL2 | COL3                | COL4 | COL5 | COL1 | COL2 | COL3                | COL4 | COL5 |
+------+------+---------------------+------+------+------+------+---------------------+------+------+
| AAA  | BBB  | 1869-05-20 19:01:32 | 100  | 20.0 | AAA  | BBB  | 1869-05-20 19:01:32 | 100  | 20.0 |
+------+------+---------------------+------+------+------+------+---------------------+------+------+

3. What did you see instead (Required)

mysql root@172.16.5.71:test> SELECT * FROM `UK_COLLATION7902` WHERE COL1 = 'AAA';
+------+------+---------------------+------+------+
| COL1 | COL2 | COL3                | COL4 | COL5 |
+------+------+---------------------+------+------+
| AAA  | BBB  | 1869-05-20 19:01:32 | 100  | 20.0 |
+------+------+---------------------+------+------+

1 row in set
Time: 0.056s
mysql > SELECT * FROM `UK_COLLATION7902` AS T1 INNER JOIN `UK_COLLATION7902` T2 ON T1.COL1 = T2.COL1 WHERE T1.COL1 = 'AAA';
+------+------+------+------+------+------+------+------+------+------+
| COL1 | COL2 | COL3 | COL4 | COL5 | COL1 | COL2 | COL3 | COL4 | COL5 |
+------+------+------+------+------+------+------+------+------+------+

0 rows in set
Time: 0.062s
mysql > DESC SELECT * FROM `UK_COLLATION7902` AS T1 INNER JOIN `UK_COLLATION7902` T2 ON T1.COL1 = T2.COL1 WHERE T1.COL1 = 'AAA';
+------------------------------+---------+-----------+------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                           | estRows | task      | access object                | operator info                                                                                                                                                                       |
+------------------------------+---------+-----------+------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| IndexJoin_14                 | 0.64    | root      |                              | inner join, inner:IndexLookUp_13, outer key:test.uk_collation7902.col1, inner key:test.uk_collation7902.col1, equal cond:eq(test.uk_collation7902.col1, test.uk_collation7902.col1) |
| ├─IndexLookUp_41(Build)      | 0.80    | root      |                              |                                                                                                                                                                                     |
| │ ├─IndexRangeScan_38(Build) | 1.00    | cop[tikv] | table:T1, index:U_COL1(COL1) | range:["AAA","AAA"], keep order:false, stats:pseudo                                                                                                                                 |
| │ └─Selection_40(Probe)      | 0.80    | cop[tikv] |                              | eq(test.uk_collation7902.col1, "AAA"), not(isnull(test.uk_collation7902.col1))                                                                                                      |
| │   └─TableRowIDScan_39      | 1.00    | cop[tikv] | table:T1                     | keep order:false, stats:pseudo                                                                                                                                                      |
| └─IndexLookUp_13(Probe)      | 0.80    | root      |                              |                                                                                                                                                                                     |
|   ├─IndexRangeScan_10(Build) | 1.00    | cop[tikv] | table:T2, index:U_COL1(COL1) | range: decided by [eq(test.uk_collation7902.col1, test.uk_collation7902.col1)], keep order:false, stats:pseudo                                                                      |
|   └─Selection_12(Probe)      | 0.80    | cop[tikv] |                              | eq(test.uk_collation7902.col1, "AAA"), not(isnull(test.uk_collation7902.col1))                                                                                                      |
|     └─TableRowIDScan_11      | 1.00    | cop[tikv] | table:T2                     | keep order:false, stats:pseudo                                                                                                                                                      |
+------------------------------+---------+-----------+------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

9 rows in set
Time: 0.055s

4. What is your TiDB version? (Required)

master 550ca8e

[ti-srebot]

Please edit this comment or add a new comment to complete the following information

Not a bug

1. Remove the 'type/bug' label
2. Add notes to indicate why it is not a bug

Duplicate bug

1. Add the 'type/duplicate' label
2. Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added
Example for how to fill out the template: #20100

1. Root Cause Analysis (RCA) (optional)

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

5. Affected versions

[v4.0.0:v4.0.11]

6. Fixed versions

master