|
Problem IC70675
|
Status: Closed |
SUM DOUBLE COLUMN WITH VALUES THAT ARE CLOSE TO ZERO MAY RESULT IN INTERMEDIATE VALUES SMALLER THAN THE LIMIT RETURNING SQL0802N |
| product: |
DB2 FOR LUW / DB2FORLUW / 950 - DB2 |
| Problem description: |
SUM of a DOUBLE column might return SQL0802N if an intermediate
floating point calculation returns a value that is
outside the range for DOUBLE, even though a reasonable result is
expected.
The following example could reasonably return a SUM of 0, but
instead
generates an error.
db2 connect to sample
db2 "drop table mbj"
db2 "CREATE TABLE mbj(value INTEGER,number DOUBLE)"
db2 "INSERT INTO mbj VALUES (1, -1.99000000000000E-300)"
db2 "INSERT INTO mbj VALUES (1, -1.99000000000000E-300)"
db2 "INSERT INTO mbj VALUES (1, -1.99000000000000E-300)"
db2 "INSERT INTO mbj VALUES (1, -1.99000000000000E-300)"
db2 "INSERT INTO mbj VALUES (1, -1.99000000000000E-300)"
db2 "INSERT INTO mbj VALUES (1, -1.99000000000000E-300)"
db2 "INSERT INTO mbj VALUES (1, -1.99000000000000E-300)"
db2 "INSERT INTO mbj VALUES (1, -1.99000000000000E-300)"
db2 "INSERT INTO mbj VALUES (1, -1.99000000000000E-300)"
db2 "INSERT INTO mbj VALUES (1, -1.99000000000000E-300)"
db2 "INSERT INTO mbj VALUES (1, 1.99000000000000E-300)"
db2 "INSERT INTO mbj VALUES (1, 1.99000000000000E-300)"
db2 "INSERT INTO mbj VALUES (1, 1.99000000000000E-300)"
db2 "INSERT INTO mbj VALUES (1, 3.98000000000000E-300)"
db2 "INSERT INTO mbj VALUES (1, 1.99000000000000E-300)"
db2 "INSERT INTO mbj VALUES (1, 1.99000000000000E-300)"
db2 "INSERT INTO mbj VALUES (1, 1.99000000000000E-300)"
db2 "INSERT INTO mbj VALUES (1, 1.99000000000000E-300)"
db2 "INSERT INTO mbj VALUES (1, 1.99000000000000E-300)"
db2 "SELECT SUM(number) FROM mbj"
Since the sum of the negative rows appears to be equivalent to
the sum
of the positive rows, a result of zero would be expected.
>db2 "SELECT SUM(number) FROM mbj"
1
------------------------
SQL0802N Arithmetic overflow or other arithmetic exception
occurred.
SQLSTATE=22003
This error might not occur with similar examples against other
database Servers.
This behavior is working as designed since double is not a
precise data type, but we will accommodate requirements from
applications like SAP and return 0 instead of SQL0802N in this
scenario when SAP is used.
Workaround:
Cast column to DECFLOAT or DECIMAL:
db2 "select SUM( DECFLOAT(number)) from mbj"
or
Use DECFLOAT or DECIMAL data type instead. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All Platforms *
****************************************************************
* PROBLEM DESCRIPTION: *
* SUM of a DOUBLE column might return SQL0802N if *
* anintermediatefloating point calculation returns a value *
* that isoutside the range for DOUBLE, even though a *
* reasonableresult isexpected.The following example could *
* reasonably return a SUM of 0,butinsteadgenerates an *
* error.db2 connect to sampledb2 "drop table mbj"db2 "CREATE *
* TABLE mbj(value INTEGER,number DOUBLE)"db2 "INSERT INTO mbj *
* VALUES (1, -1.99000000000000E-300)"db2 "INSERT INTO mbj *
* VALUES (1, -1.99000000000000E-300)"db2 "INSERT INTO mbj *
* VALUES (1, -1.99000000000000E-300)"db2 "INSERT INTO mbj *
* VALUES (1, -1.99000000000000E-300)"db2 "INSERT INTO mbj *
* VALUES (1, -1.99000000000000E-300)"db2 "INSERT INTO mbj *
* VALUES (1, -1.99000000000000E-300)"db2 "INSERT INTO mbj *
* VALUES (1, -1.99000000000000E-300)"db2 "INSERT INTO mbj *
* VALUES (1, -1.99000000000000E-300)"db2 "INSERT INTO mbj *
* VALUES (1, -1.99000000000000E-300)"db2 "INSERT INTO mbj *
* VALUES (1, -1.99000000000000E-300)"db2 "INSERT INTO mbj *
* VALUES (1, 1.99000000000000E-300)"db2 "INSERT INTO mbj *
* VALUES (1, 1.99000000000000E-300)"db2 "INSERT INTO mbj *
* VALUES (1, 1.99000000000000E-300)"db2 "INSERT INTO mbj *
* VALUES (1, 3.98000000000000E-300)"db2 "INSERT INTO mbj *
* VALUES (1, 1.99000000000000E-300)"db2 "INSERT INTO mbj *
* VALUES (1, 1.99000000000000E-300)"db2 "INSERT INTO mbj *
* VALUES (1, 1.99000000000000E-300)"db2 "INSERT INTO mbj *
* VALUES (1, 1.99000000000000E-300)"db2 "INSERT INTO mbj *
* VALUES (1, 1.99000000000000E-300)"db2 "SELECT SUM(number) *
* FROM mbj"Since the sum of the negative rows appears to be *
* equivalenttothe sumof the positive rows, a result of zero *
* would be expected.>db2 "SELECT SUM(number) FROM *
* mbj"1------------------------SQL0802N Arithmetic overflow *
* or other arithmetic exceptionoccurred.SQLSTATE=22003This *
* error might not occur with similar examples *
* againstotherdatabase Servers.This behavior is working as *
* designed since double is not aprecise data type, but we will *
* accommodate requirements fromapplications like SAP and *
* return 0 instead of SQL0802N inthisscenario when SAP is *
* used.Workaround:Cast column to DECFLOAT or DECIMAL:db2 *
* "select SUM( DECFLOAT(number)) from mbj"orUse DECFLOAT or *
* DECIMAL data type instead. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 v9.5 Fixpak 7 or higher *
**************************************************************** |
| Local Fix: |
|
| available fix packs: |
DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
First Fixed in DB2 v9.5 Fixpak 7 |
| Workaround |
not known / see Local fix |
| BUG-Tracking |
forerunner : APAR is sysrouted TO one or more of the following: IC71283
follow-up : |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 20.08.2010
25.10.2010
25.10.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.5.
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.1.0.7
|
 |
| 9.5.0.7
|
|
