MySQL FLOAT(10,2) – Precision Problems with Money

MySQL

I have a table like this:

mysql> describe transactions;
+-----------------------+---------------+------+-----+---------+----------------+
| Field                 | Type          | Null | Key | Default | Extra          |
+-----------------------+---------------+------+-----+---------+----------------+
| id                    | int(11)       | NO   | PRI | NULL    | auto_increment |
| cost                  | float(10,2)   | YES  |     | NULL    |                |
+-----------------------+---------------+------+-----+---------+----------------+

I (naively?) thought that would be a good way to store amounts.
I am 3 days from release, and I discovered this:

mysql> UPDATE transactions SET cost = 162295.99 WHERE id = 14891;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> select * from transactions where id = 14891;
+-------+---------------+
| id    | cost          |

+-------+---------------+
| 14891 |     162295.98 | 
+-------+---------------+

…?!? I entered .99, not .98!
So:

Why is this doing it?
How do I get out of this problem?
Would decimal work? I tried with:

mysql> alter table transactions modify cost decimal(12,2) NULL DEFAULT NULL;
Query OK, 1120 rows affected, 119 warnings (0.14 sec)
Records: 1120 Duplicates: 0 Warnings: 119

mysql>

However I am getting scary warnings. With "show warnings" I get:

mysql> show warnings;
+-------+------+---------------------------------------------+
| Level | Code | Message                                     |
+-------+------+---------------------------------------------+
| Note  | 1265 | Data truncated for column 'cost' at row 10  |
| Note  | 1265 | Data truncated for column 'cost' at row 369 |
| Note  | 1265 | Data truncated for column 'cost' at row 371 |
| Note  | 1265 | Data truncated for column 'cost' at row 378 |
| Note  | 1265 | Data truncated for column 'cost' at row 402 |
| Note  | 1265 | Data truncated for column 'cost' at row 403 |
+-------+------+---------------------------------------------

How do I see which one "row 10" is anyway?
KEY QUESTION: How do I get out of this hole safely?
(Rhetorical question bonus question) Why didn't this happen like 3 months ago, when fixing would have been easy?

Best Answer

Known problem with IEEE floats. Use DECIMAL

To fix

add new column of correct data type
copy data over (check for conversion errors here)
drop bad column
rename new column to correct name

Why? Murphy's Law.

Related Solutions

MySQL Stored Procedures – Data Truncated for Column

What is the the value of float_one at the time of conversion ???

Note this example from MySQL 5.5.12 in Windows

mysql> select convert(20000,decimal(7,2));
+-----------------------------+
| convert(20000,decimal(7,2)) |
+-----------------------------+
|                    20000.00 |
+-----------------------------+
1 row in set (0.00 sec)

mysql> select convert(200000,decimal(7,2));
+------------------------------+
| convert(200000,decimal(7,2)) |
+------------------------------+
|                     99999.99 |
+------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------------------------+
| Level   | Code | Message                                                               |
+---------+------+-----------------------------------------------------------------------+
| Warning | 1264 | Out of range value for column 'convert(200000,decimal(7,2))' at row 1 |
+---------+------+-----------------------------------------------------------------------+

1 row in set (0.00 sec)

It may be possible that data was truncated if a number bigger than 99999.99 was in float_one. Perhaps, mysql was converting float_one and float_two to DECIMAL(7,2) individually before performing division. Try using DECIMAL(10,2) or greater to accommodate large values.

UPDATE 2011-07-25 15:05 EDT

There are definite truncation problem going on here

According to MySQL Reference Manual Page 442 Paragraphs 2,3

DECIMAL and NUMERIC values are stored as strings, rather than as binary floating point numbers, in order to preserve the decimal precision of those numbers. One character is used for each digit of the value, the deciaml point (if scale > 0) and the - sign (for negative numbers). If the scale is 0, DECIMAL and NUMERIC values contain no decimal point or fractional part.

The maximum range of DECIMAL and NUMERIC is the same as for DOUBLE, but the actual range for the given DECIMAL or NUMERIC column can be constrainted by the precision and scale for a give column, When such a column is assigned a value with more digits following the decimal point than are allowed by the specified scale, the value is rounded to that scale. When a DECIMAL or NUMERIC column is assigned a value whose magnitude exceeds the range implied by the specified (or defaulted) precision and scale, MySQL stores the value representing the corresponding endpoint of that range.

You need to accommodate a larger precision and/or scale.

Here is an example as to why

I wrote this stored procedure using your specifications for DECMIAL and NUMERIC.

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`NumTest` $$
CREATE PROCEDURE `test`.`NumTest` (num1 NUMERIC(7,2), num2 NUMERIC(7,2))
BEGIN

  DECLARE float_one,float_two,my_result NUMERIC(7,2);
  DECLARE f1,f2 DOUBLE(7,2);

  SET f1 = num1;
  SET f2 = num2;

  SET float_one = num1;
  SET float_two = num2;

  SELECT f1 / f2;
  SELECT float_one / float_two;
  SELECT CONVERT(float_one / float_two,DECIMAL(7,2));
  SET my_result = CONVERT(float_one / float_two,DECIMAL(7,2));
  SELECT my_result;

END $$

DELIMITER ;

I used two values: 290.0 and 14.5 for this test.

Before calling the NumTest stored procedure, I manually calculated 290.0 / 14.5

mysql> select 290.0 / 14.5;
+--------------+
| 290.0 / 14.5 |
+--------------+
|     20.00000 |
+--------------+
1 row in set (0.00 sec)

I divided each number by 100, 10000, and 1000000 and tried again

mysql> select 2.9 / .145;
+------------+
| 2.9 / .145 |
+------------+
|   20.00000 |
+------------+
1 row in set (0.00 sec)

mysql> select .029 / .00145;
+---------------+
| .029 / .00145 |
+---------------+
|    20.0000000 |
+---------------+
1 row in set (0.00 sec)

mysql> select .00029 / .0000145;
+-------------------+
| .00029 / .0000145 |
+-------------------+
|      20.000000000 |
+-------------------+
1 row in set (0.00 sec)

So far, so good !!! Now for the stored procedure.

mysql> call numtest(290.0,14.5);
+-----------+
| f1 / f2   |
+-----------+
| 20.000000 |
+-----------+
1 row in set (0.00 sec)

+-----------------------+
| float_one / float_two |
+-----------------------+
|             20.000000 |
+-----------------------+
1 row in set (0.00 sec)

+---------------------------------------------+
| CONVERT(float_one / float_two,DECIMAL(7,2)) |
+---------------------------------------------+
|                                       20.00 |
+---------------------------------------------+
1 row in set (0.00 sec)

+-----------+
| my_result |
+-----------+
|     20.00 |
+-----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

So my orignal two numbers work fine. Let's divide them by 100 and try again.

mysql> call numtest(2.9,0.145);
+-----------+
| f1 / f2   |
+-----------+
| 19.333333 |
+-----------+
1 row in set (0.00 sec)

+-----------------------+
| float_one / float_two |
+-----------------------+
|             19.333333 |
+-----------------------+
1 row in set (0.00 sec)

+---------------------------------------------+
| CONVERT(float_one / float_two,DECIMAL(7,2)) |
+---------------------------------------------+
|                                       19.33 |
+---------------------------------------------+
1 row in set (0.00 sec)

+-----------+
| my_result |
+-----------+
|     19.33 |
+-----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-------------------------------------------+
| Level | Code | Message                                   |
+-------+------+-------------------------------------------+
| Note  | 1265 | Data truncated for column 'num2' at row 2 |
+-------+------+-------------------------------------------+
1 row in set (0.00 sec)

WAIT, we lost some precision. What happened ??? How did this happen ??? You need to accommodate more decmial digits (> 2)

UPDATE 2011-07-25 17:37 EDT

I substituted (7,2) with (10,7) in the stored procedure and got back the proper precision

mysql> call numtest(2.9,0.145);
+----------------+
| f1 / f2        |
+----------------+
| 20.00000000000 |
+----------------+
1 row in set (0.00 sec)

+-----------------------+
| float_one / float_two |
+-----------------------+
|        20.00000000000 |
+-----------------------+
1 row in set (0.01 sec)

+----------------------------------------------+
| CONVERT(float_one / float_two,DECIMAL(10,7)) |
+----------------------------------------------+
|                                   20.0000000 |
+----------------------------------------------+
1 row in set (0.03 sec)

+------------+
| my_result  |
+------------+
| 20.0000000 |
+------------+
1 row in set (0.05 sec)

Query OK, 0 rows affected (0.06 sec)

mysql>

MySQL conversion from float (10,2) to fixed(10,2)

BAD NEWS : I do not think the value will be preserved !!!

I wrote this post (July 25, 2011) showing how sensitive floating point numbers are to conversions.

GOOD NEWS : Here is how you can safely compare the data conversion:

If your table has these characteristics

table is called moneytable
field you wish to convert is called moneyfield
primary key is called moneyid

Then run these commands:

DROP TABLE IF EXISTS moneytabletest;
CREATE TABLE moneytabletest LIKE moneytable;
ALTER TABLE moneytabletest MODIFY moneyfield fixed(10,2);
INSERT INTO moneytabletest SELECT * FROM moneytable;
SELECT A.moneyid,A.moneyfield olddata,B.moneyfield newdata
FROM moneytable A LEFT JOIN moneytabletest B USING (moneyid)
WHERE A.moneyfield <> B.moneyfield;

Here is the bottom line: If any rows come back from the LEFT JOIN query, the conversion will be bad. You will have to modify the definition of moneyfield, reload the moneytabletest table, and run the LEFT JOIN query again and again until no rows come back from the LEFT JOIN query. Once zero(0) rows come back, you will know what conversion will be safe.

Give it a Try !!!