Deterministic vs Non-Deterministic Functions

When creating stored functions in MySQL, you are required to declare their behavioral characteristics: DETERMINISTIC or NOT DETERMINISTIC.

Declaring this correctly impacts both Query Optimization and Replication Safety.

What is a Deterministic Function?

A function is Deterministic if it always produces the exact same output given the exact same input parameters, with zero side effects.

• Mathematical Example: $f(x, y) = x + y$. For inputs $(5, 3)$, the result is always $8$.
• SQL Example: Converting Fahrenheit to Celsius, calculating string hash values, formatting telephone numbers.

Optimization Advantage:

The query optimizer knows that for a constant input, the result never changes. It can evaluate the function once and cache the result for the duration of the query!

What is a Non-Deterministic Function?

A function is Non-Deterministic if its output can change between consecutive calls even when supplied with the identical input parameters.

A function is inherently non-deterministic if it references:

• System time or dates (NOW(), CURRENT_DATE(), UNIX_TIMESTAMP())
• Random number generators (RAND())
• Dynamic database tables whose rows change over time (SELECT balance FROM accounts)
• System variables or connection IDs (CONNECTION_ID(), USER())

Because CURRENT_DATE() changes as time elapses, this function is strictly NOT DETERMINISTIC.

The Danger with Binary Logging & Replication

When MySQL replication is enabled (statement-based logging):

• If a function marked DETERMINISTIC actually uses NOW() or reads unstable data, the master and replica databases will execute the function at slightly different times, producing divergent data between master and slave nodes!
• Declaring characteristics honestly maintains cluster consistency.

Additional Characteristic Clauses

Multiple Choice Questions

1. What defines a DETERMINISTIC function in MySQL?

A. It executes in less than 1 second B. Given identical input parameters, it is guaranteed to always produce the exact same output C. It only runs once per server startup D. It modifies table rows Answer: B Explanation: A deterministic function consistently returns the identical output whenever invoked with the same input arguments.

2. Which of the following functions is inherently NOT DETERMINISTIC?

A. A function calculating: price 1.18 B. A function calculating: (celsius 9/5) + 32 C. A function returning days elapsed between an input date and CURRENT_DATE() D. A function converting a string to uppercase Answer: C Explanation: Referencing dynamic environmental variables like CURRENT_DATE() or NOW() causes outputs to change over time, making the routine non-deterministic.

3. What clause tells the optimizer that a function contains pure math and does not query database tables?

A. EMPTY SQL B. NO SQL C. ZERO_DATA D. BYPASS TABLES Answer: B Explanation: The NO SQL characteristic declares that the function body does not execute queries against database tables.

4. Why does misdeclaring a non-deterministic function as DETERMINISTIC pose a severe risk in replicated environments?

A. The server crashes immediately B. It can cause master and replica nodes to calculate different results, leading to silent replication data drift C. The binlog runs out of memory D. Passwords become visible Answer: B Explanation: Replicas executing statements containing non-deterministic logic marked as deterministic can compute divergent values, corrupting data integrity.

5. What is the default classification if neither DETERMINISTIC nor NOT DETERMINISTIC is specified?

A. DETERMINISTIC B. NOT DETERMINISTIC C. OPTIMIZED D. STRICT Answer: B Explanation: MySQL defaults to NOT DETERMINISTIC for safety unless explicitly specified otherwise.