the result is inconsistent before deleting some rows

[ChenPeng2013]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

DROP TABLE IF EXISTS `PK_RP9330`;
CREATE TABLE `PK_RP9330` (
  `COL1` tinyint NOT NULL DEFAULT '30' COMMENT 'NUMERIC PK',
  `COL2` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
  `COL4` datetime DEFAULT NULL,
  `COL3` bigint DEFAULT NULL,
  `COL5` float DEFAULT NULL,
  PRIMARY KEY (`COL1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
INSERT INTO `PK_RP9330` VALUES (-128,'绯抅駒嗁柖涄歌馂莇袖语辀斤関壧喧嶤綈橐旟','4766-11-20 19:11:07',-7179905303591732550,3.16937e38),(-125,'沒勇洑簁詢姅洩披饆澀糩戈癷温緅糩窺鍷袥莋','9742-10-31 08:10:00',4713020196780041247,-9.9061e36),(-119,'鼺畿拉龻糒晛硘訽措肢賐恺碚凖箉覿紵鋕煾丁','0225-07-04 06:57:14',-3684380231231368255,7.93555e37),(-114,'癬豴嫈羂劐鳙苆瀄燈莇嶏诂潍鋪祏簴葹敠碓罨','9149-01-08 12:12:04',861931487183620400,2.12364e38),(-113,'霽长莺俟驏哇潫涩嚓ƻ錭唋岷攩貏十隓泲窗延','9814-11-20 16:30:41',-4534329161580550041,-1.76228e38),(-103,'孀祝聑纐荇碙龄覺膂ᚮ夙難会鈭丿亾鋾Ǎ鑃苿','8267-10-26 21:27:41',-3997196061050791219,-3.93816e37),(-101,'瀰娝嚠哤喽饀蒗擈ɀ贑駲嶭跅諬狞噅懎礀秌貙','9697-12-16 17:16:21',9165647063629250736,-2.67716e37),(-99,'榕狵魤皱庎睘鼷c蚫軗闙壻侭肬鍦詉赃渂葍撽','0195-07-24 22:34:29',-8164492995576607204,-3.22801e38),(-98,'潃躉騏儠蚪艌臋飁滸洗驂狈懋厃跞喬鶌獂閰濮','2521-06-13 00:35:55',1363365385301763328,-1.68373e38),(-97,'觭贑鵭呥欛ř忣猼幔蝟癁霙眏咸娜滥碅嬭撘殽','4055-08-06 13:45:13',-6906257768261071532,9.87617e37),(-93,'ᚲ卭巑瑲鄓蹻譖圧喸谿僭尬鎴该寊劊歹瓆藊喺','2477-03-19 13:38:28',-8179420213199009019,-3.33211e38),(-86,'驷是檢僰笖颒瑿勶檒韷屾鄌箐挴毤璬磑饻癏阩','1630-10-08 07:56:50',8312134783720572306,-1.93246e38),(-82,'叵奿葨狓饀撋釢悔諦寸跚鸳拾礃崌械盐蜙鲐賉','8354-03-07 16:49:21',-7867157318415242728,8.46837e37),(-81,'鶄歗忶実Ş屠脱縢摓汄膁鰷傼岱赀撕瀽兵鏚螺','3232-04-17 19:27:32',5325870101524439380,9.43917e36),(-80,'鯕蕊臏鯀莁囙飑齒寘癸茹涏屹涃Ɗ鉿荩骋薃氍','8631-04-22 22:21:29',-4025133570262157365,3.18662e38),(-79,'?朹档廊熨鼄活ů銌黮鯉樴蒐窃z詢鳫灑殓骫','9939-07-25 01:07:17',-7655389302018848101,-1.77275e38),(-75,'菆卭舰哄怖糗芇介巚孓ç瑗宜凘巔赂聮鱞銸茅','9524-12-29 07:58:51',6864313916151275542,1.26694e38),(-73,'邖崂閭癙焑跎頋颡鋈鴆宮廼瞎滨昺榿歂忹熅煔','2875-01-01 21:50:20',2180301015604084726,3.50129e37),(-72,'蜺备靘偕蕗昍缡瞗滋臔洬龴稔咉锺讏迆纉魄襎','1258-08-14 01:31:57',-8046293391393142863,1.20903e36),(-70,'蟯阉屿恨滜蘨襺魂唨柇偀鳓玑鏞塖愪诿垇陾瞬','2243-11-29 17:51:16',-8388215878337995395,-2.10562e38),(-66,'椇腚蹊墀甌蠫隍窺馃齴徘龀弸郷糔圔櫝竊葄鎦','2856-08-04 13:00:28',-5139762612230496362,1.07485e38),(-65,'甛仈盁笉嫢扈¯眠鮤悥脟鋾侲嬐倩ᚿ簵朧垆錃','6264-05-20 07:59:05',-3638640754380301377,-2.90499e38),(-62,'翃剱魑誻晳绸镽堗垷饗覂倪鯕挑峬蝮分鱖朎準','5036-05-28 13:40:18',3521461794700953015,-3.29008e38),(-56,'垮臕ƽ圉Ƈ蜜乿艨焩ᛚ竬餀搔纋衷籑槗螹姕俩','7048-10-31 00:28:41',-2559535579231808116,1.16439e38),(-48,'蟹戔謫颎豶锍錪揔厯咍鱙晗竕鼗ᚹ榘閶麢皅枏','2787-06-18 00:42:54',2436107147634672879,1.1373e38),(-45,'蠫碘鑢鍂蟈墖笠葇郹爆蠑杢胺臩Dz韠躧斔鸖輡','6659-07-19 20:12:24',-974411310052692578,1.08584e38),(-44,'璱蝍ᛪ齍捍坾谭沟戤觕璉ⱥ妴瀼鮛预頹腩暛飩','2230-10-12 12:41:26',-586362899008859375,2.81e38),(-36,'钁镣牽箟麾躞堈隙冣忟芫钳郶框鱑薦蠔耤粬鶺','6159-03-14 01:21:33',-4205818794239977466,-4.21986e37),(-32,'戊辂秶銎鑃銒詹悴嫸襱葼鵠佰秌摈個獔熻郌裷','0574-05-14 00:37:57',-640633081266036343,-6.22308e37),(-31,'覣ᚼ涱卋腱轔髥禃蠔規夣拏稏出ᛈ鼲鲏蓕龝凅','0447-11-05 13:11:35',-8568835971627938889,2.73036e38),(-27,'龖弩跨限銓塪鳳ᛪ鮥穖蒮蔠鶀蒉哦薨閔匋羚伅','1878-07-09 22:25:10',-4141434676419573373,-1.25355e38),(-26,'鰹拤忳鐑聛燫薾濜髡趧郘邁榓汲杤蜜芮讞窣晓','0668-08-20 07:47:45',-4604375486937662733,-5.08067e37),(-24,'镵桀篆觲辍嗯鰴寗碚邱勅瑳鎽泆綌鴟農傏紭Ơ','4468-08-28 01:19:45',-7099185940864278626,1.8773e38),(-23,'錍犨栓賓胹萜姉擓贒纒寢凊仍樐拋杹孏煕美癶','9801-05-31 07:50:36',-1702340064630986654,-2.4802e38),(-21,'嶂鐒錙籄饳諃卤矏僱纘揻癬媸鄞鼁諸瓒兊欱匿','0739-12-10 04:45:20',2215312977871562541,2.24694e38),(-17,'隷辞咚郱侐禎癔攱牂餿撊鋊燘璎京胗灍熉螋嚋','8636-06-25 22:08:34',5032160997241304379,-1.07127e38),(-15,'矐拴鳼爫玻蘩纻盹徳瓵溉峬趫氿峷瀭鸔潻救堥','0269-12-27 08:55:42',2491921801428983123,3.3406e38),(-14,'霼会痶籒琑挭夏ļ孽圁崋塧蝤趵騳薬謽遦淨韃','3569-09-24 21:31:31',5648020132131281365,2.82114e38),(-13,'置猣巟溅篢呩啓觜鳠靋泟迁揺腈韾腐怬噈Đ莾','1393-02-22 11:22:28',-4066613659075128,-2.46966e38),(-3,'贑埨ᛛ熡默牾晉亻缕ƙ粼銳貀礤隽璄袊恍滹栽','2663-06-07 19:37:05',3261085044820910450,-8.59131e37),(0,'筭搻雗惙蕎驃册妑蚲滰鑨鎊盯廐霃諮珢潋馛蓗','9012-07-11 11:24:12',2603453717091342426,8.00689e37),(5,'傺愐崗伒骚谭鲒牒譛迸拆菚瑬螔昋級薾景塤險','5076-01-26 16:59:30',-630386338323663980,-2.30522e38),(7,'顧做鸇袚頠簔瘻咽嘍埐锫图耐欹槿躀媭怬罼抟','6731-08-21 16:17:32',-2953812600258002909,2.32757e38),(8,'ų耒肒粥睘梼急欕埶歘煳匡匂疇Ά簝鎢佡晐鷪','8337-08-25 08:10:29',-4077635286988363347,2.63514e38),(9,'鐈ⱬ衖戫Ǵ缡質喨榽荑汬籨桳芟亚嘧丑轰嶍蠴','9210-02-13 08:10:27',-5341050683274718546,-2.07356e37),(14,'樌搕螣鱩龃榌鐹脙儳冾鮦侲捓矐镌龼鲚矼魨珓','7851-05-10 16:43:38',-3151917958646853717,1.80779e37),(17,'蕙獅岒聱禟逈衏戃晣楁氆瑞洆另菔轔遂齅辻掺','4592-02-15 18:41:39',6995380957096520695,3.23997e38),(23,'镓芺Ŋ姊邖Í氣漡Ȓ溈Ñ滵銋颒橴蒾猄覹寐售','8704-10-07 21:53:46',349610077530363416,1.56881e38),(26,'蒿鐃鴍荵訯斿鮥亁卨眑騔Û胤穥椟嶺愖鹛埂痿','3588-03-19 08:13:50',-6733743803214206249,-1.63161e38),(29,'驔葡跭僋黿惁虇景誑鏞僤湿噬埖抉脙熷颰烪轕','8077-05-04 15:40:29',8667279684477357563,-1.96995e38),(30,'蹍摔艱阑牘裉蚢赶墓澟譽笮牞蓘鷴砗宺睵逎堵','9505-12-04 19:21:34',2994397909026874296,3.23192e38),(32,'为锔瀢些緍袈菫型複媚玽勧睰咣蠕俤偉暵亏寛','4840-07-01 23:09:19',-3455110252287282187,-7.8263e37),(33,'厩蕔刐殇蔷梾僫匔及鏠捩0悱窴蘱碹孽駑鬴訛','2922-01-03 03:01:34',-9016498949567600181,-4.42981e36),(44,'蟟忯帼纝誫綖嶜銤襊媚瀂飦葆葦婶啉绿筍菥别','1134-12-07 23:37:01',8708640396937481468,-6.62228e37),(45,'裍蜍流戕醀迎瀢瑖欤饸謝掆犪树鳦駩硡喃戮玄','8575-11-10 05:23:23',4739352172872328798,-3.74962e37),(47,'姢颾萻弝箦呞姱螌媯麖芀à宒塜胥嵐軮鳝馁焧','4122-11-10 00:02:58',9005058646034466000,-1.02463e36),(49,'Ǧ釺啨$縂淡畬虨嵻枘甞晅厥詂軠骗傜樂熻应','8719-06-14 03:18:38',2338203913395053735,-2.35497e37),(50,'盯浟馗硾砒琽贲墒柽堍嵄瑿灙誷慗泥抡瑵凙蝋','7873-12-11 22:01:34',-4611007759141898747,-2.58136e37),(56,'肟逪碍詙橭锷銀如尯嬨半妧鎭蝀闠仪蝌晜酐禟','5221-02-23 01:20:54',-503409190150518720,-3.03764e38),(57,'违敩幫檤杹臊嗏啚澾丄垰涸炲躖桯犪黖嶤仗決','0147-05-07 01:31:33',-2861998997749121377,2.53206e37),(63,'菷霋盧藅蔀翰婜毣绥啨襯駌舙癹惒骛臀ī燤毦','0496-01-27 09:17:13',6341194344954951611,3.07223e38),(64,'諳椨懔竉郺脓贉灞喰銭漻櫫粄帡氷鷂错遯咤鄅','6873-10-27 16:50:02',-6522021710073960293,-1.51003e38),(68,'蘟长尣斠葪焯鰻焴寠Ō臭Ĩ踂婙滮弄鎃尺墼苽','6670-11-01 07:25:39',8923062526753057537,-1.84752e38),(69,'軂浔畬允躻蹨圻翽冹鍴摸坣踄ǵ輆墱糊臰欲酝','0567-07-30 23:38:40',-4905210752588470270,3.28279e38),(75,'糓儈崘伊缂洪筀詙壧帵局髶嘉劎胝蓓鼾龐粄龒','8432-10-13 00:37:21',8337146243732710879,-8.36095e37),(77,'隋饆劀螒亘缢房开理纠邳篣胱俎趂頸骳舗纬鹺','4673-09-28 10:58:57',6967444326649817264,3.33156e38),(80,'齶砥誹瀠鋞灱谠祢稣傑剙翁塮蝲驆宒股绨菾駳','4329-03-21 16:45:52',-2445089293562628613,1.51844e38),(81,'珩鈧饓昏靉抳油峥枝铂蔸請椟嘟駀嬔翌澂宝于','6131-04-16 19:15:54',-9164500866654595587,2.78499e38),(83,'阎祰怒慻儎犯脺繃礮曬访饝鲩茨广勖铲膇壳藂','2044-10-27 13:05:16',6762012646058021116,-2.1097e38),(88,'诀头鈷ᛗ趛緋陻頉聜宱丅镢潈跼毱鍳恘甏蒻恠','6442-08-26 10:02:34',7802676543626757181,-2.88735e38),(89,'凙麋肻襸审墄攴膐刜宽ǚ瀷娐誹鳟趚铛姻紫恑','3091-07-07 07:48:37',-8407173469039242228,-7.18812e37),(92,'T稀淌骓髎釋骾瘘骏鬒餼惿靓詂燃涖鉧濏髄鷮','4043-05-10 13:57:31',-8673216192159203202,-1.08745e38),(99,'嘌朒鄽纁縺牨猻瀕吖虾旖樹腶朘捱诣檴嫮锚钖','8414-04-22 00:22:16',-5038498656448239376,1.78718e38),(102,'钳奨倄迳傑僃睂罪釪渌灙ź牸铛蘠估疤蓆檓您','0020-09-27 05:27:19',-8359575324299776245,-9.56816e37),(104,'鶽季颒驣牖枪袰昞盒鵟径棘僄颩豶雍裻儉奢姛','5323-12-23 16:05:54',-4520422954003071009,2.23908e38),(107,'镞牭霬驓蘊鳓楡涙堚鐑漮嫒芠虥彅虴铁囊怃曽','2481-03-09 04:07:07',-3165648496253499877,2.53541e38),(109,'皋詬瀻觀轃ȍ¿呚輝籂膸轲蒛続槚褹朘蘮繊瞎','4913-08-16 10:23:24',1455365312082465431,-1.5594e38),(110,'鮉巶覰Ī閣昂餁朑蔤爫佒紗珫臁蓹腹檔亷餦娡','4199-06-09 06:44:30',9175396576793588988,-2.08965e38),(111,'育稈x澄沮稾陞恰錡軮駐暰歅肞緔鯏迦酉韟揺','4282-04-15 21:20:38',-2275399301433762737,-1.47123e38),(113,'霡烾姣ᚱ嗎文本诊腀斳塧枞擝瑌鯷畀馵鉒瞚屓','5819-05-13 03:41:06',-4283132871694765234,-1.28861e38),(114,'繍吞骟唗娸穷屺蔊皱崈涩隄搃栽蝏简欋娣藨篧','9247-12-26 16:46:31',7398868035454932246,6.62445e36),(117,'撁纍鈶覉無黸藃宝摒汼豴膉厩ᚦI逡癕嘀駢詢','7464-12-27 16:15:00',7381350135555962041,2.02068e37),(119,'轥硁谉杕镡廲踝忉羸尬攼侣豐繶輼迼栛螓躵跉','1991-08-15 07:44:20',639351069780789550,-1.08082e38),(120,'蠙鉾痵皩·羮甤乪栠澲敺詗匇轵襈萈攄擲瞸磻','9920-03-01 00:41:27',6022472943301594064,-2.2211e38),(123,'蒍涥嚖丵鏑輪沪钀霚扰齈悔醐屓攖妾蝍飝湘藫','6038-10-22 05:45:56',6778604421242970849,1.84483e37),(124,'禫鐠烐灷碊丂巤眙湐獀桋摅紛盾报鶱圛讧驮魋','6938-10-11 16:40:45',-6900023508084655568,2.02331e38),(127,'伍繥Ɋ透喖聨洳氵鼐鈕唽甪亰啋簚醭苔珎蜼缾','2797-04-18 17:27:24',8785136433078346278,2.31549e38);
alter table PK_RP9330 set tiflash replica 1;
SELECT /*+ read_from_storage(tiflash[PK_RP9330]) */  COL1, col2, abs(col1) is true FROM PK_RP9330 WHERE (COL1 IN (-75,-5798713664489,-128) OR COL2 IS NULL) and abs(col1);
delete from PK_RP9330 where COL1 not IN (-75,-5798713664489,-128);
SELECT /*+ read_from_storage(tiflash[PK_RP9330]) */  COL1, col2, abs(col1) is true FROM PK_RP9330 WHERE (COL1 IN (-75,-5798713664489,-128) OR COL2 IS NULL) and abs(col1);

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

MySQL [test]> SELECT /*+ read_from_storage(tiflash[PK_RP9330]) */  COL1, col2, abs(col1) is true FROM PK_RP9330 WHERE (COL1 IN (-75,-5798713664489,-128) OR COL2 IS NULL) and abs(col1);
+------+--------------------------------------------------------------+-------------------+
| COL1 | col2                                                         | abs(col1) is true |
+------+--------------------------------------------------------------+-------------------+
| -128 | 绯抅駒嗁柖涄歌馂莇袖语辀斤関壧喧嶤綈橐旟                     |                 1 |
|  -75 | 菆卭舰哄怖糗芇介巚孓ç瑗宜凘巔赂聮鱞銸茅                      |                 1 |
+------+--------------------------------------------------------------+-------------------+
2 rows in set (0.01 sec)

MySQL [test]> delete from PK_RP9330 where COL1 not IN (-75,-5798713664489,-128);
Query OK, 85 rows affected (0.01 sec)

MySQL [test]> SELECT /*+ read_from_storage(tiflash[PK_RP9330]) */  COL1, col2, abs(col1) is true FROM PK_RP9330 WHERE (COL1 IN (-75,-5798713664489,-128) OR COL2 IS NULL) and abs(col1);
+------+--------------------------------------------------------------+-------------------+
| COL1 | col2                                                         | abs(col1) is true |
+------+--------------------------------------------------------------+-------------------+
| -128 | 绯抅駒嗁柖涄歌馂莇袖语辀斤関壧喧嶤綈橐旟                     |                 1 |
|  -75 | 菆卭舰哄怖糗芇介巚孓ç瑗宜凘巔赂聮鱞銸茅                      |                 1 |
+------+--------------------------------------------------------------+-------------------+
2 rows in set (0.01 sec)

3. What did you see instead (Required)

MySQL [test]> SELECT /*+ read_from_storage(tiflash[PK_RP9330]) */  COL1, col2, abs(col1) is true FROM PK_RP9330 WHERE (COL1 IN (-75,-5798713664489,-128) OR COL2 IS NULL) and abs(col1);
+------+-------------------------------------------------------------+-------------------+
| COL1 | col2                                                        | abs(col1) is true |
+------+-------------------------------------------------------------+-------------------+
|  -75 | 菆卭舰哄怖糗芇介巚孓ç瑗宜凘巔赂聮鱞銸茅                     |                 1 |
+------+-------------------------------------------------------------+-------------------+
1 row in set (0.02 sec)

MySQL [test]> delete from PK_RP9330 where COL1 not IN (-75,-5798713664489,-128);
Query OK, 85 rows affected (0.01 sec)

MySQL [test]> SELECT /*+ read_from_storage(tiflash[PK_RP9330]) */  COL1, col2, abs(col1) is true FROM PK_RP9330 WHERE (COL1 IN (-75,-5798713664489,-128) OR COL2 IS NULL) and abs(col1);
+------+--------------------------------------------------------------+-------------------+
| COL1 | col2                                                         | abs(col1) is true |
+------+--------------------------------------------------------------+-------------------+
| -128 | 绯抅駒嗁柖涄歌馂莇袖语辀斤関壧喧嶤綈橐旟                     |                 1 |
|  -75 | 菆卭舰哄怖糗芇介巚孓ç瑗宜凘巔赂聮鱞銸茅                      |                 1 |
+------+--------------------------------------------------------------+-------------------+
2 rows in set (0.01 sec)

4. What is your TiFlash version? (Required)

[root@localhost tiflash]# ./tiflash version
TiFlash
Release Version: v6.4.0-alpha
Edition:         Community
Git Commit Hash: 2b1550d10f272a294e1bf076d2838e38034bfbc8
Git Branch:      heads/refs/tags/v6.4.0-alpha
UTC Build Time:  2022-10-12 14:26:19
Enable Features: jemalloc sm4 avx avx512 unwind thinlto
Profile:         RELWITHDEBINFO

Raft Proxy
Git Commit Hash:   018e4dad59381e70add20143855de20aad649757
Git Commit Branch: HEAD
UTC Build Time:    2022-10-12 14:33:03
Rust Version:      rustc 1.64.0-nightly (0f4bcadb4 2022-07-30)
Storage Engine:    tiflash
Prometheus Prefix: tiflash_proxy_
Profile:           release

[breezewish]

Could you verify whether all TiFlash replicas has been synced before executing:

SELECT /*+ read_from_storage(tiflash[PK_RP9330]) */  COL1, col2, abs(col1) is true FROM PK_RP9330 WHERE (COL1 IN (-75,-5798713664489,-128) OR COL2 IS NULL) and abs(col1);

It can be checked via the following SQL:

SELECT * FROM INFORMATION_SCHEMA.TIFLASH_REPLICAS;

[ChenPeng2013]

Could you verify whether all TiFlash replicas has been synced before executing:

SELECT /*+ read_from_storage(tiflash[PK_RP9330]) */  COL1, col2, abs(col1) is true FROM PK_RP9330 WHERE (COL1 IN (-75,-5798713664489,-128) OR COL2 IS NULL) and abs(col1);

It can be checked via the following SQL:

SELECT * FROM INFORMATION_SCHEMA.TIFLASH_REPLICAS;

I confirm that all TiFlash replicas has been synced

[JaySon-Huang]

If we add abs(col1) != 0 instead of abs(col1), the result is correct.

mysql test> SELECT /*+ read_from_storage(tiflash[PK_RP9330]) */  COL1, COL2, abs(col1) FROM PK_RP9330 WHERE (COL1 IN (-128) OR COL2 is NULL) and abs(`col1`) != 0;
+------+------------------------------------------+-----------+
| COL1 | COL2                                     | abs(col1) |
+------+------------------------------------------+-----------+
| -128 | 绯抅駒嗁柖涄歌馂莇袖语辀斤関壧喧嶤綈橐旟 | 128       |
+------+------------------------------------------+-----------+
1 row in set
Time: 0.021s
mysql test> SELECT /*+ read_from_storage(tiflash[PK_RP9330]) */  COL1, COL2, abs(col1) FROM PK_RP9330 WHERE (COL1 IN (-128) OR COL2 is NULL) and abs(`col1`);
+------+------+-----------+
| COL1 | COL2 | abs(col1) |
+------+------+-----------+
0 rows in set
Time: 0.019s
mysql test> SELECT /*+ read_from_storage(tiflash[PK_RP9330]) */  COL1, COL2, abs(col1) FROM PK_RP9330 WHERE (COL1 IN (-128) OR COL2 is NULL) and abs(`col1`);
mysql test> explain analyze SELECT /*+ read_from_storage(tiflash[PK_RP9330]) */  COL1, COL2, abs(col1) FROM PK_RP9330 WHERE (COL1 IN (-128) OR COL2 is NULL) and abs(`col1`) != 0;
+-----------------------+---------+---------+--------------+-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------+------------+------+
| id                    | estRows | actRows | task         | access object   | execution info                                                                                                                                         | operator info                                                                                   | memory     | disk |
+-----------------------+---------+---------+--------------+-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------+------------+------+
| Projection_4          | 0.87    | 1       | root         |                 | time:5.18ms, loops:2, Concurrency:OFF                                                                                                                  | test.pk_rp9330.col1, test.pk_rp9330.col2, abs(test.pk_rp9330.col1)->Column#6                    | 1016 Bytes | N/A  |
| └─TableReader_7       | 0.87    | 1       | root         |                 | time:5.17ms, loops:2, cop_task: {num: 1, max: 5.05ms, proc_keys: 0, rpc_num: 1, rpc_time: 5.01ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15} | data:Selection_6                                                                                | 306 Bytes  | N/A  |
|   └─Selection_6       | 0.87    | 1       | cop[tiflash] |                 | tiflash_task:{time:3.9ms, loops:1, threads:1}                                                                                                          | ne(abs(test.pk_rp9330.col1), 0), or(eq(test.pk_rp9330.col1, -128), isnull(test.pk_rp9330.col2)) | N/A        | N/A  |
|     └─TableFullScan_5 | 87.00   | 87      | cop[tiflash] | table:PK_RP9330 | tiflash_task:{time:3.9ms, loops:1, threads:1}                                                                                                          | keep order:false, stats:pseudo                                                                  | N/A        | N/A  |
+-----------------------+---------+---------+--------------+-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------+------------+------+
4 rows in set
Time: 0.028s
mysql test> explain analyze SELECT /*+ read_from_storage(tiflash[PK_RP9330]) */  COL1, COL2, abs(col1) FROM PK_RP9330 WHERE (COL1 IN (-128) OR COL2 is NULL) and abs(`col1`);
+-----------------------+---------+---------+--------------+-----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------+------------+------+
| id                    | estRows | actRows | task         | access object   | execution info                                                                                                                                        | operator info                                                                            | memory     | disk |
+-----------------------+---------+---------+--------------+-----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------+------------+------+
| Projection_4          | 0.87    | 0       | root         |                 | time:4.79ms, loops:1, Concurrency:OFF                                                                                                                 | test.pk_rp9330.col1, test.pk_rp9330.col2, abs(test.pk_rp9330.col1)->Column#6             | 1016 Bytes | N/A  |
| └─TableReader_7       | 0.87    | 0       | root         |                 | time:4.79ms, loops:1, cop_task: {num: 1, max: 4.7ms, proc_keys: 0, rpc_num: 1, rpc_time: 4.67ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15} | data:Selection_6                                                                         | 201 Bytes  | N/A  |
|   └─Selection_6       | 0.87    | 0       | cop[tiflash] |                 | tiflash_task:{time:2.85ms, loops:0, threads:1}                                                                                                        | abs(test.pk_rp9330.col1), or(eq(test.pk_rp9330.col1, -128), isnull(test.pk_rp9330.col2)) | N/A        | N/A  |
|     └─TableFullScan_5 | 87.00   | 87      | cop[tiflash] | table:PK_RP9330 | tiflash_task:{time:2.85ms, loops:1, threads:1}                                                                                                        | keep order:false, stats:pseudo                                                           | N/A        | N/A  |
+-----------------------+---------+---------+--------------+-----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------+------------+------+
4 rows in set
Time: 0.021s

[JaySon-Huang]

similar to #5849, /cc @xzhangxian1008

[xzhangxian1008]

when COL1 set to bigint, the result is correct

[xzhangxian1008]

/assign