Description:
I update a DOUBLE column with the value BigDecimal("123.0000000000000000000000000000000").
When reading this column back, I get the value 122.9999999999999900000000000000000.
When updating the column with none or less than 30 decimals, the result is correctly 123.0000000000000000000000000000000 (always with 31 decimals; BigDecimal internal values: string=1230000000000000000000000000000000, scale=31)
Running the same code with a 3.0.x driver returns a BigDecimal(123) - with a scale of 0 and a string value of 123 (tested with 3.0.11 and 3.0.17).
The error is about reading - a value written with a version 3.1 driver is correctly read with a version 3.0 driver. Whereas a value written with a version 3.0 driver is not read correctly with the version 3.1 driver.
The mysql server version is:
mysql Ver 14.7 Distrib 4.1.13a, for Win32 (ia32)
How to repeat:
mysql: CREATE TABLE test(amount DOUBLE) TYPE = InnoDB
Java code snippet:
Connection connection = null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://localhost/<dbname>", "root", "");
// Update
PreparedStatement updateStatement = connection.prepareStatement("update test set amount=?");
BigDecimal newValue = new BigDecimal("123.0000000000000000000000000000000");
//BigDecimal newValue = new BigDecimal("123.000000000000000000000000000000");
//BigDecimal newValue = new BigDecimal("123.00000000000000000000000000000");
//BigDecimal newValue = new BigDecimal("123");
updateStatement.setBigDecimal(1, newValue);
int updatedRows = updateStatement.executeUpdate();
updateStatement.close();
// Select back
PreparedStatement statement = connection.prepareStatement("select amount from test");
ResultSet resultSet = statement.executeQuery();
while (resultSet.next()) {
BigDecimal amount = resultSet.getBigDecimal(1);
System.out.println("amount=" + amount);
}
resultSet.close();
statement.close();
}
catch(SQLException sqlEx) {sqlEx.printStackTrace(System.err);}
catch(ClassNotFoundException cnfEx) {cnfEx.printStackTrace(System.err);}
finally{
if (connection != null) {
try {connection.close();}
catch(SQLException sqlEx) {sqlEx.printStackTrace(System.err);}
}
}
Description: I update a DOUBLE column with the value BigDecimal("123.0000000000000000000000000000000"). When reading this column back, I get the value 122.9999999999999900000000000000000. When updating the column with none or less than 30 decimals, the result is correctly 123.0000000000000000000000000000000 (always with 31 decimals; BigDecimal internal values: string=1230000000000000000000000000000000, scale=31) Running the same code with a 3.0.x driver returns a BigDecimal(123) - with a scale of 0 and a string value of 123 (tested with 3.0.11 and 3.0.17). The error is about reading - a value written with a version 3.1 driver is correctly read with a version 3.0 driver. Whereas a value written with a version 3.0 driver is not read correctly with the version 3.1 driver. The mysql server version is: mysql Ver 14.7 Distrib 4.1.13a, for Win32 (ia32) How to repeat: mysql: CREATE TABLE test(amount DOUBLE) TYPE = InnoDB Java code snippet: Connection connection = null; try { Class.forName("com.mysql.jdbc.Driver"); connection = DriverManager.getConnection("jdbc:mysql://localhost/<dbname>", "root", ""); // Update PreparedStatement updateStatement = connection.prepareStatement("update test set amount=?"); BigDecimal newValue = new BigDecimal("123.0000000000000000000000000000000"); //BigDecimal newValue = new BigDecimal("123.000000000000000000000000000000"); //BigDecimal newValue = new BigDecimal("123.00000000000000000000000000000"); //BigDecimal newValue = new BigDecimal("123"); updateStatement.setBigDecimal(1, newValue); int updatedRows = updateStatement.executeUpdate(); updateStatement.close(); // Select back PreparedStatement statement = connection.prepareStatement("select amount from test"); ResultSet resultSet = statement.executeQuery(); while (resultSet.next()) { BigDecimal amount = resultSet.getBigDecimal(1); System.out.println("amount=" + amount); } resultSet.close(); statement.close(); } catch(SQLException sqlEx) {sqlEx.printStackTrace(System.err);} catch(ClassNotFoundException cnfEx) {cnfEx.printStackTrace(System.err);} finally{ if (connection != null) { try {connection.close();} catch(SQLException sqlEx) {sqlEx.printStackTrace(System.err);} } }