SQL's CHOOSE is choosing wrong result (and producing "severe" errors)

Question

WITH SillySequence (Ordinal) AS (
    SELECT 1
    UNION ALL
    SELECT (1 + Ordinal) FROM SillySequence WHERE Ordinal < 100
)
SELECT
    NeverNull =
        CHOOSE(
            RandomValue,
            'one', 'two'),
    SometimesNull =
        CHOOSE(
            1 + ABS(CHECKSUM(NEWID()) % 2),
            'one', 'two')
FROM (
    SELECT RandomValue = 1 + ABS(CHECKSUM(NEWID()) % 2)
    FROM SillySequence
) _

Here are few notes:

• SillySequence exists solely to produce 100 records.
• 1 + ABS(CHECKSUM(NEWID()) % 2) produces (at random) either 1 or 2.
• NeverNull is, well, never NULL. The only difference between NeverNull and SometimesNull is that the first argument for NeverNull's CHOOSE function is calculated inside a separate subquery.
• NULL values are never produced when using % 1. To produce NULL, the divisor must be 2 or greater.

The following example chooses between 5, 6, and NULL at random. It never chooses any of the other values, which leads me to believe that ABS(CHECKSUM(NEWID()) % 2) is occasionally producing NULL when directly provided as an argument to CHOOSE.

WITH SillySequence (Ordinal) AS (
    SELECT 1
    UNION ALL
    SELECT (1 + Ordinal) FROM SillySequence WHERE Ordinal < 100
)
SELECT
    SometimesNull =
        CHOOSE(
            5 + ABS(CHECKSUM(NEWID()) % 2),
            1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
FROM SillySequence

Interestingly, I can always trigger a "severe" error with the following:

SELECT CHOOSE(
    (SELECT 1 + ABS(CHECKSUM(NEWID()) % 2)),
    'one', 'two')

Msg 0, Level 11, State 0, Line 20

Cannot continue the execution because the session is in the kill state.

Msg 0, Level 20, State 0, Line 20

A severe error occurred on the current command. The results, if any, should be discarded.

I've tested the examples above in SQL Server 2016 using compatibility levels 110, 120, and 130.

Answer 1

You misunderstand how choose() works. In my opinion, that is because you are are an intelligent human being who expect software to work in reasonable ways.

The definition of choose(x, a, b) is that it is equivalent to:

(case when x = 1 then a
      when x = 2 then b
 end)

Simply enough when x is a constant. Simple enough when x refers to a column (as in your "never null" example). Simple enough when x is a non-volatile expression. 99.9% counter-intuitive (someone might think differently and comment ;) when x is a non-deterministic expression.

What does this mean? Well, you are executing:

(case when 1 + ABS(CHECKSUM(NEWID()) % 2) = 1 then a
      when 1 + ABS(CHECKSUM(NEWID()) % 2) = 2 then b
 end)

That is, the expression has two random numbers. In fact, one random number for each possible target (I did say "counter-intuitive", didn't I?).

Well, about 25% percent of the time, both comparisons will fail. And, the result will be NULL. I remember spending many hours debugging code when I first learned this.