SNOW-572407: Floating point values are truncated

[slawo-ch]

When reading a double value from the server, it is always truncated to 8 digits. Full range of 16 digits should be preserved.

Test here: https://github.com/slawo-ch/SnowflakeBugs/blob/main/src/test/java/FloatsTest.java

Possibly related to ALTER SESSION SET JDBC_QUERY_RESULT_FORMAT='JSON'; which is a required setting unless you want to run insecure VMs.

See: #533

[wheezil]

+1 seems like a pretty basic flaw.

[sfc-gh-skumbham]

Thank you for reporting this issue. It appears that the problem is a known issue with JSON formatting. The solution is to switch to the arrow format, which will resolve the problem. We have reported this to the backend team, and once they have addressed the issue, the JDBC driver should be able to handle precision loss without any changes. In the meantime, you can try using the to_decimal/to_number functions to retain precision.

[slawo-ch]

This is playing catch 22 with the JDBC driver

• 10 the solution to not having to open up the internals of the JVM is to switch to JSON
• 20 the solution to having correct data from the driver is to switch to arrow
• GOTO 10

[sfc-gh-wfateem]

@slawo-ch The solution to (10) is not necessarily switching to JSON. You just need to add the JVM arguments discussed in #533 and #589 to have the JVM allow deep reflection, which was what was already happening pre-JDK 16.

[jdimeo]

So to get lossless double handling we must use Arrow, but to handle JDK 17 we must use JSON... this is quite a problem

[wheezil]

The to_decimal/to_number workaround is not applicable to general double-precision values, because the range of exponents cannot be expressed as a fixed-point result.

Is there any other workaround? Can double-precision values be cast to text and preserve the full precision and range, e.g. 1.23456789012345E+123. There is a lot of flexibility documented at https://docs.snowflake.com/en/sql-reference/sql-format-models, but I'm looking for an example of "this format always works".

I'm guessing that to_char(VALUE, '9d99999999999999EEEE') is close. It is not clearly defined how to pick the correct exponent format, from the doc:

The EE element automatically picks the right number of digits in the exponent, and does not print the + sign, while EEE, EEEE, and EEEEE always print the + or - sign for the exponent and the requested number of digits (leading zeros are not suppressed).

OK, EE chooses the right # of digits, but omits the + sign; does it print the - sign if needed? EEEE has leading zeros in the exponent are a little weird but Java Double.parseDouble() accepts it, so I'm thinking this is maybe the answer?

[mmendonca2]

Curious to know why this issue was closed. Was the underlying bug fixed?

[wheezil]

Closed as "that sounds hard" ;-)