BUG #19826: corr()/regr_r2() of constant input: NULL serially, 1 with partial aggregation

Started by PG Bug reporting form20 days ago7 messagesbugs
Jump to latest
#1PG Bug reporting form
noreply@postgresql.org

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.

#2Venu K
venu2805k@gmail.com
In reply to: PG Bug reporting form (#1)
Re: BUG #19826: corr()/regr_r2() of constant input: NULL serially, 1 with partial aggregation

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:

Show quoted text

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:

t254526_1
v1-0001-Keep-constant-input-sums-exact-in-float8_regr_combine.patchapplication/octet-stream; name=v1-0001-Keep-constant-input-sums-exact-in-float8_regr_combine.patchDownload+89-13
#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: Venu K (#2)
Re: BUG #19826: corr()/regr_r2() of constant input: NULL serially, 1 with partial aggregation

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

#4Venu K
venu2805k@gmail.com
In reply to: Tom Lane (#3)
Re: BUG #19826: corr()/regr_r2() of constant input: NULL serially, 1 with partial aggregation

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

Show quoted text

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:

t254526_3
v2-0001-Make-float8_regr_combine-handle-constant-inputs-l (1).patchapplication/octet-stream; name="v2-0001-Make-float8_regr_combine-handle-constant-inputs-l (1).patch"Download+131-13
#5Tom Lane
tgl@sss.pgh.pa.us
In reply to: Venu K (#4)
Re: BUG #19826: corr()/regr_r2() of constant input: NULL serially, 1 with partial aggregation

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:

t254526_4
v3-0001-Make-float8_regr_combine-handle-edge-cases-like-f.patchtext/x-diff; charset=us-ascii; name*0=v3-0001-Make-float8_regr_combine-handle-edge-cases-like-f.p; name*1=atchDownload+148-15
#6Venu K
venu2805k@gmail.com
In reply to: Tom Lane (#5)
Re: BUG #19826: corr()/regr_r2() of constant input: NULL serially, 1 with partial aggregation

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

Show quoted text

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:

t254526_5
v4-0001-Make-float8_regr_combine-handle-edge-cases-like-f.patchapplication/octet-stream; name=v4-0001-Make-float8_regr_combine-handle-edge-cases-like-f.patchDownload+177-20
#7Tom Lane
tgl@sss.pgh.pa.us
In reply to: Venu K (#6)
Re: BUG #19826: corr()/regr_r2() of constant input: NULL serially, 1 with partial aggregation

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