Re: [PATCH] Allow multiple recursive self-references

On 21.09.21 13:35, Denis Hirn wrote:
>> Also, currently a query like this works [...] 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.
> With just the tree rotation, the second query can not be fixed. The order of two
> nodes is never changed. And I think that this is a good thing. Consider the
> following query:
>
>> WITH RECURSIVE t(n) AS (
>> VALUES (1)
>> UNION ALL
>> SELECT n+1 FROM t WHERE n < 100
>> UNION ALL
>> VALUES (2)
>> ) SELECT * FROM t LIMIT 100;
> If we'd just collect all non-recursive UNION branches, the semantics of the
> second query would change. But changing the semantics of a query (or preventing
> certain queries to be formulated at all) is not something I think this patch
> should do. Therfore – I think – it's appropriate that the second query fails.

I have been studying this a bit more. I don't understand your argument
here. Why would this query have different semantics than, say

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

The order of UNION branches shouldn't be semantically relevant.

I suppose you put the LIMIT clause in there to make some point, but I
didn't get it. ;-)

I also considered this example:

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

This works fine without and with your patch.

This should be equivalent:

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

But this runs forever in current PostgreSQL 14 and 15. I'd have
expected your patch to convert this form to the previous form, but it
doesn't.

I'm having difficulties understanding which subset of cases your patch
wants to address.