[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:
But taken the simple query from the cited question, the third statement seems wrong for current SA versions:
IMHO, this should return a row with two identical values, with different results for each call.
But with SA 188.8.131.5266, it returns different columns, such as
Only when the second expression is simplified to a repetition of the same expression, then two identical values get returned:
However, in ASA 184.108.40.20674, the first query does return identical rows, too.
Are my assumptions of not deterministic calls correct? - The current behaviour seems to be undeterministic w.r.t. aliases, and that seems wrong IMHO.
asked 22 Oct '10, 20:01
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.
answered 10 Dec '10, 21:55