Re: Definitional issue: stddev_pop (and related) for 1 input

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
Cc: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Re: Definitional issue: stddev_pop (and related) for 1 input
Date: 2020-06-12 19:53:38
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

I wrote:
> Before v12, stddev_pop() had the following behavior with just a
> single input value:
> ...
> As of v12, though, all three cases produce 0. I am not sure what
> to think about that with respect to an infinity input, but I'm
> quite sure I don't like it for NaN input.

While I'm still not sure whether there's an academic argument that
zero is a reasonable stddev value for a single input that is Inf,
it seems to me that backwards compatibility is a sufficient reason
for going back to producing NaN for that.

Hence, attached are some proposed patches. 0001 just adds test
cases demonstrating the current behavior; then 0002 makes the
proposed code change. It's easy to check that the test case results
after 0002 match what v11 produces.

0003 deals with a different problem which I noted in [1]: the numeric
variants of var_samp and stddev_samp also do the wrong thing for a
single special input. Their disease is that they produce NaN for a
single NaN input, where it seems more sensible to produce NULL.
At least, NULL is what we get for the same case with the float
aggregates, so we have to change one or the other set of functions
if we want consistency.

I propose back-patching 0001/0002 as far as v12, since the failure
to match the old outputs seems like a pretty clear bug/regression.
However, I'd be content to apply 0003 only to HEAD. That misbehavior
is very ancient, and the lack of complaints suggests that few people
really care about this fine point.

regards, tom lane


Attachment Content-Type Size
0001-add-single-input-test-cases.patch text/x-diff 5.5 KB
0002-fix-float-aggregates.patch text/x-diff 4.1 KB
0003-fix-numeric-aggregates.patch text/x-diff 2.3 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2020-06-12 20:06:19 Re: Infinities in type numeric
Previous Message Robert Haas 2020-06-12 19:45:43 Re: Infinities in type numeric