Rounding with FirebirdSQL

[Ertan Küçükoglu]

Hello,

I am using FirebirdSQL 4.0.2 32bit on Windows.

Below is a sample script for creating sample table and populating data.

-----

create table roundingtest

(

  vat     double precision,

  amount  double precision

);

commit;

insert into roundingtest(vat, amount) values(1, 7286.5); 

insert into roundingtest(vat, amount) values(1, 422.5);

insert into roundingtest(vat, amount) values(1, 7201);

commit;

-----

I use the following SQL for calculating and rounding VAT amounts.

select

  roundingtest.*,

  round(vat/100 * amount, 2) as vatamount

from

  roundingtest

result appears as following

       VAT|    AMOUNT| VATAMOUNT|

   DECIMAL|   DECIMAL|   DECIMAL|

---------------------------------

         1|    7286.5|     72.86|

         1|     422.5|      4.22|

         1|      7201|     72.01|

Above first two columns are wrongly rounded for my needs. The 3rd one is fine. What I need is to have anything equal or higher than 5 to be rounded up. But, I could not get that result.

1% of 7286.5 = 72.865

If I round it to 2 digits using rule anything equals 5 or higher rounds up, it should be 72.87

Is this possible using only internal functions?

Thanks & Regards,

Ertan

[Dimitry Sibiryakov]

Ertan Küçükoglu wrote 11.09.2022 15:16:
> Is this possible using only internal functions?

Yes, but double precision is a bad choice for that. Use decimal.

--
WBR, SD.

[Ertan Küçükoglu]

Dimitry Sibiryakov <s...@ibphoenix.com>, 11 Eyl 2022 Paz, 17:38 tarihinde şunu yazdı:

Ertan Küçükoglu wrote 11.09.2022 15:16:
> Is this possible using only internal functions?

   Yes, but double precision is a bad choice for that. Use decimal.

 

 Do you mean to use the DECIMAL column data type in table?

[Tomasz Tyrakowski]

Yes. Either that or cast when rounding. DECIMAL and DOUBLE PRECISION are
rounded according to different rules (DECIMAL half-up, DOUBLE PRECISION
towards the nearest even).
See the result of:

select
round(cast(72.865 as double precision), 2),
round(cast(72.865 as decimal(6,3)), 2)
from RDB$DATABASE;

It gives 72.86 and 72.87.

regards
Tomasz

[Ertan Küçükoglu]

I do not have rounding used in a lot of different cases. I will use casting during select for the time being. Below works for me

select

  roundingtest.*,

  round(cast(vat as decimal(18,4))/100 * cast(amount as decimal(18,4)), 2) as vatamount

from

  roundingtest

output is:

       VAT|    AMOUNT| VATAMOUNT|

   DECIMAL|   DECIMAL|   DECIMAL|

---------------------------------

         1|    7286.5|     72.87|

         1|     422.5|      4.23|

         1|      7201|     72.01|

Thanks & Regards,

Ertan

Tomasz Tyrakowski <t.tyra...@sol-system.pl>, 11 Eyl 2022 Paz, 20:39 tarihinde şunu yazdı:

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/2f55e9e7-8519-50f0-f678-c7fda2791810%40sol-system.pl.

[Ertan Küçükoglu]

Tomasz Tyrakowski <t.tyra...@sol-system.pl>, 11 Eyl 2022 Paz, 20:39 tarihinde şunu yazdı:

Yes. Either that or cast when rounding. DECIMAL and DOUBLE PRECISION are

rounded according to different rules (DECIMAL half-up, DOUBLE PRECISION
towards the nearest even).

 

Out of curiosity, where did you get that information "DECIMAL half-up, DOUBLE PRECISION towards the nearest even"

AFAIK this is not written in the ROUND() function explanation below

https://firebirdsql.org/file/documentation/html/en/refdocs/fblangref40/firebird-40-language-reference.html#fblangref40-scalarfuncs-round

Thanks & Regards,

Ertan

[Tomasz Tyrakowski]

Definitely not from those docs (I haven't upgraded my FB instances to
4.x yet), but I took a quick look at the docs for 3.x (and for 2.5 as
well) and they all say the same. As far as I remember, it has always
worked like that in Firebird (ever since 1.x). I use Postgres and MySQL
as well, and in MySQL rounding is always half-up (both for doubles and
decimals), while in Postgres rounding to N decimal places only works for
numeric/decimal and doubles can only be rounded to the nearest integer.
But, FYI: in Postgres round(cast(2.5 as double precision) returns 2, and
round(cast(2.5 as decimal(3,1))) gives 3. So, it seems every DBMS does
it its own way and the docs sometimes can be vague. Official Postgres
docs say:

"For numeric, ties are broken by rounding away from zero. For double
precision, the tie-breaking behavior is platform dependent, but “round
to nearest even” is the most common rule."

FB seems to follow this behavior, but the docs don't say it clearly
(unless it's written somewhere else and I just haven't found it).
However, since testing it only takes one simple query, it's probably
better to check than to rely on docs. Before answering you the first
time, I actually executed the query on the FB instance I was currently
working on (which was 2.5.9 classic on Linux, but I've just checked it
on Windows and the result is the same).

regards
Tomasz

[Dmitry Yemanov]

11.09.2022 20:39, Tomasz Tyrakowski wrote:
>
> Yes. Either that or cast when rounding. DECIMAL and DOUBLE PRECISION are
> rounded according to different rules (DECIMAL half-up, DOUBLE PRECISION
> towards the nearest even).
> See the result of:
>
> select
>     round(cast(72.865 as double precision), 2),
>     round(cast(72.865 as decimal(6,3)), 2)
> from RDB$DATABASE;
>
> It gives 72.86 and 72.87.

I suppose this is because 72.865 cannot be represented in double
precision exactly, instead it's stored as 72.86499999999999... and thus
rounded to 72.86.


Dmitry

[Tomasz Tyrakowski]

On 13.09.2022 at 12:32, Dmitry Yemanov wrote:
> I suppose this is because 72.865 cannot be represented in double
> precision exactly, instead it's stored as 72.86499999999999... and thus
> rounded to 72.86.

Yes, it probably is the case (1.25 is rounded to 1.3 - just checked, so
it's not always to the nearest even - my mistake, I probably
overinterpreted it based on Postgres behavior). I learned back in 1.x
days to use numeric for money and haven't thought much about it ever since.
Thanks for the insight.

regards
Tomasz

[Ertan Küçükoglu]

Dmitry Yemanov <fire...@yandex.ru>, 13 Eyl 2022 Sal, 13:32 tarihinde şunu yazdı:

I suppose this is because 72.865 cannot be represented in double
precision exactly, instead it's stored as 72.86499999999999... and thus
rounded to 72.86.

Yes, that is almost always correct with floating point numbers, yet still there are ways to programmatically round that 72.86499999999999... to 2 digits as 72.87

I do not have an example code at hand for C++ though. It seems like FirebirdSQL is not using something specific for such cases. I don't know, maybe it is related to performance.

Thanks & Regards,

Ertan

[Tim Crawford]

I think Dmitry is exactly right, it looks like there is a problem
with intermediate calculations where the representation digits
are being used during rounding

I consider this a pretty serious bug. Surprised this has been
around for what seems to be forever
The problem also exists for FLOAT

(Sorry for the long winded email, but want to document this so it is in
an email for now)
(Testing with FB 3.10 on Win 64 only)

Double precision is supposed to be accurate to 15 significant digits.
If I read this right it actually is that if you convert to/from a string
you
will get the same number up to 15 or 16 digits
(https://en.wikipedia.org/wiki/Double-precision_floating-point_format)

A very simple example is 4.015
The closes representation is
4.01499999999999968025576890795E0
(https://www.binaryconvert.com/result_double.html?decimal=052046048049053)

The Firebird algorithm is apparently doing the rounding using the
'closest representation' digits?
That is, getting all the digits of the closes representation and
truncating to + 1 then rounding,
so round(4.015, 2) is doing this:
  4.01499999999999968025576890795
->4.014
->4.01

This seems to be supported by doing this instead, which rounds as expected,
though I don't know that it would be correct in all cases:
round(round(4.015,3),2):
  4.01499999999999968025576890795
->4.0149
->4.015
->4.02


FLOAT does not work either

select
      'Double'
    ,            cast( 4.015 as DOUBLE PRECISION)         num
    ,round(      cast( 4.015 as DOUBLE PRECISION), 2)     rnd2 -- nope
    ,round(round(cast( 4.015 as DOUBLE PRECISION), 3),2)  rnd32 -- yes
    ,            cast( 4.015 as DOUBLE PRECISION) * 100   num100
    ,round(      cast( 4.015 as DOUBLE PRECISION) * 100,0) rnd100   -- nope
from RDB$DATABASE
union ALL
select
      'Float'
    ,            cast( 4.015 as FLOAT)         num
    ,round(      cast( 4.015 as FLOAT), 2)     rnd2 -- nope
    ,round(round(cast( 4.015 as FLOAT), 3),2)  rnd32 -- yes
    ,            cast( 4.015 as FLOAT) * 100   num100
    ,round(      cast( 4.015 as FLOAT) * 100,0) rnd100   -- nope

from RDB$DATABASE;

FlameRobin:

CONSTANT NUM                    RND2 RND32                 
NUM100                  RND100
======== ======================= =======================
======================= ======================= =======================
Double         4.015000000000000       4.010000000000000
4.020000000000000     401.499999999999943     401.000000000000000
Float          4.014999866485596       4.010000000000000
4.020000000000000     401.499986648559570     401.000000000000000

iSql:

CONSTANT NUM                    RND2 RND32                 
NUM100                  RND100
======== ======================= =======================
======================= ======================= =======================
Double         4.015000000000000       4.010000000000000
4.020000000000000     401.4999999999999       401.0000000000000
Float          4.014999866485596       4.010000000000000
4.020000000000000     401.4999866485596       401.0000000000000


ROUND is broken for double precision and FLOAT.
Should either be fixed, or doc and code should be change to remove double as
accepted data type for ROUND

[Mark Rotteveel]

This looks like rounding by re-rounding, and that is not how rounding in
Firebird works. If Firebird would actually work this way, you'd get
4.01499999999999968025576890795 => 4.015 => 4.02.

> This seems to be supported by doing this instead, which rounds as expected,
> though I don't know that it would be correct in all cases:
> round(round(4.015,3),2):
>   4.01499999999999968025576890795
> ->4.0149
> ->4.015
> ->4.02

[..]

> ROUND is broken for double precision and FLOAT.
> Should either be fixed, or doc and code should be change to remove
> double as
> accepted data type for ROUND

It is not broken. For example, Java produces the exact same result when
rounding 4.015 to 2 digits. This is what you get when using binary
floating points. Your reasoning about the example is not correct. As you
observe, 4.015e0 is actually 4.01499999999999968025576890795, so when
you round to 2 decimal digits, it rounds to 4.01, because that value is
*closer* to 4.01 than to 4.02.

Sure, other rounding strategies are possible, but that would be fudging
the numbers to fit, and would probably be undesirable in other cases. If
you want precise calculations, don't use binary floating points, but use
NUMERIC/DECIMAL, or use DECFLOAT (a decimal floating point type). Though
keep in mind, those - again - come with other potential problems (e.g.
NUMERIC/DECIMAL division)

Mark
--
Mark Rotteveel

[Tim Crawford]

Yes I figured it out finally and didn't know if I should
further muddy the water with more replies.

Yes I think everything everywhere works the same
because of math libraries and processor functions used.

My confusion stemmed from the statement here:
https://en.wikipedia.org/wiki/Double-precision_floating-point_format

"If a decimal string with at most 15 significant digits is converted to the IEEE 754 double-precision format,
giving a normal number, and then converted back to a decimal string with the same number of digits,
the final result should match the original string.

If an IEEE 754 double-precision number is converted to a decimal string with at least 17 significant digits,
and then converted back to double-precision representation, the final result must match the original number.[1]
"

I didn't read the whole article carefully and was thinking all numbers with < 16 digits
would be EXACTLY represented (e.g. accurate) in a double, but what this is saying
is that it is accurate when converting to/from string (which DECIMAL is kind of like)

My confusion was partly because when you select a double from
the database it IS converted to a string for display in your query tool,
so you see it as 4.015 when behind the scenes it is not

I'm sure I know all this back in the day, but my degree was 35 years ago
and for some reason have not run into float/double usage... I've always
worked with financial and not scientific applications, so not surprising.

Also forgot you don't pay attention to any digits after the scale + 1
so 4.4445 rounded to 2 decimal places is 44.44 not 4.45. Duh.