db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jacopo Cappellato <...@sastau.it>
Subject Re: DerbyDB truncates the values inserted in a numeric field instead of approximating them
Date Fri, 03 Feb 2006 15:12:51 GMT
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.
> 
> 


Mime
View raw message