DB2 - Problembeschreibung
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 | |
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 |