the subquery result is wrong

[ChenPeng2013]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

source  [test.txt](https://github.com/pingcap/tidb/files/10856845/test.txt)
# maybe wait for hours

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

MySQL [test]> select col1, col2 from UK_HP16709 where col1 > (select max(col1) from UK_HP16709 t1 where t1.col1 > 1113867879);
Empty set (0.06 sec)

3. What did you see instead (Required)

mysql> select col1, col2 from UK_HP16709 where col1 > (select max(col1) from UK_HP16709 t1 where t1.col1 > 1113867879);
+------------+--------------------------------------------------------------+
| col1       | col2                                                         |
+------------+--------------------------------------------------------------+
| 3747335913 | 澸穒艍朇鶩襽状闕璚筴甡堃鑽眐榯藖禬呈擰讳                     |
| 4270433610 | 抧銲勇轰贂勦螜婍唐藮冪瞈卞袈碗逗鼌鶯揬澳                     |
| 4289373785 | 绅讋檾郊鋱杹咋籲堲县忱鐯鉵凃秚袖雱鹢项臵                     |
| 4254321426 | 焯紈枎硬颿闿遛昢楑啲瀸礐橷灬齾鮚笂侠敓忳                     |
| 4257249400 | 鏜煜鉮絇詳挌而唆貋糥導撚菛砾Ĉ覩獦笊挤戤                      |
| 4016492889 | 儭章程顎菑竹磟稃琷漩弄扏煒鶈喌狳檠釈欉Dz                      |
| 3722399782 | 男慦羙圲痼蝺č冣鼙颪銝棨锚媴桵紇釾傪ᛋ瀥                       |
| 3625863817 | 賰皲孎侐钬珩靹搱灂驿诶榧抚偘捚盷恨容磍黵                     |
| 4250099741 | 琣置鐐砛搹ň蠮瑇檙鍢潂賱鏩偵枖賸唱刨宇唬                      |
| 4141088841 | 鼀傝颬媾蚝杫率褞瞪愔朲鴏輅椰遪饯琤奂椼纈                     |
| 4047043168 | 妫壼紤乼罌犏鲰郐椋t畝湹昦苻悴耬翄ĸ累婑                       |
| 3955035493 | Ɉ堼殺儭鮺虐嚜Ͱ包獚垙悷鹾蟍銢漌笕Lj嚲扰                        |
| 4294967295 | 炚欠嘔戭濧鸲铼皿釔梋呯舮嬘芵畫黵剝璅鑰瞖                     |
| 3847101422 | 毖铏憛唝刭傣骝絁\顏荍眨鹅谗喼俑窫擅淇猂                      |
+------------+--------------------------------------------------------------+
14 rows in set (0.05 sec)

4. What is your TiDB version? (Required)

Release Version: v6.7.0-alpha
Edition: Community
Git Commit Hash: 61ed093fe1f47911fcb2b1a8ab1f33be0cc5aaca
Git Branch: heads/refs/tags/v6.7.0-alpha
UTC Build Time: 2023-02-28 07:23:38
GoVersion: go1.20.1
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv

[fzzf678]

mysql> source /Users/fanzhou/Downloads/test.txt;

mysql> select col1, col2 from UK_HP16709 where col1 > (select max(col1) from UK_HP16709 t1 where t1.col1 > 1113867879);
Empty set, 1 warning (0.02 sec)

mysql> select tidb_version();
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v6.7.0-alpha-138-g61ed093fe1
Edition: Community
Git Commit Hash: 61ed093fe1f47911fcb2b1a8ab1f33be0cc5aaca
Git Branch: HEAD
UTC Build Time: 2023-03-02 03:15:44
GoVersion: go1.19
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

[fzzf678]

mysql> source /Users/fanzhou/Downloads/test.txt;

mysql> select col1, col2 from UK_HP16709 where col1 > (select max(col1) from UK_HP16709 t1 where t1.col1 > 1113867879);
Empty set, 1 warning (0.02 sec)

mysql> select tidb_version();
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                   |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v6.7.0-alpha-156-g3e5518d73a
Edition: Community
Git Commit Hash: 3e5518d73ae0aceb0dd6226f12697c736b99c8a4
Git Branch: master
UTC Build Time: 2023-03-02 03:23:19
GoVersion: go1.19
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[fzzf678]

It seems can't be reproduced on 61ed093fe1f47911fcb2b1a8ab1f33be0cc5aaca and latest master

[ChenPeng2013]

@winoros PTAL