> -----Original Message-----
> From: Corey Wangler [mailto:cwangler@...]
> Sent: Domingo 7 de Enero de 2001 20:28
> >
> > Corey, if you were using IB5.6 previously, you should have
> gotten a numeric
> > overflow in those cases where IB6-dialect1 produces negative results.
> >
> Are you sure about that? One of the cited examples
> was 3500 * 1 which did not even approach overflow,
> simply gave an incorrect result.

Geoff Worboys tested in IB5.6 and IB6. The former complains with "numeric
overflow" whereas the latter reported a negative result. That's a kind of
overflow. Let's not twist the original example, please; it wasn't
simply 3500*1
but the two quantities were casted to numeric(p,s) so when IB multipled
them, p is around 9 as a maximum, but the multiplication causes scale final
to be the sum of the two scales and
10-sum(s1+s2) is not enough to contain the integral part of the result,
hence the problem:
select cast(3500 as numeric(9,2)) * cast(1 as numeric(9,4))
from rdb$database
=> -794,967296
because combined scale is 6, leaving just 4 positions to the integral part,
but the first position is interpreted as the sign, hence you get a negative
value.

I've taken the liberty to quote part of Geoff's private answer:
GW> I think there is a misunderstanding about
GW> where the overflow is occuring.
GW> As we discussed previously it has to do
GW> with scale and precision of the result,
GW> and does not occur with simple integer calculations.

select cast(3500 as numeric(10,2)) * cast(1 as numeric(9,4))
from rdb$database

Anyway, I remember that last year I discovered that when a client dialect 1
does the following operation against a dialect 3 db:
select cast(3500 as numeric(10,2))
from rdb$database
it gets this error:
SQL error code = -817
Metadata update statement is not allowed by the current database SQL dialect
3
I don't know if this should be logged as a bug, too, since I would expect
that the client dialect 1 gets this quantity handled internally as double
and not as int64, but it may be by design.

> Our app runs fine with IB 5.6, but gives strange data
> on IB6.01 in line with this problem (we have numeric
> field operations in some select statements).

You are hitting those cases when IB can't find enough space to put the
decimal and integral part. I would discard the decimal places in my case to
get enough space for the most important part (digits at the left of the
decimal point), but this idea could be worse than the current behavior.

> > I think dialect 1 should conform to IB5.6 behavior as closely
> as possible
> > and also, it can't use INT64. Please, let's discuss this issue in
> > IB-Architect, as we are going further than a simple patch. Mark
> has already
> > raised the priority of the bug solution.
> >
> > C.
> >
> I can't see a problem in promoting to INT64 for the
> internal calculations. As long as the correct result
> is returned -- i.e. a cast to the assumed result type,
> with overflow checking on the conversion.

What happens is that by the current rules, clients in dialect 1 are
forbidden to access any field that's stored as INT64, being generators the
only exception to my knowledge. I don't know if it's desirable to change
this aspect.

> If we need to emulate the errors in 5.6's calculations
> (would there be any differences? e.g. rounding errors?) by
> using double precision, that is of a lower priority to me
> than actually getting our app to work.

It seems understandable from the point of view of the applications
programmer. I would like to read opinions about what this means at the level
of code or the design goals for IB6.

C.