PI76709: AN INCORRECT RESULT CAN BE RETURNED FROM TIMESTAMP SUBTRACTION SELECT CHAR - TIMESTAMP BECAUSE AN IMPROPER PRECISION IS USED.

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• An incorrect result can be returned from timestamp subtraction
  SELECT char - timestamp because an improper precision is used.
  Please reference this example of a failing case.
  Create table TABLE (a char(26), b timestamp);
  insert into TABLE values ('2004-02-15 21:43:55.999999',
  '2004-02-14 21:43:55.999999');
  select a - b, microsecond(a) - microsecond(b), second(a,6) -
  second(b,6),minute(a) - minute(b), hour(a)-hour(b) from TABLE;
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All DB2 12 for z/OS users of queries containing a            *
  * subtraction                                                  *
  * operation involving a timestamp.                             *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * An incorrect result can occur from a query containing a      *
  * subtraction                                                  *
  * operation involving a timestamp operand.                     *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available.                         *
  ****************************************************************
  An incorrect result can occur from a query that contains a
  
   subtraction operation involving a timestamp operand,
  
   e.g. SELECT CHAR - TIMESTAMP, because an
   improper precision is used.
  
  
  
   The following example helps to illustrate a failing query.
  
  
  
   Step 1. Create a Table T1 with a TIMESTAMP column.
  
  
  
     CREATE TABLE T1 (A CHAR(26), B TIMESTAMP);
  
  
  
   Step 2. Insert a row of data into Table T1.
  
    INSERT INTO T1 VALUES ('2004-02-15 21:43:55.999999',
  
                            '2004-02-14 21:43:55.999999');
  
  
  
  Step 3. Perform the following select statement which includes a
  
  subtraction operation involving a timestamp operand.
  
  
  
    SELECT A - B, CAST(A AS TIMESTAMP) - B
  
    FROM T1;
  
  
  
  The incorrect result is as follows:
  
   +-----------------------------------------------+
  
    | 235959.000001 | 1000000.000000 |
  
    +----------------------------------------------+
  
  
  
  The expected result is as follows:
  
   +------------------------------------------------+
  
    | 1000000.000000 | 1000000.000000 |
  
    +------------------------------------------------+
  
  
  Please note:
  In the first subtraction operation in the above query,
  the precision should  come from the subtrahend,
  the quantity being subtracted.
  

Problem conclusion

• DB2 has been modified to correct the problem when using
  
  subtraction involving a timestamp operand.
  
  Please note that the DB2 10 and DB2 11 for z/OS  for this change
  apar is PI73255.
  
  
  In addition, on behalf of this APAR the following publication
  
  changes will also be made.  The rules for subtraction
  
  involving a timestamp operand are incomplete.
  
  The change will be similar to this.
  
  
  
  
  
  In the 'DB2 12 for z/OS SQL Reference'
  
  the rules for subtraction involving a
  
  timestamp operand are incomplete.
  
  The following 'rules' will be added.
  
  
  
  
  
  Datetime arithmetic in SQL section - Subtraction rules
  
  ----------------------------------------------------------------
  --------
  
  Following this sentence,
  
  
  
  'If the first operand is a timestamp, the second operand must
  
  be a timestamp, a string representation of a timestamp,
  
  or a duration'.
  
  
  
  We are adding this sentence  for the  'missing rules'.
  
  ----------------------------------------------------------------
  ------
  If the second operand is a string representation of a
  
  timestamp, it is implicitly converted to a timestamp
  
  with the same precision as the first operand.
  
  ----------------------------------------------------------------
  ------
  
  
  
  and following this sentence,
  
  
  
  'If the second operand is a timestamp, the first operand must be
  
  a timestamp or a string representation of a timestamp'.
  
  
  
  We are adding this sentence  for the  'missing rules'.
  
  ----------------------------------------------------------------
  ---------------
  If the first operand is a string representation of a timestamp,
  
  it is implicitly converted to a timestamp with the same
  
  precision as the second operand.
  
  ----------------------------------------------------------------
  -----------------
  
  
  
  Additional Keywords: INCORROUT SQLINCORROUT DB2INCORR/K
                                     SQLINCORR SQLTIMESTAMP
  SQLSUBTRACTION
  

Temporary fix

Comments

APAR Information

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

  PI73255

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

  UI47073

Modules/Macros

• DSNXGSC2
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI47073

     UP17/05/24 P F705 ¢

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.