When a BigDecimal value is inserted into a NUMERIC column using the Simba JDBC Driver for BigQuery, why is the numeric precision lost?

Question

When inserting a BigDecimal value into a NUMERIC column using a JDBC prepared statement with a bind variable, the decimal fraction is lost. On the other hand with a bind variable is not used, the decimal fraction is preserved.

create table DS_TEST.BIGDECIMAL_TEST (NUMERIC_COL numeric)

String connectionString = "jdbc:bigquery://${host}:${port};ProjectId=${projectId};OAuthType=${OAuthType};OAuthServiceAcctEmail=${OAuthServiceAcctEmail};OAuthPvtKeyPath=${OAuthPvtKeyPath}"
Class.forName("com.simba.googlebigquery.jdbc42.Driver").newInstance()
Connection connection = DriverManager.getConnection(connectionString)

// insert statement without bind variable
PreparedStatement preparedStatement = connection.prepareStatement
    ("insert into `${projectId}`.DS_TEST.BIGDECIMAL_TEST (NUMERIC_COL) values (123.45)")
preparedStatement.executeUpdate()

// insert statement using bind variable
preparedStatement = connection.prepareStatement
    ("insert into `${projectId}`.DS_TEST.BIGDECIMAL_TEST (NUMERIC_COL) values (?)")
preparedStatement.setBigDecimal(1, new BigDecimal("567.89")) 
preparedStatement.executeUpdate()
preparedStatement.close()

I expected the output:

+-------------+
| NUMERIC_COL |
+-------------+
| 123.45      |
| 567.89      |
+-------------+

but the actual output is

+-------------+
| NUMERIC_COL |
+-------------+
| 123.45      |
| 567         |
+-------------+

Is this a bug or am I missing something?

score 1 · Answer 1 · answered Aug 20 '19 at 19:50

I was able to replicate your scenario as my BigDecimal values are being rounded too. Nevertheless, after digging into this, I found that there's an issue reported where the BigDecimal values passed to the PreparedStatement are being rounded to the nearest integer. I suggest subscribing to it to track its updates. The workaround would be using:

PreparedStatement preparedStatement = connection.prepareStatement
    ("insert into `${projectId}`.DS_TEST.BIGDECIMAL_TEST (NUMERIC_COL) values (123.45)")
preparedStatement.executeUpdate()

As you already are doing.

Hope it helps.

When a BigDecimal value is inserted into a NUMERIC column using the Simba JDBC Driver for BigQuery, why is the numeric precision lost?

1 Answers1