30

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

This works:

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

Result:

?column? 
----------
        2

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

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Adam Mackler
  • 1,980
  • 1
  • 18
  • 32
  • Though your final query looks awkward, it should be Ok, IMHO. It could be a precedence/associativity error in the parser. There are some semantic restrictions (No nested recursive CTEs, IIRC); maybe the parser is too picky, or too trigger-happy. Personally, I use a lot of nested CTEs (up to 4 levels deep), but I rarely use UNION, except for in the recursive CTEs. – wildplasser Jul 31 '12 at 16:24
  • 2
    @AdamMackler you should put that in as the answer to your own question – araqnid Jul 31 '12 at 19:55
  • 4
    Tom Lane acknowledging you found a bug, is something like an official approval that you asked a very good question. Please post what you got from the list as answer and be sure to add a link to the thread. – Erwin Brandstetter Aug 01 '12 at 00:01

1 Answers1

28

The mystery is solved: the behavior I was observing is a known bug. I sent the same original post to a PostgreSQL-specific list and got this answer:

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

http://archives.postgresql.org/pgsql-novice/2012-07/msg00113.php

Adam Mackler
  • 1,980
  • 1
  • 18
  • 32
  • 5
    Seems like this has been fixed in 9.2 beta3. I quote the newsletter: `* Fix WITH issue with set operations (UNION/INTERSECT/EXCEPT)`. – Erwin Brandstetter Aug 07 '12 at 08:12
  • 4
    I just installed 9.2beta3 and yes, the nonworking command in my original post does now work as expected. Thank you for the heads up. – Adam Mackler Aug 11 '12 at 23:38
  • 2
    Cool! Thank you for tracking this down! Your question deserves more upvotes. :) BTW, it's encouraged to accept your own (correct) answer in such a case. – Erwin Brandstetter Aug 12 '12 at 00:53