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 1xOXld-00000000CVi-48wV for pgsql-bugs@arkaria.postgresql.org; Wed, 04 Nov 2026 09:58:55 +0000 Received: from localhost ([127.0.0.1]:35888 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xOXle-00000005V60-1bsl for pgsql-bugs@arkaria.postgresql.org; Wed, 04 Nov 2026 09:58:54 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]:33404) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xNu5r-00000004NyY-0Aif for pgsql-bugs@lists.postgresql.org; Mon, 02 Nov 2026 15:37:07 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xNu5p-00000002BVG-2Z9P for pgsql-bugs@lists.postgresql.org; Mon, 02 Nov 2026 15:37:06 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=postgresql.org; s=20171124; h=Message-ID:Date:Reply-To:Cc:From:To:Subject: Content-Transfer-Encoding:MIME-Version:Content-Type:Sender:Content-ID: Content-Description:In-Reply-To:References; bh=cU6a+OcGPsWUNRP1WSPWbgykzj3QNWvHrqrhOfZHJn0=; b=56DK2GA+ncVacMG7Qnxo+jOOzN kgEop819RWyawrEHfkbPKXNa1QmBjC2v7wqZSYHfp3dQsvJQ+HnMpGl/tddtYWPKHCCWRgwbTZ9L0 sXEDrtPsTGdK4ThBty1jjkT4M1dc0+Qz6CU48/0c8R2rKtr0fgqznK8NZ6iNleFEdVgNqJeDY4I2N X5FccF73VHD2sHLB0xny45DgAF2723rGjYOTH8Uit1lS1LxL5zsygWLDqE02SVPrAErg/Nqn2pqeP ZGzXlqn/j5TmK377fT3pt9XqfxojmNv239hdbZj4NbzEcnGGoit7b5JcCqRb8ARrk63Qio6eOTGC/ +rVT4SDw==; Received: from wrigleys.postgresql.org ([2a02:16a8:dc51::60]) by mahout.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xNu5p-00000006R94-1ZXv for pgsql-bugs@lists.postgresql.org; Mon, 02 Nov 2026 15:37:05 +0000 Received: from localhost ([127.0.0.1] helo=wrigleys.postgresql.org) by wrigleys.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xNu5n-0000000C31o-0hpl for pgsql-bugs@lists.postgresql.org; Mon, 02 Nov 2026 15:37:04 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19826: corr()/regr_r2() of constant input: NULL serially, 1 with partial aggregation To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: venu2805k@gmail.com Reply-To: venu2805k@gmail.com, pgsql-bugs@lists.postgresql.org Date: Mon, 02 Nov 2026 15:36:11 +0000 Message-ID: <19826-4b769cb5342fdfe7@postgresql.org> X-Auto-Response-Suppress: All Auto-Submitted: auto-generated List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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: =20 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 =3D 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 =3D 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.