PH02264: INCORROUT MAY OCCUR ON A QUERY USING CASE WHEN STATEMENT WITH A IN LIST IN THE GROUP BY CLAUSE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• incorrout may occur on a query using CASE WHEN statement with a
  IN list in the GROUP BY clause
  Here is an example:
  -- RESULT OK
     SELECT CASE WHEN ((A = B) OR (A = C)) THEN 1 ELSE 0 END
       FROM (SELECT 1, 0, 1 FROM SYSIBM.SYSDUMMY1
           UNION SELECT 1, 0, 0 FROM SYSIBM.SYSDUMMY1
         ) AS T(A, B, C)
       GROUP BY CASE WHEN ((A = B) OR (A = C)) THEN 1 ELSE 0 END
       ;
  ---------+---------+---------+---------+---------+---------+----
            0
            1
  -- NOT OK
     SELECT CASE WHEN (A IN (B, C)) THEN 1 ELSE 0 END
       FROM (SELECT 1, 0, 1 FROM SYSIBM.SYSDUMMY1
           UNION SELECT 1, 0, 0 FROM SYSIBM.SYSDUMMY1
         ) AS T(A, B, C)
       GROUP BY CASE WHEN (A IN (B, C)) THEN 1 ELSE 0 END
       ;
  ---------+---------+---------+---------+---------+---------+----
            1
            1
  
  
  Additional keywords:
  SQLCASE SQLIN ZSA2
  DB2INCORR/K INCORROUT	 SQLINCORR	 SQLINCORROUT
  

Local fix

• Either taking off the GROUP BY clause or change the IN list to
  OR predicates
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All Db2 11 and Db2 12 for z/OS users of      *
  *                 queries that include a CASE statement with a *
  *                 WHEN clause containing an IN LIST in the     *
  *                 GROUP BY clause.                             *
  ****************************************************************
  * PROBLEM DESCRIPTION: An incorrect result can be returned     *
  *                      from a query that includes a CASE       *
  *                      statement with a WHEN clause containing *
  *                      an IN LIST in the GROUP BY clause.      *
  *                                                              *
  ****************************************************************
  * RECOMMENDATION: Apply corrective PTF when available.         *
  ****************************************************************
  An incorrect result can be returned from a query that includes a
  CASE statement with a WHEN clause containing an IN LIST in
  the GROUP BY clause.
  
  
  The following example helps to illustrate such a failing case.
  
     SELECT CASE  WHEN (A IN (B, C)) THEN 1 ELSE 0 END
       FROM (SELECT 1, 0, 1 FROM SYSIBM.SYSDUMMY1
             UNION
             SELECT 1, 0, 0 FROM SYSIBM.SYSDUMMY1)
             AS T2(A, B, C)
     GROUP BY CASE  WHEN (A IN (B, C)) THEN 1 ELSE 0 END;
  
  
  For this query, this is the incorrect result.
  
        +----------------+
      1_|              1 |
      2_|              1 |
        +----------------+
  
  For this query, this is the correct or expected result.
  
        +----------------+
      1_|              0 |
      2_|              1 |
        +----------------+
  
  Please note: The IN LIST in both the SELECT and GROUP BY
  CASE statements must contain at least two elements.
  

Problem conclusion

• Db2 has been modified to correct the problem that can cause an
  incorrect result to be returned for a query as described above.
  
  Additional Keywords: SQLGROUPBY SQLINLIST SQLCASE
                       INCORROUT SQLINCORROUT DB2INCORR/K
                       SQLINCORR
  

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:

  UI59674 UI59676

Modules/Macros

•    DSNXGSGP
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI59676

     UP18/11/29 P F811 Ž

• RC10 PSY UI59674

     UP18/11/28 P F811 Ž

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.