MySQL Bugs: #92857: Support LIMIT inside definition of recursive CTE

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

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

Documented fix as follows in the MySQL 8.0.19 changelog:

    Previously, it was not possible to use LIMIT in the recursive
    SELECT part of a recursive common table expression (CTE). LIMIT
    is now supported in such cases, along with an optional OFFSET
    clause. An example of such a recursive CTE is shown here:

WITH RECURSIVE cte AS  (
  SELECT CAST("x" AS CHAR(100)) AS a FROM DUAL
  UNION ALL 
  SELECT CONCAT("x",cte.a) FROM qn 
    WHERE LENGTH(cte.a) &lt; 10
    LIMIT 3 OFFSET 2;
)
SELECT * FROM cte; 

    Specifying LIMIT in this fashion can make execution of the CTE
    more efficient than doing so in the outermost SELECT, since only
    the requested number of rows is generated.

    For more information, see "Recursive Common Table Expressions".

Closed.