SQL Server Modulo function appears to return values outside of expected range [duplicate]

Question

While running a modulo function inside a case statement, there are many times that a value outside of the expected range is returned.

SELECT CASE WHEN ABS(CheckSUM(NewId())) % 5 IN (0,1,2,3,4) then NULL 
       ELSE 'What Happened?' END 

If you run this script a few times, you will see there are times that the result appears to be outside of the range of 0,1,2,3,4. My thinking is that this is somehow returning non-integer values during the case statement causing modulo to be an ineffective method of sorting by case.

Could someone explain what is happening in these cases so that I can combat this in the future?

NOTE: If I run the code modulo function by itself (outside of case statement) and return the results all values are in the range of 0,1,2,3,4 as expected.

Answer 1

Change your statement to

SELECT top(1)
 CASE WHEN ABS(CheckSUM(NewId())) % 5 IN (0,1,2,3,4) then NULL 
       ELSE 'What Happened?' END 

And have a look at the actual execution plan.

The IN part in the case is expanded to.

CASE WHEN abs(checksum(newid()))%(5)=(4) OR 
          abs(checksum(newid()))%(5)=(3) OR 
          abs(checksum(newid()))%(5)=(2) OR 
          abs(checksum(newid()))%(5)=(1) OR 
          abs(checksum(newid()))%(5)=(0) 
  THEN NULL 
  ELSE 'What Happened?' 
END

abs(checksum(newid()))%(5) is executed once for each value in the in clause.