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: | |
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
[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)