0

The documentation for MySQL isn't explicit on this point, but I'm wondering whether the scope for a function with respect to DETERMINISTIC is database global, or by some other scope?

Consider this definition:

CREATE
FUNCTION test() 
RETURNS VARCHAR(255) DETERMINISTIC
RETURN USER();

This function is determinstic for all calls in the same connection, as user() will always return the same value. However, does DETERMINISTIC mean that "computed" values are shared across connections?

Defensively I would treat this function as NOT DETERMINISTIC but the question came up and I'm curious about the answer.

  • A function is considered deterministic when called with same set of input values returns the same result. – McNets Mar 5 '18 at 14:40
1

Formally, since the function USER() is not deterministic according to that term's definition:

A routine is considered “deterministic” if it always produces the same result for the same input parameters, and “not deterministic” otherwise.

your function that simply returns its result is also not deterministic.

However, the manual further states that

Assessment of the nature of a routine is based on the “honesty” of the creator

What it means is that you define the scope where it is deterministic (or not). Considering that this property is used for query optimization, and that, as you say, within a single session USER() will always return the same result, you can treat it, and the functions that call it, deterministic within the scope of a single query.

This changes, however, if the query cache is enabled, as the cache is shared among sessions and it is possible that sessions other than yours might get results from the cache that would be invalid for them.

  • Thank you. The key word here of course is "always". The function I provided doesn't have that property. I will instead refactor our function to take the result of user as string-input instead; then it is guaranteed to be deterministic and callers are expected to provide the right input. – Matthias Mar 5 '18 at 16:32
  • I suspect that SELECT USER() would not go into the Query Cache since it is not globally deterministic. – Rick James Mar 8 '18 at 19:49
  • @RickJames may be it won't, but SELECT test() might, especially if you declare it DETERMINISTIC since it is for you. I just wanted to warn against possible side effects when dealing with such edge cases. – mustaccio Mar 8 '18 at 19:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.