(answering here, although the question originally was asked at firebird-general):

I think 13999,20 is the correct result! 20/120 should return 0,1666 if the precision is DECIMAL(18, 4) (which is the precision when dividing two DECIMAL (18, 2) values), and 12000*1,1666 = 13999,20.

We all do mistakes, and it is very simple to assume that it is only the final result that has to fit a certain format. I expect clients to be able to understand that intermediate results also has to fit into a certain precision, but I can understand it to be a harder to explain why, and impossible to avoid similar errors in the future.

HTH,
Set

-----Original Message-----
From: Firebird-general@yahoogroups.com [mailto:Firebird-general@yahoogroups.com] On Behalf Of Antonio Carlos Ribeiro
Sent: 21. februar 2008 21:13
To: Firebird-general@yahoogroups.com
Subject: [Firebird-general] Precision

I'm using Firebird 1.54.

What's wrong with those calculations?:

This one should return 14000 but returns 13999,20:

select
cast( 12000 as DECIMAL( 18, 2 ) )

*
(
cast( 20 as DECIMAL( 18, 2 ) )
/ ( cast( 20 as DECIMAL( 18, 2 ) )
+ 100
)
+ cast( 1 as DECIMAL( 18, 2 ) )
)

from rdb$database

Converting to double precision, with floor, fixes it:

select
cast( 12000 as DECIMAL( 18, 2 ) )

*
( floor(1) *
cast( 20 as DECIMAL( 18, 2 ) )
/ ( cast( 20 as DECIMAL( 18, 2 ) )
+ 100
)
+ cast( 1 as DECIMAL( 18, 2 ) )
)

from rdb$database --> returns 14000

-----------------------

In worst case we get an "Integer overflow" error:

select
cast( 1 as double precision )
*
( ( cast( 12000 as DECIMAL( 18, 5 ) )
* cast( 1 as DECIMAL( 18, 4 ) )
)
* ( cast( 20 as DECIMAL( 18, 4 ) )
/ ( cast( 20 as DECIMAL( 18, 4 ) )
+ 100
)
)
)
from rdb$database

- Changing 12000 to 120 'fixes' the error, but we still don't get the
right calculation that should be 20 but returns 19,9999992:

select
cast( 1 as double precision )
*
( ( cast( 120 as DECIMAL( 18, 5 ) )
* cast( 1 as DECIMAL( 18, 4 ) )
)
* ( cast( 20 as DECIMAL( 18, 4 ) )
/ ( cast( 20 as DECIMAL( 18, 4 ) )
+ 100
)
)
)
from rdb$database


Anyone could explain this behaviour?

Is there a way to protect the database against that, without changing
data types of all fields?

We have many clients, that write their own queries, and it's not
simple to explain and ask them to remember that they have to convert
every calculation to double precision.

Thank you!

Antonio Carlos