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 );
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 );