MySQL Bugs: #8595: SUM(IFNULL()) on decimal value causes rounding error

Bug #8595	SUM(IFNULL()) on decimal value causes rounding error
Submitted:	18 Feb 2005 1:02	Modified:	18 Feb 2005 1:11
Reporter:	Test Test	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / J	Severity:	S1 (Critical)
Version:	3.1.6	OS:	Windows (XP / Linux)
Assigned to:		CPU Architecture:	Any

Description:
If you perform a SUM(IFNULL()) on a decimal value inside a prepared statement it throws an ArithmeticException : Rounding necessary. Note this only happens for certain values. Please use the values described below.

How to repeat:
MySQL Database 4.1.8 (or 4.1.10)

Connector J 3.1.6

CREATE TABLE `a` (
  `id` int(11) NOT NULL default '0',
  `value` decimal(10,2) NOT NULL default '0.00'
) ENGINE=MyISAM DEFAULT CHARSET=latin1; 

INSERT INTO a VALUES (1, 1.68);

issue code through Java (1.5 Sun JRE):

    String statement = "SELECT SUM(IFNULL(value, 0.00)) AS total FROM a WHERE id = 1";
    stmt = conn.prepareStatement(statement);
    rset = stmt.executeQuery();
    if (rset.next()) {
        BigDecimal result = rset.getBigDecimal("total");
    }

throws an ArithmeticException on rset.getBigDecimal().

Suggested fix:
Debugged through the connector code and it seems to be getting back 1.6899999999 instead of 1.68 causing the rounding required (because scale is set to 2). Something wrong there.

This is a duplicate of BUG#8484 which is fixed for 3.1.7.

The reason 1.689999 is the value that the driver is operating on, is because prior to MySQL-5.0.3, there is no true fixed-point decimal type, math operations (such as SUM()), always convert the value to a double, so the value you see is the closest approximation.