#### DB2 - Problem description

Problem IC70675 | Status: Closed |

SUM DOUBLE COLUMN WITH VALUES THAT ARE CLOSE TO ZERO MAY RESULT IN | |

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

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 |