How does "deterministic" affect how MySQL executes a defined function?

Question

MySQL has to know the deterministic of a defined function, official document says:

Several characteristics provide information about the nature of data use by the routine. In MySQL, these characteristics are advisory only.

Does that mean deterministic of a function does not affect how the function is executed?

Answer 1

TL;DR The function created following MySQL8.0 Create Procedure is PROBABLY following the same rules as MySQL native functions when it comes to scheduling and determinism.

---

I've read Mysql8.0's Function Optimization more carefully this time and I think I have the answer now.

The DETERMINISTIC or NOT DETERMINISTIC affects whether the function you created will be executed once or multiple times when used in a WHERE statement.

I've tested this with a function that counts TABLE t1 rows (with more than one row in there) and UPDATES a t2 row (Literally a MODIFIES SQL counter).

I then wrote a query

SELECT my_func()
FROM t1;

SELECT *
FROM t2;
-- Counter is incremented with row count of t1

I then wrote another query

SELECT *
FROM t1
WHERE my_func() >= 0; -- It happens to return the row count of t1

SELECT *
FROM t2;
-- Counter is incremented by 1 despite multiple rows

I finally set NOT DETERMINISTIC on my_func and in the second example it was incremented by the number of rows.

Answer 2

siride's answer is the most discussion I can find on the web anywhere on this. But what happens if you combine DETERMINISTIC with MODIFIES SQL DATA? And the query might be deterministic on any given day but not between days (e.g. it is based on a lookup table that rarely changes).

My working opinion on this is to avoid using DETERMINISTIC unless either (a) the function is in fact mathematically deterministic (e.g. return input * 2) or

(b) we have a performance issue and we need to try DETERMINISTIC to speed things up.

Otherwise, why take the risk that the optimizer provides results that are wrong based on our misunderstanding of what it does?

Answer 3

If I had to make a guess, I'd say that MySQL treats DETERMINISTIC functions as having true referential integrity. Thus, if the function is called with the same argument, it will always give the same result. The upshot of this is that if a function would be called with the same argument multiple times, MySQL might choose to reorder execution so that it's only called once, or maybe it caches the result. I can't find much on Google about exactly what MySQL does when optimizing deterministic functions, but I doubt it would be anything other than what I specified.

If you mark a function as DETERMINISTIC, and it is not, then MySQL might not call it when it should, resulting in potentially incorrect results. For example, if calling it with the value '3' might produce a result of '7' one time and then '8' later on, then you might get '7' both times if MySQL thinks it's DETERMINISTIC.

In any case, if your function is truly deterministic (that is, it has no side-effects and does not read any data except what is passed to it), then marking it DETERMINISTIC won't hurt anything and might help if the optimizer is smart enough to reorder execution or reduce the number of calls. If your function reads or modifies data, or has some other side effect, then you will be definitely safe by not marking it DETERMINISTIC, and you might create problems by marking it DETERMINISTIC, as MySQL might choose an execution plan that would lead to incorrect results coming from the function. In other words, don't lie to MySQL and you'll be fine.

Answer 4

That sentence to me would imply that they may be used by mysql, but mysql also may not.

Still a good idea to use 'deterministic', but you should actually measure to see if the query execution strategy matches your expectations.