6

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.

rdbradshaw
  • 223
  • 1
  • 12

2 Answers2

6

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.

Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • What version of SQL Server? I'm using 2012, and my execution plan doesn't show that. – Tab Alleman Feb 03 '16 at 13:58
  • 1
    @TabAlleman I see the same on SQL Server 2005, 2008, 2012 and 2014. I have not tested on 2008R2. It is not visible in the estimated plan only in the actual plan. – Mikael Eriksson Feb 03 '16 at 14:01
  • Then why do I see something different? I'm looking at the actual plan. Do I need to do something differently? I added a screenshot of my execution plan to my answer. – Tab Alleman Feb 03 '16 at 14:14
  • @TabAlleman have a look at the XML version of the plan. – Mikael Eriksson Feb 03 '16 at 14:17
  • @MikaelEriksson Thanks. Easy fix to get around by separating into two steps. Was just an interesting observation that I wanted to find the answer to. – rdbradshaw Feb 16 '16 at 16:58
0

I know this isn't an answer, but I'm putting it here to format the code properly.

When I ran OP's code, I got a "What Happened?" the third time. I wanted to see what Modulo WAS being returned when that happened, to see what insight it might provide, but to do that, I needed a non-moving NewID(), so I transposed his query to this:

DECLARE @NewID UNIQUEIDENTIFIER = NEWID();

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

And I didn't get any occurrences of "What Happened?".

My best guess is that the SQL engine tries to get cute with the order of operations somehow and sometimes ends up doing the modulo on a non-numeric numerator.

But without being able to reproduce the behavior on a transposed query, it's impossible to "catch it in the act".

In the interest of making this an actual answer, I guess to combat this in the future, you should populate a variable with NEWID() rather than putting it inline in your query, if at all possible.

And other forms of "un-nesting" these operations might work as well.

Here is the execution plan I see when executing Mikael's query:

Execution Plan

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • I was doing the same testing and came to the same conclusion. – SQLChao Feb 02 '16 at 21:35
  • I believe this is what is happening. In my specific situation, I can use a temp table to store the integer returned in the `ABS(CheckSUM(NewId())) % 5` statement and run a second process to run the case statement. Just seems like order of operations should allow for this to happen the way it is listed above. – rdbradshaw Feb 02 '16 at 21:48