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

Obtain the fix for this APAR.

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