MySQL Bugs: #71676: Mysql returns rounded floats when queried

Bug #71676	Mysql returns rounded floats when queried
Submitted:	12 Feb 2014 4:45	Modified:	17 Feb 2014 17:13
Reporter:	Daniel Heath	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.6.13	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When querying fields of type 'float', mysql silently rounds floating point values to (6?) decimal places.

The data is stored correctly and can be accessed by multiplying the field by a high-precision float.

This non-intuitive behavior causes bugs in downstream applications.

How to repeat:
drop table if exists float_bug;
create table float_bug (field float);
insert into float_bug (field) values (999.9999);
select field from float_bug;
select field * 1.0000000 as field from float_bug;

Thank you for the bug report. Expected behavior explained here:

https://dev.mysql.com/doc/refman/5.6/en/problems-with-float.html

Did you run the sample code? This isn't a float rounding bug - the data is stored correctly and transmitted incorrectly.

I agree with David (the reporter here) that this is unrelated to known FLOAT rounding accuracy problems. I find Miguel's denial/refusal of this report a somewhat robotic reply disregarding what was actually reported.

The problem reported here is (as far as I can understand) that in the client FLOATS/DOUBLES are 'outputted' with 6 digits after the decimal sign.  This makes mo sense if FLOATS are used for storing *astronomically large* (distance in millimeters to the most remote known galaxy in the Universe!) or *microscopically small* (the average distance in kilometers between two electrons in a helium atom!) -values.

Peter
(not a MySQL/Oracle person)

I have to disagree with comments regarding this being a veritable bug.

What we have here is that data type FLOAT is 4 bytes long IEEE, floating point type. Hence, when you are on the edge, you can expect problems. Indeed let us change a test case and insert into the table the value of 9999.9999. What is the output that we get:

----------------
field
10000
field
10000
----------------

And what if we replace 999.9999 with 999.99999 :

----------------
field
10000
field
10000
----------------

We get the same thing.

However, what if we replace FLOAT (4 bytes) with DOUBLE (8 bytes). For the cases of 999.9999, 9999.9999 and 999.99999 we get, respectively:

----------------
field
999.9999
field
999.9999

field
9999.9999
field
9999.9999

field
999.99999
field
999.99999
----------------

This proves that FLOAT was on the very verge of it's capabilities and had to round floats. Namely, rounded floats take less space within the floating point number as you  do not need much space for the decimal part.

Hence, "Not a Bug".

If you think that this is not explained sufficiently in the documentation , please, let us know !!! And, do continue sending us reports on what you perceive to be bugs.