SELECT () does not work properly

Question

When I combine random integer generation with CHOOSE() , I get unexpected NULL values.

The following should only return the letters a - e ; instead, NULL 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 

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

 ;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. The workaround will be fine, but basically I'm curious why I get NULL values ​​at all.

Answer 1

This is strange, probably in the error category. Of course, what you do is weird because you treat a random bit pattern as a number. Must be acceptable, but there may be unintended consequences. And this is not an overflow problem. This happens with other values ​​of 8 .

Write the following (in 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 matters if you assign a value to another variable. I could imagine that some pattern of 8-byte large integers represents NaN, but not that it happens like that.

Given that it does not work with a direct call, but works when there is an intermediate variable, I came to the conclusion that this might be some kind of error. I wonder where it is documented somewhere.