Re: Learning SQL: nested CTE and UNION

Adam Mackler <adammackler(at)gmail(dot)com> writes:
> ... But this does not work:

> WITH outmost AS (
> SELECT 1
> UNION (WITH innermost as (SELECT 2)
> SELECT * FROM innermost
> UNION SELECT 3)
> )
> SELECT * FROM outmost;

> Result:
> ERROR: relation "innermost" does not exist
> LINE 4: SELECT * FROM innermost

This is a bug :-(. The parse analysis code seems to think that WITH can
only be attached to the top level or a leaf-level SELECT within a set
operation tree; but the grammar follows the SQL standard which says
no such thing. The WITH gets accepted, and attached to the
intermediate-level UNION which is where syntactically it should go,
and then it's entirely ignored during parse analysis. Will see about
fixing it.

regards, tom lane