MySQL Bugs: #55677: Floats rounded when read using JDBC

Bug #55677	Floats rounded when read using JDBC
Submitted:	2 Aug 2010 9:35	Modified:	3 Aug 2010 7:37
Reporter:	Dimitar Asenov	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.1.13	OS:	Linux (CentOS)
Assigned to:		CPU Architecture:	Any
Tags:	FLOAT, jdbc, precision, Rounding

Description:
I am working on a Java application that reads FLOAT numbers from a database using the JDBC connector. The problem is that the values are always rounded to 6 digits.

E.G.
If I put 3.1415927 in the database I get 3.14159 when reading. If I specify a rounding of 20 digits in the select statement I get back the correct result of 3.1415927.

I understand that this rounding is standard behavior for MySQL's client interface, but when using a Java application I would expect that the JDBC works with the full precision available for the real numbers in the table. The workaround to use the explicit round(field,precision) specification does not help in cases where the magnitude of the stored values varies a lot.

How to repeat:
Use this function to insert a float.

void putVal(Connection con, float val) throws SQLException
{
 String sql_query = "INSERT INTO values (float_val) VALUES (?)";
 PreparedStatement stmt = con.prepareStatement(sql_query);
		
 stmt.setFloat(1, val);
 stmt.executeUpdate();
}

Use this to retrieve all floats rounded:
ResultSet rSet = con.prepareStatement("SELECT float_val FROM values" ).executeQuery();

Use this to retrieve all floats with possibly better precision:
ResultSet rSet = con.prepareStatement("SELECT round(float_val,20) FROM values" ).executeQuery();

Suggested fix:
Make JDBC always retrieve the full precision floating point values as stored in the database.

Hi Dimitar and thanks for your report.

The problem here might be on the wire rather than in code so I'll have t check. What I need from you is following:
  o Upgrade to c/J 5.1.31 and test, 5.1.6 is way too old to have bugs reported against it.
  o Provide *complete* test case along with connection string. The one pasted is just not enough.

The statements to recreate the test Database and Table

Attachment: CreateDB.txt (text/plain), 222 bytes.

A test application that demonstrates the float rounding problem.

Attachment: FloatTest.java (application/octet-stream, text), 3.18 KiB.

Hi Tonci,

As you suggested I made a test with the newest version of JDBC 5.1.13 and the problem is still there.

I have attached the relevant source code to test the behavior. Let me know if I can help further.

Dimitar, although I get the same results as you do, I do not see it as a bug and here's why.

Java rules are: The floating-point types are float and double, which are conceptually associated with the single-precision 32-bit and double-precision 64-bit format IEEE 754 values and operations as specified in IEEE Standard for Binary Floating-Point Arithmetic, ANSI/IEEE Standard 754-1985 (IEEE, New York).
So, floating point numbers in Java are stored in a binary representation.

http://dev.mysql.com/doc/refman/5.5/en/numeric-types.html:
For FLOAT, the SQL standard permits an optional specification of the precision (but not the range of the exponent) in bits following the keyword FLOAT in parentheses. MySQL also supports this optional precision specification, but the precision value is used only to determine storage size. A precision from 0 to 23 results in a four-byte single-precision FLOAT column. A precision from 24 to 53 results in an eight-byte double-precision DOUBLE column. 

So, to retrieve completely to Java the value stored as binary floating point number in MySQL you *must* know the maximum number of decimal places in the original result...

DECIMAL/Math.BigDecimal would be my preferred combination.

Ah yes, as floating point numbers are stored in a binary representation, there exist numbers that can be written with finite-length in decimal, but repeat infinitely in binary. Thus the cut.

Hi Tonci,

This is neither a problem of binary representation nor it is a problem of the precision of float or double. Let me try to explain:

The fact is that when I insert a value like 3.1415927 into the DB, this number is stored precisely. I know this because if I retrieve it using 'SELECT round(float_val,20) FROM numbers' I get exactly 3.1415927 back. However if I only use 'SELECT float_val FROM numbers' I loose precision as the result is 3.14159 . It doesn't matter how the number is stored in the DB, all that is important is that the stored number has higher precision than the one returned by the simple SELECT statement.

When I write an application that uses JDBC to store floats in the DB I expect that whenever I retrieve those floats they will have the maximum possible precision provided by the DB. However this is not always the case. I consider this a bug.

Dimitar, I never dismiss a report unless absolutely sure. Thus, before making my ruling, I consulted others and all agreed on my assessment.

So, issuing 'SELECT round(float_val,20) FROM numbers' should work as you're telling the protocol how much of the stored value to read back and 'SELECT float_val FROM numbers' should truncate as you did not specify how much of the stored value to read back. If you need to use floats, although I can not think why, that's what you'll get. Otherwise, use DOUBLE or DECIMAL.

This is still not a bug by any means.

I have asked the documentation team to clarify this case with example in manual.

Changing the category, as this behavior is not limited to JDBC.

Considering that FLOAT is defined[1] as an approximate numeric data, without specifying what "approximate" means, we can get back anything, for example 98 instead of 100, and it can be said that it is not a bug.

However, I think the documentation is misleading in this case. It implicitly suggests, at least for me, that float is usually stored as a 4 bytes IEEE float. So one can expect that if he stores an IEEE float, for example a Java float variable in a FLOAT() column, that he gets back the same float value from MySQL. But this is not the case! It seems that MySQL rounds the value to 6 digits, if no argument is specified in the FLOAT column definition.

Again, I do not talk about the usual rounding issues when converting a number from decimal format to binary format. The issue here is that even the data written in binary and read in binary differs. Maybe there is a very good reason for this, but it would be better if it were documented in a bit more detail.

[1] http://dev.mysql.com/doc/refman/5.6/en/floating-point-types.html