function - the meaning of the deterministic flag

Question

What is the difference between deterministic and non-deterministic function?
I was hoping that deterministic would make the DBMS find the function clean and cache its result?

DETERMINISTIC non-deterministic components. The values of the values, the result is the same. It can be a DETERMINISTIC to add a DETERMINISTIC to the declaration section. . This is a result of a previously calculated result.
Source

 create function det() returns INTEGER DETERMINISTIC NO SQL return @param; create function ndet() returns INTEGER NOT DETERMINISTIC NO SQL return @param; select @param:=5, det() d1, ndet() n1 , @param:=3, det() d2, ndet() n2 , @param:=1, det() d3, ndet() n3 , @param:=7, det() d4, ndet() n4;

Result:

5, 5, 3, 3, 3, 1, 1, 1, 7, 7, 7

Those. I do not see any difference.

How to understand what the difference is and in what situations it manifests itself?

- this mainly affects replication, DETERMINISTIC can be written to the binary log directly as an expression, and NON DETERMINISTIC can be written only as row-based — otherwise, other data may NON DETERMINISTIC on the replica.
@And, in my opinion, the deterministic function is a synonym for pure function .
@vp_arth contradict yourself: D You just answered me that they are not synonymous.
@DarkByte, just "synonym" in this comment is not synonymous with "synonym" in that.
I think that you are carping and quite transparently understand what I had in mind in each of these cases.
@vp_arth "синоним" в этом комментарии не является синонимом к "синониму" в том STA ???

Accepted Answer · 2018-06-28T11:15:29

If you define DETERMINISTIC , it means that for the same parameters, the function always returns the same value. Accordingly, MySQL, in theory, can cache a value so as not to recalculate it for the same parameters.

But the support for DETERMINISTIC in MySQL is not fully implemented, the function will always be executed.

An example on MySQL 5.7.22:

 create function rr() returns INTEGER DETERMINISTIC return RAND()*1000; select rr(), rr(); +------+------+ | rr() | rr() | +------+------+ | 300 | 451 | +------+------+

WHERE is executed 1 time:

 CREATE FUNCTION r1() returns INTEGER DETERMINISTIC return RAND()*3; SELECT * FROM ( SELECT 1 AS ID UNION ALL SELECT 2 AS ID UNION ALL SELECT 3 AS ID UNION ALL SELECT 4 AS ID UNION ALL SELECT 5 AS ID ) AS t1 WHERE r1() < ID;

Displays, for example, 1 | 2 | 3 1 | 2 | 3 1 | 2 | 3 , that is, without "attacks."

Proof did not find. Heard on internal training regarding version 5.5. In the news "we fixed it" did not run. - Total Pusher

MaxU MaxU 52.7k 6 gold signs 18 silver marks 51 bronze marks · Answer 2 · 2018-06-28T11:21:37

In my opinion, in this form both functions are obviously non-deterministic, since depend on the @param external variable, which is not a function parameter ...

If you issue a function like this:

 create function det(param INTEGER) returns INTEGER DETERMINISTIC NO SQL return param;

then (in my opinion) it can be considered deterministic, since it is guaranteed to return the same result when called with the same parameters ...

Does mysql check independently that the flag was passed incorrectly?
I saw in the documentation that mysql trusts the user in this matter and therefore the results may differ from those expected (probably meant "unexpected caching"?)
Judging by the documentation, MySQL cannot verify whether the function specified as DETERMINISTIC is actually such ... I didn’t quite understand what you were trying to do ...
Let him consider the function deterministic and ignore changes to a variable (after all, can it not check?).
How to force mysql to count the function clean and return the cached result of the first call?
@MaxU want to say that FP-shnaya purity of functions and determinism are synonymous?

function - the meaning of the deterministic flag

2 answers 2

More articles: