search

pingcap / tidb

TiDB is an open-source, cloud-native, distributed, MySQL-Compatible database for elastic scale and real-time analytics. Try AI-powered Chat2Query free at : https://www.pingcap.com/tidb-serverless/
https://pingcap.com
Apache License 2.0
36.83k stars 5.79k forks source link

incorrect result caused by aggregate function #49604

Closed wjhuang2016 closed 8 months ago

wjhuang2016 commented 8 months ago

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE `ta28a9719` (
  `col_1` blob DEFAULT NULL,
  `col_2` blob DEFAULT NULL,
  `col_3` varchar(56) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT 'ok7fNc%oKNYw-T=1V)g',
  `col_4` text COLLATE utf8_general_ci NOT NULL,
  `col_5` char(76) COLLATE utf8_general_ci DEFAULT NULL,
  UNIQUE KEY `idx_1` (`col_4`(1),`col_5`(4)),
  UNIQUE KEY `idx_2` (`col_4`(1),`col_1`(3)),
  UNIQUE KEY `idx_3` (`col_1`(3),`col_3`(4))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
INSERT INTO `ta28a9719` VALUES (_binary 'Jj_ey',_binary ')u2@FcTykz5Bv2b5$M',NULL,'畷','K'),(_binary 'gS-p4~_^KHCUq',_binary ')u2@FcTykz5Bv2b5$M',NULL,'下萿3@塧','((#!x6t65InyZ'),(_binary 'tDU',_binary ')u2@FcTykz5Bv2b5$M',NULL,'*FjWRF幦笙别','@N~c_JND5'),(_binary 'o7MXqjRnclAVK@x',_binary ')u2@FcTykz5Bv2b5$M','y#','E憜O钉~q*箞D爎+dM6嚣','TBdB)Cgxl&juiZ'),(_binary '0j79VAtXpm',_binary ')u2@FcTykz5Bv2b5$M','','_皍Jek兼田','+YZ'),(_binary 'o',_binary ')u2@FcTykz5Bv2b5$M','81z~1tcDRgt','乓8X孃6崕&3犽',''),(_binary 'h!r3',_binary ')u2@FcTykz5Bv2b5$M','pEc-fj0b#tJ@M+#EXJ6','H鋈仦f3@琙蒮',''),(_binary '5P~5ayHh',_binary ')u2@FcTykz5Bv2b5$M','&R)RpB!G0s!+c','(TLh浏I%GpJ姰wg痵)2举F瓅','EUnV1Mdxv4'),(_binary 'NmcwVm=o^-6rF(xSq4A',_binary ')u2@FcTykz5Bv2b5$M','ZlO','屎ppI','~CPYqj5oJk'),(_binary 'Q_me*#',_binary ')u2@FcTykz5Bv2b5$M','1#5gNMe@K','SqC蔵H','OCB9H9G_R&fB_'),(_binary '(',_binary ')u2@FcTykz5Bv2b5$M','J-^2jZm','2P袙o萡j2糰j%)桹讌','Er2nN!D'),(_binary 'S6J=K+Dx=kBGu5fT+',_binary ')u2@FcTykz5Bv2b5$M','1^s$+K-Toq-nXm8','圞pOE*w逆頰竬b漀爷夆繓qB+K遠','UPPvMc&o'),(_binary 'bzzA=C4Wwn2W%EtQHt',_binary ')u2@FcTykz5Bv2b5$M','vK!PywB3','o@垽22筓gms孳7颜貍$韈','zO2Ppi4L'),(_binary 'x)@lxLH*smgce28H',_binary ')u2@FcTykz5Bv2b5$M','px4I3k#nkKu','bE卡PV皡毁39q霔','aiA(OT5VGB'),(_binary 'l@+GJFITrG2Bwma)Tf',_binary ')u2@FcTykz5Bv2b5$M','zR*xcb=(B&)@4)T9t','','C%F8!S('),(_binary 'I+A74',_binary ')u2@FcTykz5Bv2b5$M','GwG!(Y','S尦X0曜箛HS垹FU6p虂','IrOGE-'),(_binary 'nK2Z5hKI-Cm#JKGqJXP',_binary ')u2@FcTykz5Bv2b5$M','CK','綢41縸琓DE敞艹v柣d敩烹','fp!#yjk^kGicEi+XFdO'),(_binary 'pR3_wI2',_binary ')u2@FcTykz5Bv2b5$M','%_9zv_kN','a壭~0U*壁v輁84际~Yxx溏','ezAtZo0t'),(_binary 'Vox#5lrYtu%Vg',_binary ')u2@FcTykz5Bv2b5$M','!FtnW*tkQliPPO',')眆(蜩','Y=eQXbROES'),(_binary '2',_binary ')u2@FcTykz5Bv2b5$M','eJN','5粆饽t蘢jm6D茒','P6SdZj-kUL%z)'),(_binary '23&2p',_binary ')u2@FcTykz5Bv2b5$M','53ga#03noD^pV','d云I鹴簸b汑2鹒鋿Q拢wp','mg'),(_binary '#z4OF)R!Fw',_binary ')u2@FcTykz5Bv2b5$M','^HHCARH^ARk','H(怼FL蓝Q#Q34o珠~0覡','1j#EmcNP2wACDWLAeC'),(_binary 'N@(ccAc&$pe)GSwcLS',_binary ')u2@FcTykz5Bv2b5$M','ZQZh*P)^@aJ=T','S骴墫(1sZ槦轇绦橄瓆','v'),(_binary '~utip^(2LPaKBu',_binary ')u2@FcTykz5Bv2b5$M','u*yA4J3XpdawHDU','jZAp','+oAJOi1k(GqfQR(B#'),(_binary 'iiB6J#Ip*Nu7$AB-)7N',_binary ')u2@FcTykz5Bv2b5$M','$TZ6y','n#Gn髑zLfQ%B4舂w','^KwbK'),(_binary 'nL',_binary ')u2@FcTykz5Bv2b5$M','&lzlc_5TP','e爲2Kp',''),(_binary 'u(+frjhRTqq',_binary ')u2@FcTykz5Bv2b5$M','P(=S~*yPnjD1','撍强s伭s郈膈L&2壔','DA'),('',_binary ')u2@FcTykz5Bv2b5$M','Q','薕鈾蝞b衘讑','*15g&OLYA%0'),(_binary '5Kx!%M8l~0=n=$',_binary ')u2@FcTykz5Bv2b5$M','','F嶆暹','KGOko3eeP=-UuP2k'),(_binary 'zCA)=H',_binary ')u2@FcTykz5Bv2b5$M','0~0KZjwJh','xD','jVo%21bFZR@fz8'),(_binary 'Bgh#q',_binary ')u2@FcTykz5Bv2b5$M','R%0_S+8NB','','h%at+mJW'),(_binary '2llrxuEMa)N-Ny+MQiF',_binary ')u2@FcTykz5Bv2b5$M','2a*uV5eh6KZ','采C赭','S&n3FYMb~=ia'),(_binary 'v=nWq1z',_binary ')u2@FcTykz5Bv2b5$M','l3xdS&es1L','9补wiQ瘶','2Nm4PZ$s3YEGjA-@o8'),(_binary 'ai$WwDw@',_binary ')u2@FcTykz5Bv2b5$M','~=VFq=K(@n$*5','FG礑廌憾','mQJ*VRF3K6P)'),(_binary '6afsvXO',_binary ')u2@FcTykz5Bv2b5$M','&crA','%浯鉚I&0)*t衄Q','UsjnUPp2n_n%M#fK$'),(_binary 'vm7oV=WHhzGSGCl',_binary ')u2@FcTykz5Bv2b5$M','rWl(_!Ux%!','淀礵O嶍nuS璳#Y+U4ZCSO','J-ejlzF4lQ'),(_binary 'gL5jHF9AHTYjFy',_binary ')u2@FcTykz5Bv2b5$M','3vfca*qJLKx','G*棊欻Dt帖導_鸚茭yAE麘Dw絑','hxO&Se'),(_binary 'SHtZr4u',_binary ')u2@FcTykz5Bv2b5$M','quBMUp','噈炥捀銡','tHn2VLv0Q$CNGobtkf)'),(_binary 'WRqwh1csk^4U1-+1W0',_binary ')u2@FcTykz5Bv2b5$M','3uWt8EKfk#B!S','侓臞4蠌發X蠲f','N+C4RH'),(_binary '3(L=H$QR)E4r5FQo',_binary ')u2@FcTykz5Bv2b5$M','kz','o鵬V揢)盬=a亂f迕7k%_','b(PtRT5ewYp(6'),(_binary 'OWJ',_binary ')u2@FcTykz5Bv2b5$M','','ckP0i蚾扑嫛','s5xfIk_o'),(_binary 'gX',_binary ')u2@FcTykz5Bv2b5$M','26ZocljuDQS86=r-uIu','輘wKsU%筥-qW)(s倳','Qp%u1'),(_binary '+SoOzD',_binary ')u2@FcTykz5Bv2b5$M','=jdcU6ne5','J+Pk苲龘','KluL!d~SSkcs%bEbHQ'),(_binary 'h!~sHNaBJSn(pxvqiA',_binary ')u2@FcTykz5Bv2b5$M','Ke1xkGt$G%m1xy*W~Z','魟=F63枾扭鱇','ZLrS(!rBs*1LB'),(_binary '$23nzeZb6#1xy%h',_binary ')u2@FcTykz5Bv2b5$M','oK','','kgq'),(_binary '4j&)b4',_binary ')u2@FcTykz5Bv2b5$M','B%!U6jI@**aPQce','瑼h籽cTjri轛5w顩K_K奮-d','@&7o^)Nr#k'),(_binary 'rskt*ufNf',_binary ')u2@FcTykz5Bv2b5$M','*h6TP%aC','my2UxCq峒k','qz&CX5F6szx'),(_binary 'JL#(2U8KlqmC',_binary ')u2@FcTykz5Bv2b5$M','U4BjU~yUGsbpK','+LE-竃q劝','G5x-TPm9*tSmus*-S-'),(_binary 'lBp~#CSh',_binary ')u2@FcTykz5Bv2b5$M','gbOUnU~qm08euon5l','澽軧c毹w*Ue榨z#谼pbcw3茬P','M_+9h+a'),(_binary '1uR89lR&-V3',_binary ')u2@FcTykz5Bv2b5$M','sq','噭^y漁z2苌jJB綽S鶍笠','UX*i'),(_binary 'u_6-Jn#~KR3',_binary ')u2@FcTykz5Bv2b5$M','R','vgO爾挃媒O俨I^卧鍟*n','Mk'),(_binary 'Ahme-UA7y_$D!',_binary ')u2@FcTykz5Bv2b5$M','r@h','@矪仗踜','l'),(_binary 'wpi*8w',_binary ')u2@FcTykz5Bv2b5$M','2_G','挊nhJ6禙0q5~+','v'),(_binary 'v#sH8',_binary ')u2@FcTykz5Bv2b5$M','4sq','TtK*梚_ln(翰7攪','NFC'),(_binary 'nC1gTHt%AUzYaal',_binary ')u2@FcTykz5Bv2b5$M','v+2Q%O*OyX$&-Rh1','猇1S澱钔聁','OW9S!RaP%7hD9Ud!kQE'),(_binary 'CbxUn7c~(Sg-v6K',_binary ')u2@FcTykz5Bv2b5$M','h_VNPc-3tFwo0q-&p','撻栶j闞=','uL-Vk#'),(_binary 'aQgKSivD#LVRO',_binary ')u2@FcTykz5Bv2b5$M','fUCowXBzf4#p^','NM躘=L肌嚩Z鍓e8臨距k)喫','WpVOcCWd=7F!e'),(_binary '8pUIvl9k3EKr2',_binary ')u2@FcTykz5Bv2b5$M','t5xkUgVj_WWnJcZge','鉹湒%T(','lSKu(#eShz'),(_binary 'G#kWmULQ$!A',_binary ')u2@FcTykz5Bv2b5$M','XwUE1@b$Ru&zrKmD','贰债萶-鎒@i谑6溨澘QmE^6穱pW','R&(f6i-xE~+'),(_binary '4ZStoXNizErcO+&Og',_binary ')u2@FcTykz5Bv2b5$M','acU!*qh6cOGa%xJJleU','t暅婼f_pcM','gf'),(_binary 'vdEijMbO*-q7',_binary ')u2@FcTykz5Bv2b5$M','k2oqU^~@@J6','H琫灀鋼g揸鉕~','E617j'),(_binary 'a+SYl(5_*Hxj7YSPu1g',_binary ')u2@FcTykz5Bv2b5$M','y913GZ0Gz+a','侐ZxYi','+QaAvE1S*x1c'),(_binary 'RjF!K^L@tz%4gJpJJ',_binary ')u2@FcTykz5Bv2b5$M','vi7yO@dkUr!Y~iVR','U认~钉686烝jZ','l'),('',_binary ')u2@FcTykz5Bv2b5$M','-wP)Mz-X=P','pdMj#ucS瘦Wq砍veb','E'),(_binary 'GQd3&iWLoj4s2BNgXfl',_binary ')u2@FcTykz5Bv2b5$M','5y3D$','bW嬅龸M$','8CJx'),(_binary 'gm0#R#',_binary ')u2@FcTykz5Bv2b5$M','CRk#JIBGLN-o4_5AY3(','~鿤嗑阣z畍B苻L蚱H擡顧','ApapckCgXm*bKV%Xi#P'),(_binary 'Rh)_*^qC8cQ)',_binary ')u2@FcTykz5Bv2b5$M','=*&D(8A-cGUWyDWI7H','謷vji2往dlqEma6豒sQd稳F','dwjlYw-Ip&=)neX&%H'),(_binary 'yxUxr',_binary ')u2@FcTykz5Bv2b5$M','11+2$86','厐0O)z','l8V2kDPq4Rox@_p$Hw'),(_binary 'wAEQn7bZ7Skzu_0-Aa',_binary ')u2@FcTykz5Bv2b5$M','jI@-SGGqtGHKjqVPT)',')V朲=qB','&WBK+ziD2uHL=tx'),('',_binary ')u2@FcTykz5Bv2b5$M','^M$tx~!%(!Dn#WIU0','B0惸肮i2-躹=)~kb頜濻%','kqoHJs2%^wxu'),(_binary 'ChIYg5~@qY+3_&6K&',_binary ')u2@FcTykz5Bv2b5$M','x','Ykb彂','xdex'),(_binary 't#+dbC4tQ8BxSiC5^',_binary ')u2@FcTykz5Bv2b5$M','tLHqBvL)gK#*N~d&FM','*','8MK-oI%$'),(_binary 'piwgA%tLIohEDy',_binary ')u2@FcTykz5Bv2b5$M','sj*c-','爰mwC腑獟uZ','g6uFU=^i^Mq0Ar%QK^'),(_binary '@RKRsj$Ye*r',_binary ')u2@FcTykz5Bv2b5$M','eePIfUDy','jeai5阮','@0cKi)9A96hD'),(_binary ')Q-bD',_binary ')u2@FcTykz5Bv2b5$M',NULL,'r詰y洃_餄鍱','^'),('',_binary ')u2@FcTykz5Bv2b5$M','!OD%51YnYSJaKy','!_tv鵳刼rGX瓳!陆榼鹙','JouHgm7l+'),(_binary 'TrF*&',_binary ')u2@FcTykz5Bv2b5$M','g41pXx@az6MnRcXW0Z','i^5c~弦@古VD虂輾~霞J鱽G7I','*Ma'),(_binary 'q3=bfYokF22^Z&3pV',_binary ')u2@FcTykz5Bv2b5$M','W($7t+X%@CQQG2Sj','仏jE桳箔5Z=X^僉+猯R2仭Y(','K8~%PYFkiX'),(_binary '_%',_binary ')u2@FcTykz5Bv2b5$M','a4eq=3t','9Z屩4遞氫谝諦','cGRQx3'),(_binary '5',_binary 'keGZ2ey%5Cq','aigLdqPeT','蟘*0DnxW蔹馕疑G9x&4+E','_6^T+g'),(_binary 'yCWnks-kM@D',_binary '-BPyubn)h9Iy0q','c(','s译QqMIR陥繦籵','xe!H8g&z~l^p'),(_binary 'B7jVZ=8+V-2t~T+Ro#K',_binary 'f','&N_ebtJ=$dyXr','^','&'),(_binary 'IVkwv=a9#XfGIG(p63s',_binary 'poD22G)IKODhTTak^Q','UvI*rcumAlAVk&Z&4y','樬+=睕泿c設2W竚霆畢','J'),(_binary 'e',_binary 'nBlUr','rc032bfJw','^#-csO击','IVHe0^HegjGIuufmcYT'),(_binary 'EfY597^cs&',_binary '7-&$87a#','ww','O&酐籉tjd颱)餪O()','RQNvl=zB'),(_binary '*$nEA','','N','_閮G缗7L爈','jbUtb#@%JI'),(_binary 'oyOmZa6N$mzhZv7b',NULL,'t0!','票hKP#Zd-嶯d帘ZJ','t7Y8'),(_binary 'h5~T','','8qE-^~+','E','ljum727AiVQS'),(_binary 'GxvqD5-TVaTZrUzDX',_binary 'DxnJpGT~*oyRClV3B','TPO@1!nhGYL','Zk禼yA蓱f餇vo','84ZHT+'),(_binary 'wTvSC*wdoU',_binary '8Suwq0vr','OY+v2ah5Px^','饈巴p賮!壆fd','-U7rWD-lv66DWgzP%'),(_binary '7Pq_i@CtM^(bvN*1^#8',_binary 'T','blBNLE!','S&gVH3pxr泷Q奛lC','Pzpf0^y0'),(_binary 'VVFPxx79^-_f~jJy',_binary ')Jn%nYiJ2f','n=xU$gGPnt7rn','3菔ol','9'),(_binary 'eBq7Jb9WZXAnBHZ09)',_binary 'c(-Oe@V2hg6','r!77*@&','','xZQo1-BdQLUZ!sW'),(_binary '&lj3#JPXDNEV76y',_binary '%80DCfW(@3!_7zYD4','N@b','z~昽p',NULL),(_binary '_cNP8A*',_binary 'i40acEYejDV$6GV%','6','UW0T','PU~lAu%ckyH'),(_binary 'tNBKb82RaMeL1qd',_binary '(h0ha','hJz^C','vjd薙s幩o9N%彳f','o6e$PnzAFDLtZXv'),(_binary 'yr',_binary '#me0D@=%kz39~1UBZ_C','(QAl8nFy!^GRfCxgc','iy^嶆K','-Q0@qnB^&+!'),(_binary 'U9U)',_binary 'VUNRi!','B=u~dwzESJ%t','巖vWu2釔窃S叙c','-U_'),(_binary 'Vf*N~',_binary 'm5bFJ28u~Ag=9)','64Nl$E*qq!X9','pp桐E堺獴伈KI鋙喞T漹冇篃',NULL),(_binary '^7e',_binary 'Tta68%~Jv','','A掣夹螦@昨熅)Nlpi6洿&','aXptvE'),(_binary 'FMW!5BV%S7BKk',_binary 'n(c','ZwIEej','','6*i+oXj'),(_binary 'CbWBi@6C3-BNZ6JJ2',_binary 'cgGz!rZm','~RGUXkcp','gFAh+%鿍_y!萲Mg_-v','bE1'),(_binary '@z^r',_binary 'nge6','Rvehq3#^lm8!','b2y=','^'),(_binary 'KuCnsE0s8M&',NULL,'8IijB-b','鶱脅鰐B!餻+驡kjSa賅捰S变y鵝H','qWZbR2cq$CT'),(_binary 'z)aSA1Q$m',_binary 'Lh9$sQJxD&cVb','cp%4k_FLN@I6','QME$','1+zHn0qE(_wl!8'),(_binary 'jI',_binary 'gQ*du5ChshGb','wG0cPW)$','逕!赀y@*为','=9BEIHYy&VT3'),(_binary 'S',_binary 'bnjFDO1Q7','gxp-W','W檻','Nf0YnHZ1xmtASxQ'),(_binary '_6@DjIh(-',_binary 'Bi5oKYu$Y@&tuFd7PM','7fYjEDeri4xMSIaX','*Hi','*9~Z0u~5IDJ&(^+mJ'),(_binary 'l0ePsAWZ4',_binary 'vylgHRik2tADF*d','1gRboCH-N@ku!','7Y韔$R階gHYwf','6$yha0iBHhDT=34+f'),(_binary 'dc6$d_-+kuN8!gBE',_binary '_c8JXlB6F!4gLckjjB','o#r@','鳸O_嫮54頽SvA6*-7EK糮','@IZmbAsjNZ4)e'),(_binary '0Eg7P351nNf)95LSM',_binary 'VZ3&sMB#a-JBnVF','bbe8F','sU','q5JrddFqjtM9u1Us'),(_binary '!',_binary 'v1&OOGQca','~ReM+w=P+Z2G','问d(衭K狳b橸Fl','qo0!'),(_binary '^vt&^~hn2wfhBf3)s',_binary 'BUw8nH)cWnlAN','^JcsZ7j=3F7PTKiE3','','x46kM^#_G_^mA'),(_binary '*U7wY','','TaSH)O#5jCKSp4HItK','','VAR8At4GmiV'),(_binary '96sI8GEI2CA&G7',_binary 'ga','PSa)','d釯i','xhj!bfvNfPt=g'),(_binary 'Msa0*-%OyiR)6TJ-',_binary 'q!n)PLpYGUz*1','InDmyR=&8Ns7*','!E埡(姴婳L4f2龸a@郴摬','O-W'),(_binary 'pXi^)OyldVfn7GfD-',_binary '5MT4s~MV1lHa!V7Gu','X%_','(iQ橐肞3E烟鶵Q凡1e1鯊-嚤','ILF@nYT*HMTn7H'),(_binary '3R',_binary 'EUjjLMzN%~hRE!-JqX#','A_8ZjKv^uxOg','圾~oS',''),(_binary 'd1KN1OS^uiP4D)',_binary 'hi$ZFE)e%kxx','7Rq$r$36*+w9qCuP','g)遇A兢$eZnH7h楉孹1Smn','r66dzgb&I=UGg%A*~'),(_binary 'Oi&N1_JL&y8)m',_binary 'L=l4)aq+-(up&Z','rr4','氧v漾謭^髙G=w~筪WZ(N_彀茰','orGGwlV3(+p#*Hm'),(_binary 'P0CkccnuuX8!C!D&p',_binary '#z_nNN1e3@~C)Fk','2cM-iVsz#E','B骯汲欵XE','B8MeL+(2hyCdBg4!LlU'),(_binary 'FFI)jx',_binary 'h2QXo*8lNT924-','^y2VP','A睞缮o桲VR驙^妼-塢阠oj','XN=Z#%+G&Wf%d+gITwC'),(_binary 'qah5Dh@3vHDH0^~6a(',_binary '1A94ShvNT','c&0!R#l1_','2v樐72筻惧cO爺S-繭鰗U','2nndiH^'),(_binary 'Tz3Z5H0Kol(j',_binary '4','qO=DwpH%P','y0+钏nTFhpe栗#','L-LJOR%t4#'),(_binary 'wMeU8',_binary 'p-MRVy','XFI-mWA4','pwZ&o^o5M^磈跲E7鏘','1VK8=rCAM6wk9L*XhA'),(_binary 'YP#@fqsCaI4(Vx9',_binary '#IRyc=e&oFi@k~(B~Bi','j-oBs~','窸偦','5h0@0*Cg4c#2U');

SELECT col_4 FROM `ta28a9719` WHERE `ta28a9719`.`col_4`= 'Eb' GROUP BY `ta28a9719`.`col_5`,`ta28a9719`.`col_4` HAVING `ta28a9719`.`col_4`>='=';

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

empty

3. What did you see instead (Required)

mysql> SELECT col_4 FROM `ta28a9719` WHERE `ta28a9719`.`col_4`= 'Eb' GROUP BY `ta28a9719`.`col_5`,`ta28a9719`.`col_4` HAVING `ta28a9719`.`col_4`>='=';

+-------+
| col_4 |
+-------+
| E     |
+-------+
1 row in set (0.05 sec)

and

mysql> SELECT col_4 FROM `ta28a9719` WHERE `ta28a9719`.`col_4`= 'Eb' GROUP BY `ta28a9719`.`col_5`,`ta28a9719`.`col_4`;
Empty set (0.01 sec)

4. What is your TiDB version? (Required)

v7.6.0-alpha-342-g5883c5ba34-dirty

XuHuaiyu commented 8 months ago

After discussing with @wjhuang2016 , close since not reproducible.