Bug #104127 output count is unexpected when limit clause is used in CTE
Submitted: 28 Jun 7:00 Modified: 28 Jun 7:17
Reporter: jiangtao guo Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:8.0, 8.0.25 OS:Any
Assigned to: CPU Architecture:Any
Tags: cte, limit

[28 Jun 7:00] jiangtao guo
Description:
The following query's output count is 5.

mysql> with recursive cte_174 ( col_720,col_721,col_722 ) AS ( select 1, concat('a', 1),col_5 from tbl_1 UNION  select col_720 + 1,length(col_721),length(col_722) from cte_174 where col_720 < 5 limit 13 offset 14 ) ( select 1,cte_as_179.col_720,cte_as_179.col_721,cte_as_179.  col_722 from cte_174 as cte_as_179  order by 1,2,3,4 limit 19 );
+---+---------+---------+---------+
| 1 | col_720 | col_721 | col_722 |
+---+---------+---------+---------+
| 1 |       2 | 2       | 19      |
| 1 |       2 | 2       | 2       |
| 1 |       2 | 2       | 3       |
| 1 |       2 | 2       | 5       |
| 1 |       2 | 2       | 9       |
+---+---------+---------+---------+
5 rows in set (0.00 sec)

Check the following query which doesn't have limit clause. The output count is 26. So I guess the expected output row count should be 12(26-14 = 12). 
mysql> with recursive cte_174 ( col_720,col_721,col_722 ) AS ( select 1, concat('a', 1),col_5 from tbl_1 UNION  select col_720 + 1,length(col_721),length(col_722) from cte_174 where col_720 < 5 ) ( select 1,cte_as_179.col_720,cte_as_179.col_721,cte_as_179.  col_722 from cte_174 as cte_as_179  order by 1,2,3,4 );
+---+---------+---------+---------------------+
| 1 | col_720 | col_721 | col_722             |
+---+---------+---------+---------------------+
| 1 |       1 | a1      | NULL                |
| 1 |       1 | a1      | fLGHlZxigg          |
| 1 |       1 | a1      | Fp                  |
| 1 |       1 | a1      | gVyBgYCfNYwPGCV     |
| 1 |       1 | a1      | JmquiuYPAfc         |
| 1 |       1 | a1      | KVLSnKWIx           |
| 1 |       1 | a1      | nRUdDUeqJVW         |
| 1 |       1 | a1      | pFk                 |
| 1 |       1 | a1      | VeqxA               |
| 1 |       1 | a1      | XWlZIWPNZJOxrxlJMnY |
| 1 |       2 | 2       | NULL                |
| 1 |       2 | 2       | 10                  |
| 1 |       2 | 2       | 11                  |
| 1 |       2 | 2       | 15                  |
| 1 |       2 | 2       | 19                  |
| 1 |       2 | 2       | 2                   |
| 1 |       2 | 2       | 3                   |
| 1 |       2 | 2       | 5                   |
| 1 |       2 | 2       | 9                   |
| 1 |       3 | 1       | NULL                |
| 1 |       3 | 1       | 1                   |
| 1 |       3 | 1       | 2                   |
| 1 |       4 | 1       | NULL                |
| 1 |       4 | 1       | 1                   |
| 1 |       5 | 1       | NULL                |
| 1 |       5 | 1       | 1                   |
+---+---------+---------+---------------------+
26 rows in set (0.00 sec)

How to repeat:
drop table if exists tbl_0, tbl_1;
create table tbl_0 ( col_0 int , col_1 char(140) collate utf8mb4_unicode_ci default 'vLzh' , col_2 int default 536981947 , col_3 char(243) collate utf8mb4_bin not null ) collate utf8mb4_general_ci ;
create table tbl_1 ( col_4 int not null , col_5 char(243) collate utf8_unicode_ci , col_6 char(47) collate utf8mb4_bin , col_7 int default -881837096 ) collate utf8mb4_bin ;
insert into tbl_0 values ( -1047825563,'QhTPPAM',1192874192,'kdbZbthC' );
insert into tbl_0 values ( 407070855,'s',-475957296,'FpqtJxgRPp' );
insert into tbl_0 values ( -824941213,'qMVFAzKSGBmUZ',1669461,'sieVsD' );
insert into tbl_0 values ( -439678436,'k',1902528075,'wwLrTkiHqeWOHBtmch' );
insert into tbl_0 values ( 1271147859,'LCHLdx',-1696224809,'IJFxdbAHexBkWXJH' );
insert into tbl_0 values ( -2123312160,'VxGEbv',763334656,'CECFzh' );
insert into tbl_0 values ( 340457574,'zPxEzHVm',-1116114202,'KM' );
insert into tbl_0 values ( -1056338425,'BTuWdz',62981333,'zRfJshwG' );
insert into tbl_0 values ( 1157852143,'zkOllEN',-487136414,'JbBZrAXtroQd' );
insert into tbl_0 values ( -1459729236,null,1706442279,'hpHTiexk' );
insert into tbl_1 values ( 496218734,null,'rDAWgRYECTTFeiDU',-633686292 );
insert into tbl_1 values ( 639666231,'XWlZIWPNZJOxrxlJMnY','gyjapAfhvKhu',-999773359 );
insert into tbl_1 values ( -1880044148,'gVyBgYCfNYwPGCV','kQDwtVENMZ',413487081 );
insert into tbl_1 values ( 656223075,'pFk','UjRDPWJpEcfHCHrv',-2457643 );
insert into tbl_1 values ( -38038629,'KVLSnKWIx','exogzerZVR',497204569 );
insert into tbl_1 values ( 969632542,'Fp','hSStwtj',88455518 );
insert into tbl_1 values ( 2048471487,'VeqxA','GngftCNpAYNgF',-995435484 );
insert into tbl_1 values ( 1685522930,'JmquiuYPAfc','fWoNkL',-448434787 );
insert into tbl_1 values ( 920293933,'fLGHlZxigg','QKkAW',605375882 );
insert into tbl_1 values ( -953903066,'nRUdDUeqJVW','qb',1622361219 );
with recursive cte_174 ( col_720,col_721,col_722 ) AS ( select 1, concat('a', 1),col_5 from tbl_1 UNION  select col_720 + 1,length(col_721),length(col_722) from cte_174 where col_720 < 5 limit 13 offset 14 ) ( select 1,cte_as_179.col_720,cte_as_179.col_721,cte_as_179.  col_722 from cte_174 as cte_as_179  order by 1,2,3,4 limit 19 );
[28 Jun 7:17] MySQL Verification Team
Hello jiangtao,

Thank you for the report and test case.

regards,
Umesh