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

Author | Mark Rotteveel |

Post date | 2018-01-26T20:55:58Z |

On 2018-01-26 19:51, Helen Borrie helebor@...

[firebird-support] wrote:

by a numeric with scale, the result is a numeric with scale, if by a

floating point, then the result is a floating point.

And there is no bankers rounding ('half even' rounding) involved with

exact numeric division, it uses rounding by truncation; it stops when

the target scale is reached, and performs no rounding. Compare 1 / 8.00

(= 0.12) and 3 / 8.00 (= 0.37), if bankers rounding would be used, the

second result would have been 0.38.

scale 0).

about division in dialect 1), but the results of calculations are the

same whether you use columns of a given type, or literals of the same

type. The only influence you can apply is using explicit casts, or

changing the precision or type of a literal.

The literals in Firebird are as defined in the SQL standard, so for

exact numerics:

<exact numeric literal> ::=

<unsigned integer> [ <period> [ <unsigned integer> ] ]

| <period> <unsigned integer>

So, no digits after the decimal period (or no decimal period) is an

exact numeric with scale 0 (an 'integer', or better a decimal(18,0)), n

digits after the decimal period means scale n (a decimal(18,n).

and for floating point:

<approximate numeric literal> ::=

<mantissa> E <exponent>

<mantissa> ::=

<exact numeric literal>

Mark

[firebird-support] wrote:

>> I am not familiar with the details of the SQL standard, is this theA division of an integer by any number is not always integer: if divided

>> proper was to round and calculate numeric values in SQL?

>

> You really do need to familiarise yourself with the way SQL handles

> 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

by a numeric with scale, the result is a numeric with scale, if by a

floating point, then the result is a floating point.

And there is no bankers rounding ('half even' rounding) involved with

exact numeric division, it uses rounding by truncation; it stops when

the target scale is reached, and performs no rounding. Compare 1 / 8.00

(= 0.12) and 3 / 8.00 (= 0.37), if bankers rounding would be used, the

second result would have been 0.38.

> - the result of fixed numeric/fixed numeric gives FN where the scaleNote that the same rules apply for integers (where integers simply have

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

scale 0).

> - the result of FN/floating point or FN/FP gives FPI'm not sure what you mean with implicit casting here (unless you talk

>

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

about division in dialect 1), but the results of calculations are the

same whether you use columns of a given type, or literals of the same

type. The only influence you can apply is using explicit casts, or

changing the precision or type of a literal.

The literals in Firebird are as defined in the SQL standard, so for

exact numerics:

<exact numeric literal> ::=

<unsigned integer> [ <period> [ <unsigned integer> ] ]

| <period> <unsigned integer>

So, no digits after the decimal period (or no decimal period) is an

exact numeric with scale 0 (an 'integer', or better a decimal(18,0)), n

digits after the decimal period means scale n (a decimal(18,n).

and for floating point:

<approximate numeric literal> ::=

<mantissa> E <exponent>

<mantissa> ::=

<exact numeric literal>

Mark