SQL Modulo Function gives the wrong value?

Question

In Javascript, Excel, Python, and a scientific calculator, the modulo operation

-34086.1576962834 % 360.0      <-- python
=MOD(-34086.1576962834, 360.0) <-- Excel function

gives a value of 113.84230371659942. (Or something very close to that, depending on number of decimal places and rounding.)

But in T-SQL,

Select -34086.1576962834 % 360.0

returns a value of -246.1576962834.

I've tried using

Select cast(-34086.1576962834 as numeric(30,15)) % cast(360.0 as numeric(30,15))

As I was thinking that maybe this was an example of float vs fixed decimal math not matching up, but that doesn't fix it.

Why does SQL Server not perform this operation in a manner consistent with calculators / expected results? And more importantly, is there a way to get T-SQL to do arithmetic the same way my calculator does?

I'm primarily using SQL Server 2019, but I've confirmed that SQL Server 2012 and 2014 also give the same -246... results.

The Oracle function mod(x,y) and the python math.fmod(x,y) gives the same -246.nnn result.

Answer 1

According to Wikipedia, the SQL standard implements the truncated version of the % operator which for a number a = nq + r where q is an integer, defines the remainder r as:

r = a - n[a/n] where [] is the integral part function (integer part)¹

So plugging the example into the equation yields

r = -34086.1576962834 - 360.0[-34086.1576962834/360.0] 

  = -34086.1576962834 - 360.0[-94.68377138]

  = -34086.1576962834 - 360.0(-94)

  = -246.1576962834

So according to the SQL Standard the % function is working as intended. See Postgres and MySQL implementing the standard similarly.

If you wish to get the same result from a method that uses floored division instead of the integral part when the dividend is negative, you can add the divisor to the result as [x] + 1 = FLOOR(x) when x is negative.

r + n = a - n[a/n] + n

      = a - n([a/n] + 1) 

      = a - n(FLOOR(a/n))

Or, as demonstrated in this SO answer (n + a % n) % n) will convert to the floored division method whether the dividend is positive or negative².

---

This is what the SQL Standard has to say: (thanks to ypercubeᵀᴹ and this blog post):

<modulus expression> ::=
  MOD <left paren>
      <numeric value expression dividend>
      <comma>
      <numeric value expression divisor>
      <right paren>
...
If <modulus expression> is specified,
then the declared type of each
<numeric value expression>
shall be exact numeric with scale 0 (zero).
The declared type of the result is the
declared type of the immediately contained
<numeric value expression divisor>.
...
9)If <modulus expression> is specified,
  then let N be the value of the immediately
  contained <numeric value expression
  dividend> and let M be the value of the
  immediately contained <numeric value
  expression divisor>.
  Case:
    a)If at least one of N and M is the null
      value, then the result is the null value.
    b)If M is zero,
      then an exception condition is raised:
      data exception—division by zero.
    c)Otherwise, the result is the unique exact
      numeric value R with scale 0 (zero) such
      that all of the following are true:
      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).

So you'll note a lot of "scale of zero" but that isn't relevant as if we remove that restriction from N, M, and R the calculation is unchanged. Moving things around we have:

R = N - M * K with ||R|| < ||M|| and SIGN(R) = SIGN(N).

This isn't math.stackexchange.com, so I won't do a formal proof, but those two restrictions imply that K in this instance is [N/M] and not FLOOR(N/M).

Using your example we then have:

R = -34086.1576962834 - 360.0*(-94)
  = -246.1576962834

---

¹ This is not the same as the FLOOR function when applied to negative values.

² The proof is left as an exercise to the reader because the author hasn't touched a math textbook in over 10 years.

Answer 2

This appears to be an issue with how SQL Server calculates modulus with negative numbers.

You'll notice that if you pull out your scientific calculator (I'm using the Windows built-in calc.exe in scientific mode), and do the math for a positive quotient and negative quotient, you'll get the 113.nnn and 246.nnn numbers:

If we break down the modulus math and do it "the long way" then we can see what SQL Server is doing:

DECLARE @dividend numeric(30,15) = -34086.1576962834;
DECLARE @divisor numeric(30,15)  = 360.0;

SELECT QuotientFloor          = FLOOR(@dividend / @divisor),
       QuotientCeiling        = CEILING(@dividend / @divisor),
       Remainder              = @dividend % @divisor,
       DividendInput          = @dividend,
       DivisorInput           = @divisor,
       QuotientFloorPlusMod   = ( FLOOR(@dividend / @divisor) * @divisor ) + (@dividend % @divisor),
       QuotientCeilingPlusMod = ( CEILING(@dividend / @divisor) * @divisor ) + (@dividend % @divisor);

IMHO, this is a bug

SQL Server is always doing a FLOOR to compute the quotient, rather than always rounding towards zero. In my opinion, this is a bug, as the rules of math tell us we should round towards zero, rather than rounding down.

The Wikipedia article on "Modulo operation" shows that there are different interpretations of what "modulo" means, particularly when moving to real numbers--even though natural numbers have a more universally understood definition of modulus.

According to the above-linked Wikipedia article the ISO SQL Standard calls for the "truncated division" formula:

Many implementations use truncated division, for which the quotient is defined by where [] is the integral part function (rounding toward zero), i.e. the truncation to zero significant digits. Thus according to equation (1), the remainder has the same sign as the dividend:

Note, that this calls for rounding toward zero. This is the behavior that most folks expect, because rounding towards zero feels "natural" to many folks when rounding.

Instead, SQL Server uses the "floored division" method:

Donald Knuth promotes floored division, for which the quotient is defined by where ⌊⌋ is the floor function (rounding down). Thus according to equation (1), the remainder has the same sign as the divisor:

Note, that this calls for rounding down.

Microsoft probably won't change the behavior

SQL Server has had this behavior for so long, I wouldn't expect SQL Server to change it's implementation to use the "truncated division" formula that is called for by the ISO SQL standard for both % and mod(). Changing this functionality now would result in years of code that depends on the existing behavior to break.

Instead, I would expect Microsoft to clearly document the existing functionality to make it clear which method they are using for computing modulus. If we are lucky, they may introduce a new syntax that performs modulus using the other method as well, so that folks can select the appropriate behavior for their need.