Have a PHP script - using all the correct bcmath functions, and fully understand the issues with floats. I'm also using decimal columns, because, I was under the impression, those are stored as a different type, and the math is better on those.
So I have a query: INSERT INTO mytable (identifier, amount) VALUES ('identifer', 'some_number') ON DUPLICATE KEY UPDATE amount = amount + some_number;
Basically - if we don't have a row, insert it, and if we do, add the some_number.
The some_number column is decimal(24,8)
When run this with some_number being equal to 72100312.72872099 and the current row is zeroed out at 0.00000000, the math winds up with the column being 72100312.72872100.
If some_number is 72100312.72872098 it works fine. If it's 72100312.72872101 its fine. If I do 11111111.72872099 its fine.
If the current row is not zero, but is instead .00000005, then72100312.72872099 adds fine and the total is 72100312.72872104 as expected.
It's like it's screwing up some math. And I thought decimal columns were immune to this.
Is there a way to fix this, or do the update in a better way? I'd REALLY rather not have to do a select, a bcadd, then an update. This type of line (insert on duoplicate update) appears in the code about 120 times. Thats a lot of retro-fitting to select/bcadd/update.
Addendum: Just read MySQL servers before 5.0 converted to floats for math. I'm on 5.5. Its not the < 5.0 issue.
Edit2: Client version is mysqlnd 5.0.11 - not sure if that could be part of the issue? The server should be doing the math though, correct?
