Deterministic SQL Functions

SQL functions in SQLite can be either "deterministic" or "non-deterministic".

A deterministic function always gives the same answer whenever it has the same inputs. Most built-in SQL functions in SQLite are deterministic. For example, the abs(X) function always returns the same answer as long as its input X is the same.

Non-deterministic functions might give different answers on each invocation, even if the arguments are always the same. The following are examples of non-deterministic functions:

• random()
• changes()
• last_insert_rowid()
• sqlite3_version()
• datetime()

The random() function is obviously non-deterministic because it gives a different answer every time it is invoked. The answers from changes() and last_insert_rowid() depend on prior SQL statements, and so they are also non-deterministic. The date and time functions are all considered non-deterministic since, depending on their arguments, they might return the current time, which is forever changing. The sqlite3_version() function is mostly constant, but it can change when SQLite is upgraded, and so even though it always returns the same answer for any particular session, because it can change answers across sessions it is still considered non-deterministic.

1. Restrictions on the use of non-deterministic functions

There are some contexts in SQLite that do not allow the use of non-deterministic functions:

• In the WHERE clause of a partial index.
• In an expression used as part of an expression index.

In the cases above, the values returned by the function is recorded in the index b-tree. If the function later starts returning a different value, then the index will be seen as corrupt. Hence, to avoid corrupt indexes, only deterministic functions can be used.

2. Application-defined deterministic functions

By default, application-defined SQL functions are considered to be non-deterministic. However, if the 4th parameter to sqlite3_create_function_v2() is OR-ed with SQLITE_DETERMINISTIC, then SQLite will treat that function as if it were deterministic.

Note that if a non-deterministic function is tagged with SQLITE_DETERMINISTIC and if that function ends up being used in the WHERE clause of a partial index or in an expression index, then when the function begins to return different answers, the associated index may become corrupt. If an SQL function is nearly deterministic (which is to say, if it only rarely changes, like sqlite_version()) and it is used in an index that becomes corrupt, the corruption can be fixed by running REINDEX.