PM43780: INCORROUT WHEN USE OPTIMIZATION HINT FOR THE QUERY WITH GROUP BY AND COLUMNS IN GROUP BY ALSO HAVE EQUAL PREDICATE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• For this query:
  SELECT SUM(C3), C2 FROM T1
  WHERE C2 = 1
  GROUP BY C2
  ORDER BY C2;
  
  Without hint, we got result set
    +---------------------------------+
    |                |       C2       |
    +---------------------------------+
  1_|            111 |       11       |
    +---------------------------------+
  
  With hint, we got the following result set:
     +---------------------------------+
     |                |       C2       |
     +---------------------------------+
   1_|            111 |    -2147483648 |
     +---------------------------------+
  

Local fix

• not use hint
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 for z/OS users who have an           *
  *                 optimization hint specified on a query with  *
  *                 GROUP BY and set function, and the columns   *
  *                 in GROUP BY also have equal literal          *
  *                 predicates on them.                          *
  ****************************************************************
  * PROBLEM DESCRIPTION: For a query with GROUP BY and a set     *
  *                      function, and the GROUP BY columns also *
  *                      have equal literal predicates on them,  *
  *                      DB2 may avoid sort. If the user         *
  *                      specifies an optimization hint for it,  *
  *                      DB2 may consider evaluating the set     *
  *                      function while data is being read from  *
  *                      the table (COLUMN_FN_EVAL = 'R' in      *
  *                      PLAN_TABLE), which may result in        *
  *                      incorrect output.                       *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  For a query with GROUP BY and a set function, and the GROUP BY
  columns also have equal literal predicates on them, DB2 may
  avoid sort. If the user specifies an optimization hint for it,
  DB2 may consider evaluating the set function while data is
  being read from the table (COLUMN_FN_EVAL = 'R' in PLAN_TABLE),
  which may result in incorrect output.
  
  For example:
  SELECT SUM(C3),C2 FROM T1
  WHERE C2 = 1
  GROUP BY C2
  ORDER BY C2
  QUERYNO 1;
  
  This query has a GROUP BY column C2, and C2 also has an equal
  literal predicate C2 = 1. With an optimization hint, DB2 may
  consider evaluating the set function while data is being read
  from the table.  This may cause incorrect output.
  
  Additional Keywords:
  INCORROUT SQLACCESSPATH SQLGROUPBY SQLSETFUNCTION
  SQLINCORR SQLINCORROUT DB2INCORR/K  OPTHINT OPTHINTS HINTS
  

Problem conclusion

• Code is modified to solve the problem described as above.
  

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:

  UK71142

Modules/Macros

• DSNXOPTH
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• R910 PSY UK71142

     UP11/09/08 P F109

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.