SNOW-165204: BigDecimal specified as java.sql.Types.BIGINT

[daniel-smith]

We have a table with a column of type NUMBER(38,0). When querying the table with JDBC, getColumnType for this column from the metadata returns java.sql.Types.BIGINT, which I understand is intended for 64-bit integers (i.e. long). Going by BIGINT and attempting to retrieve large values via getLong understandably results in an exception:

net.snowflake.client.jdbc.SnowflakeSQLException: Cannot convert value in the driver from type:FIXED(null,null) to type:Long, value=5204557519631944020283414925144879961.
        at net.snowflake.client.jdbc.SnowflakeResultSetV1.getLong(SnowflakeResultSetV1.java:246)

Retrieving values for this column via getObject yields types of BigDecimal, which I understand should be represented with either java.sql.Types.NUMERIC or java.sql.Types.DECIMAL.

[daniel-smith]

It's also worth noting that all values in this particular column are at the upper or lower extremes of NUMBER(38,0). I have observed that getObject for NUMBER(38,0) does yield long values when the sizes are small enough.

[ash211]

I've been encouraging my users to work around this issue by casting to a string in the SQL query and parsing back to an arbitrary precision integer number (like a Java BigInteger) downstream as needed.

CAST(columnName as VARCHAR(20)) as columnName

But I think setting JDBC_TREAT_DECIMAL_AS_INT to false (default is true) would resolve this and get the behavior that JDBC expects. Otherwise, as you mention, getColumnType returns a java.sql.Types.BIGINT and retrieving it with getLong then fails.

@daniel-smith have you experimented with that setting? I don't know why it defaults to true, which doesn't seem like it would pass a JDBC compliance test.

[sfc-gh-wfateem]

This slipped under my radar. I'll review this in the coming days.

[sfc-gh-wfateem]

This isn't an issue. We had to add the parameter JDBC_TREAT_DECIMAL_AS_INT to help in these kinds of situations. The issue is that when you have a NUMBER column with 0 scale, i.e. NUMBER(38,0) you can store very large values in the column in Snowflake. The column metadata for the NUMBER column is java SQL type BIGINT, which as pointed out by @daniel-smith according to the JDBC specs, should map to a Java long. Values like 5204557519631944020283414925144879961 will obviously fail if you try to use the getLong method. There's not a whole lot we can do on the driver's side other than offer you the option to decide what column type is used in the metadata to represent a NUMBER column. By setting the parameter JDBC_TREAT_DECIMAL_AS_INT to FALSE the column type returned by ResultSetMetaData.getColumnType() becomes a java SQL type DECIMAL, which the JDBC specs outlines should be mapped to a java.Math.BigDecimal, so an application should then understand to use the ResultSet.getBigDecimal() method instead to retrieve the value.

[sfc-gh-dszmolka]

closing this issue per above comment, but please do comment if you need further assistance.