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

Author | Helen Borrie |

Post date | 2018-01-26T18:51:53Z |

Salim Naufal wrote:

numbers, especially division. In short, with implicit casting:

- the result of division of an integer by a number of any type is

always integer, with banker's rounding

- the result of fixed numeric/fixed numeric gives FN where the scale

of the decimal part is the sum of the scales of the operands

- the result of FN/floating point or FN/FP gives FP

Hence the reason why these last two examples got closer to the result

you wanted while performing that integer division messed it up. I

suggest you play around with your formula in isql until you get the

precision and scale that you desire in your result. Don't try to use

a GUI tool for such testing as it will probably "tidy up" the output

for you and confuse the outcome.

Note, if you match the scale of your operands throughout, you might

get away with not having to cast anything. Implicit casting casts

non-integer numbers as double precision. Any operands that are

database columns are not cast implicitly - they use the type defined

for them.

Helen

> I have noticed the following inconsistencies in Firebird 3.02:[..]

> I then tried:You really do need to familiarise yourself with the way SQL handles

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

numbers, especially division. In short, with implicit casting:

- the result of division of an integer by a number of any type is

always integer, with banker's rounding

- the result of fixed numeric/fixed numeric gives FN where the scale

of the decimal part is the sum of the scales of the operands

- the result of FN/floating point or FN/FP gives FP

Hence the reason why these last two examples got closer to the result

you wanted while performing that integer division messed it up. I

suggest you play around with your formula in isql until you get the

precision and scale that you desire in your result. Don't try to use

a GUI tool for such testing as it will probably "tidy up" the output

for you and confuse the outcome.

Note, if you match the scale of your operands throughout, you might

get away with not having to cast anything. Implicit casting casts

non-integer numbers as double precision. Any operands that are

database columns are not cast implicitly - they use the type defined

for them.

Helen