Strange behaviour of CASE modulo returning NULL

user_14207973 0
Viewed 80

I'm trying to generate a set of 10,000 records consisting of random occurrences of three values. I'm using ABS(CHECKSUM(NewId())) % 3 to get a random number between 0 and 2 (inclusive). The query works fine if I define the above expression in a WITH CTE, then use it in a subsequent CASE WHEN. However, when I define it directly in the CASE WHEN, I start getting NULLs. What is the reason for this?

WITH x AS (
    SELECT n 
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v(n)
),
y as (
    SELECT ABS(CHECKSUM(NewId())) % 3 as r
    FROM x ones, x tens, x hundreds, x thousands
)
SELECT r, 
       CASE r                          WHEN 0 THEN 'Foo' WHEN 1 THEN 'Bar' WHEN 2 THEN 'Baz' END,
       CASE ABS(CHECKSUM(NewId())) % 3 WHEN 0 THEN 'Foo' WHEN 1 THEN 'Bar' WHEN 2 THEN 'Baz' END
FROM y

enter image description here

tsql sql-server sql
1 Answers

This is (more or less) covered by the docs:

The simple CASE expression operates by comparing the first expression to the expression in each WHEN clause for equivalency. If these expressions are equivalent, the expression in the THEN clause will be returned.

[..]

  • In the order specified, evaluates input_expression = when_expression for each WHEN clause.

[..]

Note that it does not say input_expression is evaluated once, and indeed it is not. Your CASE is internally rewritten to

CASE 
   WHEN ABS(CHECKSUM(NewId())) % 3 = 0 THEN 'Foo'
   WHEN ABS(CHECKSUM(NewId())) % 3 = 1 THEN 'Bar'
   WHEN ABS(CHECKSUM(NewId())) % 3 = 2 THEN 'Baz'
END

And since each evaluation of NEWID() produces a new value (one of the few expressions in T-SQL to do so), the results of this expression are entirely nondeterministic and it's possible none of the WHEN clauses match while being evaluated in order.

user_41379160
Related