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

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.