ROUND() bug

[TJCCKM]

Round() is broken for FLOAT and DOUBLE PRECISION
Tested with 3.10 on Win64 only
Updated #1772 (very old) but not sure the issue is exactly the same

select round(cast(4.015 as DOUBLE PRECISION) , 2) from RDB$DATABASE;
ROUND
=============
4.010000000000000

Expected: 4.02

select round(cast(4.015 as FLOAT) , 2) from RDB$DATABASE;
ROUND
=======
4.0100002

Expected: 4.02

[hvlad]

Already answered at #1772

[TJCCKM]

Pasting hvlad comment from #1772 here, then my own notes,
I am not convinced #1772 is what my issues was...

hvlad had said:
4.015 can't be exactly represented as double precision.
It is represented as 4.0149999999999997.
This number is correctly rounded to the 4.01
The same for float representation.

You may check it with many tools, for example.
https://www.binaryconvert.com/result_double.html?decimal=052046048049053
Thanks... I had already did that, but not comprehending reality.

And my notes:

The example was simplification, the actual issue is with calculations...
Example: select round(4015 * 0.100)

I reread the IEEE double definition and realized it says doubles are
accurate to 15/16 digits... but goes on to say that only means converting
to/from a string will yield the same value.

So for rounding to work the way I was thinking FB would have to
convert the double argument to a string, and use the digits in the
string for the rounding, and things just don't work that way.

You'd think after 40 years at this I would have remembered how this works
In any case, example pasted below in case someone else confused comes along

create table t (dbl DOUBLE PRECISION, pcnt DOUBLE PRECISION);
commit
insert into t (dbl, pcnt) values (4015, 0.001);
select 
    t.DBL, 
    t.pcnt, 
    t.dbl * t.pcnt                   as mult, -- red herring, converted back to string for display so looks right
    round(t.dbl * t.pcnt,2)          as rnd1, -- was trying to do this, answer is 'wrong'
    round(round(t.dbl * t.pcnt,4),2) as rnd2  -- This get me what I want
from t;

DBL               PCNT                 MULT              RND1              RND2
================= ==================== ================= ================= =======================
4015.000000000000 0.001000000000000000 4.015000000000000 4.010000000000000 4.020000000000000

[EPluribusUnum]

Is round an UDF?

Also might be related to
#6928
#6980

[AlexPeshkoff]

If you need exact decimal calculations you should use (starting with FB4) DECFLOAT instead DOUBLE PRECISION. It does exactly what you want, including configurable rounding rules.

[EPluribusUnum]

@AlexPeshkoff , I don't see configurable rounding rules in firebird.conf.
How can I control rounding in FB40? Also calculation precision contol is possible?

[AlexPeshkoff]

On 9/14/22 11:38, EPluribusUnum wrote: @AlexPeshkoff <https://github.com/AlexPeshkoff> , I don't see configurable rounding rules in firebird.conf. How can I control rounding in FB40?

doc/sql.extensions/README.data_types

Also calculation precision contol is possible?

No, only have 16 or 34 digits, depending upon chosen datatype For explicit calculation precision contol use DECIMAL(n)