search

pingcap / tidb

TiDB - the open-source, cloud-native, distributed SQL database designed for modern applications.
https://pingcap.com
Apache License 2.0
37.23k stars 5.84k forks source link

incorrect result when inlining CTE #52833

Closed wjhuang2016 closed 5 months ago

wjhuang2016 commented 6 months ago

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE `tlc89ef35d` (
  `col_1` time DEFAULT '22:24:08',
  `col_2` smallint(5) unsigned NOT NULL,
  `col_3` char(158) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'wz~r0)mt@f',
  `col_4` char(28) NOT NULL DEFAULT 'cMgQsbZxGAaX',
  `col_5` json NOT NULL,
  `col_6` json NOT NULL,
  `col_7` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
PARTITION BY HASH (`col_2`) PARTITIONS 2;

INSERT INTO `tlc89ef35d` VALUES('05:47:41',48746,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('05:47:41',53941,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('05:47:41',1,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('05:47:41',2,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('05:47:41',35889,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('05:47:41',25158,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('05:47:41',39276,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('05:47:41',20993,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('05:47:41',58812,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('05:47:41',37437,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('05:47:41',52224,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('05:47:41',5068,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('05:47:41',49558,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('05:47:41',64634,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('05:47:41',16860,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('05:47:41',47417,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('05:47:41',4619,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('05:47:41',62170,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('05:47:41',15313,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('05:47:41',36333,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('05:47:41',16584,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('05:47:41',10508,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('05:47:41',29347,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('05:47:41',52000,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('05:47:41',11165,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('05:47:41',49255,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('05:47:41',4581,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('05:47:41',65449,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('05:47:41',10247,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('05:47:41',2940,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('05:47:41',28219,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('05:47:41',53622,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('05:47:41',38513,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('05:47:41',52028,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('05:47:41',17353,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('05:47:41',47480,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('05:47:41',30756,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('05:47:41',50951,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('05:47:41',11223,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('05:47:41',1573,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('05:47:41',6504,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('05:47:41',30672,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('05:47:41',56387,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('05:47:41',10752,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('05:47:41',32768,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('05:47:41',32412,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('05:47:41',18931,'HO','NMif2maWu(*Ci','[7938298399125956007, 8449685406105545797, 3475937164642687029, 4874669102630334981]','[2235502051599541264, 7361489039212308185, 6339088275797798643, 6728339872868852081]','2008-11-29'),('03:31:26',15078,'HO','dmW+&lJWer~Y8','[7631536530048858460, 2447408996910953970, 8943832578617809241, 2154678831386229953]','[5896121677032407783]','2008-11-29'),('01:08:54',40160,'HO','W%Gw4mKei3*ppcEEWjl','[5241756450442746286, 7206393906974794605, 7894094344860764774, 6112161563565985209]','[4325484332355980346, 5034801072610414690]','2008-11-29'),('12:49:19',33593,'HO','0','[3352469883372187375, 8853866282331607726, 9223372036854775807, 8198066988856412038]','[8512770485957344572]','2008-11-29'),('06:06:11',38023,'HO','$','[3133625036128230571, 361666722406834007, 6530649254895493136, 2853140308479809533, 6651262452155231315]','[7394913855320458856]','2008-11-29'),('02:28:28',42434,'HO','','[3095228517453130583, -1, 2]','[8661121465491711123, 9019798482867084152]','2008-11-29'),('07:23:21',3082,'HO','2)','[7306397657600613099, 98445468279080753]','[1, 5711552680132777083]','2008-11-29'),('14:32:12',13560,'HO','PCGU+#TfVM3+T4UAO','[2994725572380570439, 7075161348656047071]','[3355346949896983321, 2511493390926629325, 7214265054277437976, 6839895808798187658]','2008-11-29'),('13:55:32',63985,'HO','COXo#$%W+P^g','[818196473703585723, 1930975683152815480, 0]','[1300555758410035914]','2008-11-29'),('19:44:36',26535,'HO','ioEId','[6829667012506734104, 3518091975769241507, 7471858653827534500, 2253947145258350953]','[3807865033895467222, 6260115725132760387, 7819075070344264402, 840145162988709631]','2008-11-29'),('19:03:33',60205,'HO','n&@','[-9223372036854775807, 7008783949727477348, 3088291146275004650, 2363943731581558520, 3118331333404637320]','[8749981290855797338]','2008-11-29'),('04:31:19',62848,'HO','Dj','[4787237983308334338, 651186070940733486]','[6062765677113252957, 7225807707337056237, 6090566650851631105, 6294750637367545930, -9223372036854775808]','2008-11-29'),('16:51:03',61793,'HO','oSci','[9084573740681249499, 1969019939214228220, 1351514261886452320]','[8424396731046463015, 8653369842327996107, 4063183313707048625, 3538533095178761891, 1768891827739109499]','2008-11-29'),('12:59:52',50177,'HO','v(q','[2048963507800274880]','[5540842055947001681, 6876234816655427412]','2008-11-29'),('11:17:50',14707,'HO','c8cJ6dsKb#bZeRP','[-9223372036854775808]','[642233458138955903]','2008-11-29'),('18:41:13',8492,'HO','qlFBPjQrp5CDpsbOX','[2086490620297694640, 4128436282709095810, 99377714888810523]','[5637329485726792242, 8962735197814524893]','2008-11-29'),('15:18:17',42714,'HO','','[7006625245128346455]','[5979590096327257817, 2131537808895691749, 5035475402064234092, 2192506341234434029, 699412212296294117]','2008-11-29'),(NULL,11893,'HO','un^newP-9ybGc3','[3183027588008010384, 7457784875501225884, 2079323129209168454]','[6636305147462565880, 7507474577833753823, 6746559746874012867, 220444984878429288]','2008-11-29'),('23:11:28',11068,'HO','dcM8hv%kF=1ox5UFpL1','[7857377317126414974, 834084683573835762, 4713218364411162165, 3920036294415119414, 5385357443718325327]','[1544872574371790830, 8092810762698041775, 1]','2008-11-29'),('18:04:42',0,'HO','^Y','[9181924286828020599]','[7839133469113605456, 8093210022976733524, 6456223824167857455, 7848011539815904843, 6656419110782332878]','2008-11-29'),('15:07:35',11385,'HO','J-%(l','[5897088796598384458]','[2278382444280533798, 416361579355563273]','2008-11-29'),('16:19:28',37784,'HO','w*sgVUXI@H&h3','[5183660617940081211, 5657112126379254086, 1128760714956200356]','[1, 8435406652721016253]','2008-11-29'),('19:46:46',50733,'HO',')7%T2EWZrC4Niib','[654939210058735073, 5786101473646435511, 4352709083967113382, 6153465202512562292, 599445748208691200]','[9165515063547447576, 2927063742223886087]','2008-11-29'),('18:05:51',43245,'HO','+MBH)','[9020513457518223490]','[8551544626631935326, 8444424545184299452, 1320457835758520336, 3749261667741792527]','2008-11-29'),('23:26:08',58652,'HO','s=ohFIfFQdM','[822627602856324889]','[8257772676285110537, 2054686965697946615, 504531440928004091]','2008-11-29'),('18:06:25',3085,'HO','K-mT5','[3829519189444822284, 3324975725052770855, 4016531543302469734, 3729644818269201226]','[6996816679883108360, 8784731920883371785, 5558847369623642052, 1192549035817230907]','2008-11-29'),('21:04:12',40321,'HO','V8Xm#A95vIRKZ','[6360133965808935871, 2, 11047348856002191, 7908056005576863582]','[1090035091495112986]','2008-11-29'),('03:37:40',48531,'HO','WmF','[680875334230169589, 5298150127623650558, 6668477328065348312, 6471347087251165781, 7630230267814337199]','[4165633274508368607, 2530769161619033104]','2008-11-29'),('12:51:52',21759,'HO','-(','[2254364158615577563, 2511054085338533210, 4834672345054385436, 4830103055945633523, 8806466741164605339]','[7605468473401885742, 9165613264915053143, 8995928426733741939]','2008-11-29'),('15:23:06',12265,'HO','_dBOk(8*O(','[3796390869608700284, -1, 2103060027552607908, 4781948134858662930]','[266342025967340522, 614215822182885495, 7249810396529172459]','2008-11-29'),('17:41:42',18851,'HO',')qeSo1QYstk','[7861943325350572780]','[709402542122486161, 2699492812012774325, -1]','2008-11-29'),('12:00:34',16367,'HO','Y3+WEg8Vt%cWbR0#b','[364950869839119998, 7144807071025396783, 2208101728017322912, 233021013360378545]','[4508910634243136164, 2, 268474274685969209]','2008-11-29'),('23:33:40',10098,'HO','6jiEKLMZzds','[760162702057477577, 0]','[2830317805355506126, 8757850628713131679, 4524303712344378132, 553706705068596980]','2008-11-29'),('13:33:19',54759,'HO','_w$o~wDikaffKkGr','[1444100590465389920, 2046861803302734533, 7929822056281757167, 8696367997093837875, 1086857680391562478]','[-9223372036854775807]','2008-11-29'),(NULL,37518,'HO','5ZwkUbnHnA','[3183027588008010384, 7457784875501225884, 2079323129209168454]','[4589850175172424491, 479553672210442973, 5433748604416445352]','2008-11-29'),('23:10:18',12700,'HO','+GNpAF3','[-1, 1, 9136831708740159602, 4652213360615489015]','[6658253506139036593, 7919948317414872941, 2, 278793884308470741, 8526308087671528454]','2008-11-29'),('23:53:23',25884,'HO','p5)mV$cA3wsC2_','[1435397719189651308, 6013395997285748752, 419464990388383846, 380812904869350707, 4794511337334113611]','[8082007425290420570, 7357298619359167052, 9166579427644884801, 6978153995536944968]','2008-11-29'),('17:46:36',3221,'HO','hE6YW~YnH37','[8705651792977631540, 9223372036854775807, 2432921605660706224, 7236417211194522650, 6795066514096100660]','[1812035309280850854]','2008-11-29'),('01:16:31',47920,'HO','^CkJSwVmL$iLPa','[2707372922589772371]','[2856488353684614606, 4208980605252327961, 3155419365678291348]','2008-11-29'),('05:44:41',59269,'HO','6m(L)-Q9k#eZ0toKD','[5352632105576670292, -9223372036854775808, 324316012099562545, 832366300977414822]','[2737277697646620928, 6268665969912746474]','2008-11-29'),('17:29:01',5374,'HO','M6wbGK6pUW','[9223372036854775807, 5742372952040746415, 3742103343101299174, 8725927374442578339]','[0, 820075284571708571]','2008-11-29'),('15:50:59',19290,'HO','LqwgeqTm','[2879350566181030757, 2]','[8002994484335573501, 1861885287351231276, 8429604065959004284]','2008-11-29'),('00:55:05',33764,'HO','TE&^AgF0YqtaI','[5673593247014179457, 7788113479629122477, 6961238574628422438, 8831601285103860202]','[7568524469895493043, 2376647793471459424, 2604165550347765405, 2269064906985037376, 2554381431063234030]','2008-11-29'),('22:42:37',51984,'HO','E__M^$_','[5576745697974060384, -9223372036854775808, -9223372036854775808]','[6345264113206938521, 687006753385538524]','2008-11-29'),(NULL,33712,'HO','TW~U15pCoP','[3183027588008010384, 7457784875501225884, 2079323129209168454]','[2596050408211944775, 5664770110992419641, 4850314690529293907, 4249741826283613336, 7432775533291436074]','2008-11-29'),('02:20:13',64217,'HO','Ev','[2]','[2731276338142290358, -9223372036854775808]','2008-11-29'),('05:12:19',24802,'HO','M','[2822714406117003146, 1532922831528175935]','[5996470562169773621, 8458241061783453662, 7115642962943033936]','2008-11-29'),('11:05:27',32767,'HO','Le$Oo','[6162135430193681088, 0]','[-1, 1744791307095302166, 5875423612726330710, 5963163358007269512]','2008-11-29'),('23:11:52',47964,'HO','l+(5_9hRpw#L','[1888714915969677568, 5534850202933264122, 147383401206775271, 1, 157259073710504683]','[3175329925531182348]','2008-11-29'),('02:59:07',62650,'HO','zzgq6M4&t(_','[327514665577924441]','[-9223372036854775807, 1, 9223372036854775807, 7940050340439169354]','2008-11-29'),('17:17:35',9881,'HO','3tAL2s','[620375762665027653, 2431490277537745174, 7077192118755746334, 4777693079796659391, 9194949556692965238]','[2068907004994433119, 8620755303170092218, 3863583865223223030, 3249497343323642964, 8011593359088757940]','2008-11-29'),('11:25:23',49915,'HO','lPtTzd8R_a1tstc-ktj','[7767598785450197181, 236290149170328172, 3432159889592072725, 5031544190554611950]','[6033283154481786572]','2008-11-29'),('16:53:37',0,'HO','Z)Ve','[2, 8402589894759676770, 7361136672701715205]','[8961996049114378376]','2008-11-29'),('08:10:31',47799,'HO','d3C)SW#J9U9','[5081495242598540115, 2073515088130626169, 6076905314703883009]','[3632977084215835961, 4389551985684800515, 5390183189133065745, 1914112455326982247, 0]','2008-11-29'),('09:20:08',39647,'HO','U-Zq-o+riGBlhW','[9223372036854775807]','[0, -9223372036854775808]','2008-11-29'),('10:54:53',45184,'HO','EhcTrn_3v','[7340839982476120303]','[219600989525639543]','2008-11-29'),('22:48:35',10282,'HO','rW+J9Q$6','[5283201181604501305, 3584535191134294987, 1057415343866608544]','[822362562580359285]','2008-11-29'),('12:56:17',62414,'HO','wqOCN','[5760874100708430670, 6194050410480902176, 3069559203110673323, 4388675891667564487, 6891165050243492478]','[2443049029247782288]','2008-11-29'),('01:03:29',32048,'HO','^UqH','[327131973057198366, 8623282751307053016, 9120141481369999979]','[7054880025310809180, 9223372036854775807, -9223372036854775807, 5509805977389941985, 5357992223752844944]','2008-11-29'),('07:58:47',55211,'HO','b(agR','[2563797486163052910, 8743604342587337042]','[9104160236119691432, 5207626796692511124, 691408732176208142]','2008-11-29'),('00:31:24',664,'HO','7h$N','[4657471299285537560, 227430163447487486]','[4476920559969240618, 1150294942407800277, 3263659909172769672, 3179336810407904651]','2008-11-29'),('07:16:47',1,'HO','W14Z','[0]','[4865421873539990882]','2008-11-29'),('09:44:11',25082,'HO','0tlIvS9Wy3S2T*P@JK','[2677860316839211330, 1825721210349010726, 1668135143955894284, 5647777172111293067]','[7519485017081780700, 8743128585387235434, 0, -9223372036854775807, 530631316318514656]','2008-11-29'),('23:40:39',47344,'HO','v','[5933037796053839769, 1420514198800511535, 2383828649366514265]','[9223372036854775807, 7747493650338085641, 9122757634014055868]','2008-11-29'),('23:06:46',56723,'HO','6oZ=W4mF2)DU~!-VJR','[1343747033224300994, 4464153071014753389]','[1326767004881132099, 7483239170985954718]','2008-11-29'),('05:26:43',42927,'HO','-IHM@%4lNflEw3kq','[9223372036854775807]','[2777930574226903399, 8309331758924782830]','2008-11-29'),('17:13:42',46868,'HO','hW','[4452832379762822211, 3314886902917875940]','[6755503827449002754, 8915424865632788159, 0]','2008-11-29');

CREATE TABLE `tld69db889` (
  `col_1` time DEFAULT '22:24:08',
  `col_2` smallint(5) unsigned NOT NULL,
  `col_3` char(158) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'wz~r0)mt@f',
  `col_4` char(28) NOT NULL DEFAULT 'cMgQsbZxGAaX',
  `col_5` json NOT NULL,
  `col_6` json NOT NULL,
  `col_7` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
PARTITION BY HASH (`col_2`) PARTITIONS 2;

INSERT INTO `tld69db889` VALUES('17:05:47',52239,'WsvJu','hN4w@8U!-KBib9h4','[6716931770637198666, 3764775712957068867, 1085916133861130565, -9223372036854775808]','[1898389581483233206, 29061769006552941, 5974250709958693093]','1992-05-25'),('13:48:14',52239,'WsvJu','uwQ0XQLFk$yXScyHM(a','[6716931770637198666, 3764775712957068867, 1085916133861130565, -9223372036854775808]','[1898389581483233206, 29061769006552941, 5974250709958693093]','1992-05-25'),('13:05:07',52239,'WsvJu','~@uOb1wSi+jaIw','[6716931770637198666, 3764775712957068867, 1085916133861130565, -9223372036854775808]','[1898389581483233206, 29061769006552941, 5974250709958693093]','1992-05-25'),('13:24:50',52239,'WsvJu','m99','[6716931770637198666, 3764775712957068867, 1085916133861130565, -9223372036854775808]','[1898389581483233206, 29061769006552941, 5974250709958693093]','1992-05-25'),('23:36:12',52239,'WsvJu','lQ2Vuw-','[6716931770637198666, 3764775712957068867, 1085916133861130565, -9223372036854775808]','[1898389581483233206, 29061769006552941, 5974250709958693093]','1992-05-25'),('20:51:52',52239,'WsvJu','XdMgfh%%N','[6716931770637198666, 3764775712957068867, 1085916133861130565, -9223372036854775808]','[1898389581483233206, 29061769006552941, 5974250709958693093]','1992-05-25'),('12:22:39',52239,'WsvJu','pQKVcuEaG&','[6716931770637198666, 3764775712957068867, 1085916133861130565, -9223372036854775808]','[1898389581483233206, 29061769006552941, 5974250709958693093]','1992-05-25'),('22:05:12',52239,'WsvJu','kUOu','[6716931770637198666, 3764775712957068867, 1085916133861130565, -9223372036854775808]','[1898389581483233206, 29061769006552941, 5974250709958693093]','1992-05-25'),('12:33:19',52239,'WsvJu','Qb6N-ftKpb%','[6716931770637198666, 3764775712957068867, 1085916133861130565, -9223372036854775808]','[1898389581483233206, 29061769006552941, 5974250709958693093]','1992-05-25'),('14:22:56',52239,'WsvJu','s(G9vA','[6716931770637198666, 3764775712957068867, 1085916133861130565, -9223372036854775808]','[1898389581483233206, 29061769006552941, 5974250709958693093]','1992-05-25'),('15:11:40',52239,'WsvJu','ni6@%QZ','[6716931770637198666, 3764775712957068867, 1085916133861130565, -9223372036854775808]','[1898389581483233206, 29061769006552941, 5974250709958693093]','1992-05-25'),('17:42:22',52239,'WsvJu','Y','[6716931770637198666, 3764775712957068867, 1085916133861130565, -9223372036854775808]','[1898389581483233206, 29061769006552941, 5974250709958693093]','1992-05-25'),('03:54:43',52239,'WsvJu','uBzxGqK=#prjguNK','[6716931770637198666, 3764775712957068867, 1085916133861130565, -9223372036854775808]','[1898389581483233206, 29061769006552941, 5974250709958693093]','1977-10-19'),('15:24:03',52239,'WsvJu','s','[6716931770637198666, 3764775712957068867, 1085916133861130565, -9223372036854775808]','[1898389581483233206, 29061769006552941, 5974250709958693093]','1995-07-21'),('20:38:19',52239,'WsvJu','kdKFf+@zfCraap~c','[6716931770637198666, 3764775712957068867, 1085916133861130565, -9223372036854775808]','[1898389581483233206, 29061769006552941, 5974250709958693093]','1990-12-28'),('21:18:24',52239,'WsvJu','uZS&9','[6716931770637198666, 3764775712957068867, 1085916133861130565, -9223372036854775808]','[1898389581483233206, 29061769006552941, 5974250709958693093]','1976-07-24'),('16:03:41',52239,'WsvJu','jfz$','[6716931770637198666, 3764775712957068867, 1085916133861130565, -9223372036854775808]','[1898389581483233206, 29061769006552941, 5974250709958693093]','2002-10-24'),('04:28:56',52239,'WsvJu','u@&~Z3l','[6716931770637198666, 3764775712957068867, 1085916133861130565, -9223372036854775808]','[1898389581483233206, 29061769006552941, 5974250709958693093]','1976-04-07'),('14:48:39',52239,'WsvJu','T5HaC7CWEEHoR','[6716931770637198666, 3764775712957068867, 1085916133861130565, -9223372036854775808]','[1898389581483233206, 29061769006552941, 5974250709958693093]','1987-07-28'),('20:26:52',21163,'W%&Gs9^OnuazgJ8','cC~hgA','[976219141000081179, 7030935426554459847, 8028825295749938745, 5520717887167756779]','[1898389581483233206, 29061769006552941, 5974250709958693093]','1984-08-22'),('02:32:13',64173,'~&3BqOA**','','[8610200082021557309, 6934243861003703804]','[6426649845465703980, 245718560022633470, 4574444432535324719, 3005599334057205716, 4914949919930500371]','1983-06-25'),('17:20:55',61110,'+eF0kYG~gX','vw7OqaIFWPT','[3696687755255023817, 4137103387096847299]','[6851453881975699240, 7187160845607651020]','1998-10-07'),('11:20:12',34393,'_erq','l!wd+dGbB','[2]','[473730036984526226, 16912477768222325, 6156615611525819683, 1364371112805643284]','2006-11-16'),('10:11:15',54289,'H9DM^','gQtiY#o&^es0wLca','[494943810346052764]','[7225391870383535349, 2468471868722159273, 3038915580546585061, 5637704842761257960, 25556149342141813]','1979-08-11'),('04:03:09',26746,'','l*mEGt%xe$1Y^CmA5','[4166871211291257932]','[1639878351059027787, 5309245802190346073, -9223372036854775808]','1977-08-21'),('01:53:59',21010,'Ne','mv','[3512260411185616302, 2720277508731036862]','[2955864091507191226]','2000-08-27'),('08:46:23',27471,NULL,'R','[6759813578954209373, 3949710457744040708, 4899742938138600420]','[2897111566504207156, 5267438976725492791]','1985-12-07'),('14:05:52',35015,'fY','V*','[4054043559147291198, 5834319437469303211, 7368310599850184861]','[2666647694536583744, 1087189239456282949, 7798640713252465355, 2889670484917416764, 8244054230338600069]','1975-02-27'),('11:06:51',26746,'','','[1]','[1639878351059027787, 5309245802190346073, -9223372036854775808]','1990-06-23'),('11:29:42',47464,'Cr_ohfmJXBb~MmP8','tzQGUwIj2Mxoz','[2781058529471131596, 7982639273977259279]','[5625803094634435618, 172688475383573418, 5936402686821956829, 2614936008992369578, 8023274809687215645]','1984-09-13'),('11:18:23',26746,'q^=','gx1c-LAR=J','[1769650183049923497]','[1639878351059027787, 5309245802190346073, -9223372036854775808]','1997-09-08'),('18:57:57',26746,'XJu~e','4drq2Y)m%*OhFG1q#','[5762706312958446715]','[1639878351059027787, 5309245802190346073, -9223372036854775808]','2010-04-29'),('21:46:21',56421,'~zgTh&d#Aj','2+60','[3675348027479906908, 865511762685048381, -9223372036854775808, 2961452850664281573, 992564209575198030]','[2114460049378750574, 2, 2510047927640405143]','2027-08-18'),('07:56:26',48084,'1JIbKdjI#-UL-+b_O','$3-GsnWja&KKjH_zI(','[0, 618122533160700844, 7811589188646569338]','[8998690542626351470, 7960698157847043262, 2]','1998-06-20'),('09:41:08',56751,'i)','e~W*VE!+','[4551351942342002236, -1, 4308959719686442138, 820570109720344963]','[6765336233177517097, 4928383124015470627]','1991-09-20'),('17:41:08',30525,'ZoC+~nQi','','[9178204632199762935, 8364096625009183686, 2293286555054903449, 6923268641153610588]','[8912872172624105098]','2019-10-11'),('18:23:51',41371,'jEVB9+_V6!8Ug','Gkr','[3428262722851580562, 9082227033427333403, 2642415988323595047]','[8031433663547670644]','1998-06-01'),('19:19:55',57775,NULL,'V^h$gt)P~SZ5','[-9223372036854775808, 5500276404146196387]','[5816517325601115404, 5996670357393708081, -9223372036854775808, 8100136166105865296, 4799032180465503721]','1976-06-11'),('01:46:31',49792,'(aATMQn8gXE)#DS1E','djn','[8582723836774987608, 8414058781809645370]','[9221436163757719922, 9223372036854775807]','2005-04-11'),('11:18:30',31580,'H#QjGi+oxo!+*BbKSQ','V5l&bTWDONss219Hfu6','[2113268181198428509, 7471191004302601959, 2, 1546396070587078113, 6870907707373548961]','[7600960007662295725, 4993571938424145292, 7331930833314029027, 8770975994209260314]','1992-01-20'),('11:19:11',27862,'^Wz0GG~L#F+4S&a','JCJ7L*Mq-i$0mx*oRu','[6859676917936258918, 4936929032345147257, 1673003224029876387, 946063488990048391, 6843805014838483162]','[8320656977981734406, -9223372036854775808, 5733607826376169655, 3333572929033730776, 619972781048933943]','1993-10-09'),('01:29:38',51635,'!GyDexK4#9cXU9','HmyO^K&hiis','[0, -9223372036854775807, 9141549379291346483, 2859683872081924788]','[152249513753665442, 3984384992536485711, 8022167017482573980, 110399125985751786]','1986-01-16'),('15:57:18',26746,'ZZodj0MAN^-k1Ld','C0Y*@b!kSNGwk','[6622309172667276733, 8882064765768148986]','[1639878351059027787, 5309245802190346073, -9223372036854775808]','1990-01-19'),('06:03:59',54863,'hXtv6pSG=x)4#hdE9i','um','[5060496579132353233, 6351068192921656361, -1]','[6310970271265939927, 2624176201434047688, 6278617627367365184]','2024-12-03'),('01:28:53',26746,'e','KV$I0jA-zNeLXg2','[8414314966969683063, 5845686071943953413, 8764879833514345328]','[1639878351059027787, 5309245802190346073, -9223372036854775808]','1975-05-05'),('05:44:10',26746,'NFcrA89','','[3660721669775660547]','[1639878351059027787, 5309245802190346073, -9223372036854775808]','2022-09-14'),('22:35:04',7859,'emwzOIlSFn@','19dma^','[8540610772783225861, 8513495915213699313]','[8461594620415971141]','1979-05-06');

WITH `cte_3600` (`col_10746`, `col_10747`) AS (SELECT EXPORT_SET(3, `tld69db889`.`col_5`, `tld69db889`.`col_7`, '-', 8) AS
`r0`,`tld69db889`.`col_7` AS `r1` FROM `tlc89ef35d` JOIN `tld69db889` ON `tlc89ef35d`.`col_5`=`tld69db889`.`col_5`  ) (SELECT 1 FROM `cte_3600` WHERE `cte_3600`.`col_10746` BETWEEN 'Wz%vodzG3' AND 'm*RzhE#Kif(H');

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

MySQL:

mysql> WITH `cte_3600` (`col_10746`, `col_10747`) AS (SELECT EXPORT_SET(3, `tld69db889`.`col_5`, `tld69db889`.`col_7`, '-', 8) AS
    -> `r0`,`tld69db889`.`col_7` AS `r1` FROM `tlc89ef35d` JOIN `tld69db889` ON `tlc89ef35d`.`col_5`=`tld69db889`.`col_5`  ) (SELECT 1 FROM `cte_3600` WHERE `cte_3600`.`col_10746` BETWEEN 'Wz%vodzG3' AND 'm*RzhE#Kif(H');
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

3. What did you see instead (Required)

TiDB

mysql>  WITH `cte_3600` (`col_10746`, `col_10747`) AS (SELECT EXPORT_SET(3, `tld69db889`.`col_5`, `tld69db889`.`col_7`, '-', 8) AS
`r0`,`tld69db889`.`col_7` AS `r1` FROM `tlc89ef35d` JOIN `tld69db889` ON `tlc89ef35d`.`col_5`=`tld69db889`.`col_5`  ) (SELECT 1 FROM `cte_3600` WHERE `cte_3600`.`col_10746` BETWEEN 'Wz%vodzG3' AND 'm*RzhE#Kif(H');
Empty set (0.00 sec)

If disabled inlining CTE by referring cte_3600 one more time.

mysql>  WITH `cte_3600` (`col_10746`, `col_10747`) AS (SELECT EXPORT_SET(3, `tld69db889`.`col_5`, `tld69db889`.`col_7`, '-', 8) AS
`r0`,`tld69db889`.`col_7` AS `r1` FROM `tlc89ef35d` JOIN `tld69db889` ON `tlc89ef35d`.`col_5`=`tld69db889`.`col_5`  ) (SELECT 1 FROM `cte_3600`, cte_3600 t2 WHERE `cte_3600`.`col_10746` BETWEEN 'Wz%vodzG3' AND 'm*RzhE#Kif(H');
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.01 sec)

4. What is your TiDB version? (Required)

cfbabfa705dbbc97c8e1e00af7f2d2620406304c

YangKeao commented 6 months ago

A smaller reproduce:

CREATE TABLE
  `t1` (
      `col_5` json NOT NULL,
      `col_7` date DEFAULT NULL
  ) ENGINE = InnoDB DEFAULT CHARSET = utf8 COLLATE = utf8_bin;

INSERT INTO
  `t1`
VALUES
  (
    '[2]',
    '2008-11-29'
  );

CREATE TABLE
  `t2` (
    `col_5` json NOT NULL,
    `col_7` date DEFAULT NULL
  ) ENGINE = InnoDB DEFAULT CHARSET = utf8 COLLATE = utf8_bin;

INSERT INTO
  `t2`
VALUES
  (
    '[2]',
    '2006-11-16'
  );

WITH
  `cte_3600` (`col_10746`) AS (
    SELECT
      EXPORT_SET (
        3,
        `t2`.`col_5`,
        `t2`.`col_7`,
        '-',
        8
      ) AS `r0`
    FROM
      `t1`
      JOIN `t2` ON `t1`.`col_5` = `t2`.`col_5`
  ) (
    SELECT
      1
    FROM
      `cte_3600`
    WHERE
      `cte_3600`.`col_10746` BETWEEN 'Wz%vodzG3' AND 'm*RzhE#Kif(H'
  );

I think the issue is that the internal expression 

SELECT
      EXPORT_SET (
        3,
        `t2`.`col_5`,
        `t2`.`col_7`,
        '-',
        8
      ) AS `r0`
    FROM
      `t1`
      JOIN `t2` ON `t1`.`col_5` = `t2`.`col_5`

The r0 should be compared with others through binary collation;

select '[2]-[2]-2006-11-16-2006-11-16-2006-11-16-2006-11-16-2006-11-16-2006-11-16' > 'W';
select _binary '[2]-[2]-2006-11-16-2006-11-16-2006-11-16-2006-11-16-2006-11-16-2006-11-16' > 'W';
YangKeao commented 6 months ago

A smaller reproduce:

SELECT EXPORT_SET (3,cast('[]' as json),'2','-',8) BETWEEN 'W' AND 'm';

It gives 1 in MySQL, and it give 0 in TiDB. EXPORT_SET (in MySQL) returns LONG_BLOB in this case. It seems to be some magic in mysql type infer.

YangKeao commented 6 months ago

MySQL has a strange function to set the string type:

  /**
    Set the Item to be variable length string. Actual type is determined from
    maximum string size. Collation must have been set before calling function.

    @param max_l  Maximum number of characters in string
  */
  inline void set_data_type_string(uint32 max_l) {
    max_length = max_l * collation.collation->mbmaxlen;
    decimals = DECIMAL_NOT_SPECIFIED;
    if (max_length <= Field::MAX_VARCHAR_WIDTH)
      set_data_type(MYSQL_TYPE_VARCHAR);
    else if (max_length <= Field::MAX_MEDIUM_BLOB_WIDTH)
      set_data_type(MYSQL_TYPE_MEDIUM_BLOB);
    else
      set_data_type(MYSQL_TYPE_LONG_BLOB);
  }

The type of EXPORT_SET is determined by

bool Item_func_export_set::resolve_type(THD *thd) {
  if (param_type_is_default(thd, 0, 1, MYSQL_TYPE_LONGLONG)) return true;
  if (param_type_is_default(thd, 1, 4)) return true;
  if (param_type_is_default(thd, 4, 5, MYSQL_TYPE_LONGLONG)) return true;

  if (agg_arg_charsets_for_string_result(collation, args + 1,
                                         min(4U, arg_count) - 1))
    return true;

  const ulonglong length = max(args[1]->max_char_length(collation.collation),
                               args[2]->max_char_length(collation.collation));
  const ulonglong sep_length =
      (arg_count > 3 ? args[3]->max_char_length(collation.collation) : 1);

  set_data_type_string(length * 64U + sep_length * 63U);
  set_nullable(is_nullable() || max_length > thd->variables.max_allowed_packet);
  return false;
}

The problem is that, the args[1]->max_char_length(collation.collation) will always return Field::MAX_LONG_BLOB_WIDTH for json. See:

  /**
    Set the data type of the Item to be JSON.
  */
  void set_data_type_json() {
    set_data_type(MYSQL_TYPE_JSON);
    collation.set(&my_charset_utf8mb4_bin, DERIVATION_IMPLICIT);
    decimals = DECIMAL_NOT_SPECIFIED;
    max_length = Field::MAX_LONG_BLOB_WIDTH;
  }

As TiDB didn't have a unified MaxLength for each field type (and the cast function), it's not simple to have similar logic here.

YangKeao commented 6 months ago

Other incompatible cases:

select concat(cast('[]' as json), '[]') between 'W' AND 'm';

A lot of functions also have similar problem: concat, concat_ws, replace, insert, substr, soundex, format, elt, make_set, repeat, rpad, lpad, weight_string, hex, unhex, convert(...), export_set, quote, compress, date_format, lower, upper, reverse, coalesce....

YangKeao commented 5 months ago

https://github.com/pingcap/tidb/pull/53126 Fixed most of the situations. Not all functions mentioned above is included in this PR, because:

  1. Some functions have different behavior when the argument is const or not. For example, the rpad and lpad determines the max_length according to the second argument if it's constant. This PR didn't fix the issue for this case (and I'll try to fix it in the future).
  2. Some functions just return binary (like compress), which don't need fix.
  3. Some functions don't change the collation after experimenting on MySQL (and reading the MySQL source code). For example the hex/unhex didn't calculate the max_length according to its first argument when its first argument is not string.