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

Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)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
<correlation name>, 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 in J.

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