Re: Learning SQL: nested CTE and UNION

Adam Mackler <adammackler@gmail.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