Table name scope (was Re: Outer joins aren't working with views)

Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)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. The scope 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> of TR
is the <select list>, <where clause>, <group by clause>, and
<having clause> of SC, together with 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> of TR 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