[jira] [Commented] (TRAFODION-3042) RAND() function is not always random

["Hans Zeller (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/TRAFODION-3042?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16450260#comment-16450260 ] 

Hans Zeller commented on TRAFODION-3042:
----------------------------------------

Based on the implementation, in ExFunctionRandomNum::initSeed() and ExFunctionRandomNum::genRand(), it seems that when we call RAND() within a short time interval, the results will differ by multiples of 18607. In my example above, the second RAND() value was computed about 15 microseconds after the first, and the third RAND() value was computed about 6 microseconds after the second.

Thanks to Anoop for pointing me to the code and this comment in the code:

{noformat}
      // Algorithm is taken from "Random Number Generators: Good Ones
      // Are Hard To Find", by Stephen K. Park and Keith W. Miller, 
      // Communications of the ACM, Volume 31, Number 10, Oct 1988.
{noformat}

I think the solution for this is to randomize the seed in ExFunctionRandomNum::initSeed() before we use it in ExFunctionRandomNum::genRand(). A simple way to do this might be to go through one step of the random generator before we actually emit a row.

> RAND() function is not always random
> ------------------------------------
>
>                 Key: TRAFODION-3042
>                 URL: https://issues.apache.org/jira/browse/TRAFODION-3042
>             Project: Apache Trafodion
>          Issue Type: Bug
>          Components: sql-exe
>    Affects Versions: 2.2.0
>            Reporter: Hans Zeller
>            Priority: Major
>
> When trying the RAND() function, I found that it does not always behave as I would expect. I tried this example:
> {noformat}
> >>select rand(), rand(), rand() from dual;
> (EXPR)      (EXPR)      (EXPR)    
> ----------  ----------  ----------
>   78242471    78511383    78612225
> --- 1 row(s) selected.
> >>select rand(), rand(), rand() from dual;
> (EXPR)      (EXPR)      (EXPR)    
> ----------  ----------  ----------
> 1589208276  1589460381  1589561223
> --- 1 row(s) selected.
> >>select rand(), rand(), rand() from dual;
> (EXPR)      (EXPR)      (EXPR)    
> ----------  ----------  ----------
> 134371215   134724162   134858618
> --- 1 row(s) selected.
> >>
> {noformat}
> So, it seems that if we call rand() within a very short time, the value will be non-random.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)