Using a random value as a join condition

Question

I generate some test data and use dbms_random . I came across some weird behavior when using dbms_random in a JOIN condition, which I cannot explain:

 ------------------------# test-data (ids 1 .. 3) With x As ( Select Rownum id From dual Connect By Rownum <= 3 ) ------------------------# end of test-data Select x.id, x2.id id2 From x Join x x2 On ( x2.id = Floor(dbms_random.value(1, 4)) ) 

Floor(dbms_random.value(1, 4) ) returns a random number from (1,2,3), so I expected all lines x be connected to random string x2 or maybe always the same random string x2 in case the random number is evaluated only once.

When I try several times, I get the following results:

 (1) ID ID2 (2) ID ID2 (3) ---- ---- ---- ---- no rows selected. 1 2 1 3 1 3 2 3 2 2 3 3 2 3 3 2 3 3 

What am I missing?

EDIT

 SELECT ROWNUM, FLOOR(dbms_random.VALUE (1, 4)) FROM dual CONNECT BY ROWNUM <= 3 

will get the result in this case, but why does the original request behave like this?

Answer 1

To create three rows with one predicted value and one random value, try the following:

 SQL> with x as ( 2 select rownum id from dual 3 connect by rownum <= 3 4 ) 5 , y as ( 6 select floor(dbms_random.value(1, 4)) floor_val 7 from dual 8 ) 9 select x.id, 10 y.floor_val 11 from x 12 cross join y 13 / ID FLOOR_VAL ---------- ---------- 1 2 2 3 3 2 SQL 

change

Why did your original query return an inconsistent rowset?

Well, without a random bit in the ON clause, your query was basically CROSS JOIN X vs X - it would return nine lines (at least that would be if the syntax allowed this). Each of these nine lines makes a call to DBMS_RANDOM.VALUE() . Only when a random value matches the current value of X2.ID is this a string included in the result set. Therefore, the query can return 0-9 rows, randomly.

Your decision is obviously simpler - I have not refactored enough :)