Query Help (Am I Blinded?)

[Ramiro S]

Hi. I have this query:

  select arrival,id,(sales+ taxes) as sale,paid
  from reservations
  
  where filestat > 10 
  
  and ((sales+taxes) - paid) >0 order by 1 LIMIT 0, 100

Basically (sales+tax)-paid gives me the balance of the account. The problem is MySQL is giving me rows like this in the result (example):

sales = 100,51 , tax=15,11 (115,62 total price)... paid 115,62. So that row should not be there, in the result. Column types are DECIMAL 10,2.

Whats going on!?!?!?!? I'm using MySQL 4.0.20-standard

Thank you,

[r937]

i think this is due to rounding errors

mysql performs decimal arithmetic using float values

DECIMAL and NUMERIC values are stored as strings, rather than as binary floating-point numbers, in order to preserve the decimal precision of those values... The maximum range of DECIMAL and NUMERIC values is the same as for DOUBLE, but the actual range for a given DECIMAL or NUMERIC column can be constrained by the precision or scale for a given 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.

some people deal with this problem by storing monetary values as integers (representing cents)