Re: BUG #19615: COVAR_POP / COVAR_SAMP / REGR_SXY return 0.0 instead of NaN
От
Andrey Rachitskiy
Тема
Re: BUG #19615: COVAR_POP / COVAR_SAMP / REGR_SXY return 0.0 instead of NaN
Дата
Msg-id
CAB8bMiu6BYW2TfQPaVs759e+Sf6UpZ_Ag60TfuDi57npfcY-yg@mail.gmail.com
Список
Дерево обсуждения
Re: BUG #19615: COVAR_POP / COVAR_SAMP / REGR_SXY return 0.0 instead of NaN Tom Lane <tgl@sss.pgh.pa.us>
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;
```
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.
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.
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
Rachitskiy Andrey
В списке pgsql-bugs по дате отправления
От: Andrey Rachitskiy
Дата:
От: Tom Lane
Дата:
