PM88698: INCORROUT FOR QUERY WITH UNION ALL AND THERE IS CURRENT DATE IN GROUP BY CLAUSE

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • INCORROUT FOR QUERY WITH UNION ALL AND THERE IS CURRENT DATE IN
    GROUP BY CLAUSE, please following case as an example.
    
    CREATE TABLE TB1 (A SMALLINT) ;
    CREATE TABLE TB2 (A SMALLINT) ;
    
    INSERT INTO TB1 VALUES(2006);
    INSERT INTO TB1 VALUES(2007);
    INSERT INTO TB1 VALUES(2007);
    INSERT INTO TB1 VALUES(2008);
    INSERT INTO TB1 VALUES(2008);
    INSERT INTO TB1 VALUES(2008);
    
    INSERT INTO TB2 VALUES(2008);
    INSERT INTO TB2 VALUES(2008);
    INSERT INTO TB2 VALUES(2008);
    
    CREATE VIEW TV (A) AS (
    SELECT A FROM TB1
    UNION ALL
    SELECT A FROM TB2
    );
    
    SELECT A, SUM(1)
    FROM TV
    GROUP BY A;
    
     A    2
     ---- -
     2006 1
     2007 2
     2008 6
    
    SELECT A, CASE
      WHEN A = YEAR(CURRENT DATE) - 7
                THEN 'VJ6'
                WHEN A = YEAR(CURRENT DATE) - 6
                THEN 'VJ7'
                WHEN A = YEAR(CURRENT DATE) - 5
                THEN 'VJ8'
         END AS A, SUM(1)
    FROM TV
    GROUP BY A;
    
     A    A   3
     ---- --- -
     2006 VJ6 1
     2007 VJ7 2
     2008 VJ8 6
    
    
    SELECT CASE
      WHEN A = YEAR(CURRENT DATE) - 7
                THEN 'VJ6'
                WHEN A = YEAR(CURRENT DATE) - 6
                THEN 'VJ7'
                WHEN A = YEAR(CURRENT DATE) - 5
                THEN 'VJ8'
     END AS A, SUM(1)
    FROM TV
    GROUP BY CASE
      WHEN A = YEAR(CURRENT DATE) - 7
                THEN 'VJ6'
                WHEN A = YEAR(CURRENT DATE) - 6
                THEN 'VJ7'
                WHEN A = YEAR(CURRENT DATE) - 5
                THEN 'VJ8'
       END;
    
     A   2
     --- -
     VJ6 9    <-------- incorrect output, only 1 row is returned.
    
    If replace YEAR(CURRENT DATE) with 2013, then could get correct
    result.
    SELECT CASE
           WHEN A = 2013 - 7
                THEN 'VJ6'
                WHEN A = 2013 - 6
                THEN 'VJ7'
                WHEN A = 2013 - 5
                THEN 'VJ8'
     END AS A, SUM(1)
    FROM TV
    GROUP BY CASE
           WHEN A = 2013 - 7
                THEN 'VJ6'
                WHEN A = 2013 - 6
                THEN 'VJ7'
                WHEN A = 2013 - 5
                THEN 'VJ8'
       END;
    
    
     A   2
     --- -
     VJ6 1
     VJ7 2
     VJ8 6
    

Local fix

  • N/A
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All DB2 V9 and 10 for z/OS users of an SQL   *
    *                 statement that contains special registers in *
    *                 GRPOUP BY and a view or table expression     *
    *                 with UNION ALL.                              *
    ****************************************************************
    * PROBLEM DESCRIPTION: Incorrect output could happen for an    *
    *                      SQL statement that contains special     *
    *                      registers in GRPOUP BY and a view or    *
    *                      table expression with UNION ALL.        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    An example is shown below:
    
    CREATE TABLE TB1 (A SMALLINT) ;
    CREATE TABLE TB2 (A SMALLINT) ;
    
    INSERT INTO TB1 VALUES(2006);
    INSERT INTO TB1 VALUES(2007);
    INSERT INTO TB1 VALUES(2007);
    INSERT INTO TB1 VALUES(2008);
    INSERT INTO TB1 VALUES(2008);
    INSERT INTO TB1 VALUES(2008);
    
    INSERT INTO TB2 VALUES(2008);
    INSERT INTO TB2 VALUES(2008);
    INSERT INTO TB2 VALUES(2008);
    
    SELECT CASE
             WHEN A = YEAR(CURRENT DATE) - 7
               THEN 'VJ6'
             WHEN A = YEAR(CURRENT DATE) - 6
               THEN 'VJ7'
             WHEN A = YEAR(CURRENT DATE) - 5
              THEN 'VJ8'
            END AS A
          , SUM(1)
    FROM (
        SELECT A FROM TB1
        UNION ALL
        SELECT A FROM TB2
        ) AS TV
    GROUP BY CASE
               WHEN A = YEAR(CURRENT DATE) - 7
                 THEN 'VJ6'
               WHEN A = YEAR(CURRENT DATE) - 6
                 THEN 'VJ7'
               WHEN A = YEAR(CURRENT DATE) - 5
                 THEN 'VJ8'
             END
    ;
    
    The query above should return 3 rows when CURRENT DATE is 2013,
    while it only returns 1 row. DB2 did not correctly process the
    aforementioned SQL statements, which caused the incorrect
    output.
    

Problem conclusion

  • DB2 has been modified to correctly process the aforementioned
    SQL statement, so there will be no incorrect output.
    
    Additional keywords: SQLTABLEEXPR SQLVIEW SQLSPECIALREG
                         SQLUNIONALL SQLGROUPBY
    

Temporary fix

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

Comments

APAR Information

  • APAR number

    PM88698

  • 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-05-09

  • Closed date

    2013-06-25

  • Last modified date

    2013-08-02

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

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

    UK95379 UK95380

Modules/Macros

  •    DSNXODSO
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UK95379

       UP13/07/10 P F307

  • R910 PSY UK95380

       UP13/07/10 P F307

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:

A10

Reference #:

PM88698

Modified date:

2013-08-02

Translate my page

Machine Translation

Content navigation