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