From: ExsonQu <hex...@gm...> - 2014-12-10 17:16:33
|
*Dear all,* I've encountered several cases about float accuracy problem myself and report from webERP Chinese community. It causes some number like 0.9999999999999 or 1.000000000000001 stored in webERP locstock quantity field. This will lead to invoice cannot be issued due to negative stock quantity, or some display '-0.00' quantity about stock status, and sales orders not completed problem. There is a article for the float accuracy problem here http://stackoverflow.com/questions/14587290/can-i-rely-on-php-php-ini-precision-workaround-for-floating-point-issue <http://stackoverflow.com/questions/14587290/can-i-rely-on-php-php-ini-precision-workaround-for-floating-point-issue> I believe it's necessary to fix this problem since it'll lead some strange behavior. Thanks and best regards! Exson -- View this message in context: http://weberp-accounting.1478800.n4.nabble.com/Float-accuracy-problem-tp4657899.html Sent from the web-ERP-developers mailing list archive at Nabble.com. |
From: ExsonQu <hex...@gm...> - 2015-01-05 05:03:23
|
*Dear all,* I've received one more case about this. The number in the inventory account does not match with the inventory actual value. There is always some variance even we set up more decimal places. Does anybody meet this same problem? Best regards! Exson -- View this message in context: http://weberp-accounting.1478800.n4.nabble.com/Float-accuracy-problem-tp4657899p4657939.html Sent from the web-ERP-developers mailing list archive at Nabble.com. |
From: Rafael C. <raf...@gm...> - 2015-01-05 06:02:52
|
Hi Exson, In inventory I do not have this problem, but in currency I have it. Exchange rate has few room (decimals) to allocate enough "significant digits" (sorry I do not know the exact translation). I explain myselt: 600 CRC =1 USD, that is a exchange rate of 0.0016 USD/CRC. The result: For a transaction of 111.111,11 (8 "significant digits") we need 3+8= 11 decimals in the exchange rate. The real solution is to use "scientific notation" with enough "significant digits". I did not the best (but fast): I modified to have 14 decimals. Best regards, Rafael. 2015-01-04 23:01 GMT-06:00 ExsonQu <hex...@gm...>: > *Dear all,* > > I've received one more case about this. The number in the inventory > account does not match with the inventory actual value. There is always > some > variance even we set up more decimal places. > > Does anybody meet this same problem? > > Best regards! > > Exson > > > > > -- > View this message in context: > http://weberp-accounting.1478800.n4.nabble.com/Float-accuracy-problem-tp4657899p4657939.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 > |
From: ExsonQu <hex...@gm...> - 2015-01-09 02:22:23
|
*Hi, Rafael,* I've reviewed the code of currencies-- if we changed to more than 4 decimal places, it's not allowed. Do we really need the decimal places control? Best regards! Exson -- View this message in context: http://weberp-accounting.1478800.n4.nabble.com/Float-accuracy-problem-tp4657899p4657959.html Sent from the web-ERP-developers mailing list archive at Nabble.com. |
From: Pak R. <pak...@gm...> - 2015-01-05 08:34:19
|
Hi Exson: YES! We find some tiny differences but after time they become noticeable (still small, but there's a bug somewhere with float). We detected it also in multicurrency calcualtions, so I guess it is due to diffrerent ways to do the same calculations in different parts of webERP, and the float precission tricks us here. I will take more attention and try to track it down. Regards, Ricard 2015-01-05 13:01 GMT+08:00 ExsonQu <hex...@gm...>: > *Dear all,* > > I've received one more case about this. The number in the inventory > account does not match with the inventory actual value. There is always > some > variance even we set up more decimal places. > > Does anybody meet this same problem? > > Best regards! > > Exson > > > > > -- > View this message in context: > http://weberp-accounting.1478800.n4.nabble.com/Float-accuracy-problem-tp4657899p4657939.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 > |
From: ExsonQu <hex...@gm...> - 2015-01-06 15:13:27
|
*Hi, Rafael and Richard,* Thank you for your feedback. And I've also attached Tim's feedback about the cause of this problem. As Tim point out, we may use webERP's current locale_number_format() function to solve this problem. I believe the effort is deserved since webERP is accounting application. If no objection, I'd like to initiate a schedule to solve this problem. Thanks again for the feedback from all of you! Best regards! Exson Tim wrote > The problem is a general one with the way computers deal with > floating > points. Mysql stores floating point values in a 32 bit value, using an > algorithm to convert the floating point number to a 32 bit integer, > and then reversing it to get back to a floating point number. This > means that it is only actually capable of storing 4,294,967,295 > different floating point numbers. However in reality there is an > infinite number of them, even just between 0 and 1 there is an > infinite number. This inevitably means that there are an infinite > number of floating point numbers that when converted to a 32 bit int, > and then back again do not end up with the initial value. To see an > example of this enter the following sql into a mysql terminal: > > SELECT 3.1415E0 + 0.9585E0 - 4.1E0; > > the E0 just forces mysql to think of these numbers as floats. The above > gives > > +-----------------------------+ > | 3.1415E0 + 0.9585E0 - 4.1E0 | > +-----------------------------+ > | 8.881784197001252e-16 | > +-----------------------------+ > > on my Debian system but may give alternatives on your operating systems. > > Commercial accounting systems store all numbers as integers with a > number of decimal places and just convert back to floats when > displaying them. > > Hope this helps, > Tim > > Well imagine a stock item XYZ that has the decimalplaces field set to > 4. The quantity field in the locstock table should be of type integer. > If we have a quantity of 57.561 of XYZ in a particular location then > the quantity field in the locstock table would be 575610. To display > the quantity on the screen you convert it into the correct format so > here it is 575610/pow(10, 4). This could probably be done via the > number_format functions. We already hold the decimal places number for > stock items and monetary amounts. > > This "bug" has long been a problem in openerp as well. I know its been > discussed on that project over and over for many years, but I am not > sure whether they have taken any action yet. It would be a big plus to > change it, but a _lot_ of work. > > Tim Pak Ricard wrote > Hi Exson: > > YES! We find some tiny differences but after time they become noticeable > (still small, but there's a bug somewhere with float). > We detected it also in multicurrency calcualtions, so I guess it is due to > diffrerent ways to do the same calculations in different parts of webERP, > and the float precission tricks us here. > I will take more attention and try to track it down. > > Regards, > Ricard > > 2015-01-05 13:01 GMT+08:00 ExsonQu < > hexinfans@ > >: > >> *Dear all,* >> >> I've received one more case about this. The number in the inventory >> account does not match with the inventory actual value. There is always >> some >> variance even we set up more decimal places. >> >> Does anybody meet this same problem? >> >> Best regards! >> >> Exson >> >> >> >> >> -- >> View this message in context: >> http://weberp-accounting.1478800.n4.nabble.com/Float-accuracy-problem-tp4657899p4657939.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-erp-developers@.sourceforge >> https://lists.sourceforge.net/lists/listinfo/web-erp-developers >> > > ------------------------------------------------------------------------------ > 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-erp-developers@.sourceforge > https://lists.sourceforge.net/lists/listinfo/web-erp-developers -- View this message in context: http://weberp-accounting.1478800.n4.nabble.com/Float-accuracy-problem-tp4657899p4657944.html Sent from the web-ERP-developers mailing list archive at Nabble.com. |
From: Rafael C. <raf...@gm...> - 2015-01-06 16:20:24
|
Hi Exson, Yes, I think an "accounting adjustment for differences in calculus" is a bad image for webERP's precision. A couple of suggestions: 1. In calculations, to have "precedence" in calculus. I mean: first additions over subtractions; first multiplication over divisions. Subtractions and divisions destroy precision (reduce the quantity of "significant digits"). 2. If extra decimals in fixed decimal do not solve the problem, try with the floating (9e99) with +1 the quantity of digits of the bigger number to be shown. ref: http://dev.mysql.com/doc/refman/5.0/en/fixed-point-types.html Best regards, Rafael. 2015-01-06 9:11 GMT-06:00 ExsonQu <hex...@gm...>: > *Hi, Rafael and Richard,* > > Thank you for your feedback. > > And I've also attached Tim's feedback about the cause of this > problem. As Tim point out, we may use webERP's current > locale_number_format() function to solve this problem. > I believe the effort is deserved since webERP is accounting > application. > If no objection, I'd like to initiate a schedule to solve this > problem. > > Thanks again for the feedback from all of you! > > Best regards! > > Exson > > > > Tim wrote > > The problem is a general one with the way computers deal with > > floating > > points. Mysql stores floating point values in a 32 bit value, using an > > algorithm to convert the floating point number to a 32 bit integer, > > and then reversing it to get back to a floating point number. This > > means that it is only actually capable of storing 4,294,967,295 > > different floating point numbers. However in reality there is an > > infinite number of them, even just between 0 and 1 there is an > > infinite number. This inevitably means that there are an infinite > > number of floating point numbers that when converted to a 32 bit int, > > and then back again do not end up with the initial value. To see an > > example of this enter the following sql into a mysql terminal: > > > > SELECT 3.1415E0 + 0.9585E0 - 4.1E0; > > > > the E0 just forces mysql to think of these numbers as floats. The above > > gives > > > > +-----------------------------+ > > | 3.1415E0 + 0.9585E0 - 4.1E0 | > > +-----------------------------+ > > | 8.881784197001252e-16 | > > +-----------------------------+ > > > > on my Debian system but may give alternatives on your operating systems. > > > > Commercial accounting systems store all numbers as integers with a > > number of decimal places and just convert back to floats when > > displaying them. > > > > Hope this helps, > > Tim > > > > Well imagine a stock item XYZ that has the decimalplaces field set to > > 4. The quantity field in the locstock table should be of type integer. > > If we have a quantity of 57.561 of XYZ in a particular location then > > the quantity field in the locstock table would be 575610. To display > > the quantity on the screen you convert it into the correct format so > > here it is 575610/pow(10, 4). This could probably be done via the > > number_format functions. We already hold the decimal places number for > > stock items and monetary amounts. > > > > This "bug" has long been a problem in openerp as well. I know its been > > discussed on that project over and over for many years, but I am not > > sure whether they have taken any action yet. It would be a big plus to > > change it, but a _lot_ of work. > > > > Tim > > > Pak Ricard wrote > > Hi Exson: > > > > YES! We find some tiny differences but after time they become noticeable > > (still small, but there's a bug somewhere with float). > > We detected it also in multicurrency calcualtions, so I guess it is due > to > > diffrerent ways to do the same calculations in different parts of webERP, > > and the float precission tricks us here. > > I will take more attention and try to track it down. > > > > Regards, > > Ricard > > > > 2015-01-05 13:01 GMT+08:00 ExsonQu < > > > hexinfans@ > > > >: > > > >> *Dear all,* > >> > >> I've received one more case about this. The number in the inventory > >> account does not match with the inventory actual value. There is always > >> some > >> variance even we set up more decimal places. > >> > >> Does anybody meet this same problem? > >> > >> Best regards! > >> > >> Exson > >> > >> > >> > >> > >> -- > >> View this message in context: > >> > http://weberp-accounting.1478800.n4.nabble.com/Float-accuracy-problem-tp4657899p4657939.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-erp-developers@.sourceforge > > >> https://lists.sourceforge.net/lists/listinfo/web-erp-developers > >> > > > > > ------------------------------------------------------------------------------ > > 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-erp-developers@.sourceforge > > > https://lists.sourceforge.net/lists/listinfo/web-erp-developers > > > > > > -- > View this message in context: > http://weberp-accounting.1478800.n4.nabble.com/Float-accuracy-problem-tp4657899p4657944.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 > |
From: ExsonQu <hex...@gm...> - 2015-01-09 02:34:11
|
*Hi, Rafael,* Sorry, I cannot understand what do you mean 9e99 + 1. And cannot found more details in your link. Could you give more details? Best regards! Exson -- View this message in context: http://weberp-accounting.1478800.n4.nabble.com/Float-accuracy-problem-tp4657899p4657960.html Sent from the web-ERP-developers mailing list archive at Nabble.com. |
From: rfthomas <rf...@as...> - 2015-01-06 17:35:46
|
This is an old problem that is on all computer systems and actually predates computer systems. Floating point does not solve the problem, rather it will make it worse. Our company works with floating point calculations in engineering and manufacturing. Loss of precision is a major problem. There are numerous references explaining this problem in detail: Numerical Mathematical Analysis - Scarborough Computerized Accounting Methods and Controls - Tyran Error Propagation for DIfference Methods - Henrici Cobol and other accounting oriented languages normally utilize integers in fixed formats to store numbers. Order of operations, rounding and truncation all create specific problems. For example: if the cost of an item is 3 for $1.00, then if one buys one the price is $0.3333... Assuming that this unit price is stored to 2 places, i.e. $0.33 if a customer buys 3 pieces then the cost to the customer is $0.99 and the business must absorb the $0.01 shortage. There are no clean solutions. One must use finite values and live with results. If rounding of the unit prices is performed then a consistent policy needs to be established. Floating point numbers should not be used since by their nature certain values are difficult to store. It is normal to see a value of 1. stored as .9999999 if using Real*4 or .999999999999999 if using Real*8. Storage is cheap and readily expanded. Computers are very fast and the numerical calculations simple. We would prefer to expand fields and use character or integer representations for all numbers. -- View this message in context: http://weberp-accounting.1478800.n4.nabble.com/Float-accuracy-problem-tp4657899p4657949.html Sent from the web-ERP-developers mailing list archive at Nabble.com. |
From: ExsonQu <hex...@gm...> - 2015-01-07 02:39:17
|
*Hi, all,* I post Tim's reply as following: Best regards! Exson Tim wrote > Hi Robert, as I said to Exson in the off list conversations > integer is > the only really reliable way to go with this and is what all > commercial accounting systems do. *But* it is a huge task to change > what is a design decision that affects every script and most of the > database tables. > > -Tim -- View this message in context: http://weberp-accounting.1478800.n4.nabble.com/Float-accuracy-problem-tp4657899p4657952.html Sent from the web-ERP-developers mailing list archive at Nabble.com. |
From: ExsonQu <hex...@gm...> - 2015-01-09 02:44:51
|
*Hi, Robert,* Thank you for your suggestion. I've read MySQL's manual and it said it will use exact number as possible. During use webERP, we just face problem in some situation. So I think we need more efforts to pinpoint it. As Tim said, decimal type has it's shortage that we have to predefine the digital numbers and decimal places. Thanks and best regards! Exson -- View this message in context: http://weberp-accounting.1478800.n4.nabble.com/Float-accuracy-problem-tp4657899p4657961.html Sent from the web-ERP-developers mailing list archive at Nabble.com. |
From: ExsonQu <hex...@gm...> - 2015-01-09 01:03:43
|
*Dear all,* I've searched more data about this. Since we use double type almost everywhere, only sometime things go wrong. And about the precision math, mysql manual has explained that: http://dev.mysql.com/doc/refman/5.7/en/precision-math-examples.html Numbers are used with their exact value as given when possible. The simple and effective solution may just find out those places where 'exact values used not possible'. Best regards! Exson -- View this message in context: http://weberp-accounting.1478800.n4.nabble.com/Float-accuracy-problem-tp4657899p4657957.html Sent from the web-ERP-developers mailing list archive at Nabble.com. |
From: Pak R. <pak...@gm...> - 2015-01-09 01:49:10
|
Hi all: I think some of the issues come from the way we do some calculations, more than Turing's machines restrictions on floating numbers :-) As a simplified example: In some cases we calculate: X = A / B; in some other cases we maybe do: C = B / A; .... .... X = 1 / C; Even if mathematically it's equivalent, a computer can produce a slightly different result We have some examples of this on multicurrency calculations (translating from home to foreign or viceversa). This links to a "hot topic" we discussed last year, as we do the same calculations over and over in different scripts with slightly different code. But I don't want to introduce more variables to the issue. Let's focus on precision, but probably we need to have this in mind, if we look for a consistent solution. Regards, Ricard 2015-01-09 9:01 GMT+08:00 ExsonQu <hex...@gm...>: > *Dear all,* > > I've searched more data about this. > > Since we use double type almost everywhere, only sometime things > go > wrong. And about the precision math, mysql manual has explained that: > > http://dev.mysql.com/doc/refman/5.7/en/precision-math-examples.html > Numbers are used with their exact value as given when possible. > > The simple and effective solution may just find out those places > where 'exact values used not possible'. > > Best regards! > > Exson > > > > > -- > View this message in context: > http://weberp-accounting.1478800.n4.nabble.com/Float-accuracy-problem-tp4657899p4657957.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 > |
From: ExsonQu <hex...@gm...> - 2015-01-09 02:48:37
|
*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. |
From: Rafael C. <raf...@gm...> - 2015-01-09 17:35:58
|
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 > |
From: ichmon21 <chr...@gm...> - 2015-02-25 12:40:59
|
Even I also got this float accuracy problem when I solve my accounts queries! I am going to clear my doubt from Professor Aloke Ghosh <https://www.linkedin.com/pub/al-aloke-ghosh-ph-d/7/75a/9ba> , as I heard he clear everything in detail as no one can forget that in future. I wish he can clear my doubt too. -- View this message in context: http://weberp-accounting.1478800.n4.nabble.com/Float-accuracy-problem-tp4657899p4658126.html Sent from the web-ERP-developers mailing list archive at Nabble.com. |