Dev - currency-amount vs. currency-precise vs. floating-point

[Si Chen-2]

Hi.

Just noticed this which I thought was kind of odd:

OrderAdjustment.amount is a "currency-precise"
OrderItem.unitPrice, InvoiceItem.amount, etc. are "currency-amount"
OrderItem.quantity is a "floating-point"

When should we be using floating-point vs. currency-precise vs.
currency-amount?

These differences with the database are creating rounding errors.  For
example, if an adjustment is stored in the database as 2.599 and then
later on retrieved for returns, the return calculation will be using
2.599, but then it will be stored into database as 2.59, losing 1 cent.

Si
 
_______________________________________________
Dev mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/dev

[David E. Jones]

On Mar 10, 2006, at 6:18 PM, Si Chen wrote:

> Hi.
>
> Just noticed this which I thought was kind of odd:
>
> OrderAdjustment.amount is a "currency-precise"
> OrderItem.unitPrice, InvoiceItem.amount, etc. are "currency-amount"
> OrderItem.quantity is a "floating-point"
>
> When should we be using floating-point vs. currency-precise vs.
> currency-amount?

These are the best examples of these, well OrderItem.quantity anyway,  
I guess the others are good. The quantity really should be a  
"numeric" but is a floating-point for historic reasons. When there is  
a need for partial number values the amount field should be used  
combined with an integer quantity.

In general the currency-amount should always be used for all totals  
and final finals. The OrderAdjustment.amount is a good example of a  
value that should be a "currency-precise" because it is an interim  
value that will be summed to the total, and if each line is rounded  
before the sum then there can be issues with the total, so as is  
often done in business systems these are stored with greater precision.


>  These differences with the database are creating rounding errors.  
> For
> example, if an adjustment is stored in the database as 2.599 and then
> later on retrieved for returns, the return calculation will be using
> 2.599, but then it will be stored into database as 2.59, losing 1  
> cent.

This sounds like an issue of truncation being done where rounding  
really should be done...

-David

 
_______________________________________________
Dev mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/dev

[Jacopo Cappellato]

David, Si,

this is an interesting topic.

Please see my comments inline:

David E. Jones wrote:

> On Mar 10, 2006, at 6:18 PM, Si Chen wrote:
>
>> Hi.
>>
>> Just noticed this which I thought was kind of odd:
>>
>> OrderAdjustment.amount is a "currency-precise"
>> OrderItem.unitPrice, InvoiceItem.amount, etc. are "currency-amount"
>> OrderItem.quantity is a "floating-point"
>>
>> When should we be using floating-point vs. currency-precise vs.
>> currency-amount?
>
> These are the best examples of these, well OrderItem.quantity anyway,  
> I guess the others are good. The quantity really should be a  
> "numeric" but is a floating-point for historic reasons. When there is  
> a need for partial number values the amount field should be used  
> combined with an integer quantity.
>
> In general the currency-amount should always be used for all totals  
> and final finals. The OrderAdjustment.amount is a good example of a  
> value that should be a "currency-precise" because it is an interim  
> value that will be summed to the total, and if each line is rounded  
> before the sum then there can be issues with the total, so as is  
> often done in business systems these are stored with greater precision.
>
>
>>  These differences with the database are creating rounding errors.  
>> For
>> example, if an adjustment is stored in the database as 2.599 and then
>> later on retrieved for returns, the return calculation will be using
>> 2.599, but then it will be stored into database as 2.59, losing 1  
>> cent.
>
> This sounds like an issue of truncation being done where rounding  
> really should be done...
>

The real problem is that different db treat approximations in different
ways; it seems that the SQL specification says that whether rounding or
truncation is used is implementation defined, see the comments to the
following Jira issue:

http://jira.undersunconsulting.com/browse/OFBIZ-565

So for example DerbyDB truncates while MySQL approximates numbers.
For this reason, I really think we should not delegate the
approximations to the underlying db.

Another issue (but maybe a bit out of topic here) is that if we have a
(double) variable that (after some calculations) has some decimals, and
the variable will be stored in a db field with less decimals digits, we
should approximate the variable to the db fields decimal positions
BEFORE using it in any calculation; a good example of this is reported here:

http://jira.undersunconsulting.com/browse/OFBIZ-567

Jacopo

> -David
>
>  
> _______________________________________________
> Dev mailing list
> [hidden email]
> http://lists.ofbiz.org/mailman/listinfo/dev
>

 
_______________________________________________
Dev mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/dev

[David E. Jones]

On Mar 11, 2006, at 12:10 AM, Jacopo Cappellato wrote:

>> This sounds like an issue of truncation being done where rounding
>> really should be done...
>>
>
> The real problem is that different db treat approximations in  
> different
> ways; it seems that the SQL specification says that whether  
> rounding or
> truncation is used is implementation defined, see the comments to the
> following Jira issue:
>
> http://jira.undersunconsulting.com/browse/OFBIZ-565
>
> So for example DerbyDB truncates while MySQL approximates numbers.
> For this reason, I really think we should not delegate the
> approximations to the underlying db.
>
> Another issue (but maybe a bit out of topic here) is that if we have a
> (double) variable that (after some calculations) has some decimals,  
> and
> the variable will be stored in a db field with less decimals  
> digits, we
> should approximate the variable to the db fields decimal positions
> BEFORE using it in any calculation; a good example of this is  
> reported here:
>
> http://jira.undersunconsulting.com/browse/OFBIZ-567

Yes, it may that this is where the truncation is happening (ie  
expecting the database to correctly handle the additional precision).  
I agree that we should always be handling this in the OFBiz code and  
not leaving it to the database.

To really fix this problem is going to be interesting. All currency-
amount and currency-precise fields should really be changed to use  
BigDecimal instead of Double. This can be done on the Entity layer by  
simply changing it in the appropriate fieldtypes*.xml file. I did  
this a while back and tested it and made some changes to the Entity  
Engine to support it, but in that testing I found a lot of stuff that  
breaks it on higher levels. This will result in the change in many  
service definitions (and perhaps implementations) and quite a bit of  
code (especially Java code) that does these calculations and pushes  
things back to the database or gets them from the database. I did put  
some thing in there to soften the blow and convert between Double and  
BigDecimal as needed, but those should eventually be removed.

Anyway, at some point we need to take this step and I think it would  
help this issue a lot so that you never have to worry about passing a  
Double to the Entity Engine and wonder what the database will do with  
it...

-David


 
_______________________________________________
Dev mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/dev

[BJ Freeman]

This and the other subject about rounding are a thorn for Accounting.
Especally for double post ledger.
The problem I have run into is that at reconcile tiem, the .000000001
will not make the two sides of a ledger balance.
Also if you round one way on one side and round one another on the other
side you will get different results.
finally you have the weather your round each process or round the final
result of the process.

for that reason, I believe the should be a class the handles all
rounding that can be modified easily. There would be a function the
stores the results of multiple calcuation then give the result, round back.

And a good practice stated of code that deals with rounding of multiple
calcualtions.

David E. Jones sent the following on 3/10/06 11:21 PM:

> On Mar 11, 2006, at 12:10 AM, Jacopo Cappellato wrote:
>
>
>>>This sounds like an issue of truncation being done where rounding
>>>really should be done...
>>>
>>
>>The real problem is that different db treat approximations in  
>>different
>>ways; it seems that the SQL specification says that whether  
>>rounding or
>>truncation is used is implementation defined, see the comments to the
>>following Jira issue:
>>
>>http://jira.undersunconsulting.com/browse/OFBIZ-565
>>
>>So for example DerbyDB truncates while MySQL approximates numbers.
>>For this reason, I really think we should not delegate the
>>approximations to the underlying db.
>>
>>Another issue (but maybe a bit out of topic here) is that if we have a
>>(double) variable that (after some calculations) has some decimals,  
>>and
>>the variable will be stored in a db field with less decimals  
>>digits, we
>>should approximate the variable to the db fields decimal positions
>>BEFORE using it in any calculation; a good example of this is  
>>reported here:
>>
>>http://jira.undersunconsulting.com/browse/OFBIZ-567
>
>
> Yes, it may that this is where the truncation is happening (ie  
> expecting the database to correctly handle the additional precision).  
> I agree that we should always be handling this in the OFBiz code and  
> not leaving it to the database.
>
> To really fix this problem is going to be interesting. All currency-
> amount and currency-precise fields should really be changed to use  
> BigDecimal instead of Double. This can be done on the Entity layer by  
> simply changing it in the appropriate fieldtypes*.xml file. I did  
> this a while back and tested it and made some changes to the Entity  
> Engine to support it, but in that testing I found a lot of stuff that  
> breaks it on higher levels. This will result in the change in many  
> service definitions (and perhaps implementations) and quite a bit of  
> code (especially Java code) that does these calculations and pushes  
> things back to the database or gets them from the database. I did put  
> some thing in there to soften the blow and convert between Double and  
> BigDecimal as needed, but those should eventually be removed.
>
> Anyway, at some point we need to take this step and I think it would  
> help this issue a lot so that you never have to worry about passing a  
> Double to the Entity Engine and wonder what the database will do with  
> it...
>
> -David
>
>
>  
> _______________________________________________
> Dev mailing list
> [hidden email]
> http://lists.ofbiz.org/mailman/listinfo/dev
>

 
_______________________________________________
Dev mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/dev

[Leon Torres-2]

David E. Jones wrote:
>
> Anyway, at some point we need to take this step and I think it would  
> help this issue a lot so that you never have to worry about passing a  
> Double to the Entity Engine and wonder what the database will do with  
> it...
>
> -David

Maybe we should create a branch specifically for this issue? It would help
mitigate complaints of these rounding errors while we refactor and keep the
development and bug reporting in focus. I've noticed that the refactorings tend
to flush out more rounding issues with legacy code, which is great from a
refactoring perspective because we can flag that code, but bad for user perception.

- Leon
 
_______________________________________________
Dev mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/dev

[David E. Jones]

Leon Torres wrote:

> David E. Jones wrote:
>> Anyway, at some point we need to take this step and I think it would  
>> help this issue a lot so that you never have to worry about passing a  
>> Double to the Entity Engine and wonder what the database will do with  
>> it...
>>
>> -David
>
> Maybe we should create a branch specifically for this issue? It would help
> mitigate complaints of these rounding errors while we refactor and keep the
> development and bug reporting in focus. I've noticed that the refactorings tend
> to flush out more rounding issues with legacy code, which is great from a
> refactoring perspective because we can flag that code, but bad for user perception.
>
> - Leon

We could do a branch for this but there are a number of things to watch out for when doing a branch, and for that reason I usually don't like them. They are okay if they are kept very short lived to avoid too many code sync issues. Also they are only really needed if a group is already in place that plans to work on it. In other words, a local sandbox should be used for changes unless there are multiple people working on it that need to coordinate through a shared repository.

-David
 
_______________________________________________
Dev mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/dev

[Leon Torres-2]

Hi all,

I'm going to go through and review all the existing BigDecimal implementations,
standardize the usage, and identify the next set of services to refactor.

However, what do we want to do with service input/output attributes that are
currently Double but are handled internally as BigDecimal? Should we replace the
Double with java.math.BigDecimal in the servicedefs?

Thanks,

- Leon
 
_______________________________________________
Dev mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/dev

[David E. Jones]

Leon Torres wrote:
> Hi all,
>
> I'm going to go through and review all the existing BigDecimal implementations,
> standardize the usage, and identify the next set of services to refactor.
>
> However, what do we want to do with service input/output attributes that are
> currently Double but are handled internally as BigDecimal? Should we replace the
> Double with java.math.BigDecimal in the servicedefs?

Yes, definitely yes these should change from Double to BigDecimal. I'm guesing this came up under the overall pattern of avoiding conversion between Double and BigDecimal, and this certainly fits into that.

-David


 
_______________________________________________
Dev mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/dev

[Si Chen-2]

Ok.  This really helped to clear it up.  So now I think ReturnAdjustment
should be "currency-precise" just like OrderAdjustment.

What about true "intermediate values" like average cost?  If we have an
average cost such as 3.889107143, should we just store it as a
floating-point for maximum precision?  Should these be rounded at all?

Si

David E. Jones wrote:

>On Mar 11, 2006, at 12:10 AM, Jacopo Cappellato wrote:
>
>  
>
>>>This sounds like an issue of truncation being done where rounding
>>>really should be done...
>>>
>>>      
>>>
>>The real problem is that different db treat approximations in  
>>different
>>ways; it seems that the SQL specification says that whether  
>>rounding or
>>truncation is used is implementation defined, see the comments to the
>>following Jira issue:
>>
>>http://jira.undersunconsulting.com/browse/OFBIZ-565
>>
>>So for example DerbyDB truncates while MySQL approximates numbers.
>>For this reason, I really think we should not delegate the
>>approximations to the underlying db.
>>
>>Another issue (but maybe a bit out of topic here) is that if we have a
>>(double) variable that (after some calculations) has some decimals,  
>>and
>>the variable will be stored in a db field with less decimals  
>>digits, we
>>should approximate the variable to the db fields decimal positions
>>BEFORE using it in any calculation; a good example of this is  
>>reported here:
>>
>>http://jira.undersunconsulting.com/browse/OFBIZ-567
>>    
>>
>
>Yes, it may that this is where the truncation is happening (ie  
>expecting the database to correctly handle the additional precision).  
>I agree that we should always be handling this in the OFBiz code and  
>not leaving it to the database.
>
>To really fix this problem is going to be interesting. All currency-
>amount and currency-precise fields should really be changed to use  
>BigDecimal instead of Double. This can be done on the Entity layer by  
>simply changing it in the appropriate fieldtypes*.xml file. I did  
>this a while back and tested it and made some changes to the Entity  
>Engine to support it, but in that testing I found a lot of stuff that  
>breaks it on higher levels. This will result in the change in many  
>service definitions (and perhaps implementations) and quite a bit of  
>code (especially Java code) that does these calculations and pushes  
>things back to the database or gets them from the database. I did put  
>some thing in there to soften the blow and convert between Double and  
>BigDecimal as needed, but those should eventually be removed.
>
>Anyway, at some point we need to take this step and I think it would  
>help this issue a lot so that you never have to worry about passing a  
>Double to the Entity Engine and wonder what the database will do with  
>it...
>
>-David
>
>
>
>_______________________________________________
>Dev mailing list
>[hidden email]
>http://lists.ofbiz.org/mailman/listinfo/dev
>
>  
>

 
_______________________________________________
Dev mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/dev

[Adrian Crum]

My two cents worth: store/calculate everything with highest precision available.
Save the rounding for output (display/print).


Si Chen wrote:

> Ok.  This really helped to clear it up.  So now I think ReturnAdjustment
> should be "currency-precise" just like OrderAdjustment.
>
> What about true "intermediate values" like average cost?  If we have an
> average cost such as 3.889107143, should we just store it as a
> floating-point for maximum precision?  Should these be rounded at all?
>
> Si
>
> David E. Jones wrote:
>
>
>>On Mar 11, 2006, at 12:10 AM, Jacopo Cappellato wrote:
>>
>>
>>
>>
>>>>This sounds like an issue of truncation being done where rounding
>>>>really should be done...
>>>>
>>>>    
>>>>
>>>
>>>The real problem is that different db treat approximations in  
>>>different
>>>ways; it seems that the SQL specification says that whether  
>>>rounding or
>>>truncation is used is implementation defined, see the comments to the
>>>following Jira issue:
>>>
>>>http://jira.undersunconsulting.com/browse/OFBIZ-565
>>>
>>>So for example DerbyDB truncates while MySQL approximates numbers.
>>>For this reason, I really think we should not delegate the
>>>approximations to the underlying db.
>>>
>>>Another issue (but maybe a bit out of topic here) is that if we have a
>>>(double) variable that (after some calculations) has some decimals,  
>>>and
>>>the variable will be stored in a db field with less decimals  
>>>digits, we
>>>should approximate the variable to the db fields decimal positions
>>>BEFORE using it in any calculation; a good example of this is  
>>>reported here:
>>>
>>>http://jira.undersunconsulting.com/browse/OFBIZ-567
>>>  
>>>
>>
>>Yes, it may that this is where the truncation is happening (ie  
>>expecting the database to correctly handle the additional precision).  
>>I agree that we should always be handling this in the OFBiz code and  
>>not leaving it to the database.
>>
>>To really fix this problem is going to be interesting. All currency-
>>amount and currency-precise fields should really be changed to use  
>>BigDecimal instead of Double. This can be done on the Entity layer by  
>>simply changing it in the appropriate fieldtypes*.xml file. I did  
>>this a while back and tested it and made some changes to the Entity  
>>Engine to support it, but in that testing I found a lot of stuff that  
>>breaks it on higher levels. This will result in the change in many  
>>service definitions (and perhaps implementations) and quite a bit of  
>>code (especially Java code) that does these calculations and pushes  
>>things back to the database or gets them from the database. I did put  
>>some thing in there to soften the blow and convert between Double and  
>>BigDecimal as needed, but those should eventually be removed.
>>
>>Anyway, at some point we need to take this step and I think it would  
>>help this issue a lot so that you never have to worry about passing a  
>>Double to the Entity Engine and wonder what the database will do with  
>>it...
>>
>>-David
>>
>>
>>
>>_______________________________________________
>>Dev mailing list
>>[hidden email]
>>http://lists.ofbiz.org/mailman/listinfo/dev
>>
>>
>>
>
>  
> _______________________________________________
> Dev mailing list
> [hidden email]
> http://lists.ofbiz.org/mailman/listinfo/dev
>

 
_______________________________________________
Dev mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/dev

[David E. Jones]

This may be a good idea in some cases, but for others it is dangerous... like where consistency for "final" numbers is important. That would include things like order totals, invoice amounts, payment amounts, etc. By definition you can't pay someone in partial cents, so the amounts must be rounded there, and better to do it and have it be consistent than to risk something somehow changing and messing things up later on.

-David


Adrian Crum wrote:

> My two cents worth: store/calculate everything with highest precision available.
> Save the rounding for output (display/print).
>
>
> Si Chen wrote:
>
>> Ok.  This really helped to clear it up.  So now I think ReturnAdjustment
>> should be "currency-precise" just like OrderAdjustment.
>>
>> What about true "intermediate values" like average cost?  If we have an
>> average cost such as 3.889107143, should we just store it as a
>> floating-point for maximum precision?  Should these be rounded at all?
>>
>> Si
>>
>> David E. Jones wrote:
>>
>>
>>> On Mar 11, 2006, at 12:10 AM, Jacopo Cappellato wrote:
>>>
>>>
>>>
>>>
>>>>> This sounds like an issue of truncation being done where rounding
>>>>> really should be done...
>>>>>
>>>>>    
>>>>>
>>>> The real problem is that different db treat approximations in  
>>>> different
>>>> ways; it seems that the SQL specification says that whether  
>>>> rounding or
>>>> truncation is used is implementation defined, see the comments to the
>>>> following Jira issue:
>>>>
>>>> http://jira.undersunconsulting.com/browse/OFBIZ-565
>>>>
>>>> So for example DerbyDB truncates while MySQL approximates numbers.
>>>> For this reason, I really think we should not delegate the
>>>> approximations to the underlying db.
>>>>
>>>> Another issue (but maybe a bit out of topic here) is that if we have a
>>>> (double) variable that (after some calculations) has some decimals,  
>>>> and
>>>> the variable will be stored in a db field with less decimals  
>>>> digits, we
>>>> should approximate the variable to the db fields decimal positions
>>>> BEFORE using it in any calculation; a good example of this is  
>>>> reported here:
>>>>
>>>> http://jira.undersunconsulting.com/browse/OFBIZ-567
>>>>  
>>>>
>>> Yes, it may that this is where the truncation is happening (ie  
>>> expecting the database to correctly handle the additional precision).  
>>> I agree that we should always be handling this in the OFBiz code and  
>>> not leaving it to the database.
>>>
>>> To really fix this problem is going to be interesting. All currency-
>>> amount and currency-precise fields should really be changed to use  
>>> BigDecimal instead of Double. This can be done on the Entity layer by  
>>> simply changing it in the appropriate fieldtypes*.xml file. I did  
>>> this a while back and tested it and made some changes to the Entity  
>>> Engine to support it, but in that testing I found a lot of stuff that  
>>> breaks it on higher levels. This will result in the change in many  
>>> service definitions (and perhaps implementations) and quite a bit of  
>>> code (especially Java code) that does these calculations and pushes  
>>> things back to the database or gets them from the database. I did put  
>>> some thing in there to soften the blow and convert between Double and  
>>> BigDecimal as needed, but those should eventually be removed.
>>>
>>> Anyway, at some point we need to take this step and I think it would  
>>> help this issue a lot so that you never have to worry about passing a  
>>> Double to the Entity Engine and wonder what the database will do with  
>>> it...
>>>
>>> -David
>>>
>>>
>>>
>>> _______________________________________________
>>> Dev mailing list
>>> [hidden email]
>>> http://lists.ofbiz.org/mailman/listinfo/dev
>>>
>>>
>>>
>>  
>> _______________________________________________
>> Dev mailing list
>> [hidden email]
>> http://lists.ofbiz.org/mailman/listinfo/dev
>>
>  
> _______________________________________________
> Dev mailing list
> [hidden email]
> http://lists.ofbiz.org/mailman/listinfo/dev

 
_______________________________________________
Dev mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/dev

[David E. Jones]

In general what is used would depend on the intended purpose of the field. In this case it is a value that is intended to be used as part of price calculations. It will be put into formulas and so it does kind of make sense to have more precision and let the rounding be done after the calculation is complete. Of course, on that note it might be better to have all prices be available with more precision as they are all potentially usable in a calculation (well, with a few exceptions... like max/min and in most cases default).

Still, I'm not sure if we want to use floating point values there. They are currently using the "currency-precise" type so that there are a couple of extra digits to play with. Only in fairly extreme calculations would this not be sufficient... like charging 1000 times the average cost, for example, then the precision would not be sufficient. Of course, if you can get away with charging 1000 times the cost, then you probably don't care to much about those few pennies of additional gain/loss. In fact, after doing that for just a short time you probably don't have much more to worry about than whether or not the beach waiter at your favorite resort and current residence in the bahamas might have been a customer and found out about that profit margin.

Anyway... I guess I'm not sure in what scenario this would become a problem. For order large quantities usually the per-item price is rounded before multiplying by the quantity, though not always (like gas for example... somehow they think that people are going to buy more or something if the price appears to be 1 cent cheaper?).

-David



Si Chen wrote:

> Ok.  This really helped to clear it up.  So now I think ReturnAdjustment
> should be "currency-precise" just like OrderAdjustment.
>
> What about true "intermediate values" like average cost?  If we have an
> average cost such as 3.889107143, should we just store it as a
> floating-point for maximum precision?  Should these be rounded at all?
>
> Si
>
> David E. Jones wrote:
>
>> On Mar 11, 2006, at 12:10 AM, Jacopo Cappellato wrote:
>>
>>  
>>
>>>> This sounds like an issue of truncation being done where rounding
>>>> really should be done...
>>>>
>>>>      
>>>>
>>> The real problem is that different db treat approximations in  
>>> different
>>> ways; it seems that the SQL specification says that whether  
>>> rounding or
>>> truncation is used is implementation defined, see the comments to the
>>> following Jira issue:
>>>
>>> http://jira.undersunconsulting.com/browse/OFBIZ-565
>>>
>>> So for example DerbyDB truncates while MySQL approximates numbers.
>>> For this reason, I really think we should not delegate the
>>> approximations to the underlying db.
>>>
>>> Another issue (but maybe a bit out of topic here) is that if we have a
>>> (double) variable that (after some calculations) has some decimals,  
>>> and
>>> the variable will be stored in a db field with less decimals  
>>> digits, we
>>> should approximate the variable to the db fields decimal positions
>>> BEFORE using it in any calculation; a good example of this is  
>>> reported here:
>>>
>>> http://jira.undersunconsulting.com/browse/OFBIZ-567
>>>    
>>>
>> Yes, it may that this is where the truncation is happening (ie  
>> expecting the database to correctly handle the additional precision).  
>> I agree that we should always be handling this in the OFBiz code and  
>> not leaving it to the database.
>>
>> To really fix this problem is going to be interesting. All currency-
>> amount and currency-precise fields should really be changed to use  
>> BigDecimal instead of Double. This can be done on the Entity layer by  
>> simply changing it in the appropriate fieldtypes*.xml file. I did  
>> this a while back and tested it and made some changes to the Entity  
>> Engine to support it, but in that testing I found a lot of stuff that  
>> breaks it on higher levels. This will result in the change in many  
>> service definitions (and perhaps implementations) and quite a bit of  
>> code (especially Java code) that does these calculations and pushes  
>> things back to the database or gets them from the database. I did put  
>> some thing in there to soften the blow and convert between Double and  
>> BigDecimal as needed, but those should eventually be removed.
>>
>> Anyway, at some point we need to take this step and I think it would  
>> help this issue a lot so that you never have to worry about passing a  
>> Double to the Entity Engine and wonder what the database will do with  
>> it...
>>
>> -David
>>
>>
>>
>> _______________________________________________
>> Dev mailing list
>> [hidden email]
>> http://lists.ofbiz.org/mailman/listinfo/dev
>>
>>  
>>
>  
> _______________________________________________
> Dev mailing list
> [hidden email]
> http://lists.ofbiz.org/mailman/listinfo/dev

 
_______________________________________________
Dev mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/dev

[Daniel Kunkel]

Hi Si, David

> Still, I'm not sure if we want to use floating point values there.
> They are currently using the "currency-precise" type so that there
> are a couple of extra digits to play with. Only in fairly extreme
> calculations would this not be sufficient... like charging 1000
> times the average cost, for example, then the precision would not
>  be sufficient. Of course, if you can get away with charging 1000
> times the cost, then you probably don't care to much about those
> few pennies of additional gain/loss. In fact, after doing that for
> just a short time you probably don't have much more to worry about
> than whether or not the beach waiter at your favorite resort and
> current residence in the bahamas might have been a customer and
> found out about that profit margin.
>

I believe the calculations should be done in such a way that the costs
work out to the penny, instead of rounding, and eventually having large
errors.

For example, if you bought a million pieces for $3,889,107,143, and sold
them out of inventory one at a time for $3.89 without tracking/updating
the residual average cost..  eventually you'd have to account for
difference of more than $800,000!

Adjusting the average cost after each transaction would avoid the
problem of course.

Another way that it might be done is to track the total cost/value of
inventory. This with the total quantity would automatically adjust the
price between 3.88 and 3.89 so the accounting would be exactly correct.
When some was sold, you'd subtract the accounted cost to get a residual
that was accurate to the penny.

Thanks

Daniel

> Anyway... I guess I'm not sure in what scenario this would become a problem. For order large quantities usually the per-item price is rounded before multiplying by the quantity, though not always (like gas for example... somehow they think that people are going to buy more or something if the price appears to be 1 cent cheaper?).
>
> -David
>
>
>
> Si Chen wrote:
> > Ok.  This really helped to clear it up.  So now I think ReturnAdjustment
> > should be "currency-precise" just like OrderAdjustment.
> >
> > What about true "intermediate values" like average cost?  If we have an
> > average cost such as 3.889107143, should we just store it as a
> > floating-point for maximum precision?  Should these be rounded at all?
> >
> > Si
> >
> > David E. Jones wrote:
> >
> >> On Mar 11, 2006, at 12:10 AM, Jacopo Cappellato wrote:
> >>
> >>  
> >>
> >>>> This sounds like an issue of truncation being done where rounding
> >>>> really should be done...
> >>>>
> >>>>      
> >>>>
> >>> The real problem is that different db treat approximations in  
> >>> different
> >>> ways; it seems that the SQL specification says that whether  
> >>> rounding or
> >>> truncation is used is implementation defined, see the comments to the
> >>> following Jira issue:
> >>>
> >>> http://jira.undersunconsulting.com/browse/OFBIZ-565
> >>>
> >>> So for example DerbyDB truncates while MySQL approximates numbers.
> >>> For this reason, I really think we should not delegate the
> >>> approximations to the underlying db.
> >>>
> >>> Another issue (but maybe a bit out of topic here) is that if we have a
> >>> (double) variable that (after some calculations) has some decimals,  
> >>> and
> >>> the variable will be stored in a db field with less decimals  
> >>> digits, we
> >>> should approximate the variable to the db fields decimal positions
> >>> BEFORE using it in any calculation; a good example of this is  
> >>> reported here:
> >>>
> >>> http://jira.undersunconsulting.com/browse/OFBIZ-567
> >>>    
> >>>
> >> Yes, it may that this is where the truncation is happening (ie  
> >> expecting the database to correctly handle the additional precision).  
> >> I agree that we should always be handling this in the OFBiz code and  
> >> not leaving it to the database.
> >>
> >> To really fix this problem is going to be interesting. All currency-
> >> amount and currency-precise fields should really be changed to use  
> >> BigDecimal instead of Double. This can be done on the Entity layer by  
> >> simply changing it in the appropriate fieldtypes*.xml file. I did  
> >> this a while back and tested it and made some changes to the Entity  
> >> Engine to support it, but in that testing I found a lot of stuff that  
> >> breaks it on higher levels. This will result in the change in many  
> >> service definitions (and perhaps implementations) and quite a bit of  
> >> code (especially Java code) that does these calculations and pushes  
> >> things back to the database or gets them from the database. I did put  
> >> some thing in there to soften the blow and convert between Double and  
> >> BigDecimal as needed, but those should eventually be removed.
> >>
> >> Anyway, at some point we need to take this step and I think it would  
> >> help this issue a lot so that you never have to worry about passing a  
> >> Double to the Entity Engine and wonder what the database will do with  
> >> it...
> >>
> >> -David
> >>
> >>
> >>
> >> _______________________________________________
> >> Dev mailing list
> >> [hidden email]
> >> http://lists.ofbiz.org/mailman/listinfo/dev
> >>
> >>  
> >>
> >  
> > _______________________________________________
> > Dev mailing list
> > [hidden email]
> > http://lists.ofbiz.org/mailman/listinfo/dev
>  
> _______________________________________________
> Dev mailing list
> [hidden email]
> http://lists.ofbiz.org/mailman/listinfo/dev

--
Daniel

*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-
Have a GREAT Day!

Daniel Kunkel           [hidden email]
BioWaves, LLC           http://www.BioWaves.com
14150 NE 20th St. Suite F1
Bellevue, WA 98007
800-734-3588    425-895-0050
http://www.Apartment-Pets.com  http://www.Focus-Illusion.com
http://www.Brain-Fun.com       http://www.ColorGlasses.com
*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-

 
_______________________________________________
Dev mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/dev

[Si Chen-2]

David, et al. -

I think the real trick is how the field is intended to be used.  Also,
the actual field in the database should be tied directly to the
BigDecimal rounding used.  Specifically:

1.  If it's intended as the final amount, then "currency-amount" is
good, and BigDecimal rounding should be set to 2 places.

2.  If it's intended as an intermediate value which is to be *added* to
derive a final amount, then "currency-precise" should be fine.  That
additional one decimal place should do the trick, and BigDecimal
rounding should be set to 3 places.

3.  If it's intended as an intermediate value which is going to be
*multipled* to derive a final amount, then we should have more decimal
places to preserve precision.  This would apply to
InventoryItem.unitCost and ProductAverageCost.averageCost, for example.  
This could apply For BigDecimal rounding, we're actually doing the
equivalent of no rounding (ie, 100 decimal places), see r 6972 for
InvoiceServices.java:

BigDecimal ratio = receipt.getBigDecimal("quantityAccepted").divide(returnItem.getBigDecimal("returnQuantity"), 100, rounding);

Well, maybe this is a bit of a hack and we should have ideally a
BigDecimal rounding setting and a field type which have the same
precision, of say 4, 6, 8, or 10, or even 12 decimal places.  So the
question is whether we should create a new fieldtype in fieldtype_.xml
which has the requisite number of decimal places, or just use
"floating-point"?

Maybe it might be better to create a special fieldtype for it just so we
could explicitly control behavior across databases?

Si

PS By the way, I think a 0.01 or 0.02 difference in values would not so
much have an economic impact as affect people's perception of how
"accurate" or "error-prone" the program is.  Hence my quest to chase
down that last penny, I guess...

David E Jones wrote:

>In general what is used would depend on the intended purpose of the field. In this case it is a value that is intended to be used as part of price calculations. It will be put into formulas and so it does kind of make sense to have more precision and let the rounding be done after the calculation is complete. Of course, on that note it might be better to have all prices be available with more precision as they are all potentially usable in a calculation (well, with a few exceptions... like max/min and in most cases default).
>
>Still, I'm not sure if we want to use floating point values there. They are currently using the "currency-precise" type so that there are a couple of extra digits to play with. Only in fairly extreme calculations would this not be sufficient... like charging 1000 times the average cost, for example, then the precision would not be sufficient. Of course, if you can get away with charging 1000 times the cost, then you probably don't care to much about those few pennies of additional gain/loss. In fact, after doing that for just a short time you probably don't have much more to worry about than whether or not the beach waiter at your favorite resort and current residence in the bahamas might have been a customer and found out about that profit margin.
>
>Anyway... I guess I'm not sure in what scenario this would become a problem. For order large quantities usually the per-item price is rounded before multiplying by the quantity, though not always (like gas for example... somehow they think that people are going to buy more or something if the price appears to be 1 cent cheaper?).
>
>-David
>
>
>
>Si Chen wrote:
>  
>
>>Ok.  This really helped to clear it up.  So now I think ReturnAdjustment
>>should be "currency-precise" just like OrderAdjustment.
>>
>>What about true "intermediate values" like average cost?  If we have an
>>average cost such as 3.889107143, should we just store it as a
>>floating-point for maximum precision?  Should these be rounded at all?
>>
>>Si
>>
>>David E. Jones wrote:
>>
>>    
>>
>>>On Mar 11, 2006, at 12:10 AM, Jacopo Cappellato wrote:
>>>
>>>
>>>
>>>      
>>>
>>>>>This sounds like an issue of truncation being done where rounding
>>>>>really should be done...
>>>>>
>>>>>    
>>>>>
>>>>>          
>>>>>
>>>>The real problem is that different db treat approximations in  
>>>>different
>>>>ways; it seems that the SQL specification says that whether  
>>>>rounding or
>>>>truncation is used is implementation defined, see the comments to the
>>>>following Jira issue:
>>>>
>>>>http://jira.undersunconsulting.com/browse/OFBIZ-565
>>>>
>>>>So for example DerbyDB truncates while MySQL approximates numbers.
>>>>For this reason, I really think we should not delegate the
>>>>approximations to the underlying db.
>>>>
>>>>Another issue (but maybe a bit out of topic here) is that if we have a
>>>>(double) variable that (after some calculations) has some decimals,  
>>>>and
>>>>the variable will be stored in a db field with less decimals  
>>>>digits, we
>>>>should approximate the variable to the db fields decimal positions
>>>>BEFORE using it in any calculation; a good example of this is  
>>>>reported here:
>>>>
>>>>http://jira.undersunconsulting.com/browse/OFBIZ-567
>>>>  
>>>>
>>>>        
>>>>
>>>Yes, it may that this is where the truncation is happening (ie  
>>>expecting the database to correctly handle the additional precision).  
>>>I agree that we should always be handling this in the OFBiz code and  
>>>not leaving it to the database.
>>>
>>>To really fix this problem is going to be interesting. All currency-
>>>amount and currency-precise fields should really be changed to use  
>>>BigDecimal instead of Double. This can be done on the Entity layer by  
>>>simply changing it in the appropriate fieldtypes*.xml file. I did  
>>>this a while back and tested it and made some changes to the Entity  
>>>Engine to support it, but in that testing I found a lot of stuff that  
>>>breaks it on higher levels. This will result in the change in many  
>>>service definitions (and perhaps implementations) and quite a bit of  
>>>code (especially Java code) that does these calculations and pushes  
>>>things back to the database or gets them from the database. I did put  
>>>some thing in there to soften the blow and convert between Double and  
>>>BigDecimal as needed, but those should eventually be removed.
>>>
>>>Anyway, at some point we need to take this step and I think it would  
>>>help this issue a lot so that you never have to worry about passing a  
>>>Double to the Entity Engine and wonder what the database will do with  
>>>it...
>>>
>>>-David
>>>
>>>
>>>
>>>_______________________________________________
>>>Dev mailing list
>>>[hidden email]
>>>http://lists.ofbiz.org/mailman/listinfo/dev
>>>
>>>
>>>
>>>      
>>>
>>
>>_______________________________________________
>>Dev mailing list
>>[hidden email]
>>http://lists.ofbiz.org/mailman/listinfo/dev
>>    
>>
>
>_______________________________________________
>Dev mailing list
>[hidden email]
>http://lists.ofbiz.org/mailman/listinfo/dev
>
>  
>

 
_______________________________________________
Dev mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/dev

[Leon Torres-2]

Si Chen wrote:
 > David, et al. -
 >
 > I think the real trick is how the field is intended to be used.  Also,
 > the actual field in the database should be tied directly to the
 > BigDecimal rounding used.  Specifically:
 >
 > 1.  If it's intended as the final amount, then "currency-amount" is
 > good, and BigDecimal rounding should be set to 2 places.
 >
 > 2.  If it's intended as an intermediate value which is to be *added* to
 > derive a final amount, then "currency-precise" should be fine.  That
 > additional one decimal place should do the trick, and BigDecimal
 > rounding should be set to 3 places.
 >

An extra note for fields that have a set number of decimal places (cases 1 and 2):

The value should be rounded immediately after getBigDecimal() is called,
otherwise the value is something imprecise:

// suppose obj.value is 12.99 in database
value = obj.getBigDecimal(); // returns something like 12.989999998763...
value.setScale(decimals, rounding); // round it back to 12.99

Those extra digits can introduce errors when the value is multiplied or divided
later on.

- Leon


 
_______________________________________________
Dev mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/dev

[BJ Freeman]

these were started last year before the accounting
if the new accounting covers these they should be closed.

 > OFBIZ-229
 > OFBIZ-138
 > OFBIZ-154
 > OFBIZ-153
 > OFBIZ-152
 > OFBIZ-150
 > OFBIZ-149
 > OFBIZ-148
 > OFBIZ-147
 > OFBIZ-146
 > OFBIZ-145
 > OFBIZ-144
 > OFBIZ-143
 > OFBIZ-142
 > OFBIZ-141
 > OFBIZ-140
 > OFBIZ-139
 > OFBIZ-137
 > OFBIZ-136
 > OFBIZ-135
 > OFBIZ-134
 > OFBIZ-133
 > OFBIZ-132
 > OFBIZ-131
 > OFBIZ-130
 > OFBIZ-129
 > OFBIZ-128
 > OFBIZ-127
 > OFBIZ-126
 > OFBIZ-125
 >
 
_______________________________________________
Dev mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/dev