PI73255: 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 10 and DB2 11 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:                                              *
  ****************************************************************
  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.
  
  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 both the 'DB2 10 for z/OS SQL Reference'
  and the 'DB2 11 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

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

Comments

APAR Information

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

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

  PI76709 UI44988 UI44989

Modules/Macros

• DSNXGSC2
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UI44988

     UP17/03/15 P F703 Ž

• RB10 PSY UI44989

     UP17/03/15 P F703 Ž

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.