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

Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)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