Representation of decimal numbers with mysql float type?

Question

I know that it is a bad idea to store decimal values as float in mysql.

Example: If I store a product the price of $ 5.01, there is no exact floating type representation of that number.

If I you do that:

CREATE TABLE IF NOT EXISTS `test` (
  `test` float NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `test` (`test`) VALUES
(5.01);

and then you run the query

SELECT AVG( test ) 
FROM test

against that table you will end up with this:

mysql> select avg(test) from test;
+------------------+
| avg(test)        |
+------------------+
| 5.01000022888184 |
+------------------+
1 row in set (0.00 sec)

however if you select just the field like this:

mysql> select * from test;
+------+
| test |
+------+
| 5.01 |
+------+
1 row in set (0.00 sec)

you end up with the correct amount.

My question is: How can that be?

If float type cannot represent my value accurately?

Shouldnt the select return 5.01000022888184 as well?

score 1 · Answer 1 · edited May 23 '17 at 09:58

1

I think it is as simple as the db stores the number as is however once you manipulate it, it is then used in the IEEE 754 form

see a previous (oracle related) question Oracle Floats vs Number

edited May 23 '17 at 09:58

Community

1
1

answered Jul 28 '11 at 11:57

Kevin Burton

11,676
2
24
37

score 1 · Accepted Answer · answered Jul 28 '11 at 12:16

The reason why the plain select returns 5.01 is that it is the shortest number that is closer to the actual value than to any other floating-point number; MySQL displays this instead of converting the actual value back to the closest decimal fraction because in most cases users will perceive this result as "more correct".

The reason why avg() behaves differently is probably that it does its calculation using double, and 5.01 is of course not closer to the rounded-to-float actual value than all double values, because there are far more of those.

score 0 · Answer 3 · answered Oct 17 '12 at 21:17

The real problem is that you can't do:

SELECT * FROM `test` WHERE `test` = 5.01

The value of a float stored in MySQL is unknowable

It's a huge bug, but they're not willing to fix it. Decimal and Double have similar problems. The only fix is to specify an exact precision for the field in your schema

ALTER TABLE `test` change `test` `test` FLOAT(10,2)

But then you loose any precision in calculating where the precision would be less specified (eg. any fractions)

Representation of decimal numbers with mysql float type?

3 Answers3