Subject Miscalculation of floating points in Firebird Salim Naufal 2018-01-26T15:17:12Z
I have noticed the following inconsistencies in Firebird 3.02:

Try performing the following calculations:

select (1 - (1/1.11)) FROM RDB\$DATABASE    result is rounded and yields 0.10 (rounded result)

to rule out any results being incorrectly displayed, I tried:

SELECT 10000 * (select (1 - (1/1.11)) FROM RDB\$DATABASE) FROM RDB\$DATABASE  and got 1000  (another rounded result)

thinking that it could be an interpreter issue, I tried the following hoping to force the interpreter to use floating points:

select (1.0 - (1.0/1.11)) FROM RDB\$DATABASE and got 0.100 which is an incorrect rounded number

The rounding in this case is quite small but examine the following real case.

Company A has priced an item at 10,000 EUR (VAT inclusive) and wants to calculate the VAT amount (VAT is 11 percent). The VAT Inclusive amount is stored in the database in the NUMERIC(18,2) type.

This can be done using the following: VAT = Amount * (1 - (1/1.11))  = 990.99

to calculate all VAT amounts in the database, I execute a similar function:

SELECT CAST(10000 AS NUMERIC(18,2)) * (1 - (1/1.11)) FROM RDB\$DATABASE      Firebird returns the result of 1,000 which is a rounded result.

I therefore tried:
SELECT CAST(10000 AS NUMERIC(18,2)) * (CAST(1 AS DOUBLE PRECISION) - (1/1.11)) FROM RDB\$DATABASE and got the same rounded result.

SELECT CAST(10000 AS NUMERIC(18,2)) * (1.0 - (1.0/1.11)) FROM RDB\$DATABASE  hoping that the procedure parser in Firebird 3.02 will treat the 1.0 as Float
I still got the result of 1,000 which is rounded

I then tried:
SELECT CAST(10000 AS NUMERIC(18,2)) * (1.00 - (1.00/1.11)) FROM RDB\$DATABASE and got a much better result of 991.00000

Finally, the correct result is retrieved using:

SELECT CAST(10000 AS NUMERIC(18,2)) * (CAST(1 AS DOUBLE PRECISION) - (CAST(1 AS DOUBLE PRECISION)/1.11)) FROM RDB\$DATABASE

I am not familiar with the details of the SQL standard, is this the proper was to round and calculate numeric values in SQL?

Regards