Thread

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

Tom Lane <tgl@sss.pgh.pa.us> — 2025-12-02T17:22:07Z

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.postgresql.org

			regards, tom lane