How does random=random evaluates?

I had fun answering a question about random on the technical forums.

What is in your opinion the boolean value of DBMS_RANDOM.VALUE=DBMS_RANDOM.VALUE?

Or, how many rows would
select * from dual where dbms_random.value=dbms_random.value;
return?

It is wrong to assume the function will be evaluated twice.

The short answer would be : do not rely on random plsql functions in SQL…

here is a test case in 11.2.0.2 and 10.2.0.3


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"())

In 10g, the function is executed twice per row, and the chance to have two different values is more than 99.9999…%.


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)

Here the optimized execute the function only once per row, and since the result is never null, it always evaluates to true.

Is this a bug or a feature?

In my opinion it is a confusing tuning enhancement that may break badsome programs.

In this thread, I mentioned that prior dbms_random.value is not null is an unsafe construct.

Published by

Laurent Schneider

Oracle Certified Master

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

  1. “…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. 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. 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?

  4. 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

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>