Thread

1. rounding_up

   Daria Shanina <vilensipkdm@gmail.com> — 2025-04-14T08:24:20Z

   Hello everyone!
   
   I noticed, when we parse and validate values (in particular, the int type),
   we use the *rint* method, but unfortunately it does not work according to
   the round rules. Although on the website
   https://learn.microsoft.com/en-us/cpp/c-runtime-library/reference/rint-rintf-rintl?view=msvc-170
   says something else.
   
   I tested at several OS:
   
   
   Lubuntu
   
   daria-shanina@lnv-dshanina:~/projects/test$ ./rounding_up
   
   rint(2.00) is 2.0 | round(2.00) is 2.0 | ceil(2.00) is 2.0 | floor(2.00) is
   2.0
   
   rint(2.10) is 2.0 | round(2.10) is 2.0 | ceil(2.10) is 3.0 | floor(2.10) is
   2.0
   
   rint(2.20) is 2.0 | round(2.20) is 2.0 | ceil(2.20) is 3.0 | floor(2.20) is
   2.0
   
   rint(2.30) is 2.0 | round(2.30) is 2.0 | ceil(2.30) is 3.0 | floor(2.30) is
   2.0
   
   rint(2.40) is 2.0 | round(2.40) is 2.0 | ceil(2.40) is 3.0 | floor(2.40) is
   2.0
   
   rint(2.50) is 2.0 | round(2.50) is 3.0 | ceil(2.50) is 3.0 | floor(2.50) is
   2.0
   
   rint(2.60) is 3.0 | round(2.60) is 3.0 | ceil(2.60) is 3.0 | floor(2.60) is
   2.0
   
   rint(2.70) is 3.0 | round(2.70) is 3.0 | ceil(2.70) is 3.0 | floor(2.70) is
   2.0
   
   rint(2.80) is 3.0 | round(2.80) is 3.0 | ceil(2.80) is 3.0 | floor(2.80) is
   2.0
   
   rint(2.90) is 3.0 | round(2.90) is 3.0 | ceil(2.90) is 3.0 | floor(2.90) is
   2.0
   
   
   FreeBSD
   
   daria@2ndfreebsd:~/projects/test$ ./rounding_up
   
   rint(2.00) is 2.0 | round(2.00) is 2.0 | ceil(2.00) is 2.0 | floor(2.00) is
   2.0
   
   rint(2.10) is 2.0 | round(2.10) is 2.0 | ceil(2.10) is 3.0 | floor(2.10) is
   2.0
   
   rint(2.20) is 2.0 | round(2.20) is 2.0 | ceil(2.20) is 3.0 | floor(2.20) is
   2.0
   
   rint(2.30) is 2.0 | round(2.30) is 2.0 | ceil(2.30) is 3.0 | floor(2.30) is
   2.0
   
   rint(2.40) is 2.0 | round(2.40) is 2.0 | ceil(2.40) is 3.0 | floor(2.40) is
   2.0
   
   rint(2.50) is 2.0 | round(2.50) is 3.0 | ceil(2.50) is 3.0 | floor(2.50) is
   2.0
   
   rint(2.60) is 3.0 | round(2.60) is 3.0 | ceil(2.60) is 3.0 | floor(2.60) is
   2.0
   
   rint(2.70) is 3.0 | round(2.70) is 3.0 | ceil(2.70) is 3.0 | floor(2.70) is
   2.0
   
   rint(2.80) is 3.0 | round(2.80) is 3.0 | ceil(2.80) is 3.0 | floor(2.80) is
   2.0
   
   rint(2.90) is 3.0 | round(2.90) is 3.0 | ceil(2.90) is 3.0 | floor(2.90) is
   2.0
   
   
   Windows
   
   C:\Users\Daria\projects\test>rounding_up.exe
   
   rint(2.00) is 2.0 | round(2.00) is 2.0 | ceil(2.00) is 2.0 | floor(2.00) is
   2.0
   
   rint(2.10) is 2.0 | round(2.10) is 2.0 | ceil(2.10) is 3.0 | floor(2.10) is
   2.0
   
   rint(2.20) is 2.0 | round(2.20) is 2.0 | ceil(2.20) is 3.0 | floor(2.20) is
   2.0
   
   rint(2.30) is 2.0 | round(2.30) is 2.0 | ceil(2.30) is 3.0 | floor(2.30) is
   2.0
   
   rint(2.40) is 2.0 | round(2.40) is 2.0 | ceil(2.40) is 3.0 | floor(2.40) is
   2.0
   
   rint(2.50) is 2.0 | round(2.50) is 3.0 | ceil(2.50) is 3.0 | floor(2.50) is
   2.0
   
   rint(2.60) is 3.0 | round(2.60) is 3.0 | ceil(2.60) is 3.0 | floor(2.60) is
   2.0
   
   rint(2.70) is 3.0 | round(2.70) is 3.0 | ceil(2.70) is 3.0 | floor(2.70) is
   2.0
   
   rint(2.80) is 3.0 | round(2.80) is 3.0 | ceil(2.80) is 3.0 | floor(2.80) is
   2.0
   
   rint(2.90) is 3.0 | round(2.90) is 3.0 | ceil(2.90) is 3.0 | floor(2.90) is
   2.0
   
   
   As you could see in the output, the *round* method works according to the
   rules. Maybe we should use it?
   
   
   Thank you for your attention!
   
   
   --
   
   Best regards,
   
   Daria Shanina
   

2. Re: rounding_up

   x4mmm@yandex-team.ru — 2025-04-14T10:17:50Z

   Hi Daria!
   
   > On 14 Apr 2025, at 13:24, Daria Shanina <vilensipkdm@gmail.com> wrote:
   > 
   > when we parse and validate values (in particular, the int type), we use the rint method, but unfortunately it does not work according to the round rules.
   
   Are this concerns explainable in SQL query?
   
   As far as I can see from your data, rint() is consistent across OSes. Can user observe any inconsistency caused by rint() behavior in PostgreSQL?
   
   Thanks!
   
   
   Best regards, Andrey Borodin.
   
   
   

3. Re: rounding_up

   Christoph Moench-Tegeder <cmt@burggraben.net> — 2025-04-14T13:47:13Z

   Hi,
   
   ## Daria Shanina (vilensipkdm@gmail.com):
   
   > I noticed, when we parse and validate values (in particular, the int type),
   > we use the *rint* method, but unfortunately it does not work according to
   > the round rules.
   
   First question would be "which round rule?" as (of course) there're
   multiple to chose from.
   
   Second, the rules in use are consistent with the documentation of
   round(double precision)
   : Rounds to nearest integer. 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.
   https://www.postgresql.org/docs/current/functions-math.html
   and I think it makes sense to have round() and implicit rounding
   behave the same.
   
   Third, rint() works the way you set it to with fesetround() (see
   man page). And that works on the nearest Linux and FreeBSD I
   could grab :)
   
   Regards,
   Christoph
   
   -- 
   Spare Space.
   
   
   
   

4. Re: rounding_up

   Tom Lane <tgl@sss.pgh.pa.us> — 2025-04-14T14:26:42Z

   Christoph Moench-Tegeder <cmt@burggraben.net> writes:
   > ## Daria Shanina (vilensipkdm@gmail.com):
   >> I noticed, when we parse and validate values (in particular, the int type),
   >> we use the *rint* method, but unfortunately it does not work according to
   >> the round rules.
   
   > First question would be "which round rule?" as (of course) there're
   > multiple to chose from.
   
   Yeah.  Round-to-nearest-even is a well-respected rule, which is why
   it's the default per IEEE 754.  I don't see a good reason for us
   to switch.  Even if someone advanced an argument, it would have
   to be a *mighty* convincing argument to justify breaking backwards
   compatibility here.
   
   I do find it a little unfortunate that our numeric type does it
   differently than our float types.  Again though, there's a huge
   compatibility argument against changing that now.  It does give
   you an "out" if you really need one or the other behavior for
   a particular application: you can cast to numeric or float8
   before casting to int.
   
   			regards, tom lane
   
   
   
   

5. Re: rounding_up

   Daria Shanina <vilensipkdm@gmail.com> — 2025-04-14T14:55:17Z

   Hi, Christoph!
   
   You wrote a very interesting answer.
   
   
   > First question would be "which round rule?"
   
   I mean rounding up “as at school”, but there are nuances in programming.
   
   > https://www.postgresql.org/docs/current/functions-math.html
   
   Thanks a lot for the link to the doc!
   
   > you set it to with fesetround() (see
   man page)
   
   To my great shame, I didn't know about fesetround(). Thanks a lot too!
   
   
   Best regards,
   
   Daria Shanina
   
   пн, 14 апр. 2025 г. в 17:26, Tom Lane <tgl@sss.pgh.pa.us>:
   
   > Christoph Moench-Tegeder <cmt@burggraben.net> writes:
   > > ## Daria Shanina (vilensipkdm@gmail.com):
   > >> I noticed, when we parse and validate values (in particular, the int
   > type),
   > >> we use the *rint* method, but unfortunately it does not work according
   > to
   > >> the round rules.
   >
   > > First question would be "which round rule?" as (of course) there're
   > > multiple to chose from.
   >
   > Yeah.  Round-to-nearest-even is a well-respected rule, which is why
   > it's the default per IEEE 754.  I don't see a good reason for us
   > to switch.  Even if someone advanced an argument, it would have
   > to be a *mighty* convincing argument to justify breaking backwards
   > compatibility here.
   >
   > I do find it a little unfortunate that our numeric type does it
   > differently than our float types.  Again though, there's a huge
   > compatibility argument against changing that now.  It does give
   > you an "out" if you really need one or the other behavior for
   > a particular application: you can cast to numeric or float8
   > before casting to int.
   >
   >                         regards, tom lane
   >
   
   
   -- 
   С уважением,
   Шанина Дарья Александровна
   

6. Re: rounding_up

   Daria Shanina <vilensipkdm@gmail.com> — 2025-04-14T15:05:16Z

   Hi, Tom!
   
   > Round-to-nearest-even is a well-respected rule
   
   Yes, you're convinced me! I can’t argue with IEEE 754 =) And, of course,
   can’t break compatibility.
   
   
   Best regards,
   
   Daria Shanina
   
   пн, 14 апр. 2025 г. в 17:26, Tom Lane <tgl@sss.pgh.pa.us>:
   
   > Christoph Moench-Tegeder <cmt@burggraben.net> writes:
   > > ## Daria Shanina (vilensipkdm@gmail.com):
   > >> I noticed, when we parse and validate values (in particular, the int
   > type),
   > >> we use the *rint* method, but unfortunately it does not work according
   > to
   > >> the round rules.
   >
   > > First question would be "which round rule?" as (of course) there're
   > > multiple to chose from.
   >
   > Yeah.  Round-to-nearest-even is a well-respected rule, which is why
   > it's the default per IEEE 754.  I don't see a good reason for us
   > to switch.  Even if someone advanced an argument, it would have
   > to be a *mighty* convincing argument to justify breaking backwards
   > compatibility here.
   >
   > I do find it a little unfortunate that our numeric type does it
   > differently than our float types.  Again though, there's a huge
   > compatibility argument against changing that now.  It does give
   > you an "out" if you really need one or the other behavior for
   > a particular application: you can cast to numeric or float8
   > before casting to int.
   >
   >                         regards, tom lane
   >
   
   
   -- 
   С уважением,
   Шанина Дарья Александровна