From: Tom Lane <tgl@sss.pgh.pa.us>
To: Martin Visser <Martin.Visser@brytlyt.com>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: WIDTH_BUCKET inconsistency
Date: Thu, 08 Oct 2020 11:28:53 -0400
Message-ID: <2465974.1602170933@sss.pgh.pa.us> (raw)
In-Reply-To: <2465409.1602170063@sss.pgh.pa.us>
References: <6FA5117D-6AED-4656-8FEF-B74AC18FAD85@brytlyt.com>
<2465409.1602170063@sss.pgh.pa.us>
I wrote:
> That's basically roundoff error, which I'm afraid we're not going
> to be able to do much about. You could probably find other examples
> where the numeric calculation is "right" and the float8 calculation
> is "wrong". For example, the case
> regression=# select width_bucket(20::numeric,10,100,9);
> width_bucket
> --------------
> 1
> (1 row)
> is essentially computing floor() of
> regression=# select 9 * (10::numeric / 90::numeric) + 1;
> ?column?
> ------------------------
> 1.99999999999999999999
> (1 row)
...
> [ thinks for awhile... ] It's tempting to propose that we take
> the next-to-last result (1.99999999999999999999 here) and round
> it to one fewer decimal place before applying floor(). It's
> still scary to contemplate whether that might make as many cases
> worse as it does better, but I suspect the reason why we get a
> nicer answer for this case in the float8 code is that the CPU is
> doing something equivalent to that in the float calculation.
No, waitasecond. Studying the float code a little closer, I realize
that what it's doing is equivalent to
regression=# select (9 * 10::numeric) / 90::numeric + 1;
?column?
------------------------
2.00000000000000000000
(1 row)
That is, the numeric code is doing the calculations in an order that
must lead to an inexact result, whereas we could easily do them in
an order that always gives an exact result when one is possible.
Independently of whether trying to round a little harder would be
advisable, that seems like a clear win.
There are certain parties around here who would probably bitch
if I were to back-patch this change, but it seems fine to do in
HEAD.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, Martin.Visser@brytlyt.com, pgsql-bugs@lists.postgresql.org
Subject: Re: WIDTH_BUCKET inconsistency
In-Reply-To: <2465974.1602170933@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox