CHOOSE() not working as intended [duplicate]

Question

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

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

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:

;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

CHOOSE() works as follows (index starts at 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.

Answer 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.

Witness the following (on SQL Fiddle):

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.

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.