Re: [WebERP-developers] Float accuracy problem

[Rafael C. <raf...@gm...>]

Hi,

* About handling precision:

http://dev.mysql.com/doc/refman/5.7/en/precision-math-examples.html shows
how mySQL works, but I think we need to know how PHP does. I agree with
Ricard, we have to follow some "rules" in writing calculus (e.g. write
formulas with first additions over subtractions, first multiplications over
divisions).


* About code of currencies:

In banktrans.functionalexrate I store exchange rate with 12 o more
decimals. Sometimes, I have to "correct" data stored in the database
(several tables). I explain:
A local_value of ¢1.234.567,44 CRC (9 significant figures) with a
"currency_price" of  545,40 CRC/USD is in dollars_value $2.263,60 USD.To
convert $2.263,60 USD to CRC,
* if we use a exchange rate of 0,00183 USD/CRC (3 significant figures) we
get 1.236.939,89 (with an error of 2.372,45).
* But if we use a exchange rate of 0,001833516685 USD/CRC (10 "significant
figures") we get 1.234.567,44 (with an error of 0,00).


* About Floating-Point Types:

"Try with the floating (9e99) with +1 the quantity of digits of the bigger
number to be shown". Sorry. I want to say:
In engineering software (calculus done out of mySQL database), with numbers
expressed in scientific notation 8.88E+99, we count the significant figures
of the biggest number in precision to be shown (the "8.88" part of the
number), and we use this quantity of significant figures increasing it by
one. E.g.:
8.88E+99 has 3 significant figures --> we use 4.
7.77777E+99 has 6 significant figures --> we use 7.
If R= A * B, and A=8.88E+99 and B=7.77777E+99, the quantity used to store
data is MAX(4, 9) = 9 significant figures.
This is to "cover" the +/- 0.5 error in the last significant figure.


* Fixed-Point Types (exact values) vs. Floating-Point Types (approximate
values):

Yes, I agree. In database, store data in (1) in fixed-point types --exact
values--, (2) but if needed floating-point types --approximate values--, to
use double-precision.
Also, I suggest to do as (as much is possible, the calculus inside PHP and
outside myQSL).


* Other comments:

To have in mind that subtraction and division are "precision destructors".
That is:
+ Leave them as the last operation in a formula (instead of A / B * C is
better A * C / B);
+ Use as little as possible these operations or numbers that are
symmetrical by this operations (value = 1/x --the worst; value = -x). e.g.
instead of A/B +C/B is better (A+C)/B.

Best regards, Rafael.


2015-01-08 20:46 GMT-06:00 ExsonQu <hex...@gm...>:

> *Hi, Richard,*
>
>             You've rise a good point. We need to keep that in mind.
>
>             We may need to focus on precision before data storing. We
> cannot
> leave that to mysql itself. A policy maybe necessary.
>
>             Lets just find more evidence and data to find a better
> solution.
>
>             Best regards!
>
>             Exson
>
>
>
> --
> View this message in context:
> http://weberp-accounting.1478800.n4.nabble.com/Float-accuracy-problem-tp4657899p4657962.html
> Sent from the web-ERP-developers mailing list archive at Nabble.com.
>
>
> ------------------------------------------------------------------------------
> Dive into the World of Parallel Programming! The Go Parallel Website,
> sponsored by Intel and developed in partnership with Slashdot Media, is
> your
> hub for all things parallel software development, from weekly thought
> leadership blogs to news, videos, case studies, tutorials and more. Take a
> look and join the conversation now. http://goparallel.sourceforge.net
> _______________________________________________
> Web-erp-developers mailing list
> Web...@li...
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>