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
|
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<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>
</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_div("searchmenu")'>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">
<span class="desktoponly">Search for:</span> <input type="text" name="q">
<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 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.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;
}
</script>
</div>
<div class=fancy>
<div class=nosearch>
<div class="fancy_title">
Deterministic SQL Functions
</div>
</div>
<p>
SQL functions in SQLite can be either "deterministic" or "non-deterministic".
</p><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 <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><li> <a href="lang_datefunc.html">datetime()</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="lang_datefunc.html">date and time functions</a> are all
considered non-deterministic since, depending on their arguments, they
might return the current time, which is forever changing. 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>1. </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 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></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.
</p><h1 id="application_defined_deterministic_functions"><span>2. </span>Application-defined deterministic functions</h1>
<p>
By default, <a href="c3ref/create_function.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">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">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>
|
