Wrong Results With FULL OUTER JOIN In Versions Prior To 11g Release 1. (Doc ID 824898.1)

Last updated on SEPTEMBER 15, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.5.0
This problem can occur on any platform.

Symptoms

Full Outer Join of tables created with same name can give wrong results.


create table tgpowner.lsc_t(a number, b number);
insert into tgpowner.lsc_t values (1,2);
insert into tgpowner.lsc_t values (3,4);
commit;
create table sin_tgpowner.lsc_t(c number, d number);
insert into sin_tgpowner.lsc_t values (1,5);
insert into sin_tgpowner.lsc_t values (6,7);
commit;
select * from tgpowner.lsc_t full join sin_tgpowner.lsc_t on (a=c);
A B C D
- - - -
1 2 1 5
3 4 N N
N N N N

The last row is incorrect, should be {N,N,6,7}

If the tables are created by different users with the same table name

or

if the tables are created by the same user with different table name , then we get the right results.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms