Bug #6537 SUM() function loses precision with Connector J and 4.1 database

• Submitted: 10 Nov 2004 2:07
• Modified: 10 Nov 2004 23:09
• Reporter: Test Test
• Status: Closed
• Category: Connector / J
• Severity: S2 (Serious)
• Version: 3.0.15-ga
• OS: Linux (Linux)
• Assigned to: Mark Matthews
• CPU Architecture: Any

[10 Nov 2004 2:07] Test Test

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?

[10 Nov 2004 23: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.