When trying to insert a record into the Snowflake database using Spring JDBC, I am getting an exception. The only work around I have is to revert to using jdbcTemplate.execute but I would prefer to use SimpleJdbcInsert so I can receive the generated key column for the row inserted.
I filed a ticket with Snowflake support and their engineers have provided analysis that leads them to believe the issue is with the Spring JDBC code. The code for the insert is immediately below and the Snowflake engineer's analysis follows that.
// the code below does not work
private void insertRecord() {
SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(jdbcTemplate)
.withTableName("JOE_LOAD_AUDIT")
.usingGeneratedKeyColumns("ROW_ID");
// Populate the record's fields.
Map<String, Object> parameters = new HashMap<>();
parameters.put("FILE_NAME", "myfile.csv");
parameters.put("FILE_SIZE", 123);
parameters.put("STATUS", "LOADED");
parameters.put("SOURCE_COMPONENT_NAME", "snowflake-test");
// Insert the record and save the surrogate id for the record.
Number id = simpleJdbcInsert.executeAndReturnKey(parameters);
log.info("Record inserted, id={}", id.longValue());
}
The exception is as follows:
2020-03-30 12:29:35.831 JPARDIPC snowflake-test ERROR 16588 --- [ main] o.s.boot.SpringApplication :826 : Application run failed
java.lang.IllegalStateException: Failed to execute ApplicationRunner
at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:778)
at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:765)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:322)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1226)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1215)
at com.covetrus.test.SnowflakeTestApplication.main(SnowflakeTestApplication.java:42)
Caused by: java.lang.ClassCastException: class java.lang.Integer cannot be cast to class java.lang.Boolean (java.lang.Integer and java.lang.Boolean are in module java.base of loader 'bootstrap')
at net.snowflake.client.jdbc.SnowflakeDatabaseMetaDataResultSet.getBoolean(SnowflakeDatabaseMetaDataResultSet.java:345)
at net.snowflake.client.jdbc.SnowflakeBaseResultSet.getBoolean(SnowflakeBaseResultSet.java:167)
at org.springframework.jdbc.core.metadata.GenericTableMetaDataProvider.processTableColumns(GenericTableMetaDataProvider.java:414)
at org.springframework.jdbc.core.metadata.GenericTableMetaDataProvider.locateTableAndProcessMetaData(GenericTableMetaDataProvider.java:346)
at org.springframework.jdbc.core.metadata.GenericTableMetaDataProvider.initializeWithTableColumnMetaData(GenericTableMetaDataProvider.java:222)
at org.springframework.jdbc.core.metadata.TableMetaDataProviderFactory.lambda$createMetaDataProvider$0(TableMetaDataProviderFactory.java:80)
at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:342)
at org.springframework.jdbc.core.metadata.TableMetaDataProviderFactory.createMetaDataProvider(TableMetaDataProviderFactory.java:52)
at org.springframework.jdbc.core.metadata.TableMetaDataContext.processMetaData(TableMetaDataContext.java:171)
at org.springframework.jdbc.core.simple.AbstractJdbcInsert.compileInternal(AbstractJdbcInsert.java:277)
at org.springframework.jdbc.core.simple.AbstractJdbcInsert.compile(AbstractJdbcInsert.java:261)
at org.springframework.jdbc.core.simple.AbstractJdbcInsert.checkCompiled(AbstractJdbcInsert.java:309)
at org.springframework.jdbc.core.simple.AbstractJdbcInsert.doExecuteAndReturnKey(AbstractJdbcInsert.java:368)
at org.springframework.jdbc.core.simple.SimpleJdbcInsert.executeAndReturnKey(SimpleJdbcInsert.java:127)
at com.covetrus.test.SnowflakeTestApplication.insertRecord(SnowflakeTestApplication.java:107)
at com.covetrus.test.SnowflakeTestApplication.run(SnowflakeTestApplication.java:48)
at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:775)
... 5 common frames omitted
The Engineering team has completed their investigation on this issue. Please find below details.
They have classified it as a bug within Spring, not the Snowflake JDBC driver.
The Spring application contains a file called GenericTableMetaDataProvider.class, which contains the function processTableColumns(DatabaseMetaData databaseMetaData, GenericTableMetaDataProvider.TableMetaData tmd). Within this function, there is the statement
boolean nullable = tableColumns.getBoolean("NULLABLE");
However, the NULLABLE column returns an integer, not a boolean. This can be seen in the documentation of JDBC getColumns() here:
The Nullable column contains 1 of 3 integer values:
/**
* The constant indicating that a
* column does not allow <code>NULL</code> values.
*/
int columnNoNulls = 0;
/**
* The constant indicating that a
* column allows <code>NULL</code> values.
*/
int columnNullable = 1;
/**
* The constant indicating that the
* nullability of a column's values is unknown.
*/
int columnNullableUnknown = 2;
So an easy fix on Spring's end to fix this issue would be to change the problem statement from this:
boolean nullable = tableColumns.getBoolean("NULLABLE");
To this:
int nullableInt = tableColumns.getInt("NULLABLE");
boolean nullable = (nullableInt == 1);
// this works for values of 0 or 1 but they'll have to decide what to do if nullableInt is equal to 2 (unknown)
It is possible for us to make a change to allow integer values to be returned as booleans (0 == false, >=1 == true), but this comes with the caveat that not all values would be returned correctly. For example, in this case, then a value of NullableUnknown would be returned as TRUE and some information would be lost. A better solution would be for Spring to make the change.
Kindly let me know in case of any further question or I can close this.
Comment From: snicoll
Thanks for the report but neither of those are part of Spring Boot. Moving this to the appropriate issue tracker.
Comment From: jhoeller
As far as I can see, JDBC has explicit rules for how to treat integer values when calling ResultSet.getBoolean: https://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html#getBoolean(java.lang.String)
So our call there seems entirely valid, expecting 0 to be returned as false and 1 as true. Admittedly the JDBC rules leave it unclear what happens in case of 2 there; the HSQLDB implementation returns any other value as true as well which seems sensible.
With that rule in mind, I'd rather keep our call as getBoolean, leaving it up to the JDBC driver. I have not heard about an incompatibility with any other driver in more than ten years there, so this seems commonly supported. Could Snowflake simply implement those getBoolean rules as well?
Comment From: joepardi
Thank you Juergen, I have posted your response to their support portal. They usually take a few days to respond, so I'll be sure to surface their reply once they do.
Comment From: joepardi
Update on this ... the Snowflake engineering team has fixed this problem in version 3.12.6 of their driver. Closing this issue.
Comment From: jhoeller
Good to hear! Thanks for following up.