What are the rules mysql uses for displaying float values?

Question

I'm storing values in a column defined as float. When I read back these values from the database they sometimes differ much more from the original values than I would expect (I know that float doesn't store exact values, please take a look at the example to see what I mean).

Here's my test case:

drop table if exists  float_test;
create table float_test (value1 float(8, 3), value2 float);
insert into float_test values (11743.85, 11743.85);
select mt.*, round(mt.value1, 6), round(mt.value2, 6) from float_test mt;

The results of the select are:

mysql> select mt.*, round(mt.value1, 6), round(mt.value2, 6) from float_test mt;
+-----------+---------+---------------------+---------------------+
| value1    | value2  | round(mt.value1, 6) | round(mt.value2, 6) |
+-----------+---------+---------------------+---------------------+
| 11743.850 | 11743.8 |        11743.849609 |        11743.849609 |
+-----------+---------+---------------------+---------------------+
1 row in set (0.01 sec)

As you can see, selecting value2 results in 11743.8 whereas selecting round(value2, 6) results in a value that is much closer to the one I originally put in. Also if you

mysql> select * from float_test mt where value1 = value2;
+-----------+---------+
| value1    | value2  |
+-----------+---------+
| 11743.850 | 11743.8 |
+-----------+---------+
1 row in set (0.00 sec)

you can see that the values stored in value1 and value2 are actually equal. Thus I think it's just a matter of how the results are displayed. Looking through the myql documentation I could not find any rules that say how float values are being rounded automatically for display.
So now I have tow questions:
1. What are the rules mysql uses for displaying float values?
2. Is there a way (e.g. some configuration options) I can retrieve the value that's not rounded to the first decimal place without modifying my table definition and without changing my select statement?

I've tested this on mysql 4.0, 5.0 and 5.1.

Thank you,
Stefan

Answer 1

If you want to maintain a precision of 2 decimal points, you should probably explicitly create the field as float(M, D) where D=2 (precision) and M=the desired number of digits in total.

Defining the field as float(8,2) for example, would likely yield what you wanted.

It's important to keep in mind that float and double are used to store approximate values in MySQL... You may be better off using the DECIMAL data type... for example, DECIMAL(8,3).

Take care.

Answer 2

This doesn't completely answer my question but solved the problem for me as I'm using jdbc to connect to the database:
You get the desired results when using PreparedStatements instead of ordinary Statements.
Code:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCTest {
    private static final String URL = "jdbc:mysql://sp101ap0006:3306/lvs_server_38080";
    private static final String USER = "user";
    private static final String PASSWD = "abc";

    private static void executeQuery(Connection connection) throws SQLException {
        Statement statement = connection.createStatement();         
        ResultSet queryResult = statement.executeQuery("select * from float_test");
        queryResult.first();
        System.out.println(queryResult.getFloat(1) + " - " + queryResult.getFloat(2));
    }

    private static void executePreparedQuery(Connection connection) throws SQLException {
        PreparedStatement statement = connection.prepareStatement("select * from float_test"); 
        ResultSet queryResult = statement.executeQuery();
        queryResult.first();
        System.out.println(queryResult.getFloat(1) + " - " + queryResult.getFloat(2));
    }

    public static void main(String[] args) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection connection = DriverManager.getConnection(URL, USER, PASSWD);
            executeQuery(connection);
            executePreparedQuery(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}

gives the following results:

11743.85 - 11743.8
11743.85 - 11743.85