hessodreamy — 2012-06-21T05:37:16-04:00 — #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 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?
r937 — 2012-06-21T05:55:11-04:00 — #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
hessodreamy — 2012-06-21T07:01:16-04:00 — #3
Gah! It would be something as simple as that! Cheers!