8

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.

2
  • 3
    "In Javascript" - no it doesn't. % is the remainder operator in JavaScript, and returns a negative value for negative inputs.
    – Bergi
    Nov 19, 2022 at 0:53
  • I though that in JS (and unlike, say, Python), % takes the sign of the dividend?
    – 681234
    Nov 19, 2022 at 19:51

2 Answers 2

13

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)1

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 negative2.


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

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

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

1
  • 2
    Db2 and Firebird also return -246.xxx. So it seems all SQL databases agree on how this should work
    – user1822
    Nov 19, 2022 at 14:12
3

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: Calculator

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 truncated division Quotient formula 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: truncated division Mod formula

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 floored division quotient formula where ⌊⌋ is the floor function (rounding down). Thus according to equation (1), the remainder has the same sign as the divisor: floored division mod formula

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.

5
  • 3
    You have the implementations flipped - SQL Standard uses the truncated division (remainder is the same sign as the dividend), floored division yields -34086.1576962834 - 360.0*FLOOR(-34086.1576962834/360) = -34086.1576962834 - 360.0*FLOOR(-94.683771378565) = -34086.1576962834 - 360.0*(-95) = 113.8423037166
    – user212533
    Nov 18, 2022 at 17:59
  • 3
    The SQL standard defines the <modulus expression> to be what SQL Server implements with % (albeit defined in the standard only for numbers of scale 0). I don't see how you reached the opposite conclusion. Nov 18, 2022 at 18:42
  • 2
    Per the example in the question and your answer: dividend=-34086.1576962834, remainder=-246.1576962834. Both negative, as in "Truncated" method. Nov 18, 2022 at 18:53
  • "this is a bug" huh? rounding towards zero gets the negative result.
    – Jasen
    Nov 19, 2022 at 8:17
  • 1
    C#, Java, and JavaScript with % operator yield -246.15769628340058. The same does C with fmod(), C++ with std::fmod(), Go using math.Mod(), PHP with fmod() and Unix bc. On the other hand Python, R, and Ruby all return 113.8423. So not sure where's the issue.
    – Endrju
    Nov 19, 2022 at 22:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.