indexjoin return wrong results in partition table

[ChenPeng2013]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

use test;
set tidb_enable_clustered_index=1;
set tidb_partition_prune_mode=dynamic; 
drop table if exists t1, t2;
create table t1  (c_int int, c_str varchar(40) character set latin1 collate latin1_bin, c_datetime datetime, c_timestamp timestamp, c_double double, c_decimal decimal(12, 6), c_enum enum('blue','green','red','yellow','white','orange','purple'), primary key (c_datetime) , key(c_int) , key(c_str(27)) , key(c_decimal)  ) partition by range (to_days(c_datetime)) ( partition p0 values less than (to_days('2020-02-01')), partition p1 values less than (to_days('2020-04-01')), partition p2 values less than (to_days('2020-06-01')), partition p3 values less than maxvalue) ;
create table t2  like t1 ;
insert into t1 values (1, 'laughing brahmagupta', '2020-04-21 22:48:57', '2020-04-13 15:32:09', 67.158610, 6.460, 'white'), (2, 'silly brattain', '2020-03-13 12:46:14', '2020-01-31 23:32:03', 39.159617, 9.997, 'yellow'), (3, 'keen proskuriakova', '2020-06-28 16:29:23', '2020-05-01 02:48:38', 10.036821, 5.438, 'red'), (4, 'festive faraday', '2020-05-31 22:20:23', '2020-03-21 03:20:34', 88.259904, 2.554, 'white'), (5, 'peaceful bouman', '2020-04-08 18:39:24', '2020-02-10 03:48:01', 51.904213, 9.230, 'yellow');
insert into t1 values (6, 'stupefied jepsen', '2020-03-19 20:09:25', '2020-01-11 02:01:41', 32.827268, 1.358, 'yellow'), (7, 'exciting engelbart', '2020-02-28 17:51:30', '2020-04-22 04:00:33', 20.161705, 9.528, 'red'), (8, 'flamboyant stonebraker', '2020-05-07 10:34:51', '2020-02-07 11:48:33', 43.514339, 9.248, 'white'), (9, 'dazzling lalande', '2020-04-10 03:40:15', '2020-04-04 21:38:41', 44.208521, 2.658, 'blue'), (10, 'hopeful gagarin', '2020-01-13 16:55:42', '2020-06-06 06:37:03', 79.028624, 3.146, 'blue');
insert into t2 select * from t1 ;
select * from t1 where c_datetime in (select c_datetime from t2 where t1.c_int = 2);

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

MySQL [test]> select * from t1 where c_datetime in (select c_datetime from t2 where t1.c_int = 2);
+-------+----------------+---------------------+---------------------+-----------+-----------+--------+
| c_int | c_str          | c_datetime          | c_timestamp         | c_double  | c_decimal | c_enum |
+-------+----------------+---------------------+---------------------+-----------+-----------+--------+
|     2 | silly brattain | 2020-03-13 12:46:14 | 2020-01-31 23:32:03 | 39.159617 |  9.997000 | yellow |
+-------+----------------+---------------------+---------------------+-----------+-----------+--------+
1 row in set (0.01 sec)

3. What did you see instead (Required)

MySQL [test]> select * from t1 where c_datetime in (select c_datetime from t2 where t1.c_int = 2);
Empty set (0.00 sec)

4. What is your TiDB version? (Required)

Release Version: v6.1.3-35-g52c603bf9
Edition: Community
Git Commit Hash: 52c603bf90992e0f2f2fadc10cae027f7972346b
Git Branch: release-6.1
UTC Build Time: 2023-01-28 03:26:42
GoVersion: go1.19.1
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[ChenPeng2013]

@mjonss tidb-v6.1.3 don't have this issue, but the latest release-6.1 exists this problem

[mjonss]

Regression from:

commit 3cda90b481e9154948d97badfb77408f12eaec8e
Author: Ti Chi Robot <ti-community-prow-bot@tidb.io>
Date:   Fri Jan 13 20:03:47 2023 +0800

    *: Fix issue 39999, used wrong column id list for checking partitions (#40003) (#40063)
    
    close pingcap/tidb#39999

[mjonss]

Basically #40674 needs to be backported to 6.1 and 6.5, the fix is the same.

[mjonss]

#40674 closed #40596 which is already included in 6.5

[kennedy8312]

/type regression