Re: [COMMITTERS] pgsql: Fix NUMERIC modulus to properly truncate

Tom Lane wrote:
> Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> >> No, I don't think so.  It doesn't seem to be something that enough
> >> people use to risk the change in behavior --- it might break something
> >> that was working.  But, if folks want it backported we can do it.  It is
> >> only a change to properly do modulus for numeric.
> 
> > Well, from my point of view it's an absolute mathematical error - i'd 
> > backport it.  I can't see anyone relying on it :)
> 
> Doesn't this patch break the basic theorem that
> 
>     a = trunc(a / b) * b + (a mod b)
> 
> ?  If division rounds and mod doesn't, you've got pretty serious issues.

Well, this is a good question.  In the equation above we assume '/' is
an integer division.  The problem with NUMERIC when used with zero-scale
operands is that the result is already _rounded_ to the nearest hole
number before it gets to trunc(), and that is why we used to get
negative modulus values.  I assume the big point is that we don't offer
any way for users to get a NUMERIC division without rounding.

With integers, we always round down to the nearest whole number on
division;  float doesn't offer a modulus operator, and C doesn't support
it either.

We round NUMERICs to the specific scale because we want to give the most
accurate value:
test=> select 100000000000000000000000::numeric(24,0) /11::numeric(24,0);        ?column?------------------------
9090909090909090909091

The actual values is:                               -- 9090909090909090909090.90

But the problem is that the equation at the top assumes the division is
not rounded.  Should we supply a NUMERIC division operator that doesn't
round?  integer doesn't need it, and float doesn't have the accurate
precision needed for modulus operators.  The user could supply some
digits in the division:test=> select 100000000000000000000000::numeric(30,6) /11::numeric(24,0);
?column?-------------------------------9090909090909090909090.909091(1 row)
 

but there really is no _right_ value to prevent rounding (think
0.9999999).  A non-rounding NUMERIC division would require duplicating
numeric_div() but with a false for 'round', and adding operators.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073