PK78126: INCORROUT (WRONG VALUE RETURNED) WITH QUERY ON MQT

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Incorrect output can be returned when running a query to
  select from MQT.  DB2INCORR/K      (defect 117695)
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 V8 and V9 users who use materialized *
  *                 query table.                                 *
  ****************************************************************
  * PROBLEM DESCRIPTION: Incorrect output might occur when a     *
  *                      query contains an aggregate function    *
  *                      and is rewritten using a materialized   *
  *                      query table.                            *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  Incorrect output might occur when a query contains a set
  function which is an argument of a scalar function
  and the query is rewritten using a materialized query table.
  
  For example:
  
  Original query:
  
  SELECT DIGITS(DECIMAL(SUM(FLOAT(G3*5)+FLOAT(G4-4))))
  FROM TBLV61D3 D, TBLV61F1 F
  WHERE F.G1 = D.G1 AND
        D.G2 >  '11111111'
  GROUP BY D.G1;
  
  Materialized query table definition:
  SELECT D.G1,  DIGITS(DECIMAL(SUM(FLOAT(G4-4)+FLOAT(5*G3))))
  FROM TBLV61D3 D
  WHERE D.G2 >= '11111111'
  GROUP BY D.G1
  
  
  Rewritten query:
  SELECT B5MQT1.C2
  FROM TBLV61D3 D,TBLV61F1 F,B5MQT1
  WHERE  F.G1 = D.G1 AND      (join back)
         D.G2 > '11111111' AND F.G1 = B5MQT1.C1
        AND D.G1 = B5MQT1.C1  (transitive closure)
  GROUP BY B5MQT1.C1 ;
  
  The rewritten query is not a valid query, causing incorect
  output.
  

Problem conclusion

• DB2 was fixed to not pick the materialized query table.
  
  Additional keywords: SQLMQT SQLINCORR SQLAGGREGATE
  

Temporary fix

Comments

APAR Information

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

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

  UK45840 UK45841

Modules/Macros

• DSNXOXED
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• R810 PSY UK45840

     UP09/05/06 P F905

• R910 PSY UK45841

     UP09/05/06 P F905

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.