Subject | Re: [ib-support] IB6 BUG #221589: numeric fields and |
---|---|

Author | Claudio Valderrama C. |

Post date | 2001-08-24T08:09:37Z |

lobolo2000 wrote:

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

> This closed bug relates partially to the following:I do not have any idea how you manage to get 9 as a result in dialect 3:

> --------------------------------------------------------

> 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.

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