5

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.
2
  • In Microsoft SQL Server, you get this error: Msg 1011, Level 16, State 1, Server SQLSERVER, Line 1 The correlation name 'T' is specified multiple times in a FROM clause. In MySQL, you get ERROR 1066 (42000): Not unique table/alias: 'T' Jun 30, 2018 at 17:08
  • Reproduced on 11gR2: dbfiddle.uk/… Jun 30, 2018 at 20:55

1 Answer 1

4

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.