[
https://issues.apache.org/jira/browse/DERBY-7095?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17258494#comment-17258494
]
Richard N. Hillegas commented on DERBY-7095:
--------------------------------------------
Hi Will,
I think that JPA is going to get into trouble if it assumes that a SQL FLOAT
type is always a 32-bit floating point number. The FLOAT type takes a precision
argument. Depending on the precision, the underlying storage type can be 32-bit
or 64-bit, that is, it can correspond to either a Java float or a Java double.
Even worse for JPA, the default precision of a FLOAT is vendor-specific,
according to the 2016 SQL Standard, part 2, subclause 6.1 (<data type>), syntax
rule 20.
JPA is always going to incur casting anomalies (and variable test results) if
it adopts a hard and fast rule of always using
java.sql.PreparedStatement.setFloat() or always using
java.sql.PreparedStatement.setDouble() to set a FLOAT parameter. JPA needs to
be sensitive to the precision of the FLOAT and to the default precision of the
target database. You should be able to get the precision from
java.sql.ParameterMetaData.getPrecision().
Hope this helps,
-Rick
> Different query results with parameter binding vs literals
> ----------------------------------------------------------
>
> Key: DERBY-7095
> URL: https://issues.apache.org/jira/browse/DERBY-7095
> Project: Derby
> Issue Type: Bug
> Components: JDBC
> Affects Versions: 10.12.1.1
> Reporter: Will Dazey
> Priority: Minor
>
> I was running some tests locally today and I noticed a weird behavior when
> executing SQL with parameters vs literals. Maybe I am wrong here, but it
> seems wrong to me.
> Here is the simple test I threw together:
> {code:java}
> cstmt = con.prepareCall("CREATE TABLE SIMPLE_TABLE (ID1 FLOAT NOT NULL,
> ID2 FLOAT NOT NULL, STRING01 VARCHAR(255), PRIMARY KEY (ID1, ID2))");
> cstmt.execute();
> cstmt = con.prepareCall("INSERT INTO SIMPLE_TABLE (ID1,ID2,STRING01)
> VALUES (108.01080322265625,109.01090240478516,'TEST_STR')");
> cstmt.execute();
>
> cstmt = con.prepareCall("SELECT ID1, ID2, STRING01 FROM SIMPLE_TABLE
> WHERE ((ID1 = 108.0108) AND (ID2 = 109.0109))");
> ResultSet res = cstmt.executeQuery();
> System.out.println("Test literals: ");
> while(res.next()) {
> System.out.println(res.getFloat("ID1"));
> System.out.println(res.getFloat("ID2"));
> }
> System.out.println();
> cstmt = con.prepareCall("SELECT ID1, ID2, STRING01 FROM SIMPLE_TABLE
> WHERE ((ID1 = ?) AND (ID2 = ?))");
> cstmt.setFloat(1, 108.0108f);
> cstmt.setFloat(2, 109.0109f);
> res = cstmt.executeQuery();
> System.out.println("Test bind parameters: ");
> while(res.next()) {
> System.out.println(res.getFloat("ID1"));
> System.out.println(res.getFloat("ID2"));
> }
> System.out.println();
> {code}
> The output I get running this against Derby is:
> {code:java}
> Test literals:
> Test bind parameters:
> 108.0108
> 109.0109
> {code}
> ----
> According to the FLOAT doc
> (https://db.apache.org/derby/docs/10.1/ref/rrefsqlj27281.html), the default
> precision should be 53. It seems odd to me that there should be different
> behavior between these two queries and setting the bind parameters returns
> results when the table values don't even match the WHERE clause parameters.
> I can then change to a different database, like DB2 or MySQL, and I get no
> results from either query (which is what I expected really). Thoughts?
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
