Re: Table name scope (was Re: [BUGS] 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>
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.
 

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>
<derivedcolumn list> <right paren> ] ]             | <derived table> [ AS ] <correlation name>                   [
<leftparen> <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