I see there are a few misconceptions about numeric and modulus on here:
(1) A modulus operation on a numeric type should NOT have rounding
errors. The whole point of numeric is that it is an arbitrary precision
BASE 10 representation of your number. The modulus returns the (whole
number) remainder as a result of a division.
(2) the modulus operator/function is, AFAIK, supposed to return the
modulus with the SAME SIGN as the divisor, so I think this is a bug.
That's what every other modulus operator that I have ever seen does.
Would you mind doing
foodb=> SELECT version();
(3) MySQL just rounds large numbers to the highest value that the type
will support, and apparently, no arbitrary precision types are listed on
this page:
http://dev.mysql.com/doc/mysql/en/Numberic_type_overview.html
You can't expect to get a modulus from an out-of-range number.
Paul Tillotson
Chadwick Boggs wrote:
> Example of wrong results from modulo operation of arbitrary precision
> numbers:
>
> # select '123456789012345678901234567890'::numeric % 123;
> ?column?
> ----------
> -6
> (1 row)
>
> # select mod('123456789012345678901234567890'::numeric, 123);
> mod
> -----
> -6
> (1 row)
>
> The correct result (at least according to another, unnamed, RDBMS):
>
> > select '123456789012345678901234567890' % 123;
> +----------------------------------------+
> | '123456789012345678901234567890' % 123 |
> +----------------------------------------+
> | 58 |
> +----------------------------------------+
> 1 row in set (0.00 sec)
>
>
> Bruno Wolff III wrote:
>
>> On Mon, Apr 26, 2004 at 10:18:52 -0400,
>> Chadwick Boggs <chadwickboggs@yahoo.com> wrote:
>>
>>
>>> I need to perform modulo operations on extremely large numbers. The
>>> % operator is giving me number out of range errors and the mod(x, y)
>>> function simply seems to return the wrong results. Also, my
>>> numerator is in the format of a quoted string, which the mod
>>> function can't take.
>>>
>>
>>
>> How large is extremely large?
>> You can cast the strings to a numeric type to solve the string problem.
>> 'numeric' should work for numbers up to about 1000 digits.
>> For example:
>> area=> select '1234567890'::numeric % '123'::numeric;
>> ?column?
>> ----------
>> 39
>> (1 row)
>>
>> If you are getting wrong results you should post a specific example so
>> that the developers can figure out what is going wrong.
>>
>>
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>