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

Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> (p142, after a discussion of other cases)
> "One very counterintuitive consequence of this unorthodox scoping rule
> is illustrated by the following example: The result of the expression

>   select distinct sp.* from sp natural join s;

> will include columns PNO and QTY but *not* column SNO, because --
> believe it or not -- there is no column "SP.SNO" in the result of the
> join expression (indeed specifying SP.SNO in the SELECT clause would be
> a syntax error)."

> The emphasis is D&D's, not mine ;)

Hm.  After further digging in the spec, it seems that their
interpretation rests on SQL92's section 6.4 <column reference> syntax
rule 2.b.  Rule 2 in full is:
        2) If CR contains a <qualifier> Q, then CR shall appear within the           scope of one or more <table name>s
or<correlation name>s that           are equal to Q. If there is more than one such <table name> or
<correlationname>, then the one with the most local scope is           specified. Let T be the table associated with
Q.
           a) T shall include a column whose <column name> is CN.
           b) If T is a <table reference> in a <joined table> J, then CN             shall not be a common column name
inJ.
 
             Note: Common column name is defined in Subclause 7.5, "<joined             table>".

2.b strikes me as a completely unnecessary and counterintuitive
restriction.  Do D&D provide any justification for it?  I'm not
especially inclined to make our implementation substantially more
complex in order to enforce what seems a bogus restriction.

What's even more interesting is that I can find no equivalent
text in SQL99.
        regards, tom lane