Strange behaviour of CASE construction

Question

Background : I was trying to get some random 'hex' values while creating dummy data and came up with this construction :

SELECT TOP 100 
   result = (CASE ABS(Binary_Checksum(NewID())) % 16 
                WHEN -1 THEN 'hello'
                WHEN 0 THEN '0' 
                WHEN 1 THEN '1' 
                WHEN 2 THEN '2' 
                WHEN 3 THEN '3'
                WHEN 4 THEN '4' 
                WHEN 5 THEN '5' 
                WHEN 6 THEN '6' 
                WHEN 7 THEN '7'
                WHEN 8 THEN '8' 
                WHEN 9 THEN '9' 
                WHEN 10 THEN 'a' 
                WHEN 11 THEN 'b'
                WHEN 12 THEN 'c' 
                WHEN 13 THEN 'd' 
                WHEN 14 THEN 'e' 
                WHEN 15 THEN 'f' 
                ELSE 'huh'  END)
          FROM sys.objects 

When running this on my SQL Server 2008 R2 instance, I get quite a lot of 'huh' records:

result
------
huh
3
huh
huh
6
8
6

I really don't understand why. What I would expect to happen is :

• for every record NewID() comes up with a new random value
• Binary_Checksum() calculates an int based on said value
• ABS() makes the value positive
• % 16 returns the remainder of that positive value if it would be divided by 16, which then would be a value between 0 and 15
• the CASE construction converts the value to a relevant character
• Since there are WHENs for every value between 0 and 15, the ELSE should never be needed

or at least, that is what I would think should happen... but obviously something goes wrong along the road...

When doing the same thing in a two-step approach (via temp-table), the huh's are gone...

SELECT TOP 100 x = ABS(Binary_Checksum(NewID())) % 16,
               result = 'hello'
  INTO #test
  FROM sys.objects

UPDATE #test 
   SET result = (CASE x WHEN 0 THEN '0' WHEN 1 THEN '1' WHEN 2 THEN '2' WHEN 3 THEN '3'
                        WHEN 4 THEN '4' WHEN 5 THEN '5' WHEN 6 THEN '6' WHEN 7 THEN '7'
                        WHEN 8 THEN '8' WHEN 9 THEN '9' WHEN 10 THEN 'a' WHEN 11 THEN 'b'
                        WHEN 12 THEN 'c' WHEN 13 THEN 'd' WHEN 14 THEN 'e' WHEN 15 THEN 'f' 
                        ELSE 'huh'  END)

SELECT * FROM #test

Anyone who understands this ? As far as I can tell it should give the same result (it IS copy-paste indeed) regardless of me doing it directly or via a temp-table... But obviously something goes wrong if I do it in a single statement.

PS: I don't need a 'fix' for this, I already have a workaround (see below), I merely am hoping someone can explain me why this does what it does.

Workaround :

SELECT TOP 100 result = SubString('0123456789abcdef', 1 + (ABS(Binary_Checksum(NewID())) % 16), 1) 
  FROM sys.objects

Answer 1

The compute scalar in the plan has the following formula

[Expr1038] = Scalar Operator(CASE WHEN abs(binary_checksum(newid()))%(16)=(-1) THEN 'hello' ELSE CASE WHEN abs(binary_checksum(newid()))%(16)=(0) THEN '0' ELSE CASE WHEN abs(binary_checksum(newid()))%(16)=(1) THEN '1' ELSE CASE WHEN abs(binary_checksum(newid()))%(16)=(2) THEN '2' ELSE CASE WHEN abs(binary_checksum(newid()))%(16)=(3) THEN '3' ELSE CASE WHEN abs(binary_checksum(newid()))%(16)=(4) THEN '4' ELSE CASE WHEN abs(binary_checksum(newid()))%(16)=(5) THEN '5' ELSE CASE WHEN abs(binary_checksum(newid()))%(16)=(6) THEN '6' ELSE CASE WHEN abs(binary_checksum(newid()))%(16)=(7) THEN '7' ELSE CASE WHEN abs(binary_checksum(newid()))%(16)=(8) THEN '8' ELSE CASE WHEN abs(binary_checksum(newid()))%(16)=(9) THEN '9' ELSE CASE WHEN abs(binary_checksum(newid()))%(16)=(10) THEN 'a' ELSE CASE WHEN abs(binary_checksum(newid()))%(16)=(11) THEN 'b' ELSE CASE WHEN abs(binary_checksum(newid()))%(16)=(12) THEN 'c' ELSE CASE WHEN abs(binary_checksum(newid()))%(16)=(13) THEN 'd' ELSE CASE WHEN abs(binary_checksum(newid()))%(16)=(14) THEN 'e' ELSE CASE WHEN abs(binary_checksum(newid()))%(16)=(15) THEN 'f' ELSE 'huh' END END END END END END END END END END END END END END END END END)

The random number is repeatedly re-evaluated rather than being evaluated once and kept constant throughout each branch of the CASE statement.

The (fixed) proposed solution in Damien's answer does work for me

SELECT TOP 100 
    result = (CASE ABS(Binary_Checksum(Value)) % 16 
            WHEN -1 THEN 'hello'
            /*...*/
            ELSE 'huh'  END)
          FROM (select NewID() as Value,* from sys.objects ) so

Because the plan has 2 compute scalar operators. The first one with definition

[Expr1038] = Scalar Operator(newid())

Then that constant expression Expr1038 is fed into the CASE expression. I'm not sure that this behavior is absolutely guaranteed however. It may be subject to the whims of the optimiser.

Answer 2

I believe that, contrary to the description of the simple CASE expression, that it actually re-evaluates input_expression for each input_expression = when_expression comparison (this would normally be safe, unless, as in this case, there's a non-deterministic function in input_expression)

So, what happens is that it keeps generating different random numbers between 0 and 15 for each comparison, and the huhs come out if, after 16 evaluations/comparisons, it never generated a matching number.

---

This doesn't generate huhs:

SELECT TOP 100 
    result = (CASE ABS(Binary_Checksum(Value)) % 16 
            WHEN -1 THEN 'hello'
            WHEN 0 THEN '0' 
            WHEN 1 THEN '1' 
            WHEN 2 THEN '2' 
            WHEN 3 THEN '3'
            WHEN 4 THEN '4' 
            WHEN 5 THEN '5' 
            WHEN 6 THEN '6' 
            WHEN 7 THEN '7'
            WHEN 8 THEN '8' 
            WHEN 9 THEN '9' 
            WHEN 10 THEN 'a' 
            WHEN 11 THEN 'b'
            WHEN 12 THEN 'c' 
            WHEN 13 THEN 'd' 
            WHEN 14 THEN 'e' 
            WHEN 15 THEN 'f' 
            ELSE 'huh'  END)
          FROM (select NewID() as Value,* from sys.objects ) so