Deterministic vs Non-Deterministic Functions0%

Working with Cursors: Declare, Open, Fetch, Close

Deterministic vs Non-Deterministic Functions

Advanced13 min read•Updated: 2026-09-12

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.

1CREATE FUNCTION FahrenheitToCelsius(f_temp DOUBLE) 
2RETURNS DOUBLE
3DETERMINISTIC
4NO SQL
5BEGIN
6    RETURN ROUND((f_temp - 32) * 5 / 9, 2);
7END;

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())

1CREATE FUNCTION CalculateAge(birthdate DATE) 
2RETURNS INT
3NOT DETERMINISTIC
4READS SQL DATA
5BEGIN
6    RETURN TIMESTAMPDIFF(YEAR, birthdate, CURRENT_DATE());
7END;

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

Clause	Meaning
`DETERMINISTIC`	Same inputs always return same output.
`NOT DETERMINISTIC`	Output may vary for same inputs.
`CONTAINS SQL`	Routine contains SQL statements but does not read or write data.
`NO SQL`	Routine contains no SQL statements (pure math/logic).
`READS SQL DATA`	Routine queries database tables using `SELECT`.
`MODIFIES SQL DATA`	Routine writes to tables via `INSERT`, `UPDATE`, or `DELETE`.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What defines a DETERMINISTIC function in MySQL?

Question 2

Which of the following functions is inherently NOT DETERMINISTIC?

Question 3

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

Question 4

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

Question 5

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

Next Lesson

Working with Cursors: Declare, Open, Fetch, Close

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Creating User-Defined Functions (CREATE FUNCTION)	Working with Cursors: Declare, Open, Fetch, Close

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Working with Cursors: Declare, Open, Fetch, Close

Deterministic vs Non-Deterministic Functions

Advanced13 min read•Updated: 2026-09-12

Study Materials Cheatsheet

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.

1CREATE FUNCTION FahrenheitToCelsius(f_temp DOUBLE) 
2RETURNS DOUBLE
3DETERMINISTIC
4NO SQL
5BEGIN
6    RETURN ROUND((f_temp - 32) * 5 / 9, 2);
7END;

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())

1CREATE FUNCTION CalculateAge(birthdate DATE) 
2RETURNS INT
3NOT DETERMINISTIC
4READS SQL DATA
5BEGIN
6    RETURN TIMESTAMPDIFF(YEAR, birthdate, CURRENT_DATE());
7END;

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

Clause	Meaning
`DETERMINISTIC`	Same inputs always return same output.
`NOT DETERMINISTIC`	Output may vary for same inputs.
`CONTAINS SQL`	Routine contains SQL statements but does not read or write data.
`NO SQL`	Routine contains no SQL statements (pure math/logic).
`READS SQL DATA`	Routine queries database tables using `SELECT`.
`MODIFIES SQL DATA`	Routine writes to tables via `INSERT`, `UPDATE`, or `DELETE`.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What defines a DETERMINISTIC function in MySQL?

Question 2

Which of the following functions is inherently NOT DETERMINISTIC?

Question 3

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

Question 4

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

Question 5

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

Next Lesson

Working with Cursors: Declare, Open, Fetch, Close

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Creating User-Defined Functions (CREATE FUNCTION)	Working with Cursors: Declare, Open, Fetch, Close

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next