From: Chengpeng Yan <chengpeng_yan@outlook.com>
To: Dean Rasheed <dean.a.rasheed@gmail.com>
CC: Tom Lane <tgl@sss.pgh.pa.us>, PostgreSQL-development
	<pgsql-hackers@postgresql.org>
Subject: Re: [PATCH] Fix overflow and underflow in regr_r2()
Thread-Topic: [PATCH] Fix overflow and underflow in regr_r2()
Thread-Index: 
 AQHc1jeYNmULsFKlYkyKLV2a8es4/7YQg1oAgABDBwCAADPuAIAAFe6AgAAK9oCAAB8igIAADGGAgADIloCACP/WAIAIggoAgAEH+QA=
Date: Fri, 29 May 2026 04:22:55 +0000
Message-ID: <D7866E05-B2B7-4475-94CB-A5C404413F67@outlook.com>
References: <33E01656-BB3B-46E9-A41F-24A01A7C35F4@outlook.com>
 <CAEZATCUaV+qmBCh0zv0EPdBvhE2skHCzkRw78VNjBMCX9Z7h+w@mail.gmail.com>
 <1494820.1778938764@sss.pgh.pa.us> <1506197.1778949916@sss.pgh.pa.us>
 <CAEZATCXpUwijoE2imbwWWraG63SmzgXyE+B8eoFNennu87-=kw@mail.gmail.com>
 <1513654.1778956979@sss.pgh.pa.us>
 <CAEZATCWDfB8QQeqrhD2_BvDrPOF5b=nskOWaRmzMGvyntv5Tnw@mail.gmail.com>
 <1524050.1778966323@sss.pgh.pa.us>
 <CAEZATCUAu5JOJnyy5yXTGsfSRCDnbrGfHCWRxYVPtxROzKH30A@mail.gmail.com>
 <10B9314B-B6F2-4657-94BC-C990F918CE44@outlook.com>
 <CAEZATCWwqMUHnjNzBday-fh15oPNqec3vLnqj7A8-G+vnmQ0RQ@mail.gmail.com>
In-Reply-To: 
 <CAEZATCWwqMUHnjNzBday-fh15oPNqec3vLnqj7A8-G+vnmQ0RQ@mail.gmail.com>
Accept-Language: zh-CN, en-US
Content-Language: en-US
x-ms-exchange-messagesentrepresentingtype: 1
x-ms-publictraffictype: Email
x-ms-traffictypediagnostic: OS9PR01MB14121:EE_|TYWPR01MB9276:EE_
x-ms-office365-filtering-correlation-id: 56270aa1-0e33-45b6-fa3d-08debd39f55c
x-ms-exchange-slblob-mailprops: 
 AZnQBsB9XmoMTHls4qxpPCIOT+xRm2QPE822Ez5sUAG3Mj4JTunLXcbj2Edtt/cJN7STKL7rUi0qILFPsdmaj8NXTJndJzPIkWlhWMRtBe/dlQTw8DT1E6jcP4isJYxr3+vRkSF5DnoViTbQMu6Et44r9DpIyzNbfMqe3+nrYbMguKuLHgfqkBZWDwJnwvU3FwG8wEUXgn1vWSEe8/Ouf5AO33u8hyIKfwho8+2asad5EHb6z8eq9UfBhCzEYuw7C9hKP7531NOeFROrkiGhx18bC559mL9fzLG9Oo/dwIxfCwzOlVFvYIhxkZEM5dnr7rZh/TuLd/2OqPIhT9VSTg/rhDOzOHwmLoo7X5LSgkHFhSRguGj5V8bEMSSSk3OSDPrjU4ReQ7dusHXVt/62S+WHRybAiEwRRU6a9zdeWZcjR/dWn9P2xG6RvYmkMKcTo/FLl8zc4nyAv0TQQXGh1YPgChBAfm9ev9X8B5OO9Iz6Axm+dv1NRXOFGkaFNUWveMEu60H/ijmJA2qDQuU+Cz6cK9h+Hymb72z0/PJ4jgDJP3a9Fk5d+XyXwt7BCNlyqtsNPc1Jx9uYnLOhNXLblzGTNNuVHxZkdPP7MOsUFuy9nn6DGAj9clWFY6WsjCtcwUTqzfyNN0TyhofKljliHvpUEPe6w0HZgaW8XeMKRdn7He/96guCesVMfDh7/JySxGp9Uuk9F1UiXex9OhSrryguGJ/YnXMeoClFuhMD+Qa9Fq6sbMKaGDzINtpRyyxwu9Z0CBeuJ+I=
x-microsoft-antispam: 
 BCL:0;ARA:14566002|24021099003|24121999003|15080799012|19110799012|22091999003|31061999003|8060799015|8062599012|52005399003|40105399003|3412199025|440099028|102099032;
x-microsoft-antispam-message-info: 
 =?us-ascii?Q?/wImQAn2+tczxGirDm3IPJlowg7sJi+83MAX7dcwnQi/v0C68k9Tl2JqTLvi?=
 =?us-ascii?Q?oX/lVUBu2YH7QaCC3lso9q9uaxd8+0gNf1BQKLkN7wfZZmtLnfIm3S+WqLdH?=
 =?us-ascii?Q?IJDE4fX5XltQP6V09c2HZH7mvu97FywsrqKkAgw5wgOTJE4McmM64ud1pe44?=
 =?us-ascii?Q?AnwqRBnh8RrAwRir6n1n+g5yC9ibI2BHFW7MQ2yKUbRbxps4/cozcnmZ8U6j?=
 =?us-ascii?Q?2NqWeOWgporSLYYZP+e4CwFKfLbDvzV3tg1GqMLF+aKie7hmMH75G7nRu0EX?=
 =?us-ascii?Q?MkITCpP9Ag0n/jariHHQ6jCPB0XBa7VU8eyyxcUQ6AJDNaigEoCEjnHWzDba?=
 =?us-ascii?Q?mIGZiO5B5uX3+b7azIG1USGN8TAcNIIVRIwJkvsE1VbO/745UeoYpp+uJ5gK?=
 =?us-ascii?Q?c6nXpkp9mpvQ6hlYvE1r+C/8xja2YH1i4qAivAcvxV0DFyUwfBI4G2IahRUP?=
 =?us-ascii?Q?k5x2vQJgmdSr4ri7y2/fPykEOmdNlfXlJ8R6AGvg8d7og2EB6993Hfx2OBC3?=
 =?us-ascii?Q?1nXswLVnKXlPfq5bwmIjupBCZ2puFlm9HsJq9qqAH5/n35mmbNHz+tCta8RS?=
 =?us-ascii?Q?atYtq25YH8HDdCPjp7zx6MagOVgpkegMPA1sjamBqHC+mXkH7RCk2Q7uUNL5?=
 =?us-ascii?Q?Cxa2BYWCJwa/NI1NVqHrs9wjVOUgjE1qVlT855wCL+PcjBNvBrLGvb3moX6v?=
 =?us-ascii?Q?cFLB25isaGxkwlxYybB13sm+GK9zGxE1NCxbLyEtJhxVL6DpAh/dCcN+s6Ts?=
 =?us-ascii?Q?mSvDs+eZHVAyhl4lsDn4Kc62TPzE9ygnsJnUU5tGocvMD9/VZVa7jsxf7pcl?=
 =?us-ascii?Q?dV9nwEy1c+Pw5VzeisFGnC0ZCj20qLsq09BYsBPDVxWV/gfWnulG+bznSdKh?=
 =?us-ascii?Q?cXDwX2nOIBzI9IffnmHMEJopHEOP0CLHMC4wE/GtckIkxsNJSKWzp3/g6ABr?=
 =?us-ascii?Q?a1AEZ/aYnM7iwskzG7nvhi3vO0SY7NXG1KeGU7KnE7YuDHec9Auj7n1dhVi5?=
 =?us-ascii?Q?nj70?=
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0: 
 =?us-ascii?Q?3VO1i1vnHRkHfC+TMuYBPnKO6D35GBzMBeKWC7P5hqkmyjlkOJIusqq+dlAK?=
 =?us-ascii?Q?w8bYSXDN8y92cKuAPuJGoAC2fP1+VaMOnzsJoodl0EFA7+hSiQ7Cv3HoNBRS?=
 =?us-ascii?Q?bcxrgh0KFpibquJcClb/UINtiqQULyqEI3+kIfV9z+cupUKI+SGbQDszKKDT?=
 =?us-ascii?Q?SJjesw0K7nktu4pGoZKvbKRSyladIwSsCyjjQbdcInqqaf6yXys38eRSCZAi?=
 =?us-ascii?Q?PsrhzdafsTqsE4FaYdpuOMOnEdbB4rfR539L8G3QeTBE/8e3TG7Z+6f5h+bk?=
 =?us-ascii?Q?a3aeYiuAj+AMe90UqB15IDLDayDOlHDYq4R1GYvFRzt7t2278Ynfk1gp8YvY?=
 =?us-ascii?Q?MjbA7Rm+FxbF+jIQKup/nHuVkMWku63YITV/QdcQit4Pl/E7D+uCc21WOGks?=
 =?us-ascii?Q?PXj3XU4KguoYJUnWNqM83CUDMHCugXTjSy0/ImwWHbbMrTOGH8aKjRKmk1Lt?=
 =?us-ascii?Q?/iaBjUJj6N5SwFRE/sVBVni0Ny/3VaPDPY3oMDpFfVZNKZq+exMz5wPSCeHT?=
 =?us-ascii?Q?Vc6Cou5HRwSwNx957TM11g/s+jHjzrqEVr03l1wvn5dZecfSTfjrpdtPk87o?=
 =?us-ascii?Q?D+auIQFycZ+kLKEKu4C4vt7np5IPsWTY6vx9oyb8yfLAxhIVH23zSFaEMCNZ?=
 =?us-ascii?Q?lQxcSXE7hZGJ+TuOhS3J2HECQ9p5ZACHSkIVGDEHTFvM9oua9q3oRW1DR9zK?=
 =?us-ascii?Q?fspQFT9hkkQm2KZRaEzjRKukZA2g26PSMaSo3+TC8SlFdn4NUjCdYCTpQnog?=
 =?us-ascii?Q?bQ2mx0T8nR/oaPtVn/VnFoAj/IewEPmyRXEe+DmcwwaKKzwd0z/EXJK0jnIx?=
 =?us-ascii?Q?p3dVwaw0QLEXeS0zDrbrzP16lq06u54RzCECxkkeBFXkGOFDb7w73K6Ryily?=
 =?us-ascii?Q?sh7+iOoqyFEfwWv9ogBxwv7NAoz6mwVQXah1oh2w3KBighLhtNjzlKkupdsl?=
 =?us-ascii?Q?iAWP/3QveaVTGAAe4Ji+d6kfDP3ByLUWdxTZ4Uv2xoLuZX3dTWpoSOKlGhL0?=
 =?us-ascii?Q?qmhFfCyem+HAlSNDaPPeeHW4jfugoDNYm1P/cDbELnW9lHdxc1Mb5sbguipQ?=
 =?us-ascii?Q?i3qIaDBO5+qDzwuSw3/hs/Z4Dfp0e6Bd/nkHygLhyIiDCalAJJMswrg0xVTf?=
 =?us-ascii?Q?hGf5zTpbhcyL9dIDe8anHeue3Ej7RiOhWQ8v+pGU9kY8I5c/57T7/0ldubyv?=
 =?us-ascii?Q?mtzXYvGaZbV35U/XdntjT5smiLbm+sitsMSO2/WTamIrafyIUSYc8Yp609y6?=
 =?us-ascii?Q?JjK8TTQtu+o4ymbLl5QwulmerQbEVjs7eKYkDnxMdZ+nqq87yyfrzmK8M832?=
 =?us-ascii?Q?XMPXROa94YAukUKWKYyc6SGbEwF87h5xypRCr3JWbmbCePT8Sr9R6MpLwzHB?=
 =?us-ascii?Q?pQrk9gsizLiPXDywFqqB30XCFCt++8RA7hqe8vllZRFQbDHxCg=3D=3D?=
Content-Type: text/plain; charset="us-ascii"
Content-ID: <DFB2D567D922B049A3E686D09612290C@jpnprd01.prod.outlook.com>
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
X-OriginatorOrg: outlook.com
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: OS9PR01MB14121.jpnprd01.prod.outlook.com
X-MS-Exchange-CrossTenant-RMS-PersistedConsumerOrg: 
 00000000-0000-0000-0000-000000000000
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 56270aa1-0e33-45b6-fa3d-08debd39f55c
X-MS-Exchange-CrossTenant-originalarrivaltime: 29 May 2026 04:22:55.9893
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 84df9e7f-e9f6-40af-b435-aaaaaaaaaaaa
X-MS-Exchange-CrossTenant-rms-persistedconsumerorg: 
 00000000-0000-0000-0000-000000000000
X-MS-Exchange-Transport-CrossTenantHeadersStamped: TYWPR01MB9276
List-Id: <pgsql-hackers.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-hackers@lists.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-hackers>
Archived-At: 
 <https://www.postgresql.org/message-id/D7866E05-B2B7-4475-94CB-A5C404413F67%40outlook.com>
Precedence: bulk

Hi,

> On May 28, 2026, at 20:37, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
>=20
> [Shrug] I think dy is common enough to denote a difference in
> y-values, and it seems clear enough, given the large comment above it.

Makes sense, thanks for the clarification.

>> 3. Do we need to add tests for the underflow path, and perhaps for the
>> Inf/NaN guard?
>=20
> Yeah, I think it makes sense to include a test with underflow, since
> that really can lead to a large relative error. I don't think it's
> worth testing the Inf/NaN guard, since that's more about avoiding
> operating on technically uninitialised variables, and I don't believe
> that it actually affects the results.
>=20
> I've add this test case:
>=20
> SELECT regr_intercept(y, x) FROM (VALUES (-1e-131, 0), (2e-131,
> 3e-131)) v(x, y);
>=20
> Here, directly computing Sx * Sxy / Sxx causes an underflow to zero,
> while the correct result should be 1e-131. Since Sy is 3e-131, this
> makes a noticeable difference to the final result (without the patch,
> it returns an intercept of 1.5e-131, whereas with the patch, it
> correctly returns 1e-131).
>=20
> If there are no objections from the RMT, I'll push both of these (to
> HEAD only) in a couple of days or so.
>=20
> Regards,
> Dean
> <v2-0001-Improve-overflow-underflow-handling-in-regr_r2.patch><v2-0002-Im=
prove-overflow-underflow-handling-in-regr_inter.patch>

v2 LGTM.

I ran the regression tests locally on Apple Silicon as well, and all
tests passed.

--
Best regards,
Chengpeng Yan