[
https://issues.apache.org/jira/browse/DERBY-7095?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17258340#comment-17258340
]
Will Dazey commented on DERBY-7095:
-----------------------------------
[~rhillegas]
So, to recap, if I use:
{code:java}
// INSERT INTO SIMPLE_TABLE (ID1,ID2,STRING01) VALUES
(108.01080322265625,109.01090240478516,'TEST_STR')
cstmt = con.prepareCall("SELECT ID1, ID2, STRING01 FROM SIMPLE_TABLE WHERE
((ID1 = ?) AND (ID2 = ?))");
cstmt.setFloat(1, 108.0108f);
cstmt.setFloat(2, 109.0109f);
{code}
Then Derby returns a result, possibly because of some rounding (I'm seeing
Postgres behave this way too). However, MySQL & DB2 don't return a result.
The reason I am investigating this is for the JPA project EclipseLink. With
JPA, we translate an Entity field of java type "float" to DB type "FLOAT" for
these database platforms. Then, when executing queries, JPA users can call
`javax.persistence.Query.setParameter(String arg0, Object arg1)`. For instance:
{code:java}
javax.persistence.Query query = createQuery("SELECT e FROM Entity01 e WHERE
e.id1 = :id1 AND e.id2 = :id2");
query.setParameter("id1", 0108.0108F);
query.setParameter("id2", 0109.0109F);
{code}
Since the Entity classes Java field type is "float", we just call
`java.sql.PreparedStatement.setFloat()` behind the scene, passing the parameter
value the user passed in. During testing, I noticed different behavior
depending on the database platform and also depending on literal vs parameter
for Derby and Postgres. It seems like Derby and Postgres are, perhaps
unintentionally, widening the literal float to a double for the query.
> 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)
