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(at)postgresql(dot)org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 19826
> Logged by: Venu Kummari
> Email address: venu2805k(at)gmail(dot)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.
>
>