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:14:23 -0400
Message-ID: <2465409.1602170063@sss.pgh.pa.us> (raw)
In-Reply-To: <6FA5117D-6AED-4656-8FEF-B74AC18FAD85@brytlyt.com>
References: <6FA5117D-6AED-4656-8FEF-B74AC18FAD85@brytlyt.com>
Martin Visser <Martin.Visser@brytlyt.com> writes:
> Inconsistent results between numeric and double precision types.
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)
and that's really the right answer given
regression=# select 10::numeric / 90::numeric;
?column?
------------------------
0.11111111111111111111
(1 row)
We could carry that division out to more decimal places, but we'd
still just have a string of ones, which would become a string of
nines after multiplication by 9, and then floor() doesn't have a
choice about what to return. If we try to fudge that to get the
"right" answer, we'd be getting wrong answers for other cases.
[ 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.
While comparing the numeric and float8 versions of width_bucket,
I did notice that float8 allows +-Infinity for the first argument:
regression=# select width_bucket('inf'::float8,10,100,9);
width_bucket
--------------
10
(1 row)
while numeric doesn't:
regression=# select width_bucket('inf'::numeric,10,100,9);
ERROR: operand, lower bound, and upper bound cannot be infinity
That seems like an oversight in my recent patch to allow infinities
in numeric.
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: <2465409.1602170063@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