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 ~~bad~~some programs.

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

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

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

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

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 ) );

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?

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

Comments are closed.