<guido.klapperich@...> wrote in message
news:3C6C2500.89BC4AC6@......

> I have a following test-table
> CREATE TABLE NEW_TABLE (
> MONEY1 NUMERIC(18,4),
> MONEY2 NUMERIC(9,4),
> MONEY3 NUMERIC(18,5));
>
> INSERT INTO NEW_TABLE (MONEY1,MONEY2,MONEY3) VALUES
> (-70000,-70000,-70000);
>
> Now the query:
> select money1,money1/1.95583,
> money2,money2/1.95583,
> money3,money3/1.95583
> from new_table
>
> and the result:
> MONEY1 F_1 MONEY2 F_2 MONEY3
> F_3
> -70000 -35.790,431684 -70.000,000000 -35.790,431684 -70.000,000000
> 1.936,250234
>
> When the field is Numeric(18,5), then IB calculates wrong. Exists
> therefore a logical solution or is it a bug ?
>
>
> Guido

ROTFL - ROTFL - ROTFL (can't avoid it, sorry).
If you read the sources inside JRD, you will see a big explanation on how
multiplication and division is performed in dialect 3, theory, formulas and
the method to detect overflow, etc. There's one big comment for multiply2
and other for divide2. Oh, yes, theory's nice, they say.

The person with the famous name (A. Nevsky <g>) is right in that I only
touched the dialect 1 versions to enforce compatibility with dialect 1,
avoid strange overflows that didn't happened in IB5 (since dialect 1 is
supposed to be an emulation of IB5) and detect overflow, if it happens. This
was done a year ago, maybe. Two months ago, Borland posted their own change
and then I discovered that I solved only a puntual case and added a few
lines to handle the general case. Still, the solutions are slightly
different.

Now, on dialect 3, I hope that the engineer that wrote those ops (Chris
Jewell) didn't asked the mathematician <g> or I would ROTFL twice... after
all, we are humans and we all fall due to oversights, otherwise SW would be
perfect.

Dialect 3 has to scale the results by multiplying by 10 to get in an integer
the whole number, including all decimal places. For division, the divisor is
retrieved first so we can yell immediatey if it's zero (we still don't solve
the division by zero in Firebird, sorry <vbg>).Then the dividend is
retrieved. The operation should have as many decimals as the sum of both
operands' decimal places.

First, if we want to be accurate, the limit for negative numbers is one more
in absolute value than the limit for positive values in typical
two-complement architectures. One place in the code checks for this, the
other doesn't.

Second, if you are in a loop checking for possible overflow in the next
scaling by 10, it's highly unlikely that you will succeed in detecting such
possible overflow if you have a negative quantity (Guido's example) to be
scaled by 10 BUT the limit to be compared against is always a positive
value, LOL! Seems a joke, but I'm reading the code. Is there a place
when -70 multiplied by 10 and 10 and so on is smaller than a big positive
int? Yes, when it's going to overflow for the second time, but that's not
what you want, of course.

I could try to solve those two issues, but FB1 is carved in stone, the
destiny is already written since we were born and the wish of the FB admins
is sacred for me, so, Guido, enjoy the bug and use your calculator more
often and rely on good old listings. I just tried this to test my theory in
practice:

SQL> create view vsol(d) as select -((-money3)/1.95583) from new_table;
SQL> select * from vsol;

D
=====================

-35790.4316837350

SQL> ^Z

As you can see, it gives the right result, but you cannot use the example as
a solution, because you then will stuff the positive values instead.

But wait, don't leave our bug show too soon, there're more cases with int64:

SQL> select (-4611686018427387904)/-0.5 from rdb$database;


=====================

0.0

I'm sure some mathematic society will be interested in this result.
;-)

And I even discovered that if I don't fix also the the second place where
the first check is validated, the result could become 8.0 instead of zero.

C.
--
Claudio Valderrama C. - http://www.cvalde.com - http://www.firebirdSql.org
Independent developer
Owner of the Interbase® WebRing