Incorrect querying result via jdbc

Question

When querying values from Snowflake via jdbc driver snowflake-jdbc-3.12.5.jar, the result is incorrect.

1. The table definition in Snowflake:

create table test_dob(DOB DECIMAL(38,3),REL DECIMAL(38,5));
insert into test_dob values(0.99, 0.99);

2. Querying values from table 'test_dob' via jdbc driver snowflake-jdbc-3.12.5.jar;

.....
Connection conn = DriverManager.getConnection("jdbc:snowflake://XXXXX.aws.snowflakecomputing.com?db=testdbu", "user", "password");

......
String sql = "select dob,rel from test_dob";
PreparedStatement stmt = conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery();
  while (rs.next()) {
    for (int j = 1; j < rs.getMetaData().getColumnCount() + 1; j++) {
      //System.out.print(rs.getString(j) + ",");
      System.out.print(rs.getDouble(j) + ",");
  }
.......

The querying results are:

 0.9900000095367432,0.99, 

You can get that the querying value of first column("dob") is incorrect, but the second("rel") is correct.

Is it a bug for driver?

Answer 1

The right ResultSet method to use for a SQL type of DECIMAL is ResultSet::getBigDecimal(col).

By asking for a double type via ResultSet::getDouble(col) instead, you're forcing a conversion of the underlying numeric representation.

Given the differing scales (3 and 5) in your SQL decimal/number data types, the conversion to double is handled differently for each case. For the smaller scale (3), a small-int type is used against the transferred value due to its limited bit-width, and its double conversion actually uses a float type underneath that upcasts to a double. For the larger scale (5), a regular integer is used and the double conversion is done directly.

Here's a pure Java (via jshell) representation of what is going on behind the scenes

~> jshell

jshell> short sv = 990; // with scale 3
jshell> (double) ((float) sv / (float) 1000l)
$n ==> 0.9900000095367432

jshell> int iv = 99000; // with scale 5
jshell> (double) ((double) iv / (double) 100000l)
$n ==> 0.99

Is it a bug for driver?

The double conversion done in the byte/short/int cases can certainly be unified for improved behaviour, but it will not solve the problem beyond the use of 0.99 as a test example. Other DECIMAL typed numbers that cannot be represented precisely in double will continue to surface.

While the conversions will yield different results because of the bits used to represent each type, both are valid from a driver-correctness perspective because by asking for a double in place of a BigDecimal you are indicating that you do not require a precise representation of the number being retrieved.

If precision/correctness is required (such as for handling currency, or in certain scientific applications), use the following instead:

System.out.print(rs.getBigDecimal(j) + ",");

For understanding the lossy precisions of float and double types, read: Is floating point math broken?