Bug #92857 Support LIMIT inside definition of recursive CTE
Submitted: 19 Oct 2018 9:22
Reporter: Guilhem Bichot Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:8.0.14 OS:Any
Assigned to: CPU Architecture:Any

[19 Oct 2018 9:22] Guilhem Bichot
Description:
Got a request for placing a global limit on the CTE:

with recursive qn as
  (
   select cast("x" as char(100)) as a from dual
   union all select concat("x",qn.a) from qn where length(qn.a)<10
   limit 3 <<<<<<<
   )
select * from qn;
would return 3 strings (x,xx,xxx) instead of 10 (x,xx,xxx,etc).
(right now it throws an error because unsupported).

It would be a limit on the total number of rows in the CTE, not on the number of rows that each iteration may produce. Global ORDER BY would still be rejected, as well as ORDER BY or LIMIT in the recursive member(s).

Note that there is a bit of non-predictability in the order where rows are created in the CTE: the recursive member (after UNION ALL, above) is allowed to "eat" previously produced rows in the order it prefers. So one cannot predict that, among rows produced by iteration #N, it's the first-produced of them which will produce the first row in iteration #N+1. With LIMIT, this can be of importance or not, depending on the use case.

Today in MySQL, instead of limiting the number of rows as above, one can already introduce a "depth level" column in the CTE and stop recursion at a certain depth. That is a more logical and robust solution, though takes more SQL writing.

How to repeat:
see above.

Suggested fix:
I'll look at it together with:
bug#79340 UNION ALL ... LIMIT 1 EVALUATES TOO MANY ROWS IN 5.5/6 AND TWICE MORE IN 5.7