[
https://issues.apache.org/jira/browse/DERBY-7095?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17257842#comment-17257842
]
Richard N. Hillegas edited comment on DERBY-7095 at 1/3/21, 7:28 PM:
---------------------------------------------------------------------
Your datatype is a 64-bit floating point number. What happens if you stuff your
prepared statement with 64-bit literals:
{noformat}
cstmt.setDouble(1, 108.0108);
cstmt.setDouble(2, 109.0109);
{noformat}
was (Author: rhillegas):
Your datatype is a 64-bit floating point number. What happens if you stuff your
prepared statement with 64-bit literals:
{noformat}
cstmt.setDecimal(1, 108.0108);
cstmt.setDecimal(2, 109.0109);
{noformat}
> 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)
