Attention-grabbing bug with nondeterministic capabilities

I used to be engaged on a bit simulation utilizing random numbers and located I used to be getting odd outcomes from my question. After spending some time dissecting it looking for my error I discovered the issue was within the database itself. I used to be capable of replicate the issue on Stay SQL utilizing a easy take a look at case with each 19c and 23ai.

The instance code is meant to roll 8 cube two occasions every. If the primary die is under 4 then I’ll use the results of the second die. In any other case, I’ll preserve the primary roll. The issue is that the CASE assertion at all times returns the second die, whatever the first die consequence. The 2 hyperlinks above will allow you to run the instance your self, however I’ll embody the code and output right here.

First, I set a seed. The seed worth itself isn’t vital; however selecting one permits everybody to get the identical outcomes from the pseudo-random quantity generator in DBMS_RANDOM.

Then, my question generates eight rows of two random values every. The rows are numbered so as by the results of the primary die. The CASE assertion ought to resolve the primary vs second die outcomes. Nonetheless, you will note on line 7, the roll was a 5, which ought to be stored; however the CASE returned the reroll worth as an alternative. Then, on line 8, the primary roll was a 6, and once more, the CASE returned the reroll as an alternative of utilizing that.

SQL> BEGIN
  2      DBMS_RANDOM.seed(0);
  3  END;
  4  /

PL/SQL process efficiently accomplished.

SQL>   SELECT ROW_NUMBER() OVER (ORDER BY first_roll) rn,
  2           first_roll,
  3           reroll,
  4           CASE WHEN first_roll 



I posted this to an Oracle ACE chat to see if anybody else had ever seen something like this.  The suggestions I obtained was that it appeared just like the SQL transformation step was "over-zealous" in making an attempt to simplify the expression.
Trying on the inside question, first_roll and reroll are an identical.
They're each the expression FLOOR(DBMS_RANDOM.VALUE * 6) + 1.
So, if we name that expression X, the CASE may then be interpreted as 
case when X 
For the reason that case returns X it doesn't matter what the situation is, then it is sensible to simplify to simply return X.  On this case the X is the reroll.   
With out understanding precise the internals of how that transfomation works, I'll assume the reroll worth is at all times used as a result of it's first return worth of the case assertion.  To check that principle, I'll invert the logic.  As a substitute of checking to make use of the reroll if the first_roll is lower than 4, I'll verify to maintain the first_roll whether it is higher than or equal to 4.  Functionally it ought to be the identical if there is no such thing as a bug; however on this case I am making an attempt to check the bug itself.



SQL>   SELECT ROW_NUMBER() OVER (ORDER BY first_roll) rn,
  2           first_roll,
  3           reroll,
  4           CASE WHEN first_roll >= 4 THEN first_roll else reroll  END closing
  5      FROM (
  6                   SELECT FLOOR(DBMS_RANDOM.VALUE * 6) + 1 first_roll, FLOOR(DBMS_RANDOM.VALUE * 6) + 1 reroll
  7                     FROM DUAL
  8               CONNECT BY LEVEL 



It appears like my assumption was right.  With the primary roll being the returned worth of the primary WHEN situation - that finally ends up getting used, no matter whether or not the WHEN situation is true or not.



A bug report is being filed internally with Oracle to look into this; however till a repair is made obtainable I rewrote the question like this:



SQL>   SELECT rn, first_roll, reroll, NVL(reroll, first_roll) closing
  2      FROM (SELECT ROW_NUMBER() OVER (ORDER BY first_roll) rn,
  3                   first_roll,
  4                   CASE WHEN first_roll 



Now, as an alternative of rolling each cube each time.  I solely reroll if the primary roll warrants it.   Thus I've NULL for a reroll if the first_roll is 4 or increased and a reroll worth if the first_roll is 3 or decrease.
Then within the outer-most question, I take advantage of NVL to resolve the distinction.  Now, there may be by no means a degree within the question the place transformation logic might be taking a look at two values which might be outlined by the identical expression.



I had some preliminary doubts of whether or not this was a bug or just a misunderstanding.  If my expression is deterministic, then it'd make sense for the transformation to exchange my case with a single worth; as a result of a deterministic expression will at all times return the identical factor, it will not matter which worth of two an identical expressions is used, as a result of they're assured to return the identical worth.



However, with non-deterministic capabilities (which dbms_random.worth definitely qualifies as) that transformation is not legitimate.  The syntax of every expression is identical; however as a result of first_roll and reroll are nondeterministic they have to be handled as distinct entities.  Thus the transformation's simplification is misguided.



At first look, failing a easy less-than verify might appear to be an egregious mistake with the parsing/remodeling steps.  Upon evaluation although, I believe it is a uncommon state of affairs the place a question can be constructed as I did.  That's, querying a case assertion over a sub-query of an identical, non-deterministic expressions.  Whereas it made sense for the needs of my simulation, most enterprise instances will probably be completely different.



I hope this helps if another person runs into an identical transformation error.  It was an attention-grabbing train to dig via and I am glad I've a community of individuals to bounce concepts off.  Thanks for studying.  Questions and feedback, as at all times, are welcome.