Public

I learned something today about MySQL and how it stores numbers. Perhaps others will be interested.

This query does what (I believe) we all expect. It's meant to round the numbers to one decimal place and it succeeds "correctly".

[NOTE: I can't figure out how to show this with a monospaced font.]

select round(0.24,1),round(0.25,1),round(0.26,1);

+~~-----------------~~+~~------------------~~+~~------------------~~+

| round(0.24,1) | round(0.25,1) | round(0.26,1) |

+~~-------------~~+~~-------------~~+~~-------------~~+

| 0.2 | 0.3 | 0.3 |

+~~-------------~~+~~-------------~~+~~-------------~~+

1 row in set (0.00 sec)

However, if we store the same values in a table the query gives "incorrect" results.

create table t1 (`value` float);

insert into t1 values (0.24), (0.25),(0.26);

select value, round(value,1) from t1;

+~~-----~~+~~--------------~~+

| value | round(value,1) |

+~~-----~~+~~--------------~~+

| 0.24 | 0.2 |

| 0.25 | 0.2 |

| 0.26 | 0.3 |

+~~-----~~+~~--------------~~+

3 rows in set (0.04 sec)

The problem is that the numbers were stored in the inexact floating point representation and that we should have instead used an exact representation (such as

create table t2 (`value` decimal(5,2));

insert into t2 values (0.24), (0.25),(0.26);

select value, round(value,1) from t2;

+~~-----~~+~~--------------~~+

| value | round(value,1) |

+~~-----~~+~~--------------~~+

| 0.24 | 0.2 |

| 0.25 | 0.3 |

| 0.26 | 0.3 |

+~~-----~~+~~--------------~~+

3 rows in set (0.00 sec)

It sure seems wrong (see note below) to me that 0.25 stored as a float doesn't round correctly but I'm not going to try to argue with learned computer science and math types who, it seems, say this is expected. Presumably the 0.25, when stored as a float, is really 0.249999999999999999 or similar.

Presumably very many people who work with these kind of systems are well aware of this. However, I bet that many are not. Now you know.

Some keywords: MySQL, round, float, exact, inexact, decimal, representation, rounding, error, confusion, sad face.

This query does what (I believe) we all expect. It's meant to round the numbers to one decimal place and it succeeds "correctly".

[NOTE: I can't figure out how to show this with a monospaced font.]

select round(0.24,1),round(0.25,1),round(0.26,1);

+

| round(0.24,1) | round(0.25,1) | round(0.26,1) |

+

| 0.2 | 0.3 | 0.3 |

+

1 row in set (0.00 sec)

However, if we store the same values in a table the query gives "incorrect" results.

create table t1 (`value` float);

insert into t1 values (0.24), (0.25),(0.26);

select value, round(value,1) from t1;

+

| value | round(value,1) |

+

| 0.24 | 0.2 |

| 0.25 | 0.2 |

| 0.26 | 0.3 |

+

3 rows in set (0.04 sec)

The problem is that the numbers were stored in the inexact floating point representation and that we should have instead used an exact representation (such as

*decimal*) to store them if we care that possible future uses of defined mathematical operations are to be correctly evaluated.create table t2 (`value` decimal(5,2));

insert into t2 values (0.24), (0.25),(0.26);

select value, round(value,1) from t2;

+

| value | round(value,1) |

+

| 0.24 | 0.2 |

| 0.25 | 0.3 |

| 0.26 | 0.3 |

+

3 rows in set (0.00 sec)

It sure seems wrong (see note below) to me that 0.25 stored as a float doesn't round correctly but I'm not going to try to argue with learned computer science and math types who, it seems, say this is expected. Presumably the 0.25, when stored as a float, is really 0.249999999999999999 or similar.

Presumably very many people who work with these kind of systems are well aware of this. However, I bet that many are not. Now you know.

**NOTE**: Apparently this kind of inexact mathematics isn't "wrong". It looks wrong and one would think that one would design a rounding function to do better but it turns out that that's where one would actually be wrong.Some keywords: MySQL, round, float, exact, inexact, decimal, representation, rounding, error, confusion, sad face.

Shared publicly

Add a comment...