Answered by:
A strange query behaviour with RAND()
Question
Answers

Please refer to below linkwhere it is explained that null out put is expectedhttp://manuals.sybase.com/onlinebooks/groupas/asg1250e/sqlug/@Generic__BookTextView/39235;pt=39091
"
Using rand() functions in case expressionsExpressions that reference the rand function, the getdate function, and so on, produce different values each time they are evaluated. This can yield unexpected results when you use these expressions in certaincase expressions. For example, the SQL standard specifies that case expressions with the form:
case expression when value1 then result1 when value2 then result2 when value3 then result3 ... end
are equivalent to the following form of case expression:
case expression when expression=value1 then result1 when expression=value2 then result2 when expression=value3 then result3 ... end
This definition explicitly requires that the expression be evaluated repeatedly in each when clause that is examined. This definition of case expressions affects case expressions that reference functions such as the rand function. For example, the following case expression:
select CASE convert(int, (RAND() * 3)) when 0 then "A" when 1 then "B" when 2 then "C" when 3 then "D" else "E" end
is defined to be equivalent to the following according to the SQL standard:
select CASE when convert(int, (RAND() * 3)) = 0 then "A" when convert(int, (RAND() * 3)) = 1 then "B" when convert(int, (RAND() * 3)) = 2 then "C" when convert(int, (RAND() * 3)) = 3 then "D" else "E" end
In this form, a new rand value is generated for each when clause, and the case expression frequently produce the result "E".
"
also u can try this as per SQL standardI guess the problem is due to type casting ..
when i tried with this ,it never returned null
select case
when rand()*4 <1 then 0
when rand()*4 <2 then 1
when rand()*4 <3 then 2
when rand()*4 <=4 then 3
endalso keep a condition for rand()*4 = 4 also,this is not handled in the code ,in that case also it returns null
Thanks and Regards, Sandhya
 Edited by SandhyaDevineni Monday, May 21, 2012 9:49 AM valid with this
 Proposed as answer by SergNL Monday, May 21, 2012 1:34 PM
 Marked as answer by nicofer Tuesday, May 22, 2012 1:44 AM

Hi Sandhya, thanks for your link, I got it.
To be more clear, we use a table for example:
set statistics profile on select case cast(rand()*4 as int) when 0 then 0 when 1 then 1 when 2 then 2 when 3 then 3 end FROM [sys].[columns]
then its execute plan :
Compute Scalar(DEFINE:([Expr1017]=CASE WHEN CONVERT(int,rand()*(4.000000000000000e+000),0)=(0) THEN (0) ELSE CASE WHEN CONVERT(int,rand()*(4.000000000000000e+000),0)=(1) THEN (1) ELSE CASE WHEN CONVERT(int,rand()*(4.000000000000000e+000),0)=(2) THEN (2) ELSE CASE WHEN CONVERT(int,rand()*(4.000000000000000e+000),0)=(3) THEN (3) ELSE NULL END END END END))
It shows that a new rand value is generated for each when clause.
aha,Thanks all.nicofer
All replies





To avoid any null case, you can try with this:
select case cast(isnull(rand(),0)*4 as int)
when 0 then 0
when 1 then 1
when 2 then 2
when 3 then 3
end
Many Thanks & Best Regards, Hua Min
Hi Hua, thanks,but it still reunts null .
select case isnull(cast(rand()*4 as int),'0') when 0 then 0 when 1 then 1 when 2 then 2 when 3 then 3 end select case cast(isnull(rand(),0)*4 as int) when 0 then 0 when 1 then 1 when 2 then 2 when 3 then 3 end
Thanks,
nicofer


interesting.
 Edited by Steven Wang  Shangzhou Monday, May 21, 2012 9:41 AM

Hi Paresh, thanks for your reply, In fact, I know this solution,the below script is ok.
declare @value INT select @value = cast(rand()*4 as int) select @value,case @value when 0 then 0 when 1 then 1 when 2 then 2 when 3 then 3 end
aha, I just would like to know why ? It is very interesting, is it a bug for MS SQL Server?

Please refer to below linkwhere it is explained that null out put is expectedhttp://manuals.sybase.com/onlinebooks/groupas/asg1250e/sqlug/@Generic__BookTextView/39235;pt=39091
"
Using rand() functions in case expressionsExpressions that reference the rand function, the getdate function, and so on, produce different values each time they are evaluated. This can yield unexpected results when you use these expressions in certaincase expressions. For example, the SQL standard specifies that case expressions with the form:
case expression when value1 then result1 when value2 then result2 when value3 then result3 ... end
are equivalent to the following form of case expression:
case expression when expression=value1 then result1 when expression=value2 then result2 when expression=value3 then result3 ... end
This definition explicitly requires that the expression be evaluated repeatedly in each when clause that is examined. This definition of case expressions affects case expressions that reference functions such as the rand function. For example, the following case expression:
select CASE convert(int, (RAND() * 3)) when 0 then "A" when 1 then "B" when 2 then "C" when 3 then "D" else "E" end
is defined to be equivalent to the following according to the SQL standard:
select CASE when convert(int, (RAND() * 3)) = 0 then "A" when convert(int, (RAND() * 3)) = 1 then "B" when convert(int, (RAND() * 3)) = 2 then "C" when convert(int, (RAND() * 3)) = 3 then "D" else "E" end
In this form, a new rand value is generated for each when clause, and the case expression frequently produce the result "E".
"
also u can try this as per SQL standardI guess the problem is due to type casting ..
when i tried with this ,it never returned null
select case
when rand()*4 <1 then 0
when rand()*4 <2 then 1
when rand()*4 <3 then 2
when rand()*4 <=4 then 3
endalso keep a condition for rand()*4 = 4 also,this is not handled in the code ,in that case also it returns null
Thanks and Regards, Sandhya
 Edited by SandhyaDevineni Monday, May 21, 2012 9:49 AM valid with this
 Proposed as answer by SergNL Monday, May 21, 2012 1:34 PM
 Marked as answer by nicofer Tuesday, May 22, 2012 1:44 AM

Hi,
select cast(rand()*4 as int) never returns a null value, however your select query doesn't have a condition to say what value to be returned when the case is >4. hence its returning a NULL value(consider this as a unknown value)
If you want to see what is the value of select cast(rand()*4 as int) then
try this
select case cast(rand()*4 as int) when 0 then 0 when 1 then 1 when 2 then 2 when 3 then 3 else cast(rand()*4 as int) end
Regards
Satheesh
 Edited by Satheesh VariathMicrosoft community contributor Monday, May 21, 2012 1:24 PM

Hi Sandhya, thanks for your link, I got it.
To be more clear, we use a table for example:
set statistics profile on select case cast(rand()*4 as int) when 0 then 0 when 1 then 1 when 2 then 2 when 3 then 3 end FROM [sys].[columns]
then its execute plan :
Compute Scalar(DEFINE:([Expr1017]=CASE WHEN CONVERT(int,rand()*(4.000000000000000e+000),0)=(0) THEN (0) ELSE CASE WHEN CONVERT(int,rand()*(4.000000000000000e+000),0)=(1) THEN (1) ELSE CASE WHEN CONVERT(int,rand()*(4.000000000000000e+000),0)=(2) THEN (2) ELSE CASE WHEN CONVERT(int,rand()*(4.000000000000000e+000),0)=(3) THEN (3) ELSE NULL END END END END))
It shows that a new rand value is generated for each when clause.
aha,Thanks all.nicofer

when i tried with this ,it never returned null
select case
when rand()*4 <1 then 0
when rand()*4 <2 then 1
when rand()*4 <3 then 2
when rand()*4 <=4 then 3
end
It is true that the above expression will never return NULL, however, it is not equally likely to return all 4 values. That is because, as you noted above, the rand() is evaluated up to 4 times. The first call to rand()*4, on average 25% of the time it will be < 1, so case statement will exit returning 0. So 0 will get returned 25% of the time. This, of course, is what we want. BUT, 75% of the time the first call to rand()*4 will be >= 1, so the case statement will call rand()*4 again. Now for this second call, on average 50% of the time it will be < 2, but since the value returned by the second call to rand() is independent of the value returned by the first call, you get 1 returned only if BOTH the first rand()*4 >= 1 AND the second rand()*4 < 2. So to compute the probability, we must multiply the two probabilities together, that is, .75 * .5 or .375. So, on average, 37.5% of the time you will get 1 returned by the case statement. The same line of argument will show that the expected chance of the value 2 being returned is 28.125% and the value 3 is returned only 9.375% of the time. To test this, run the following which does that case statement 10,000 and keeps a count of how many times each value is returned. The counts will, of course, vary since rand() is a random number generator, but they will be close to the above percentages.
Declare @Count0 int, @Count1 int, @Count2 int, @Count3 int, @ResultOfRandCase int, @LoopCount int; Set @Count0 = 0; Set @Count1 = 0; Set @Count2 = 0; Set @Count3 = 0; Set @LoopCount = 0 While @LoopCount < 10000 Begin select @ResultOfRandCase = case when rand()*4 <1 then 0 when rand()*4 <2 then 1 when rand()*4 <3 then 2 when rand()*4 <=4 then 3 end If @ResultOfRandCase = 0 Set @Count0 = @Count0 + 1; If @ResultOfRandCase = 1 Set @Count1 = @Count1 + 1; If @ResultOfRandCase = 2 Set @Count2 = @Count2 + 1; If @ResultOfRandCase = 3 Set @Count3 = @Count3 + 1; Select @LoopCount = @LoopCount + 1; End; Select @Count0 As Count0, @Count1 As Count1, @Count2 As Count2, @Count3 As Count3;
So how should we do this if we want each value to be equally likely? To do that, you want to call rand() one time, and store that in a variable. Then do the case on the variable, for example,
Declare @Count0 int, @Count1 int, @Count2 int, @Count3 int, @ResultOfRand float, @ResultOfRandCase int, @LoopCount int; Set @Count0 = 0; Set @Count1 = 0; Set @Count2 = 0; Set @Count3 = 0; Set @LoopCount = 0 While @LoopCount < 10000 Begin Select @ResultOfRand = rand(); select @ResultOfRandCase = case when @ResultOfRand*4 <1 then 0 when @ResultOfRand*4 <2 then 1 when @ResultOfRand*4 <3 then 2 when @ResultOfRand*4 <=4 then 3 end If @ResultOfRandCase = 0 Set @Count0 = @Count0 + 1; If @ResultOfRandCase = 1 Set @Count1 = @Count1 + 1; If @ResultOfRandCase = 2 Set @Count2 = @Count2 + 1; If @ResultOfRandCase = 3 Set @Count3 = @Count3 + 1; Select @LoopCount = @LoopCount + 1; End; Select @Count0 As Count0, @Count1 As Count1, @Count2 As Count2, @Count3 As Count3;
Tom