Mysql deterministic function, scope?

Question

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.

• McNets: A function is considered deterministic when called with same set of input values returns the same result.

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

• Matthias: 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.
• Rick James: I suspect that SELECT USER() would not go into the Query Cache since it is not globally deterministic.
• mustaccio: @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.