PM80699: SQLINCORROUT WHEN GROUP BY LIST CONTAINS COLUMN AND COLUMN EXPRESSION WHICH REFERENCES AND SHOWS BEFORE THE COLUMN

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • Incorrout may happen when query contains group by list and
    the list contains a column and a column expression which
    references the column and shows before the column.
    

Local fix

  • REVERSE THE COLUMN AND THE COLUMN EXPRESSION.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: DB2 9 and 10 for z/OS users whose queries    *
    *                 contain a GROUP BY list which has a SUBSTR   *
    *                 or YEAR function as well as the column       *
    *                 referenced by the function.                  *
    ****************************************************************
    * PROBLEM DESCRIPTION: Incorrect output may be returned for a  *
    *                      query with a GROUP BY list which has    *
    *                      a SUBSTR or YEAR function and the       *
    *                      column referenced by the function.      *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    Incorrect output may be returned for a query with a GROUP BY
    list if all the following conditions are satisfied:
    
    1. The GROUP BY list contains a SUBSTR or YEAR function as well
    as the column referenced by the function;
    
    2. The function appears before the column it references in the
    GROUP BY list;
    
    3. The function preserves the order of the column it
    references;
    
    4. There is an index supporting the avoidance of the GROUP BY
    sort and the index is selected.
    
    For example,
    
    SELECT SUBSTR(C1,1,4), C1, COUNT(*)
    FROM T1
    GROUP BY SUBSTR(C1,1,4), C1;
    
    C1 is a CHAR(10) column. There is an index IX1 created on T1.C1.
    The DB2 optimizer may select an access path without sort if IX1
    is used to access T1 because SUBSTR(C1,1,4) preserves the order
    of C1.  Incorrect output may occur for the query above.
    
    Additional Keywords:
    SQLINCORR SQLINCORROUT INCORROUT DB2INCORR/K SQLGROUPBY
    SQLSUBSTR SQLYEAR
    

Problem conclusion

  • Code has been fixed to give correct output for queries
    described above.
    

Temporary fix

  • *********
    * HIPER *
    *********
    

Comments

APAR Information

  • APAR number

    PM80699

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-01-15

  • Closed date

    2013-03-14

  • Last modified date

    2013-05-03

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

    UK92539 UK92540

Modules/Macros

  •    DSNXOPRP
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UK92539

       UP13/04/02 P F304

  • R910 PSY UK92540

       UP13/04/02 P F304

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

Add comments

Document information


More support for:

DB2 for z/OS

Software version:

A10

Reference #:

PM80699

Modified date:

2013-05-03

Translate my page

Machine Translation

Content navigation