Re: CTE bug?

I wrote:
> David Fetter <david@fetter.org> writes:
>> WITH RECURSIVE t(j) AS (
>>     WITH RECURSIVE s(i) AS (
>>         VALUES (1)
>>     UNION ALL
>>         SELECT i+1 FROM s WHERE i < 10
>>     ) SELECT i AS j FROM s
>> UNION ALL
>>     SELECT j+1 FROM t WHERE j < 10
>> )
>> SELECT * FROM t;
>> ERROR:  relation "s" does not exist
>> LINE 6:     ) SELECT i AS j FROM s
>>                                  ^
>> Shouldn't this work?

> Huh, nice test case.  It looks like it's trying to do the "throwaway
> parse analysis" of the nonrecursive term (around line 200 of
> parse_cte.c) without having analyzed the inner WITH clause.  We could
> probably fix it by doing a throwaway analysis of the inner WITH too
> ... but ... that whole throwaway thing is pretty ugly and objectionable
> from a performance standpoint anyhow.  I wonder if it wouldn't be better
> to refactor so that transformSetOperationStmt knows when it's dealing
> with the body of a recursive UNION and does the analyzeCTETargetList
> business after having processed the first UNION arm.

I've committed a fix along those lines.  Too late for 8.4.1
unfortunately :-(.  In the meantime, you could work around the
problem in this particular case with some more parentheses:

WITH RECURSIVE t(j) AS ( (   WITH RECURSIVE s(i) AS (       VALUES (1)   UNION ALL       SELECT i+1 FROM s WHERE i < 10
 ) SELECT i AS j FROM s )
 
UNION ALL   SELECT j+1 FROM t WHERE j < 10
)
SELECT * FROM t;
        regards, tom lane