On 2018-01-26 16:17, Salim Naufal s.naufal@... [firebird-support]
wrote:

> 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)

Which is the correct and expected result. Contrary to what you are
thinking, there is no floating point involved here.

1 is an exact numeric literal with scale, and 1.11 is **not** a floating
point literal, but an exact numeric literal with scale 2. The rules for
exact numeric division are not specified in the SQL standard, but left
to the implementation. In the case of Firebird, it will follow the same
rules as for multiplication. That is, the precision of the result is 18,
with as scale the sum of the scale of both operands (see
https://www.ibphoenix.com/resources/documents/design/doc_154).

In other words, the scale of the result is 0 + 2 = 2. The applied
division stops when the precision is reached, and the remainder is
'thrown' away; so no rounding is applied.

For 1/1.11, this results in 0.90, and then 1 - 0.90 result in precision
18 with the largest scale of either operands, so scale 2. And the result
is 0.10

> 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

Again, these are exact numerics, but now you have scale 1 and scale 2,
so the result of has scale 3.

> 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

The SQL standard defines that as an exact numeric literal, which is how
Firebird handles it.

> 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

This result has nothing to do with the cast, but with the fact that the
result of 1.00/1.11 has scale 4, so the result is 0.9009.

> 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?

This would have worked with only casting one operand to double precision

However, if you want to use floating point calculations, then you can
simply use an approximate numeric literal (aka floating point literal),
eg 1.11e0

select 1 / 1.11e0 from rdb$database has a double precision result with
value 0.9009009009009008.

Mark