A fix is available
APAR status
Closed as program error.
Error description
Incorrect output is received on a query LEFT JOIN with a Nested Table Expression (NTE) that contains LEFT OUTER JOIN. Only the rows that have a match with the NTE rows are returned (as with an INNER JOIN). Additional Keywords: SQLLEFT SQLLEFTJOIN SQLTABLEEXPR DB2INCORR/K INCORROUT SQLINCORR SQLINCORROUT
Local fix
N/A
Problem summary
**************************************************************** * USERS AFFECTED: * * All Db2 12 for z/OS users of an SQL * * statement containing left or right outer * * joins. * **************************************************************** * PROBLEM DESCRIPTION: * * Incorrect output may happen for the * * following two cases. * * * * Case 1: * * An SQL statement satisfying all of * * following conditions: * * 1. It contains multiple left or * * right outer joins. * * 2. The ON clause contains an IS NOT * * NULL predicate on a not null * * column. * * * * Case 2: * * An SQL statement satisfying all of * * following conditions: * * 1. It contains a table expression, * * view or SQL table function which is * * on the null-supplying side of a * * left or right outer join. * * 2. The table expression, view or * * SQL table function contains * * multiple left or right outer joins. * * 3. The left or right outer join * * contains a non-Boolean term * * predicate that references a column * * from the null supplying side of the * * outer join. * **************************************************************** * RECOMMENDATION: * * Apply corrective PTF when available * **************************************************************** An example for case 1 is shown below: CREATE TABLE TBX( C1 INTEGER NOT NULL , C2 INTEGER NOT NULL , C3 INTEGER NOT NULL) ; CREATE TABLE TBY( C1 INTEGER NOT NULL , C2 INTEGER NOT NULL , C3 INTEGER NOT NULL) ; CREATE TABLE TBZ( C1 INTEGER NOT NULL , C2 INTEGER NOT NULL , C3 INTEGER NOT NULL) ; INSERT INTO TBX(C1,C2,C3) VALUES(1,2,3); INSERT INTO TBY(C1,C2,C3) VALUES(1,2,3); INSERT INTO TBZ(C1,C2,C3) VALUES(1,2,3); SELECT T1.C1 AS T1C1,T2.C1 AS T2C1,TW.C1 AS TWC1,T3.C1 AS T3C1 FROM TBX T1 LEFT JOIN TBX TW ON 1=1 LEFT JOIN TBY T2 ON (T2.C3 = TW.C3 AND T2.C1 = 1 AND T2.C2 = 2 ) JOIN TBZ T3 ON (T2.C3 IS NOT NULL AND T3.C3 = T2.C3 AND T3.C2 = T2.C2 AND T3.C1 = T2.C1 ) OR (T2.C3 IS NULL AND 0 = 0 AND T3.C3 = TW.C3 AND T3.C2 = 2 AND T3.C1 = 1 ) ; The SELECT statement returned wrong result for some of the columns. An example for case 2 is shown below: CREATE TABLE T1 (C1 INT, C2 INT); CREATE TABLE T2 (C1 INT, C2 INT); CREATE TABLE T3 (C1 INT, C2 INT); CREATE TABLE T4 (C1 INT, C2 INT); CREATE TABLE T5 (C1 INT, C2 INT); INSERT INTO T1 VALUES(1,10); INSERT INTO T1 VALUES(2,20); INSERT INTO T1 VALUES(3,30); INSERT INTO T2 VALUES(1,10); INSERT INTO T2 VALUES(4,40); INSERT INTO T3 VALUES(1,10); INSERT INTO T3 VALUES(4,40); INSERT INTO T3 VALUES(5,50); INSERT INTO T4 VALUES(1,10); INSERT INTO T4 VALUES(4,40); INSERT INTO T5 VALUES(1,10); INSERT INTO T5 VALUES(4,40); SELECT T1.C1, TX.CX, TX.CY FROM T1 LEFT JOIN (SELECT T2.C1 , T3.C1 FROM T2 INNER JOIN T4 ON T2.C1=T4.C1 LEFT JOIN T3 ON T2.C1=T3.C1 LEFT JOIN T5 ON T2.C1=T5.C1 WHERE (T2.C1=4 OR T3.C1 IS NOT NULL) )TX(CX,CY) ON T1.C1=TX.CX ; The SELECT statement returned less rows. Db2 did not correctly process the left or right outer joins and resulted in the incorrect output.
Problem conclusion
Db2 has been modified to correctly process the aforementioned SQL statement, and correct result will be returned. Additional keywords: SQLINCORR INCORROUT SQLINCORROUT Db2INCORR/K SQLTABLEEXP SQLVIEW SQLTABLEUDF SQLTUDF SQLOUTERJOIN SQLOUTER SQLJOIN SQLLEFTJOIN SQLLEFT SQLRIGHTJOIN SQLRIGHT SQLNOTNULL SQLISNOTNULL
Temporary fix
********* * HIPER * *********
Comments
APAR Information
APAR number
PH12229
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
C10
Status
CLOSED PER
PE
NoPE
HIPER
YesHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2019-05-20
Closed date
2019-06-11
Last modified date
2019-07-01
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UI63563
Modules/Macros
DSNXOMQB
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
RC10 PSY UI63563
UP19/06/26 P F906 ¢
Fix is available
Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"12.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"12.0","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
01 July 2019