MySQL, recursive CTE. How does it work in such a syntactic form?

Question

Common recursive CTE looks like:

WITH RECURSIVE
cte AS (
  SELECT 1 id
  UNION ALL
  SELECT id + 1 FROM cte WHERE id < 1000
)
SELECT COUNT(*) FROM cte;

This form is well-described in Reference Manual.

But the same output can be produced while using another synthactic form of the CTE:

WITH RECURSIVE
cte AS (
  SELECT 1 id
  UNION ALL
  SELECT id + 1 FROM cte LIMIT 1000
)
SELECT COUNT(*) FROM cte;

This alternative form is not described in RM. Moreover, it does not match the next part of the description in RM:

Each iteration of the recursive part operates only on the rows produced by the previous iteration. If the recursive part has multiple query blocks, iterations of each query block are scheduled in unspecified order, and each query block operates on rows that have been produced either by its previous iteration or by other query blocks since that previous iteration's end.

Each iteration of the recursive part produces one row. There is nothing in the recursive part that would lead to the fact that a new row will NOT be created at the next step of the recursion.

But the recursion nevertheless ends, and the total number of rows corresponds to the LIMIT value.

How does recursive CTE work in such a syntactic form?

DEMO fiddle

---

UPDATE - copied from the comments.

Reason is that LIMIT applies to the UNION statement, not to the single queries (unless you use parentheses). – lemon

It seems obvious. But I can't find where RM describes that the whole, combined, result is being investigated.

And one more thing - if the CTE recursion stops, then the 1001st row is generated, which is then discarded using LIMIT, this does not lead to new rows and stops the recursion.. but I can't find the reason why exactly the row that is created on the most last iteration is discarded using LIMIT deterministically.

Answer 1

You can find reference to the usage of LIMIT in the context of recursive queries down the same Reference Manual page you were looking at. Referencing it:

Prior to MySQL 8.0.19, the recursive SELECT part of a recursive CTE also could not use a LIMIT clause. This restriction is lifted in MySQL 8.0.19, and LIMIT is now supported in such cases, along with an optional OFFSET clause. The effect on the result set is the same as when using LIMIT in the outermost SELECT, but is also more efficient, since using it with the recursive SELECT stops the generation of rows as soon as the requested number of them has been produced.

These constraints do not apply to the nonrecursive SELECT part of a recursive CTE.

Making a summary, LIMIT clause didn't make sense inside a recursive query if applied to each recursive step (as long as each iteration may bring an amount of rows which is less than the defined limit, without stopping the recursion itself), reason why it was not considered in MySQL 5. Developers decided to include it in v8 to allow the limitation of the full amount of rows of the recursive step only, hence non iterated as part of the recursion itself.

An additional hint of this is the fact that if you try including the LIMIT clause inside parentheses, making the LIMIT clause specifically relegated to the recursive query part as follows:

WITH RECURSIVE
cte AS (
  SELECT 1 id
  UNION ALL
  (SELECT id + 1 FROM cte LIMIT 1000)
)
SELECT COUNT(*) FROM cte;

you would get such error to be fired:

This version of MySQL doesn't yet support 'ORDER BY / LIMIT / SELECT DISTINCT in recursive query block of Common Table Expression'