Re: Learning SQL: nested CTE and UNION

Hi:

I'm trying to learn SQL, using PostgreSQL 9.1.3. I would like to understand some behavior that strikes me as inconsistent. To wit:

This works:

WITH innermost AS (SELECT 2)
SELECT * FROM innermost
UNION SELECT 3;

I get this:

?column?
----------
2
3
(2 rows)

This works:

WITH outmost AS (
(WITH innermost AS (SELECT 2)
SELECT * FROM innermost)
)
SELECT * FROM outmost;

Result:

?column?
----------
2
(1 row)

This also works:

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

I get this:

?column?
----------
1
2
(2 rows)

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

To my way of thinking, either the last one should succeed or one of the other ones should fail. I don't see the pattern. Is there some general rule that would enable me to predict what combinations of nested CTEs and UNIONs will or will not work?

Thanks very much.

--
Adam Mackler

I hope this gives you a clue.

This works:

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

> 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;
>
>
>

--
Jonatan Reiners

Thanks for your reply. I think I should have been clearer. I'm trying to understand why the error message says "relation "innermost" does not exist" when clearly it does (seem to). Let me try asking using different examples:

I can UNIONize two selections that refer to the same CTE, like this:

WITH foo AS (SELECT 1 AS mycol) SELECT mycol FROM foo UNION SELECT mycol+1 FROM foo;

and I don't need to put everything before the UNION inside parentheses.

The change you made to the query I posted is to move a parenthesis so that the second part of the union cannot refer to the CTE the way the line above does.

Put another way, here is the non-working query that I posted in my last message, with one minor change to reference a column in the purportedly nonexistent relation:

WITH outmost AS (
SELECT 1
UNION (WITH innermost as (SELECT 2 as mycol)
SELECT mycol FROM innermost
UNION SELECT mycol+1 from innermost)
)
SELECT * FROM outmost;

Can you make this one work by moving a parenthesis?

Moreover, removing the "SELECT 1 UNION" starting on the second line will make the error message about "innermost" not existing go away, which does not make sense to me. There seems to be some strange (to me) interaction between the CTEs and UNION. If this is something you understand I would most gratefully appreciate any explanation or guidance that could help me to understand.

Note, I'm not trying to just get something to happen, I'm trying to understand why I am getting a message telling me that "innermost" does not exist, when it looks to me as if it does exist.

Thank you.

On Tue, Jul 31, 2012 at 9:57 AM, Jonatan Reiners <jreiners(at)encc(dot)de> wrote:
>
> I hope this gives you a clue.
>
> This works:
>
> WITH outmost AS (
> SELECT 1
> UNION (WITH innermost as (SELECT 2)
> SELECT * FROM innermost
> )UNION SELECT 3
> )
> SELECT * FROM outmost;
>
> 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;
>
>
> --
> Jonatan Reiners
>

--
Adam Mackler

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