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@postgresql.org> wrote:

> The following bug has been logged on the website:
>
> Bug reference:      17876
> Logged by:          Mats Kindahl
> Email address:      mats@timescale.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)
>
>