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
