Bug #1665 Modulo mathmatical function seems to be flawed
Submitted: 26 Oct 2003 6:26 Modified: 27 Oct 2003 13:40
Reporter: Erlend Oftedal Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:3.23.51 OS:Linux (Linux)
Assigned to: Peter Gulutzan CPU Architecture:Any

[26 Oct 2003 6:26] Erlend Oftedal
Description:
The modulo function returns wrong results for negative numbers:
select -7 % 10;
+---------+
| -7 % 10 |
+---------+
|      -7 |
+---------+
1 row in set (0.01 sec)

and

select -12 % 10;
+----------+
| -12 % 10 |
+----------+
|      -2  |
+----------+
1 row in set (0.01 sec)

How to repeat:
Regular SQL

Suggested fix:
Though the correct result should be -7 % 10 = 3 and -12 % 10 = 8
[27 Oct 2003 2:19] Indrek Siitan
MySQL returns correct values in this case. If you're interested, here's a link
that explains a similar confusion in Excel:

http://mathforum.org/library/drmath/view/52343.html
[27 Oct 2003 2:50] Erlend Oftedal
From the link you sent me, one can read:

--begin quote
 Properly, the modulus operator a mod b should be mathematically
 defined as the number in the range [0,b) that is congruent to a, as
 stated here:

    http://mathworld.wolfram.com/ModulusCongruence.html
--end quote 

This was my point exactly. The modulus should not return a negative when b is positive.
[27 Oct 2003 13:40] Sergei Golubchik
As far as SQL standards are concerned, I can read there about "N % M = R"
(the syntax in the standard is, in fact, MOD(N,M), but it doesn't affect the following).
...
 i) R has the same sign as N.
 ii) The absolute value of R is less than the absolute value of M.
 iii) N = M * K + R for some exact numeric value K with scale 0 (zero).
...

Why do you think that "modulus should not return a negative when b is positive" ?
[27 Oct 2003 17:01] Peter Gulutzan
There is some ambiguity (perhaps "contradiction" would be a 
better word) in the SQL:2003 standard document regarding 
<modulus expression>. We interpret it the way we do, allowing 
a negative result, for three reasons: 
 
(1) the standard document says that the result should look 
like a remainder: 
 
"<modulus expression> operates on two exact numeric arguments 
with scale 0 (zero) and returns the modulus (remainder) of the 
first argument divided by the second argument as an exact numeric 
with scale 0 (zero)." 
-- 4.4.3 Operations involving numbers ("Concepts") 
 
(2) as Mr Golubchik has pointed out earlier, the result should 
take the sign of the first operand 
 
(3) we have compared MySQL's results with the results from 
another DBMS, and found them to be identical. 
 
There was once a proposal for a change to the SQL standard 
in this respect (for an account read Joe Celko's "SQL For 
Smarties" book); however, it does not appear to have made 
its way into the current standard.
[28 Oct 2003 2:49] Erlend Oftedal
After testing this in a few programming languages, I see there is some ambiguaty there as well. Regarding Mr. Golubchik's point ii) and iii) both of these are satisfied when negative N returns a positive R, only K is decreased by 1.
The reason why I feel R should be in [0,M>, is that modulus is a commonly used technique for mapping large values into a smaller adress space.

Anyways it is not a big problem. I can always solve this by using ((N % M)+ M)% M)