sql-server - SQL Server CHOOSE() 函数与 RAND() 函数的行为异常

Question

我在尝试使用 RAND 在 T-sql 中生成随机值时遇到了一个有趣的 SQL 服务器行为和 CHOOSE功能。

我的目标是尝试使用 RAND() 作为 rng 返回两个给定值之一。很简单吧？

对于那些不知道的人，CHOOSE 函数接受一个索引号 (int) 以及一组值，并返回指定索引处的值。非常简单。

起初尝试我的 SQL 是这样的:

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

令我惊讶的是，这个表达式返回了三个值之一:null、'a' 或 'b'。因为我没想到我开始挖掘的空值。 RAND() 函数返回一个范围从 0(包括)到 1(不包括)的 float 。因为我将它乘以 2，所以它应该返回 0(包括)到 2(不包括)范围内的任何值。因此，在使用 CEILING 函数后，最终值应为以下之一:0、1、2。在意识到我通过“c”扩展值列表以检查是否可能返回之后。我还查看了 CEILING 的文档页面并了解到:

Return values have the same type as numeric_expression.

我假设 CEILING 函数返回了 int，但在这种情况下意味着该值在用于 CHOOSE 之前被隐式转换为 int，文档页面上确实说明了这一点:

If the provided index value has a numeric data type other than int,then the value is implicitly converted to an integer.

以防万一我添加了一个显式转换。我的 SQL 查询现在看起来像这样:

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

但是，结果集没有改变。为了检查是哪些值导致了问题，我尝试预先生成该值并将其与 CHOOSE 结果一起选择。它看起来像这样:

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

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

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

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

    ceiling((rand()*2))

，但由于某种原因它永远不会为 0(至少当我像这样尝试了 100 万次以上时)

    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语句中生成才会这样呢？也许在 SELECT 中解析 CAST、CELING 或 RAND 的顺序与看起来不同？的确，我只尝试了有限的次数，但在这一点上，它成为统计波动的可能性非常小。不知何故这是一个浮点错误？我真的很迷茫，期待任何解释。

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

干杯，南方科技大学

编辑:格式化

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) ,']') )