|
Problem IC70675
|
Status: Geschlossen |
SUM DOUBLE COLUMN WITH VALUES THAT ARE CLOSE TO ZERO MAY RESULT IN INTERMEDIATE VALUES SMALLER THAN THE LIMIT RETURNING SQL0802N |
| Produkt: |
DB2 FOR LUW / DB2FORLUW / 950 - DB2 |
| Problembeschreibung: |
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-Zusammenfassung: |
****************************************************************
* 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: |
|
| verfügbare FixPacks: |
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
|
| Lösung |
First Fixed in DB2 v9.5 Fixpak 7 |
| Workaround |
keiner bekannt / siehe Local-Fix |
| Bug-Verfolgung |
Vorgänger : APAR is sysrouted TO one or more of the following: IC71283
Nachfolger : |
| Weitere Daten |
Datum - Problem gemeldet :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
| 20.08.2010
25.10.2010
25.10.2010 |
| Problem behoben ab folgender Versionen (IBM BugInfos) |
9.5.
|
| Problem behoben lt. FixList in der Version |
| 9.1.0.7
|
 |
| 9.5.0.7
|
|
