Re: CTE bug?

I wrote:
> David Fetter <david(at)fetter(dot)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