pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18951: Precision loss in inner join while using SUM aggregate function
3+ messages / 3 participants
[nested] [flat]
* BUG #18951: Precision loss in inner join while using SUM aggregate function
@ 2025-06-08 14:38 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 2 replies; 3+ messages in thread
From: PG Bug reporting form @ 2025-06-08 14:38 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: bingyanli@email.ncu.edu.cn
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)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #18951: Precision loss in inner join while using SUM aggregate function
@ 2025-06-08 15:44 Junwang Zhao <zhjwpku@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 0 replies; 3+ messages in thread
From: Junwang Zhao @ 2025-06-08 15:44 UTC (permalink / raw)
To: bingyanli@email.ncu.edu.cn; pgsql-bugs@lists.postgresql.org
On Sun, Jun 8, 2025 at 10:49 PM PG Bug reporting form
<noreply@postgresql.org> wrote:
>
> 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)
>
It's nothing to do with the inner join, it's just what the ieee 754
float algorithms do,
if you try:
select 0.7898343::float8+0.7898343::float8+0.7898343::float8+156160112::float8+156160112::float8+156160112::float8;
?column?
-------------------
468480338.3695029
select 156160112::float8+156160112::float8+156160112::float8+0.7898343::float8+0.7898343::float8+0.7898343::float8;
?column?
--------------------
468480338.36950296
If you don't want the inconsistency, you might want to use numeric.
--
Regards
Junwang Zhao
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #18951: Precision loss in inner join while using SUM aggregate function
@ 2025-06-08 15:50 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2025-06-08 15:50 UTC (permalink / raw)
To: bingyanli@email.ncu.edu.cn; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> -- judgement: s1 = s2 + s3, However, s2 is not correctly computed
[ shrug... ] If you are expecting exact results, don't use
floating-point arithmetic (ie, use "numeric" not "double precision").
This example, which will result in adding values of enormously
different magnitudes in various orders, is tailor-made to
exhibit roundoff error.
https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-FLOAT
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-06-08 15:50 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-06-08 14:38 BUG #18951: Precision loss in inner join while using SUM aggregate function PG Bug reporting form <noreply@postgresql.org>
2025-06-08 15:44 ` Junwang Zhao <zhjwpku@gmail.com>
2025-06-08 15:50 ` Tom Lane <tgl@sss.pgh.pa.us>
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