window function and plan-cache

[ChenPeng2013]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

DROP TABLE IF EXISTS `PK_LP9463`;
CREATE TABLE `PK_LP9463` (
  `COL1` mediumint NOT NULL DEFAULT '77' 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_LP9463` VALUES (-7415279,'笚綷想摻癫梒偆荈湩窐曋繾鏫蘌憬稁渣½隨苆','1001-11-02 05:11:33',-3745331437675076296,-3.21618e38),(-7153863,'鯷氤衡椻闍饑堀鱟垩啵緬氂哨笂序鉲秼摀巽茊','6800-06-20 23:39:12',-7871155140266310321,-3.04829e38),(-4913947,'棰蛎祠焰譴佁犾痝谢鏑置徚鱉莯噮咚贤盐潌豺','2263-08-08 17:23:49',1545414483822624556,-2.15272e38),(-4806012,'旔竣鐹伪嶄砯岡剾时玭剌螻徾姽聖熪宼ȃ挾窌','6370-04-29 19:11:28',-8216933188566193952,1.32682e38),(-2778233,'殝歿殝险談絝鏿磼玗籔晎涳械隅慗髹鼳嬥蘮邁','2520-09-07 15:17:00',5249477780712693231,-3.11251e38),(-2772327,'蒩餗頵曝鸽嫔媘飕傪楿趪旯蘋哅秔菌娾睌伽隞','8737-12-07 19:45:00',-4956075548674417581,1.85502e38),(-2387147,'懌迎诸愎覄邪锡艂穙詝簱I躷誹铴簂圜梻躃蓔','1622-06-28 15:39:27',-6463678879558062191,-2.31652e38),(-633093,'扅郁舦睵啧吀实涆羸慏殅吰壖鬴粺鰇跘鎺釞循','2096-05-13 09:45:41',-5312920068057488968,-2.08597e38),(-182598,'続覸槙兮肸渄礤Ă緻¡躸鄸墡筦窣鐕椸粠惊蘽','0853-06-03 13:06:07',-829122271613610053,-3.44481e37),(-28944,'铃酸惧朣死鋑擠皝誻穑架芪弓笕偹鹠ͽ棄臄鎋','4177-08-05 09:49:12',3746549421566482264,-1.6843e38),(-8045143,'綞蕗嗧耵惙籵淃蝈群蓩薇嗋磛谢蹝低漸将蝋煜','1022-04-25 22:28:00',2872555976061758995,-2.2882e37),(-8038396,'恸汿斧綴啘籪禮嶊秶右夭菧狔蕽菔曙懢錇馀眈','7996-06-06 08:59:03',-8114866438011717679,2.78188e38),(-6153255,'赻簔澌琦桤撆粥捑栀骃鉑獳瞠阄镤鴁狜孁嘚龋','5383-04-30 04:50:07',-2448082699896872733,-4.37179e37),(-5712877,'滷鑖綌曘糫ª覄媴煈黸鶐信裞ņ埤葫癗爟期Lj','1490-09-25 07:33:16',-5088168709682328233,6.91001e37),(-4582206,'痖憊鼣赙聝擡埵眒娍蕢箾挡鄕郏軎炫脢莎遏臟','9798-05-09 03:41:28',8206149033752359327,-2.28612e38),(-4293999,'曩矴炨築銻擹嵼鄴亡捠榅匜呦娺鶼婻叴瑗匥頜','0230-05-23 05:06:42',347144365316404900,2.63069e38),(-4184662,'皶澞鐞橣諏獣玿問盅蔁孵鱝磥璯笏í报籶枦沊','6755-09-21 02:11:27',-6211022393393007898,-2.475e38),(-3255943,'嗈鈴徹箙骅娹诪佃阖栢榆ƕ倐緮瞢弳簀埆怯榔','2856-07-05 01:04:43',1334060740054412859,-2.56555e38),(-129213,'礿悕輗艬掹獎ƞ溋琚釿掣椀鸌呩蓛箐鮸贔Ų癰','6390-08-12 03:04:03',-8298089244104231201,1.18572e38),(-30554,'繫七壟惻ⱺ夿摡動賴馫杯汈律瘪蝢沮鋵莳褷煴','2627-10-11 16:27:06',2912321128157837822,-2.99935e38),(-8388608,'鴭驛螠溯櫇虣箃鵍聁滧欮鴢穋菰鑕檩ᛊ媊璚毈','9604-09-28 17:31:29',-7371998953091755926,-8.15966e37),(-8082990,'磰鰑刚榭峂蘎厪峁ᚳ鶞胜鬥竿澈钟面屧衄蛋耂','7985-03-30 14:01:26',-6427550397125826939,3.12961e38),(-8011363,'弖摴忮蜠啠檪萏语甽炼弸匓柊嫴爜贕鳸懿謥泍','6794-01-21 21:02:49',-4448578021967277134,2.72955e37),(-5160665,'颩蟦病浃崻菢鵙倞瑡疎轭涕瑛蜎稡孏禠魛璐夎','9383-12-13 13:21:05',-2679903265649860172,-8.6224e37),(-4945132,'逴洒拸眃瘔鐐鉨咈匊绪韚勧漐巤焺桤讼魛潺赙','1506-10-04 03:10:07',-5843564349939979116,-2.85083e38),(-3880692,'舋鍹緇歳硋遡幺銔鉅徣泏逾蓶嚵衕捱嚚歰韃穏','5525-03-28 08:57:28',-6195559273672467388,-1.03691e38),(-2498021,'継國蚊輿桍譒壠柖菲飈仁鼱屨艎旌缢飆侖ⱷ形','4050-03-15 00:32:32',792069456425330341,-1.15159e38),(-1895953,'賱酐0嘒鴠贺蓝盥槥虪蓩呓呡掺貚蛆ü磌藚醫','0487-08-24 17:19:13',3400110933867284980,-1.64809e38),(-1776937,'羡鏢嘽忘馆媪麴垯宿濑瀋莸靝塁果惻陡躯霛璲','7035-04-05 10:50:43',-4547549934203549002,4.55481e37),(-1420580,'茷襱糫箂媏啁喙琕欂菌鯱氭軹徶春儠啑鈻巇洜','8791-05-29 05:00:57',2190477941768171122,-1.70447e38),(-6944425,'Ʃ忬戻獹減眈粔泰韧ᛃ瞒®b眕戩爿梇嬜亨龡','0614-05-26 23:31:28',-630766702122741002,-3.10786e38),(-6506153,'餛晷蓣瞖嚱綜凴擋炤鮲綔t尌鱊珰濦磪恍箹宑','1522-06-12 01:29:49',159195722670589092,1.82989e38),(-6489922,'偏釒迳雭箳踓辻漫蒎娳僰售Ħ堼顀茝蒍龧坿岹','0698-04-28 02:45:25',-3595502563276192571,-3.02782e38),(-6199986,'柏堊橽倥賽磧嶳開攎水灪垣樤镕藑骝磛獁垆瓋','4450-10-08 09:17:39',1655759404016671236,7.04081e37),(-6014019,'雁鞟猶轠Á劓細廓傽鹾蹖汔讳轸驘还鄍鴍咮凰','4808-11-27 03:01:30',8334480384434477423,2.95118e38),(-5738659,'鉲奻蚔曙巘鷊陰暘樂祀櫠ᛍâ浉獋伝峞嵉嶏騪','6698-04-17 09:46:38',1200454705176909368,-1.28185e38),(-5169913,'乡懔韈鮨駖疃咚鐷馱撶离圻諓譯竟侍纟鹜绑脘','3673-05-01 06:19:38',-5173977021461180219,1.97799e38),(-2758326,'中踨谸毊O伄嵩哄峯蝏筟犫麛櫬殫职齷捴蹞鐓','3113-09-19 06:14:59',6257025853983232907,4.78083e37),(-1720500,'戉腏料W滲慡恊鶟攤员輡旺熇惀陆虢伷叝粣蒤','4034-05-31 13:03:37',-4256184090190700732,-4.04535e37),(-1671606,'讻霣倀芍喋幀慫爞匍礲裮Ʈ擵姡疔霭斖糏Ǹ叕','3303-02-17 01:35:24',-8823540281690126741,2.83951e38),(-8138350,'腲胶靃寏虮嬐誚鯎悕髑戹鯮鸾扬榞骱炜錑殭轕','3032-06-25 17:45:05',-848303542958739399,-2.6842e38),(-6930338,'寜嘭僽俯蝩隗衅觴墲皥淐磵癚嚦鱝暏旭糏烜雀','7926-05-10 23:31:47',2691538439506285306,-8.56625e36),(-6555475,'艊勢Ł高恻豄財砤缐丫妑鏕丗闤膿劜肄檴«楗','2574-10-30 15:11:05',2073160765756683969,2.03128e38),(-5350613,'館鸂呞砟愆慮呃峁頶牞鶍祡樫瀮黐荼婠輯鍣进','2077-07-10 00:15:06',-8348653747675319984,2.3749e38),(-3734049,'饣杺叴牵摰蒛薠秒渇蒴苳袔贌摐蒝憨鹇婴镺妭','4898-10-24 07:40:44',7218762996652705574,-1.47406e38),(-3365298,'赏祤羯绶ƨ堗歛韲埸街龡氓祐餋铉膠嫮隋楓摮','4668-09-13 16:56:11',2487383637110957678,3.35725e38),(-3197856,'囵蚼椠谜鮄榜浕鞖漉衾厚忉矽萵捶诒典肷ᛜ愉','9563-03-04 07:04:30',129645528205711603,-2.24686e38),(-2008138,'椯帆煱髮煺战劓峲宻菂鮁蠕聄Õ酱蝇煘丱鋿躛','5339-11-02 01:45:52',-8444110819169744674,3.30073e37),(-575394,'敆嘠烘轈塦嫜儩蔣遚楊夵Ú鑅槛暈齻鄙齤Ⱦ邟','9386-12-17 02:10:15',-7674328585546586725,-2.87191e38),(-246657,'巑根m蓉擓頳遒桦Ù眞祜搚睷玙謉蠋嘐韾呸薊','3280-10-11 11:54:16',-8955646210370594916,1.7089e38),(-7904655,'緍茓仼訿殟啦岟缭瀿隤郶趲鵰瓬模誄妾騇绠笒','4912-03-30 07:38:15',605377544245061273,-3.06954e38),(-7524792,'輔蛨昑鱽攁顈藫觷礷嵤爄沣槜鷻龆仵咹岕情褏','3262-07-09 15:09:20',-1377829256004875466,2.08251e38),(-3501867,'犅汋呙隧搬弬測隢癉私忱僑滌綌堼鱾伆章踴龙','5324-07-14 02:36:39',-927618339832080585,-7.98116e37),(-3103684,'迕鉾褑槚專士赚祩魷禆羰歆楧摫膵豢敟夀鋴烓','1602-09-23 06:06:38',3936014361230164803,-3.7221e37),(-2565589,'斣琨塇邡蜽垈島篾蔎恁销捻挝霗忕此淙趥赸嚏','6136-12-14 07:00:27',-7727855699588064901,-2.96631e38),(-2091597,'瀥膾芖罆諲魏磀裪珍巧鏭唇傥钓辬媢嗽墳鯓篰','3949-03-11 19:09:40',-8175811140852161086,-7.07872e37),(-939429,'拐票讶窂馍礄绫Ɯ筿血闱旕仺畭琰鱁牖髮杚猽','4832-11-15 22:14:36',6320778724328745740,1.49185e38),(77,'娥藨潰眤徕菗柢礥蕶浠嶲憅榩椻鍙鑜堋ᛀ暵氎','4473-09-13 01:18:59',4076508026242316746,-1.9525e38),(16614,'阖旕雐盬皪豧篣哙舄糗悄蟊鯴瞶珧赺潴嶽簤彉','2745-12-29 00:29:06',-4242415439257105874,2.71063e37);

prepare stmt from 'SELECT *, rank() OVER (PARTITION BY col2 ORDER BY COL1) FROM PK_LP9463 WHERE col1 != ? AND col1 < ?;';
set @a=-8414766051197, @b=-8388608;
execute stmt using @a,@b;
set @a=16614, @b=16614;
execute stmt using @a,@b;
SELECT *, rank() OVER (PARTITION BY col2 ORDER BY COL1) FROM PK_LP9463 WHERE col1 != 16614 and col1 < 16614;

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

MySQL [test]> execute stmt using @a,@b;
+----------+--------------------------------------------------------------+---------------------+----------------------+-------------+-----------------------------------------------+
| COL1     | COL2                                                         | COL4                | COL3                 | COL5        | rank() OVER (PARTITION BY col2 ORDER BY COL1) |
+----------+--------------------------------------------------------------+---------------------+----------------------+-------------+-----------------------------------------------+
| -6489922 | 偏釒迳雭箳踓辻漫蒎娳僰售Ħ堼顀茝蒍龧坿岹                       | 0698-04-28 02:45:25 | -3595502563276192571 | -3.02782e38 |
                                    1 |
......
+----------+--------------------------------------------------------------+---------------------+----------------------+-------------+-----------------------------------------------+
58 rows in set (0.00 sec)
MySQL [test]> SELECT *, rank() OVER (PARTITION BY col2 ORDER BY COL1) FROM PK_LP9463 WHERE col1 != 16614 and col1 < 16614;
+----------+--------------------------------------------------------------+---------------------+----------------------+-------------+-----------------------------------------------+
| COL1     | COL2                                                         | COL4                | COL3                 | COL5        | rank() OVER (PARTITION BY col2 ORDER BY COL1) |
+----------+--------------------------------------------------------------+---------------------+----------------------+-------------+-----------------------------------------------+
| -6489922 | 偏釒迳雭箳踓辻漫蒎娳僰售Ħ堼顀茝蒍龧坿岹                       | 0698-04-28 02:45:25 | -3595502563276192571 | -3.02782e38 |
                                    1 |
......
+----------+--------------------------------------------------------------+---------------------+----------------------+-------------+-----------------------------------------------+
58 rows in set (0.00 sec)

3. What did you see instead (Required)

MySQL [test]> execute stmt using @a,@b;
Empty set (0.00 sec)
MySQL [test]> SELECT *, rank() OVER (PARTITION BY col2 ORDER BY COL1) FROM PK_LP9463 WHERE col1 != 16614 and col1 < 16614;
+----------+--------------------------------------------------------------+---------------------+----------------------+-------------+-----------------------------------------------+
| COL1     | COL2                                                         | COL4                | COL3                 | COL5        | rank() OVER (PARTITION BY col2 ORDER BY COL1) |
+----------+--------------------------------------------------------------+---------------------+----------------------+-------------+-----------------------------------------------+
| -6489922 | 偏釒迳雭箳踓辻漫蒎娳僰售Ħ堼顀茝蒍龧坿岹                       | 0698-04-28 02:45:25 | -3595502563276192571 | -3.02782e38 |
                                    1 |
......
+----------+--------------------------------------------------------------+---------------------+----------------------+-------------+-----------------------------------------------+
58 rows in set (0.00 sec)

4. What is your TiDB version? (Required)

Release Version: v6.4.0-alpha-13-g556daf722
Edition: Community
Git Commit Hash: 556daf722ecb538b0b20aa800185f995e8543a4f
Git Branch: master
UTC Build Time: 2022-10-08 02:53:23
GoVersion: go1.19.1
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: unistore

[qw4990]

A minimal reproducible case:

DROP TABLE IF EXISTS `PK_LP9463`;
CREATE TABLE `PK_LP9463` (
  `COL1` mediumint NOT NULL DEFAULT '77' 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_LP9463` 
VALUES 
  (
    -6489922, '偏釒迳雭箳踓辻漫蒎娳僰售Ħ堼顀茝蒍龧坿岹', 
    '0698-04-28 02:45:25',-3595502563276192571, 
    -3.02782e38
  );

prepare stmt from 'SELECT *, rank() OVER (PARTITION BY col2 ORDER BY COL1) FROM PK_LP9463 WHERE col1 != ? AND col1 < ?;';
set @a=-8414766051197, @b=-8388608;
execute stmt using @a,@b;
set @a=16614, @b=16614;
execute stmt using @a,@b;
SELECT *, rank() OVER (PARTITION BY col2 ORDER BY COL1) FROM PK_LP9463 WHERE col1 != 16614 and col1 < 16614;

mysql> explain SELECT *, rank() OVER (PARTITION BY col2 ORDER BY COL1) FROM PK_LP9463 WHERE col1 != 16614 and col1 < 16614;;
+------------------------------+---------+-----------+-----------------+--------------------------------------------------------------------------------------+
| id                           | estRows | task      | access object   | operator info                                                                        |
+------------------------------+---------+-----------+-----------------+--------------------------------------------------------------------------------------+
| Shuffle_12                   | 3333.33 | root      |                 | execution info: concurrency:5, data sources:[TableReader_10]                         |
| └─Window_8                   | 3333.33 | root      |                 | rank()->Column#7 over(partition by test.pk_lp9463.col2 order by test.pk_lp9463.col1) |
|   └─Sort_11                  | 3333.33 | root      |                 | test.pk_lp9463.col2, test.pk_lp9463.col1                                             |
|     └─TableReader_10         | 3333.33 | root      |                 | data:TableRangeScan_9                                                                |
|       └─TableRangeScan_9     | 3333.33 | cop[tikv] | table:PK_LP9463 | range:[-inf,16614), keep order:false, stats:pseudo                                   |
+------------------------------+---------+-----------+-----------------+--------------------------------------------------------------------------------------+
5 rows in set (0.02 sec)

[qw4990]

Could you take a look at this? @fzzf678

[fzzf678]

/assign