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

 2 2 [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: 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. 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. 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. asked 22 Oct '10, 20:01 Volker Barth 31.7k●322●465●680 accept rate: 32%

 toggle preview community wiki:

By Email:

Markdown Basics

• *italic* or _italic_
• **bold** or __bold__
• image?![alt text](/path/img.jpg "title")
• numbered list: 1. Foo 2. Bar
• to add a line break simply add two spaces to where you would like the new line to be.
• basic HTML tags are also supported

Question tags:

×36
×28
×5
×4

question asked: 22 Oct '10, 20:01

question was seen: 1,721 times

last updated: 10 Dec '10, 21:55