Is the treatment of "deterministic-unknown" functions similar in ASA 8 and SA 12?

[VolkerBarth]

A question based on do-functions-really-default-to-deterministic:

When creating functions without the [NOT] DETERMINISTIC clause, the server is free to treat them as deterministic.

This is documented both for ASA 8 and for newer versions up to SA 12.

I'm about to add that particular clause for all functions (and a lot of them do have side-effects), but beforehand I'd like to know if the server's treatment of functions has (significantly) changed between ASA 8 and SA 12 w.r.t. deterministic behaviour - i.e. will SA 12 make more use of function result caching than ASA 8?

Note that I'm generally using those functions with side-effects in simple queries such as

select fnMyFunction('blah');

so there's no need to call the same function multiple times within the same query. (For queries that use functions inside select lists, where clauses and the like, I would expect there have been optimizations to cache results.)

[VolkerBarth]

Sidenote: I have always had the impression the NON DETERMINISTIC clause had been introduced in V9...but it was in 8.0.1, and so I could have used in all those years in my V8 setup. Some kind of deja vû, methinks:)

[Former Member · Accepted solution]

The documentation is correct that UDFs whose determinism is unspecified are treated as if DETERMINISTIC was actually declared.

It is difficult to answer whether or not SQL Anywhere 11/12 makes "more" use of cached results than was the case with Version 8. Even with Version 8, one could offer no guarantees to the number of times a function was evaluated because changes in context (ie access plan changes from invocation to invocation) could change the pattern of evaluation, and the sequence of parameter values used to invoke the function. All of that is equally true with Version 11/12, and even more so because predicate optimization in Version 11/12 is completely different than what existed in Version 8.

[VolkerBarth · Accepted solution]

So I conclude that if I want to have guaranteed not-deterministic behaviour, I should specify the appropriate clause. I'm gonna do that - for both kinds of functions:)

[Former Member · Accepted solution]

Excellent idea, if for no other reason than self-documentation.