Many engines let you declare a stored function as deterministic, or classify it as IMMUTABLE, STABLE, or VOLATILE. What does that declaration actually mean, and what does the database do with it?

## The problem the declaration solves The optimizer would love to be clever with function calls: evaluate one once and reuse it, hoist it out of a loop, turn `WHERE f(x) = 5` into an index lookup, or store `f(x)` in an index so a query never recomputes it. All of those are only legal if the function behaves like a *mathematical* function — same inputs, same output. SQL cannot inspect an arbitrary routine body and prove that. So the engine asks the author to declare it, and then trusts the declaration completely. ## The tiers **Immutable / deterministic.** The result is a pure function of the arguments, forever. `upper(text)`, `a + b`, a formatting routine. It may not read tables, may not call the clock, may not depend on session settings such as time zone or collation-affecting locale, and may not write. Because the answer never changes, the engine can: - fold the call to a constant at planning time when arguments are constants; - evaluate once and reuse for repeated identical arguments; - allow the expression in an **expression index** or a stored generated column, because an entry computed today must still match tomorrow; - use it inside CHECK constraints, since a row that passed validation must still pass on re-check. **Stable.** The result is constant for the duration of a single statement, given the same arguments, but may differ between statements. The canonical case is a function that reads tables: within one statement it sees one snapshot, so it is consistent there. Also anything sensitive to session state that does not change mid-statement, such as the current time zone. The engine may use a stable function in an index *scan* — `WHERE indexed_col = stable_f(1)` can be evaluated once and turned into a lookup key — but it may **not** store the result in an index. **Volatile.** No promise at all. `random()`, sequence generators, functions that write, functions that read data they themselves changed. The engine must evaluate them once per row in the order the plan dictates, and cannot cache, fold, or move them across the plan. ## What the wrong declaration costs you Declaring something *more* volatile than it really is is merely slow: repeated evaluation, no index-expression eligibility, no constant folding. This is why the safe defaults exist — PostgreSQL's default is VOLATILE, MySQL's is NOT DETERMINISTIC. Declaring something *less* volatile than it really is is a correctness bug, and it is quiet. The classic disaster: mark a function IMMUTABLE, build an expression index on it, then change what the function returns — a lookup table changes, a time-zone-dependent conversion drifts, someone edits the body. The index now holds values that no longer match what the function would produce. Queries using the index return one answer, queries that recompute return another, and nothing errors. Recovering requires rebuilding every index and generated column that depends on the function. Subtler versions of the same trap: a timestamp-to-date conversion that silently depends on the session time zone; a text function whose output depends on collation or locale; a function that "only reads a config table that never changes" — until it does. ## Adjacent knobs Some engines pair volatility with other planner hints on routines: an estimated **cost** per call, an estimated **row count** for set-returning functions, a **leakproof**-style flag saying the function cannot leak argument values through error messages (which controls whether it may be pushed below a security barrier), and a **parallel-safety** class saying whether the routine may run in a parallel worker. They are separate promises with the same shape: the author asserts, the planner exploits, and a false assertion is on the author. ## Replication also cares With statement-based replication, a non-deterministic routine replayed on a replica produces different data than on the primary — which is why MySQL historically warned or refused when non-deterministic routines were created under binlog formats that replay statements. Row-based replication removes that concern by shipping the resulting rows instead. ## How to answer in an interview Say the declaration is a contract, name the three tiers with an example each, state at least two optimizations it unlocks (constant folding / caching, and index expressions), and finish on the failure mode: an over-optimistic declaration plus an expression index equals silently wrong results. That sequence covers everything the question is testing.