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

Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)edu> writes:
> 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"?

Very easily ;-)

>> 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 ;)

Or they realized they blew it the first time.

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

There's no question about what happens as far as the output of the join
is concerned. However, 7.7.7 does not say word one about what is
implied by direct access (ie, qualified-name access) to the component
tables of the join.

I've been through the SQL99 draft again, and there is quite clearly NOT
any restriction corresponding to the old 6.4.2.b; so under SQL99 it is
legal to refer to A.ID and B.ID. However, they do still have the idea
that A.* should omit ID: 7.11 <query specification> syntax rule 7.g.i
(concerning expansion of qualified asterisks) says

i) If the basis is a <table or query name> or <correlation
name>, then let TQ be the table associated with the basis.
The <select sublist> is equivalent to a <value expression>
sequence in which each <value expression> is a column
reference CR that references a column of TQ that is not
a common column of a <joined table>. Each column of TQ
that is not a referenced common column shall be referenced
exactly once. The columns shall be referenced in the
ascending sequence of their ordinal positions within TQ.

which is essentially taken from 7.9.4 of the old spec. This is a mess;
I wonder if the discrepancy between qualified-name access and asterisk
expansion is deliberate? (Perhaps they felt that allowing qualified
name access was an extension that wouldn't break old code, but that they
couldn't change the asterisk expansion rule without breaking backwards
compatibility?) It'd be nice to see if this is still true in SQL99
final.

> So there are two issues here which I hope to clarify: scoping
> on joins, and NATURAL and USING join column sets.

Two issues? I thought we were only arguing about the latter one.

regards, tom lane