incorrect result using cte with cornel value

[wjhuang2016]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE `t5d31e16c` (
  `col_33` tinyint(1) DEFAULT '0',
  `col_34` char(133) CHARACTER SET gbk COLLATE gbk_bin DEFAULT 'hn*尫q內hhZ斔W祝蔒M',
  `col_35` bigint(20) unsigned NOT NULL,
  UNIQUE KEY `idx_16` (`col_35`,`col_33`,`col_34`),
  KEY `idx_17` (`col_35`),
  UNIQUE KEY `idx_24` (`col_34`(1),`col_33`),
  UNIQUE KEY `idx_28` (`col_34`(1))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
INSERT INTO `t5d31e16c` VALUES(0,'Hix黁屙',10936307246032683432),(0,'怏0常x顣uOT蠮r欏肪$Z',9903439567313171036),(0,'2崮G',9903439567313171036),(0,'@勨鸤偙韻',9903439567313171036),(1,'elf(g铫j!bnUj癏灊g硥彜=I',6392638880944062756),(0,'熐Pg',9903439567313171036),(0,'-f邞8o&颃m涉锤璆^oV發d舓撽x',9903439567313171036),(0,'8$詆秏',9903439567313171036),(0,'F抾屧蹠gT缹CZ@8~m麼h16c',9903439567313171036),(0,NULL,9903439567313171036),(0,'%Hw5葘vo蒇5mM',9903439567313171036),(0,'駾鵩*F墚r^iw0暸囥稀H騌UU',9903439567313171036),(0,'XB~2瞾5',9903439567313171036),(0,'3糰+壙D&6嬉id*栬ri',9903439567313171036),(0,'(4RnYF姞Gf盰邡禭!菰f-',9903439567313171036),(0,'M轫~t+4羂舀k_6遚魕B@鬃雤f',9903439567313171036),(0,'验-9E奏濻U跱肹StT*Ve5@',9903439567313171036),(0,'&',9903439567313171036),(0,'x^枷2L驗怲癠Y湣栓麛编xDf體',14135777245801028053),(0,'1e9讔H拊Z綺$f廾!杮',3372031430429443879),(0,'qG问志C郎Iyyt',6195513455144692336),(0,'寫uRtsj叀蹸赎U盌obZS蛫J熚J',9928923883776507807),(0,'PN搈W=撐鲯埭单猗fw飻',1895768644557977098),(NULL,'殐4&棷~(@^弖0',10373262373129681312),(1,'IUJ醾憄7rZ锃覕柣怬',6273793030265797059),(1,'鷉',7060569570842450162),(0,'j梀續C7o圩9',1937050996502043761),(0,'額丬镆D',1748977837145249438),(1,'穱AZw&阒e_労巐k0uO',12100190738305597674),(0,'o杔阏n婁s2',8766555686027910604),(1,'JxLn^TWw愘g5BJ斕',775777131081350272),(0,'',7676612183741020496),(0,NULL,876355662772462532),(0,'蔇',4087476786454981078);

WITH `cte_2151` (`col_6516`, `col_6517`, `col_6518`, `col_6519`, `col_6520`) AS (SELECT `t5d31e16c`.`col_35` AS `r0`,SUBSTRING(`t5d31e16c`.`col_35`, 0) AS `r1`,`t5d31e16c`.`col_35` AS `r2`,REVERSE(`t5d31e16c`.`col_35`) AS `r3`,MID(`t5d31e16c`.`col_35`, 1, 3) AS `r4` FROM `t5d31e16c` GROUP BY `t5d31e16c`.`col_35` HAVING `t5d31e16c`.`col_35`<13227180432975340348) (SELECT 1 FROM `cte_2151` WHERE NOT (`cte_2151`.`col_6518`<=9223372036854775807) LIMIT 4493839);

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

+---+
| 1 |
+---+
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
+---+
5 rows in set (0.06 sec)

3. What did you see instead (Required)

mysql> WITH `cte_2151` (`col_6516`, `col_6517`, `col_6518`, `col_6519`, `col_6520`) AS (SELECT `t5d31e16c`.`col_35` AS `r0`,SUBSTRING(`t5d31e16c`.`col_35`, 0) AS `r1`,`t5d31e16c`.`col_35` AS `r2`,REVERSE(`t5d31e16c`.`col_35`) AS `r3`,MID(`t5d31e16c`.`col_35`, 1, 3) AS `r4` FROM `t5d31e16c` GROUP BY `t5d31e16c`.`col_35` HAVING `t5d31e16c`.`col_35`<13227180432975340348) (SELECT 1 FROM `cte_2151` WHERE NOT (`cte_2151`.`col_6518`<=9223372036854775807) LIMIT 4493839);
Empty set (0.02 sec)

mysql> desc WITH `cte_2151` (`col_6516`, `col_6517`, `col_6518`, `col_6519`, `col_6520`) AS (SELECT `t5d31e16c`.`col_35` AS `r0`,SUBSTRING(`t5d31e16c`.`col_35`, 0) AS `r1`,`t5d31e16c`.`col_35` AS `r2`,REVERSE(`t5d31e16c`.`col_35`) AS `r3`,MID(`t5d31e16c`.`col_35`, 1, 3) AS `r4` FROM `t5d31e16c` GROUP BY `t5d31e16c`.`col_35` HAVING `t5d31e16c`.`col_35`<13227180432975340348) (SELECT 1 FROM `cte_2151` WHERE NOT (`cte_2151`.`col_6518`<=9223372036854775807) LIMIT 4493839);
+------------------------+---------+------+---------------+----------------------------------------------------------------------------------------------+
| id                     | estRows | task | access object | operator info                                                                                |
+------------------------+---------+------+---------------+----------------------------------------------------------------------------------------------+
| Projection_15          | 25.38   | root |               | 1->Column#20                                                                                 |
| └─Limit_18             | 25.38   | root |               | offset:0, count:4493839                                                                      |
|   └─StreamAgg_22       | 25.38   | root |               | group by:test.t5d31e16c.col_35, funcs:firstrow(test.t5d31e16c.col_35)->test.t5d31e16c.col_35 |
|     └─TableDual_27     | 33.83   | root |               | rows:0                                                                                       |
+------------------------+---------+------+---------------+----------------------------------------------------------------------------------------------+
4 rows in set (0.03 sec)

4. What is your TiDB version? (Required)

12833e8

[wjhuang2016]

Maybe duplicate with #50051

[aytrack]

change the condition from <=9223372036854775807 to <=9223372036854775807 returns the correct result

[18:15:37]TiDB root:test> desc WITH `cte_2151` (`col_6516`, `col_6517`, `col_6518`, `col_6519`, `col_6520`) AS (SELECT `t5d31e16c`.`col_35` AS `r0`,SUBSTRING(`t5d31e16c`.`col_35`, 0) AS `r1`,`t5d31e16c`.`col_35` AS `r2`,REVERSE(`t5d31e16c`.`col_35`) AS `r3`,MID(`t5d31e16c
                       -> `.`col_35`, 1, 3) AS `r4` FROM `t5d31e16c` GROUP BY `t5d31e16c`.`col_35` HAVING `t5d31e16c`.`col_35`<13227180432975340348) (SELECT 1 FROM `cte_2151` where not (`cte_2151`.`col_6518`<=9223372036854775806));
+---------------------------+---------+-----------+---------------------------------------+---------------------------------------------------------------------------------+
| id                        | estRows | task      | access object                         | operator info                                                                   |
+---------------------------+---------+-----------+---------------------------------------+---------------------------------------------------------------------------------+
| Projection_12             | 200.00  | root      |                                       | 1->Column#20                                                                    |
| └─StreamAgg_30            | 200.00  | root      |                                       | group by:test.t5d31e16c.col_35, funcs:firstrow(Column#30)->Column#22            |
|   └─IndexReader_31        | 200.00  | root      |                                       | index:StreamAgg_16                                                              |
|     └─StreamAgg_16        | 200.00  | cop[tikv] |                                       | group by:test.t5d31e16c.col_35, funcs:firstrow(1)->Column#30                    |
|       └─IndexRangeScan_29 | 250.00  | cop[tikv] | table:t5d31e16c, index:idx_17(col_35) | range:(9223372036854775806,13227180432975340348), keep order:true, stats:pseudo |
+---------------------------+---------+-----------+---------------------------------------+---------------------------------------------------------------------------------+
5 rows in set
Time: 0.004s
[18:15:41]TiDB root:test> desc WITH `cte_2151` (`col_6516`, `col_6517`, `col_6518`, `col_6519`, `col_6520`) AS (SELECT `t5d31e16c`.`col_35` AS `r0`,SUBSTRING(`t5d31e16c`.`col_35`, 0) AS `r1`,`t5d31e16c`.`col_35` AS `r2`,REVERSE(`t5d31e16c`.`col_35`) AS `r3`,MID(`t5d31e16c
                       -> `.`col_35`, 1, 3) AS `r4` FROM `t5d31e16c` GROUP BY `t5d31e16c`.`col_35` HAVING `t5d31e16c`.`col_35`<13227180432975340348) (SELECT 1 FROM `cte_2151` where not (`cte_2151`.`col_6518`<=9223372036854775807));
+------------------+---------+------+---------------+--------------------------------------------------------------+
| id               | estRows | task | access object | operator info                                                |
+------------------+---------+------+---------------+--------------------------------------------------------------+
| Projection_12    | 200.00  | root |               | 1->Column#20                                                 |
| └─StreamAgg_16   | 200.00  | root |               | group by:test.t5d31e16c.col_35, funcs:firstrow(1)->Column#22 |
|   └─TableDual_21 | 250.00  | root |               | rows:0                                                       |
+------------------+---------+------+---------------+--------------------------------------------------------------+
3 rows in set

[hawkingrei]

It is now fixed on master.