Re: BUG #17876: Function width_bucket() for float8 input returns value out of range

Hi all,

Attached is a proposed fix for the issue. It has extended the tests to
cover these cases and also some additional tests for values close to
DBL_MIN. I compared the old and new version using the attached program with
the following result:

DBL_MAX: 1.797693e+308, DBL_MIN: 2.225074e-308
1.04000e+01, low: -1.79769e+308, high: 1.79769e+308, count: 10 --> orig:
-2147483648, new: 6
-8.98847e+307, low: -1.79769e+308, high: 1.79769e+308, count: 10 -->
orig: -2147483648, new: 3
8.98847e+307, low: -1.79769e+308, high: 1.79769e+308, count: 10 -->
orig: -2147483648, new: 8
1.04000e+01, low: -1.79769e+308, high: 1.79769e+308, count: 12 --> orig:
-2147483648, new: 7
-8.98847e+307, low: -1.79769e+308, high: 1.79769e+308, count: 12 -->
orig: -2147483648, new: 4
8.98847e+307, low: -1.79769e+308, high: 1.79769e+308, count: 12 -->
orig: -2147483648, new: 10
1.04000e+01, low: -1.79769e+308, high: 1.79769e+308, count: 1 --> orig:
1, new: 1
-8.98847e+307, low: -1.79769e+308, high: 1.79769e+308, count: 1 --> orig:
1, new: 1
8.98847e+307, low: -1.79769e+308, high: 1.79769e+308, count: 1 --> orig:
-2147483648, new: 1
1.04000e+01, low: -1.79769e+308, high: 1.79769e+308, count: 2 --> orig:
-2147483648, new: 2
-8.98847e+307, low: -1.79769e+308, high: 1.79769e+308, count: 2 --> orig:
1, new: 1
8.98847e+307, low: -1.79769e+308, high: 1.79769e+308, count: 2 --> orig:
-2147483648, new: 2
5.35000e+00, low: 2.40000e-02, high: 1.00600e+01, count: 5 --> orig:
3, new: 3
2.22507e-308, low: -4.45015e-308, high: 4.45015e-308, count: 4 --> orig:
4, new: 4
-2.22507e-308, low: -4.45015e-308, high: 4.45015e-308, count: 4 --> orig:
2, new: 2
2.22507e-308, low: -6.67522e-308, high: 6.67522e-308, count: 4 --> orig:
3, new: 3
-2.22507e-308, low: -6.67522e-308, high: 6.67522e-308, count: 4 --> orig:
2, new: 2
2.22507e-308, low: -6.67522e-308, high: 6.67522e-308, count: 6 --> orig:
5, new: 5
-2.22507e-308, low: -6.67522e-308, high: 6.67522e-308, count: 6 --> orig:
3, new: 3

As you can see, the old and new computations seem to produce the same
values also for values close to DBL_MIN.

On Wed, Mar 29, 2023 at 10:12 AM PG Bug reporting form <
noreply(at)postgresql(dot)org> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 17876
> Logged by: Mats Kindahl
> Email address: mats(at)timescale(dot)com
> PostgreSQL version: 15.2
> Operating system: All
> Description:
>
> On 64-bit platforms, the existing width_bucket() computation can
> result in a NaN (0x8000000) if the difference between the `operand`,
> `low`, or `high` parameters exceeds DBL_MAX. This is then cast to the
> value -2147483648.
>
> mats=# WITH sample(operand, low, high, cnt) AS (
> VALUES
> (10.5::float8, -1.797e+308::float8, 1.797e+308::float8, 1::int4),
> (10.5::float8, -1.797e+308::float8, 1.797e+308::float8, 2),
> (10.5::float8, -1.797e+308::float8, 1.797e+308::float8, 3),
>
> (1.797e+308::float8 / 2, -1.797e+308::float8, 1.797e+308::float8,
> 10),
> (-1.797e+308::float8 / 2, -1.797e+308::float8, 1.797e+308::float8,
> 10),
>
> (1.797e+308::float8 / 2, -1.797e+308::float8, 1.797e+308::float8,
> 16),
> (-1.797e+308::float8 / 2, -1.797e+308::float8, 1.797e+308::float8,
> 16),
>
> (1.797e+308::float8, -1.797e+308::float8 / 2, 1.797e+308::float8 /
> 2,
> 10),
> (-1.797e+308::float8, -1.797e+308::float8 / 2, 1.797e+308::float8
> / 2,
> 10)
> ) SELECT width_bucket(operand, low, high, cnt) FROM sample;
> width_bucket
> --------------
> 1
> -2147483648
> -2147483648
> -2147483648
> -2147483648
> -2147483648
> -2147483648
> 11
> 0
> (9 rows)
>
>