postgres.git / summary / log / commit / refs
commit 7774249d0b66fff0b60823783db24090981020ac
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: Wed Aug 12 16:48:53 2026 +0000
First released in: 19.0
Fix float8_regr_accum() for Inf/NaN with constant other input.
Commit 649828769 improved this code to keep Sxx, Syy, and Sxy
exactly zero so long as we see only a single value of the input(s).
However, if any values of the other input are Inf or NaN, we'd
better set Sxy to NaN instead. Otherwise we risk reporting zero
variance when the result is really undefined. The old coding handled
this implicitly, but in the short-circuit path we have to take care
of it explicitly.
Bug: #19615
Reported-by: Junwen An <feasiblechart@gmail.com>
Author: Andrey Rachitskiy <pl0h0yp1@gmail.com>
Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
Discussion: https://postgr.es/m/19615-c7e390593416f6b6@postgresql.org
Backpatch-through: 19
src/backend/utils/adt/float.c | 11 ++++++++++
src/test/regress/expected/aggregates.out | 36 ++++++++++++++++++++++++++++++++
src/test/regress/sql/aggregates.sql | 12 +++++++++++
3 files changed, 59 insertions(+)
diff --git a/src/backend/utils/adt/float.c b/src/backend/utils/adt/float.c
index 4c2ccdfbf3f..fd7a6587132 100644
--- a/src/backend/utils/adt/float.c
+++ b/src/backend/utils/adt/float.c
@@ -3456,6 +3456,17 @@ 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))
+ {
+ /*
+ * If one input has been constant so far, but the other one is Inf
+ * or NaN this time, we must force Sxy to NaN to avoid falsely
+ * reporting variance zero (compare the special case for the first
+ * inputs, below). Sxx and Syy don't have this issue.
+ */
+ 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..7d07619956f 100644
--- a/src/test/regress/expected/aggregates.out
+++ b/src/test/regress/expected/aggregates.out
@@ -538,6 +538,42 @@ SELECT corr(1.3 + g * 1e-16, 1.3 + g * 1e-16)
(1 row)
+-- verify that we handle Inf/NaN the same regardless of position
+with data(x) as (values (1::float8),(2::float8),(3::float8))
+select covar_pop(x, 0::float8) from data;
+ covar_pop
+-----------
+ 0
+(1 row)
+
+with data(x) as (values ('Inf'::float8),(2::float8),(3::float8))
+select covar_pop(x, 0::float8) from data;
+ covar_pop
+-----------
+ NaN
+(1 row)
+
+with data(x) as (values (1::float8),('Inf'::float8),(3::float8))
+select covar_pop(x, 0::float8) from data;
+ covar_pop
+-----------
+ NaN
+(1 row)
+
+with data(x) as (values ('NaN'::float8),(2::float8),(3::float8))
+select covar_pop(x, 0::float8) from data;
+ covar_pop
+-----------
+ NaN
+(1 row)
+
+with data(x) as (values (1::float8),('NaN'::float8),(3::float8))
+select covar_pop(x, 0::float8) from data;
+ covar_pop
+-----------
+ NaN
+(1 row)
+
-- check some cases that formerly suffered from internal overflow/underflow
SELECT corr(1e-100 + g * 1e-105, 1e-100 + g * 1e-105),
regr_r2(1e-100 + g * 1e-105, 1e-100 + g * 1e-105)
diff --git a/src/test/regress/sql/aggregates.sql b/src/test/regress/sql/aggregates.sql
index b788152f0c2..91f8342166f 100644
--- a/src/test/regress/sql/aggregates.sql
+++ b/src/test/regress/sql/aggregates.sql
@@ -149,6 +149,18 @@ SELECT corr(g, 0.09), regr_r2(g, 0.09), regr_slope(g, 0.09), regr_intercept(g, 0
SELECT corr(1.3 + g * 1e-16, 1.3 + g * 1e-16)
FROM generate_series(1, 3) g;
+-- verify that we handle Inf/NaN the same regardless of position
+with data(x) as (values (1::float8),(2::float8),(3::float8))
+select covar_pop(x, 0::float8) from data;
+with data(x) as (values ('Inf'::float8),(2::float8),(3::float8))
+select covar_pop(x, 0::float8) from data;
+with data(x) as (values (1::float8),('Inf'::float8),(3::float8))
+select covar_pop(x, 0::float8) from data;
+with data(x) as (values ('NaN'::float8),(2::float8),(3::float8))
+select covar_pop(x, 0::float8) from data;
+with data(x) as (values (1::float8),('NaN'::float8),(3::float8))
+select covar_pop(x, 0::float8) from data;
+
-- check some cases that formerly suffered from internal overflow/underflow
SELECT corr(1e-100 + g * 1e-105, 1e-100 + g * 1e-105),
regr_r2(1e-100 + g * 1e-105, 1e-100 + g * 1e-105)
[parent: aaf67e4825f3]