DerbyDB truncates the values inserted in a numeric field instead of approximating them

[Jacopo Cappellato <ti...@sastau.it>]

Hi all,

my name is Jacopo Cappellato, I'm one of the developers of the OFBiz 
project (www.ofbiz.org), that will soon start the incubation process.

OFBiz is using DerbyDb as the default db and it works pretty well even 
if we have found some minor issues.

One of these is the way numbers are approximated when inserted in 
numeric fields.

Namely, DerbyDB truncates the values inserted in a numeric field instead 
of approximating them 
(http://db.apache.org/derby/docs/10.0/manuals/reference/sqlj132.html).

Since the general approach in OFBiz is that of using db approximations 
(instead of doing them in the code), this is not a good thing because 
other databases (e.g. MaxDB/SapDB) perform approximations instead of 
truncations in the same situations.

So switching from DerbyDB to another one could lead to different 
calculations.

Is there a way to configure the way approximations are done? Is it 
something that should be fixed?

Thanks,

Jacopo

Re: DerbyDB truncates the values inserted in a numeric field instead of approximating them

[Jacopo Cappellato <ti...@sastau.it>]

Daniel,

Daniel John Debrunner wrote:
> DECIMAL and NUMERIC are the same in Derby. So it looks like you are
> using the correct type. One option is to increase the scale of the
> number (digits after the decimal point), e.g. NUMERIC(20,4) which would
> then hold two extra digits that would be lost in the assignment to
> NUMERIC(18.2). Maybe this is what you meant by approximation, the
> ability to control the rounding as Dag said?
> 

Yes, exactly (and sorry for my bad English).
Right now, we are not using (in general) the NUMERIC(20,4) approach to 
avoid having to deal with amount rounding in the code... just leaving to 
the db this task.
But in order to do this correctly with all the dbs we need to control 
the rounding algorithm of the db. If, as pointed out by Dag, this is not 
covered by the SQL spec, we'll have to cahnge this approach.

Regards,

Jacopo

> Dan.
> 
> [
> The SQL standard allows DECIMAL (I think) to have greater precision than
> defined while NUMERIC must match its defined precision.
> ]
> 
> 
> 
> 
> 

Re: DerbyDB truncates the values inserted in a numeric field instead of approximating them

[Daniel John Debrunner <dj...@apache.org>]

Jacopo Cappellato wrote:
> Daniel, Dag,
> 
> thanks for your answers: they are really helpful.
> 
> Well, right now we are now using NUMERIC(18,2) field types for currency
> amount fields... so that values are stored in the db with the exact
> number of decimal we need (0k, we are actually in the process of
> reviewing this approach).
> 
> Do you think we should use a different field type? (e.g. DECIMAL)

DECIMAL and NUMERIC are the same in Derby. So it looks like you are
using the correct type. One option is to increase the scale of the
number (digits after the decimal point), e.g. NUMERIC(20,4) which would
then hold two extra digits that would be lost in the assignment to
NUMERIC(18.2). Maybe this is what you meant by approximation, the
ability to control the rounding as Dag said?

Dan.

[
The SQL standard allows DECIMAL (I think) to have greater precision than
defined while NUMERIC must match its defined precision.
]

Re: DerbyDB truncates the values inserted in a numeric field instead of approximating them

[Jacopo Cappellato <ti...@sastau.it>]

Daniel, Dag,

thanks for your answers: they are really helpful.

Well, right now we are now using NUMERIC(18,2) field types for currency 
amount fields... so that values are stored in the db with the exact 
number of decimal we need (0k, we are actually in the process of 
reviewing this approach).

Do you think we should use a different field type? (e.g. DECIMAL)

Again, thanks,

Jacopo

Daniel John Debrunner wrote:
> Jacopo Cappellato wrote:
> 
>> Hi all,
>>
>> my name is Jacopo Cappellato, I'm one of the developers of the OFBiz
>> project (www.ofbiz.org), that will soon start the incubation process.
> 
> Welcome, I've watching the vote on the incubator list.
> 
>> OFBiz is using DerbyDb as the default db and it works pretty well even
>> if we have found some minor issues.
> 
> That's great, please inform this list of any issues with Derby as you
> find them.
> 
>> One of these is the way numbers are approximated when inserted in
>> numeric fields.
>>
>> Namely, DerbyDB truncates the values inserted in a numeric field instead
>> of approximating them
>> (http://db.apache.org/derby/docs/10.0/manuals/reference/sqlj132.html).
>>
>> Since the general approach in OFBiz is that of using db approximations
>> (instead of doing them in the code), this is not a good thing because
>> other databases (e.g. MaxDB/SapDB) perform approximations instead of
>> truncations in the same situations.
> 
> DECIMAL are exact numeric types, which means they do not approximate
> values or calculations. They are designed for financial and scientific
> applications where exact calculations are required. You don't want your
> bank approximating the addition of your pay check into your existing
> balance. :-)
> 
> MySQL's DECIMAL implementation has always been wrong and they have
> finally fixed it in version 5.
> 
> Since OFBiz is a business application suite/framework, what sort of
> "ERP, CRM, E-Business / E-Commerce, etc." requires approximations? Seems
> like a set of applications that require exactness.
> 
>> So switching from DerbyDB to another one could lead to different
>> calculations.
> 
> Switching from Derby to databases that handle DECIMAL's incorrectly can
> lead to different calculations. My advice, don't switch to those broken
> databases. :-)
> 
>> Is there a way to configure the way approximations are done? Is it
>> something that should be fixed?
> 
> No it should not be fixed. Note that MySQL has fixed their broken
> DECIMAL implementation in version 5.0. I never understood how people
> could be using MySQL and DECIMAL, I hope no banks were.
> 
> If you want approximate numeric values, then REAL and DOUBLE are the SQL
> datatypes you require.
> 
> Dan.
> 
> 

Re: DerbyDB truncates the values inserted in a numeric field instead of approximating them

[Daniel John Debrunner <dj...@apache.org>]

Jacopo Cappellato wrote:

> Hi all,
> 
> my name is Jacopo Cappellato, I'm one of the developers of the OFBiz
> project (www.ofbiz.org), that will soon start the incubation process.

Welcome, I've watching the vote on the incubator list.

> OFBiz is using DerbyDb as the default db and it works pretty well even
> if we have found some minor issues.

That's great, please inform this list of any issues with Derby as you
find them.

> 
> One of these is the way numbers are approximated when inserted in
> numeric fields.
> 
> Namely, DerbyDB truncates the values inserted in a numeric field instead
> of approximating them
> (http://db.apache.org/derby/docs/10.0/manuals/reference/sqlj132.html).
> 
> Since the general approach in OFBiz is that of using db approximations
> (instead of doing them in the code), this is not a good thing because
> other databases (e.g. MaxDB/SapDB) perform approximations instead of
> truncations in the same situations.

DECIMAL are exact numeric types, which means they do not approximate
values or calculations. They are designed for financial and scientific
applications where exact calculations are required. You don't want your
bank approximating the addition of your pay check into your existing
balance. :-)

MySQL's DECIMAL implementation has always been wrong and they have
finally fixed it in version 5.

Since OFBiz is a business application suite/framework, what sort of
"ERP, CRM, E-Business / E-Commerce, etc." requires approximations? Seems
like a set of applications that require exactness.

> So switching from DerbyDB to another one could lead to different
> calculations.

Switching from Derby to databases that handle DECIMAL's incorrectly can
lead to different calculations. My advice, don't switch to those broken
databases. :-)

> Is there a way to configure the way approximations are done? Is it
> something that should be fixed?

No it should not be fixed. Note that MySQL has fixed their broken
DECIMAL implementation in version 5.0. I never understood how people
could be using MySQL and DECIMAL, I hope no banks were.

If you want approximate numeric values, then REAL and DOUBLE are the SQL
datatypes you require.

Dan.

Re: DerbyDB truncates the values inserted in a numeric field instead of approximating them

["Dag H. Wanvik" <Da...@Sun.COM>]

Hi Jacopo,

>>>>> "Jacopo" == Jacopo Cappellato <ti...@sastau.it> wrote:

Jacopo> Is there a way to configure the way approximations are done? Is it 
Jacopo> something that should be fixed?

I checked the SQL specification and it says that whether rounding or
truncation is used is implementation defined, so Derby is SQL
compliant when it chooses to do truncation. I guess if you want to use
different data base back-ends, your milage might vary in any case, so
it could be advisable to handle this in process in the application
(not assuming anything about how the db handles this).

Dag



Jacopo> 
Jacopo> Hi all,
Jacopo> my name is Jacopo Cappellato, I'm one of the developers of the OFBiz 
Jacopo> project (www.ofbiz.org), that will soon start the incubation process.
Jacopo> 
Jacopo> OFBiz is using DerbyDb as the default db and it works pretty well even 
Jacopo> if we have found some minor issues.
Jacopo> 
Jacopo> One of these is the way numbers are approximated when inserted in 
Jacopo> numeric fields.
Jacopo> 
Jacopo> Namely, DerbyDB truncates the values inserted in a numeric field instead 
Jacopo> of approximating them 
Jacopo> (http://db.apache.org/derby/docs/10.0/manuals/reference/sqlj132.html).
Jacopo> 
Jacopo> Since the general approach in OFBiz is that of using db approximations 
Jacopo> (instead of doing them in the code), this is not a good thing because 
Jacopo> other databases (e.g. MaxDB/SapDB) perform approximations instead of 
Jacopo> truncations in the same situations.
Jacopo> 
Jacopo> So switching from DerbyDB to another one could lead to different 
Jacopo> calculations.
Jacopo> 
Jacopo> Is there a way to configure the way approximations are done? Is it 
Jacopo> something that should be fixed?
Jacopo> 
Jacopo> Thanks,
Jacopo> 
Jacopo> Jacopo
Jacopo> 
-- 
Dag H. Wanvik
Sun Microsystems, Database Technology Group (DBTG)
Haakon VII gt. 7b, N-7485 Trondheim, Norway
Tel: x43496/+47 73842196, Fax:  +47 73842101