Simple number comparison giving false results

Databases
1

I’m doing a number comparison between values in different tables and MySql is telling me the numbers are not equal when in fact they are.

As an illustration:


SELECT
	(select costPrice from tProducts where productID=164082) AS n1,
	(select costPrice from simons_products where productID=164082) AS n2,
	(select costPrice from tProducts where productID=164082) = (select costPrice from simons_products where productID=164082) AS is_equal

This gives the result


n1	n2	is_equal
13.12	13.12	0

Both of these fields are defined as double(10,2) and have charset latin1, so I’m a bit stumped as to why it’s giving this result.

I can get around it by applying a 2 dp rounding to each value when doing the equality evaluation, but I’m still unsure as to why this is necessary. Any ideas?

2

there’s your problem right there

floating point numbers are approximations only

floating point numbers are intended for extremely small numbers (e.g. distance between atoms measured in kilometres) or extremely large numbers (e.g. distance between stars measured in centimetres)

use DECIMAL(10,2) and your troubles will go away

3

Gah! It would be something as simple as that! Cheers!