Thread: Duplicate table names

The following query is rejected (and always has been afaik):

select * from t1, t1;

Does this rejection have any basis in SQL92? (I haven't looked; hoping
someone else has.)

istm that

select x from t1, t1;

would have trouble, but the wildcard could do the Right Thing even
without resorting to (for example)

select * from t1 a, t1;

as is currently required. I'm not sure what it would take to do this,
but it probably touches on an area of "outer join syntax" I'm looking
at:

select a, b from t1 join t2 using (a);

is legal, but the "join table" (t1 join t2 using...) must lose its
underlying table names (yuck, only for the join columns), resulting in
disallowing, for example,

select t1.a from t1 join t2 using (a);

That is, the "relation.column" syntax is not allowed to refer to the
join column(s), unless one specifies an alias for the "join table", as
in

select tx.a from (t1 join t2 using (a)) as tx;

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.

Comments on either or both issues?
                    - Thomas

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California

On 2000-02-06, Thomas Lockhart mentioned:

> The following query is rejected (and always has been afaik):
> 
> select * from t1, t1;
> 
> Does this rejection have any basis in SQL92? (I haven't looked; hoping
> someone else has.)

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

> 
> istm that
> 
> select x from t1, t1;
> 
> would have trouble, but the wildcard could do the Right Thing even

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.

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

-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden

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@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert
Serviceand other goodies at http://donb.photo.net.
 

At 12:26 PM 2/7/00 -0800, Don Baccus wrote:

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

I consulted the Oracle, and it agrees with Peter, hmmm...and the
wording in Date's a bit ambiguous, he's not clear as to whether
the range variable must be unique when DEFINED, or must only be
unique if it is referenced, i.e. select tx.foo from t1 tx, t3 tx
is ambiguous.

Reading further into Date, he says that

select ... from t1 

implicitly defines t1 as a range variable, and since

select ... from t1, t1 is legal, then range variables need not be
unique to be defined, 'cause according to the standard this
causes two range variables named t1 to be implicitly defined.

So, his comment about uniqueness within scope applies to whether
or not you can explicitly REFERENCE, not DEFINE the range var.

Sorry for the confusion...Peter was right all along.




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

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

Right. We're not likely to meet all of the scoping rules in the first
implementation; they are *really* tough :(
                    - Thomas

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California