cancel
Showing results for 
Search instead for 
Did you mean: 

How "not deterministic" is not deterministic - a question of rand()?

VolkerBarth
Contributor
3,645

[This is a follow-up on this question and as such another follow-up form the question "Find random free alphanumeric value" in the general newsgroup]:

The cited question led me tho this one - in order to understand the non-determinism of function calls in one single query better.

My current hypothesis is:

  1. Basically, each occurrence of a not deterministic function is guaranteed to be evaluated on its own, i.e. the results of calling a function with identical arguments will not be cached and re-used.

  2. When an expression that is based on a function call is evaluated as part of a WHERE or ORDER BY clause (or similar clauses) that evaluation must not trigger another function call but must re-use the function result - otherwise filtering or ordering based on not-deterministic functions would simply be unreliable.

  3. Using an alias for an expression that is based on a function call should also not trigger another function call but should re-use the function result, as an alias is just another name for the same expression.

But taken the simple query from the cited question, the third statement seems wrong for current SA versions:

select rndInd1, rndInd1 + 0
from (select
   cast(truncnum(rand() * 36, 0) as int) as rndInd1) S

IMHO, this should return a row with two identical values, with different results for each call.

But with SA 12.0.0.2566, it returns different columns, such as

16, 26 or 34, 17

Only when the second expression is simplified to a repetition of the same expression, then two identical values get returned:

select rndInd1, rndInd1
from (select
   cast(truncnum(rand() * 36, 0) as int) as rndInd1) S

However, in ASA 8.0.3.5574, the first query does return identical rows, too.

Question:

Are my assumptions of not deterministic calls correct? - The current behaviour seems to be undeterministic w.r.t. aliases, and that seems wrong IMHO.

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member

Very interesting. While counter-intuitive, I do not believe that hypothesis 2 and 3 are true. 3 clearly is not, from your example. I do not have an example 2 failing.

I don't believe it is guaranteed behavior that a value is cached if the function generating it is deterministic. I actually read up in the help on deterministic, and I don't think there are any guarantees on how a deterministic function is evaluated when executed multiple times. The guarantee works in the opposite direction. A deterministic function guarantees to provide the same result over and over again from the same input.

In the example you have provided (nice one!) the optimizer clearly chooses to recalculate a deterministic value instead of using the cached value. The result is unexpected (and honestly, I would have never predicted it) but I think it is valid.

I do think that there would be a lot of merit to having non deterministic functions being evaluated exactly as you describe in your hypothesis, I just don't think that is the documented behavior right now.

VolkerBarth
Contributor
0 Kudos

Thanks for your comments! I agree with your point relating to deterministic funtions (i.e. that they may be cached and are not guaranteed to do so). Though the statement "A deterministic function guarantees to provide the same result over and over again from the same input." is not really true. The function just claims to do so. It's the coder's responsibility whether this is true or not.

VolkerBarth
Contributor
0 Kudos

That being said, I feel that my question has not fully come through. I'm not asking about deterministic functions, I'm asking about not deterministic functions and aliases. rand() is simply a very typical not deterministic function. - And I still feel my third hypothesis should be true, and the current implementation might be wrong. But that is a question for the SQL Anywhere developers, methinks...

Former Member
0 Kudos

I guess the thing to remember is that an Alias is not a Variable, even though they look an awful lot alike. The value of an alias may be cached, or it may be recalculated. There is no guaranteed behavior described for non-deterministic functions other than your assumption #1 (that I know of).

I do feel both your 2nd and 3rd hypo's should be true, but that's from a functional POV. I can also see the possible problems with implementing it as such, as these assumptions can get pretty hairy and I am not 100% sure your three cover the spectrum.

As it is now, SQL Anywhere behaves as documented.