Case 语句未正确匹配预期值 [英] Case statement not correctly matching expected values

问题描述

我正在尝试生成一些随机数据，并且我一直在使用 newid() 来播种函数，因为它为每一行调用一次，并且保证每次都返回不同的结果.但是，我经常得到的值不等于预期范围内的任何整数.

I'm trying to generate some randomized data, and I've been using newid() to seed functions since it is called once for every row and is guaranteed to return a different result each time. However I'm frequently getting values that are somehow not equal to any integers in the expected range.

我尝试了一些变体，包括 高度赞成，但它们都导致相同的问题.我已将其放入显示问题的脚本中:

I've tried a few variations, including a highly upvoted one, but they all result in the same issue. I've put it into a script that shows the problem:

declare @test table (id uniqueidentifier)
insert into @test
select newid() from sys.objects

select 
    floor(rand(checksum(id)) * 4),
    case isnull(floor(rand(checksum(id)) * 4), -1)
        when 0 then 0
        when 1 then 1
        when 2 then 2
        when 3 then 3
        when -1 then -1
        else 999
    end,
    floor(rand(checksum(newid())) * 4),
    case isnull(floor(rand(checksum(newid())) * 4), -1)
        when 0 then 0
        when 1 then 1
        when 2 then 2
        when 3 then 3
        when -1 then -1
        else 999
    end
from @test

我希望所有四列的结果始终在 0 到 3 的范围内.当从表中检索唯一标识符时，结果总是正确的(前两列).同样，当它们动态输出时，它们也是正确的(第三列).但是当它们动态比较时case 语句中的整数，它通常返回一个超出预期范围的值.

I expect the results to always be in the range 0 to 3 for all four columns. When the unique identifiers are retrieved from a table, the results are always correct (first two columns.) Similarly, when they're output on the fly they're also correct (third column.) But when they're compared on the fly to integers in a case statement, it often returns a value outside the expected range.

这是一个例子，这是我刚刚运行时的前 20 行.如您所见，最后一列中有不应该存在的999"实例:

Here's an example, these are the first 20 rows when I ran it just now. As you can see there are '999' instances in the last column that shouldn't be there:

0   0   3   1
3   3   3   1
0   0   3   3
3   3   2   999
1   1   2   999
3   3   2   1
2   2   0   999
0   0   0   0
3   3   2   0
1   1   3   999
3   3   0   999
2   2   2   2
1   1   3   0
2   2   3   0
3   3   1   999
0   0   1   999
3   3   1   1
0   0   0   3
3   3   0   999
0   0   1   0

一开始我想可能类型强制和我预期的不同，rand() * int 的结果是一个浮点数而不是一个整数.所以我把它全部包裹在地板上以强制它成为一个整数.然后我想也许有一个奇怪的空值潜入，但是在我的 case 语句中，空值将返回为 -1，并且没有.

At first I thought maybe the type coercion was different than I expected, and the result of rand() * int was a float not an int. So I wrapped it all in floor to force it to be an int. Then I thought perhaps there's an odd null value creeping in, but with my case statement a null would be returned as -1, and there are none.

我已经运行了这两个不同的 SQL Server 2012 SP1 实例，都给出了相同的结果.

I've run this one two different SQL Server 2012 SP1 instances, both give the same sort of results.

推荐答案

在第四列中，isnull(floor(rand(rand(checksum(newid())) * 4), -1) 是每行最多评估五次.案例的每个分支一次.每次调用时，值可以不同.所以它可以返回2，不匹配1，3不匹配2，1不匹配3，3不匹配4落到else，返回999.

In the fourth column, isnull(floor(rand(checksum(newid())) * 4), -1) is being evaluated up to five times for each row. Once for each branch of the case. On each call the values can be different. So it can return 2, not match 1, 3 not match 2, 1 not match 3, 3 not match 4 fall to the else and return 999.

这个如果你拿到执行计划就可以看到，再看XML，有一行[空格已添加.]:

This can be seen if you get the execution plan, and look at the XML, there is a line [whitespace added.]:

<ScalarOperator ScalarString="
CASE WHEN isnull(floor(rand(checksum(newid()))*(4.000000000000000e+000)),(-1.000000000000000e+000))=(0.000000000000000e+000) THEN (0) 
    ELSE CASE WHEN isnull(floor(rand(checksum(newid()))*(4.000000000000000e+000)),(-1.000000000000000e+000))=(1.000000000000000e+000) THEN (1) 
        ELSE CASE WHEN isnull(floor(rand(checksum(newid()))*(4.000000000000000e+000)),(-1.000000000000000e+000))=(2.000000000000000e+000) THEN (2) 
            ELSE CASE WHEN isnull(floor(rand(checksum(newid()))*(4.000000000000000e+000)),(-1.000000000000000e+000))=(3.000000000000000e+000) THEN (3) 
                ELSE CASE WHEN isnull(floor(rand(checksum(newid()))*(4.000000000000000e+000)),(-1.000000000000000e+000))=(-1.000000000000000e+000) THEN (-1) 
                    ELSE (999) 
                END 
            END 
        END 
    END 
END
">

将表达式放在 CTE 中似乎可以防止重新计算发生:

Placing the expression in a CTE seems to keep the recomputes from happening:

; WITH T AS (SELECT isnull(floor(rand(checksum(newid())) * 4), -1) AS C FROM @Test)
SELECT CASE C
        when 0 then 0
        when 1 then 1
        when 2 then 2
        when 3 then 3
        when -1 then -1
        else 999 END
FROM T

这篇关于Case 语句未正确匹配预期值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！