agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedBUG #19615: COVAR_POP / COVAR_SAMP / REGR_SXY return 0.0 instead of NaN
4+ messages / 3 participants
[nested] [flat]
* BUG #19615: COVAR_POP / COVAR_SAMP / REGR_SXY return 0.0 instead of NaN
@ 2026-08-11 02:34 PG Bug reporting form <noreply@postgresql.org>
2026-08-11 15:24 ` Re: BUG #19615: COVAR_POP / COVAR_SAMP / REGR_SXY return 0.0 instead of NaN Andrey Rachitskiy <pl0h0yp1@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: PG Bug reporting form @ 2026-08-11 02:34 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: feasiblechart@gmail.com
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19615: COVAR_POP / COVAR_SAMP / REGR_SXY return 0.0 instead of NaN
2026-08-11 02:34 BUG #19615: COVAR_POP / COVAR_SAMP / REGR_SXY return 0.0 instead of NaN PG Bug reporting form <noreply@postgresql.org>
@ 2026-08-11 15:24 ` Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-08-11 15:56 ` Re: BUG #19615: COVAR_POP / COVAR_SAMP / REGR_SXY return 0.0 instead of NaN Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Andrey Rachitskiy @ 2026-08-11 15:24 UTC (permalink / raw)
To: feasiblechart@gmail.com; pgsql-bugs@lists.postgresql.org
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
Attachments:
[text/x-patch] 0001-fix-covar-inf-constant.patch (4.6K, ../../CAB8bMiu6BYW2TfQPaVs759e+Sf6UpZ_Ag60TfuDi57npfcY-yg@mail.gmail.com/3-0001-fix-covar-inf-constant.patch)
download | inline diff:
From: Andrey Rachitskiy <pl0h0yp1@gmail.com>
Date: Tue, 11 Aug 2026 19:53:33 +0500
Subject: [PATCH] Fix covar_pop/regr_sxy for Inf with a constant other input
Commit 6498287696d tracks commonX/commonY so that Sxx, Syy, and Sxy
stay exactly zero while a column is still constant. That fixed
roundoff problems in corr() and friends. It also skipped the
Youngs-Cramer update of Sxy whenever either side was still constant.
If the other argument later becomes Inf or NaN, the Youngs-Cramer
update we skip would have been the 0*Inf / 0*NaN product that
yields NaN. Sxy then remains 0, so covar_pop(), covar_samp(),
and regr_sxy() return 0 instead of NaN. Inf in the first row is
still fine, because the first-input path already forces Sxy to NaN.
Force Sxy to NaN in that short-circuit path when either new input is
Inf or NaN, matching the first-input handling. Finite constant
inputs still produce exact zero.
Reported-by: Junwen An <feasiblechart@gmail.com>
Author: Andrey Rachitskiy <pl0h0yp1@gmail.com>
---
src/backend/utils/adt/float.c | 7 +++++++
src/test/regress/expected/aggregates.out | 22 ++++++++++++++++++++++
src/test/regress/sql/aggregates.sql | 12 ++++++++++++
3 files changed, 41 insertions(+)
diff --git a/src/backend/utils/adt/float.c b/src/backend/utils/adt/float.c
index 4c2ccdfbf3f..aef03cfebb1 100644
--- a/src/backend/utils/adt/float.c
+++ b/src/backend/utils/adt/float.c
@@ -3449,6 +3449,10 @@ float8_regr_accum(PG_FUNCTION_ARGS)
* etc). Updating them would just create the possibility of injecting
* roundoff error, and we need exact zero results so that the final
* functions will return NULL in the right cases.
+ *
+ * Inf/NaN inputs must still force Sxy to NaN when the other variable
+ * is constant and the Sxy update is skipped. Otherwise we leave a
+ * false exact-zero Sxy. The first-input path below does the same.
*/
if (isnan(commonX))
Sxx += tmpX * tmpX * scale;
@@ -3456,6 +3460,9 @@ float8_regr_accum(PG_FUNCTION_ARGS)
Syy += tmpY * tmpY * scale;
if (isnan(commonX) && isnan(commonY))
Sxy += tmpX * tmpY * scale;
+ else if (isnan(newvalX) || isinf(newvalX) ||
+ isnan(newvalY) || isinf(newvalY))
+ Sxy = get_float8_nan();
/*
* Overflow check. We only report an overflow error when finite
diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out
index 5f0668382ed..82666a729cc 100644
--- a/src/test/regress/expected/aggregates.out
+++ b/src/test/regress/expected/aggregates.out
@@ -515,6 +515,28 @@ SELECT covar_pop(1::float8,'nan'::float8), covar_samp(3::float8,'nan'::float8);
NaN |
(1 row)
+-- Inf/NaN not first, other arg constant: must still yield NaN, not 0
+CREATE TEMP TABLE regr_inf_pos (ord int, y float8);
+INSERT INTO regr_inf_pos VALUES (1, 3), (2, 'Infinity'), (3, 4);
+SELECT covar_pop(0::float8, y ORDER BY ord),
+ covar_pop(y, 0::float8 ORDER BY ord),
+ regr_sxy(0::float8, y ORDER BY ord)
+ FROM regr_inf_pos;
+ covar_pop | covar_pop | regr_sxy
+-----------+-----------+----------
+ NaN | NaN | NaN
+(1 row)
+
+DELETE FROM regr_inf_pos;
+INSERT INTO regr_inf_pos VALUES (1, 3), (2, 'NaN'), (3, 4);
+SELECT covar_pop(0::float8, y ORDER BY ord) FROM regr_inf_pos;
+ covar_pop
+-----------
+ NaN
+(1 row)
+
+DROP TABLE regr_inf_pos;
+
-- check some cases that formerly had poor roundoff-error behavior
-- note: regr_r2() differs from corr() for a horizontal line, per spec
SELECT corr(0.09, g), regr_r2(0.09, g)
diff --git a/src/test/regress/sql/aggregates.sql b/src/test/regress/sql/aggregates.sql
index b788152f0c2..3c615f7dc60 100644
--- a/src/test/regress/sql/aggregates.sql
+++ b/src/test/regress/sql/aggregates.sql
@@ -140,6 +140,18 @@ SELECT covar_pop(1::float8,2::float8), covar_samp(3::float8,4::float8);
SELECT covar_pop(1::float8,'inf'::float8), covar_samp(3::float8,'inf'::float8);
SELECT covar_pop(1::float8,'nan'::float8), covar_samp(3::float8,'nan'::float8);
+-- Inf/NaN not first, other arg constant: must still yield NaN, not 0
+CREATE TEMP TABLE regr_inf_pos (ord int, y float8);
+INSERT INTO regr_inf_pos VALUES (1, 3), (2, 'Infinity'), (3, 4);
+SELECT covar_pop(0::float8, y ORDER BY ord),
+ covar_pop(y, 0::float8 ORDER BY ord),
+ regr_sxy(0::float8, y ORDER BY ord)
+ FROM regr_inf_pos;
+DELETE FROM regr_inf_pos;
+INSERT INTO regr_inf_pos VALUES (1, 3), (2, 'NaN'), (3, 4);
+SELECT covar_pop(0::float8, y ORDER BY ord) FROM regr_inf_pos;
+DROP TABLE regr_inf_pos;
+
-- check some cases that formerly had poor roundoff-error behavior
-- note: regr_r2() differs from corr() for a horizontal line, per spec
SELECT corr(0.09, g), regr_r2(0.09, g)
--
2.53.0
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19615: COVAR_POP / COVAR_SAMP / REGR_SXY return 0.0 instead of NaN
2026-08-11 02:34 BUG #19615: COVAR_POP / COVAR_SAMP / REGR_SXY return 0.0 instead of NaN PG Bug reporting form <noreply@postgresql.org>
2026-08-11 15:24 ` Re: BUG #19615: COVAR_POP / COVAR_SAMP / REGR_SXY return 0.0 instead of NaN Andrey Rachitskiy <pl0h0yp1@gmail.com>
@ 2026-08-11 15:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-08-12 16:50 ` Re: BUG #19615: COVAR_POP / COVAR_SAMP / REGR_SXY return 0.0 instead of NaN Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2026-08-11 15:56 UTC (permalink / raw)
To: Andrey Rachitskiy <pl0h0yp1@gmail.com>; +Cc: feasiblechart@gmail.com; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19615: COVAR_POP / COVAR_SAMP / REGR_SXY return 0.0 instead of NaN
2026-08-11 02:34 BUG #19615: COVAR_POP / COVAR_SAMP / REGR_SXY return 0.0 instead of NaN PG Bug reporting form <noreply@postgresql.org>
2026-08-11 15:24 ` Re: BUG #19615: COVAR_POP / COVAR_SAMP / REGR_SXY return 0.0 instead of NaN Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-08-11 15:56 ` Re: BUG #19615: COVAR_POP / COVAR_SAMP / REGR_SXY return 0.0 instead of NaN Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-08-12 16:50 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2026-08-12 16:50 UTC (permalink / raw)
To: Andrey Rachitskiy <pl0h0yp1@gmail.com>; +Cc: feasiblechart@gmail.com; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2026-08-12 16:50 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-08-11 02:34 BUG #19615: COVAR_POP / COVAR_SAMP / REGR_SXY return 0.0 instead of NaN PG Bug reporting form <noreply@postgresql.org>
2026-08-11 15:24 ` Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-08-11 15:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-08-12 16:50 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox