PI82323: INCORROUT MAY HAPPEN WHEN A TABLE EXPRESSION OR VIEW COLUMN IS USED IN A JOIN PREDICATE AND THIS COLUMN IS BINARY OR VARBINARY

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Incorrout may happen when a table expression or view column is
  used in a join predicate and this column is of binary or
  varbinary type.                                        (d57146)
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All DB2 12 for z/OS users of views or                        *
  * table expressions that contain UNION                         *
  * ALL and have columns of BINARY or                            *
  * VARBINARY type.                                              *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * Incorrect output may occur when a                            *
  * view or table expression contains                            *
  * UNION ALL and one of its columns                             *
  * of BINARY or VARBINARY type is                               *
  * used in a join predicate.                                    *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  Here is an example to show the failing case:
  
  CREATE TABLE T1 (C11 INT, C12 BINARY(2), C13 VARBINARY(2));
  CREATE TABLE T2 (C21 INT, C22 BINARY(1), C23 VARBINARY(1));
  CREATE TABLE T3 (C31 INT, C32 BINARY(2), C33 VARBINARY(2));
  
  INSERT INTO T1 VALUES (1, BX'0000', BX'0000');
  INSERT INTO T1 VALUES (2, BX'0001', BX'0001');
  INSERT INTO T2 VALUES (1, BX'00'  , BX'00'  );
  INSERT INTO T3 VALUES (1, BX'0000', BX'0000');
  
  SELECT T1.C11, X.X1
    FROM T1
    LEFT JOIN TABLE
         (SELECT C21,C22 FROM T2
           UNION ALL
          SELECT C31,C32 FROM T3
         ) AS X (X1, X2)
      ON T1.C12 = X.X2;
  
  The following incorrect output may be observed:
     +---------------------------------+
     |      C11       |       X1       |
     +---------------------------------+
   1_|              1 |              1 |
   2_|              2 | ?              |
     +---------------------------------+
  
  where the correct output should be:
     +---------------------------------+
     |      C11       |       X1       |
     +---------------------------------+
   1_|              1 |              1 |
   2_|              1 |              1 |
   3_|              2 | ?              |
     +---------------------------------+
  

Problem conclusion

• DB2 code has been modified to handle the aforementioned problem
  correctly.
  
  Additional keywords: SQLUNIONALL SQLOUTERJOIN SQLOUTER
                       SQLFULLJOIN SQLFULL SQLJOIN SQLLEFTJOIN
                       SQLLEFT SQLRIGHTJOIN SQLRIGHT SQLVIEW
                       SQLTABLEEXPR SQLINCORR INCORROUT
                       SQLINCORROUT DB2INCORR/K
  

Temporary fix

• *********
  * HIPER *
  *********
  

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  UI50368

Modules/Macros

• DSNXORPP
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI50368

     UP17/09/30 P F709 ¢

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.