[
https://issues.apache.org/jira/browse/DERBY-7095?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17258394#comment-17258394
]
Will Dazey commented on DERBY-7095:
-----------------------------------
Running this test:
{code:java}
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}
I checked my Derby.log and I see this log line:
{code:java}
... Executing prepared statement: SELECT ID1, ID2, STRING01 FROM SIMPLE_TABLE
WHERE ((ID1 = ?) AND (ID2 = ?)) :End prepared statement with 2 parameters begin
parameter #1: 108.01080322265625 :end parameter begin parameter #2:
109.01090240478516 :end parameter
{code}
> 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)
