Documentation Source Text

Check-in [c705444999]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add requirements marks on the deterministic.html document.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: c705444999a5b63a4cca9e2e72c5f58b2de636c9ff3358c2f7a49451301b2113
User & Date: drh 2017-07-24 13:24:23.793
Context
2017-07-24
14:17
Update the performance graphs for version 3.20.0. (check-in: 5f75ab8f7e user: drh tags: trunk)
13:24
Add requirements marks on the deterministic.html document. (check-in: c705444999 user: drh tags: trunk)
12:05
Fix a typo in the change log. (check-in: 3eb9aa1196 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/deterministic.in.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54



55


56
57

58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
<title>Deterministic SQL Functions</title>
<tcl>hd_keywords {deterministic function} {deterministic functions}\
     {deterministic SQL functions} {non-deterministic functions}</tcl>

<fancy_format>

<h1>Overview</h1>

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

<p>
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|abs(X)] function always returns
the same answer as long as its input X is the same.

<p>
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:

<ul>
<li> [random()]
<li> [changes()]
<li> [last_insert_rowid()]
<li> [sqlite3_version()]
</ul>

<p>
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
[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.


<h1>Restrictions on the use of non-deterministic functions</h1>

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

<ul>
<li>In the expression of a [CHECK constraint].
<li>In the WHERE clause of a [partial index].
<li>In an expression used as part of an [expression index].
</ul>

<p>
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.


<tcl>hd_fragment dtexception {date/time special case}</tcl>
<h1>Special-case Processing For Date/Time Functions</h1>

<p>
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.  Because the inputs to functions 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 allows.

<p>
Prior to SQLite 3.20.0 ([dateof:3.20.0]) all date/time functions were
always considered non-deterministic.  The ability for date/time functions
to be deterministic sometimes and non-deterministic at other times,
depending on their arguments, was added for the 3.20.0 release.

<h1>Application-defined deterministic functions</h1>

<p>
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.

<p>
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].

<p>
The interfaces necessary to construct a function that is sometimes
deterministic and sometimes non-deterministic depending on their












|






|







|


|
|

|









|






|


|
>
>
>
|
>
>
|
|
>





|



|













|
|





|



|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
<title>Deterministic SQL Functions</title>
<tcl>hd_keywords {deterministic function} {deterministic functions}\
     {deterministic SQL functions} {non-deterministic functions}</tcl>

<fancy_format>

<h1>Overview</h1>

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

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

<p>
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:

<ul>
<li> [random()]
<li> [changes()]
<li> [last_insert_rowid()]
<li> [sqlite3_version()]
</ul>)^

<p>
^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
[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.


<h1>Restrictions on the use of non-deterministic functions</h1>

<p>
^(There are some contexts in SQLite that do not allow the use of
non-deterministic functions:

<ul>
<li>In the expression of a [CHECK constraint].
<li>In the WHERE clause of a [partial index].
<li>In an expression used as part of an [expression index].
</ul>)^

<p>
In the cases above, the values returned by the function affects the
information stored in the database file.  The values of functions
in CHECK constraints determines which entries are valid for a table,
and functions in the WHERE clause of a partial index or in an index on
an expression compute values stored in the index b-tree.
If any of these functions later returns a different
value, then the database might no longer be well-formed.  
Hence, to avoid database corruption,
only deterministic functions can be used in the contexts
above.

<tcl>hd_fragment dtexception {date/time special case}</tcl>
<h1>Special-case Processing For Date/Time Functions</h1>

<p>
^(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.)^  ^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 allows.

<p>
Prior to SQLite 3.20.0 ([dateof:3.20.0]) all date/time functions were
always considered non-deterministic.  The ability for date/time functions
to be deterministic sometimes and non-deterministic at other times,
depending on their arguments, was added for the 3.20.0 release.

<h1>Application-defined deterministic functions</h1>

<p>
^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.

<p>
^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].

<p>
The interfaces necessary to construct a function that is sometimes
deterministic and sometimes non-deterministic depending on their