HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

Same table alias for two different tables (Oracle)

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
sametablestabledifferenttwofororaclealias

Problem

This example has the same table alias used for two different tables. I don't understand why this is allowed by Oracle, and if allowed, how the results make any sense.

create table Table_A (x number);
create table Table_B (x number);
insert into Table_A values (1);
insert into Table_A values (2);
insert into Table_B values (2);
insert into Table_B values (3);

select * from Table_A ;
         X
----------
         1
         2
2 rows selected.

select * from Table_B ;
         X
----------
         2
         3
2 rows selected.

select *
  from Table_A  T
  join Table_B  T
    on T.x = T.x;
         X          X
---------- ----------
         2          2
         2          2
         3          3
         3          3
4 rows selected.

Solution

Bug 25342699 : WRONG RESULTS WITH ANSI JOIN USING AND IDENTICAL TABLE ALIASES (login required)

The public versions of Oracle Support notes are quite limited. Here is the note that references the above bug (visible in the full version after logging in).

The bug is "still being worked on".

The workaround is obvious: use different aliases.

Non-ANSI join throws an error as expected.

Seems to be fixed in 18.1 and 18.2 as well.

Context

StackExchange Database Administrators Q#211046, answer score: 4

Revisions (0)

No revisions yet.