From: Tom Lane <tgl@sss.pgh.pa.us>
To: Dean Rasheed <dean.a.rasheed@gmail.com>
cc: Oleg Ivanov <o15611@gmail.com>, Laurenz Albe <laurenz.albe@cybertec.at>,
        pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19340: Wrong result from CORR() function
In-reply-to: 
 <CAEZATCUo6skdTfRP1XM1sbMveRo1PgpjzOMZEps69Bbz6-OnKA@mail.gmail.com>
References: <19340-6fb9f6637f562092@postgresql.org>
 <4ab9867066e9545dd1a7e835a480bb0ecbe1a00d.camel@cybertec.at>
 <CAH1GMznwE=WGJYCZU1ou9iOmNR87NQFSRdYHqE3=JQ3as4P1hQ@mail.gmail.com>
 <375068.1764696127@sss.pgh.pa.us>
 <CAEZATCU8rnyKN3z_5-osk3Bn8dtzWf9nKjTr2E16-ExXiESNrQ@mail.gmail.com>
 <434484.1764707203@sss.pgh.pa.us>
 <CAEZATCUo6skdTfRP1XM1sbMveRo1PgpjzOMZEps69Bbz6-OnKA@mail.gmail.com>
Comments: In-reply-to Dean Rasheed <dean.a.rasheed@gmail.com>
	message dated "Tue, 02 Dec 2025 23:03:45 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <513344.1764717860.1@sss.pgh.pa.us>
Date: Tue, 02 Dec 2025 18:24:20 -0500
Message-ID: <513345.1764717860@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/513345.1764717860%40sss.pgh.pa.us>
Precedence: bulk

Dean Rasheed <dean.a.rasheed@gmail.com> writes:
> I played around with having just 2 extra array elements, constX and
> constY equal to the common value if all the values are the same, and
> NaN otherwise.

Hmm.

> Doing it that way does lead to one difference though: all-NaN inputs
> leads to a NaN result, whereas your patch produces NULL for that case.

Yeah, I did it as I did precisely because I wanted all-NaN-input to be
seen as a constant.  But you could make an argument that NaN is not
really a fixed value but has more kinship to the "we don't know what
the value is" interpretation of SQL NULL.  In that case your proposal
is semantically reasonable on the grounds that maybe the NaNs aren't
really all equal, and I agree it ought to be a little faster than
mine.

			regards, tom lane