SQLite Requirement Matrix Details
deterministic.html
Index Summary Markup Original
R-45034-43967-17911-30540-50129-42981-54712-01086 tcl slt th3 src
The following are examples of non-deterministic functions:
/* IMP: R-45034-43967 */
# EVIDENCE-OF: R-45034-43967 The following are examples of # non-deterministic functions: random() changes() last_insert_rowid() # sqlite3_version()
R-58546-22075-08471-42930-58515-49649-00646-41652 tcl slt th3 src
The random() function is obviously non-deterministic because it gives a different answer every time it is invoked.
/* IMP: R-58546-22075 */
# EVIDENCE-OF: R-58546-22075 The random() function is obviously # non-deterministic because it gives a different answer every time it is # invoked.
R-60806-29331-56214-63587-21345-27818-10618-50452 tcl slt th3 src
The answers from changes() and last_insert_rowid() depend on prior SQL statements, and so they are also non-deterministic.
/* IMP: R-60806-29331 */
# EVIDENCE-OF: R-60806-29331 The answers from changes() and # last_insert_rowid() depend on prior SQL statements, and so they are # also non-deterministic.
R-24161-47822-59915-14251-27754-57973-48969-03751 tcl slt th3 src
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.
/* IMP: R-24161-47822 */
# EVIDENCE-OF: R-24161-47822 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.
R-55246-25452-29787-15226-62317-06868-45565-21162 tcl slt th3 src
There are some contexts in SQLite that do not allow the use of non-deterministic functions:
- In the expression of a CHECK constraint.
- In the WHERE clause of a partial index.
- In an expression used as part of an expression index.
- In the expression of a generated column.
/* IMP: R-55246-25452 */
# EVIDENCE-OF: R-55246-25452 There are some contexts in SQLite that do # not allow the use of non-deterministic functions: In the expression of # a CHECK constraint. In the WHERE clause of a partial index. In an # expression used as part of an expression index. In the expression of a # generated column.
R-32667-09902-21409-45460-33680-48359-54103-12889 tcl slt th3 src
The built-in date and time functions of SQLite are a special case. These functions are usually considered deterministic. However, if these functions use the string "now" as the date, or if they use the localtime modifier or the utc modifier, then they are considered non-deterministic.
/* IMP: R-32667-09902 */
# EVIDENCE-OF: R-32667-09902 The built-in date and time functions of # SQLite are a special case. These functions are usually considered # deterministic. However, if these functions use the string "now" as the # date, or if they use the localtime modifier or the utc modifier, then # they are considered non-deterministic.
R-24983-33491-21111-64156-17936-16093-21195-54073 tcl slt th3 src
Because the function inputs are not necessarily known until run-time, the date/time functions will throw an exception if they encounter any of the non-deterministic features in a context where only deterministic functions are allowed.
/* IMP: R-24983-33491 */
# EVIDENCE-OF: R-24983-33491 Because the function inputs are not # necessarily known until run-time, the date/time functions will throw # an exception if they encounter any of the non-deterministic features # in a context where only deterministic functions are allowed.
R-35968-60209-16507-36689-50525-63369-04471-36400 tcl slt th3 src
By default, application-defined SQL functions are considered to be non-deterministic.
/* IMP: R-35968-60209 */
# EVIDENCE-OF: R-35968-60209 By default, application-defined SQL # functions are considered to be non-deterministic.
R-25645-37767-35870-43075-30633-04117-48100-12426 tcl slt th3 src
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.
/* IMP: R-25645-37767 */
# EVIDENCE-OF: R-25645-37767 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.
R-34472-09915-38123-43434-43436-60925-14387-37410 tcl slt th3 src
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.
/* IMP: R-34472-09915 */
# EVIDENCE-OF: R-34472-09915 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.
R-42776-35033-43191-08460-16792-47540-52989-38911 tcl slt th3 src
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.
/* IMP: R-42776-35033 */
# EVIDENCE-OF: R-42776-35033 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.