PK54816: INCORROUT WITH INSERT FROM SELECT MIN BIF BUILT IN FUNCTION

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Incorrout with select from insert using Min function with char
  argument (v8, V9 problem)
  
  Example:
  CREATE TABLE T1 (C1 CHAR(10));
  INSERT INTO T1 SELECT MIN(CURRENT DATE) FROM SYSIBM.SYSDUMMY1;
  SELECT * FROM T1;
              +------------+
              |     C1     |
              +------------+
            1_| 0000-00-00 |
  

Local fix

• cast MIN with CHAR function,
  example:
  INSERT INTO T1 SELECT CHAR(MIN(CURRENT DATE)) FROM
  SYSIBM.SYSDUMMY1;
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 R810 and R910 Users of aggregate     *
  *                 functions.                                   *
  ****************************************************************
  * PROBLEM DESCRIPTION: An incorrect result set is returned for *
  *                      a query that contains an INSERT from    *
  *                      SELECT with the MIN() function.         *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  An incorrect result set is returned for a query that contains an
  INSERT from SELECT with the MIN function.
  
  The incorrect result is caused by DB2 not processing the
  aggregate (MIN) function correctly.  When the aggregate
  function's argument has a different data type than the insert
  column's data type, the incorrect result may occur.
  
  
  The following example illustrates the problem.
  
  STEP 1. Create table T1.
  
    CREATE TABLE T1 (C1 CHAR(10));
  
  
  STEP 2. Run the following query. Note that the argument for the
  MIN function is a DATE datatype and the column we are inserting
  into is a CHAR datatype.
  
    INSERT INTO T1 SELECT MIN(CURRENT DATE) FROM SYSIBM.SYSDUMMY1;
  
  
  STEP 3. Evaluate the results.
  
  The incorrect result.
  
              +------------+
              |     C1     |
              +------------+
            1_| 0000-00-00 |
              +------------+
  
  Here is the expected result.
  
              +------------+
              |     C1     |
              +------------+
            1_| 2007-12-17 |   <--- current date
              +------------+
  
  Please note that this problem can occur with other aggregate
  functions as well when using a single argument. Some examples
  of aggregate functions are MIN, MAX, and COUNT.
  

Problem conclusion

• The code in DB2 is modified so as to return the correct value
  for a query that contains an INSERT from SELECT with a MIN
  function. This correction will also take care of the cases
  with other aggregate functions used in the same way.
  
  Additional Keywords: SQLMIN SQLINSERT SQLBIF SQLMAX SQLCOUNT
  

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:

  UK32622 UK32623

Modules/Macros

• DSNXGSFL
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• R810 PSY UK32622

     UP08/01/08 P F801

• R910 PSY UK32623

     UP08/01/08 P F801

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.