none
A strange query behaviour with RAND()

    Question

  • Hi guys,

    Why the following query script returns null value sometimes ? Is it a bug ?

    select case cast(rand()*4 as int)
    when 0 then 0
    when 1 then 1
    when 2 then 2
    when 3 then 3
    end 

    Any help would be appreciated.

    Thanks & Regards

    nicofer


    Best Regards, nicofer

    Monday, May 21, 2012 8:48 AM

Answers


  • Please refer to below linkwhere it is explained that null out put is expected 

    http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/39235;pt=39091

    "

    Using rand() functions in case expressions

    Expressions 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 standard 

    I 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
    end  

    also 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
    Monday, May 21, 2012 9:32 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



    • Marked as answer by nicofer Tuesday, May 22, 2012 1:45 AM
    • Edited by nicofer Tuesday, May 22, 2012 1:45 AM
    Tuesday, May 22, 2012 1:44 AM

All replies

  • There is no condition specified if 

    case cast(rand()*4 as int) is other than 0,1,2,3  so sql return null when the condition is not satisfied

    Thanks and Regards, Sandhya

    Monday, May 21, 2012 8:52 AM
  • To be more clear

    add else block  

    select case cast(rand()*4 as int)
    when 0 then 0
    when 1 then 1
    when 2 then 2
    when 3 then 3

    else .......
    end 


    Thanks and Regards, Sandhya

    Monday, May 21, 2012 8:59 AM
  • 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

    Monday, May 21, 2012 9:09 AM
  • Hi Sandhya, Thanks for the quick response. but i would like to know why ? why return null ?
    in fact , the result of "select  cast(rand()*4 as int)" never returns null value .

    Thanks,

    nicofer

    Monday, May 21, 2012 9:10 AM
  • 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

    Monday, May 21, 2012 9:14 AM
  • check this :

    declare @value INT
    select @value = cast(isnull(cast(rand()*4 as int),'0') AS INT)
    select @value,case @value
    when 0 then 0 
    when  1 then 1 
    when  2 then 2
    when  3 then 3
    end  



    Please vote if you find this posting was helpful or Mark it as answered.

    Monday, May 21, 2012 9:23 AM
  • interesting.

    Monday, May 21, 2012 9:29 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?

    Monday, May 21, 2012 9:31 AM

  • Please refer to below linkwhere it is explained that null out put is expected 

    http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/39235;pt=39091

    "

    Using rand() functions in case expressions

    Expressions 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 standard 

    I 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
    end  

    also 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
    Monday, May 21, 2012 9:32 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


    Monday, May 21, 2012 1:22 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



    • Marked as answer by nicofer Tuesday, May 22, 2012 1:45 AM
    • Edited by nicofer Tuesday, May 22, 2012 1:45 AM
    Tuesday, May 22, 2012 1:44 AM
  • 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


     
    Tuesday, May 22, 2012 7:01 AM