Re: BUG #19615: COVAR_POP / COVAR_SAMP / REGR_SXY return 0.0 instead of NaN

Hi, Junwen!

Thanks for the report.

The cause is commit 6498287696d (BUG #19340). That change tracks
commonX/commonY and skips the Youngs-Cramer updates of Sxx/Syy/Sxy
while a column is still constant, so those sums stay exact zero for
corr() and friends. Sxy is updated only when both sides are already
marked non-constant:
```
if (isnan(commonX) && isnan(commonY))
Sxy += tmpX * tmpY * scale;
```
With a constant X and Inf arriving later in Y, commonX stays finite,
so that update is skipped and Sxy remains 0. Before the commit, Sxy
was always updated. With constant X, tmpX is ~0, so the product
0*Inf (or a tiny roundoff times Inf) produced NaN under IEEE rules.
Inf in the first row still yields NaN, because the older first-input
path from 33dd9bb3b0a is intact and forces Sxy to NaN up front.
The new short-circuit never got the matching Inf/NaN handling.
I do not think returning 0 here was intentional. The #19340
discussion was about finite constant inputs and roundoff. Dean's
note that covar_* should return exact zero for a constant column
was about that finite case.

The attached patch forces Sxy to NaN on that short-circuit path when
either new input is Inf or NaN, matching the first-input handling.
Finite constant inputs still produce exact zero. A regress case
based on Inf/NaN not in the first row is included.

вт, 11 авг. 2026 г. в 19:34, PG Bug reporting form <noreply(at)postgresql(dot)org>:

> The following bug has been logged on the website:
>
> Bug reference: 19615
> Logged by: Junwen An
> Email address: feasiblechart(at)gmail(dot)com
> PostgreSQL version: 19beta2
> Operating system: Linux Ubuntu
> Description:
>
> I found `COVAR_POP`/`REGR_SXY` returns 0.0 instead of NaN when one arg is
> constant and the other has Inf (not first), which might be unexpected. I
> could reproduce it on 19beta1, 19beta2, and 20devel, but not on 18.4.
>
> Minimal repro:
>
> CREATE TABLE t (y double precision);
> INSERT INTO t VALUES (3), ('Infinity'), (4);
>
> SELECT COVAR_POP(0::float8, y) FROM t;
> -- Expected 1 row: NaN
> -- Actual: 0.0
>
> SELECT COVAR_POP(y, 0::float8) FROM t;
> -- Expected 1 row: NaN
> -- Actual: 0.0
>
> SELECT COVAR_SAMP(0::float8, y) FROM t;
> -- Expected 1 row: NaN
> -- Actual: 0.0
>
> SELECT REGR_SXY(0::float8, y) FROM t;
> -- Expected 1 row: NaN
> -- Actual: 0.0
>
> Did some further experiments, and it seems this behavior also depends on
> the
> position of 'Inf'
>
> WITH t(ord, y) AS (
> VALUES
> (1, 'Infinity'::float8),
> (2, 3::float8),
> (3, 4::float8)
> )
> SELECT
> covar_pop(0::float8, y ORDER BY ord) AS inf_first,
> covar_pop(0::float8, y ORDER BY ord DESC) AS inf_last
> FROM t;
>
> inf_first | inf_last
> -----------+----------
> NaN | 0
>
>
>
>
вт, 11 авг. 2026 г. в 19:34, PG Bug reporting form <noreply(at)postgresql(dot)org>:

> The following bug has been logged on the website:
>
> Bug reference: 19615
> Logged by: Junwen An
> Email address: feasiblechart(at)gmail(dot)com
> PostgreSQL version: 19beta2
> Operating system: Linux Ubuntu
> Description:
>
> I found `COVAR_POP`/`REGR_SXY` returns 0.0 instead of NaN when one arg is
> constant and the other has Inf (not first), which might be unexpected. I
> could reproduce it on 19beta1, 19beta2, and 20devel, but not on 18.4.
>
> Minimal repro:
>
> CREATE TABLE t (y double precision);
> INSERT INTO t VALUES (3), ('Infinity'), (4);
>
> SELECT COVAR_POP(0::float8, y) FROM t;
> -- Expected 1 row: NaN
> -- Actual: 0.0
>
> SELECT COVAR_POP(y, 0::float8) FROM t;
> -- Expected 1 row: NaN
> -- Actual: 0.0
>
> SELECT COVAR_SAMP(0::float8, y) FROM t;
> -- Expected 1 row: NaN
> -- Actual: 0.0
>
> SELECT REGR_SXY(0::float8, y) FROM t;
> -- Expected 1 row: NaN
> -- Actual: 0.0
>
> Did some further experiments, and it seems this behavior also depends on
> the
> position of 'Inf'
>
> WITH t(ord, y) AS (
> VALUES
> (1, 'Infinity'::float8),
> (2, 3::float8),
> (3, 4::float8)
> )
> SELECT
> covar_pop(0::float8, y ORDER BY ord) AS inf_first,
> covar_pop(0::float8, y ORDER BY ord DESC) AS inf_last
> FROM t;
>
> inf_first | inf_last
> -----------+----------
> NaN | 0
>
>
>
>
>

--
Regards,
Rachitskiy Andrey