pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #19826: corr()/regr_r2() of constant input: NULL serially, 1 with partial aggregation
7+ messages / 3 participants
[nested] [flat]

Commits from this thread:
  2026-11-12 ef1b740441 30e70fa76f  Make float8_regr_combine() handle edge cases like float8_regr_accum().
* BUG #19826: corr()/regr_r2() of constant input: NULL serially, 1 with partial aggregation
@ 2026-11-02 15:36  PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 1 reply; 7+ messages in thread

From: PG Bug reporting form @ 2026-11-02 15:36 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: venu2805k@gmail.com

The following bug has been logged on the website:

Bug reference:      19826
Logged by:          Venu Kummari
Email address:      venu2805k@gmail.com
PostgreSQL version: 19rc1
Operating system:   Ubuntu 24.04, x86_64
Description:        

PostgreSQL version: master (20devel), tested on 633dee1b and 126976a8.
Operating system: Ubuntu 24.04, x86_64. Built from source with assertions.

Since commit 64982876 ("Handle constant inputs to corr() and related
aggregates more precisely"), corr(), regr_r2() and regr_slope() return NULL
for a constant input. They still return 1 when the aggregate is computed
with partial aggregation (partitionwise aggregate or parallel query), so the
same query gives a different answer depending on the plan:

CREATE TABLE e01 (k int, x float8) PARTITION BY LIST (k);
CREATE TABLE e01_1 PARTITION OF e01 FOR VALUES IN (1);
CREATE TABLE e01_2 PARTITION OF e01 FOR VALUES IN (2);
CREATE TABLE e01_3 PARTITION OF e01 FOR VALUES IN (3);
INSERT INTO e01 SELECT 1 + g % 3, 1.0 / 3 FROM generate_series(1, 80) g;
ANALYZE e01;

SELECT corr(x, x), regr_r2(x, x), regr_slope(x, x) FROM e01;
 corr | regr_r2 | regr_slope
------+---------+------------
      |         |
(1 row)

SET enable_partitionwise_aggregate = on;
SELECT corr(x, x), regr_r2(x, x), regr_slope(x, x) FROM e01;
 corr | regr_r2 | regr_slope
------+---------+------------
    1 |       1 |          1
(1 row)

The plan of the second query is Finalize Aggregate over Partial Aggregate
per partition. The same happens with Gather over Partial Aggregate (a
table of 300000 equal values, 4 workers): NULL with
max_parallel_workers_per_gather = 0, 1 with parallel workers.

Expected: NULL in both cases, as for the serial plan. PostgreSQL 18.6
returns 1 in both cases (the behaviour before 64982876), so the two plans
agree there.

Cause: float8_regr_accum() deliberately keeps Sxx, Syy and Sxy exactly
zero while all inputs are equal (the common values are tracked in
transvalues[6] and [7]). float8_regr_combine() still adds the correction
term N1 * N2 * (Sx1/N1 - Sx2/N2)^2 / N, which is not exactly zero because
of roundoff in Sx1/N1 and Sx2/N2, so the combined state has Sxx > 0 and the
final functions no longer see a constant input.

I have a patch that applies the same rule in float8_regr_combine() (add
a correction term only when Cx or Cy is NaN), with a regression test;
make check passes (239/239). I will send it as a reply to this report.

Found with a grammar-based fuzzer that compares query results across
planner settings.







^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: BUG #19826: corr()/regr_r2() of constant input: NULL serially, 1 with partial aggregation
@ 2026-11-02 15:39  Venu K <venu2805k@gmail.com>
  parent: PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 1 reply; 7+ messages in thread

From: Venu K @ 2026-11-02 15:39 UTC (permalink / raw)
  To: venu2805k@gmail.com; pgsql-bugs@lists.postgresql.org

Hi,

Attached is a patch for master.

float8_regr_combine() now computes the common values Cx and Cy first and
adds the correction terms to Sxx, Syy and Sxy only for a variable that is
not constant (Cx or Cy is NaN), which is the rule float8_regr_accum()
already follows. With the patch the reproducer returns NULL with both plans,
including the parallel case. Results for non-constant input are unchanged
(checked with partitionwise aggregation over 300 varying rows).

The patch adds a regression test to aggregates.sql: a partitionwise
aggregate over a constant column, with the plan shown so that the combine
path is exercised.

One existing expected output changes: the direct call

  float8_regr_combine('{3,60,200,750,20000,2000,7,8}'::float8[],
                      '{2,180,200,740,57800,-3400,7,9}'::float8[])

combines two hand-made states whose common X value (7) contradicts their
sums (Sx/N = 20 and 90), which real input cannot produce. With the patch
the combine trusts the common value, so Sxx becomes 400 instead of 6280.
If you prefer, I can change that test's input to consistent states instead.

make check passes (239/239) on master 633dee1b with the patch.

Regards,
Venu Kummari

On Mon, Nov 2, 2026 at 9:07 PM PG Bug reporting form
<noreply@postgresql.org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference:      19826
> Logged by:          Venu Kummari
> Email address:      venu2805k@gmail.com
> PostgreSQL version: 19rc1
> Operating system:   Ubuntu 24.04, x86_64
> Description:
>
> PostgreSQL version: master (20devel), tested on 633dee1b and 126976a8.
> Operating system: Ubuntu 24.04, x86_64. Built from source with assertions.
>
> Since commit 64982876 ("Handle constant inputs to corr() and related
> aggregates more precisely"), corr(), regr_r2() and regr_slope() return NULL
> for a constant input. They still return 1 when the aggregate is computed
> with partial aggregation (partitionwise aggregate or parallel query), so the
> same query gives a different answer depending on the plan:
>
> CREATE TABLE e01 (k int, x float8) PARTITION BY LIST (k);
> CREATE TABLE e01_1 PARTITION OF e01 FOR VALUES IN (1);
> CREATE TABLE e01_2 PARTITION OF e01 FOR VALUES IN (2);
> CREATE TABLE e01_3 PARTITION OF e01 FOR VALUES IN (3);
> INSERT INTO e01 SELECT 1 + g % 3, 1.0 / 3 FROM generate_series(1, 80) g;
> ANALYZE e01;
>
> SELECT corr(x, x), regr_r2(x, x), regr_slope(x, x) FROM e01;
>  corr | regr_r2 | regr_slope
> ------+---------+------------
>       |         |
> (1 row)
>
> SET enable_partitionwise_aggregate = on;
> SELECT corr(x, x), regr_r2(x, x), regr_slope(x, x) FROM e01;
>  corr | regr_r2 | regr_slope
> ------+---------+------------
>     1 |       1 |          1
> (1 row)
>
> The plan of the second query is Finalize Aggregate over Partial Aggregate
> per partition. The same happens with Gather over Partial Aggregate (a
> table of 300000 equal values, 4 workers): NULL with
> max_parallel_workers_per_gather = 0, 1 with parallel workers.
>
> Expected: NULL in both cases, as for the serial plan. PostgreSQL 18.6
> returns 1 in both cases (the behaviour before 64982876), so the two plans
> agree there.
>
> Cause: float8_regr_accum() deliberately keeps Sxx, Syy and Sxy exactly
> zero while all inputs are equal (the common values are tracked in
> transvalues[6] and [7]). float8_regr_combine() still adds the correction
> term N1 * N2 * (Sx1/N1 - Sx2/N2)^2 / N, which is not exactly zero because
> of roundoff in Sx1/N1 and Sx2/N2, so the combined state has Sxx > 0 and the
> final functions no longer see a constant input.
>
> I have a patch that applies the same rule in float8_regr_combine() (add
> a correction term only when Cx or Cy is NaN), with a regression test;
> make check passes (239/239). I will send it as a reply to this report.
>
> Found with a grammar-based fuzzer that compares query results across
> planner settings.
>
>

Attachments:

v1-0001-Keep-constant-input-sums-exact-in-float8_regr_combine.patch (application/octet-stream, 7.1K) download From 458a4ceacd2c90a33b9e5203988ae16c9e117262 Mon Sep 17 00:00:00 2001 From: Venu Kummari <venu2805k@gmail.com> Date: Mon, 2 Nov 2026 15:30:35 +0000 Subject: [PATCH] Keep constant-input sums exact in float8_regr_combine() Commit 64982876 taught float8_regr_accum() to leave Sxx, Syy and Sxy at exactly zero while all inputs seen so far are equal, so that corr(), regr_r2(), regr_slope() and friends return NULL for a constant input. float8_regr_combine() still added the correction terms N1 * N2 * (Sx1/N1 - Sx2/N2)^2 / N, which are not exactly zero because of roundoff in Sx1/N1 and Sx2/N2. With partial aggregation (parallel query or partitionwise aggregate) a constant input therefore gave Sxx > 0 and corr() = 1, while the serial plan gave NULL. Apply the same rule as float8_regr_accum(): add a correction term only for a variable whose common value is not known (Cx or Cy is NaN). One existing test combines two hand-made states whose common X value (7) contradicts their sums; its expected output changes accordingly. --- src/backend/utils/adt/float.c | 42 +++++++++++++++++------- src/test/regress/expected/aggregates.out | 41 ++++++++++++++++++++++- src/test/regress/sql/aggregates.sql | 18 ++++++++++ 3 files changed, 89 insertions(+), 12 deletions(-) diff --git a/src/backend/utils/adt/float.c b/src/backend/utils/adt/float.c index 38ef6daf..6e3b5c70 100644 --- a/src/backend/utils/adt/float.c +++ b/src/backend/utils/adt/float.c @@ -3650,18 +3650,7 @@ float8_regr_combine(PG_FUNCTION_ARGS) { N = N1 + N2; Sx = float8_pl(Sx1, Sx2); - tmp1 = Sx1 / N1 - Sx2 / N2; - Sxx = Sxx1 + Sxx2 + N1 * N2 * tmp1 * tmp1 / N; - if (unlikely(isinf(Sxx)) && !isinf(Sxx1) && !isinf(Sxx2)) - float_overflow_error(); Sy = float8_pl(Sy1, Sy2); - tmp2 = Sy1 / N1 - Sy2 / N2; - Syy = Syy1 + Syy2 + N1 * N2 * tmp2 * tmp2 / N; - if (unlikely(isinf(Syy)) && !isinf(Syy1) && !isinf(Syy2)) - float_overflow_error(); - Sxy = Sxy1 + Sxy2 + N1 * N2 * tmp1 * tmp2 / N; - if (unlikely(isinf(Sxy)) && !isinf(Sxy1) && !isinf(Sxy2)) - float_overflow_error(); if (float8_eq(Cx1, Cx2)) Cx = Cx1; else @@ -3670,6 +3659,37 @@ float8_regr_combine(PG_FUNCTION_ARGS) Cy = Cy1; else Cy = get_float8_nan(); + + /* + * As in float8_regr_accum, if all inputs seen so far have the same + * X (or Y) value, the corresponding sums of squares must remain + * exactly what they were (zero, or NaN): Sx1/N1 and Sx2/N2 are only + * approximately equal because of roundoff, so adding the correction + * term would make the final functions miss a constant input. + */ + Sxx = Sxx1 + Sxx2; + Syy = Syy1 + Syy2; + Sxy = Sxy1 + Sxy2; + tmp1 = Sx1 / N1 - Sx2 / N2; + tmp2 = Sy1 / N1 - Sy2 / N2; + if (isnan(Cx)) + { + Sxx += N1 * N2 * tmp1 * tmp1 / N; + if (unlikely(isinf(Sxx)) && !isinf(Sxx1) && !isinf(Sxx2)) + float_overflow_error(); + } + if (isnan(Cy)) + { + Syy += N1 * N2 * tmp2 * tmp2 / N; + if (unlikely(isinf(Syy)) && !isinf(Syy1) && !isinf(Syy2)) + float_overflow_error(); + } + if (isnan(Cx) && isnan(Cy)) + { + Sxy += N1 * N2 * tmp1 * tmp2 / N; + if (unlikely(isinf(Sxy)) && !isinf(Sxy1) && !isinf(Sxy2)) + float_overflow_error(); + } } /* diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out index 7d076199..049f7b93 100644 --- a/src/test/regress/expected/aggregates.out +++ b/src/test/regress/expected/aggregates.out @@ -637,6 +637,45 @@ SELECT corr('NaN', 'NaN'), regr_r2('NaN', 'NaN') FROM generate_series(1, 30) g; NaN | NaN (1 row) +-- constant inputs must give the same results when partial aggregation +-- states are combined (float8_regr_combine) +CREATE TABLE regr_const (k int, x float8) PARTITION BY LIST (k); +CREATE TABLE regr_const_1 PARTITION OF regr_const FOR VALUES IN (1); +CREATE TABLE regr_const_2 PARTITION OF regr_const FOR VALUES IN (2); +CREATE TABLE regr_const_3 PARTITION OF regr_const FOR VALUES IN (3); +INSERT INTO regr_const SELECT 1 + g % 3, 1.0 / 3 FROM generate_series(1, 80) g; +ANALYZE regr_const; +SET enable_partitionwise_aggregate = on; +EXPLAIN (COSTS OFF) +SELECT corr(x, x) FROM regr_const; + QUERY PLAN +--------------------------------------------------------- + Finalize Aggregate + -> Append + -> Partial Aggregate + -> Seq Scan on regr_const_1 regr_const + -> Partial Aggregate + -> Seq Scan on regr_const_2 regr_const_1 + -> Partial Aggregate + -> Seq Scan on regr_const_3 regr_const_2 +(8 rows) + +SELECT corr(x, x), regr_r2(x, x), regr_slope(x, x), regr_sxx(x, x), regr_syy(x, x) +FROM regr_const; + corr | regr_r2 | regr_slope | regr_sxx | regr_syy +------+---------+------------+----------+---------- + | | | 0 | 0 +(1 row) + +RESET enable_partitionwise_aggregate; +SELECT corr(x, x), regr_r2(x, x), regr_slope(x, x), regr_sxx(x, x), regr_syy(x, x) +FROM regr_const; + corr | regr_r2 | regr_slope | regr_sxx | regr_syy +------+---------+------------+----------+---------- + | | | 0 | 0 +(1 row) + +DROP TABLE regr_const; -- test accum and combine functions directly CREATE TABLE regr_test (x float8, y float8); INSERT INTO regr_test VALUES (10,150),(20,250),(30,350),(80,540),(100,200); @@ -716,7 +755,7 @@ SELECT float8_regr_combine('{3,60,200,750,20000,2000,7,8}'::float8[], '{2,180,200,740,57800,-3400,7,9}'::float8[]); float8_regr_combine ------------------------------------ - {5,240,6280,1490,95080,8680,7,NaN} + {5,240,400,1490,95080,-1400,7,NaN} (1 row) DROP TABLE regr_test; diff --git a/src/test/regress/sql/aggregates.sql b/src/test/regress/sql/aggregates.sql index 91f83421..7d4f0f3d 100644 --- a/src/test/regress/sql/aggregates.sql +++ b/src/test/regress/sql/aggregates.sql @@ -181,6 +181,24 @@ SELECT corr(0.1, 'NaN'), regr_r2(0.1, 'NaN') FROM generate_series(1, 30) g; SELECT corr('NaN', 0.1), regr_r2('NaN', 0.1) FROM generate_series(1, 30) g; SELECT corr('NaN', 'NaN'), regr_r2('NaN', 'NaN') FROM generate_series(1, 30) g; +-- constant inputs must give the same results when partial aggregation +-- states are combined (float8_regr_combine) +CREATE TABLE regr_const (k int, x float8) PARTITION BY LIST (k); +CREATE TABLE regr_const_1 PARTITION OF regr_const FOR VALUES IN (1); +CREATE TABLE regr_const_2 PARTITION OF regr_const FOR VALUES IN (2); +CREATE TABLE regr_const_3 PARTITION OF regr_const FOR VALUES IN (3); +INSERT INTO regr_const SELECT 1 + g % 3, 1.0 / 3 FROM generate_series(1, 80) g; +ANALYZE regr_const; +SET enable_partitionwise_aggregate = on; +EXPLAIN (COSTS OFF) +SELECT corr(x, x) FROM regr_const; +SELECT corr(x, x), regr_r2(x, x), regr_slope(x, x), regr_sxx(x, x), regr_syy(x, x) +FROM regr_const; +RESET enable_partitionwise_aggregate; +SELECT corr(x, x), regr_r2(x, x), regr_slope(x, x), regr_sxx(x, x), regr_syy(x, x) +FROM regr_const; +DROP TABLE regr_const; + -- test accum and combine functions directly CREATE TABLE regr_test (x float8, y float8); INSERT INTO regr_test VALUES (10,150),(20,250),(30,350),(80,540),(100,200); -- 2.43.0
^ permalink raw reply [nested|flat] 7+ messages in thread

* Re: BUG #19826: corr()/regr_r2() of constant input: NULL serially, 1 with partial aggregation
@ 2026-11-03 03:03  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Venu K <venu2805k@gmail.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Tom Lane @ 2026-11-03 03:03 UTC (permalink / raw)
  To: Venu K <venu2805k@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org

Venu K <venu2805k@gmail.com> writes:
> Attached is a patch for master.

Hm, the actual bug report hasn't come through yet, but I gather your
point is that float8_regr_combine hasn't gotten the word about the
efforts we've made in float8_regr_accum to avoid roundoff error for
all-the-same inputs.  Good point, but isn't it also missing the latter
routine's recently-added logic for forcing outputs to NaN when
necessary?

Also, TBH, the proposed comments read like AI slop; they don't
address any of the questions I had on first reading the patch.
Some more effort seems appropriate there.

			regards, tom lane






^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: BUG #19826: corr()/regr_r2() of constant input: NULL serially, 1 with partial aggregation
@ 2026-11-03 05:23  Venu K <venu2805k@gmail.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 7+ messages in thread

From: Venu K @ 2026-11-03 05:23 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org

On Mon, Nov 3, 2026 at 8:33 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Hm, the actual bug report hasn't come through yet, but I gather your
> point is that float8_regr_combine hasn't gotten the word about the
> efforts we've made in float8_regr_accum to avoid roundoff error for
> all-the-same inputs.

Right.  It's not only corr(): with 300000 identical rows and a parallel
plan, regr_slope(y, x) gives -1.2e17 and covar_pop(x, y) -2e-08, where
the serial plan gives NULL and 0.

> Good point, but isn't it also missing the latter routine's recently-added
> logic for forcing outputs to NaN when necessary?

Yes.  Once the Sxy correction is skipped for a constant input, the NaN
that 0 * Inf used to produce is gone.  v2 forces Sxy to NaN in that case,
as float8_regr_accum does since 7774249d0.  The combine function can't
see the inputs, so it tests Sx and Sy instead.

In practice a partial state that has seen an Inf or NaN already has
Sxy = NaN, so I couldn't get a different result from SQL either way, but
I don't think the combine function should depend on that.

> Also, TBH, the proposed comments read like AI slop; they don't
> address any of the questions I had on first reading the patch.

Rewritten.  The comment now explains why dropping the term is right:
both means are exactly c, but Sx1 and Sx2 are rounded sums, so their
computed difference isn't zero and leaves Sxx slightly positive.  The
commit message also mentions 649828769 and 7774249d0.

The regression test compares partitionwise aggregation against the
serial plan, for a constant input and for Inf/NaN in one partition.

One existing expected output changes: the direct float8_regr_combine()
call in aggregates.sql passes two states with the same common X (7) but
different means, so Sxx is now 400 rather than 6280.  Such states can't
come from real input; I can change the test input instead if you prefer.

v2 attached; make check passes on master.

Regards,
Venu

On Tue, Nov 3, 2026 at 8:33 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Venu K <venu2805k@gmail.com> writes:
> > Attached is a patch for master.
>
> Hm, the actual bug report hasn't come through yet, but I gather your
> point is that float8_regr_combine hasn't gotten the word about the
> efforts we've made in float8_regr_accum to avoid roundoff error for
> all-the-same inputs.  Good point, but isn't it also missing the latter
> routine's recently-added logic for forcing outputs to NaN when
> necessary?
>
> Also, TBH, the proposed comments read like AI slop; they don't
> address any of the questions I had on first reading the patch.
> Some more effort seems appropriate there.
>
>                         regards, tom lane

Attachments:

v2-0001-Make-float8_regr_combine-handle-constant-inputs-l (1).patch (application/octet-stream, 8.8K) download From 61345ff3ab1a6f0b72920fae9d9f6ce62118a227 Mon Sep 17 00:00:00 2001 From: Venu Kummari <venu2805k@gmail.com> Date: Mon, 2 Nov 2026 15:30:35 +0000 Subject: [PATCH v2] Make float8_regr_combine() handle constant inputs like float8_regr_accum(). Commit 649828769 taught float8_regr_accum() to keep Sxx, Syy and Sxy exactly zero while all the inputs for X (or Y) are the same, so that corr() and the regr_*() functions return NULL for a constant input, and 7774249d0 made it force Sxy to NaN when one input is constant and the other is Inf or NaN. float8_regr_combine() got neither change: it still adds the correction terms computed from the rounded sums Sx1 and Sx2, which are usually slightly nonzero even when both states saw the same single value. So corr() of a constant column returned 1 instead of NULL with parallel or partitionwise aggregation, and regr_slope() returned huge values. Skip each correction term when the corresponding common value is known, and force Sxy to NaN when the Sxy correction is skipped and the other input has seen an Inf or NaN. Bug: #19826 --- src/backend/utils/adt/float.c | 53 ++++++++++++++++---- src/test/regress/expected/aggregates.out | 64 +++++++++++++++++++++++- src/test/regress/sql/aggregates.sql | 26 ++++++++++ 3 files changed, 131 insertions(+), 12 deletions(-) diff --git a/src/backend/utils/adt/float.c b/src/backend/utils/adt/float.c index 38ef6daf2..d2a756eb5 100644 --- a/src/backend/utils/adt/float.c +++ b/src/backend/utils/adt/float.c @@ -3650,18 +3650,7 @@ float8_regr_combine(PG_FUNCTION_ARGS) { N = N1 + N2; Sx = float8_pl(Sx1, Sx2); - tmp1 = Sx1 / N1 - Sx2 / N2; - Sxx = Sxx1 + Sxx2 + N1 * N2 * tmp1 * tmp1 / N; - if (unlikely(isinf(Sxx)) && !isinf(Sxx1) && !isinf(Sxx2)) - float_overflow_error(); Sy = float8_pl(Sy1, Sy2); - tmp2 = Sy1 / N1 - Sy2 / N2; - Syy = Syy1 + Syy2 + N1 * N2 * tmp2 * tmp2 / N; - if (unlikely(isinf(Syy)) && !isinf(Syy1) && !isinf(Syy2)) - float_overflow_error(); - Sxy = Sxy1 + Sxy2 + N1 * N2 * tmp1 * tmp2 / N; - if (unlikely(isinf(Sxy)) && !isinf(Sxy1) && !isinf(Sxy2)) - float_overflow_error(); if (float8_eq(Cx1, Cx2)) Cx = Cx1; else @@ -3670,6 +3659,48 @@ float8_regr_combine(PG_FUNCTION_ARGS) Cy = Cy1; else Cy = get_float8_nan(); + + /* + * As in float8_regr_accum, if we have not seen distinct inputs for X + * (or Y) in either state, the corresponding sums should remain exactly + * zero: both means are then exactly Cx, and computing the correction + * term from the rounded sums Sx1 and Sx2 would just inject roundoff + * error. We need exact zero results so that the final functions will + * return NULL in the right cases. + * + * If we skip the Sxy correction because only one input is constant, + * we must still force Sxy to NaN if the other input has seen an Inf + * or NaN, as float8_regr_accum does. We can't see the inputs here, + * but any such value makes Sx or Sy Inf or NaN. + */ + tmp1 = Sx1 / N1 - Sx2 / N2; + tmp2 = Sy1 / N1 - Sy2 / N2; + + Sxx = Sxx1 + Sxx2; + if (isnan(Cx)) + { + Sxx += N1 * N2 * tmp1 * tmp1 / N; + if (unlikely(isinf(Sxx)) && !isinf(Sxx1) && !isinf(Sxx2)) + float_overflow_error(); + } + + Syy = Syy1 + Syy2; + if (isnan(Cy)) + { + Syy += N1 * N2 * tmp2 * tmp2 / N; + if (unlikely(isinf(Syy)) && !isinf(Syy1) && !isinf(Syy2)) + float_overflow_error(); + } + + Sxy = Sxy1 + Sxy2; + if (isnan(Cx) && isnan(Cy)) + { + Sxy += N1 * N2 * tmp1 * tmp2 / N; + if (unlikely(isinf(Sxy)) && !isinf(Sxy1) && !isinf(Sxy2)) + float_overflow_error(); + } + else if (isnan(Sx) || isinf(Sx) || isnan(Sy) || isinf(Sy)) + Sxy = get_float8_nan(); } /* diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out index 7d0761995..487c58d19 100644 --- a/src/test/regress/expected/aggregates.out +++ b/src/test/regress/expected/aggregates.out @@ -637,6 +637,68 @@ SELECT corr('NaN', 'NaN'), regr_r2('NaN', 'NaN') FROM generate_series(1, 30) g; NaN | NaN (1 row) +-- float8_regr_combine must treat constant inputs, and Inf/NaN with a +-- constant other input, the same way float8_regr_accum does, so that +-- partial aggregation gives the same results as a serial plan +CREATE TABLE regr_part (k int, x float8, y float8) PARTITION BY LIST (k); +CREATE TABLE regr_part_1 PARTITION OF regr_part FOR VALUES IN (1); +CREATE TABLE regr_part_2 PARTITION OF regr_part FOR VALUES IN (2); +CREATE TABLE regr_part_3 PARTITION OF regr_part FOR VALUES IN (3); +INSERT INTO regr_part SELECT 1 + g % 3, 1.0 / 3, g FROM generate_series(1, 80) g; +ANALYZE regr_part; +SET enable_partitionwise_aggregate = on; +EXPLAIN (COSTS OFF) +SELECT corr(x, x) FROM regr_part; + QUERY PLAN +------------------------------------------------------- + Finalize Aggregate + -> Append + -> Partial Aggregate + -> Seq Scan on regr_part_1 regr_part + -> Partial Aggregate + -> Seq Scan on regr_part_2 regr_part_1 + -> Partial Aggregate + -> Seq Scan on regr_part_3 regr_part_2 +(8 rows) + +SELECT corr(x, x), regr_r2(x, x), regr_slope(y, x), regr_sxx(y, x), covar_pop(x, y) +FROM regr_part; + corr | regr_r2 | regr_slope | regr_sxx | covar_pop +------+---------+------------+----------+----------- + | | | 0 | 0 +(1 row) + +-- an Inf or NaN in one partition, the other input constant +INSERT INTO regr_part VALUES (2, 'Infinity', 1); +SELECT covar_pop(x, 0::float8), covar_pop(0::float8, x) FROM regr_part; + covar_pop | covar_pop +-----------+----------- + NaN | NaN +(1 row) + +UPDATE regr_part SET x = 'NaN' WHERE x = 'Infinity'; +SELECT covar_pop(x, 0::float8), covar_pop(0::float8, x) FROM regr_part; + covar_pop | covar_pop +-----------+----------- + NaN | NaN +(1 row) + +RESET enable_partitionwise_aggregate; +SELECT covar_pop(x, 0::float8), covar_pop(0::float8, x) FROM regr_part; + covar_pop | covar_pop +-----------+----------- + NaN | NaN +(1 row) + +DELETE FROM regr_part WHERE x = 'NaN'; +SELECT corr(x, x), regr_r2(x, x), regr_slope(y, x), regr_sxx(y, x), covar_pop(x, y) +FROM regr_part; + corr | regr_r2 | regr_slope | regr_sxx | covar_pop +------+---------+------------+----------+----------- + | | | 0 | 0 +(1 row) + +DROP TABLE regr_part; -- test accum and combine functions directly CREATE TABLE regr_test (x float8, y float8); INSERT INTO regr_test VALUES (10,150),(20,250),(30,350),(80,540),(100,200); @@ -716,7 +778,7 @@ SELECT float8_regr_combine('{3,60,200,750,20000,2000,7,8}'::float8[], '{2,180,200,740,57800,-3400,7,9}'::float8[]); float8_regr_combine ------------------------------------ - {5,240,6280,1490,95080,8680,7,NaN} + {5,240,400,1490,95080,-1400,7,NaN} (1 row) DROP TABLE regr_test; diff --git a/src/test/regress/sql/aggregates.sql b/src/test/regress/sql/aggregates.sql index 91f834216..ea65b57c0 100644 --- a/src/test/regress/sql/aggregates.sql +++ b/src/test/regress/sql/aggregates.sql @@ -181,6 +181,32 @@ SELECT corr(0.1, 'NaN'), regr_r2(0.1, 'NaN') FROM generate_series(1, 30) g; SELECT corr('NaN', 0.1), regr_r2('NaN', 0.1) FROM generate_series(1, 30) g; SELECT corr('NaN', 'NaN'), regr_r2('NaN', 'NaN') FROM generate_series(1, 30) g; +-- float8_regr_combine must treat constant inputs, and Inf/NaN with a +-- constant other input, the same way float8_regr_accum does, so that +-- partial aggregation gives the same results as a serial plan +CREATE TABLE regr_part (k int, x float8, y float8) PARTITION BY LIST (k); +CREATE TABLE regr_part_1 PARTITION OF regr_part FOR VALUES IN (1); +CREATE TABLE regr_part_2 PARTITION OF regr_part FOR VALUES IN (2); +CREATE TABLE regr_part_3 PARTITION OF regr_part FOR VALUES IN (3); +INSERT INTO regr_part SELECT 1 + g % 3, 1.0 / 3, g FROM generate_series(1, 80) g; +ANALYZE regr_part; +SET enable_partitionwise_aggregate = on; +EXPLAIN (COSTS OFF) +SELECT corr(x, x) FROM regr_part; +SELECT corr(x, x), regr_r2(x, x), regr_slope(y, x), regr_sxx(y, x), covar_pop(x, y) +FROM regr_part; +-- an Inf or NaN in one partition, the other input constant +INSERT INTO regr_part VALUES (2, 'Infinity', 1); +SELECT covar_pop(x, 0::float8), covar_pop(0::float8, x) FROM regr_part; +UPDATE regr_part SET x = 'NaN' WHERE x = 'Infinity'; +SELECT covar_pop(x, 0::float8), covar_pop(0::float8, x) FROM regr_part; +RESET enable_partitionwise_aggregate; +SELECT covar_pop(x, 0::float8), covar_pop(0::float8, x) FROM regr_part; +DELETE FROM regr_part WHERE x = 'NaN'; +SELECT corr(x, x), regr_r2(x, x), regr_slope(y, x), regr_sxx(y, x), covar_pop(x, y) +FROM regr_part; +DROP TABLE regr_part; + -- test accum and combine functions directly CREATE TABLE regr_test (x float8, y float8); INSERT INTO regr_test VALUES (10,150),(20,250),(30,350),(80,540),(100,200); -- 2.43.0
^ permalink raw reply [nested|flat] 7+ messages in thread

* Re: BUG #19826: corr()/regr_r2() of constant input: NULL serially, 1 with partial aggregation
@ 2026-11-11 20:23  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Venu K <venu2805k@gmail.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Tom Lane @ 2026-11-11 20:23 UTC (permalink / raw)
  To: Venu K <venu2805k@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org

Venu K <venu2805k@gmail.com> writes:
> v2 attached; make check passes on master.

I studied this a bit, and remained unhappy about one point: it's
not clear that float8_regr_combine() has the same behavior for
overflow cases as float8_regr_accum().  Maybe it does, but I don't
think readers should have to spend time convincing themselves of that.
I suggest adjusting it to use the same block of overflow-handling
logic that float8_regr_accum() has, as in v3 attached.  Does that
seem sane to you?

> In practice a partial state that has seen an Inf or NaN already has
> Sxy = NaN, so I couldn't get a different result from SQL either way, but
> I don't think the combine function should depend on that.

Right, I'm with you on that.  I suspect that the last few lines in
my v3,

+			if (isinf(Sxx))
+				Sxx = get_float8_nan();
+			if (isinf(Syy))
+				Syy = get_float8_nan();
+			if (isinf(Sxy))
+				Sxy = get_float8_nan();

are dead code for the same reason, but again that doesn't seem
like something to depend on, especially in a spot like this
that can't be at all performance-critical.

> One existing expected output changes: the direct float8_regr_combine()
> call in aggregates.sql passes two states with the same common X (7) but
> different means, so Sxx is now 400 rather than 6280.  Such states can't
> come from real input; I can change the test input instead if you prefer.

Yeah, it seems clear that that test case is just made-up data,
probably put there just so that float8_regr_combine() shows as having
some coverage in our coverage tests.  I wonder whether the new test
doesn't render it useless even for that.  It might be better to change
it to exercise something your new test still doesn't reach, like an
overflow error.  I didn't do anything about that in v3 though.

			regards, tom lane

Attachments:

v3-0001-Make-float8_regr_combine-handle-edge-cases-like-f.patch (text/x-diff, 9.7K) download From 001df21bc29374b191981333f31ee639dc848952 Mon Sep 17 00:00:00 2001 From: Tom Lane <tgl@sss.pgh.pa.us> Date: Wed, 11 Nov 2026 15:08:56 -0500 Subject: [PATCH v3] Make float8_regr_combine() handle edge cases like float8_regr_accum(). Commit 649828769 taught float8_regr_accum() to keep Sxx, Syy and Sxy exactly zero while all the inputs for X (or Y) are the same, so that corr() and the regr_*() functions return NULL for a constant input. Then 7774249d0 made it force Sxy to NaN when one input is constant and the other is Inf or NaN. float8_regr_combine() got neither change: it still added the correction terms computed from the rounded sums Sx1 and Sx2, which are usually slightly nonzero even when both states saw the same single value. Thus, the edge cases those commits intended to fix occurred anyway under parallel aggregation. Skip each correction term when the corresponding input(s) have a common value, and force Sxy to NaN when the Sxy correction is skipped and the other input has seen an Inf or NaN. Also, make the overflow handling in float8_regr_combine() look more like float8_regr_accum(). It might have worked out the same in terms of when we throw error or don't, but even if that's true, proving it and then keeping it true in the future requires brain cells readers shouldn't have to expend. Bug: #19826 Reported-by: Venu Kummari <venu2805k@gmail.com> Author: Venu Kummari <venu2805k@gmail.com> Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us> Discussion: https://postgr.es/m/19826-4b769cb5342fdfe7@postgresql.org Backpatch-through: 19 --- src/backend/utils/adt/float.c | 72 +++++++++++++++++++----- src/test/regress/expected/aggregates.out | 64 ++++++++++++++++++++- src/test/regress/sql/aggregates.sql | 26 +++++++++ 3 files changed, 148 insertions(+), 14 deletions(-) diff --git a/src/backend/utils/adt/float.c b/src/backend/utils/adt/float.c index 38ef6daf290..f2fe0f5b495 100644 --- a/src/backend/utils/adt/float.c +++ b/src/backend/utils/adt/float.c @@ -3649,19 +3649,10 @@ float8_regr_combine(PG_FUNCTION_ARGS) else { N = N1 + N2; - Sx = float8_pl(Sx1, Sx2); - tmp1 = Sx1 / N1 - Sx2 / N2; - Sxx = Sxx1 + Sxx2 + N1 * N2 * tmp1 * tmp1 / N; - if (unlikely(isinf(Sxx)) && !isinf(Sxx1) && !isinf(Sxx2)) - float_overflow_error(); - Sy = float8_pl(Sy1, Sy2); - tmp2 = Sy1 / N1 - Sy2 / N2; - Syy = Syy1 + Syy2 + N1 * N2 * tmp2 * tmp2 / N; - if (unlikely(isinf(Syy)) && !isinf(Syy1) && !isinf(Syy2)) - float_overflow_error(); - Sxy = Sxy1 + Sxy2 + N1 * N2 * tmp1 * tmp2 / N; - if (unlikely(isinf(Sxy)) && !isinf(Sxy1) && !isinf(Sxy2)) - float_overflow_error(); + Sx = Sx1 + Sx2; + Sy = Sy1 + Sy2; + + /* Check to see if we have seen distinct inputs */ if (float8_eq(Cx1, Cx2)) Cx = Cx1; else @@ -3670,6 +3661,61 @@ float8_regr_combine(PG_FUNCTION_ARGS) Cy = Cy1; else Cy = get_float8_nan(); + + tmp1 = Sx1 / N1 - Sx2 / N2; + tmp2 = Sy1 / N1 - Sy2 / N2; + + /* + * As in float8_regr_accum, if we have not seen distinct inputs for X + * (or Y) in either state, the corresponding sums should remain + * exactly zero: both means are then exactly Cx, and computing the + * correction terms from the rounded sums Sx1 and Sx2 would just + * inject roundoff error. We need exact zero results so that the + * final functions will return NULL in the right cases. + */ + Sxx = Sxx1 + Sxx2; + if (isnan(Cx)) + Sxx += N1 * N2 * tmp1 * tmp1 / N; + Syy = Syy1 + Syy2; + if (isnan(Cy)) + Syy += N1 * N2 * tmp2 * tmp2 / N; + Sxy = Sxy1 + Sxy2; + if (isnan(Cx) && isnan(Cy)) + Sxy += N1 * N2 * tmp1 * tmp2 / N; + else if (isnan(Sx) || isinf(Sx) || + isnan(Sy) || isinf(Sy)) + { + /* + * If one input has been constant so far, but the other one has + * been Inf or NaN, we must force Sxy to NaN to avoid falsely + * reporting variance zero. (We can't see individual X/Y values + * from here, but if any one has been Inf or NaN, the sum Sx/Sy + * must now be Inf or NaN.) Sxx and Syy don't have this issue. + */ + Sxy = get_float8_nan(); + } + + /* + * Overflow check, same logic as in float8_regr_accum. + */ + if (isinf(Sx) || isinf(Sxx) || isinf(Sy) || isinf(Syy) || isinf(Sxy)) + { + if (((isinf(Sx) || isinf(Sxx)) && + !isinf(Sx1) && !isinf(Sx2)) || + ((isinf(Sy) || isinf(Syy)) && + !isinf(Sy1) && !isinf(Sy2)) || + (isinf(Sxy) && + !isinf(Sx1) && !isinf(Sx2) && + !isinf(Sy1) && !isinf(Sy2))) + float_overflow_error(); + + if (isinf(Sxx)) + Sxx = get_float8_nan(); + if (isinf(Syy)) + Syy = get_float8_nan(); + if (isinf(Sxy)) + Sxy = get_float8_nan(); + } } /* diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out index ce555a31693..4f57b616db5 100644 --- a/src/test/regress/expected/aggregates.out +++ b/src/test/regress/expected/aggregates.out @@ -637,6 +637,68 @@ SELECT corr('NaN', 'NaN'), regr_r2('NaN', 'NaN') FROM generate_series(1, 30) g; NaN | NaN (1 row) +-- float8_regr_combine must treat constant inputs, and Inf/NaN with a +-- constant other input, the same way float8_regr_accum does, so that +-- partial aggregation gives the same results as a serial plan +CREATE TABLE regr_part (k int, x float8, y float8) PARTITION BY LIST (k); +CREATE TABLE regr_part_1 PARTITION OF regr_part FOR VALUES IN (1); +CREATE TABLE regr_part_2 PARTITION OF regr_part FOR VALUES IN (2); +CREATE TABLE regr_part_3 PARTITION OF regr_part FOR VALUES IN (3); +INSERT INTO regr_part SELECT 1 + g % 3, 1.0 / 3, g FROM generate_series(1, 80) g; +ANALYZE regr_part; +SET enable_partitionwise_aggregate = on; +EXPLAIN (COSTS OFF) +SELECT corr(x, x) FROM regr_part; + QUERY PLAN +------------------------------------------------------- + Finalize Aggregate + -> Append + -> Partial Aggregate + -> Seq Scan on regr_part_1 regr_part + -> Partial Aggregate + -> Seq Scan on regr_part_2 regr_part_1 + -> Partial Aggregate + -> Seq Scan on regr_part_3 regr_part_2 +(8 rows) + +SELECT corr(x, x), regr_r2(x, x), regr_slope(y, x), regr_sxx(y, x), covar_pop(x, y) +FROM regr_part; + corr | regr_r2 | regr_slope | regr_sxx | covar_pop +------+---------+------------+----------+----------- + | | | 0 | 0 +(1 row) + +-- an Inf or NaN in one partition, the other input constant +INSERT INTO regr_part VALUES (2, 'Infinity', 1); +SELECT covar_pop(x, 0::float8), covar_pop(0::float8, x) FROM regr_part; + covar_pop | covar_pop +-----------+----------- + NaN | NaN +(1 row) + +UPDATE regr_part SET x = 'NaN' WHERE x = 'Infinity'; +SELECT covar_pop(x, 0::float8), covar_pop(0::float8, x) FROM regr_part; + covar_pop | covar_pop +-----------+----------- + NaN | NaN +(1 row) + +RESET enable_partitionwise_aggregate; +SELECT covar_pop(x, 0::float8), covar_pop(0::float8, x) FROM regr_part; + covar_pop | covar_pop +-----------+----------- + NaN | NaN +(1 row) + +DELETE FROM regr_part WHERE x = 'NaN'; +SELECT corr(x, x), regr_r2(x, x), regr_slope(y, x), regr_sxx(y, x), covar_pop(x, y) +FROM regr_part; + corr | regr_r2 | regr_slope | regr_sxx | covar_pop +------+---------+------------+----------+----------- + | | | 0 | 0 +(1 row) + +DROP TABLE regr_part; -- test accum and combine functions directly CREATE TABLE regr_test (x float8, y float8); INSERT INTO regr_test VALUES (10,150),(20,250),(30,350),(80,540),(100,200); @@ -716,7 +778,7 @@ SELECT float8_regr_combine('{3,60,200,750,20000,2000,7,8}'::float8[], '{2,180,200,740,57800,-3400,7,9}'::float8[]); float8_regr_combine ------------------------------------ - {5,240,6280,1490,95080,8680,7,NaN} + {5,240,400,1490,95080,-1400,7,NaN} (1 row) DROP TABLE regr_test; diff --git a/src/test/regress/sql/aggregates.sql b/src/test/regress/sql/aggregates.sql index 0936088b074..9fd7a4e0ea2 100644 --- a/src/test/regress/sql/aggregates.sql +++ b/src/test/regress/sql/aggregates.sql @@ -181,6 +181,32 @@ SELECT corr(0.1, 'NaN'), regr_r2(0.1, 'NaN') FROM generate_series(1, 30) g; SELECT corr('NaN', 0.1), regr_r2('NaN', 0.1) FROM generate_series(1, 30) g; SELECT corr('NaN', 'NaN'), regr_r2('NaN', 'NaN') FROM generate_series(1, 30) g; +-- float8_regr_combine must treat constant inputs, and Inf/NaN with a +-- constant other input, the same way float8_regr_accum does, so that +-- partial aggregation gives the same results as a serial plan +CREATE TABLE regr_part (k int, x float8, y float8) PARTITION BY LIST (k); +CREATE TABLE regr_part_1 PARTITION OF regr_part FOR VALUES IN (1); +CREATE TABLE regr_part_2 PARTITION OF regr_part FOR VALUES IN (2); +CREATE TABLE regr_part_3 PARTITION OF regr_part FOR VALUES IN (3); +INSERT INTO regr_part SELECT 1 + g % 3, 1.0 / 3, g FROM generate_series(1, 80) g; +ANALYZE regr_part; +SET enable_partitionwise_aggregate = on; +EXPLAIN (COSTS OFF) +SELECT corr(x, x) FROM regr_part; +SELECT corr(x, x), regr_r2(x, x), regr_slope(y, x), regr_sxx(y, x), covar_pop(x, y) +FROM regr_part; +-- an Inf or NaN in one partition, the other input constant +INSERT INTO regr_part VALUES (2, 'Infinity', 1); +SELECT covar_pop(x, 0::float8), covar_pop(0::float8, x) FROM regr_part; +UPDATE regr_part SET x = 'NaN' WHERE x = 'Infinity'; +SELECT covar_pop(x, 0::float8), covar_pop(0::float8, x) FROM regr_part; +RESET enable_partitionwise_aggregate; +SELECT covar_pop(x, 0::float8), covar_pop(0::float8, x) FROM regr_part; +DELETE FROM regr_part WHERE x = 'NaN'; +SELECT corr(x, x), regr_r2(x, x), regr_slope(y, x), regr_sxx(y, x), covar_pop(x, y) +FROM regr_part; +DROP TABLE regr_part; + -- test accum and combine functions directly CREATE TABLE regr_test (x float8, y float8); INSERT INTO regr_test VALUES (10,150),(20,250),(30,350),(80,540),(100,200); -- 2.52.0
^ permalink raw reply [nested|flat] 7+ messages in thread

* Re: BUG #19826: corr()/regr_r2() of constant input: NULL serially, 1 with partial aggregation
@ 2026-11-12 16:13  Venu K <venu2805k@gmail.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 7+ messages in thread

From: Venu K @ 2026-11-12 16:13 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org

On Thu, Nov 12, 2026 at 1:53 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I studied this a bit, and remained unhappy about one point: it's
> not clear that float8_regr_combine() has the same behavior for
> overflow cases as float8_regr_accum().  Maybe it does, but I don't
> think readers should have to spend time convincing themselves of that.
> I suggest adjusting it to use the same block of overflow-handling
> logic that float8_regr_accum() has, as in v3 attached.  Does that
> seem sane to you?

Yes.  I compared it with float8_regr_accum() case by case: Sx1 and
Sx2 play the roles of transvalues[1] and newvalX there, so we still
only raise an error when finite sums give an infinite result, and
dropping float8_pl() for Sx and Sy loses nothing because the new block
catches that overflow.  Testing Sx/Sy for the Inf/NaN case is right
too, since one Inf or NaN anywhere in a state makes its sum Inf or NaN.

> I suspect that the last few lines in my v3, [...] are dead code for
> the same reason, but again that doesn't seem like something to
> depend on

Agreed.  A state built by float8_regr_accum() never has an infinite
Sxx, Syy or Sxy, so those lines only matter for hand-made states, but
they keep the two functions in step.

> Yeah, it seems clear that that test case is just made-up data,
> probably put there just so that float8_regr_combine() shows as having
> some coverage in our coverage tests.  I wonder whether the new test
> doesn't render it useless even for that.  It might be better to change
> it to exercise something your new test still doesn't reach, like an
> overflow error.

The first six entries aren't made up: they are the transition states
for the regr_test rows with x IN (10,20,30) and x IN (80,100), which
the queries just above print.  Only the commonX/commonY entries (7,8
and 7,9) don't fit those rows, which have distinct X and Y values.
With NaN in both, the test goes through the path where both
correction terms are applied, which regr_part doesn't reach since its
X is constant, and the result is again
{5,240,6280,1490,95080,8680,NaN,NaN}, the sums over the whole table.

I also added two overflow cases (Sx itself, and the Sxx correction
term alone) and one showing that an infinite input is not reported
as an overflow.  These give the same output with and without the
float.c change, so they are coverage for the combine function rather
than a test of this fix.

v4 attached: your v3 plus those test changes and a paragraph about
them in the commit message.  make check passes on master (b0496e3)
with assertions enabled.

Regards,
Venu

On Thu, Nov 12, 2026 at 1:53 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Venu K <venu2805k@gmail.com> writes:
> > v2 attached; make check passes on master.
>
> I studied this a bit, and remained unhappy about one point: it's
> not clear that float8_regr_combine() has the same behavior for
> overflow cases as float8_regr_accum().  Maybe it does, but I don't
> think readers should have to spend time convincing themselves of that.
> I suggest adjusting it to use the same block of overflow-handling
> logic that float8_regr_accum() has, as in v3 attached.  Does that
> seem sane to you?
>
> > In practice a partial state that has seen an Inf or NaN already has
> > Sxy = NaN, so I couldn't get a different result from SQL either way, but
> > I don't think the combine function should depend on that.
>
> Right, I'm with you on that.  I suspect that the last few lines in
> my v3,
>
> +                       if (isinf(Sxx))
> +                               Sxx = get_float8_nan();
> +                       if (isinf(Syy))
> +                               Syy = get_float8_nan();
> +                       if (isinf(Sxy))
> +                               Sxy = get_float8_nan();
>
> are dead code for the same reason, but again that doesn't seem
> like something to depend on, especially in a spot like this
> that can't be at all performance-critical.
>
> > One existing expected output changes: the direct float8_regr_combine()
> > call in aggregates.sql passes two states with the same common X (7) but
> > different means, so Sxx is now 400 rather than 6280.  Such states can't
> > come from real input; I can change the test input instead if you prefer.
>
> Yeah, it seems clear that that test case is just made-up data,
> probably put there just so that float8_regr_combine() shows as having
> some coverage in our coverage tests.  I wonder whether the new test
> doesn't render it useless even for that.  It might be better to change
> it to exercise something your new test still doesn't reach, like an
> overflow error.  I didn't do anything about that in v3 though.
>
>                         regards, tom lane
>

Attachments:

v4-0001-Make-float8_regr_combine-handle-edge-cases-like-f.patch (application/octet-stream, 12.3K) download From deb8e2026271c011d6898912af77d72ba4b83b47 Mon Sep 17 00:00:00 2001 From: Tom Lane <tgl@sss.pgh.pa.us> Date: Wed, 11 Nov 2026 15:08:56 -0500 Subject: [PATCH v4] Make float8_regr_combine() handle edge cases like float8_regr_accum(). Commit 649828769 taught float8_regr_accum() to keep Sxx, Syy and Sxy exactly zero while all the inputs for X (or Y) are the same, so that corr() and the regr_*() functions return NULL for a constant input. Then 7774249d0 made it force Sxy to NaN when one input is constant and the other is Inf or NaN. float8_regr_combine() got neither change: it still added the correction terms computed from the rounded sums Sx1 and Sx2, which are usually slightly nonzero even when both states saw the same single value. Thus, the edge cases those commits intended to fix occurred anyway under parallel aggregation. Skip each correction term when the corresponding input(s) have a common value, and force Sxy to NaN when the Sxy correction is skipped and the other input has seen an Inf or NaN. Also, make the overflow handling in float8_regr_combine() look more like float8_regr_accum(). It might have worked out the same in terms of when we throw error or don't, but even if that's true, proving it and then keeping it true in the future requires brain cells readers shouldn't have to expend. In the existing direct test of float8_regr_combine(), the two states are those of the regr_test rows with x IN (10,20,30) and x IN (80,100), but their commonX/commonY entries didn't match those rows. Set them to NaN, so that the test exercises the correction terms and its result again agrees with the aggregates over the whole table. Also test that overflow is reported and that an infinite input is not an overflow. Bug: #19826 Reported-by: Venu Kummari <venu2805k@gmail.com> Author: Venu Kummari <venu2805k@gmail.com> Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us> Discussion: https://postgr.es/m/19826-4b769cb5342fdfe7@postgresql.org Backpatch-through: 19 --- src/backend/utils/adt/float.c | 72 ++++++++++++++++---- src/test/regress/expected/aggregates.out | 87 ++++++++++++++++++++++-- src/test/regress/sql/aggregates.sql | 38 ++++++++++- 3 files changed, 177 insertions(+), 20 deletions(-) diff --git a/src/backend/utils/adt/float.c b/src/backend/utils/adt/float.c index 38ef6da..f2fe0f5 100644 --- a/src/backend/utils/adt/float.c +++ b/src/backend/utils/adt/float.c @@ -3649,19 +3649,10 @@ float8_regr_combine(PG_FUNCTION_ARGS) else { N = N1 + N2; - Sx = float8_pl(Sx1, Sx2); - tmp1 = Sx1 / N1 - Sx2 / N2; - Sxx = Sxx1 + Sxx2 + N1 * N2 * tmp1 * tmp1 / N; - if (unlikely(isinf(Sxx)) && !isinf(Sxx1) && !isinf(Sxx2)) - float_overflow_error(); - Sy = float8_pl(Sy1, Sy2); - tmp2 = Sy1 / N1 - Sy2 / N2; - Syy = Syy1 + Syy2 + N1 * N2 * tmp2 * tmp2 / N; - if (unlikely(isinf(Syy)) && !isinf(Syy1) && !isinf(Syy2)) - float_overflow_error(); - Sxy = Sxy1 + Sxy2 + N1 * N2 * tmp1 * tmp2 / N; - if (unlikely(isinf(Sxy)) && !isinf(Sxy1) && !isinf(Sxy2)) - float_overflow_error(); + Sx = Sx1 + Sx2; + Sy = Sy1 + Sy2; + + /* Check to see if we have seen distinct inputs */ if (float8_eq(Cx1, Cx2)) Cx = Cx1; else @@ -3670,6 +3661,61 @@ float8_regr_combine(PG_FUNCTION_ARGS) Cy = Cy1; else Cy = get_float8_nan(); + + tmp1 = Sx1 / N1 - Sx2 / N2; + tmp2 = Sy1 / N1 - Sy2 / N2; + + /* + * As in float8_regr_accum, if we have not seen distinct inputs for X + * (or Y) in either state, the corresponding sums should remain + * exactly zero: both means are then exactly Cx, and computing the + * correction terms from the rounded sums Sx1 and Sx2 would just + * inject roundoff error. We need exact zero results so that the + * final functions will return NULL in the right cases. + */ + Sxx = Sxx1 + Sxx2; + if (isnan(Cx)) + Sxx += N1 * N2 * tmp1 * tmp1 / N; + Syy = Syy1 + Syy2; + if (isnan(Cy)) + Syy += N1 * N2 * tmp2 * tmp2 / N; + Sxy = Sxy1 + Sxy2; + if (isnan(Cx) && isnan(Cy)) + Sxy += N1 * N2 * tmp1 * tmp2 / N; + else if (isnan(Sx) || isinf(Sx) || + isnan(Sy) || isinf(Sy)) + { + /* + * If one input has been constant so far, but the other one has + * been Inf or NaN, we must force Sxy to NaN to avoid falsely + * reporting variance zero. (We can't see individual X/Y values + * from here, but if any one has been Inf or NaN, the sum Sx/Sy + * must now be Inf or NaN.) Sxx and Syy don't have this issue. + */ + Sxy = get_float8_nan(); + } + + /* + * Overflow check, same logic as in float8_regr_accum. + */ + if (isinf(Sx) || isinf(Sxx) || isinf(Sy) || isinf(Syy) || isinf(Sxy)) + { + if (((isinf(Sx) || isinf(Sxx)) && + !isinf(Sx1) && !isinf(Sx2)) || + ((isinf(Sy) || isinf(Syy)) && + !isinf(Sy1) && !isinf(Sy2)) || + (isinf(Sxy) && + !isinf(Sx1) && !isinf(Sx2) && + !isinf(Sy1) && !isinf(Sy2))) + float_overflow_error(); + + if (isinf(Sxx)) + Sxx = get_float8_nan(); + if (isinf(Syy)) + Syy = get_float8_nan(); + if (isinf(Sxy)) + Sxy = get_float8_nan(); + } } /* diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out index ce555a3..bcaefe2 100644 --- a/src/test/regress/expected/aggregates.out +++ b/src/test/regress/expected/aggregates.out @@ -637,6 +637,68 @@ SELECT corr('NaN', 'NaN'), regr_r2('NaN', 'NaN') FROM generate_series(1, 30) g; NaN | NaN (1 row) +-- float8_regr_combine must treat constant inputs, and Inf/NaN with a +-- constant other input, the same way float8_regr_accum does, so that +-- partial aggregation gives the same results as a serial plan +CREATE TABLE regr_part (k int, x float8, y float8) PARTITION BY LIST (k); +CREATE TABLE regr_part_1 PARTITION OF regr_part FOR VALUES IN (1); +CREATE TABLE regr_part_2 PARTITION OF regr_part FOR VALUES IN (2); +CREATE TABLE regr_part_3 PARTITION OF regr_part FOR VALUES IN (3); +INSERT INTO regr_part SELECT 1 + g % 3, 1.0 / 3, g FROM generate_series(1, 80) g; +ANALYZE regr_part; +SET enable_partitionwise_aggregate = on; +EXPLAIN (COSTS OFF) +SELECT corr(x, x) FROM regr_part; + QUERY PLAN +------------------------------------------------------- + Finalize Aggregate + -> Append + -> Partial Aggregate + -> Seq Scan on regr_part_1 regr_part + -> Partial Aggregate + -> Seq Scan on regr_part_2 regr_part_1 + -> Partial Aggregate + -> Seq Scan on regr_part_3 regr_part_2 +(8 rows) + +SELECT corr(x, x), regr_r2(x, x), regr_slope(y, x), regr_sxx(y, x), covar_pop(x, y) +FROM regr_part; + corr | regr_r2 | regr_slope | regr_sxx | covar_pop +------+---------+------------+----------+----------- + | | | 0 | 0 +(1 row) + +-- an Inf or NaN in one partition, the other input constant +INSERT INTO regr_part VALUES (2, 'Infinity', 1); +SELECT covar_pop(x, 0::float8), covar_pop(0::float8, x) FROM regr_part; + covar_pop | covar_pop +-----------+----------- + NaN | NaN +(1 row) + +UPDATE regr_part SET x = 'NaN' WHERE x = 'Infinity'; +SELECT covar_pop(x, 0::float8), covar_pop(0::float8, x) FROM regr_part; + covar_pop | covar_pop +-----------+----------- + NaN | NaN +(1 row) + +RESET enable_partitionwise_aggregate; +SELECT covar_pop(x, 0::float8), covar_pop(0::float8, x) FROM regr_part; + covar_pop | covar_pop +-----------+----------- + NaN | NaN +(1 row) + +DELETE FROM regr_part WHERE x = 'NaN'; +SELECT corr(x, x), regr_r2(x, x), regr_slope(y, x), regr_sxx(y, x), covar_pop(x, y) +FROM regr_part; + corr | regr_r2 | regr_slope | regr_sxx | covar_pop +------+---------+------------+----------+----------- + | | | 0 | 0 +(1 row) + +DROP TABLE regr_part; -- test accum and combine functions directly CREATE TABLE regr_test (x float8, y float8); INSERT INTO regr_test VALUES (10,150),(20,250),(30,350),(80,540),(100,200); @@ -712,11 +774,26 @@ SELECT float8_regr_combine('{0,0,0,0,0,0,0,0}'::float8[], {2,180,200,740,57800,-3400,NaN,1} (1 row) -SELECT float8_regr_combine('{3,60,200,750,20000,2000,7,8}'::float8[], - '{2,180,200,740,57800,-3400,7,9}'::float8[]); - float8_regr_combine ------------------------------------- - {5,240,6280,1490,95080,8680,7,NaN} +SELECT float8_regr_combine('{3,60,200,750,20000,2000,NaN,NaN}'::float8[], + '{2,180,200,740,57800,-3400,NaN,NaN}'::float8[]); + float8_regr_combine +-------------------------------------- + {5,240,6280,1490,95080,8680,NaN,NaN} +(1 row) + +-- overflow of Sx, and of the Sxx correction term, must be reported +SELECT float8_regr_combine('{1,1e308,0,1,0,0,1e308,1}'::float8[], + '{1,1e308,0,1,0,0,1e308,1}'::float8[]); +ERROR: value out of range: overflow +SELECT float8_regr_combine('{1,1e308,0,1,0,0,1e308,1}'::float8[], + '{1,-1e308,0,1,0,0,-1e308,1}'::float8[]); +ERROR: value out of range: overflow +-- but an infinite input is not an overflow +SELECT float8_regr_combine('{1,Infinity,NaN,1,0,NaN,Infinity,1}'::float8[], + '{1,1,0,1,0,0,1,1}'::float8[]); + float8_regr_combine +-------------------------------- + {2,Infinity,NaN,2,0,NaN,NaN,1} (1 row) DROP TABLE regr_test; diff --git a/src/test/regress/sql/aggregates.sql b/src/test/regress/sql/aggregates.sql index 0936088..1af05df 100644 --- a/src/test/regress/sql/aggregates.sql +++ b/src/test/regress/sql/aggregates.sql @@ -181,6 +181,32 @@ SELECT corr(0.1, 'NaN'), regr_r2(0.1, 'NaN') FROM generate_series(1, 30) g; SELECT corr('NaN', 0.1), regr_r2('NaN', 0.1) FROM generate_series(1, 30) g; SELECT corr('NaN', 'NaN'), regr_r2('NaN', 'NaN') FROM generate_series(1, 30) g; +-- float8_regr_combine must treat constant inputs, and Inf/NaN with a +-- constant other input, the same way float8_regr_accum does, so that +-- partial aggregation gives the same results as a serial plan +CREATE TABLE regr_part (k int, x float8, y float8) PARTITION BY LIST (k); +CREATE TABLE regr_part_1 PARTITION OF regr_part FOR VALUES IN (1); +CREATE TABLE regr_part_2 PARTITION OF regr_part FOR VALUES IN (2); +CREATE TABLE regr_part_3 PARTITION OF regr_part FOR VALUES IN (3); +INSERT INTO regr_part SELECT 1 + g % 3, 1.0 / 3, g FROM generate_series(1, 80) g; +ANALYZE regr_part; +SET enable_partitionwise_aggregate = on; +EXPLAIN (COSTS OFF) +SELECT corr(x, x) FROM regr_part; +SELECT corr(x, x), regr_r2(x, x), regr_slope(y, x), regr_sxx(y, x), covar_pop(x, y) +FROM regr_part; +-- an Inf or NaN in one partition, the other input constant +INSERT INTO regr_part VALUES (2, 'Infinity', 1); +SELECT covar_pop(x, 0::float8), covar_pop(0::float8, x) FROM regr_part; +UPDATE regr_part SET x = 'NaN' WHERE x = 'Infinity'; +SELECT covar_pop(x, 0::float8), covar_pop(0::float8, x) FROM regr_part; +RESET enable_partitionwise_aggregate; +SELECT covar_pop(x, 0::float8), covar_pop(0::float8, x) FROM regr_part; +DELETE FROM regr_part WHERE x = 'NaN'; +SELECT corr(x, x), regr_r2(x, x), regr_slope(y, x), regr_sxx(y, x), covar_pop(x, y) +FROM regr_part; +DROP TABLE regr_part; + -- test accum and combine functions directly CREATE TABLE regr_test (x float8, y float8); INSERT INTO regr_test VALUES (10,150),(20,250),(30,350),(80,540),(100,200); @@ -201,8 +227,16 @@ SELECT float8_regr_combine('{3,60,200,750,20000,2000,1,NaN}'::float8[], '{0,0,0,0,0,0,0,0}'::float8[]); SELECT float8_regr_combine('{0,0,0,0,0,0,0,0}'::float8[], '{2,180,200,740,57800,-3400,NaN,1}'::float8[]); -SELECT float8_regr_combine('{3,60,200,750,20000,2000,7,8}'::float8[], - '{2,180,200,740,57800,-3400,7,9}'::float8[]); +SELECT float8_regr_combine('{3,60,200,750,20000,2000,NaN,NaN}'::float8[], + '{2,180,200,740,57800,-3400,NaN,NaN}'::float8[]); +-- overflow of Sx, and of the Sxx correction term, must be reported +SELECT float8_regr_combine('{1,1e308,0,1,0,0,1e308,1}'::float8[], + '{1,1e308,0,1,0,0,1e308,1}'::float8[]); +SELECT float8_regr_combine('{1,1e308,0,1,0,0,1e308,1}'::float8[], + '{1,-1e308,0,1,0,0,-1e308,1}'::float8[]); +-- but an infinite input is not an overflow +SELECT float8_regr_combine('{1,Infinity,NaN,1,0,NaN,Infinity,1}'::float8[], + '{1,1,0,1,0,0,1,1}'::float8[]); DROP TABLE regr_test; -- test count, distinct -- 2.43.0
^ permalink raw reply [nested|flat] 7+ messages in thread

* Re: BUG #19826: corr()/regr_r2() of constant input: NULL serially, 1 with partial aggregation
@ 2026-11-12 17:22  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Venu K <venu2805k@gmail.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Tom Lane @ 2026-11-12 17:22 UTC (permalink / raw)
  To: Venu K <venu2805k@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org

Venu K <venu2805k@gmail.com> writes:
> On Thu, Nov 12, 2026 at 1:53 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Yeah, it seems clear that that test case is just made-up data,

> The first six entries aren't made up: they are the transition states
> for the regr_test rows with x IN (10,20,30) and x IN (80,100), which
> the queries just above print.  Only the commonX/commonY entries (7,8
> and 7,9) don't fit those rows, which have distinct X and Y values.

Ah.  Now that I think about it, the bogus commonX/commonY entries are
probably my fault: they came in with 649828769, and I was not aware
that the other columns matched some real state, so I just made them
test both the NaN and non-NaN code paths.

> v4 attached: your v3 plus those test changes and a paragraph about
> them in the commit message.  make check passes on master (b0496e3)
> with assertions enabled.

v4 seems good, pushed.  Thanks for the report and patch!

			regards, tom lane






^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2026-11-12 17:22 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-11-02 15:36 BUG #19826: corr()/regr_r2() of constant input: NULL serially, 1 with partial aggregation PG Bug reporting form <noreply@postgresql.org>
2026-11-02 15:39 ` Venu K <venu2805k@gmail.com>
2026-11-03 03:03   ` Tom Lane <tgl@sss.pgh.pa.us>
2026-11-03 05:23     ` Venu K <venu2805k@gmail.com>
2026-11-11 20:23       ` Tom Lane <tgl@sss.pgh.pa.us>
2026-11-12 16:13         ` Venu K <venu2805k@gmail.com>
2026-11-12 17:22           ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox