My 1 cent
I have had at least two situations (maybe more but I only noticed two) where the amount when used with formatmoney() function is reduced by 1 cent. To clarify this is not a rounding issue, these are hard numbers that are being reduced by 1 cent.
I discovered it first when I printed a check and the amount on the check was 1 cent less than it should have been. The cents in the "body" (ie written portion) however were correct.
In another case I entered a credit memo, which when printed on the credit memo report and the statement was 1 cent less than the amount entered. I ran a Select on the aropen table using the formatmoney() function and the result was in fact 1 cent less than the actual amount. There was only one record in the table that showed this behavior.
One cent obviously is not a big deal, but as in the case with a check, some banks will not accept a check where the amount and body do not match, so I would really like to fix this.
Any help is appreciated.
Alex
Sounds like http://www.xtuple.org/node/2405
I did read that post before I posted. It wasn't clear to me if that solution addressed my problem. Plus if that is the solution, I'm not sure how to implement it.
Thanks.
Hi--
I actually did a little bit of work with Alex regarding this issue, but didn't have time to dig deep into it I suggested Alex post his question here. We did look at the issue that Ned linked to earlier in the thread. However a quick review of the code change that was made to fix the issue in the link told me that it should not have fixed it for Alex from a business logic perspective... but it did fix it! That being the case I was concerned that the fix cured a symptom and not the actual problem.
I have since taken a few minutes to dig in and I can say with confidence that I understand the issue pretty well now and that there is no deeper problem than what the fix cures and there is no further need to be worried. Having said that, it does represent a particular behavior of PostgreSQL, and arguably a bug in PostgreSQL, that should be kept in mind during the kind of operations that the formatNumeric() function performs and will likely re-appear if that behavior is not respected during similar functions.
NOTE: It gets very techie from here out! Business users have now been warned.
The root of the problem behind the bug described at http://www.xtuple.org/node/2405 is actually related to implicit data type coercion within PostgreSQL. What the patch actually changed were the conditions of the type coercions related to the order of operations in the formula. The original formula from the non-working version of the system was :
_abs::numeric = trunc(_abs::numeric * (10 ^ _scale::integer))
On the face of it there is nothing wrong with this formula, but the issue comes the exponentiation process. The literal 10 in this context assumes a data type of integer, so far so good, now we have a fragment that looks like:
10::integer ^ _scale::integer
and here the problems start. In PostgreSQL there is no exponent operator that takes two integers as arguments. There are only two versions of the exponent operator (^) defined in out of box PostgreSQL, one is (numeric, numeric) and the other is (float8,float8). At this stage, PostgreSQL is implicitly casting the integer values to float8 (which is just an alias for double precision). After this operation we have a complete formula that looks like this, in effect:
_abs::numeric = trunc(_abs::numeric * (10::double precision ^ _scale::double precision)::double precision)
Since double precision values are by definition not always exact _abs::numeric must necessarily be down cast from the precise type of numeric to the imprecise double precision as well and thus in those rare instances where the _abs assumes a value that the double precision type can't handle well, you get extraneous rounding errors. Since TRUNC always rounds towards zero, this is more pronounced than if it used 'round to nearest' rounding; this isn't to say the use of TRUNC is wrong, but simply that it will surface the issue with greater frequency. There's certainly good argument that (integer, integer) arguments to the ^ operator should implicitly cast to (numeric, numeric) since both are exact precision... but PostgreSQL just doesn't do this.
The fix in the patch should work, but I think in this case explicitly casting the variable _scale::integer to _scale::numeric would be safer; this would have caused the 10 to be cast to a numeric as well and avoids the problem. Right now the code still depends on implicit type coercion which is in the hands of third parties, so PostgreSQL could (though unlikely) change the behavior of the code.
All of this can be tested really simply using the PostgreSQL expression evaluation capability with select statements and the _abs & _scale values reported in the original bug report:
select trunc(2.51::numeric * (10^2::integer)); result = 250 --------------------------------- select trunc(2.51::numeric * (10^2::numeric)); result = 251 --------------------------------- select trunc(2.51::numeric * (10::integer^2::integer)); result = 250
Hopes this helps!
Regards,
Steven C. Buttgereit
Muse Systems
www.musesystems.com
