2

While I'll admit I'm a bit sketchy on modulo operations of negative numbers, I do know that

-2 mod 50 = 48

in just about every online modulo calculator as well as LibreOffice Calc:

=MOD(-2,50)

And Python:

-2 % 50

This number (48) conveniently is the answer I need for a function I am writing in a MySQL procedure. However, in MySQL:

SELECT MOD(-2,50)

Gives me -2 as a result.

What is the reason for this, and how do I get the result I am looking for in MySQL?

For Posterity:

As of 2018 the following languages provide these different results from -2 % 50:

48

  • Python
  • R
  • Google Calculator
  • Google Sheets
  • LibreOffice Calc
  • Ubuntu Calculator

-2

  • Javascript
  • MySQL
  • Microsoft Calculator
  • PHP

And as pointed out in the comments, a more exhaustive list is here: https://en.wikipedia.org/wiki/Modulo_operation

Trees4theForest
  • 1,267
  • 2
  • 18
  • 48

3 Answers3

4

You could implement your own version of MOD using division as in this question but probably the simplest solution is to perform MOD twice, adding 50 between operations:

SELECT MOD(MOD(input, 50)+50, 50)

This will give you correct results regardless of the input value.

Nick
  • 138,499
  • 22
  • 57
  • 95
2

In sql when you do modulo example Mod(x,y) and x is a negative answer the result is given from 0 to x-1 range. hence you are getting the wrong output.

Perhaps you can model the question in such a way that you are performing the same form of mod.

Try doing the querying like this.

select ((x+y)+y%y) -> select ((-2+50)+50%50)

Should work.

Shalvin
  • 188
  • 1
  • 1
  • 12
2

Unfortunately, many MOD functions and % operators in the world of programming languages do not implement a modulo function in the mathematical sense: for negative arguments, they return -(|a| mod b) instead.

Workarounds:

  • if a < 0, use MOD(a + b - 1, b);

  • use MOD(a + c, b) where c is a multiple of b if it is guaranteed that a + c >= 0 and there is no overflow.