Deterministic function in mysql

From the MySQL 5.0 Reference:

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. Prior to MySQL 5.0.44, the DETERMINISTIC characteristic is accepted, but not used by the optimizer.

So there you have it, you can tag a stored routine as DETERMINISTIC even if it is not, but it might lead to unexpected results or performance problems.

DETERMINISTIC results does not refer to different results sets being returned at different times (depending on what data has been added in the mean time). Moreover it is a reference to the result sets on different machines using the same data. If for example, you have 2 machines which run a function including uuid() or referencing server variables then these should be considered NOT DETERMINISTIC. This is useful for example in replication because the function calls are stored in the binary log (master) and then also executed by the slave. For details and examples see http://dev.mysql.com/doc/refman/5.0/en/stored-programs-logging.html

The use of DETERMINISTIC is thus (99% of the time) correct, not to be considered misuse.

I think that your routine is deterministic. The documentation is not very clear and this has led to many people being very confused about this issue, which is actually more about replication than anything else.

Consider a situation where you have replication set up between two databases. The master database keeps a log of all the stored routines that were executed including their input parameters, and ships this log to the the slave. The slave executes the same stored routines in the same order with the same input parameters. Will the slave database now contain identical data to the master database? If the stored routines create GUIDs and store these in the database then no, the master and slave databases will be different and replication will be broken.

The main purpose of the DETERMINISTIC flag is to tell MySQL whether including calls to this stored routine in the replication log will result in differences between the master database and the replicated slaves, and is therefore unsafe.

When deciding if the DETERMINISTIC flag is appropriate for a stored routine think of it like this: If I start with two identical databases and I execute my routine on both databases with the same input parameters will my databases still be identical? If they are then my routine is deterministic.

If you declare your routine is deterministic when it is not, then replicas of your main database might not be identical to the original because MySQL will only add the procedure call to the replication log, and executing the procedure on the slave does not produce identical results.

If your routine is non-deterministic then MySQL must include the affected rows in the replication log instead. If you declare your routine as non-deterministic when it is not this will not break anything, but the replication log will contain all of the affected rows when just the procedure call would have been enough and this could impact performance.