Subject Re: [IB-Architect] [ Bug #121589 ] numeric fields and mathematical ops
Author Corey Wangler
Hi Claudio,

Thanks for clearing this up.

After your in-depth explanation of Bug #121589,
I agree with all of your comments... the fix
should follow what IB5.6 does and raise an error
when it hits an overflow condition for the
resulting type (which is already the same type
as that returned by IB5.6, if I understand

I don't seen any overflow errors when running our
problem code against IB5.6, so obviously I'm looking
at a different bug. The mathematical operations we are
doing are on summed fields. I'll investigate further
and report accordingly once I get my facts straight.

Sorry for jumping the gun.

Best regards,

"Claudio Valderrama C." wrote:
> > -----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.
> To unsubscribe from this group, send an email to: