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.

asked 2022-11-18 by CaM

---

Answer

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.

answered 2022-11-18 by Andy Mallon