Debian Codegraph @

www/staleexpridx.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>Stale Expression Indexes</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 Stale Expression Indexes
122 </div>
123 <details class="fancy_toc">
124 <summary>Table Of Contents</summary>
125 <div id="toc_sub"><div class="fancy-toc1"><a href="#overview">1. Overview</a></div>
126 <div class="fancy-toc1"><a href="#deterministic_functions_that_are_not_always_100_deterministic">2. Deterministic Functions That Are Not Always 100% Deterministic</a></div>
127 <div class="fancy-toc1"><a href="#are_stale_expression_indexes_really_a_problem_">3. Are Stale Expression Indexes Really A Problem?</a></div>
128 <div class="fancy-toc1"><a href="#self_healing_expression_indexes">4. Self-Healing Expression Indexes</a></div>
129 <div class="fancy-toc1"><a href="#how_to_avoid_and_or_fix_stale_expression_indexes">5. How To Avoid And/Or Fix Stale Expression Indexes</a></div>
130 </div>
131 </details>
132 </div>
133
134
135
136
137
138 <h1 id="overview"><span>1. </span>Overview</h1>
139
140 <p>
141 An <a href="expridx.html">expression index</a> is an <a href="lang_createindex.html">index</a> on either a
142 <a href="gencol.html#vgc*">VIRTUAL generated column</a> or on an expression of table columns.
143 Expression indexes are different in that one or more of their
144 key values is the result of computing an expression rather than
145 just making a copy of a column from the table being indexed.
146
147 </p><p>
148 In order for the index to work correctly, the value of the expression
149 must not change as long as the corresponding table row is unchanged.
150 Hence, indexed expressions must use <a href="deterministic.html">deterministic functions</a> - functions
151 that always return the same output given the same inputs. Deterministic
152 functions are things like "abs(X)" and "concat(X,Y)". Non-deterministic
153 functions are things like "datetime('now')" and "random()".
154
155 </p><p>
156 Sometimes functions that are identified as "deterministic" are not
157 completely deterministic across different CPU architectures, operating
158 systems, and/or SQLite versions. When that happens, an expression index
159 created on one CPU/OS/SQLite-version might be incorrect on a different
160 CPU/OS/SQLite-version. We call this a "<b>stale expression index</b>".
161
162 </p><p>
163 Stale expression indexes rarely occur. All known occurrences involve
164 indexes on floating-point values plus one of the following
165 circumstances:
166
167 </p><ul>
168 <li> The SQLite database is moved from one CPU architecture to another
169 (example: x86_64 to ARM),
170 </li><li> The SQLite database is moved from one operating system to another
171 (example: Windows to Mac)
172 </li><li> There is a version change in the SQLite library and/or an
173 external library that SQLite uses to implement SQL functions.
174 </li></ul>
175
176 <h1 id="deterministic_functions_that_are_not_always_100_deterministic"><span>2. </span>Deterministic Functions That Are Not Always 100% Deterministic</h1>
177
178 <p>
179 A deterministic function is supposed to give the same result always,
180 even when run on different platforms and in different versions of
181 SQLite. But sometimes, rarely, moving from one operating system to
182 another, or when changing from one version of SQLite to another,
183 the output from a supposedly deterministic function can change
184 slightly. Some examples of when this might happen:
185
186 </p><ol>
187 <li><p>
188 The transcendental SQL math functions work by calling the corresponding
189 system C-library functions. So for example,
190 the "sin(X)" SQL function just invokes
191 the sin() function in the standard C math library. The various C math
192 libraries in use today are remarkably consistent. Nevertheless there
193 is nothing in the C-language standards that requires sin(X) to return
194 exactly the same value from one system to another. The sin(X) only needs
195 to return a number that is "close" (within one or two
196 <a href="https://en.wikipedia.org/wiki/Unit_in_the_last_place">ULPs</a> of the
197 correct value) in order to be considered "correct".
198
199 </p></li><li><p>
200 Suppose you are using the <a href="https://github.com/unicode-org/icu">ICU</a>
201 extension function "lower()" and linking
202 against your systems libicu.so. Then an upgrade happens that converts
203 your system libicu.so to a newer version, possibly on a different version
204 of Unicode. The outputs from the lower() function might change, slightly,
205 in extreme corner cases.
206
207 </p></li><li><p>
208 Sometimes built-in functions in SQLite get bug fixes or upgrades that
209 can change their behavior ever so slightly in extreme corner cases - sometimes
210 without the developers even being aware of the change.
211
212 </p></li><li><p>
213 If the database is using an <a href="appfunc.html">application-defined SQL function</a> (ADF),
214 and there is a bug fix or enhancement to that ADF, the values returned
215 might be different.
216 </p></li></ol>
217
218 <p>
219 A notable instance of case 3 is when the internal text&rarr;float conversion
220 routines in SQLite get upgraded for improved accuracy and/or performance,
221 such as happened when moving from version 3.42 to 3.43 and again going
222 from 3.51 to 3.52. There need not be an explicit
223 "<tt>CAST(...&nbsp;AS&nbsp;REAL)</tt>" to force a
224 text&rarr;float conversion. The conversion might happen due to
225 automatic type coercion. Another common "hidden"
226 text&rarr;float conversion happens when using
227 <a href="json1.html#jptr">the ->> operator</a> to extract a floating-point value out of a JSON document.
228 Suppose you want the ability to search a large corpus of JSON documents
229 according to a range of the "mtime" values, which is a floating point
230 number of seconds since the Unix epoch. You might write:
231
232 </p><div class="codeblock"><pre>CREATE INDEX doc_mtime ON docstore(doc-&gt;&gt;'mtime');
233 </pre></div>
234
235 <p>
236 Then if you switch from one version of SQLite to another that implements
237 text&rarr;float conversion differently, you might sometimes get a value from
238 the "<tt>doc-&gt;&gt;'mtime'</tt>" expression that is one ULP different
239 from the value stored in the index.
240
241 </p><p>
242 When the value computed for the expression of an
243 expression index differs from the value actually stored in the
244 index and used as an index key, we call
245 that a "<b>stale expression index</b>".
246
247 </p><h1 id="are_stale_expression_indexes_really_a_problem_"><span>3. </span>Are Stale Expression Indexes Really A Problem?</h1>
248
249 <p>
250 Stale expression indexes rarely occur. You are unlikely to ever
251 encounter one in practice.
252
253 </p><p>
254 Back in 2023, almost three years before the SQLite developers realized
255 that stale expression indexes might exist and might cause concerns, there
256 was an update to text&rarr;float conversion logic inside of SQLite that
257 caused some floating-point value computations to shift by one ULP. This
258 change will cause stale expression indexes. (We know, because we have
259 gone back and tried it.) And yet, even though that change
260 went into billions and billions of phones and personal computers, the
261 SQLite developers never received a single report of index corruption
262 because of it.
263
264 </p><p>
265 Stale expression indexes usually do not cause problems for the application
266 even when they do occur.
267 If the stale value is a floating-point number that is off by one ULP
268 (the only case ever actually observed in the wild), then the error
269 does not normally cause problems for search. You can get differing
270 results when the anomolous entry is right at the boundary of the search
271 range, but <a href="floatingpoint.html#fpapprox">floating point values are approximations</a> - close
272 approximations to be sure, but approximations all the same - so missing
273 an entry that occurs
274 right at the boundary of a search is usually not considered a problem.
275
276 </p><p>
277 Trying to DELETE or UPDATE a row that has a stale expression index entry
278 will cause an error in older versions of SQLite (but not with
279 SQLite 3.53.0 (2026-04-09) or later - see below) and errors will
280 appear when running <a href="pragma.html#pragma_integrity_check">PRAGMA integrity_check</a>. But such problems can be
281 easily cleared by running <a href="lang_reindex.html">REINDEX</a>. If users in the field ever
282 encountered such problems, they never told the SQLite developers about
283 them.
284
285 </p><p>
286 In summary then, stale expression indexes rarely occur, and when they
287 do occur, they are not fatal to the application. Those who aspire to
288 be SQLite experts should be aware of stale expression indexes, but
289 for the average developer, stale expression indexes should never
290 be an issue.
291
292 <a name="selfheal"></a>
293
294 </p><h1 id="self_healing_expression_indexes"><span>4. </span>Self-Healing Expression Indexes</h1>
295
296 <p>
297 Beginning with SQLite version 3.53.0 (2026-04-09), SQLite attempts
298 to fix stale expression indexes automatically in some circumstances.
299 When attempting to DELETE or UPDATE a table row that is associated with
300 a stale expression index entry, instead of raising an error, SQLite
301 now fixes the index. No errors are raised. The application never knows
302 that something was ever amiss.
303
304 </p><p>
305 The <a href="pragma.html#pragma_integrity_check">PRAGMA integrity_check</a> command still reports stale index entries
306 in its output. But in the common case that the stale entry is a
307 floating-point number that is only off by one or two ULPs, then instead
308 of saying that the index is corrupt, the message emitted by
309 PRAGMA integrity_check says something like:
310
311 </p><blockquote><pre>
312 index NAME stores an imprecise floating-point value for row N
313 </pre></blockquote>
314
315 <h1 id="how_to_avoid_and_or_fix_stale_expression_indexes"><span>5. </span>How To Avoid And/Or Fix Stale Expression Indexes</h1>
316
317 <p>
318 If you never want there to be any possibility of encountering a stale
319 expression index, then don't use expression indexes. A good alternative
320 is to create <a href="gencol.html#vgc*">STORED generated columns</a> for expressions that you
321 want to compute automatically, and then create indexes on those
322 STORED generated columns.
323
324 </p><p>
325 The doc_mtime index example shown above would change to be
326 something like the following:
327
328 </p><div class="codeblock"><pre>CREATE TABLE doc(
329 doc JSON,
330 mtime AS (doc-&gt;&gt;'mtime') STORED
331 );
332 CREATE INDEX doc_mtime ON doc(mtime);
333 </pre></div>
334
335 <p>
336 If you have a database that you did not design and just want to know
337 which indexes are expression indexes, you can bring the database up
338 in the <a href="cli.html">CLI</a> (version 3.53.0 or later) and type the command:
339
340 </p><div class="codeblock"><pre>.indexes --expr
341 </pre></div>
342
343 <p>
344 If you think you have stale expression indexes (maybe you know that
345 you changed an <a href="appfunc.html">ADF</a> that is used in an index expression) then you can
346 refresh all your indexes by running the <a href="lang_reindex.html">REINDEX</a> command. Since
347 SQLite version 3.53.0 (2026-04-09) you can save time and I/O by running:
348
349 </p><div class="codeblock"><pre>REINDEX EXPRESSIONS;
350 </pre></div>
351
352 <p>
353 The REINDEX EXPRESSIONS command rebuilds only expression indexes and
354 leaves other indexes unchanged. If the database has no expression
355 indexes (the usual case), then REINDEX EXPRESSIONS is a no-op.
356 </p><p align="center"><small><i>This page was last updated on 2026-04-13 10:54:51Z </small></i></p>
357