Modulus operator returns negative values / numeric division rounds up sometimes - Mailing list pgsql-general

Alvaro Herrera (and others) have noticed that the numeric modulus operator sometimes gives negative results even when
thedividend and divisor are positive: 

> Oh, and while at it, it would be nice to solve the modulo bug that still lurks there:

alvherre=# select 12345678901234567890 % 123;
 ?column?
----------
      -45
(1 fila)

alvherre=# select 12345678901234567890 % 123::numeric(4,1);
 ?column?
----------
     78.0
(1 fila)

alvherre=# select 12345678901234567890 % 123::numeric(3,0);
 ?column?
----------
      -45
(1 fila)

alvherre=# select version();
                                           version
----------------------------------------------------------------------------------------------
 PostgreSQL 8.1devel on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.6 (Debian 1:3.3.6-4)
(1 fila)

This "bug" happens happens because modulus(a, b) is calculated as a - (truncate(a / b) * b).  However, since the
divisionoperator does its own rounding, then sometimes a / b has already been rounded up, such that truncate(a / b) * b
isgreater than a, thus giving the negative modulus observed. 

Now, Alvaro called it a "bug," but the code comments appear to indicate that this is a feature:

/*
 * div_var() -
 *
 *    Division on variable level. Quotient of var1 / var2 is stored
 *    in result.    Result is ROUNDED to no more than rscale fractional digits.
 */

(From /src/backend/utils/adt/numeric.c  Capitalization added.)

It looks like the "bug" can be easily fixed by changing the end of div_var where it says

    round_var(result, rscale);

to

    trunc_var(result, scale);

I did so myself and recompiled, producing these results:

paul=# select 12345678901234567890 % 123;
 ?column?
----------
       78
(1 row)
paul=# select version();
                                                  version
                                            
------------------------------------------------------------------------------------------------------------
 PostgreSQL 8.0.0rc1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.2 20031022 (Red Hat Linux 3.3.2-1)
(1 row)

I don't think anyone wants to defend the negative modulus as such, but to fix it, we have to do one of these:

(1) Keep rounding division, but rewrite the numeric modulus operator to use a form of division that always rounds
towardszero. 

This violates the identity ((a / b) * b) + (a % b) = a, where (a / b) is coerced to an integer.  (Logically, this
correspondsto saying that a % b is the remainder obtained when dividing a / b.) 

This would probably be preferred by people who use numeric division as a sort of more-precise-than-double floating
pointnumber, as they want division to round to the nearest integer rather than to towards zero. 

or

(2) Give up rounding division in favor of truncating towards zero.

This would probably be preferred by people who think of numeric as a very large integer.

Consider:

paul=# select 9 / 5;
 ?column?
----------
        1
(1 row)

paul=# select 9 % 5;
 ?column?
----------
        4
(1 row)

Also, here is what you get with bc and python:

bc 1.06
Copyright 1991-1994, 1997, 1998, 2000 Free Software Foundation, Inc.
This is free software with ABSOLUTELY NO WARRANTY.
For details type `warranty'.
>>> 12345678901234567890 % 123
78
>>> 12345678901234567890 / 123
100371373180768844
>>> scale=2
>>> 12345678901234567890 / 123
100371373180768844.63

And with python's built-in multi-precision library:

Python 2.2.3 (#1, Oct 15 2003, 23:33:35)
[GCC 3.3.1 20030930 (Red Hat Linux 3.3.1-6)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> 12345678901234567890 % 123
78L
>>>

To me, having numeric division (at least numerics with no fractional part) behave like integer division seems more
useful.

Thoughts, anyone?

Regards,
Paul Tillotson