MySQL Bugs: #18401: Float: prepared statements and statements may return diff. results

Bug #18401	Float: prepared statements and statements may return diff. results
Submitted:	21 Mar 2006 17:17	Modified:	1 Nov 2009 20:53
Reporter:	Stephane Giron	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:		OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
Hi,

When retrieving a float from MySQL 5.0.18 with Connector/J 3.1.12, the value may be different when using a prepared statement or a statement.

The provided test case will fail on :
    assertEquals(rs.getFloat(2), rs.getFloat(1), 0F);

junit.framework.AssertionFailedError: expected:<3.1415927> but was:<3.14159>

How to repeat:
This is my test case (junit) :

    Statement stmt = conn.createStatement();

    stmt.execute("SET sql_mode=''");

    stmt.executeUpdate("DROP TABLE IF EXISTS test_bind_fetch");
    stmt
        .executeUpdate("CREATE TABLE test_bind_fetch(c1 float(24), "
            + "c2 float, c3 float unsigned, c4 float,c5 float,c6 float, c7 float(10) unsigned)");

    stmt.close();

    stmt = conn.createStatement();

    stmt
        .executeUpdate("INSERT INTO test_bind_fetch (c1,c2,c3,c4,c5,c6,c7) "
            + "VALUES (3.1415926535,-0.000001, 0, 999999999999, sin(0.6), 1.00000000000001, 888888888888888)");
    stmt.close();

    PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM test_bind_fetch");
    stmt = conn.createStatement();
    
    ResultSet rs2 = pstmt.executeQuery();
    ResultSet rs = stmt.executeQuery("SELECT * FROM test_bind_fetch");

    assertTrue(rs.next() && rs2.next());
    
    assertEquals(3.1415926535F, rs.getFloat(1), 0F);
    assertEquals(rs.getFloat(2), rs.getFloat(1), 0F);

    rs.close();
    pstmt.close();

Sorry of that, my test case was wrong but bug is still there. Lines that are updated are the 2 assert lines.

The correct test case is :
    Statement stmt = conn.createStatement();

    stmt.execute("SET sql_mode=''");

    stmt.executeUpdate("DROP TABLE IF EXISTS test_bind_fetch");
    stmt
        .executeUpdate("CREATE TABLE test_bind_fetch(c1 float(24), "
            + "c2 float, c3 float unsigned, c4 float,c5 float,c6 float, c7 float(10) unsigned)");

    stmt.close();

    stmt = conn.createStatement();

    stmt
        .executeUpdate("INSERT INTO test_bind_fetch (c1,c2,c3,c4,c5,c6,c7) "
            + "VALUES (3.1415926535,-0.000001, 0, 999999999999, sin(0.6), 1.00000000000001, 888888888888888)");
    stmt.close();

    PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM test_bind_fetch");
    stmt = conn.createStatement();
    
    ResultSet rs2 = pstmt.executeQuery();
    ResultSet rs = stmt.executeQuery("SELECT * FROM test_bind_fetch");

    assertTrue(rs.next() && rs2.next());
    
    assertEquals(3.1415926535F, rs2.getFloat(1), 0F);
    assertEquals(rs.getFloat(1), rs2.getFloat(1), 0F);

    rs.close();
    pstmt.close();

The failing line is : assertEquals(rs.getFloat(1), rs2.getFloat(1), 0F);
with the following error :
junit.framework.AssertionFailedError: expected:<3.14159> but was:<3.1415927>

You can't compare float values directly, as they can often have round-off error (this is a common problem when working with the "float" datatype regardless of platform).

Floats are created in different ways in the JDBC driver depending on whether a plain statement (parsed from a string) or prepared statement (represented as IEEE bits). Therefore, there can be situations where they are slightly different.

If you want _precise_ numbers, you should be using the SQL type "DECIMAL" which maps to java.math.BigDecimal.

Let's forget for a minute the sample JDBC application, and focus on the title of the bug.
No rounding should ever happen here:

 assertEquals(Statement.getFloat(1), PreparedStatement.getFloat(1))

The fact that dealing with floats "often" triggers some rounding is not an excuse to
perform rounding when there is no need to.

By the way it is perfectly possible to convert float->base10string->float without precision loss. You just need to print enough digits. That is: 9  digits for IEEE754 floats and 17 digits for doubles. See 
"What every computer scientist should know about floating-point arithmetic", David Goldberg
http://www2.hursley.ibm.com/decimal/#links

Hi again Stephan.
Verified as described by reporter:
  MySQL server 5.0.23-debug bk build
  mysql-connector-java-3-1-13
  JDK-1.6
  junit-3.8.1

//OK
Assert.assertEquals(3.1415926535F, rs2.getFloat(1), 0F);
                           
//FAILS
Assert.assertEquals(rs.getFloat(1), rs2.getFloat(1), 0F);

Test case

Attachment: MyTest.java (text/x-java), 4.24 KiB.

I do not see any problem at server's level with prepared statements:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.86-community-nt-log MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE test_bind_fetch(c1 float(24),
    -> c2 float,
    -> c3 float unsigned,
    -> c4 float,
    -> c5 float,
    -> c6 float,
    -> c7 float(10) unsigned);
Query OK, 0 rows affected (0.69 sec)

mysql>
mysql> INSERT INTO test_bind_fetch (c1,c2,c3,c4,c5,c6,c7)
    -> VALUES (3.1415926535,-0.000001, 0, 999999999999, sin(0.6),
    -> 1.00000000000001, 888888888888888);
Query OK, 1 row affected (0.16 sec)

mysql>
mysql> SELECT * FROM test_bind_fetch;
+---------+---------+------+--------+----------+------+--------------+
| c1      | c2      | c3   | c4     | c5       | c6   | c7           |
+---------+---------+------+--------+----------+------+--------------+
| 3.14159 | -1e-006 |    0 | 1e+012 | 0.564642 |    1 | 8.88889e+014 |
+---------+---------+------+--------+----------+------+--------------+
1 row in set (0.00 sec)

mysql> prepare stmt1 from "select * from test_bind_fetch";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> execute stmt1;
+---------+---------+------+--------+----------+------+--------------+
| c1      | c2      | c3   | c4     | c5       | c6   | c7           |
+---------+---------+------+--------+----------+------+--------------+
| 3.14159 | -1e-006 |    0 | 1e+012 | 0.564642 |    1 | 8.88889e+014 |
+---------+---------+------+--------+----------+------+--------------+
1 row in set (0.00 sec)

So I do not see a server bug here.