Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Received-SPF: neutral (asf.osuosl.org: local policy)
Message-ID: <43E36FD8.8040204@apache.org>
Date: Fri, 03 Feb 2006 06:59:36 -0800
From: Daniel John Debrunner <djd@apache.org>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
 rv:1.7.3) Gecko/20040910
MIME-Version: 1.0
To: derby-dev@db.apache.org
Subject: Re: DerbyDB truncates the values inserted in a numeric field instead
 of approximating them
References: <43E33070.6000805@sastau.it>
In-Reply-To: <43E33070.6000805@sastau.it>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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.