Can't use Java Double.MIN_VALUE 4.9E-324 as a floating point literal in SQL Server 2019

user_521799 0
Viewed 370

Running the following JDBC code with SQL Server:

try (PreparedStatement s = connection.prepareStatement("select 4.9E-324, ?")) {
    s.setDouble(1, 4.9E-324);

    try (ResultSet rs = s.executeQuery()) {
        while (rs.next()) {
            System.out.println(rs.getDouble(1));
            System.out.println(rs.getDouble(2));
        }
    }
}

The result is:

0.0
4.9E-324

So, while the bind value is echoed back correctly, the literal is not. How can this be explained? I've noticed in the docs.

Any float value less than 5E-18 (when set using either the scientific notation of 5E-18 or the decimal notation of 0.0000000000000000050000000000000005) rounds down to 0. This is no longer a restriction as of SQL Server 2016 (13.x).

But I'm using SQL Server 2019

select @@version

Yields:

Microsoft SQL Server 2019 (RTM-CU8-GDR) (KB4583459) - 15.0.4083.2 (X64) 
Nov  2 2020 18:35:09 
Copyright (C) 2019 Microsoft Corporation
Express Edition (64-bit) on Linux (Ubuntu 18.04.5 LTS) <X64>

And the latest version of SQL Server's JDBC driver

<dependency>
  <groupId>com.microsoft.sqlserver</groupId>
  <artifactId>mssql-jdbc</artifactId>
  <version>9.2.1.jre8</version>
</dependency>
jdbc floating-point sql-server
1 Answers

That same documentation page also lists the range of valid float values to be:

-1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308

So, the Java Double.MIN_VALUE is out of range. This works:

try (PreparedStatement s = connection.prepareStatement("select 2.23E-308, ?")) {
    s.setDouble(1, 2.23E-308);

    try (ResultSet rs = s.executeQuery()) {
        while (rs.next()) {
            System.out.println(rs.getDouble(1));
            System.out.println(rs.getDouble(2));
        }
    }
}

Producing:

2.23E-308
2.23E-308

It doesn't explain why the bind variable is echoed back correctly, though.

user_5217990
Related