Hey Pantelis,
I am not at all sure what the standard says about such recursion [...]
as far as I know, the standard does not constraint the number of self-references
of recursive common table expressions. However, I could be wrong here.
[...] but it looks like the two t's are treated in your patch as the same incarnation of the table, not as a cross join of two incarnations.
That's right and – as far as I'm concerned – it's expected behaviour. The patch only allows the recursive
union operator's working table to be read more than once. All self-references read exactly the same rows
in each iteration. You could basically accomplish the same thing with another CTE like this:
WITH RECURSIVE t(n) AS (
VALUES(1)
UNION ALL
(WITH wt AS (SELECT * FROM t)
SELECT wt.n+f.n
FROM wt, wt AS f
WHERE wt.n < 100)
) SELECT * FROM t;
But honestly, this feels more like a hack than a solution to me. The entire working table is
materialized by the (non recursive) common table expression wt, effectively doubling the
memory consumption of the query. This patch eliminates this intermediate materialization.
I don't think any other DBMS has implemented this, except MariaDB. Tested here:
There are a few recent DBMSs that I know of that support this: HyPer, Umbra, DuckDB, and NoisePage.
I'm sure there are some more examples. Still, you are right, many other DBMSs do not support this – yet.