Select With Join On Remote Tables Via Dblink Using TG4DRDA Gives Wrong Result
(Doc ID 402389.1)
Last updated on JUNE 08, 2023
Applies to:
Oracle Database Gateway for DRDA - Version 10.1.0.3.0 to 10.2.0.4Information 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 |