2

At least in MySQL v5.1.73 (CentOS 6.6), MOD() function returns a bogus result... unless someone can explain how this is actually correct.

mysql> select MOD(-385.4784399 ,1440);
+-------------------------+
| MOD(-385.4784399 ,1440) |
+-------------------------+
|            -385.4784399 |
+-------------------------+
1 row in set (0.01 sec)

Is this the best alternative?

mysql> select -385.478439885319 - 1440 * FLOOR(-385.478439885319/1440);
+----------------------------------------------------------+
| -385.478439885319 - 1440 * FLOOR(-385.478439885319/1440) |
+----------------------------------------------------------+
|                                        1054.521560114681 |
+----------------------------------------------------------+
1 row in set (0.00 sec)

I think this will work as well, but it's all going the long way around to do something simple.

mysql> select MOD((MOD(-385.478439885319, 1440) + 1440), 1440);
+--------------------------------------------------+
| MOD((MOD(-385.478439885319, 1440) + 1440), 1440) |
+--------------------------------------------------+
|                                1054.521560114681 |
+--------------------------------------------------+
1 row in set (0.00 sec)
tlum
  • 913
  • 3
  • 13
  • 30
  • It isn't broken unless there is some specification you can cite that it isn't meeting. It's defined in the documentation as a remainder, and no example is given wih a negative first argument. – user207421 Jun 09 '15 at 13:56
  • What result do you expect? MOD is modulo operation and returns the remainder of `-385.478439885319` divided by `1440`. Result of modulo operation can't be greater than divisor. – Nicolai Jun 09 '15 at 14:01
  • 1
    It's basic modular arithmetic, and I expect the correct answer... and I even demonstrated the correct answer; I'm just not certain that's the most efficient way to go about it. – tlum Jun 09 '15 at 15:36
  • Google calculator agrees with @tlum https://www.google.com/search?q=-385.4784399+%25+1440&oq=-385.4784399+%25+1440&aqs=chrome..69i57.3392j0j7&sourceid=chrome&es_sm=91&ie=UTF-8 – Martin Burch Jun 09 '15 at 15:50
  • C# does not: `-385.4784399 % 1440 == -385.4784399` – mclark1129 Jun 09 '15 at 16:01
  • There is math, and then there are calculators which implement math. SQL products appear to behave the way they do because the original SQL specification defined only positive numbers, leaving negative numbers to be implementation specific. Consequently, absent a specification, vendors came up with various interpretations for the implementation of each of their calculators. There are no less than three variations, none of which actually return the mathematically correct answer. There was a proposal nearly 20 years ago to fix the divergence, but thus far no one has. – tlum Jun 09 '15 at 16:40

1 Answers1

3

MySQL isn't giving you a bogus result, it's simply using a different implementation of modulus than you are expecting. Unfortunately the term modulus seems to have been defined somewhat ambiguously, and it's implementation varies from language to language. From what I can tell in the Wikipedia on Modulo, MySQL's implementation is using truncated division:

r = a - n * trunc(a / n)

Where you are expecting the implementation to use floored division:

r = a - n * floor(a / n)

Since this how you implemented your first workaround, I'd say it's probably the best alternative to the Mod operator.

From what I've seen (and this is a very quick unscientific analysis!), it seems like more imperative programming languages implement truncated division and more functional mathematical languages seem to use floored division.

mclark1129
  • 7,532
  • 5
  • 48
  • 84
  • 1
    MySQL is giving a mathematically incorrect solution to the problem; this is because truncated division is flawed. Euclidean theorems date back more than 2000 years. Today, calculators attempt to capture the essence of it using modern computer science theorems, but some implementations do a more complete job than others. "Despite its widespread use, truncated division is shown to be inferior to the other definitions." --Leijen, Daan (December 3, 2001). ["Division and Modulus for Computer Scientists"](http://research.microsoft.com/pubs/151917/divmodnote.pdf) Retrieved 2015-06-09. – tlum Jun 09 '15 at 17:40