You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Matthias Ohlemeyer <ma...@web.de> on 2006/03/20 20:55:47 UTC
Bug in division operator with numeric operands?
Hi,
I've been trying to port a relatively complex application from Oracle to
Derby for quite a while now, but it seems like I've hit a road block
now.
Can somebody explain the following behaviour? I copied the following
input-output sequence from ij:
CREATE TABLE t (d1 DOUBLE, d2 DOUBLE, n1 NUMERIC(31,11), n2
NUMERIC(31,11));
INSERT INTO t VALUES (1.5, 2.5, 1.5, 2.5);
SELECT d1/d2, n1/n2, n1*(1.0/n2) FROM t;
1 |2 |3
----------------------------------------------------------------
0.6 |0 |0.600000000000000000000000000000
1 row selected
All three result columns should represent 1.5/2.5=0.6.
Column 1 has the result I expected, but
a) why is the result of column 2 zero? The source columns contain the
same values as for column1, only represented as NUMERIC(31,11) instead
of DOUBLE.
b) why does column 3 show the right result? It is numerically equivalent
to column 2: n1/n2 = n1*(1.0/n2) (?!)
BTW: If I replace one of the column names with a constant I always get
correct results.
There is no way for me to switch from NUMERIC to DOUBLE because I need
the exact precision calculation.
Any hint is appreciated! Could this be a bug?
Matthias
Re: Compiere on Derby (WAS: Bug in division operator with numeric
operands?)
Posted by "Jean T. Anderson" <jt...@bristowhill.com>.
Matthias Ohlemeyer wrote:
> ...
> One other remark: There was news that Compiere would do a Derby port on
> their own and that there is an agreement with some Derby developers to
> help them. This came after I was half way through. So far I have not
> seen any activity on this subject on any of the Derby lists. Is there
> some ongoing work I am not aware of?
> Quote: ""We're working together with the Derby development team and
> we'll provide a complete open source solution based on Derby and
> Cloudscape in six to nine months," said Jorg Janke, the company's
> founder and president, speaking at the Solutions Linux conference in
> Paris"
>
> See: http://www.networkworld.com/news/2006/013106-compiere.html
I don't recall anything about Compiere and don't spot anything in the
Derby mail archives -- it's possible it slipped through the cracks of my
email in-box. Does this ring a bell with anyone else? --It's always
possible the ERP app was mentioned in a post without mentioning Compiere
itself.
At any rate, Derby welcomes any integration effort, so I guess the best
suggestion is to point anyone with questions or issues to
http://db.apache.org/derby/derby_mail.html .
Matthias, thanks for taking the time to provide feedback.
regards,
-jean
Compiere on Derby (WAS: Bug in division operator with numeric
operands?)
Posted by Matthias Ohlemeyer <ma...@web.de>.
Am Montag, den 20.03.2006, 13:18 -0800 schrieb Satheesh Bandaram:
> Hi Matthias,
>
> Matthias Ohlemeyer wrote:
> > Hi,
> >
> > I've been trying to port a relatively complex application from Oracle to
> > Derby for quite a while now, but it seems like I've hit a road block
> > now.
> >
> > Can somebody explain the following behaviour? I copied the following
> > input-output sequence from ij:
> >
> I can help, if you promise to share our experience of porting your
> application from Oracle to Derby. :-)
Most certainly! I'm glad to share my experiences - I'm not so sure you
will like what I think I found or how I judge my findings.
Quite a while back I thought that it would be really neat to have an
open source ERP package with an embedded open source database: Compiere
on Derby.I took the approach of using the Compiere Database Kit to
create a new port. This means that the Oracle-centric SQL-statements
spread throughout the application remain unchanged in the code and a
conversion algorithm has to mangle the statements to create SQL that is
syntactically correct for the target db and semantically equivalent to
the original statement. At first I created a some suitable conversion
methods, then I realised (much too late!) that the approach would never
work in all situations, so that I ended up with over 100 patches to the
original Compiere code. In many of these cases one could argue if it is
a result of a Derby "limitation" or a result of Compiere's
Oracle-centric SQL-usage - I'll let others decide.
Fact is, that I have Compiere Rel. 2.5.3b up and running - untested and
with some known and most certainly many unknown limitations.
Then I hit the error that sparked this thread. One might understand, why
my assessment of the question if Derby is ready to run an ERP
application has changed since then.
It was my intention to create a Sourceforge project for the code, right
now I'm not sure if this is the right thing to do, because I know, that
some financial calculations will be wrong. (Actually I'm not quite ready
to post the code because I would have to clean up a bit and would have
to mark my changes - this is quite some work which just now I'm not
ready to invest.)
Maybe this sounds too negative, but I guess that's how it goes when you
think you are really close and have to give up. There are certainly many
things to like about Derby and it is absolutely not my intention to
criticise this open source project. I hear the OS mantra "It's all about
scratching your own itch" - only this time I can't, because it does not
fit my goals. When I started out, I hoped to create an easy to install,
ERP package, that could be used in POS situations, i.e. I wanted to
concentrate on the business side of things, not on the technical side.
> What kind of issues you faced and how easy or difficult was it etc...
This would be part of one ore more different threads, wouldn't it? As I
said: There is nothing to hide and I'll gladly share my thoughts (though
time restrictions will probably make subsequent posts more concise!).
Waiting for suggestions on how to proceed (in case you are interested at
all and have not fallen asleep).
One other remark: There was news that Compiere would do a Derby port on
their own and that there is an agreement with some Derby developers to
help them. This came after I was half way through. So far I have not
seen any activity on this subject on any of the Derby lists. Is there
some ongoing work I am not aware of?
Quote: ""We're working together with the Derby development team and
we'll provide a complete open source solution based on Derby and
Cloudscape in six to nine months," said Jorg Janke, the company's
founder and president, speaking at the Solutions Linux conference in
Paris"
See: http://www.networkworld.com/news/2006/013106-compiere.html
Just a side note: There are other projects out there with similar goals
but different approaches, e.g. http://www.knowledgesuccess.com (Compiere
on hsqldb; they seem to have the policy to rather change the database
behaviour before patching Compiere and seem to make some good progress.
Another one is CODAF http://www.daffodildb.com/daffodil-compiere.html
(They seem to be ready for production, but not completely open source;
maybe this has changed.)
So long
Matthias
> > CREATE TABLE t (d1 DOUBLE, d2 DOUBLE, n1 NUMERIC(31,11), n2
> > NUMERIC(31,11));
> > INSERT INTO t VALUES (1.5, 2.5, 1.5, 2.5);
> > SELECT d1/d2, n1/n2, n1*(1.0/n2) FROM t;
> >
> > 1 |2 |3
> >
> > ----------------------------------------------------------------
> > 0.6 |0 |0.600000000000000000000000000000
> >
> > 1 row selected
> >
> This seems like a simple bug, at the first look. If you look at
> NumericTypeCompiler code, which calculates the scale and precision of
> operation result type, the comments and the code doesn't seem to
> match. (getScale() method) I would suggest filing a bug in JIRA.
>
> I can provide more help in resolving the issue.
>
> Satheesh
>
> NumericTypeCompiler.java
>
> else if (TypeCompiler.DIVIDE_OP.equals(operator))
> {
> /*
> ** Take max left scale + right precision - right scale +
> 1,
> ** or 4, whichever is biggest
> */
> LanguageConnectionContext lcc =
> (LanguageConnectionContext)
>
> (ContextService.getContext(LanguageConnectionContext.CONTEXT_ID));
>
> // Scale: 31 - left precision + left scale - right scale
> val = Math.max(NumberDataValue.MAX_DECIMAL_PRECISION_SCALE
> - lprec + lscale - rscale, 0);
> }
>
> Here val is returning zero for scale, it seems.
>
Re: Bug in division operator with numeric operands?
Posted by Matthias Ohlemeyer <ma...@web.de>.
Hi Satheesh,
thanks for all your input! I'm still figuring out the answers to your
first post, but i think I might as well start here:
Although you are right for the current choice of values (operand 1: 1.5,
operand 2: 2.5), the situation does not improve for other operands, e.g.
replace 1.5 with 7 and 2.5 with 17. The SELECT will yield a value of
0.41176... in column1, a value of exactly 0.4 in column 2 (which is
plain wrong!) and about the same value as in column 1 in column 3.
Matthias
Am Montag, den 20.03.2006, 14:23 -0800 schrieb Satheesh Bandaram:
> Also note your script would work if you had used a precision of 30,
> instead of 31.
>
> ij> CREATE TABLE t (d1 DOUBLE, d2 DOUBLE, n1 NUMERIC(30,11), n2
> NUMERIC(30,11));
> 0 rows inserted/updated/deleted
> ij> INSERT INTO t VALUES (1.5, 2.5, 1.5, 2.5);
> 1 row inserted/updated/deleted
> ij> SELECT d1/d2, n1/n2, n1*(1.0/n2) FROM t;
> 1 |2 |3
>
> --------------------------------------------------------------------------------
> 0.6 |0.6 |
> 0.60000000000000000000
>
> 1 row selected
>
> Satheesh
>
> Satheesh Bandaram wrote:
> > Hi Matthias,
> >
> > Matthias Ohlemeyer wrote:
> > > Hi,
> > >
> > > I've been trying to port a relatively complex application from Oracle to
> > > Derby for quite a while now, but it seems like I've hit a road block
> > > now.
> > >
> > > Can somebody explain the following behaviour? I copied the following
> > > input-output sequence from ij:
> > >
> > I can help, if you promise to share our experience of porting your
> > application from Oracle to Derby. :-)
> > What kind of issues you faced and how easy or difficult was it
> > etc...
> > > CREATE TABLE t (d1 DOUBLE, d2 DOUBLE, n1 NUMERIC(31,11), n2
> > > NUMERIC(31,11));
> > > INSERT INTO t VALUES (1.5, 2.5, 1.5, 2.5);
> > > SELECT d1/d2, n1/n2, n1*(1.0/n2) FROM t;
> > >
> > > 1 |2 |3
> > >
> > > ----------------------------------------------------------------
> > > 0.6 |0 |0.600000000000000000000000000000
> > >
> > > 1 row selected
> > >
> > This seems like a simple bug, at the first look. If you look at
> > NumericTypeCompiler code, which calculates the scale and precision
> > of operation result type, the comments and the code doesn't seem to
> > match. (getScale() method) I would suggest filing a bug in JIRA.
> >
> > I can provide more help in resolving the issue.
> >
> > Satheesh
> >
> > NumericTypeCompiler.java
> >
> > else if (TypeCompiler.DIVIDE_OP.equals(operator))
> > {
> > /*
> > ** Take max left scale + right precision - right scale +
> > 1,
> > ** or 4, whichever is biggest
> > */
> > LanguageConnectionContext lcc =
> > (LanguageConnectionContext)
> >
> > (ContextService.getContext(LanguageConnectionContext.CONTEXT_ID));
> >
> > // Scale: 31 - left precision + left scale - right scale
> > val =
> > Math.max(NumberDataValue.MAX_DECIMAL_PRECISION_SCALE - lprec +
> > lscale - rscale, 0);
> > }
> >
> > Here val is returning zero for scale, it seems.
> >