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

A fix is available

Subscribe

You can track all active APARs for this component.

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 number

    PM43780

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    910

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2011-07-15

  • Closed date

    2011-08-23

  • Last modified date

    2011-10-03

  • 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.



Rate this page:

(0 users)Average rating

Document information


More support for:

DB2 for z/OS

Software version:

910

Reference #:

PM43780

Modified date:

2011-10-03

Translate my page

Machine Translation

Content navigation