Re: CTE bug?

David Fetter <david(at)fetter(dot)org> writes:
> WITH RECURSIVE t1(n) AS (
> VALUES(2)
> UNION ALL
> SELECT n+1 FROM t1 WHERE n < 1000
> ),
> t2 (n, i) AS (
> SELECT 2*n+2, 2
> FROM t1 WHERE 2*n+2 <= 1000
> UNION ALL
> WITH t3(k) AS (
> SELECT max(i) FROM t2
> )
> SELECT k*n+k, k
> FROM
> t1
> CROSS JOIN
> t3
> WHERE k*n+k <= 1000
> )
> SELECT * FROM t1 EXCEPT SELECT n FROM t2 ORDER BY 1;
> ERROR: syntax error at or near "WITH t3"
> LINE 10: WITH t3(k) AS (
> ^

I think you need parentheses to have WITH attached to a member of a
UNION. In
WITH (...) SELECT x UNION SELECT y
I'm pretty sure the WITH attaches to the whole union, not the first
member.

regards, tom lane