Bug #6537 SUM() function loses precision with Connector J and 4.1 database
Submitted: 10 Nov 2004 3:07 Modified: 11 Nov 2004 0:09
Reporter: [ name withheld ]
Status: Closed
Category:Connector/J Severity:S2 (Serious)
Version:3.0.15-ga OS:Linux (Linux)
Assigned to: Mark Matthews Target Version:

[10 Nov 2004 3:07] [ name withheld ] 
Description:
If a decimal column with a precision of 2 is used in a SUM() function, the precision
returned will be 1 (if using the standard jdbc driver). I have tried the same query with
mysql 4.0 and it works fine. It also works fine on the command line and through the
control centre. Thus it must be a problem with the jdbc driver.

How to repeat:
CREATE TABLE `my_data` (
  `id` int(11) NOT NULL default '0',
  `value` decimal(10,2) NOT NULL default '0.00',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO my_data (id, value) VALUES (1, 100.00);

SELECT SUM(value) FROM my_data;

This query works fine on the command line (ie. it returns 100.00). But if you run it
through a Java JDBC query such as:

//....
String statement = "SELECT SUM(value) as total FROM my_data WHERE id = ?";
PreparedStatement stmt = conn.prepareStatement(statement);
stmt.setInt(1, 1);
rset = stmt.executeQuery();
if (rset.next()) {
    BigDecimal result = rset.getBigDecimal("total");
    System.out.println(result);
}
//....

you will get 100.0 which is clearly wrong. Note this works fine on mysql 4.0, but exhibits
this behaviour on 4.1.

Suggested fix:
Fix the JDBC driver?
[11 Nov 2004 0:09] Mark Matthews 
I couldn't repeat this in 3.0.15, however it is repeatable in 3.1.x with server-side
prepared statements (due to the server converting to a Double before sending it because of
the SUM(), and thus dropping the 'extra' zeroes, which doesn't happen when not using
prepared statements, because the values are _always_ sent back to the client as strings,
with formatting intact.

This is now fixed, and will be available in 3.1.5.