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
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
|
<!DOCTYPE html>
<html><head>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<link href="sqlite.css" rel="stylesheet">
<title>Deterministic SQL Functions</title>
<!-- path= -->
</head>
<body>
<div class=nosearch>
<a href="index.html">
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0">
</a>
<div><!-- IE hack to prevent disappearing logo --></div>
<div class="tagline desktoponly">
Small. Fast. Reliable.<br>Choose any three.
</div>
<div class="menu mainmenu">
<ul>
<li><a href="index.html">Home</a>
<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
<li class='wideonly'><a href='about.html'>About</a>
<li class='desktoponly'><a href="docs.html">Documentation</a>
<li class='desktoponly'><a href="download.html">Download</a>
<li class='wideonly'><a href='copyright.html'>License</a>
<li class='desktoponly'><a href="support.html">Support</a>
<li class='desktoponly'><a href="prosupport.html">Purchase</a>
<li class='search' id='search_menubutton'>
<a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
</ul>
</div>
<div class="menu submenu" id="submenu">
<ul>
<li><a href='about.html'>About</a>
<li><a href='docs.html'>Documentation</a>
<li><a href='download.html'>Download</a>
<li><a href='support.html'>Support</a>
<li><a href='prosupport.html'>Purchase</a>
</ul>
</div>
<div class="searchmenu" id="searchmenu">
<form method="GET" action="search">
<select name="s" id="searchtype">
<option value="d">Search Documentation</option>
<option value="c">Search Changelog</option>
</select>
<input type="text" name="q" id="searchbox" value="">
<input type="submit" value="Go">
</form>
</div>
</div>
<script>
function toggle_div(nm) {
var w = document.getElementById(nm);
if( w.style.display=="block" ){
w.style.display = "none";
}else{
w.style.display = "block";
}
}
function toggle_search() {
var w = document.getElementById("searchmenu");
if( w.style.display=="block" ){
w.style.display = "none";
} else {
w.style.display = "block";
setTimeout(function(){
document.getElementById("searchbox").focus()
}, 30);
}
}
function div_off(nm){document.getElementById(nm).style.display="none";}
window.onbeforeunload = function(e){div_off("submenu");}
/* Disable the Search feature if we are not operating from CGI, since */
/* Search is accomplished using CGI and will not work without it. */
if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
document.getElementById("search_menubutton").style.display = "none";
}
/* Used by the Hide/Show button beside syntax diagrams, to toggle the */
function hideorshow(btn,obj){
var x = document.getElementById(obj);
var b = document.getElementById(btn);
if( x.style.display!='none' ){
x.style.display = 'none';
b.innerHTML='show';
}else{
x.style.display = '';
b.innerHTML='hide';
}
return false;
}
var antiRobot = 0;
function antiRobotGo(){
if( antiRobot!=3 ) return;
antiRobot = 7;
var j = document.getElementById("mtimelink");
if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
}
function antiRobotDefense(){
document.body.onmousedown=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousedown=null;
}
document.body.onmousemove=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousemove=null;
}
setTimeout(function(){
antiRobot |= 1;
antiRobotGo();
}, 100)
antiRobotGo();
}
antiRobotDefense();
</script>
<div class=fancy>
<div class=nosearch>
<div class="fancy_title">
Deterministic SQL Functions
</div>
</div>
<h1 id="overview"><span>1. </span>Overview</h1>
<p>
SQL functions in SQLite can be either "deterministic" or "non-deterministic".
</p><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 <a href="lang_corefunc.html#abs">abs(X)</a> function always returns
the same answer as long as its input X is the same.
</p><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:
</p><ul>
<li> <a href="lang_corefunc.html#random">random()</a>
</li><li> <a href="lang_corefunc.html#changes">changes()</a>
</li><li> <a href="lang_corefunc.html#last_insert_rowid">last_insert_rowid()</a>
</li><li> <a href="c3ref/libversion.html">sqlite3_version()</a>
</li></ul>
<p>
The <a href="lang_corefunc.html#random">random()</a> function is obviously non-deterministic because it gives
a different answer every time it is invoked. The answers from <a href="lang_corefunc.html#changes">changes()</a>
and <a href="lang_corefunc.html#last_insert_rowid">last_insert_rowid()</a> depend on prior SQL statements, and so they
are also non-deterministic. The
<a href="c3ref/libversion.html">sqlite3_version()</a> 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.
</p><h1 id="restrictions_on_the_use_of_non_deterministic_functions"><span>2. </span>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:
</p><ul>
<li>In the expression of a <a href="lang_createtable.html#ckconst">CHECK constraint</a>.
</li><li>In the WHERE clause of a <a href="partialindex.html">partial index</a>.
</li><li>In an expression used as part of an <a href="expridx.html">expression index</a>.
</li><li>In the expression of a <a href="gencol.html">generated column</a>.
</li></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.
<a name="dtexception"></a>
</p><h1 id="special_case_processing_for_date_time_functions"><span>3. </span>Special-case Processing For Date/Time Functions</h1>
<p>
The built-in <a href="lang_datefunc.html">date and time functions</a> 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 <a href="lang_datefunc.html#localtime">localtime modifier</a> or the <a href="lang_datefunc.html#localtime">utc modifier</a>, 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 allowed.
</p><p>
Prior to SQLite 3.20.0 (2017-08-01) 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.
</p><h2 id="bug_fix_in_version_3_35_2"><span>3.1. </span>Bug fix in version 3.35.2</h2>
<p>
When the enhancement was made to SQLite 3.20.0 such that date/time
functions would be considered deterministic as they do not depend
on the current time, one case was overlooked:
Many of the date/time functions can be called
with no arguments at all. These no-argument date/time functions
behave as if they had a single "<tt>'now'</tt>" argument.
Thus "<tt>datetime()</tt>" and
"<tt>datetime('now')</tt>" both yield the current date and time.
However, only the second form was recognized as non-deterministic.
This meant that developers could sneak the non-deterministic
"<tt>datetime()</tt>" form into CHECK constraints, index
expressions, generated column expressions, and similar places
where non-deterministic functions make no sense.
This oversight was fixed in version 3.35.2 (2021-03-17).
However, there may be legacy databases in circulation that were created
by SQLite version 3.20.0 through 3.35.1 that have non-deterministic
date/time functions in their schemas.
</p><h1 id="application_defined_deterministic_functions"><span>4. </span>Application-defined deterministic functions</h1>
<p>
By default, <a href="appfunc.html">application-defined SQL functions</a> are considered to
be non-deterministic. However, if the 4th parameter to
<a href="c3ref/create_function.html">sqlite3_create_function_v2()</a> is OR-ed with
<a href="c3ref/c_deterministic.html#sqlitedeterministic">SQLITE_DETERMINISTIC</a>, then SQLite will treat that function as if it
were deterministic.
</p><p>
Note that if a non-deterministic function is tagged with
<a href="c3ref/c_deterministic.html#sqlitedeterministic">SQLITE_DETERMINISTIC</a> and if that function ends up being used in
the WHERE clause of a <a href="partialindex.html">partial index</a> or in an
<a href="expridx.html">expression index</a>, 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 <a href="lang_corefunc.html#sqlite_version">sqlite_version()</a>) and it is used in an index that becomes
corrupt, the corruption can be fixed by running <a href="lang_reindex.html">REINDEX</a>.
</p><p>
The interfaces necessary to construct a function that is sometimes
deterministic and sometimes non-deterministic depending on their
inputs, such as the built-in date/time functions, are not published.
Generic <a href="appfunc.html">application-defined SQL functions</a> must
be always deterministic or always non-deterministic.
</p><p align="center"><small><i>This page last modified on <a href="https://sqlite.org/docsrc/honeypot" id="mtimelink" data-href="https://sqlite.org/docsrc/finfo/pages/deterministic.in?m=72aa24619c4616127">2021-06-17 16:11:09</a> UTC </small></i></p>
|
