[PATCH] Fix overflow and underflow in regr_r2()
Chengpeng Yan <chengpeng_yan@outlook.com>
From: Chengpeng Yan <chengpeng_yan@outlook.com>
To: PostgreSQL-development <pgsql-hackers@postgresql.org>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Dean Rasheed <dean.a.rasheed@gmail.com>
Date: 2026-04-27T11:18:38Z
Lists: pgsql-hackers
Attachments
- v1-0001-Avoid-overflow-and-underflow-in-regr_r2.patch (application/octet-stream) patch v1-0001
Hi, While looking at the corr() overflow/underflow discussion [1], I noticed that regr_r2() still computes (Sxy * Sxy) / (Sxx * Syy) directly. At very small or very large scales, those products can round to zero or infinity even when the ratio itself is finite. For example, SELECT regr_r2(1e-100 + g * 1e-105, 1e-100 + g * 1e-105) FROM generate_series(1, 3) g; returns NaN without the patch, although the inputs are perfectly correlated and the result should be 1. corr() already has a stabilized calculation for the same Sxx * Syy denominator scale. This patch factors that into a helper and lets regr_r2() use it as a fallback when one of its direct products has rounded to zero or infinity. Otherwise, regr_r2() keeps the existing direct formula. This preserves regr_r2()'s existing SQL-level special cases. The added tests cover the fallback path and nearby NaN behavior. Thoughts? References: [1] https://www.postgresql.org/message-id/flat/19340-6fb9f6637f562092%40postgresql.org -- Best regards, Chengpeng Yan
Commits
Same data as JSON:
GET /api/v1/messages/:b64id/commits
the thread's linked commits as JSON, with link sources.
API reference →
-
Improve overflow/underflow handling in regr_intercept().
- eb8e76e130fd 19 (unreleased) landed
-
Improve overflow/underflow handling in regr_r2().
- d58ec50e0faa 19 (unreleased) landed