Re: [Firebird-devel] bug in computation of numeric datatype

[Daniel R. <da...@ac...>]

Hi,

At July 31, 2003, 14:20, Leyne, Sean wrote:
>> There are clear rules for math outside SQL: having N decimal places
>> in one side and M in other side, you will get N+M decimal places.
>> Just use your calculator!

> In the case of my desk top calculator, 1.0005 x 20.0000 = 20.01!  ;-)

The actual result would be: 20.01000000. But the calculator
truncates(or trims off) the trailing zeros from the 20.0000 value to
make it 20(this is so the math processor doesn't have to do extra work
for nothing), and then again from the result if it is required.

> I must also be said that the standard does not provide the appropriate
> guidance when N+M decimal places is beyond the limit of the datatype or
> any datatype.  Using that logic you would **never** be able to multiply
> any two value of type NUMERIC( 18, 10).

Unless, we trim the trailing zeros that appear after the decimal
before performing the calculations.  Then it might be possible to
multiply those values, still depending on the precision on either side
of the decimal.

> If I remember my high school/college science classes, wasn't there a
> rule that the result of a math function was only as accurate as the most
> precise value.  I.e. 1.000001 x 2.04 = 2.04002, whereas the calculator
> shows the value as 2.0400204

I think the rule that you are referring to applies to additions and
subtractions.  Your calculator is right.

>> From a strict point of view, if user knows that w + s is
>> greater than 19, then the user should cast one of the or both
>> operands:
>> 
>> cast(A as numric(x, n - t)) * cast(B as numeric(y, m - u))

Let's say value A is NUMERIC(18,10) and B is NUMERIC(18,10).  What
happens in the following: CAST((A*B) AS NUMERIC(18,9))?  Should it
throw an exception because of A*B has a combined scale of 20?
According to the standard, it shouldn't as long as the result of the
multiplication can be represented in the resulting data type.

In Example, still using the same data types as above:
A=10
B=10.2145
A*B=102.145    ->  Firebird thows an exception
CAST((A*B) AS NUMERIC(18,10))=102.145  -> Firebird throws an exception

But, if following the SQL standard, it shouldn't give an exception
since 102.145 can easily be represented with NUMERIC(18,10).

> It is the result which should be less precise, not the factor which go
> into the calculation.  Consider the following NUMERIC( 18, 9) values

> A = 100.000000099
> B =   2.123456789

> The real answer of A x B is 212.34567909111111101 (My "ideal" answer
> would be 212.345679091)

Even, if casting to NUMERIC(18,9) or assigning to a variable of type
NUMERIC(18,9), an exception should occur(according to SQL standards).
Unless, you would be using the rounding or truncate SQL functions to
eliminate the digits past the 9th decimal, then the resulting value is
NUMERIC(18,9) and should be accepted.

-- 
Best regards,
 Daniel Rail
 Senior System Engineer
 ACCRA Group Inc. (www.accra.ca)
 ACCRA Med Software Inc. (www.filopto.com)