PI58268: INCORROUT FOR A QUERY USING CTE WITH GROUP BY ROLLUP(COLUMN) AND WHERE CLAUSE ATTEMPTING FILTER ON THE ROLLUP COLUMN

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• INCORROUT can occur when the group by rollup feature is used
  and predicate filtering is requested using the rollup column.
  Example SQL:
  
  WITH TESTDATA(TEXT, NUM) AS
  ( SELECT 'VALUE1', 1
      FROM SYSIBM.SYSDUMMY1
    UNION ALL
    SELECT 'VALUE2', 1
      FROM SYSIBM.SYSDUMMY1
    UNION ALL
    SELECT 'VALUE2', 2
      FROM SYSIBM.SYSDUMMY1
  )
  , TESTDATA2(TEXT,NUM,COUNT) AS
  ( SELECT TEXT, NUM, COUNT (*)
      FROM TESTDATA
    GROUP BY TEXT, ROLLUP (NUM)
  )
   SELECT * FROM TESTDATA2
    WHERE NUM   IS NOT NULL
       OR COUNT > 1
   ORDER BY TEXT, VALUE (NUM, +9) ;
  
  Expecting 4 rows receive 5.
  
  Additional Keywords:
  INCORROUT SQLGROUPBY SQLCTE
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 11 for z/OS users of SQL statement   *
  *                 using GROUPING SETS, TABLE EXPRESSION and    *
  *                 IS NULL or IS NOT NULL predicate.            *
  ****************************************************************
  * PROBLEM DESCRIPTION: An incorrect output may occur when all  *
  *                      of following conditions are satisfied:  *
  *                      1. GROUPING SETS, Cube or RollUp clause *
  *                         is used within a TABLE EXPRESSION    *
  *                         definition;                          *
  *                      2. Column defined as NOT NULL is        *
  *                         used in the SELECT list of the       *
  *                         TABLE EXPRESSION definition;         *
  *                      3. Operand of the predicate IS NULL or  *
  *                         IS NOT NULL references a column      *
  *                         defined in above table expression.   *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  The following example helps to illustrate the problem:
  
  DDL:
  CREATE TABLE TEST.TAB
  (C1 INT NOT NULL, C2 VARCHAR(10) NOT NULL);
  
  INSERT INTO TEST.TAB VALUES(1, 'ABC');
  INSERT INTO TEST.TAB VALUES(2, 'DEF');
  
  SQL:
  SELECT COUNT(*) AS ROWS#
  FROM TABLE(SELECT A.C1, A.C2
             FROM TEST.TAB AS A
             GROUP BY GROUPING SETS((A.C1),(A.C2)))
       AS TEX(COL1, COL2)
  WHERE COL1 IS NOT NULL
        OR COL2 = 'ABC';
  
  The value of ROWS# is 4, which is incorrect. The correct value
  of ROWS# should be 3.
  
  DB2 didn't handle the IS NOT NULL predicate correctly and caused
  incorrect output.
  

Problem conclusion

• DB2 has been modified to correctly process predicate when
  using the conditions stated in this problem.
  
  Additional keywords: SQLGROUPBY SQLISNULL SQLISNOTNULL
                       SQLTABLEEXPR INCORROUT SQLCTE
  

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:

  UI37145

Modules/Macros

• DSNXOW1
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI37145

     UP16/05/05 P F605 ¢

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.