SQL Server SQL()函数使用RAND()函数进行意外行为

Question

在试图使用兰德和选函数在T中生成随机值时，我遇到了一种有趣的server行为。

我的目标是尝试使用RAND()作为rng返回两个给定值中的一个。很容易对吧？

对于那些不知道的人，选择函数接受一个索引号(Int)以及一个值集合，并在指定的索引处返回一个值。相当直截了当。

一开始，我的SQL如下所示：

    select choose(ceiling((rand()*2)) ,'a','b')

令我惊讶的是，这个表达式返回了三个值中的一个: null、'a‘或'b’。因为我没有预料到我开始挖掘的空值。函数返回范围为0(包括)到1(排除)的浮点数。因为我将它乘以2，所以它应该返回从0(包括)到2(不包括)的值。因此，在使用上限函数之后，最终值应该是0,1,2之一。在意识到将值列表扩展为'c‘之后，检查是否会返回。我还查看了顶棚的docs页面，了解到：

返回值的类型与numeric_expression相同。

我假定CEILINGfunction返回int，但在本例中，这意味着在选择中使用之前，该值被隐式转换为int，这在docs页面上声明得足够多：

如果提供的索引值具有int以外的数字数据类型，则该值将隐式转换为整数。

以防万一我加入了一个明确的演员阵容。我的SQL查询现在如下所示：

    select choose(cast(ceiling((rand()*2)) as int) ,'a','b','c')

但是，结果集没有改变。为了检查哪些值会导致问题，我尝试提前生成值并与选择结果一起选择它。看起来是这样的：

    declare @int int = cast(ceiling((rand()*2)) as int)
    select @int,choose( @int,'a','b','c') 

有趣的是，现在结果集改为(1，a)，(2，b)，这是我最初的目标。在深入研究了页面并进行了一些测试之后，我了解到在以下两种情况中的一种情况下返回“null”：

1. 给定的索引为空。
2. 给定索引超出范围

在这种情况下，这意味着在SELECT语句中生成的索引值要么为0，要么大于2/3 (我假设这里不可能出现负数，并从1中选择函数索引)。正如我以前说过的，0应该是一种可能性：

    ceiling((rand()*2))

，但出于某种原因，它从来不是0(至少当我尝试了1次这样的million+ )

    set nocount on
    
    declare @test table(ceiling_rand int)
    declare @counter int = 0
    
    while @counter<1000000
    begin
    insert into @test 
    select ceiling((rand()*2))
    
    set @counter=@counter+1
    end
    
    select distinct ceiling_rand from @test

因此，我假设SELECT中生成的值大于2/3或NULL。为什么只有在SELECT语句中生成时才会出现这种情况？也许分辨出的顺序，内选，芹菜或兰德是不同的，它会看起来吗？的确，我只是尝试了有限的次数，但在这一点上，统计波动的可能性是非常小的。这是个浮点错误吗？我真的被绊倒了，期待着任何解释。

TL;DR：在SELECT语句中生成随机数时，可能值的结果集与在SELECT语句之前生成的结果集不同。

干杯，NFSU

编辑:格式化

Answer 1

如果你看一下执行计划，你就能看到正在发生的事情。

SET SHOWPLAN_TEXT ON

GO

SELECT (select choose(ceiling((rand()*2)) ,'a','b'))

返回

  |--Constant Scan(VALUES:((CASE WHEN CONVERT_IMPLICIT(int,ceiling(rand()*(2.0000000000000000e+000)),0)=(1) THEN 'a' ELSE CASE WHEN CONVERT_IMPLICIT(int,ceiling(rand()*(2.0000000000000000e+000)),0)=(2) THEN 'b' ELSE NULL END END)))

CHOOSE被扩展到

SELECT CASE
         WHEN ceiling(( rand() * 2 )) = 1 THEN 'a'
         ELSE
           CASE
             WHEN ceiling(( rand() * 2 )) = 2 THEN 'b'
             ELSE NULL
           END
       END 

rand()被引用了两次。每个评估都可以返回不同的结果。

您也会遇到同样的问题，下面的重写也将被展开。

  SELECT CASE ceiling(( rand() * 2 ))
         WHEN 1 THEN 'a'
         WHEN 2 THEN 'b'
       END 

避免对此及其任何变体使用CASE。

一种方法是

SELECT JSON_VALUE ( '["a", "b"]' , CONCAT('$[', FLOOR(rand()*2) ,']') )