Обсуждение: CTE bug?

Folks,

While working to write the Sieve of Eratosthenes using CTEs, I ran
across a strange error, namely that it appears I'm not allowed to nest
WITH.  Is this a bug?

Cheers,
David.

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
WHEREk*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 (            ^

-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter <david@fetter.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.  InWITH (...) SELECT x UNION SELECT y
I'm pretty sure the WITH attaches to the whole union, not the first
member.
        regards, tom lane