PH37171: INCORROUT MAY OCCUR FOR A QUERY USING INLIST OR BETWEEN WHICH CONTAINS COLUMNS

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• INCORROUT may occur for a query using INLIST or BETWEEN which
  contains columns
  
  Below is example for INLIST:
  CREATE TABLE T1(
  C1 CHAR(1) FOR SBCS DATA NOT NULL,
  C2 CHAR(1) FOR SBCS DATA NOT NULL,
  C3 CHAR(1) FOR SBCS DATA NOT NULL
  );
  
  SELECT COUNT(*) FROM T1
  WHERE 'N'  IN (C1 , C2 , C3);
  
  The above query may result in an incorrect result, with the
  COUNT(*) value being higher or lower than it should be,
  depending on the sequence of columns in the INLIST
  
  Additional Keywords:
  SQLIN SQLBETWEEN
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All Db2 12 for z/OS users of queries that                    *
  * include a CASE statement with an IN LIST                     *
  * containing at least 2 columns or a BETWEEN                   *
  * predicate with 2 columns that are                            *
  * character based.                                             *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * An incorrect result can occur for                            *
  * queries that include a CASE statement                        *
  * with an IN LIST containing at least 2                        *
  * columns or a BETWEEN predicate with                          *
  * 2 columns that are character based.                          *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  An incorrect result can occur for queries that include a CASE
  statement with an IN LIST containing at least 2 column
  elements or a BETWEEN predicate with 2 columns that are
  character based.
  The problem occurs because an invalid source is referenced for
  the subsequent columns in the 'generated optimized code' for
  CASE statement processing. Simply, if the source matches
  the first column in the IN LIST, a correct result will be
  returned. However, if the first match is a subsequent column,
  an incorrect result will be returned.
  Please reference the following CASE statement example.
  1. Create Table TBL1 and insert four rows.
  CREATE TABLE TBL1
     (C1       INT,
      C2       CHAR(1),
      C3       CHAR(1),
      C4       CHAR(1));
  
  INSERT INTO TBL1 VALUES (1, 'N', 'N', 'N');
  INSERT INTO TBL1 VALUES (2, 'N', 'N', 'Y');
  INSERT INTO TBL1 VALUES (3, 'N', 'Y', 'N');
  INSERT INTO TBL1 VALUES (4, 'N', 'Y', 'Y');
  INSERT INTO TBL1 VALUES (5, 'Y', 'N', 'N');
  INSERT INTO TBL1 VALUES (6, 'Y', 'N', 'Y');
  INSERT INTO TBL1 VALUES (7, 'Y', 'Y', 'N');
  INSERT INTO TBL1 VALUES (8, 'Y', 'Y', 'Y');
  2. Run this query. Notice multiple columns in the IN LIST.
  SELECT C1, (CASE WHEN 'N' IN (C2, C3, C4)
             THEN 1 ELSE 0 END) AS CIN1
  FROM TBL1;
  3. Verify the results.
  This result is incorrect.
    +---------------------------------+
    |       C1       |      CIN1      |
    +---------------------------------+
  1_|              1 |              1 |
  2_|              2 |              1 |
  3_|              3 |              1 |
  4_|              4 |              1 |
  5_|              5 |              0 | <--wrong
  6_|              6 |              0 | <--wrong
  7_|              7 |              0 | <--wrong
  8_|              8 |              0 |
    +---------------------------------+
  This is the correct and expected result.
    +---------------------------------+
    |       C1       |      CIN1      |
    +---------------------------------+
  1_|              1 |              1 |
  2_|              2 |              1 |
  3_|              3 |              1 |
  4_|              4 |              1 |
  5_|              5 |              1 |
  6_|              6 |              1 |
  7_|              7 |              1 |
  8_|              8 |              0 |
    +---------------------------------+
  Please note. BETWEEN predicate processing is similar in
  the 'generated optimized code'.
  

Problem conclusion

• Db2 has been modified to prevent the problem described above
  where an incorrect source column is referenced for a subsequent
  column in an IN LIST or BETWEEN predicate when processing
  in the 'generated optimized code'.
  This will allow the correct result to be returned.
  Additional Keywords: DB2INCORR/K INCORROUT SQLINCORR
                       SQLINCORROUT SQLCASE SQLINLIST
                       SQLBETWEEN
  

Temporary fix

Comments

APAR Information

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

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

  UI75893

Modules/Macros

• DSNXGCAS
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI75893

     UP21/06/23 P F106

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.