delete with subquery returns incorrect result

## Bug Report

Please answer these questions before submitting your issue. Thanks!

### 1. Minimal reproduce step (Required)

DROP TABLE /*! IF EXISTS*/ mysql_1;
CREATE TABLE `mysql_1` (
`col_int_auto_increment` int(10) AUTO_INCREMENT,
`col_pk_char` char(60) not null,
`col_pk_varchar` varchar(60) not null,
`col_pk_date` date not null,
`col_int` int,
`col_int__1` int,
`col_int_8` int(8),
`col_int_8__1` int(8),
`col_bigint` bigint,
`col_smallint` smallint,
`col_tinyint` tinyint,
`col_float` float,
`col_double` double,
`col_numeric` numeric,
`col_bit` bit,
`col_bit__1` bit,
`col_enum` enum ('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z'),
`col_set` set ('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z'),
`col_char_3` char(3),
`col_char_255` char(255),
`col_varchar_1` varchar(1),
`col_varchar_64` varchar(64),
`col_varchar_2048` varchar(2048),
`col_binary_8` binary(8),
`col_varbinary_8` varbinary(8),
`col_date` date,
`col_time` time,
`col_datetime` datetime,
`col_timestamp` timestamp ,
`col_year` year,
`col_text` text,
`col_bool` bool,
`col_boolean` boolean,
`col_longtext` longtext,
`col_mediumtext` mediumtext,
`col_tinyblob` tinyblob,
`col_mediumblob` mediumblob,
`col_longblob` longblob,
`col_blob` blob,
/*Indices*/
KEY `prefix_index2` (`col_char_255`(10)),
KEY `prefix_index1` (`col_text`(15)),
KEY `functional_index2` (`col_datetime`,`col_date`),
KEY `functional_index1` (`col_enum`,`col_year`,`col_int`),
KEY k2 (`col_int`,`col_char_255`),
KEY k1 (`col_pk_varchar`),
primary key (`col_int_auto_increment`,`col_pk_varchar`,`col_datetime`,`col_int`,`col_date`)) ;

DROP TABLE /*! IF EXISTS*/ mysql_3;
CREATE TABLE `mysql_3` (
`col_int_auto_increment` int(10) AUTO_INCREMENT,
`col_pk_char` char(60) not null,
`col_pk_varchar` varchar(60) not null,
`col_pk_date` date not null,
`col_int` int,
`col_int__1` int,
`col_int_8` int(8),
`col_int_8__1` int(8),
`col_bigint` bigint,
`col_smallint` smallint,
`col_tinyint` tinyint,
`col_float` float,
`col_double` double,
`col_numeric` numeric,
`col_bit` bit,
`col_bit__1` bit,
`col_enum` enum ('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z'),
`col_set` set ('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z'),
`col_char_3` char(3),
`col_char_255` char(255),
`col_varchar_1` varchar(1),
`col_varchar_64` varchar(64),
`col_varchar_2048` varchar(2048),
`col_binary_8` binary(8),
`col_varbinary_8` varbinary(8),
`col_date` date,
`col_time` time,
`col_datetime` datetime,
`col_timestamp` timestamp ,
`col_year` year,
`col_text` text,
`col_bool` bool,
`col_boolean` boolean,
`col_longtext` longtext,
`col_mediumtext` mediumtext,
`col_tinyblob` tinyblob,
`col_mediumblob` mediumblob,
`col_longblob` longblob,
`col_blob` blob,
/*Indices*/
KEY `prefix_index2` (`col_char_255`(10)),
KEY `prefix_index1` (`col_text`(15)),
KEY `functional_index2` (`col_datetime`,`col_date`),
KEY `functional_index1` (`col_enum`,`col_year`,`col_int`),
KEY k2 (`col_int`,`col_char_255`),
KEY k1 (`col_pk_varchar`),
primary key (`col_int_auto_increment`,`col_pk_varchar`,`col_datetime`,`col_int`,`col_date`)) ;

DROP TABLE /*! IF EXISTS*/ mysql_2;
CREATE TABLE `mysql_2` (
`col_int_auto_increment` int(10) AUTO_INCREMENT,
`col_pk_char` char(60) not null,
`col_pk_varchar` varchar(60) not null,
`col_pk_date` date not null,
`col_int` int,
`col_int__1` int,
`col_int_8` int(8),
`col_int_8__1` int(8),
`col_bigint` bigint,
`col_smallint` smallint,
`col_tinyint` tinyint,
`col_float` float,
`col_double` double,
`col_numeric` numeric,
`col_bit` bit,
`col_bit__1` bit,
`col_enum` enum ('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z'),
`col_set` set ('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z'),
`col_char_3` char(3),
`col_char_255` char(255),
`col_varchar_1` varchar(1),
`col_varchar_64` varchar(64),
`col_varchar_2048` varchar(2048),
`col_binary_8` binary(8),
`col_varbinary_8` varbinary(8),
`col_date` date,
`col_time` time,
`col_datetime` datetime,
`col_timestamp` timestamp ,
`col_year` year,
`col_text` text,
`col_bool` bool,
`col_boolean` boolean,
`col_longtext` longtext,
`col_mediumtext` mediumtext,
`col_tinyblob` tinyblob,
`col_mediumblob` mediumblob,
`col_longblob` longblob,
`col_blob` blob,
/*Indices*/
KEY `prefix_index2` (`col_char_255`(10)),
KEY `prefix_index1` (`col_text`(15)),
KEY `functional_index2` (`col_datetime`,`col_date`),
KEY `functional_index1` (`col_enum`,`col_year`,`col_int`),
KEY k2 (`col_int`,`col_char_255`),
KEY k1 (`col_pk_varchar`),
primary key (`col_int_auto_increment`,`col_pk_varchar`,`col_datetime`,`col_int`,`col_date`)) ;

INSERT /*! IGNORE */ INTO mysql_1 (col_pk_char,col_pk_varchar,col_pk_date,col_int,col_int__1,col_int_8,col_int_8__1,col_bigint,col_smallint,col_tinyint,col_float,col_double,col_numeric,col_bit,col_bit__1,col_enum,col_set,col_char_3,col_char_255,col_varchar_1,col_varchar_64,col_varchar_2048,col_binary_8,col_varbinary_8,col_date,col_time,col_datetime,col_timestamp,col_year,col_text,col_bool,col_boolean,col_longtext,col_mediumtext,col_tinyblob,col_mediumblob,col_longblob,col_blob) VALUES ('Привет, мир!', NULL, '2003-08-19', NULL, 3.52386474609375e+80, 318570496, NULL, 1204486144, 62359, 1.75048828125e+80, -1540685824, 7, -1122041856, 49247, NULL, 'p', 'm', 'p', 'str_p17_0xl', 'gyzjwxlgqjocdqzzwvilnibklpphforlxxdhzrzvhljbnkmdosigeixttncwdfujdcnqxduqjflkezociprjakllehxpiiug', 'e', 'incident', 'yzjwxlgqjocdqzzwvilnibklpphforlxxdhzrzvhljbnkmdosigeixttncwdfujdcnqxduqjflkezociprjakllehxpiiugmqwharkorupkwgiwripflgthlxrzardrxpwlfgtadpfpngyarpqybxzohygfcaasntcxjrzafbudntnfnbhaajdzhaafvqlsjdoukccyangcbgjucubxtwevxoicfgbyzdrrxqwqqzdmndotlkbqgipnppxnwefakqjkdcxpvrkjxgomsbqwlfmoyfqyrtpswviiwjkosnbvqhywtugxxyyjpgrqbekjmmyzirritoruqpyrrsjoqdoeuaewyociyfjvhrinhhbncftttxiakbejrepzquuxaonudozihjwyrnigkuxxzolzuwuzokgserimpgqvbppjdjxszdzmhxpncunomaawdimejbdnnflqcmxyeequvqxepqpzoahztlcgfzlglahprifzdqpfjjnyiaczhdrxyspgqdyjfzetrwjgawftkwheiyfzkytgzernhtitnsyjcvktuiemnelnpiglprldbdgxubgtarwvffuaijgjtuggnovgqfvojpsfkdlhtdxnfniirolwvlmebaqiquuycskktpqfxpghdlnugqgykrxvqvitiufuifxixlvgjisvoolhmguderokrjkkgettdbopdyszbnejyzudrzibrdgyuexkcvdbhcvwgerrirlyonfgzwlmhweolgkcrwkiancyoeipardimjqqxjpnkmfgtxiuqrbqkoaxykcmmokvjjqesdoppiwzjojkxactfuwiwkijnydlaidllethtudycfzbbcmqmbdbqqtmordhemuvzhdnfbslfalcljtgkbpijoo', 'a', '1986-12-17', '2011', '2017-07-03 00:00:00', '2007-12-07 23:50:05.033659', '2032', '8.427734375e+80', b'1', b'0', NULL, 't', '2009-11-09 22:55:53.063095', 'b', '2014-12-07 00:48:55.062178', NULL);

INSERT /*! IGNORE */ INTO mysql_2 (col_pk_char,col_pk_varchar,col_pk_date,col_int,col_int__1,col_int_8,col_int_8__1,col_bigint,col_smallint,col_tinyint,col_float,col_double,col_numeric,col_bit,col_bit__1,col_enum,col_set,col_char_3,col_char_255,col_varchar_1,col_varchar_64,col_varchar_2048,col_binary_8,col_varbinary_8,col_date,col_time,col_datetime,col_timestamp,col_year,col_text,col_bool,col_boolean,col_longtext,col_mediumtext,col_tinyblob,col_mediumblob,col_longblob,col_blob) VALUES ('Привет, мир!', NULL, '2003-08-19', NULL, 3.52386474609375e+80, 318570496, NULL, 1204486144, 62359, 1.75048828125e+80, -1540685824, 7, -1122041856, 49247, NULL, 'p', 'm', 'p', 'str_p17_0xl', 'gyzjwxlgqjocdqzzwvilnibklpphforlxxdhzrzvhljbnkmdosigeixttncwdfujdcnqxduqjflkezociprjakllehxpiiug', 'e', 'incident', 'yzjwxlgqjocdqzzwvilnibklpphforlxxdhzrzvhljbnkmdosigeixttncwdfujdcnqxduqjflkezociprjakllehxpiiugmqwharkorupkwgiwripflgthlxrzardrxpwlfgtadpfpngyarpqybxzohygfcaasntcxjrzafbudntnfnbhaajdzhaafvqlsjdoukccyangcbgjucubxtwevxoicfgbyzdrrxqwqqzdmndotlkbqgipnppxnwefakqjkdcxpvrkjxgomsbqwlfmoyfqyrtpswviiwjkosnbvqhywtugxxyyjpgrqbekjmmyzirritoruqpyrrsjoqdoeuaewyociyfjvhrinhhbncftttxiakbejrepzquuxaonudozihjwyrnigkuxxzolzuwuzokgserimpgqvbppjdjxszdzmhxpncunomaawdimejbdnnflqcmxyeequvqxepqpzoahztlcgfzlglahprifzdqpfjjnyiaczhdrxyspgqdyjfzetrwjgawftkwheiyfzkytgzernhtitnsyjcvktuiemnelnpiglprldbdgxubgtarwvffuaijgjtuggnovgqfvojpsfkdlhtdxnfniirolwvlmebaqiquuycskktpqfxpghdlnugqgykrxvqvitiufuifxixlvgjisvoolhmguderokrjkkgettdbopdyszbnejyzudrzibrdgyuexkcvdbhcvwgerrirlyonfgzwlmhweolgkcrwkiancyoeipardimjqqxjpnkmfgtxiuqrbqkoaxykcmmokvjjqesdoppiwzjojkxactfuwiwkijnydlaidllethtudycfzbbcmqmbdbqqtmordhemuvzhdnfbslfalcljtgkbpijoo', 'a', '1986-12-17', '2011', '2017-07-03 00:00:00', '2007-12-07 23:50:05.033659', '2032', '8.427734375e+80', b'1', b'0', NULL, 't', '2009-11-09 22:55:53.063095', 'b', '2014-12-07 00:48:55.062178', NULL);
INSERT /*! IGNORE */ INTO mysql_3 (col_pk_char,col_pk_varchar,col_pk_date,col_int,col_int__1,col_int_8,col_int_8__1,col_bigint,col_smallint,col_tinyint,col_float,col_double,col_numeric,col_bit,col_bit__1,col_enum,col_set,col_char_3,col_char_255,col_varchar_1,col_varchar_64,col_varchar_2048,col_binary_8,col_varbinary_8,col_date,col_time,col_datetime,col_timestamp,col_year,col_text,col_bool,col_boolean,col_longtext,col_mediumtext,col_tinyblob,col_mediumblob,col_longblob,col_blob) VALUES ('Привет, мир!', NULL, '2003-08-19', NULL, 3.52386474609375e+80, 318570496, NULL, 1204486144, 62359, 1.75048828125e+80, -1540685824, 7, -1122041856, 49247, NULL, 'p', 'm', 'p', 'str_p17_0xl', 'gyzjwxlgqjocdqzzwvilnibklpphforlxxdhzrzvhljbnkmdosigeixttncwdfujdcnqxduqjflkezociprjakllehxpiiug', 'e', 'incident', 'yzjwxlgqjocdqzzwvilnibklpphforlxxdhzrzvhljbnkmdosigeixttncwdfujdcnqxduqjflkezociprjakllehxpiiugmqwharkorupkwgiwripflgthlxrzardrxpwlfgtadpfpngyarpqybxzohygfcaasntcxjrzafbudntnfnbhaajdzhaafvqlsjdoukccyangcbgjucubxtwevxoicfgbyzdrrxqwqqzdmndotlkbqgipnppxnwefakqjkdcxpvrkjxgomsbqwlfmoyfqyrtpswviiwjkosnbvqhywtugxxyyjpgrqbekjmmyzirritoruqpyrrsjoqdoeuaewyociyfjvhrinhhbncftttxiakbejrepzquuxaonudozihjwyrnigkuxxzolzuwuzokgserimpgqvbppjdjxszdzmhxpncunomaawdimejbdnnflqcmxyeequvqxepqpzoahztlcgfzlglahprifzdqpfjjnyiaczhdrxyspgqdyjfzetrwjgawftkwheiyfzkytgzernhtitnsyjcvktuiemnelnpiglprldbdgxubgtarwvffuaijgjtuggnovgqfvojpsfkdlhtdxnfniirolwvlmebaqiquuycskktpqfxpghdlnugqgykrxvqvitiufuifxixlvgjisvoolhmguderokrjkkgettdbopdyszbnejyzudrzibrdgyuexkcvdbhcvwgerrirlyonfgzwlmhweolgkcrwkiancyoeipardimjqqxjpnkmfgtxiuqrbqkoaxykcmmokvjjqesdoppiwzjojkxactfuwiwkijnydlaidllethtudycfzbbcmqmbdbqqtmordhemuvzhdnfbslfalcljtgkbpijoo', 'a', '1986-12-17', '2011', '2017-07-03 00:00:00', '2007-12-07 23:50:05.033659', '2032', '8.427734375e+80', b'1', b'0', NULL, 't', '2009-11-09 22:55:53.063095', 'b', '2014-12-07 00:48:55.062178', NULL);

begin;
delete from mysql_3 t1 where exists ( SELECT a1.col_int_auto_increment, a2.col_pk_char, a2.col_pk_varchar, a1.col_pk_date, a2.col_int, a1.col_int__1, a2.col_int_8, a2.col_int_8__1, a2.col_bigint, a1.col_smallint, a2.col_tinyint, a1.col_float, a2.col_double, a2.col_numeric, a2.col_bit, a1.col_bit__1, a1.col_enum, a1.col_set, a1.col_char_3, a2.col_char_255, a2.col_varchar_1, a1.col_varchar_64, a2.col_varchar_2048, a2.col_binary_8, a2.col_varbinary_8, a2.col_date, a2.col_time, a1.col_datetime, a1.col_timestamp, a2.col_year, a2.col_text, a2.col_bool, a2.col_boolean, a1.col_longtext, a1.col_mediumtext, a1.col_tinyblob, a1.col_mediumblob, a2.col_longblob, a1.col_blob FROM mysql_2 a1 NATURAL LEFT JOIN mysql_1 a2 WHERE a2.col_pk_date IS NULL ) /* QNO 924 CON_ID 20312 */ ;

SELECT a1.col_int_auto_increment, a2.col_pk_char, a2.col_pk_varchar, a1.col_pk_date, a2.col_int, a1.col_int__1, a2.col_int_8, a2.col_int_8__1, a2.col_bigint, a1.col_smallint, a2.col_tinyint, a1.col_float, a2.col_double, a2.col_numeric, a2.col_bit, a1.col_bit__1, a1.col_enum, a1.col_set, a1.col_char_3, a2.col_char_255, a2.col_varchar_1, a1.col_varchar_64, a2.col_varchar_2048, a2.col_binary_8, a2.col_varbinary_8, a2.col_date, a2.col_time, a1.col_datetime, a1.col_timestamp, a2.col_year, a2.col_text, a2.col_bool, a2.col_boolean, a1.col_longtext, a1.col_mediumtext, a1.col_tinyblob, a1.col_mediumblob, a2.col_longblob, a1.col_blob FROM mysql_2 a1 NATURAL LEFT JOIN mysql_1 a2 WHERE a2.col_pk_date IS NULL;
select count(*) from mysql_3;
rollback;

### 2. What did you expect to see? (Required)
results from mysql8:

MySQL [chqin]> delete from mysql_3 t1 where exists ( SELECT a1.col_int_auto_increment, a2.col_pk_char, a2.col_pk_varchar, a1.col_pk_date, a2.col_int, a1.col_int__1, a2.col_int_8, a2.col_int_8__1, a2.col_bigint, a1.col_smallint, a2.col_tinyint, a1.col_float, a2.col_double, a2.col_numeric, a2.col_bit, a1.col_bit__1, a1.col_enum, a1.col_set, a1.col_char_3, a2.col_char_255, a2.col_varchar_1, a1.col_varchar_64, a2.col_varchar_2048, a2.col_binary_8, a2.col_varbinary_8, a2.col_date, a2.col_time, a1.col_datetime, a1.col_timestamp, a2.col_year, a2.col_text, a2.col_bool, a2.col_boolean, a1.col_longtext, a1.col_mediumtext, a1.col_tinyblob, a1.col_mediumblob, a2.col_longblob, a1.col_blob FROM mysql_2 a1 NATURAL LEFT JOIN mysql_1 a2 WHERE a2.col_pk_date IS NULL ) /* QNO 924 CON_ID 20312 */ ;
Query OK, 1 row affected (0.00 sec)

### 3. What did you see instead (Required)

mysql> delete from mysql_3 t1 where exists ( SELECT a1.col_int_auto_increment, a2.col_pk_char, a2.col_pk_varchar, a1.col_pk_date, a2.col_int, a1.col_int__1, a2.col_int_8, a2.col_int_8__1, a2.col_bigint, a1.col_smallint, a2.col_tinyint, a1.col_float, a2.col_double, a2.col_numeric, a2.col_bit, a1.col_bit__1, a1.col_enum, a1.col_set, a1.col_char_3, a2.col_char_255, a2.col_varchar_1, a1.col_varchar_64, a2.col_varchar_2048, a2.col_binary_8, a2.col_varbinary_8, a2.col_date, a2.col_time, a1.col_datetime, a1.col_timestamp, a2.col_year, a2.col_text, a2.col_bool, a2.col_boolean, a1.col_longtext, a1.col_mediumtext, a1.col_tinyblob, a1.col_mediumblob, a2.col_longblob, a1.col_blob FROM mysql_2 a1 NATURAL LEFT JOIN mysql_1 a2 WHERE a2.col_pk_date IS NULL ) /* QNO 924 CON_ID 20312 */ ;
Query OK, 0 rows affected (0.00 sec)

### 4. What is your TiDB version? (Required)
mysql> select tidb_version();
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version() |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v8.5.1
Edition: Community
Git Commit Hash: fea86c8e35ad4a86a5e1160701f99493c2ee547c
Git Branch: HEAD
UTC Build Time: 2025-01-16 07:38:34
GoVersion: go1.23.4
Race Enabled: false
Check Table Before Drop: false
Store: tikv |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>