Numeric Division - Result Scale Calculation Oddity

From: "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
To: PostgreSQL General List <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Numeric Division - Result Scale Calculation Oddity
Date: 2023-03-15 17:19:50
Message-ID: CAKFQuwZ8pKSeJv3PdLHt43eMvKEeOyaK0wsnxMe1mCX6WjZkSw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hey,

v16 to get the nice underscore separators for readability.

This came up on Reddit [1] the other day and boils down to the question:
"why do the two divisions below end up with radically different result
scales?"

postgres=# select .999_999_999_999_999_999_999 / 2;
?column?
-------------------------
0.500000000000000000000
(1 row)

postgres=# select .000_000_000_000_000_000_001 / 2;
?column?
--------------------------------------------
0.0000000000000000000005000000000000000000
(1 row)

Is this an expected difference?

The first example produces a rounded answer since the correct answer will
not fit within the 21 digits of the left input.

The second example has the same exact problem but because the system
calculated a scale of 40 the result does indeed fit without rounding.

I'm getting my head around "weight" finally and realize that the difference
must somehow come down to the discarded zeros in the packed form of
NumericVar. But figured I'd at least post here to see if there is some
foundational knowledge to be shared before I try to figure out exactly what
the algorithm is doing. I did find "The Art of Computer Programming,
Volume 2" by Donald E. Knuth (1997) on my Safari Bookshelf subscription and
skimmed the addition algorithm, but not yet the division one. The code
comments mention Knuth by name though the comment block at the top of
numeric.c doesn't.

[1]
https://www.reddit.com/r/PostgreSQL/comments/11pu7vp/numeric_type_division_weirdness/

Thanks!

David J.

Browse pgsql-general by date

  From Date Subject
Next Message Mark Hill 2023-03-15 18:31:27 uuid-ossp source or binaries for Windows
Previous Message Erik Wienhold 2023-03-15 16:50:40 Re: Removing trailing zeros (decimal places) from a numeric (pre trim_scale()) with unexpected behaviour