Subqueries with ANY

[ChenPeng2013]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

use test;
DROP TABLE IF EXISTS `NT_HP27214`;
CREATE TABLE `NT_HP27214` (
  `COL1` bigint(28) unsigned DEFAULT NULL,
  `COL3` bigint(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

INSERT INTO `NT_HP27214` VALUES (6220562886261696648,8261016829378031712),(10088102545798736221,-1558721468091366150),(10401222536632439594,5721131584059512056),(11018384144747912258,-3057315319739652875),(4015976195844691698,8606385755820910945),(11176460915885983346,5887199438506050947),(9577741293842179700,-6270227925317661122),(16349679579622283077,-1988896798215189834),(6154979418745691333,-5209699437426123125),(5821827058326609142,4347727649905629786),(15434470546971793283,1874804858485671306),(7728607038602378684,44026710798329268),(5655367855514909860,74720407031939842),(17260144938906176082,-965691670643172598),(5420944291171243500,-3848656606908539668),(13256194665459014102,-8504853291814834455),(NULL,5124435464385242274),(16665272528599597178,-5071622193705928807),(5976396280710886723,1939243348171331792),(10495850831604220768,6030267970709134938),(4400568414980573598,9219234636442765303),(3662727265758252507,7716459581632374019),(7256296369035949382,-3834209611695136301),(2147382816496430986,139736485019661245),(2884146628035020681,3515310243600218575),(9422590822561053822,5039158359109568008),(13210564327318254779,-2173822270105558820),(12220498992575919692,4055869888152035467),(13018474875661771259,877243009019298550),(6453916978143348035,1066613911297970909),(15342564674360212479,-7761278088581948525),(4692857100769427393,-1692625896162955324),(6153644930770128169,1921215264679562370),(8944586759188048199,8251291321519461088),(13959767471910862655,1435780090122296812),(8568811203135280378,-3250649594535282474),(11698469189749886562,2913427245451653540),(5228464281096099199,-4943082233404768184),(17058545765526361582,-7301623733007385259),(8891695369511058343,-5187008302133250980),(1224526695017275448,-175212547323031695),(5957760426062089599,494475545853594284),(4105999306532429326,633427416380883488),(7053653476121383649,-3760507788572420585),(15018267507185944691,-6157903872938952782),(3844922660743015880,-3110718933485404346),(12751193755990848466,-852077609463051894),(15113497514113617034,-2180761512619372077),(6203804416288606140,1931685332309616041),(17796853788263032853,-4937461481697212778),(14907369666560861255,280814649587539320),(7694355628809647849,6930703852477447506),(951094775002229167,6003224010773148454),(12709042357433446794,-871221670273830469),(8266581451756387080,8099316213749080115),(5250226292030724334,6403859064823568612),(6837971591036767316,-5379212789282147821),(9214702860477873685,2813797399193388491),(5240621597212679306,2801969629165167704),(9483758573142948309,1484500643622667962),(15274141373631362730,-4709626085595505542),(6712170741819891443,5671407670367644330),(4710651488396572015,8335766099138079503),(11270792907077809362,3187374616507068981),(6027509065973572164,-5186300264971681271),(18446744073709551615,-63741608666336622),(3269927106576652896,3253298271868150536),(15287426335551662010,-6445649107127159883),(8908345154180095149,-3687734118117227455),(11766617684687573821,594038904205215631);

SELECT COL1 FROM NT_HP27214 WHERE COL1<=ANY (SELECT COL1 FROM NT_HP27214) AND COL1 =18446744073709551615;

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

Mysql 8.0.21

mysql> SELECT COL1 FROM NT_HP27214 WHERE COL1<=ANY (SELECT COL1 FROM NT_HP27214) AND COL1 =18446744073709551615;
+----------------------+
| COL1                 |
+----------------------+
| 18446744073709551615 |
+----------------------+
1 row in set (0.00 sec)

3. What did you see instead (Required)

mysql> SELECT COL1 FROM NT_HP27214 WHERE COL1<=ANY (SELECT COL1 FROM NT_HP27214) AND COL1 =18446744073709551615;
Empty set (0.00 sec)

4. What is your TiDB version? (Required)

Release Version: v4.0.14
Edition: Community
Git Commit Hash: e4a757382f976ecbdc6a5a5658c2b670032f1e71
Git Branch: heads/refs/tags/v4.0.14
UTC Build Time: 2021-07-17 05:39:29
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[ChenPeng2013]

It can be reproduced using tikv store

[guo-shaoge]

Same with #24205 , not cherry pick to 4.0 for now.

[guo-shaoge]

master and 5.1 is already fixed. Cherry pik is on the way.

cherry pick to release-5.0 in PR copr-10617
cherry pick to release-4.0 in PR copr-10616

[guo-shaoge]

Dup with #24205 , fix will be in next release.