On Wednesday 2002-02-13 at 14:02:58 +1100, Helen Borrie wrote:

> At 02:28 AM 14-02-02 +0100, Guido Kapperich wrote:
> >I have the following query
> >select to1valuens as DM,(to1valuens/1.95583) as EURO
> >from TURNOVER_CS_MP_MONTH
> >where to1csid=6 and to1paid=1 and to1mpid=2
> >
> >and the result
> >DM EURO
> >
> > 149.115,080000 76.241,329768
> > -77.374,130000 -1.834,082566
> > 80.532,730000 41.175,731020
> > 205.586,430000 105.114,672543
> > 284.087,030000 145.251,391992
> > 711.952,750000 364.015,660870
> > 212.314,730000 108.554,797707
> > 576.549,930000 294.785,298313
> > 404.336,820000 206.734,133335
> > 327.176,500000 167.282,688168
> > 288.400,760000 147.456,967119
> >-190.479,560000 -3.073,947768
> >
> >As you can see, in the second and last row the result is wrong.
> >Now I change the query a little bit
> >select to1valuens as DM,(to1valuens/1.9558) as EURO
> >from TURNOVER_CS_MP_MONTH
> >where to1csid=6 and to1paid=1 and to1mpid=2
> >
> >I changed only the dividing value from 1.95583 to 1.9558 and I get the
> >result
> >
> >DM EURO
> >
> > 149.115,080000 76.242,499233
> > -77.374,130000 -39.561,371306
> > 80.532,730000 41.176,362614
> > 205.586,430000 105.116,284896
> > 284.087,030000 145.253,620002
> > 711.952,750000 364.021,244504
> > 212.314,730000 108.556,462829
> > 576.549,930000 294.789,820022
> > 404.336,820000 206.737,304428
> > 327.176,500000 167.285,254116
> > 288.400,760000 147.459,228960
> >-190.479,560000 -97.392,146436
> >
> >Now IB calculates right. TO1VALUENS has the type Numeric(18,5).
> >Can anybody explain this to me. It has taken me 4 hours to find out,
> >that IB calculates sometimes wrong :-(
>
> IB is getting it "wrong" because you give it no choice. You are
> "using up" 5 digits for scale, leaving 11 maximum for precision.

That doesn't seem to be the case. First, the scale used doesn't affect
the precision, only the absolute range of permissible values. Second,
InterBase obviously _did_ have a choice, since only two out of twelve
calculations were incorrect (the ones with a negative input value),
and the ones it got incorrect were no different from the others in
terms of precision and scale.

> By applying a division with a scale of 5, your result will be
> numeric(8,10)

NUMERIC(8,10) is an impossibility, since one can't have a scale of ten
when there are only eight digits to play with.

If I understand reasonably accurately, which might not be the case,
since I'm only Joe Average as far as this stuff goes, InterBase will
be trying to do something like:

NUMERIC(18,5) / NUMERIC(18,6) = NUMERIC(18,11)

This leaves seven digits to the left of the decimal point, which
should be enough for those calculations shown above. Obviously it was
for 10 out of 12 cases. It appears likely that the signed numbers
threw the system out.


I have to admit this behaviour is very puzzling.


David Trudgett