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.
--
Denis Hirn
