Unexpected rand() behaviour?

Question

The TSQL below is supposed to randomly generate 20 rows containing an incrementing ID and a day of the week. See this in action here.

Note: I know this code is already flawed I'm just playing around with an idea.

declare @loop int = 1;
if OBJECT_ID('tempdb..#temp') is not null drop table #temp;
create table #temp(id int, dayofweek varchar(10))

while @loop < 21
begin
    insert into #temp
    values(@loop, case ceiling(rand()*7)
                    when 1 then 'Monday'
                    when 2 then 'Tuesday'
                    when 3 then 'Wednesday'
                    when 4 then 'Thursday'
                    when 5 then 'Friday'
                    when 6 then 'Saturday'
                    when 7 then 'Sunday'
                end)
    set @loop += 1
end

If I do select * from #temp I get some NULL values in my query results for the 'dayofweek' column. Can anybody explain why this is? I've looked at the return results of ceiling(rand()*7) and as far as I can tell it would only ever return results between 1 and 7.

What am I missing?

Very similar to http://stackoverflow.com/questions/32917341/case-statement-not-correctly-matching-expected-values/32917512#32917512 — Shannon Severance, Oct 21 '15 at 23:33

score 7 · Accepted Answer · answered Oct 21 '15 at 22:07

This is very subtle. The problem is that the case expression is being evaluated once for each comparison. So, sometimes all the comparisons will fail, because they are using different numbers.

Buried in the documentation is this note:

Evaluates input_expression, and then in the order specified, evaluates input_expression = when_expression for each WHEN clause.

This is all the more surprising, because rand() doesn't usually evaluate multiple times (in a select, set, or where clause at least). In this case, it seems to be. Fortunately, there is an easy fix for you:

declare @loop int = 1;
declare @dow int;
if OBJECT_ID('tempdb..#temp') is not null drop table #temp;
create table #temp(id int, dayofweek varchar(10))

while @loop < 21
begin
    set @dow = ceiling(rand()*7);

    insert into #temp
    values(@loop, case dow
                    when 1 then 'Monday'
                    when 2 then 'Tuesday'
                    when 3 then 'Wednesday'
                    when 4 then 'Thursday'
                    when 5 then 'Friday'
                    when 6 then 'Saturday'
                    when 7 then 'Sunday'
                end)
    set @loop += 1;
end;

Your fix works, but I'm not sure I follow why my code doesn't work... It's not like all the comparisons fail...the failures seem almost random in themselves. — Mat Richardson, Oct 21 '15 at 22:14

Unexpected rand() behaviour?

1 Answers1