SQL中CASE表达式内模运算返回NULL的异常行为原因咨询

问题原因 #

核心问题出在NewID()函数的特性上：NewID()是非确定性行级函数，SQL Server不会缓存它的计算结果，查询中每引用一次包含NewID()的表达式，都会重新执行一次计算生成全新的随机值。

两种写法的执行逻辑差异如下：

• 把随机表达式写在CTE中定义为列r：CTE执行时每行只会计算一次随机值，后续SELECT语句中所有对r的引用，用的都是已经计算完成的固定值，必然落在0、1、2的取值范围内，CASE WHEN可以正常匹配到对应结果，不会出现NULL。
• 把随机表达式直接写在CASE关键词后：CASE ABS(CHECKSUM(NewId())) % 3 WHEN 0 THEN 'Foo' WHEN 1 THEN 'Bar' WHEN 2 THEN 'Baz' END的逻辑，会在每个WHEN分支判断时都重新计算一次随机值：
  1. 第一次判断是否等于0，生成的随机值可能为1，匹配失败
  2. 第二次判断是否等于1，重新生成的随机值可能为2，匹配失败
  3. 第三次判断是否等于2，再次重新生成的随机值可能为0，匹配失败
     所有分支都匹配失败时，CASE WHEN就会返回默认的NULL值，这就是你观察到NULL出现的原因。

解决方案 #

提前在CTE或者子查询中把随机值计算为固定列，后续基于这个固定列做条件判断即可，避免多次计算随机值导致的匹配错位，参考写法如下：

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
FROM y

内容的提问来源于stack exchange，提问作者14207973