agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedFrom: Tom Lane <tgl@sss.pgh.pa.us>
To: Oleg Ivanov <o15611@gmail.com>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: Dean Rasheed <dean.a.rasheed@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19340: Wrong result from CORR() function
Date: Tue, 02 Dec 2025 12:22:07 -0500
Message-ID: <375068.1764696127@sss.pgh.pa.us> (raw)
In-Reply-To: <CAH1GMznwE=WGJYCZU1ou9iOmNR87NQFSRdYHqE3=JQ3as4P1hQ@mail.gmail.com>
References: <19340-6fb9f6637f562092@postgresql.org>
<4ab9867066e9545dd1a7e835a480bb0ecbe1a00d.camel@cybertec.at>
<CAH1GMznwE=WGJYCZU1ou9iOmNR87NQFSRdYHqE3=JQ3as4P1hQ@mail.gmail.com>
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 = <optimized out>
transvalues = 0x1b96da8
N = 25
Sxx = 3.2869204384208827e-34
Syy = 9.3266240309214617e-33
Sxy = -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 == 0 ||
Syy == 0)" will catch it).
You might find the previous discussion interesting:
https://www.postgresql.org/message-id/flat/153313051300.1397.9594490737341194671%40wrigleys.postgres...
regards, tom lane
view thread (24+ messages) latest in thread
Message-ID: <375068.1764696127@sss.pgh.pa.us>
Permalink: ../375068.1764696127@sss.pgh.pa.us/
Also on: postgresql.org/message-id/375068.1764696127@sss.pgh.pa.us
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, o15611@gmail.com, laurenz.albe@cybertec.at, dean.a.rasheed@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19340: Wrong result from CORR() function
In-Reply-To: <375068.1764696127@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox