Function volatility

In Spanner, functions, including built-in SQL and user-defined functions, are classified by their volatility. The volatility category indicates whether a function returns the same result when you call it with the same arguments. This classification affects how the query optimizer handles the function and where you can use the function, such as in generated column expressions or index definitions. Understanding function volatility helps you write more predictable and performant queries and schemas in Spanner.

There are three volatility categories, which are similar to concepts in systems such as PostgreSQL:

  • Immutable: A function is immutable if it returns the same results when you call it with the same argument values. This behavior means the function's result depends only on its input arguments and not on database state, session settings, or external factors. Most mathematical functions are immutable. The following functions are examples:

    • ABS

    • LENGTH

    • String manipulation functions, such as CONCAT and LOWER

    • Date and time formatting functions with explicit inputs

    Functions that depend on runtime-configurable settings, such as time zone, aren't immutable because their results can vary across different database or session configurations.

  • Stable: A function is stable if it returns the same results for the same argument values within a single statement execution. However, its result can change across different SQL statements. Functions such as CURRENT_TIMESTAMP, CURRENT_DATE, and similar time-based functions are stable. For example, multiple calls to CURRENT_TIMESTAMP within the same SELECT statement yield the same timestamp, but subsequent statements might produce a different timestamp.

  • Volatile: A function is volatile if its value can change even within a single statement execution with the same arguments. For example, the GENERATE_UUID function, which generates a unique ID, is volatile.

Volatility implications

A function's volatility category dictates where it can be used in your database schema and how queries containing the function are optimized:

  • Generated columns: Expressions that define generated columns must be immutable. This ensures that the stored value is deterministic based on the other columns in the row. Spanner produces an error if you try to define a generated column with an expression that's not immutable.

  • Indexing: You can't create indexes on expressions that involve volatile functions. Spanner produces an error if you try to create an index on an expression that involves volatile functions.

  • Query optimization: The query optimizer uses volatility information to perform optimizations. For example, the query optimizer precalculates calls to immutable functions with constant arguments. The optimizer often caches results of stable functions within the scope of a single statement's execution.