No Data In All Joined Tables Merged Via Inner Join (Doc ID 462441.1)

Last updated on JULY 05, 2017

Applies to:

Oracle Provider for OLE DB - Version: 10.2.0.1.0 and later   [Release: and later ]
Microsoft Windows (32-bit)

Symptoms

An OLE DB program that uses INNER JOIN syntax does not return correct results. The query worked
as expected in OLE DB 9i.

For example, the query:
SELECT * from test1 INNER JOIN test2 ON id1 = id2 INNER JOIN test3 ON id2 = id3

executed on the following data:

create table test1 ( id1 number, dat1 varchar2( 10 ) );
create table test2 ( id2 number, dat2 varchar2( 10 ) );
create table test3 ( id3 number, dat3 varchar2( 10 ) );
insert into test1 values ( 1, 'test1' );
insert into test1 values ( 2, 'test1' );
insert into test2 values ( 1, 'test2' );
insert into test2 values ( 2, 'test2' );
insert into test3 values ( 1, 'test3' );
insert into test3 values ( 2, 'test3' );
commit;

displays the following in SQL*Plus:

1 test1 1 test2 1 test3
2 test1 2 test2 2 test3 

but the versions of the OLE DB Provider that exhibit this bug only return:

1 test1 1 test2
2 test1 2 test2

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