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

> > (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.

Hmm. istm that the D&D interpretation is entirely clear, and that for
NATURAL and USING joins there is no other way to carry along join
results as intermediate "tables". If

select * from t1 natural join t2;

produces, say, three columns, how can any other specification of the
target list using only wildcards produce *more* columns? In particular,
how can

select t1.*, t2.* from t1 natural join t2;

produce columns from t1 and t2 which are *not present* in the join "t1
natural join t2"?

> What's even more interesting is that I can find no equivalent
> text in SQL99.

Of course. When they bloated the spec by a factor of three or four, they
had to leave out the clear parts to save space ;)

I'm pretty sure that the sections I quoted (in 7.7.7 in the draft
document I have -- hopefully the same as what you have available?)
cover this topic. In particular, NATURAL and USING joins are not the
same as other inner or outer joins in the resulting set of available
columns. So there are two issues here which I hope to clarify: scoping
on joins, and NATURAL and USING join column sets.

- Thomas