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

The following bug has been logged on the website:

Bug reference: 19615
Logged by: Junwen An
Email address: feasiblechart@gmail.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@postgresql.org>:

The following bug has been logged on the website:

Bug reference: 19615
Logged by: Junwen An
Email address: feasiblechart@gmail.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

Andrey Rachitskiy <pl0h0yp1@gmail.com> writes:

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.

Yeah, I just arrived at pretty much the same conclusion. We get
Inf/NaN handling right for Sxx and Syy, but not for the cross-product
Sxy.

regards, tom lane

I wrote:

Yeah, I just arrived at pretty much the same conclusion. We get
Inf/NaN handling right for Sxx and Syy, but not for the cross-product
Sxy.

Pushed after some fooling with the comment and test cases.

regards, tom lane