[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.