pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: venu2805k@gmail.com
Subject: BUG #19826: corr()/regr_r2() of constant input: NULL serially, 1 with partial aggregation
Date: Mon, 02 Nov 2026 15:36:11 +0000
Message-ID: <19826-4b769cb5342fdfe7@postgresql.org> (raw)

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.







view thread (7+ messages)  latest in thread

Message-ID: <19826-4b769cb5342fdfe7@postgresql.org>
Permalink:  ../19826-4b769cb5342fdfe7@postgresql.org/
Also on:    postgresql.org/message-id/19826-4b769cb5342fdfe7@postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, venu2805k@gmail.com
  Subject: Re: BUG #19826: corr()/regr_r2() of constant input: NULL serially, 1 with partial aggregation
  In-Reply-To: <19826-4b769cb5342fdfe7@postgresql.org>
  References: <19826-4b769cb5342fdfe7@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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