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.
  • 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' – Tom Warfield Jun 30 '18 at 17:08
  • 4 digit Oracle version and edition? – Michael Kutz Jun 30 '18 at 17:59
  • Looks like a bug. I think I've seen a similar question some years ago. – ypercubeᵀᴹ Jun 30 '18 at 18:30
  • Oh... it's definitely a bug. 11g XE (11.2.0.2) on home PC and 18c EE (18.0.0.0) on LiveSQL show two different answers. 18c throws an error. More info might be found in Oracle Support but research will need all 4 digits to confirm. – Michael Kutz Jun 30 '18 at 18:38
  • Same behavior using: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – Tom Warfield Jun 30 '18 at 18:43
3

Bug 25342699 : WRONG RESULTS WITH ANSI JOIN USING AND IDENTICAL TABLE ALIASES

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.

| improve this answer | |
  • Is the bug page visible without a login? – ypercubeᵀᴹ Jun 30 '18 at 19:04
  • @ypercubeᵀᴹ Unfortunately not. 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) support.oracle.com/knowledge/Oracle%20Database%20Products/… - – Balazs Papp Jun 30 '18 at 19:13
  • @BalazsPapp - You should add some text indicating that the link requires a valid current Oracle Support contract – Michael Kutz Jun 30 '18 at 20:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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