public inbox for [email protected]
help / color / mirror / Atom feedInconsistent results for division and multiplication operations
6+ messages / 6 participants
[nested] [flat]
* Inconsistent results for division and multiplication operations
@ 2024-11-25 15:46 =?ISO-8859-1?B?c3p5?= <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: =?ISO-8859-1?B?c3p5?= @ 2024-11-25 15:46 UTC (permalink / raw)
To: =?ISO-8859-1?B?cGdzcWwtc3Fs?= <[email protected]>
Hi PostgreSQL community,
I have observed inconsistent results when performing division and multiplication operations in PostgreSQL.
postgres=# select 1.003/1.002*5.01;
?column?
--------------------------
5.0149999999999999999806
(1 row)
postgres=# select 1.003*5.01/1.002;
?column?
--------------------
5.0150000000000000
(1 row)
However, the expected result should be consistent for both queries. The actual results differ
szy
[email protected]
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Inconsistent results for division and multiplication operations
@ 2024-11-25 15:53 Erik Brandsberg <[email protected]>
parent: =?ISO-8859-1?B?c3p5?= <[email protected]>
0 siblings, 3 replies; 6+ messages in thread
From: Erik Brandsberg @ 2024-11-25 15:53 UTC (permalink / raw)
To: szy <[email protected]>; +Cc: pgsql-sql <[email protected]>
This is a common issue with using floating point math. You will see the
same issue with many systems. Basically, the order of operations can
trigger very minor differences in results, but if you round the first
result to the same number of significant digits as the input, it would be
identical.
https://learn.microsoft.com/en-us/office/troubleshoot/access/floating-calculations-info
On Mon, Nov 25, 2024 at 10:46 AM szy <[email protected]> wrote:
>
> Hi PostgreSQL community,
>
> I have observed inconsistent results when performing division and
> multiplication operations in PostgreSQL.
>
> postgres=# select 1.003/1.002*5.01;
> ?column?
> --------------------------
> 5.0149999999999999999806
> (1 row)
>
> postgres=# select 1.003*5.01/1.002;
> ?column?
> --------------------
> 5.0150000000000000
> (1 row)
>
> However, the expected result should be consistent for both queries. The
> actual results differ
>
>
> ------------------------------
> szy
> [email protected]
>
> <https://wx.mail.qq.com/home/index?t=readmail_businesscard_midpage&nocheck=true&name=szy&...;
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* =?gb18030?B?u9i4tKO6IEluY29uc2lzdGVudCByZXN1bHRzIGZv?= =?gb18030?B?ciBkaXZpc2lvbiBhbmQgbXVsdGlwbGljYXRpb24g?= =?gb18030?B?b3BlcmF0aW9ucw==?=
@ 2024-11-25 16:12 =?gb18030?B?c3p5?= <[email protected]>
parent: Erik Brandsberg <[email protected]>
2 siblings, 1 reply; 6+ messages in thread
From: =?gb18030?B?c3p5?= @ 2024-11-25 16:12 UTC (permalink / raw)
To: =?gb18030?B?RXJpayBCcmFuZHNiZXJn?= <[email protected]>; +Cc: =?gb18030?B?cGdzcWwtc3Fs?= <[email protected]>
If the number of significant digits in the input is not fixed, it becomes challenging to achieve consistent results by rounding.
for example
postgres=# select round(1.003/1.002*5.01,2);
?column?
--------------------------
5.01
(1 row)
postgres=# select round(1.003*5.01/1.002,2);
?column?
--------------------
5.02
(1 row)
szy
[email protected]
This is a common issue with using floating point math. You will see the same issue with many systems. Basically, the order of operations can trigger very minor differences in results, but if you round the first result to the same number of significant digits as the input, it would be identical. https://learn.microsoft.com/en-us/office/troubleshoot/access/floating-calculations-info
On Mon, Nov 25, 2024 at 10:46 AM szy <[email protected]> wrote:
Hi PostgreSQL community,
I have observed inconsistent results when performing division and multiplication operations in PostgreSQL.
postgres=# select 1.003/1.002*5.01;
?column?
--------------------------
5.0149999999999999999806
(1 row)
postgres=# select 1.003*5.01/1.002;
?column?
--------------------
5.0150000000000000
(1 row)
However, the expected result should be consistent for both queries. The actual results differ
szy
[email protected]
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Inconsistent results for division and multiplication operations
@ 2024-11-25 16:23 Philip Semanchuk <[email protected]>
parent: Erik Brandsberg <[email protected]>
2 siblings, 0 replies; 6+ messages in thread
From: Philip Semanchuk @ 2024-11-25 16:23 UTC (permalink / raw)
To: Erik Brandsberg <[email protected]>; +Cc: szy <[email protected]>; pgsql-sql <[email protected]>
> On Nov 25, 2024, at 10:53 AM, Erik Brandsberg <[email protected]> wrote:
>
> This is a common issue with using floating point math. You will see the same issue with many systems. Basically, the order of operations can trigger very minor differences in results, but if you round the first result to the same number of significant digits as the input, it would be identical. https://learn.microsoft.com/en-us/office/troubleshoot/access/floating-calculations-info
@szy Erik is right on target. The fine details of floating point math confuse almost everyone at first encounter. If it makes you feel any better, Python (and IIUC any other language that uses IEEE floating point notation) exhibits the same quirk -
$ python
>>> 1.003/1.002*5.01
5.015
>>> 1.003*5.01/1.002
5.014999999999999
If you need extremely accurate representation of numbers (e.g. for tracking money), use fixed precision (the numeric type in Postgres). Math operations are much faster on floating point than on fixed precision, so floating point is the default data type for non-integral values. Fixed precision is more of a “use as necessary” data type.
Hope this helps
Philip
> On Mon, Nov 25, 2024 at 10:46 AM szy <[email protected]> wrote:
>
> Hi PostgreSQL community,
>
> I have observed inconsistent results when performing division and multiplication operations in PostgreSQL.
>
> postgres=# select 1.003/1.002*5.01;
> ?column?
> --------------------------
> 5.0149999999999999999806
> (1 row)
>
> postgres=# select 1.003*5.01/1.002;
> ?column?
> --------------------
> 5.0150000000000000
> (1 row)
>
> However, the expected result should be consistent for both queries. The actual results differ
>
>
> szy
> [email protected]
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Inconsistent results for division and multiplication operations
@ 2024-11-25 16:34 Tom Lane <[email protected]>
parent: Erik Brandsberg <[email protected]>
2 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2024-11-25 16:34 UTC (permalink / raw)
To: Erik Brandsberg <[email protected]>; +Cc: szy <[email protected]>; pgsql-sql <[email protected]>
Erik Brandsberg <[email protected]> writes:
> This is a common issue with using floating point math. You will see the
> same issue with many systems. Basically, the order of operations can
> trigger very minor differences in results, but if you round the first
> result to the same number of significant digits as the input, it would be
> identical.
> https://learn.microsoft.com/en-us/office/troubleshoot/access/floating-calculations-info
Yeah. The OP is actually working with PG's "numeric" type, not
floating-point, but the principle is the same. Some division
results can't be represented exactly in any finite number of
digits, so you get roundoff error.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Inconsistent results for division and multiplication operations
@ 2024-11-26 08:13 Martin Norbäck Olivers <[email protected]>
parent: =?gb18030?B?c3p5?= <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Martin Norbäck Olivers @ 2024-11-26 08:13 UTC (permalink / raw)
To: szy <[email protected]>; +Cc: Erik Brandsberg <[email protected]>; pgsql-sql <[email protected]>
On Mon, Nov 25, 2024 at 5:18 PM szy <[email protected]> wrote:
> If the number of significant digits in the input is not fixed, it becomes
> challenging to achieve consistent results by rounding.
> for example
> postgres=# select round(1.003/1.002*5.01,2);
> ?column?
> --------------------------
> 5.01
> (1 row)
>
> postgres=# select round(1.003*5.01/1.002,2);
> ?column?
> --------------------
> 5.02
> (1 row)
>
>
Correct. That's why you should always use numeric with the desired
precision if you want precision numbers.
for instance
select 1.003/1.002*5.01 :: numeric(10,4)
will give the same result as
select 1.003*5.01/1.002 :: numeric(10,4)
They are much slower to calculate than floating point, however, so if you
don't care about precision you can keep using just floating point.
Regards,
Martin
--
Martin Norbäck Olivers
IT-konsult, Masara AB
Telefon: +46 703 22 70 12
E-post: [email protected]
Kärrhöksvägen 4
656 72 Skattkärr
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2024-11-26 08:13 UTC | newest]
Thread overview: 6+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-11-25 15:46 Inconsistent results for division and multiplication operations =?ISO-8859-1?B?c3p5?= <[email protected]>
2024-11-25 15:53 ` Erik Brandsberg <[email protected]>
2024-11-25 16:12 ` =?gb18030?B?u9i4tKO6IEluY29uc2lzdGVudCByZXN1bHRzIGZv?= =?gb18030?B?ciBkaXZpc2lvbiBhbmQgbXVsdGlwbGljYXRpb24g?= =?gb18030?B?b3BlcmF0aW9ucw==?= =?gb18030?B?c3p5?= <[email protected]>
2024-11-26 08:13 ` Martin Norbäck Olivers <[email protected]>
2024-11-25 16:23 ` Philip Semanchuk <[email protected]>
2024-11-25 16:34 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox