MySQL Bugs: #84822: Definition of "DETERMINISTIC" routine is not clear

Bug #84822	Definition of "DETERMINISTIC" routine is not clear
Submitted:	5 Feb 2017 16:29	Modified:	23 May 2018 1:15
Reporter:	Grigory Rubtsov	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
MySQL documentation on "CREATE PROCEDURE" states:
> A routine is considered “deterministic” if it always produces the same result for the same input parameters, and “not deterministic” otherwise. 

From this definition it's not clear whether DETERMINISTIC function may rely on the data from database tables. The community assumes that the usage of data is possible and the definition is wider "produces the same result for the same input parameters and the same state of the database".

How to repeat:
See discussion at 
http://stackoverflow.com/questions/7946553/deterministic-function-in-mysql

Suggested fix:
Clarify definition in one of the two ways (which one is true):
1. A routine is considered “deterministic” if it always produces the same result for the same input parameters AND THE SAME STATE OF THE DATABASE, and “not deterministic” otherwise. 

2. A routine is considered “deterministic” if it always produces the same result for the same input parameters, and “not deterministic” otherwise. The result of the DETERMINISTIC routine must be independent on the table data.

Thank you for the bug report.

Posted by developer:
 
The meaning is already given in the CREATE FUNCTION section at:
https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html

"
A routine is considered “deterministic” if it always produces the same result for the same input parameters, and “not deterministic” otherwise. If neither DETERMINISTIC nor NOT DETERMINISTIC is given in the routine definition, the default is NOT DETERMINISTIC. To declare that a function is deterministic, you must specify DETERMINISTIC explicitly.

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

So if you write a function that is not going to produce deterministic results, you should not label it DETERMINISTIC. Adding DETERMINISTIC does not *cause* a function to become deterministic. It's a statement by the function creator whether or not it is deterministic.