SQL> select version from v$instance;
VERSION
-----------------
10.2.0.3.0

SQL> select * from dual where dbms_random.value=dbms_random.value;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 1224005312

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     2 |     2   (0)| 00:00:01 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DBMS_RANDOM"."VALUE"()="DBMS_RANDOM"."VALUE"())

SQL> select version from v$instance;
VERSION
-----------------
11.2.0.2.0

SQL> select * from dual where dbms_random.value=dbms_random.value
D
-
X

Execution Plan
----------------------------------------------------------
Plan hash value: 1224005312

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     2 |     2   (0)| 00:00:01 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DBMS_RANDOM"."VALUE"() IS NOT NULL)

5 thoughts on “How does random=random evaluates?”

1. Sokrates March 14, 2011 at 16:49

   “…Is this a bug or a feature?….”

   a bug in my eyes.

   
   select dbms_random.value from dual where dbms_random.value = dbms_random.value connect by level=1;
   

   would be a workaround
2. Laurent Schneider Post authorMarch 14, 2011 at 17:09

   Amusing workaround !

   I also reported once to Oracle that order by does not sort with random date desc

   select sysdate+dbms_random.value*10 x from emp order by x desc;

   they fixed it in 11.2.0.2
3. Sokrates March 14, 2011 at 17:14

   Laurent Schneider :
   Amusing workaround !

   here is another one:
   

   
   select * from dual where not ( lnnvl ( dbms_random.value = dbms_random.value ) );
   
4. joel garry March 15, 2011 at 23:39

   From the docs, both versions:

   “…It will automatically initialize with the date, userid, and process id if no explicit initialization is performed…If this package is seeded twice with the same seed, then accessed in the same way, it will produce the same results in both cases.”

   Which pushes the question to, are functions accessed the same way on both sides of a filter equality?

   From that viewpoint, 10.2 behavior is buggy?
5. Laurent Schneider Post authorMarch 17, 2011 at 12:10

   Nope, 10g behavior is not buggy.

   If you initialise the seed with 1234, you will get the random number 0.428 , 0.004, 0.185, 0.356, 0.450.

   Each execution will get a new number, but the result will be the same.

   The bug in 11g is that the function is executed only once

   set serverout on 
   begin
     dbms_output.put_line('seeding 1234...');
     dbms_random.seed(1234);
     dbms_output.put_line(to_char(dbms_random.value,'0.000'));
     dbms_output.put_line(to_char(dbms_random.value,'0.000'));
     dbms_output.put_line(to_char(dbms_random.value,'0.000'));
     dbms_output.put_line(to_char(dbms_random.value,'0.000'));
     dbms_output.put_line(to_char(dbms_random.value,'0.000'));
     dbms_output.put_line('seeding 1234...');
     dbms_random.seed(1234);
     dbms_output.put_line(to_char(dbms_random.value,'0.000'));
     dbms_output.put_line(to_char(dbms_random.value,'0.000'));
     dbms_output.put_line(to_char(dbms_random.value,'0.000'));
     dbms_output.put_line(to_char(dbms_random.value,'0.000'));
     dbms_output.put_line(to_char(dbms_random.value,'0.000'));
   end;
   /
   
   seeding 1234...
    0.428
    0.004
    0.185
    0.356
    0.450
   seeding 1234...
    0.428
    0.004
    0.185
    0.356
    0.450
   PL/SQL procedure successfully completed.
   

   In SQL :
   

   set numf 0.000;
   exec dbms_random.seed(1234);
   select 
     dbms_random.value,
     dbms_random.value,
     dbms_random.value,
     dbms_random.value,
     dbms_random.value
   from dual;
   
    VALUE VALUE_1 VALUE_2 VALUE_3 VALUE_4
   ------ ------- ------- ------- -------
    0.428   0.004   0.185   0.356   0.450