Horton

Thread

Commits

  1. Allow width_bucket()'s "operand" input to be NaN.

  1. Allow the "operand" input of width_bucket() to be NaN

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-06-21T21:21:51Z

    The attached patch does what was discussed in the pgsql-docs
thread at [1], namely change the four-argument variants of
width_bucket() to allow their first argument to be NaN,
treating that value as larger than any non-NaN.

While these functions are defined by the SQL standard, it doesn't
appear to have anything to say about NaN values.  So it's up to
us to decide what's the most consistent behavior.  The arguments
for changing this are:

1. It's consistent with the array variant of width_bucket(),
which treats NaN as a valid input larger than any non-NaN.

2. The first argument is probably coming from a table,
so it's more likely for it to be NaN than is the case for
the histogram endpoints.  It'd be better not to throw an
error in such cases.

Of course, #2 is a bit of a value judgment.  One could
alternatively argue that accepting NaN risks "garbage in,
garbage out" results, since the result will not be visibly
distinct from the results for ordinary values.

Thoughts?  (I'm envisioning this as a v19 change.)

			regards, tom lane

[1] https://www.postgresql.org/message-id/flat/2BD74F86-5B89-4AC1-8F13-23CED3546AC1%40gmail.com

  2. Re: Allow the "operand" input of width_bucket() to be NaN

    Dean Rasheed <dean.a.rasheed@gmail.com> — 2025-07-02T06:48:31Z

    On Sat, 21 Jun 2025 at 22:21, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> The attached patch does what was discussed in the pgsql-docs
> thread at [1], namely change the four-argument variants of
> width_bucket() to allow their first argument to be NaN,
> treating that value as larger than any non-NaN.
>

LGTM.

I note that there is no doc update, and I think that is correct, since
we never previously documented that NaN wasn't allowed. The new
behaviour is what one would have expected, given that we do document
that NaN values compare as greater than all non-NaN values.

It could even be argued that this is a bug fix, but the lack of prior
complaints justifies not back-patching.

Regards,
Dean

  3. Re: Allow the "operand" input of width_bucket() to be NaN

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-07-02T15:36:01Z

    Dean Rasheed <dean.a.rasheed@gmail.com> writes:
> On Sat, 21 Jun 2025 at 22:21, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> The attached patch does what was discussed in the pgsql-docs
>> thread at [1], namely change the four-argument variants of
>> width_bucket() to allow their first argument to be NaN,
>> treating that value as larger than any non-NaN.

> LGTM.

Pushed, thanks for looking at it.

> It could even be argued that this is a bug fix, but the lack of prior
> complaints justifies not back-patching.

Yeah, that was my feeling as well.

			regards, tom lane