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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Cc: Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
Subject: Allow the "operand" input of width_bucket() to be NaN
Date: 2025-06-21 21:21:51
Message-ID: 2822872.1750540911@sss.pgh.pa.us
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
v1-allow-NaN-in-width-bucket.patch text/x-diff 5.7 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message Arseniy Mukhin 2025-06-21 21:55:31 Re: amcheck support for BRIN indexes
Previous Message Mihail Nikalayeu 2025-06-21 20:32:44 Re: Revisiting {CREATE INDEX, REINDEX} CONCURRENTLY improvements