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.