Use hexadecimal to select and get wrong results when opening new collation

[wan1y]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE `t1` (
  `COL1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`COL1`(5)) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

INSERT INTO `t1` VALUES ('Ȇ');

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

mysql> select * from t1 where col1 not in (0xc484, 0xe5a4bc, 0xc3b3);
+------+
| COL1 |
+------+
| Ȇ    |
+------+
1 row in set (0.05 sec)
 

3. What did you see instead (Required)

mysql> select * from t1 where col1 not in (0xc484, 0xe5a4bc, 0xc3b3);
+------+
| COL1 |
+------+
| Ȇ    |
| Ȇ    |
+------+
2 rows in set (0.01 sec)
 

4. What is your TiDB version? (Required)

v4.0.0-beta
v5.0.0-nightly

[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

6. Fixed versions