Debian Codegraph @

www/deterministic.html

raw
1 <!DOCTYPE html>
2 <html><head>
3 <meta name="viewport" content="width=device-width, initial-scale=1.0">
4 <meta http-equiv="content-type" content="text/html; charset=UTF-8">
5 <link href="sqlite.css" rel="stylesheet">
6 <title>Deterministic SQL Functions</title>
7 <!-- path= -->
8 </head>
9 <body>
10 <div class=nosearch>
11 <a href="index.html">
12 <img class="logo" src="images/sqlite370_banner.svg" alt="SQLite" border="0">
13 </a>
14 <div><!-- IE hack to prevent disappearing logo --></div>
15 <div class="tagline desktoponly">
16 Small. Fast. Reliable.<br>Choose any three.
17 </div>
18 <div class="menu mainmenu">
19 <ul>
20 <li><a href="index.html">Home</a>
21 <li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
22 <li class='wideonly'><a href='about.html'>About</a>
23 <li class='desktoponly'><a href="docs.html">Documentation</a>
24 <li class='desktoponly'><a href="download.html">Download</a>
25 <li class='wideonly'><a href='copyright.html'>License</a>
26 <li class='desktoponly'><a href="support.html">Support</a>
27 <li class='desktoponly'><a href="prosupport.html">Purchase</a>
28 <li class='search' id='search_menubutton'>
29 <a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
30 </ul>
31 </div>
32 <div class="menu submenu" id="submenu">
33 <ul>
34 <li><a href='about.html'>About</a>
35 <li><a href='docs.html'>Documentation</a>
36 <li><a href='download.html'>Download</a>
37 <li><a href='support.html'>Support</a>
38 <li><a href='prosupport.html'>Purchase</a>
39 </ul>
40 </div>
41 <div class="searchmenu" id="searchmenu">
42 <form method="GET" action="search">
43 <select name="s" id="searchtype">
44 <option value="d">Search Documentation</option>
45 <option value="c">Search Changelog</option>
46 </select>
47 <input type="text" name="q" id="searchbox" value="">
48 <input type="submit" value="Go">
49 </form>
50 </div>
51 </div>
52 <script>
53 function toggle_div(nm) {
54 var w = document.getElementById(nm);
55 if( w.style.display=="block" ){
56 w.style.display = "none";
57 }else{
58 w.style.display = "block";
59 }
60 }
61 function toggle_search() {
62 var w = document.getElementById("searchmenu");
63 if( w.style.display=="block" ){
64 w.style.display = "none";
65 } else {
66 w.style.display = "block";
67 setTimeout(function(){
68 document.getElementById("searchbox").focus()
69 }, 30);
70 }
71 }
72 function div_off(nm){document.getElementById(nm).style.display="none";}
73 window.onbeforeunload = function(e){div_off("submenu");}
74 /* Disable the Search feature if we are not operating from CGI, since */
75 /* Search is accomplished using CGI and will not work without it. */
76 if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
77 document.getElementById("search_menubutton").style.display = "none";
78 }
79 /* Used by the Hide/Show button beside syntax diagrams, to toggle the */
80 function hideorshow(btn,obj){
81 var x = document.getElementById(obj);
82 var b = document.getElementById(btn);
83 if( x.style.display!='none' ){
84 x.style.display = 'none';
85 b.innerHTML='show';
86 }else{
87 x.style.display = '';
88 b.innerHTML='hide';
89 }
90 return false;
91 }
92 var antiRobot = 0;
93 function antiRobotGo(){
94 if( antiRobot!=3 ) return;
95 antiRobot = 7;
96 var j = document.getElementById("mtimelink");
97 if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
98 }
99 function antiRobotDefense(){
100 document.body.onmousedown=function(){
101 antiRobot |= 2;
102 antiRobotGo();
103 document.body.onmousedown=null;
104 }
105 document.body.onmousemove=function(){
106 antiRobot |= 2;
107 antiRobotGo();
108 document.body.onmousemove=null;
109 }
110 setTimeout(function(){
111 antiRobot |= 1;
112 antiRobotGo();
113 }, 100)
114 antiRobotGo();
115 }
116 antiRobotDefense();
117 </script>
118 <div class=fancy>
119 <div class=nosearch>
120 <div class="fancy_title">
121 Deterministic SQL Functions
122 </div>
123 </div>
124
125
126
127
128
129 <h1 id="overview"><span>1. </span>Overview</h1>
130
131 <p>
132 SQL functions in SQLite can be either "deterministic" or "non-deterministic".
133
134 </p><p>
135 A deterministic function always gives the same answer when it has
136 the same inputs. Most built-in SQL functions in SQLite are
137 deterministic. For example, the <a href="lang_corefunc.html#abs">abs(X)</a> function always returns
138 the same answer as long as its input X is the same.
139
140 </p><p>
141 Non-deterministic functions might give different answers on each
142 invocation, even if the arguments are always the same. The following
143 are examples of non-deterministic functions:
144
145 </p><ul>
146 <li> <a href="lang_corefunc.html#random">random()</a>
147 </li><li> <a href="lang_corefunc.html#changes">changes()</a>
148 </li><li> <a href="lang_corefunc.html#last_insert_rowid">last_insert_rowid()</a>
149 </li><li> <a href="c3ref/libversion.html">sqlite3_version()</a>
150 </li></ul>
151
152 <p>
153 The <a href="lang_corefunc.html#random">random()</a> function is obviously non-deterministic because it gives
154 a different answer every time it is invoked. The answers from <a href="lang_corefunc.html#changes">changes()</a>
155 and <a href="lang_corefunc.html#last_insert_rowid">last_insert_rowid()</a> depend on prior SQL statements, and so they
156 are also non-deterministic. The
157 <a href="c3ref/libversion.html">sqlite3_version()</a> function is mostly constant, but it can change when
158 SQLite is upgraded, and so even though it always returns the same answer
159 for any particular session, because it can change answers across sessions
160 it is still considered non-deterministic.
161
162
163 </p><h1 id="restrictions_on_the_use_of_non_deterministic_functions"><span>2. </span>Restrictions on the use of non-deterministic functions</h1>
164
165 <p>
166 There are some contexts in SQLite that do not allow the use of
167 non-deterministic functions:
168
169 </p><ul>
170 <li>In the expression of a <a href="lang_createtable.html#ckconst">CHECK constraint</a>. <i>&larr; see 2.1 below</i>
171 </li><li>In the WHERE clause of a <a href="partialindex.html">partial index</a>.
172 </li><li>In an expression used as part of an <a href="expridx.html">expression index</a>.
173 </li><li>In the expression of a <a href="gencol.html">generated column</a>.
174 </li></ul>
175
176 <p>
177 In the cases above, the values returned by the function affects the
178 information stored in the database file. The values of functions
179 in CHECK constraints determines which entries are valid for a table,
180 and functions in the WHERE clause of a partial index or in an index on
181 an expression compute values stored in the index b-tree.
182 If any of these functions later returns a different
183 value, then the database might no longer be well-formed.
184 Hence, to avoid database corruption,
185 only deterministic functions can be used in the contexts
186 above.
187
188 </p><h2 id="historical_exception_for_check_constraints"><span>2.1. </span>Historical exception for CHECK constraints</h2>
189
190 <p>
191 It is stated above that non-deterministic functions are not allowed
192 in CHECK constraints. That really ought to be the case, but due to
193 an historical bug and the desire to maintain backwards compatibility,
194 that restriction is not actually enforced. You can put a non-deterministic
195 function inside of a CHECK constraints and SQLite will not complain.
196 Doing so will not cause a crash or memory error. But apart from not
197 crashing, no guarantees are made about how that CHECK constraint will
198 function. You ought not do this. If you ignore this advice and you get
199 unexpected behavior, that will not be considered a bug (unless it crashes,
200 as crashes are always considered bugs).
201
202
203 <a name="dtexception"></a>
204
205 </p><h1 id="special_case_processing_for_date_time_functions"><span>3. </span>Special-case Processing For Date/Time Functions</h1>
206
207 <p>
208 The built-in <a href="lang_datefunc.html">date and time functions</a> of SQLite are a special case.
209 These functions are usually considered deterministic. However, if
210 these functions use the string "now" as the date, or if they use
211 the <a href="lang_datefunc.html#localtime">localtime modifier</a> or the <a href="lang_datefunc.html#localtime">utc modifier</a>, then they are
212 considered non-deterministic. Because the function inputs are
213 not necessarily known until run-time, the date/time functions will
214 throw an exception if they encounter any of the non-deterministic
215 features in a context where only deterministic functions are allowed.
216
217 </p><p>
218 Prior to SQLite 3.20.0 (2017-08-01) all date/time functions were
219 always considered non-deterministic. The ability for date/time functions
220 to be deterministic sometimes and non-deterministic at other times,
221 depending on their arguments, was added for the 3.20.0 release.
222
223 </p><h2 id="bug_fix_in_version_3_35_2"><span>3.1. </span>Bug fix in version 3.35.2</h2>
224
225 <p>
226 When the enhancement was made to SQLite 3.20.0 such that date/time
227 functions would be considered deterministic as they do not depend
228 on the current time, one case was overlooked:
229 Many of the date/time functions can be called
230 with no arguments at all. These no-argument date/time functions
231 behave as if they had a single "<tt>'now'</tt>" argument.
232 Thus "<tt>datetime()</tt>" and
233 "<tt>datetime('now')</tt>" both yield the current date and time.
234 However, only the second form was recognized as non-deterministic.
235 This meant that developers could sneak the non-deterministic
236 "<tt>datetime()</tt>" form into CHECK constraints, index
237 expressions, generated column expressions, and similar places
238 where non-deterministic functions make no sense.
239 This oversight was fixed in version 3.35.2 (2021-03-17).
240 However, there may be legacy databases in circulation that were created
241 by SQLite version 3.20.0 through 3.35.1 that have non-deterministic
242 date/time functions in their schemas.
243
244 </p><h1 id="application_defined_deterministic_functions"><span>4. </span>Application-defined deterministic functions</h1>
245
246 <p>
247 By default, <a href="appfunc.html">application-defined SQL functions</a> are considered to
248 be non-deterministic. However, if the 4th parameter to
249 <a href="c3ref/create_function.html">sqlite3_create_function_v2()</a> is OR-ed with
250 <a href="c3ref/c_deterministic.html#sqlitedeterministic">SQLITE_DETERMINISTIC</a>, then SQLite will treat that function as if it
251 were deterministic.
252
253 </p><p>
254 Note that if a non-deterministic function is tagged with
255 <a href="c3ref/c_deterministic.html#sqlitedeterministic">SQLITE_DETERMINISTIC</a> and if that function ends up being used in
256 the WHERE clause of a <a href="partialindex.html">partial index</a> or in an
257 <a href="expridx.html">expression index</a>, then when the function begins to return different
258 answers, the associated index may become corrupt. If an SQL function
259 is nearly deterministic (which is to say, if it only rarely changes,
260 like <a href="lang_corefunc.html#sqlite_version">sqlite_version()</a>) and it is used in an index that becomes
261 corrupt, the corruption can be fixed by running <a href="lang_reindex.html">REINDEX</a>.
262
263 </p><p>
264 The interfaces necessary to construct a function that is sometimes
265 deterministic and sometimes non-deterministic depending on their
266 inputs, such as the built-in date/time functions, are not published.
267 Generic <a href="appfunc.html">application-defined SQL functions</a> must
268 be always deterministic or always non-deterministic.
269 </p><p align="center"><small><i>This page was last updated on 2026-05-20 11:35:23Z </small></i></p>
270