[DERBY-7095] Different query results with parameter binding vs literals

• Type: Bug
• Status: Resolved
• Priority: Minor
• Resolution: Not A Bug
• Affects Version/s: 10.12.1.1
• Component/s: JDBC
• Bug behavior facts: Wrong query result
• Assignee: Unassigned
• Reporter: Will Dazey
• Created: 02/Jan/21 21:37
• Updated: 06/Jan/21 18:27
• Resolved: 06/Jan/21 16:08

Description

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:

    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();

The output I get running this against Derby is:

Test literals: 
Test bind parameters: 
108.0108
109.0109

---

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?