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