Thread

Re: BUG #19340: Wrong result from CORR() function

Tom Lane <tgl@sss.pgh.pa.us> — 2025-12-06T18:18:43Z

Dean Rasheed <dean.a.rasheed@gmail.com> writes:
> The first result is required by the SQL standard, which says first
> that if N * Sxx = Sx * Sx, then the result is NULL, and similarly for
> y. So that rule should take precedence over any rule for NaNs.

> The second result can be justified by the IEEE rules for NaNs,
> according to which NaN does not equal any other number, including
> itself, and so an all-NaN column is not all equal (and it doesn't
> satisfy "N * Sxx = Sx * Sx" either). So the SQL standard all-the-same
> rule doesn't apply to the second query, and the standard computation
> yields NaN.

Right, that's pretty much the thinking I've ended up on.

> If we're happy with those decisions, then I think this comment should
> be updated:

Here's a v3 with another try at that comment, and the other points
addressed.  Also now with a draft commit message.  I credited you
as co-author since so much of this is your ideas.

			regards, tom lane