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: <531516.1764721052@sss.pgh.pa.us>
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>
 <513345.1764717860@sss.pgh.pa.us> <531516.1764721052@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Tue, 02 Dec 2025 19:17:32 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <545889.1764725276.1@sss.pgh.pa.us>
Date: Tue, 02 Dec 2025 20:27:56 -0500
Message-ID: <545890.1764725276@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/545890.1764725276%40sss.pgh.pa.us>
Precedence: bulk

I wrote:
> I'm coming around to the conclusion that your way is better,
> though.  It seems good that "any NaN in the input results in
> NaN output", which your way does and mine doesn't.

Poking further at this, I found that my v2 patch fails that principle
in one case:

regression=# SELECT corr( 0.1 , 'nan' ) FROM generate_series(1,1000) g;
 corr 
------
     
(1 row)

We see that Y is constant and therefore return NULL, despite the
other NaN input.

I think we can fix that along these lines:

@@ -3776,8 +3776,12 @@ float8_corr(PG_FUNCTION_ARGS)
 	if (N < 1.0)
 		PG_RETURN_NULL();
 
-	/* per spec, return NULL for horizontal and vertical lines */
-	if (!isnan(commonX) || !isnan(commonY))
+	/*
+	 * per spec, return NULL for horizontal and vertical lines; but not if the
+	 * result would otherwise be NaN
+	 */
+	if ((!isnan(commonX) || !isnan(commonY)) &&
+		(!isnan(Sxx) && !isnan(Syy)))
 		PG_RETURN_NULL();
 
 	/* at this point, Sxx and Syy cannot be zero or negative */

(don't think it should be necessary to also check Sxy)


BTW, HEAD is inconsistent: it will return NaN for this example, but
only because it's confused by roundoff error into thinking that Y
isn't constant.  With few enough inputs, it produces NULL too:

regression=# SELECT corr( 0.1 , 'nan' ) FROM generate_series(1,3) g;
 corr 
------
     
(1 row)

			regards, tom lane