Re: [PATCH] Allow multiple recursive self-references

On 31.08.21 16:48, Denis Hirn wrote:
> The documentation was not up to date anymore with the most recent changes.
> This version of the patch fixes that.

I studied up on the theory and terminology being discussed here.  I 
conclude that what the latest version of this patch is doing (allowing 
multiple recursive references, but only in a linear-recursion way) is 
sound and useful.

I haven't looked closely at the implementation changes yet.  I'm not 
very familiar with that part of the code, so it will take a bit longer. 
Perhaps you could explain what you are doing there, either in email or 
(even better) in the commit message.

What I think this patch needs is a lot more test cases.  I would like to 
see more variants of different nestings of the UNION branches, some 
mixtures of UNION ALL and UNION DISTINCT, joins of the recursive CTE 
with regular tables (like in the flights-and-trains examples), as well 
as various cases of what is not allowed, namely showing that it can 
carefully prohibit non-linear recursion.  Also, in one instance you 
modify an existing test case.  I'm not sure why.  Perhaps it would be 
better to leave the existing test case alone and write a new one.

You also introduce this concept of reshuffling the UNION branches to 
collect all the recursive terms under one subtree.  That could use more 
testing, like combinations like nonrec UNION rec UNION nonrec UNION rec 
etc.  Also, currently a query like this works

WITH RECURSIVE t(n) AS (
     VALUES (1)
UNION ALL
     SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;

but this doesn't:

WITH RECURSIVE t(n) AS (
     SELECT n+1 FROM t WHERE n < 100
UNION ALL
     VALUES (1)
)
SELECT sum(n) FROM t;

With your patch, the second should also work, so let's show some tests 
for that as well.