My Oracle Support Banner

Select With Join On Remote Tables Via Dblink Using TG4DRDA Gives Wrong Result (Doc ID 402389.1)

Last updated on AUGUST 18, 2021

Applies to:

Oracle Database Gateway for DRDA - Version 10.1.0.3.0 to 10.2.0.4
Information in this document applies to any platform.
Oracle Transparent Gateway for IBM DRDA - Version: 10.1 to 10.2



Symptoms

A SELECT with a JOIN (OUTER , LEFT , REMOTE JOIN) on remote tables via the Oracle Transparent gateway for IBM DRDA,  gives a wrong result.

In this example (showing the base tables first):

 

SQL> SELECT * FROM SCOTT.EMPL@DBLINK;
COD_EMPL   NOM_EMLP                       COD_D
---------- ------------------------------ -----
EMP1       EMPL1                          DEP1
EMP2       EMPL2                          DEP2
EMP3       EMPL3                          (null)

 

SQL> SELECT * FROM SCOTT.DEPT@DBLINK;
COD_D NOM_DEPT
----- ------------------------------
DEP1  DEPARTMENT 1

 

A SELECT with a LEFT JOIN gives a wrong result:

 

SQL> SELECT * FROM SCOTT.EMPL@DBLINK EMPL
2 LEFT JOIN SCOTT.DEPT@DBLINK DEPT
3 ON DEPT.COD_DEPT = EMPL.COD_DEPT
4 WHERE EMPL.COD_DEPT = 'DEP1'
5 OR EMPL.COD_DEPT = 'DEP2'
6 /

COD_EMPL   NOM_EMPL                       COD_D COD_D NOM_DEPT
---------- ------------------------------ ----- ----- ------------------------------
EMP1       EMPL1                          DEP1  DEP1  DEPARTMENT 1



A SELECT on Oracle local tables with the same data as the remote tables, the result is correct:

SQL> SELECT * FROM EMPL EMPL
2 LEFT JOIN DEPT DEPT
3 ON DEPT.COD_DEPT = EMPL.COD_DEPT
4 WHERE EMPL.COD_DEPT = 'DEP1'
5 OR EMPL.COD_DEPT = 'DEP2'
6 /

The correct result is 2 rows (and not 1):
COD_EMPL   NOM_EMPL                       COD_D COD_D NOM_DEPT
---------- ------------------------------ ----- ----- ------------------------------
EMP1       EMPL1                          DEP1  DEP1  DEPARTMENT1
EMP2       EMPL2                          DEP2

Changes

This behavior could occur after a TG4DRDA gateway upgrade to 10.1.0.x or 10.2.0.x version.

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.