选择（）不按预期工作[重复]

This question already has an answer here:

这个问题在这里已有答案:

How does this CASE expression reach the ELSE clause? 3 answers

这个CASE表达式如何到达ELSE子句? 3个答案

When I combine random integer generation with CHOOSE() I am getting unexpected NULL values.

当我将随机整数生成与CHOOSE()组合时,我得到了意外的NULL值。

The following should only return letters a-e, instead NULL is also included in the values:

以下应仅返回字母a-e,而值也包括NULL:

Query:

;WITH cte AS (SELECT 1 RN
              UNION  ALL 
              SELECT RN + 1 
              FROM cte
              WHERE RN < 100)
SELECT DISTINCT CHOOSE(1 + ABS(CRYPT_GEN_RANDOM(8)%5),'a','b','c','d','e','f')
FROM cte

Results:

NULL
a
b
c
d
e

Expected Results:

a
b
c
d
e

The random number generation works as expected, returning only values 1-5:

随机数生成按预期工作,仅返回值1-5:

;WITH cte AS (SELECT 1 RN
              UNION  ALL 
              SELECT RN + 1 
              FROM cte
              WHERE RN < 50)
SELECT 1 + ABS(CRYPT_GEN_RANDOM(8)%5)
FROM cte

Demo: SQL Fiddle

演示:SQL小提琴

CHOOSE() works as follows (index starts at 1):

CHOOSE()的工作原理如下(索引从1开始):

SELECT CHOOSE(3,'dog','cat','horse','fish')  
-- horse
SELECT CHOOSE(8,'dog','cat','horse','fish')  
-- NULL

Using random number generation in functions works fine for LEFT(),RIGHT(),CHAR(),etc. A workaround would be fine, but mostly I'm curious as to why I get NULL values at all.

在函数中使用随机数生成对于LEFT(),RIGHT(),CHAR()等工作正常。解决方法很好,但大多数时候我很好奇为什么我会得到NULL值。

1 个解决方案

#1

This is weird, probably in the category of a bug. Of course, what you are doing is strange, because you are treating a random pattern of bits as a number. Should be valid, but there could be unintended consequences. And, this is not an overflow problem. It occurs with other values of 8.

这很奇怪,可能属于bug的范畴。当然,你所做的是奇怪的,因为你将一个随机的位模式视为一个数字。应该是有效的,但可能会产生意想不到的后果。而且,这不是溢出问题。它与其他值8一起出现。

Witness the following (on SQL Fiddle):

见证以下(在SQL小提琴上):

WITH cte AS (SELECT 1 RN
              UNION  ALL 
              SELECT RN + 1 
              FROM cte
              WHERE RN < 100)
SELECT  CHOOSE(1 + ABS(n),'a','b','c','d','e','f'),
        CHOOSE(1 + abs(CRYPT_GEN_RANDOM(8)%5),'a','b','c','d','e','f')
FROM (select abs(CRYPT_GEN_RANDOM(8)%5) as n
      from cte
     ) n
order by 1

The first column is never NULL. The second column is periodically NULL. In other words, it makes a difference if you assign the value to another variable. I could imagine that some pattern of 8-byte big integers represents NaN, but not that this happens so much.

第一列永远不会为NULL。第二列是周期性的NULL。换句话说,如果将值赋给另一个变量,则会有所不同。我可以想象一些8字节大整数的模式代表NaN,但并不是说这种情况发生了太多。

Given that it fails with a direct call but works when there is an intermediate variable, I'm led to the conclusion that this might be some sort of bug. I wonder if it is documented somewhere.

鉴于它在直接调用时失败但在有中间变量时起作用,我得出的结论是,这可能是某种错误。我想知道它是否在某处记录。

#1