PI43010: INFINITE LOOP MAY HAPPEN WHEN EXPLAIN AN SQL WHERE GROUPING SET CLAUSE IS CONTAINED

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• While running the following test cases, DB2 will run into an
  infinite loop when EXPLAIN is turned on.
  
  SET CURRENT EXPLAIN MODE = YES;
  
  SET CURRENT DEGREE='1';
  
   SELECT CHARC||'ABC', NUMC*2 + INTC, AVG(FLOATC),
          LEFT('ABC'||STRINGC, 5)
   FROM NL073.intTBL
   GROUP BY GROUPING SETS((FLOATC, NUMC*2, INTC),
              (CHARC||'ABC', 'ABC'||STRINGC))
   HAVING (CHARC||'ABC',
           NUMC*2 + INTC,
           AVG(FLOATC),
           LEFT('ABC'||STRINGC, 5)) IN (SELECT CHARC||'ABC',
           NUMC*2 + INTC,
           AVG(FLOATC),
           LEFT('ABC'||STRINGC, 5)
       FROM NL073.intTBL
       WHERE NUMC BETWEEN -10 AND 10
       GROUP BY GROUPING SETS((FLOATC,
             NUMC*2, INTC),
             (CHARC,CHARC||'ABC', 'ABC'||STRINGC))
               HAVING CHARC LIKE '%ABC');
  
  It is in test case N073DN03 TX01N003, member file N073DN03
  SQL1_7. The first having gets into an infinite loop, outputting
  "NUMC*2" over and over again.
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 11 for z/OS users of SQL statement   *
  *                 containing GROUPING SETS clause, and IN      *
  *                 predicate with row-value-expression used in  *
  *                 HAVING clause.                               *
  ****************************************************************
  * PROBLEM DESCRIPTION: DB2 would be hung when EXPLAIN a SQL    *
  *                      statement with following conditions:    *
  *                      1. GROUPING SETS clause is used;        *
  *                      2. an IN predicate with operand:        *
  *                         row-value-expression is used in      *
  *                         HAVING clause;                       *
  *                      3. arithmetic expression is used in     *
  *                         above row-value-expression.          *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  Following example helps to illustrate aforementioned problem:
  
  CREATE TABLE TEST.TAB
  (C1 INT , C2 VARCHAR(10), C3 CHAR(3));
  
  EXPLAIN ALL FOR
  SELECT C1, C2
  FROM TEST.TAB AS A
  GROUP BY GROUPING SETS(C1, C2, C3)
  HAVING (C2||'CD', C1+1, C3) IN (SELECT B.C2, B.C1, B.C3
                                  FROM TEST.TAB AS B);
  
  DB2 didn't handle IN predicate with row-value-expression in
  HAVING clause correctly when GROUPING SET is used, and caused
  DB2 was hung.
  

Problem conclusion

• DB2 has been modified to correctly process IN predicate with
  row-value-expression in HAVING clause when GROUPING SETS is
  used.
  
  Additional keyword: SQLHAVING SQLIN SQLGROUPBY
  

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:

  UI36592

Modules/Macros

• DSNXOSUB
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI36592

     UP16/04/14 P F604 ¢

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.