Table name scope (was Re: Outer joins aren't working with views) - Mailing list pgsql-bugs

Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
>> It works for me:
>> regression=# select t1.*, t2.* from t1 natural left outer join t2;
>> id | id2 | id
>> ----+-----+----
>> 1 |   1 |  1

> My recollection is that SQL9x requires that the join result lose the
> link to the original table names. That is,
>   select id, id2 from t1 natural left outer join t2;
> is legal, but
>   select t1.id, ...
> is not.

Hm.  This is one of the areas that I had put down on my personal TODO
list as needing a close look before release.  So, let's get to it.

My first scan of SQL92 looks like our current behavior is right.
I find these paras that seem to be relevant to the scope of a
<correlation name> (ie, a table alias):

5.4 Names and identifiers, syntax rule 12:
        12)An <identifier> that is a <correlation name> is associated with           a table within a particular scope.
Thescope of a <correlation           name> is either a <select statement: single row>, <subquery>, or           <query
specification>(see Subclause 6.3, "<table reference>").           Scopes may be nested. In different scopes, the same
<correlation          name> may be associated with different tables or with the same           table.
 

6.3 <table reference>, syntax rule 2:
        2) Case:
           a) If a <table reference> TR is contained in a <from clause> FC             with no intervening <derived
table>,then the scope clause             SC of TR is the <select statement: single row> or innermost             <query
specification>that contains FC. The scope clause of             the exposed <correlation name> or exposed <table name>
ofTR             is the <select list>, <where clause>, <group by clause>, and             <having clause> of SC,
togetherwith the <join condition> of             all <joined table>s contained in SC that contains TR.
 
           b) Otherwise, the scope clause SC of TR is the outermost <joined             table> that contains TR with no
intervening<derived table>.             The scope of the exposed <correlation name> or exposed <table             name>
ofTR is the <join condition> of SC and of all <joined             table>s contained in SC that contain TR.
 

(Note that <derived table> means subselect-in-FROM, cf 6.3 and 7.11.)

The first and second items here seem to be perfectly clear that the
names t1 and t2 have scope across the whole SELECT statement and are not
hidden within the <joined table> formed by the OUTER JOIN clause.

On the other hand, the third item leaves me confused again.  I don't
see how it applies at all, ie, when is the "If" of 2(a) ever false?
How is it *possible* to have a <table reference> that's not directly
contained in a <from clause>?  The business about a <derived table>
seems like horsepucky, because a table ref inside a subselect would be
contained in the subselect's from-clause and its scope would be that
subselect.  Where in the spec does it allow a table reference that's
not in a from-clause?  (Our PostQuel extensions do not count ;-))

It'd be useful to check the above example against Oracle and other
implementations, but the parts of the spec that I can follow seem
to say that we've got the right behavior now.
        regards, tom lane