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
