Will Dazey created DERBY-7095:
---------------------------------
Summary: 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
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 to test:
{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)
