Claudio,

To start with, you are an IB expert whereas I am one level higher than IB
ignoramus, as I am relatively new to IB. Therefore, you have to take it
easy with my questions which definitely do not fall in the criticism
category as much as they are legitimate questions. I do apologize if any of
my words showed otherwise.

Since the bug complaint mentioned both dialects giving a result of 9, I
tried it myself so that I could understand what was going on and avoid
consequent pitfalls, but with dialect 1, which, according to the bug
complaint, should have given 9. I got instead 8.95. This fact prompted my
question. So I'll just uninstall IB6 and install Firebird 1.0.

On the other hand, if we consider dialect 3 with exact numerics, multiplying
2 numbers results in a number with a decimal precision equal to the sum of
both decimal precisions (AFAIK), so that no precision is lost (ex: 1.12*1.12
gives 1.2544). One would consequently expect that division should try to
conserve precision as much as possible, at least in the intermediate results
if not in the final result. It would be a little tricky for example to note
that if price is numeric(x,2) and percent_discount numeric(x,0), then
price*(1-percent_discount/100) would most probably be equal to price as
10.12*(1-15/100)=10.12*(1-0)=10.12. In order to avoid this pitfall, the 100
should be either cast to double or substituted by 100.00. This is a simple
case. What if there were lots of variables involved in divisions in a
formula?

IMHO, I think that division of exact numerics should be done with the
maximum exact precision, then reduced to the lowest precision that will not
cause any decimal digit to be discarded. It would become much more
consistent with addition, subtraction, and multiplication where no precision
is lost.

I would appreciate your comments.

TIA

----- Original Message -----
Date: Fri, 24 Aug 2001 04:09:37 -0400
From: "Claudio Valderrama C." <cvalde@...>
Subject: Re: IB6 BUG #221589: numeric fields and

lobolo2000 wrote:

> This closed bug relates partially to the following:
> --------------------------------------------------------
> in dialect 1 and 3
> select field1*(1-field2/100) from mytable
> where:
> field1 numeric(9,2) value -> 10
> field2 numeric(9,2) value -> 10.5
>
> the result is: 9 instead of: 8.95
> Possible explanation:
> Since values are numeric(9,2), then
> field2/100 is 0.10 because they are exact numerics.
> Hence, 10*(1-0.10)=10*0.9 = 9 as IB answered.
> -------------------------------------------------------
>
> Is it true that IB6 (and FB) output 9 as result?
> I tried it and got 8.95, and hope that the explanation
> given is not true due to its implication that exact numerics
> are not suitable for most cases.

I do not have any idea how you manage to get 9 as a result in dialect 3:

10 as numeric(9,2) may be written as 10.00
10.5 as numeric(9,2) may be written as 10.50
Hence

IN DIALECT 3:
select 10.00*(1-10.50/100) from rdb$database
=> 9

IN DIALECT 1:
select 10.00*(1-10.50/100) from rdb$database
=> 8.95

So what's the wrong explanation, according to you? Please use a tool where
you can be sure you're using the right dialect. The simplest one is the
command-line isql.exe that comes with the Windows version and that should
come without the ".exe" extension on other platforms. Be sure your db is
dialect 3 and that your preferred tool is connected to it on dialect 3, too.

One problem was that IB6 was performing dialect 1 multiplication on exact
arithmetics, hence you may get same results in both dialects. Of course,
this was wrong, since dialect 1 should emulate IB5 where possible.(This may
be the reason the text you quoted speaks about dialect 1 and 3.) Also,
overflow was not detected, so people realized their data had been spoiled
silently after the disaster had happened. Hence Firebird reverted the
behavior and multiplication in dialect 1 on integer storage (numeric up to
precision 9) will be performed in isc_int64 and casted to integer but if it
doesn't fit, it's converted to double. This trick delivers the same answers
than IB5 and hopefully even in a wider range.

The dialect 3 behavior is not arbitrary. It was taken by Chris Jewell
(former Borland engineer) from the SQL standard. Operations on exact
numerics happen in exact numerics. Whether the standard leaves opened the
possibility of executing intermediate, internal results in floating point
arithmetics I do not know; but if you want to work with more precision, you
have to cast to a numeric type with more scale (and hence with more
precision to make room) or cast to double precision, in whose case you may
end up with typical rounding issues that have plagued IB4 and IB5 with any
numeric value above precision 9.

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







_________________________________________________________
Do You Yahoo!?
Get your free @... address at http://mail.yahoo.com