Re[2]: [Firebird-devel] Computation problem in 1.5RC3

[Gabriel J. <gj...@co...>]

Hi Calin,

Friday, June 27, 2003, 6:30:19 PM, you wrote:

CIP> ----- Original Message ----- 
CIP> From: "Gabriel Juncu"
>> GJ> In the stored procedure where I noticed this behavior, I have 3 local
>> GJ> variables of type numeric(18,4) and I get the above error in a simple
>> GJ> multiply operation:
>>
>> GJ> v2 = 5655555;
>> GJ> v3 = 32900;
>> GJ> v1 = v2 * v3;
>>
>> GJ> What am I doing wrong? Or is it a bug in Firebird?
>>
CIP> Firebird works corectly.
CIP> You are trying to multiply 2 fixed precision numerics together.
CIP> Both have precision 4 so you have 10 digits multiplied with 9 digits
CIP> this results in 19 digits wich is the limit for int64 integer.

CIP> Therefore you have integer overflow.

Calin,
First of all, thank you for your answer!

Well, I don't want to add more to this thread, and I guess that if the
case above is "as designed" I should move this to Firebird-Support, but I
still think that this is a problem.
I understand that it is required some internal scaling and the
multiply operation shoud be done on 128 bit, but in my opinion this
shoud be done internally transparent to user.
I don't think that I should get overflow from 4 decimal digits that I
don't request.
For example, Borland Delphi does this internal scaling for Currency
type (8 byte fixed point 4 decimals).

CIP> If you want those two numbers multiplied then do:

CIP> v1=Cast(cast(v2 as double precision)*cast(v3 as double precision) as
CIP> numeric(18,4);

Well, is not exactly the same thing, double precision has only 15 digits
precision. I use numeric(18,4) for monetary calculations and to store
Delphi currency, an if I pass trough double precision I loose 3
digits.
If Firebird would have a float type simmilar to Extended in Delphi (10
bytes float, so 18 digits in mantissa) then maybe the cast would be
acceptable.
A small example:

712345678.1234 x 123456.5678 = 87943752508278.50886652

Firebird:

CREATE PROCEDURE W_NUMTST
RETURNS (
  n NUMERIC(18,4),
  d double precision)
AS
  declare variable n1 numeric(18,4);
  declare variable n2 numeric(18,4);
  declare variable d1 double precision;
  declare variable d2 double precision;
begin
  n1 = 712345678.1234;
  n2 = 123456.5678;

  d1 = 712345678.1234;
  d2 = 123456.5678;

  n = cast(n1 as double precision) * cast (n2 as double precision);
  d = d1 * d2;
  suspend;
end

select * from w_NUMTST;

Results:
                    N                       D
===================== =======================

  87943752508278.5152       87943752508278.52


In Delphi:

Currency x Currency: 87943752508278.5089 (CORRECT!)
Double x Double: 87943752508278.5156
Extended x Extended: 87943752508278.5089 (CORRECT again)

So, can anybody tell me how can I get correct arithmetic in Firebird
on 18 digits without overflow errors?

Thank you,
Gabriel