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

>>>> So there are two issues here which I hope to clarify: scoping
>>>> on joins, and NATURAL and USING join column sets.

I've been looking some more at this business, and I have found one of
the reasons that I was confused. The SQL92 spec says (6.3 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.

I mistakenly read this with the assumption that <derived table> means
a sub-SELECT. It does mean that, but it also means a <joined table>,
*if and only if* that joined table is labeled with a <correlation name>.
The relevant productions are:

<table reference> ::=
<table name> [ [ AS ] <correlation name>
[ <left paren> <derived column list> <right paren> ] ]
| <derived table> [ AS ] <correlation name>
[ <left paren> <derived column list> <right paren> ]
| <joined table>

<derived table> ::= <table subquery>

<table subquery> ::= <subquery>

<subquery> ::= <left paren> <query expression> <right paren>

<query expression> ::=
<non-join query expression>
| <joined table>

So "(<joined table>) AS foo" has a <subquery> but "<joined table>" doesn't.
AFAICT, this means that table references defined within the join are
invisible outside "(<joined table>) AS foo", but they are visible
outside a plain "<joined table>". This is more than a tad bizarre
... but it explains the examples you quoted from Date and Darwen.

However, as long as a table reference is visible, I think that the
set of qualified column names available from it should not depend on
whether it came from inside a JOIN expression or not. Comments?

regards, tom lane