scruz-denodo commented on PR #1407:
URL: https://github.com/apache/arrow-java/pull/1407#issuecomment-6438823595
Hi,
the root cause is with how Java does the cast from the `float`. In that
process is where the precision of the initial number is lost.
Here is a testing class showing the root cause and results returned by
Postgresq, SQLServer, Oracle, Gizmo (for testing a flight-sql database) and
Duckdb.
``` java
package scruz.test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class FloatTests {
public static void main(String args[]) throws Exception {
TestConfig[] testConfigs = new TestConfig[]{
new TestConfig("jdbc:postgresql://localhost:5432/vdp",
"org.postgresql.Driver", "xxxx", "xxxx", "SELECT CAST(0.1 AS real)"),
new
TestConfig("jdbc:sqlserver://<host>:1433;databaseName=master;encrypt=false",
"com.microsoft.sqlserver.jdbc.SQLServerDriver", "xxxx", "xxxx", "SELECT
CAST(0.1 AS real);"),
new TestConfig("jdbc:oracle:thin:@<host>:1521/ORCLPDB1",
"oracle.jdbc.OracleDriver", "xxxx", "xxxx", "SELECT CAST(0.1 AS real) FROM
dual"),
new
TestConfig("jdbc:arrow-flight-sql://localhost:31337?useEncryption=0&disableCertificateVerification=true",
"org.apache.arrow.driver.jdbc.ArrowFlightJdbcDriver", "admin", "admin",
"SELECT CAST(0.1 AS real)"),
new TestConfig("jdbc:duckdb:", "org.duckdb.DuckDBDriver", "",
"", "SELECT CAST(0.1 AS real)"),
};
for (TestConfig testConfig : testConfigs) {
Class.forName(testConfig.DRIVER);
System.out.println("\nTesting " + testConfig.URI);
try (Connection connection =
DriverManager.getConnection(testConfig.URI, testConfig.USER,
testConfig.PASSWORD);
PreparedStatement pst =
connection.prepareStatement(testConfig.QUERY);
ResultSet rs = pst.executeQuery()) {
rs.next();
System.out.println("getString() " + rs.getString(1));
System.out.println("getObject() " + rs.getObject(1));
System.out.println("getFloat() " + rs.getFloat(1));
System.out.println("getDouble() " + rs.getDouble(1));
System.out.println("getBigDecimal() " + rs.getBigDecimal(1));
}
}
//Java isolated test
System.out.println("\nJava isolated");
final float value = 0.1f;
System.out.println("value: " + value);
System.out.println("value (with cast double): " + (double)value);
System.out.println("value (with double + toString): " +
Double.parseDouble(Float.toString(value)));
}
private static class TestConfig {
final String URI;
final String DRIVER;
final String USER;
final String PASSWORD;
final String QUERY;
TestConfig(String URI, String DRIVER, String USER, String PASSWORD,
String QUERY) {
this.URI = URI;
this.DRIVER = DRIVER;
this.USER = USER;
this.PASSWORD = PASSWORD;
this.QUERY = QUERY;
}
}
}
```
The output shows that:
- Oracle, and Postgresql do not have problems with the conversion.
- SQLServer and Duckdb have problem with the `getDouble` method returning
`0.10000000149011612`
- Gizmo through flight-sql JDBC driver have problem with `getObject`,
`getDouble` and `getBigDecimal`
The isolated java test shows the root cause and the proposed solution.
This is the entire output.
``` shell
Testing jdbc:postgresql://localhost:5432/vdp
getString() 0.1
getObject() 0.1
getFloat() 0.1
getDouble() 0.1
getBigDecimal() 0.1
Testing jdbc:sqlserver://<host>:1433;databaseName=master;encrypt=false
getString() 0.1
getObject() 0.1
getFloat() 0.1
getDouble() 0.10000000149011612
getBigDecimal() 0.1
Testing jdbc:oracle:thin:@<host>:1521/ORCLPDB1
getString() 0.1
getObject() 0.1
getFloat() 0.1
getDouble() 0.1
getBigDecimal() 0.1
Testing
jdbc:arrow-flight-sql://localhost:31337?useEncryption=0&disableCertificateVerification=true
getString() 0.1
getObject() 0.10000000149011612
getFloat() 0.1
getDouble() 0.10000000149011612
getBigDecimal() 0.10000000149011612
Testing jdbc:duckdb:
getString() 0.1
getObject() 0.1
getFloat() 0.1
getDouble() 0.10000000149011612
getBigDecimal() 0.1
Java isolated
value: 0.1
value (with cast double): 0.10000000149011612
value (with double + toString): 0.1
```
About the proposed solution, we are already using that solution for avoiding
the issue on other sites for a long time.
I was searching for alternatives but I do not find any other fix which can
deal with the 100% of scenarios.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
