Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xRErU-00000000okb-0BUQ for pgsql-bugs@arkaria.postgresql.org; Wed, 11 Nov 2026 20:24:09 +0000 Received: from localhost ([127.0.0.1]:44990 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xRErQ-0000000Cn3m-3jpU for pgsql-bugs@arkaria.postgresql.org; Wed, 11 Nov 2026 20:24:01 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]:33366) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xRErQ-0000000Cn3e-2TQb for pgsql-bugs@lists.postgresql.org; Wed, 11 Nov 2026 20:24:00 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xRErN-00000000Whb-1w6u for pgsql-bugs@lists.postgresql.org; Wed, 11 Nov 2026 20:23:59 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.18.1/8.18.1) with ESMTP id 6ABKNnHE3406914; Wed, 11 Nov 2026 15:23:49 -0500 DKIM-Filter: OpenDKIM Filter v2.11.0 sss.pgh.pa.us 6ABKNnHE3406914 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=sss.pgh.pa.us; s=default; t=1794428629; bh=HR+jUEvo4N+lZmuhYZm9Zo8eLYYnqRR6R4G7IrJyyFM=; h=From:To:cc:Subject:In-reply-to:References:Date:From; b=Dbneplhk/jSeY4Lsv0uD+gTaJBLzQKoNTJC/TFnTWxHgyZXg5abM4jV5OZChyZsTM PdQC/VO4PISW99z8cVqSB7OS2xGykSKgKIcUaGyYTPsBROeB1nRPneo2Blk2/rHtZs YM1YnROjyqEB9ho3zVUjWQiVH07VfcXZ6klBQFqw= From: Tom Lane To: Venu K cc: pgsql-bugs@lists.postgresql.org Subject: Re: BUG #19826: corr()/regr_r2() of constant input: NULL serially, 1 with partial aggregation In-reply-to: References: <19826-4b769cb5342fdfe7@postgresql.org> <1382078.1793675027@sss.pgh.pa.us> Comments: In-reply-to Venu K message dated "Tue, 03 Nov 2026 10:53:49 +0530" MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0" Content-ID: <3406861.1794428578.0@sss.pgh.pa.us> Date: Wed, 11 Nov 2026 15:23:49 -0500 Message-ID: <3406913.1794428629@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------- =_aaaaaaaaaa0 Content-Type: text/plain; charset="us-ascii" Content-ID: <3406861.1794428578.1@sss.pgh.pa.us> Venu K 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 ------- =_aaaaaaaaaa0 Content-Type: text/x-diff; name*0="v3-0001-Make-float8_regr_combine-handle-edge-cases-like-f.p"; name*1="atch"; charset="us-ascii" Content-ID: <3406861.1794428578.2@sss.pgh.pa.us> Content-Description: v3-0001-Make-float8_regr_combine-handle-edge-cases-like-f.patch Content-Transfer-Encoding: quoted-printable =46rom 001df21bc29374b191981333f31ee639dc848952 Mon Sep 17 00:00:00 2001 From: Tom Lane 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 Author: Venu Kummari Reviewed-by: Tom Lane 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 =3D N1 + N2; - Sx =3D float8_pl(Sx1, Sx2); - tmp1 =3D Sx1 / N1 - Sx2 / N2; - Sxx =3D Sxx1 + Sxx2 + N1 * N2 * tmp1 * tmp1 / N; - if (unlikely(isinf(Sxx)) && !isinf(Sxx1) && !isinf(Sxx2)) - float_overflow_error(); - Sy =3D float8_pl(Sy1, Sy2); - tmp2 =3D Sy1 / N1 - Sy2 / N2; - Syy =3D Syy1 + Syy2 + N1 * N2 * tmp2 * tmp2 / N; - if (unlikely(isinf(Syy)) && !isinf(Syy1) && !isinf(Syy2)) - float_overflow_error(); - Sxy =3D Sxy1 + Sxy2 + N1 * N2 * tmp1 * tmp2 / N; - if (unlikely(isinf(Sxy)) && !isinf(Sxy1) && !isinf(Sxy2)) - float_overflow_error(); + Sx =3D Sx1 + Sx2; + Sy =3D Sy1 + Sy2; + + /* Check to see if we have seen distinct inputs */ if (float8_eq(Cx1, Cx2)) Cx =3D Cx1; else @@ -3670,6 +3661,61 @@ float8_regr_combine(PG_FUNCTION_ARGS) Cy =3D Cy1; else Cy =3D get_float8_nan(); + + tmp1 =3D Sx1 / N1 - Sx2 / N2; + tmp2 =3D 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 =3D Sxx1 + Sxx2; + if (isnan(Cx)) + Sxx +=3D N1 * N2 * tmp1 * tmp1 / N; + Syy =3D Syy1 + Syy2; + if (isnan(Cy)) + Syy +=3D N1 * N2 * tmp2 * tmp2 / N; + Sxy =3D Sxy1 + Sxy2; + if (isnan(Cx) && isnan(Cy)) + Sxy +=3D 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 =3D 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 =3D get_float8_nan(); + if (isinf(Syy)) + Syy =3D get_float8_nan(); + if (isinf(Sxy)) + Sxy =3D get_float8_nan(); + } } = /* diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/e= xpected/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 =3D 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 =3D 'NaN' WHERE x =3D '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 =3D '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/ag= gregates.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 gen= erate_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 =3D 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 =3D 'NaN' WHERE x =3D '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 =3D '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 ------- =_aaaaaaaaaa0--