Question 290967 in Mysql Question posted in
The official MySQL documentation can be found here.

The pros and cons to use proper and improper characteristics for a MySQL function?

I created test function with the proper characteristic DETERMINISTIC as shown below because it always produces the same result 3 according to the doc:

CREATE FUNCTION test() RETURNS INT 
DETERMINISTIC -- Here
RETURN 3;

And, I also created test function with the improper characteristics NOT DETERMINISTIC, NO SQL, READS SQL DATA and MODIFIES SQL DATA as shown below:

CREATE FUNCTION test() RETURNS INT 
NOT DETERMINISTIC -- Here
NO SQL -- Here
READS SQL DATA -- Here 
MODIFIES SQL DATA -- Here
RETURN 3;

Even if:

  • NO SQL indicates that the routine contains no SQL statements.

  • READS SQL DATA indicates that the routine contains statements that read data (for example, SELECT), but not statements that write data.

  • MODIFIES SQL DATA indicates that the routine contains statements that may write data (for example, INSERT or DELETE).

So, what is the pros(advantages) and cons(disadvantages) to use proper and improper characteristics for a function in MySQL?

Tags:

2

Answers


  1. 0

    The only implication that I know of is that functions marked DETERMINISTIC are allowed to be used freely in statements while binlog_format=STATEMENT is in effect. Read https://dev.mysql.com/doc/refman/8.0/en/stored-programs-logging.html for details.

    The other annotations are advisory only. They have no effect as far as MySQL is concerned, so you can use them however you want.

    Login or Signup to reply.
  2. 0

    In reference to declaring DETERMINISTIC or NOT DETERMINISTIC, the docs state: "misdeclaring a routine might affect results or affect performance"

    Assessment of the nature of a routine is based on the “honesty” of the creator: MySQL does not check that a routine declared DETERMINISTIC is free of statements that produce nondeterministic results. However, misdeclaring a routine might affect results or affect performance. Declaring a nondeterministic routine as DETERMINISTIC might lead to unexpected results by causing the optimizer to make incorrect execution plan choices. Declaring a deterministic routine as NONDETERMINISTIC might diminish performance by causing available optimizations not to be used.

    If binary logging is enabled, the DETERMINISTIC characteristic affects which routine definitions MySQL accepts. See Section 25.7, “Stored Program Binary Logging”.

    The other four characteristics:

    Several characteristics provide information about the nature of data use by the routine. In MySQL, these characteristics are advisory only. The server does not use them to constrain what kinds of statements a routine is permitted to execute.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search