> And it seems to only occur with a scale higher than 4, from what I
> read in another post. Have to say it looks like a bug in InterBase's
> numeric handling. I assume that if you cast everything as
> NUMERIC(18,4), then everything works properly? i.e.:

Using Guido's second demonstration of the problem I setup a select
like this (Firebird 1 RC2)...

select
money1,
(money1/1.95583) as money1a,
money2,
(money2/1.95583) as money2a,
money3,
(money3/1.95583) as money3a,
CAST( (
CAST(money3 as NUMERIC(18,3)) /
CAST(1.95583 as NUMERIC(18,6))
) AS NUMERIC(18,10) )
as money3b
from new_table

If you play around with variations of the casts it appears that the
problem relates to the scale of the result - although it is not
consistent. That is; regardless of how you cast the result of the
calculation, internally IB/FB still creates a numeric scaled according
to the scale of the values in the calculation.

Playing around with the scales of the calculation values (3 and 6
above) gives very inconsistent results, some work and some dont. But
it appears that combined scales of 7 or less are consistently ok,
whereas combined scales of 8 or more are unreliable - with most
problems existing when the divisor has the higher scale.

Note that even this...
CAST( (
CAST(money3 as NUMERIC(9,3)) /
CAST(1.95583 as NUMERIC(9,6))
) AS NUMERIC(18,10) )

results in a problem whereas this...
CAST( (
CAST(money3 as NUMERIC(9,4)) /
CAST(1.95583 as NUMERIC(9,5))
) AS NUMERIC(18,10) )

does not.


For the moment I am just glad that I have not used scaled numerics in
my apps - I just spend lots of time writing triggers etc to perform
explicit rounding. It may not be perfect, but so far it has been
consistent.


--
Geoff Worboys
Telesis Computing