The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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

 1 1 [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 29.3k●287●438●644 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:

×33
×26
×4
×4

question asked: 22 Oct '10, 20:01

question was seen: 1,389 times

last updated: 10 Dec '10, 21:55