Subject | Re: [firebird-support] Miscalculation of floating points in Firebird |
---|---|

Author | Mark Rotteveel |

Post date | 2018-01-26T20:36:41Z |

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

wrote:

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

so the result of has scale 3.

Firebird handles it.

result of 1.00/1.11 has scale 4, so the result is 0.9009.

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

wrote:

> I have noticed the following inconsistencies in Firebird 3.02:Which is the correct and expected result. Contrary to what you are

>

> Try performing the following calculations:

>

> SELECT (1 - (1/1.11)) FROM RDB$DATABASE result is rounded and

> yields 0.10 (rounded result)

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:Again, these are exact numerics, but now you have scale 1 and scale 2,

>

> 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

so the result of has scale 3.

> The rounding in this case is quite small but examine the followingThe SQL standard defines that as an exact numeric literal, which is how

> 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

Firebird handles it.

> I still got the result of 1,000 which is roundedThis result has nothing to do with the cast, but with the fact that the

>

> 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

result of 1.00/1.11 has scale 4, so the result is 0.9009.

> Finally, the correct result is retrieved using:This would have worked with only casting one operand to double precision

>

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

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