This is a short update to the presumed bug I wrote about recently in this post.

After a few back and forth with Oracle support it turns out this is a well-known (to them) effect of using dbms_random in SQL.

The base for this seems to be a bug report in 10g that was declared “not a bug”. You can read the bug report on MOS. It is also covered in this doc that references the same bug.

It turns out that a function can be called many times during the evaluation. This can of course cause all kinds of havoc to the expected result of the SQL when the function produces a non deterministic result.

You can prove it to yourself by writing a wrapper for dbms_random.value.

As a user I still feel it seems it should not produce results like this, while I understand and appreciate that on a purely technical level it is an effect of how the query is executed. It is however deemed to be “working as designed”.

The linked document shows a test case to prove how it works. It is surprising that this is the result, but it is good to be aware of this and be careful with dbms_random when using it in SQL. The danger is that the effect may change with small changes in optimization or with new versions of the database.

You are probably better off to avoid it in SQL when there are alternatives. When there is not, make sure to put such SQL on the list of code to test with every patch and new version.