Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.96)
	(envelope-from <pgsql-bugs-owner+archive@lists.postgresql.org>)
	id 1vQU4s-008jbU-2L
	for pgsql-bugs@arkaria.postgresql.org;
	Tue, 02 Dec 2025 17:22:15 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.96)
	(envelope-from <pgsql-bugs-owner+archive@lists.postgresql.org>)
	id 1vQU4r-009BtH-2c
	for pgsql-bugs@arkaria.postgresql.org;
	Tue, 02 Dec 2025 17:22:14 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.96)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1vQU4r-009Bt8-1m
	for pgsql-bugs@lists.postgresql.org;
	Tue, 02 Dec 2025 17:22:13 +0000
Received: from sss.pgh.pa.us ([68.162.161.243])
	by magus.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.96)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1vQU4p-002lxh-14
	for pgsql-bugs@lists.postgresql.org;
	Tue, 02 Dec 2025 17:22:13 +0000
Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1])
	by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 5B2HM751375069;
	Tue, 2 Dec 2025 12:22:07 -0500
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Oleg Ivanov <o15611@gmail.com>
cc: Laurenz Albe <laurenz.albe@cybertec.at>,
        Dean Rasheed <dean.a.rasheed@gmail.com>,
        pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19340: Wrong result from CORR() function
In-reply-to: 
 <CAH1GMznwE=WGJYCZU1ou9iOmNR87NQFSRdYHqE3=JQ3as4P1hQ@mail.gmail.com>
References: <19340-6fb9f6637f562092@postgresql.org>
 <4ab9867066e9545dd1a7e835a480bb0ecbe1a00d.camel@cybertec.at>
 <CAH1GMznwE=WGJYCZU1ou9iOmNR87NQFSRdYHqE3=JQ3as4P1hQ@mail.gmail.com>
Comments: In-reply-to Oleg Ivanov <o15611@gmail.com>
	message dated "Tue, 02 Dec 2025 17:05:23 +0300"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <375067.1764696127.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Tue, 02 Dec 2025 12:22:07 -0500
Message-ID: <375068.1764696127@sss.pgh.pa.us>
List-Id: <pgsql-bugs.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-bugs@lists.postgresql.org>
List-Owner: <mailto:pgsql-bugs-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-bugs>
Archived-At: 
 <https://www.postgresql.org/message-id/375068.1764696127%40sss.pgh.pa.us>
Precedence: bulk

Oleg Ivanov <o15611@gmail.com> writes:
> Yes, must be NULL in all the queries I have provided!
> But PostgreSQL curr() returns numbers, wich is incorrect.

Yeah, looks like roundoff error to me.  In your example

SELECT corr( 0.09 , 0.09000001 ) FROM generate_series(1,25) ;

at the end of float8_corr we have

3754            PG_RETURN_FLOAT8(Sxy / sqrt(Sxx * Syy));
(gdb) i locals
transarray =3D <optimized out>
transvalues =3D 0x1b96da8
N =3D 25
Sxx =3D 3.2869204384208827e-34
Syy =3D 9.3266240309214617e-33
Sxy =3D -3.2869204384208827e-34

where ideally those three values would be zero (and we would have
fallen out with a NULL result at the preceding line).

It's fundamentally impossible to guarantee exact results with
floating-point arithmetic, so if you are expecting that you need
to readjust your expectations.  But having said that, it does
seem a bit sad that we can't detect constant-input cases exactly.
I wonder whether it'd be worth carrying additional state to
check that explicitly (instead of assuming that "if (Sxx =3D=3D 0 ||
Syy =3D=3D 0)" will catch it).

You might find the previous discussion interesting:

https://www.postgresql.org/message-id/flat/153313051300.1397.9594490737341=
194671%40wrigleys.postgresql.org

			regards, tom lane