Re: BUG #18951: Precision loss in inner join while using SUM aggregate function

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