Re: [HACKERS] Duplicate table names

At 08:49 PM 2/7/00 +0100, Peter Eisentraut wrote:

>Not according to the way I decoded it. It's a join of t1 with itself and
>you get all columns twice.

...

>This is the same problem as
>
>select x from t1, t2;
>
>where both t1 and t2 have a column x. It's an error. It's not an error if
>column x is unambiguous. Chances are pretty good (=100%) that there will
>be ambiguity if you list the same table twice, but there's no reason to
>reject this for the reason it gives now.

I believe that Peter's right on all counts.

>
>[snip]
>> I'm thinking of implementing this by allowing multiple RTEs to have
>> the *same* table alias, (as long as there aren't column name conflicts
>> in the "visible" columns), so that, at least internally,
>>
>> select * from t1 tx, t3 tx;

>> becomes legal as long as t1 and t3 do not share common column names.

>This seems perfectly legal as well, even if they do share column names.
>Any reference to tx.y will fail due to ambiguity, but it shouldn't merely
>because of name checking.

Actually, according to Date an explicit range variable must be
unique within a given scope.

Does Postgres implement scope? Apparently JOIN opens a new
scope...so do subselects.

select * from t1 tx, t3 tx is not legal SQL

select * from t1 tx, (select * from t3 tx) is legal SQL.

The tx inside the subselect hides the outer tx, just like
any 'ole block-structured language.

Date takes over six pages of fairly terse prose with few examples to
define the scope of range variables in and out of JOIN expressions.
A bit over one page of that is devoted to scoping issues unique
to JOINs, which I don't feel like reading at the moment!

- Don Baccus, Portland OR <dhogaza(at)pacifier(dot)com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.