Subject | Re: IB6 BUG #221589: numeric fields and mathematical operations |
---|---|

Author | lobolo2000 |

Post date | 2001-08-24T22:05:55Z |

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

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