From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: bingyanli@email.ncu.edu.cn
Subject: BUG #18951: Precision loss in inner join while using SUM aggregate function
Date: Sun, 08 Jun 2025 14:38:55 +0000
Message-ID: <18951-3092d80beab3d2ef@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 18951
Logged by: bingyan li
Email address: bingyanli@email.ncu.edu.cn
PostgreSQL version: 17.4
Operating system: ubuntu 22.04
Description:
Database version: 17.4 (Debian 17.4-1.pgdg120+2)
DROP TABLE t0, t1;
CREATE UNLOGGED TABLE t0 (c0 boolean, c1 double precision, CONSTRAINT
t0_pkey PRIMARY KEY (c1));
CREATE TABLE t1 (c0 numeric);
INSERT INTO t0 (c0, c1) VALUES(false, 156160112), (false, 0.7898343);
INSERT INTO t1 (c0) VALUES(0.09662093327539545),
(0.06221937587785409),(0.6254519330662367);
-- judgement: s1 = s2 + s3, However, s2 is not correctly computed
SELECT SUM(t0.c1) AS s1 FROM ONLY t0 INNER JOIN ONLY t1 ON t0.c1 != t1.c0
GROUP BY t0.c0;
s1
--------------------
468480338.36950296
(1 row)
SELECT SUM(t0.c1) AS s2 FROM ONLY t0 INNER JOIN ONLY t1 ON t0.c1 > t1.c0
GROUP BY t0.c0;
s2
-------------------
468480338.3695029
(1 row)
SELECT SUM(t0.c1) AS s3 FROM ONLY t0 INNER JOIN ONLY t1 ON t0.c1 < t1.c0
GROUP BY t0.c0;
s3
----
(0 rows)
-- further analysis: s2 is not correctly computed, it should be
468480338.36950296
database76=# SELECT t0.c1 AS s2 FROM t0 INNER JOIN t1 ON t0.c1 > t1.c0;
s2
-----------
0.7898343
156160112
0.7898343
156160112
0.7898343
156160112
(6 rows)
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 using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, bingyanli@email.ncu.edu.cn
Subject: Re: BUG #18951: Precision loss in inner join while using SUM aggregate function
In-Reply-To: <18951-3092d80beab3d2ef@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