partition pruning leads to different partition when utf8mb4_general_ci is set

[jiyfhust]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table t(col varchar(32) COLLATE utf8mb4_general_ci DEFAULT NULL) PARTITION BY KEY (`col`) PARTITIONS 7;
explain select * from t where col = 'linpin';
explain select * from t where col = 'LINPIN';

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

The query above should be pruned to the same partition.

3. What did you see instead (Required)

mysql> explain select * from t where col = 'linpin';
+-------------------------+----------+-----------+-----------------------+--------------------------------+
| id                      | estRows  | task      | access object         | operator info                  |
+-------------------------+----------+-----------+-----------------------+--------------------------------+
| TableReader_8           | 10.00    | root      |                       | data:Selection_7               |
| └─Selection_7           | 10.00    | cop[tikv] |                       | eq(test.t.col, "linpin")       |
|   └─TableFullScan_6     | 10000.00 | cop[tikv] | table:t, partition:p4 | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+-----------------------+--------------------------------+
3 rows in set, 1 warning (0.01 sec)

mysql> explain select * from t where col = 'LINPIN';
+-------------------------+----------+-----------+-----------------------+--------------------------------+
| id                      | estRows  | task      | access object         | operator info                  |
+-------------------------+----------+-----------+-----------------------+--------------------------------+
| TableReader_8           | 10.00    | root      |                       | data:Selection_7               |
| └─Selection_7           | 10.00    | cop[tikv] |                       | eq(test.t.col, "LINPIN")       |
|   └─TableFullScan_6     | 10000.00 | cop[tikv] | table:t, partition:p3 | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+-----------------------+--------------------------------+
3 rows in set, 1 warning (0.00 sec)

partition:p4 and partition:p3, they are not same.

4. What is your TiDB version? (Required)

master

[jiyfhust]

Simalar with #51196. But not exactly same.

[bb7133]

Caused by #51164.

[kennedy8312]

/type regression

[kennedy8312]

Regression Analysis
PR Caused this regression: #48522

[kennedy8312]

Regression Analysis:

Commit 7c37466 : Success
Commit 0c7659c : Failure
Commit e053c27 : Failure

-----------------Regression Test Result on e053c27-----------------
Server on e053c27 started.
Validation failed at run 1. e053c27 is a bad label.
Expected:
id estRows task access object operator info
TableReader_8 10.00 root data:Selection_7
└─Selection_7 10.00 cop[tikv] eq(test.t.col, "linpin")
└─TableFullScan_6 10000.00 cop[tikv] table:t, partition:p4 keep order:false, stats:pseudo
id estRows task access object operator info
TableReader_8 10.00 root data:Selection_7
└─Selection_7 10.00 cop[tikv] eq(test.t.col, "LINPIN")
└─TableFullScan_6 10000.00 cop[tikv] table:t, partition:p4 keep order:false, stats:pseudo
Actual:
id estRows task access object operator info
TableReader_8 10.00 root data:Selection_7
└─Selection_7 10.00 cop[tikv] eq(test.t.col, "linpin")
└─TableFullScan_6 10000.00 cop[tikv] table:t, partition:p4 keep order:false, stats:pseudo
id estRows task access object operator info
TableReader_8 10.00 root data:Selection_7
└─Selection_7 10.00 cop[tikv] eq(test.t.col, "LINPIN")
└─TableFullScan_6 10000.00 cop[tikv] table:t, partition:p3 keep order:false, stats:pseudo
-----------------Test Case-----------------
mysql -h 127.0.0.1 -P 4000 -u root -D test --local-infile=true < issue51316_testcase.sql
issue51316_testcase.sql:
drop table if exists t;
create table t(col varchar(32) COLLATE utf8mb4_general_ci DEFAULT NULL) PARTITION BY KEY (col) PARTITIONS 7;
explain select * from t where col = 'linpin';
explain select * from t where col = 'LINPIN';
-----------------Test Result-----------------
Result :
id estRows task access object operator info
TableReader_8 10.00 root data:Selection_7
└─Selection_7 10.00 cop[tikv] eq(test.t.col, "linpin")
└─TableFullScan_6 10000.00 cop[tikv] table:t, partition:p4 keep order:false, stats:pseudo
id estRows task access object operator info
TableReader_8 10.00 root data:Selection_7
└─Selection_7 10.00 cop[tikv] eq(test.t.col, "LINPIN")
└─TableFullScan_6 10000.00 cop[tikv] table:t, partition:p3 keep order:false, stats:pseudo
-----------------TiDB Version-----------------
tidb_version()
Release Version: v7.6.0-alpha-382-ge053c27f06-dirty
Edition: Community
Git Commit Hash: e053c27
Git Branch: HEAD
UTC Build Time: 2024-03-04 08:26:21
GoVersion: go1.21.4
Race Enabled: false
Check Table Before Drop: false
Store: unistore

[kennedy8312]

-----------------Regression Test Result on 7c37466-----------------
Server on 7c37466 started.
Validation passed. 7c37466 is a good label
-----------------Test Case-----------------
mysql -h 127.0.0.1 -P 4000 -u root -D test --local-infile=true < issue51316_testcase.sql
issue51316_testcase.sql:
drop table if exists t;
create table t(col varchar(32) COLLATE utf8mb4_general_ci DEFAULT NULL) PARTITION BY KEY (col) PARTITIONS 7;
explain select * from t where col = 'linpin';
explain select * from t where col = 'LINPIN';
-----------------Test Result-----------------
Result :
id estRows task access object operator info
TableReader_8 10.00 root data:Selection_7
└─Selection_7 10.00 cop[tikv] eq(test.t.col, "linpin")
└─TableFullScan_6 10000.00 cop[tikv] table:t, partition:p4 keep order:false, stats:pseudo
id estRows task access object operator info
TableReader_8 10.00 root data:Selection_7
└─Selection_7 10.00 cop[tikv] eq(test.t.col, "LINPIN")
└─TableFullScan_6 10000.00 cop[tikv] table:t, partition:p4 keep order:false, stats:pseudo
-----------------TiDB Version-----------------
tidb_version()
Release Version: v7.6.0-alpha-381-g7c3746685d-dirty
Edition: Community
Git Commit Hash: 7c37466
Git Branch: HEAD
UTC Build Time: 2024-03-04 08:25:10
GoVersion: go1.21.4
Race Enabled: false
Check Table Before Drop: false
Store: unistore

-----------------End of regression test on 7c37466-----------------