Debian Codegraph @

www/testing.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>How SQLite Is Tested</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 How SQLite Is Tested
122 </div>
123 <details class="fancy_toc">
124 <summary>Table Of Contents</summary>
125 <div id="toc_sub"><div class="fancy-toc1"><a href="#introduction">1. Introduction</a></div>
126 <div class="fancy-toc2"><a href="#executive_summary">1.1. Executive Summary</a></div>
127 <div class="fancy-toc1"><a href="#test_harnesses">2. Test Harnesses</a></div>
128 <div class="fancy-toc1"><a href="#anomaly_testing">3. Anomaly Testing</a></div>
129 <div class="fancy-toc2"><a href="#out_of_memory_testing">3.1. Out-Of-Memory Testing</a></div>
130 <div class="fancy-toc2"><a href="#i_o_error_testing">3.2. I/O Error Testing</a></div>
131 <div class="fancy-toc2"><a href="#crash_testing">3.3. Crash Testing</a></div>
132 <div class="fancy-toc2"><a href="#compound_failure_tests">3.4. Compound failure tests</a></div>
133 <div class="fancy-toc1"><a href="#fuzz_testing">4. Fuzz Testing</a></div>
134 <div class="fancy-toc2"><a href="#sql_fuzz">4.1. SQL Fuzz</a></div>
135 <div class="fancy-toc3"><a href="#sql_fuzz_using_the_american_fuzzy_lop_fuzzer">4.1.1. SQL Fuzz Using The American Fuzzy Lop Fuzzer</a></div>
136 <div class="fancy-toc3"><a href="#google_oss_fuzz">4.1.2. Google OSS Fuzz</a></div>
137 <div class="fancy-toc3"><a href="#the_dbsqlfuzz_and_jfuzz_fuzzers">4.1.3. The dbsqlfuzz and jfuzz fuzzers</a></div>
138 <div class="fancy-toc3"><a href="#other_third_party_fuzzers">4.1.4. Other third-party fuzzers</a></div>
139 <div class="fancy-toc3"><a href="#the_fuzzcheck_test_harness">4.1.5. The fuzzcheck test harness</a></div>
140 <div class="fancy-toc3"><a href="#tension_between_fuzz_testing_and_100_mc_dc_testing">4.1.6. Tension Between Fuzz Testing And 100% MC/DC Testing</a></div>
141 <div class="fancy-toc2"><a href="#malformed_database_files">4.2. Malformed Database Files</a></div>
142 <div class="fancy-toc2"><a href="#boundary_value_tests">4.3. Boundary Value Tests</a></div>
143 <div class="fancy-toc1"><a href="#regression_testing">5. Regression Testing</a></div>
144 <div class="fancy-toc1"><a href="#automatic_resource_leak_detection">6. Automatic Resource Leak Detection</a></div>
145 <div class="fancy-toc1"><a href="#test_coverage">7. Test Coverage</a></div>
146 <div class="fancy-toc2"><a href="#statement_versus_branch_coverage">7.1. Statement versus branch coverage</a></div>
147 <div class="fancy-toc2"><a href="#coverage_testing_of_defensive_code">7.2. Coverage testing of defensive code</a></div>
148 <div class="fancy-toc2"><a href="#forcing_coverage_of_boundary_values_and_boolean_vector_tests">7.3. Forcing coverage of boundary values and boolean vector tests</a></div>
149 <div class="fancy-toc2"><a href="#branch_coverage_versus_mc_dc">7.4. Branch coverage versus MC/DC</a></div>
150 <div class="fancy-toc2"><a href="#measuring_branch_coverage">7.5. Measuring branch coverage</a></div>
151 <div class="fancy-toc2"><a href="#mutation_testing">7.6. Mutation testing</a></div>
152 <div class="fancy-toc2"><a href="#experience_with_full_test_coverage">7.7. Experience with full test coverage</a></div>
153 <div class="fancy-toc1"><a href="#dynamic_analysis">8. Dynamic Analysis</a></div>
154 <div class="fancy-toc2"><a href="#assert">8.1. Assert</a></div>
155 <div class="fancy-toc2"><a href="#valgrind">8.2. Valgrind</a></div>
156 <div class="fancy-toc2"><a href="#memsys2">8.3. Memsys2</a></div>
157 <div class="fancy-toc2"><a href="#mutex_asserts">8.4. Mutex Asserts</a></div>
158 <div class="fancy-toc2"><a href="#journal_tests">8.5. Journal Tests</a></div>
159 <div class="fancy-toc2"><a href="#undefined_behavior_checks">8.6. Undefined Behavior Checks</a></div>
160 <div class="fancy-toc1"><a href="#disabled_optimization_tests">9. Disabled Optimization Tests</a></div>
161 <div class="fancy-toc1"><a href="#checklists">10. Checklists</a></div>
162 <div class="fancy-toc1"><a href="#static_analysis">11. Static Analysis</a></div>
163 <div class="fancy-toc1"><a href="#summary">12. Summary</a></div>
164 </div>
165 </details>
166 </div>
167
168
169
170
171
172
173
174 <h1 id="introduction"><span>1. </span>Introduction</h1>
175
176 <p>The reliability and robustness of SQLite is achieved in part
177 by thorough and careful testing.</p>
178
179 <p>As of <a href="releaselog/3_42_0.html">version 3.42.0</a> (2023-05-16),
180 the SQLite library consists of approximately
181 155.8 KSLOC of C code.
182 (KSLOC means thousands of "Source Lines Of Code" or, in other words,
183 lines of code excluding blank lines and comments.)
184 By comparison, the project has
185 590 times as much
186 test code and test scripts -
187 92053.1 KSLOC.</p>
188
189 <h2 id="executive_summary"><span>1.1. </span>Executive Summary</h2>
190
191 <ul>
192 <li> Four independently developed test harnesses
193 </li><li> 100% branch test coverage in an as-deployed configuration
194 </li><li> Millions and millions of test cases
195 </li><li> Out-of-memory tests
196 </li><li> I/O error tests
197 </li><li> Crash and power loss tests
198 </li><li> Fuzz tests
199 </li><li> Boundary value tests
200 </li><li> Disabled optimization tests
201 </li><li> Regression tests
202 </li><li> Malformed database tests
203 </li><li> Extensive use of assert() and run-time checks
204 </li><li> Valgrind analysis
205 </li><li> Undefined behavior checks
206 </li><li> Checklists
207 </li></ul>
208
209 <a name="harnesses"></a>
210
211 <h1 id="test_harnesses"><span>2. </span>Test Harnesses</h1>
212
213 <p>There are four independent test harnesses used for testing the
214 core SQLite library.
215 Each test harness is designed, maintained, and managed separately
216 from the others.
217 </p>
218
219 <ol>
220 <li><p>
221 <a name="tcl"></a>
222
223 The <b>TCL Tests</b> are the original tests for SQLite.
224 They are contained in the same source tree as the
225 SQLite core and like the SQLite core are in the public domain. The
226 TCL tests are the primary tests used during development.
227 The TCL tests are written using the
228 <a href="http://www.tcl-lang.org/">TCL scripting language</a>.
229 The TCL test harness itself consists of 27.2 KSLOC
230 of C code used to create the TCL interface. The test scripts are contained
231 in 1390 files totaling
232 23.2MB in size. There are
233 51445 distinct test cases, but many of the test
234 cases are parameterized and run multiple times (with different parameters)
235 so that on a full test run millions of
236 separate tests are performed.
237 </p>
238 </li>
239
240 <li><p>
241 The <b><a href="th3.html">TH3</a></b> test harness is a set of proprietary tests, written in
242 C that provide 100% branch test coverage
243 (and <a href="testing.html#mcdc">100% MC/DC test coverage</a>) to
244 the core SQLite library. The TH3 tests are designed to run
245 on embedded and specialized platforms that would not easily support
246 TCL or other workstation services. TH3 tests use only the published
247 SQLite interfaces. TH3 consists of about
248 76.9 MB or 1055.4 KSLOC
249 of C code implementing 50362 distinct test cases.
250 TH3 tests are heavily parameterized, though, so a full-coverage test runs
251 about 2.4 million different test
252 instances.
253 The cases that provide 100% branch test coverage constitute
254 a subset of the total TH3 test suite. A soak test
255 prior to release does about
256 248.5 million tests.
257 Additional information on TH3 is <a href="th3.html">available separately</a>.</p></li>
258
259 <li><p>
260 <a name="slt"></a>
261
262 The <a href="https://sqlite.org/sqllogictest"><b>SQL Logic Test</b></a>
263 or SLT test harness is used to run huge numbers
264 of SQL statements against both SQLite and several other SQL database engines
265 and verify that they all get the same answers. SLT currently compares
266 SQLite against PostgreSQL, MySQL, Microsoft SQL Server, and Oracle 10g.
267 SLT runs 7.2 million queries comprising
268 1.12GB of test data.
269 </p></li>
270
271 <li><p>
272 The <a href="#dbsqlfuzz"><b>dbsqlfuzz</b></a> engine is a
273 proprietary fuzz tester. Other <a href="testing.html#fuzztesting">fuzzers for SQLite</a>
274 mutate either the SQL inputs or the database file. Dbsqlfuzz mutates
275 both the SQL and the database file at the same time, and is thus able
276 to reach new error states. Dbsqlfuzz is built using the
277 <a href="http://llvm.org/docs/LibFuzzer.html">libFuzzer</a> framework of LLVM
278 with a custom mutator. There are
279 336 seed files. The dbsqlfuzz fuzzer
280 runs about one billion test mutations per day.
281 Dbsqlfuzz helps ensure
282 that SQLite is robust against attack via malicious SQL or database
283 inputs.
284 </p></li></ol>
285
286 <p>In addition to the four main test harnesses, there are many other
287 small programs that implement specialized tests. Here are a few
288 examples:
289 </p><ol>
290 <li value="5">The "speedtest1.c" program
291 estimates the performance of SQLite under a typical workload.
292 </li><li>The "mptester.c" program is a stress test for multiple processes
293 concurrently reading and writing a single database.
294 </li><li>The "threadtest3.c" program is a stress test for multiple threads using
295 SQLite simultaneously.
296 </li><li>The "fuzzershell.c" program is used to
297 run some <a href="#fuzztesting">fuzz tests</a>.
298 </li><li>The "jfuzz" program is a libfuzzer-based fuzzer for
299 <a href="json1.html#jsonbx">JSONB</a> inputs to the <a href="json1.html">JSON SQL functions</a>.
300 </li></ol>
301
302
303 <p>All of the tests above must run successfully, on multiple platforms
304 and under multiple compile-time configurations,
305 before each release of SQLite.</p>
306
307 <p>Prior to each check-in to the SQLite source tree, developers
308 typically run a subset (called "veryquick") of the Tcl tests
309 consisting of about
310 304.7 thousand test cases.
311 The veryquick tests include most tests other than the anomaly, fuzz, and
312 soak tests. The idea behind the veryquick tests are that they are
313 sufficient to catch most errors, but also run in only a few minutes
314 instead of a few hours.</p>
315
316 <a name="anomaly"></a>
317
318 <h1 id="anomaly_testing"><span>3. </span>Anomaly Testing</h1>
319
320 <p>Anomaly tests are tests designed to verify the correct behavior
321 of SQLite when something goes wrong. It is (relatively) easy to build
322 an SQL database engine that behaves correctly on well-formed inputs
323 on a fully functional computer. It is more difficult to build a system
324 that responds sanely to invalid inputs and continues to function following
325 system malfunctions. The anomaly tests are designed to verify the latter
326 behavior.</p>
327
328 <a name="oomtesting"></a>
329
330 <h2 id="out_of_memory_testing"><span>3.1. </span>Out-Of-Memory Testing</h2>
331
332 <p>SQLite, like all SQL database engines, makes extensive use of
333 malloc() (See the separate report on
334 <a href="malloc.html">dynamic memory allocation in SQLite</a> for
335 additional detail.)
336 On servers and workstations, malloc() never fails in practice and so correct
337 handling of out-of-memory (OOM) errors is not particularly important.
338 But on embedded devices, OOM errors are frighteningly common and since
339 SQLite is frequently used on embedded devices, it is important that
340 SQLite be able to gracefully handle OOM errors.</p>
341
342 <p>OOM testing is accomplished by simulating OOM errors.
343 SQLite allows an application to substitute an alternative malloc()
344 implementation using the <a href="c3ref/config.html">sqlite3_config</a>(<a href="c3ref/c_config_covering_index_scan.html#sqliteconfigmalloc">SQLITE_CONFIG_MALLOC</a>,...)
345 interface. The TCL and TH3 test harnesses are both capable of
346 inserting a modified version of malloc() that can be rigged to fail
347 after a certain number of allocations. These instrumented mallocs
348 can be set to fail only once and then start working again, or to
349 continue failing after the first failure. OOM tests are done in a
350 loop. On the first iteration of the loop, the instrumented malloc
351 is rigged to fail on the first allocation. Then some SQLite operation
352 is carried out and checks are done to make sure SQLite handled the
353 OOM error correctly. Then the time-to-failure counter
354 on the instrumented malloc is increased by one and the test is
355 repeated. The loop continues until the entire operation runs to
356 completion without ever encountering a simulated OOM failure.
357 Tests like this are run twice, once with the instrumented malloc
358 set to fail only once, and again with the instrumented malloc set
359 to fail continuously after the first failure.</p>
360
361 <a name="ioerrtesting"></a>
362
363 <h2 id="i_o_error_testing"><span>3.2. </span>I/O Error Testing</h2>
364
365 <p>I/O error testing seeks to verify that SQLite responds sanely
366 to failed I/O operations. I/O errors might result from a full disk drive,
367 malfunctioning disk hardware, network outages when using a network
368 file system, system configuration or permission changes that occur in the
369 middle of an SQL operation, or other hardware or operating system
370 malfunctions. Whatever the cause, it is important that SQLite be able
371 to respond correctly to these errors and I/O error testing seeks to
372 verify that it does.</p>
373
374 <p>I/O error testing is similar in concept to OOM testing; I/O errors
375 are simulated and checks are made to verify that SQLite responds
376 correctly to the simulated errors. I/O errors are simulated in both
377 the TCL and TH3 test harnesses by inserting a new
378 <a href="c3ref/vfs.html">Virtual File System object</a> that is specially rigged
379 to simulate an I/O error after a set number of I/O operations.
380 As with OOM error testing, the I/O error simulators can be set to
381 fail just once, or to fail continuously after the first failure.
382 Tests are run in a loop, slowly increasing the point of failure until
383 the test case runs to completion without error. The loop is run twice,
384 once with the I/O error simulator set to simulate only a single failure
385 and a second time with it set to fail all I/O operations after the first
386 failure.</p>
387
388 <p>In I/O error tests, after the I/O error simulation failure mechanism
389 is disabled, the database is examined using
390 <a href="pragma.html#pragma_integrity_check">PRAGMA integrity_check</a> to make sure that the I/O error has not
391 introduced database corruption.</p>
392
393 <a name="crashtesting"></a>
394
395 <h2 id="crash_testing"><span>3.3. </span>Crash Testing</h2>
396
397 <p>Crash testing seeks to demonstrate that an SQLite database will not
398 go corrupt if the application or operating system crashes or if there
399 is a power failure in the middle of a database update. A separate
400 white-paper titled
401 <a href="atomiccommit.html">Atomic Commit in SQLite</a> describes the
402 defensive measures SQLite takes to prevent database corruption following
403 a crash. Crash tests strive to verify that those defensive measures
404 are working correctly.</p>
405
406 <p>It is impractical to do crash testing using real power failures, of
407 course, and so crash testing is done in simulation. An alternative
408 <a href="c3ref/vfs.html">Virtual File System</a> is inserted that allows the test
409 harness to simulate the state of the database file following a crash.</p>
410
411 <p>In the TCL test harness, the crash simulation is done in a separate
412 process. The main testing process spawns a child process which runs
413 some SQLite operation and randomly crashes somewhere in the middle of
414 a write operation. A special <a href="vfs.html">VFS</a> randomly reorders and corrupts
415 the unsynchronized
416 write operations to simulate the effect of buffered filesystems. After
417 the child dies, the original test process opens and reads the test
418 database and verifies that the changes attempted by the child either
419 completed successfully or else were completely rolled back. The
420 <a href="pragma.html#pragma_integrity_check">integrity_check</a> <a href="pragma.html#syntax">PRAGMA</a> is used to make sure no database corruption
421 occurs.</p>
422
423 <p>The TH3 test harness needs to run on embedded systems that do not
424 necessarily have the ability to spawn child processes, so it uses
425 an in-memory <a href="vfs.html">VFS</a> to simulate crashes. The in-memory <a href="vfs.html">VFS</a> can be rigged
426 to make a snapshot of the entire filesystem after a set number of I/O
427 operations. Crash tests run in a loop. On each iteration of the loop,
428 the point at which a snapshot is made is advanced until the SQLite
429 operations being tested run to completion without ever hitting a
430 snapshot. Within the loop, after the SQLite operation under test has
431 completed, the filesystem is reverted to the snapshot and random file
432 damage is introduced that is characteristic of the kinds of damage
433 one expects to see following a power loss. Then the database is opened
434 and checks are made to ensure that it is well-formed and that the
435 transaction either ran to completion or was completely rolled back.
436 The interior of the loop is repeated multiple times for each
437 snapshot with different random damage each time.</p>
438
439 <a name="multifail"></a>
440
441 <h2 id="compound_failure_tests"><span>3.4. </span>Compound failure tests</h2>
442
443 <p>The test suites for SQLite also explore the result of stacking
444 multiple failures. For example, tests are run to ensure correct behavior
445 when an I/O error or OOM fault occurs while trying to recover from a
446 prior crash.
447
448 <a name="fuzztesting"></a>
449
450 </p><h1 id="fuzz_testing"><span>4. </span>Fuzz Testing</h1>
451
452 <p><a href="http://en.wikipedia.org/wiki/Fuzz_testing">Fuzz testing</a>
453 seeks to establish that SQLite responds correctly to invalid, out-of-range,
454 or malformed inputs.</p>
455
456 <h2 id="sql_fuzz"><span>4.1. </span>SQL Fuzz</h2>
457
458 <p>SQL fuzz testing consists of creating syntactically correct yet
459 wildly nonsensical SQL statements and feeding them to SQLite to see
460 what it will do with them. Usually some kind of error is returned
461 (such as "no such table"). Sometimes, purely by chance, the SQL
462 statement also happens to be semantically correct. In that case, the
463 resulting prepared statement is run to make sure it gives a reasonable
464 result.</p>
465
466 <a name="aflfuzz"></a>
467
468 <h3 id="sql_fuzz_using_the_american_fuzzy_lop_fuzzer"><span>4.1.1. </span>SQL Fuzz Using The American Fuzzy Lop Fuzzer</h3>
469
470 <p>The concept of fuzz testing has been around for decades, but fuzz
471 testing was not an effective way to find bugs until 2014 when
472 Michal Zalewski invented the first practical profile-guided fuzzer,
473 <a href="http://lcamtuf.coredump.cx/afl/">American Fuzzy Lop</a> or "AFL".
474 Unlike prior fuzzers that blindly generate random inputs, AFL
475 instruments the program being tested (by modifying the assembly-language
476 output from the C compiler) and uses that instrumentation to detect when
477 an input causes the program to do something different - to follow
478 a new control path or loop a different number of times. Inputs that provoke
479 new behavior are retained and further mutated. In this way, AFL is able
480 to "discover" new behaviors of the program under test, including behaviors
481 that were never envisioned by the designers.
482
483 </p><p>AFL proved adept at finding arcane bugs in SQLite.
484 Most of the findings have been assert() statements where the conditional
485 was false under obscure circumstances. But AFL has also found
486 a fair number of crash bugs in SQLite, and even a few cases where SQLite
487 computed incorrect results.
488
489 </p><p>Because of its past success, AFL became a standard part of the testing
490 strategy for SQLite beginning with <a href="releaselog/3_8_10.html">version 3.8.10</a> (2015-05-07) until
491 it was superseded by better fuzzers in <a href="releaselog/3_29_0.html">version 3.29.0</a> (2019-07-10).
492
493 <a name="ossfuzz"></a>
494
495 </p><h3 id="google_oss_fuzz"><span>4.1.2. </span>Google OSS Fuzz</h3>
496
497 <p>Beginning in 2016, a team of engineers at Google started the
498 <a href="https://github.com/google/oss-fuzz">OSS Fuzz</a> project.
499 OSS Fuzz uses a AFL-style guided fuzzer running on Google's infrastructure.
500 The Fuzzer automatically downloads the latest check-ins for participating
501 projects, fuzzes them, and sends email to the developers reporting any
502 problems. When a fix is checked in, the fuzzer automatically detects this
503 and emails a confirmation to the developers.
504
505 </p><p>SQLite is one of many open-source projects that OSS Fuzz tests. The
506 <a href="https://sqlite.org/src/file/test/ossfuzz.c">test/ossfuzz.c</a> source file
507 in the SQLite repository is SQLite's interface to OSS fuzz.
508
509 </p><p>OSS Fuzz no longer finds historical bugs in SQLite. But it is still
510 running and does occasionally find issues in new development check-ins.
511 Examples:
512 <a href="https://sqlite.org/src/timeline?y=ci&c=c422afb507dc8757">&#91;1&#93;</a>
513 <a href="https://sqlite.org/src/timeline?y=ci&c=0a2eb949f8a759e5">&#91;2&#93;</a>
514 <a href="https://sqlite.org/src/timeline?y=ci&c=62f2235adf796c72">&#91;3&#93;</a>.
515
516 <a name="dbsqlfuzz"></a>
517
518 </p><h3 id="the_dbsqlfuzz_and_jfuzz_fuzzers"><span>4.1.3. </span>The dbsqlfuzz and jfuzz fuzzers</h3>
519
520 <p>Beginning in late 2018, SQLite has been fuzzed using a proprietary
521 fuzzer called "dbsqlfuzz". Dbsqlfuzz is built using the
522 <a href="http://llvm.org/docs/LibFuzzer.html">libFuzzer</a> framework of LLVM.
523
524 </p><p>The dbsqlfuzz fuzzer mutates both the SQL input and the database file
525 at the same time. Dbsqlfuzz uses a custom
526 <a href="https://github.com/google/fuzzing/blob/master/docs/structure-aware-fuzzing.md">Structure-Aware Mutator</a>
527 on a specialized input file that defines both an input database and SQL
528 text to be run against that database. Because it mutates both the input
529 database and the input SQL at the same time, dbsqlfuzz has been able to
530 find some obscure faults in SQLite that were missed by prior fuzzers that
531 mutated only SQL inputs or only the database file.
532 The SQLite developers keep dbsqlfuzz running against trunk in about
533 16 cores at all times. Each instance of dbsqlfuzz program is able to
534 evalutes about 400 test cases per second, meaning that about 500 million
535 cases are checked every day.</p>
536
537 <p>The dbsqlfuzz fuzzer has been very successful at hardening the
538 SQLite code base against malicious attack. Since dbsqlfuzz has been
539 added to the SQLite internal test suite, bug reports from external
540 fuzzers such as OSSFuzz have all but stopped.
541
542 </p><p>Note that dbsqlfuzz is <i>not</i> the Protobuf-based structure-aware
543 fuzzer for SQLite that is used by Chromium and described in the
544 <a href="https://github.com/google/fuzzing/blob/master/docs/structure-aware-fuzzing.md#user-content-example-sqlite">Structure-Aware Mutator article</a>.
545 There is no connection between these two fuzzers, other than the fact that they
546 are both based on <a href="http://llvm.org/docs/LibFuzzer.html">libFuzzer</a>
547 The Protobuf fuzzer for SQLite is written and maintained by the Chromium
548 team at Google, whereas dbsqlfuzz is written and maintained by the original
549 SQLite developers. Having multiple independently-developed fuzzers for SQLite
550 is good, as it means that obscure issues are more likely to be uncovered.
551
552 </p><p>Near the end of January 2024, a second libFuzzer-based tool called
553 "jfuzz" came into use. Jfuzz generates corrupt <a href="json1.html#jsonbx">JSONB</a> blobs and feeds
554 them into the <a href="json1.html">JSON SQL functions</a> to verify that the JSON functions
555 are able to safely and efficiently deal with corrupt binary inputs.
556
557
558 <a name="3pfuzz"></a>
559
560 </p><h3 id="other_third_party_fuzzers"><span>4.1.4. </span>Other third-party fuzzers</h3>
561
562 <p>SQLite seems to be a popular target for third-parties to fuzz.
563 The developers hear about many attempts to fuzz SQLite
564 and they do occasionally get bug reports found by independent
565 fuzzers. All such reports are promptly fixed, so the product is
566 improved and that the entire SQLite user community benefits.
567 This mechanism of having many independent testers is similar to
568 <a href="https://en.wikipedia.org/wiki/Linus%27s_law">Linus's law</a>:
569 "given enough eyeballs, all bugs are shallow".
570
571 </p><p>One fuzzing researcher of particular note is
572 <a href="https://www.manuelrigger.at/">Manuel Rigger</a>.
573 Most fuzzers only look for assertion faults, crashes, undefined behavior (UB),
574 or other easily detected anomalies. Dr. Rigger's fuzzers, on the other hand,
575 are able to find cases where SQLite computes an incorrect answer.
576 Rigger has found
577 <a href="https://sqlite.org/src/timeline?y=t&u=mrigger&n=all">many such cases</a>.
578 Most of these finds are obscure corner cases involving type
579 conversions and affinity transformations, and a good number of the finds
580 are against unreleased features. Nevertheless, his finds are still important
581 as they are real bugs,
582 and the SQLite developers are grateful to be able to identify and fix
583 the underlying problems.
584
585 <a name="fuzzcheck"></a>
586
587 </p><h3 id="the_fuzzcheck_test_harness"><span>4.1.5. </span>The fuzzcheck test harness</h3>
588
589 <p>Historical test cases from <a href="testing.html#aflfuzz">AFL</a>, <a href="testing.html#ossfuzz">OSS Fuzz</a>, and <a href="testing.html#dbsqlfuzz">dbsqlfuzz</a> are
590 collected in a set of database files in the main SQLite source tree
591 and then rerun by the "fuzzcheck" utility program whenever one runs
592 "make test". Fuzzcheck only runs a few thousand "interesting" cases
593 out of the billions of cases that the various fuzzers have
594 examined over the years. "Interesting" cases are cases that exhibit
595 previously unseen behavior. Actual bugs found by fuzzers are always
596 included among the interesting test cases, but most of the cases run
597 by fuzzcheck were never actual bugs.
598
599 <a name="tension"></a>
600
601 </p><h3 id="tension_between_fuzz_testing_and_100_mc_dc_testing"><span>4.1.6. </span>Tension Between Fuzz Testing And 100% MC/DC Testing</h3>
602
603 <p>Fuzz testing and <a href="testing.html#mcdc">100% MC/DC testing</a> are in tension with
604 one another.
605 That is to say, code tested to 100% MC/DC will tend to be
606 more vulnerable to problems found by fuzzing and code that performs
607 well during fuzz testing will tend to have (much) less than
608 100% MC/DC.
609 This is because MC/DC testing discourages <a href="testing.html#defcode">defensive code</a> with
610 unreachable branches, but without defensive code, a fuzzer is
611 more likely to find a path that causes problems. MC/DC testing
612 seems to work well for building code that is robust during
613 normal use, whereas fuzz testing is good for building code that is
614 robust against malicious attack.
615
616 </p><p>Of course, users would prefer code that is both robust in normal
617 use and resistant to malicious attack. The SQLite developers are
618 dedicated to providing that. The purpose of this section is merely
619 to point out that doing both at the same time is difficult.
620
621 </p><p>For much of its history SQLite has been focused on 100% MC/DC testing.
622 Resistance to fuzzing attacks only became a concern with the introduction
623 of AFL in 2014. For a while there, fuzzers were finding many problems
624 in SQLite. In more recent years, the testing strategy of SQLite has
625 evolved to place more emphasis on fuzz testing. We still maintain
626 100% MC/DC of the core SQLite code, but most testing CPU cycles are
627 now devoted to fuzzing.
628
629 </p><p>While fuzz testing and 100% MC/DC testing are in tension, they
630 are not completely at cross-purposes. The fact that the SQlite test
631 suite does test to 100% MC/DC means that when fuzzers do find problems,
632 those problems can be fixed quickly and with little risk of introducing
633 new errors.
634
635 </p><h2 id="malformed_database_files"><span>4.2. </span>Malformed Database Files</h2>
636
637 <p>There are numerous test cases that verify that SQLite is able to
638 deal with malformed database files.
639 These tests first build a well-formed database file, then add
640 corruption by changing one or more bytes in the file by some means
641 other than SQLite. Then SQLite is used to read the database.
642 In some cases, the bytes changes are in the middle of data.
643 This causes the content of the database to change while keeping the
644 database well-formed.
645 In other cases, unused bytes of the file are modified, which has
646 no effect on the integrity of the database.
647 The interesting cases are when bytes of the file that
648 define database structure get changed. The malformed database tests
649 verify that SQLite finds the file format errors and reports them
650 using the <a href="rescode.html#corrupt">SQLITE_CORRUPT</a> return code without overflowing
651 buffers, dereferencing NULL pointers, or performing other
652 unwholesome actions.</p>
653
654 <p>The <a href="testing.html#dbsqlfuzz">dbsqlfuzz</a> fuzzer also does an excellent job of verifying
655 that SQLite responds sanely to malformed database files.</p>
656
657 <h2 id="boundary_value_tests"><span>4.3. </span>Boundary Value Tests</h2>
658
659 <p>SQLite defines certain <a href="limits.html">limits</a> on its operation, such as the
660 maximum number of columns in a table, the maximum length of an
661 SQL statement, or the maximum value of an integer. The TCL and TH3 test
662 suites both contains numerous tests that push SQLite right to the edge
663 of its defined limits and verify that it performs correctly for
664 all allowed values. Additional tests go beyond the defined limits
665 and verify that SQLite correctly returns errors. The source code
666 contains <a href="testing.html#testcase">testcase macros</a> to verify that both sides of each boundary
667 have been tested.</p>
668
669 <a name="regressiontesting"></a>
670
671 <h1 id="regression_testing"><span>5. </span>Regression Testing</h1>
672
673 <p>Whenever a bug is reported against SQLite, that bug is not considered
674 fixed until new test cases that would exhibit the bug have been added
675 to either the TCL or TH3 test suites.
676 Over the years,
677 this has resulted in thousands and thousands of new tests.
678 These regression tests ensure that bugs that have
679 been fixed in the past are not reintroduced into future versions of
680 SQLite.</p>
681
682 <a name="leakcheck"></a>
683
684 <h1 id="automatic_resource_leak_detection"><span>6. </span>Automatic Resource Leak Detection</h1>
685
686 <p>Resource leak occurs when system resources
687 are allocated and never freed. The most troublesome resource leaks
688 in many applications are memory leaks - when memory is allocated using
689 malloc() but never released using free(). But other kinds of resources
690 can also be leaked: file descriptors, threads, mutexes, etc.</p>
691
692 <p>Both the TCL and TH3 test harnesses automatically track system
693 resources and report resource leaks on <u>every</u> test run.
694 No special configuration or setup is required. The test harnesses
695 are especially vigilant with regard to memory leaks. If a change
696 causes a memory leak, the test harnesses will recognize this
697 quickly. SQLite is designed to never leak memory, even after
698 an exception such as an OOM error or disk I/O error. The test
699 harnesses are zealous to enforce this.</p>
700
701 <a name="coverage"></a>
702
703 <h1 id="test_coverage"><span>7. </span>Test Coverage</h1>
704
705 <p>The SQLite core, including the unix <a href="vfs.html">VFS</a>,
706 has 100% branch test coverage under <a href="th3.html">TH3</a> in
707 its default configuration as measured by
708 <a href="http://gcc.gnu.org/onlinedocs/gcc/Gcov.html">gcov</a>.
709 Extensions such as FTS3 and RTree are excluded from this
710 analysis.</p>
711
712 <a name="stmtvbr"></a>
713
714 <h2 id="statement_versus_branch_coverage"><span>7.1. </span>Statement versus branch coverage</h2>
715
716 <p>There are many ways to measure test coverage. The most popular
717 metric is "statement coverage". When you hear someone say that their
718 program as "XX% test coverage" without further explanation, they usually
719 mean statement coverage. Statement coverage measures what percentage
720 of lines of code are executed at least once by the test suite.</p>
721
722 <p>Branch coverage is more rigorous than statement coverage. Branch
723 coverage measures the number of machine-code branch instructions that
724 are evaluated at least once on both directions.</p>
725
726 <p>To illustrate the difference between statement coverage and
727 branch coverage, consider the following hypothetical
728 line of C code:</p>
729
730 <div class="codeblock"><pre>if( a>b && c!=25 ){ d++; }
731 </pre></div>
732
733 <p>Such a line of C code might generate a dozen separate machine code
734 instructions. If any one of those instructions is ever evaluated, then
735 we say that the statement has been tested. So, for example, it might
736 be the case that the conditional expression is
737 always false and the "d" variable is
738 never incremented. Even so, statement coverage counts this line of
739 code as having been tested.</p>
740
741 <p>Branch coverage is more strict. With branch coverage, each test and
742 each subblock within the statement is considered separately. In order
743 to achieve 100% branch coverage in the example above, there must be at
744 least three test cases:</p>
745
746 <p></p><ul>
747 <li> a&lt;=b
748 </li><li> a&gt;b && c==25
749 </li><li> a&gt;b && c!=25
750 </li></ul>
751
752 <p>Any one of the above test cases would provide 100% statement coverage
753 but all three are required for 100% branch coverage. Generally speaking,
754 100% branch coverage implies 100% statement coverage, but the converse is
755 not true. To reemphasize, the
756 <a href="th3.html">TH3</a> test harness for SQLite provides the stronger form of
757 test coverage - 100% branch test coverage.</p>
758
759 <a name="defcode"></a>
760
761 <h2 id="coverage_testing_of_defensive_code"><span>7.2. </span>Coverage testing of defensive code</h2>
762
763 <p>A well-written C program will typically contain some defensive
764 conditionals which in practice are always true or always false.
765 This leads to a
766 programming dilemma: Does one remove defensive code in order to obtain
767 100% branch coverage?</p>
768
769 <p>In SQLite, the answer to the previous question is "no".
770 For testing purposes, the SQLite source code defines
771 macros called ALWAYS() and NEVER(). The ALWAYS() macro
772 surrounds conditions
773 which are expected to always evaluate as true and NEVER() surrounds
774 conditions that are always evaluated to false. These macros serve as
775 comments to indicate that the conditions are defensive code.
776 In release builds, these macros are pass-throughs:</p>
777
778 <div class="codeblock"><pre>#define ALWAYS(X) (X)
779 #define NEVER(X) (X)
780 </pre></div>
781
782 <p>During most testing, however, these macros will throw an assertion
783 fault if their argument does not have the expected truth value. This
784 alerts the developers quickly to incorrect design assumptions.
785
786 </p><div class="codeblock"><pre>#define ALWAYS(X) ((X)?1:assert(0),0)
787 #define NEVER(X) ((X)?assert(0),1:0)
788 </pre></div>
789
790 <p>When measuring test coverage, these macros are defined to be constant
791 truth values so that they do not generate assembly language branch
792 instructions, and hence do not come into play when calculating the
793 branch coverage:</p>
794
795 <div class="codeblock"><pre>#define ALWAYS(X) (1)
796 #define NEVER(X) (0)
797 </pre></div>
798
799 <p>The test suite is designed to be run three times, once for each of
800 the ALWAYS() and NEVER() definitions shown above. All three test runs
801 should yield exactly the same result. There is a run-time test using
802 the <a href="c3ref/test_control.html">sqlite3_test_control</a>(<a href="c3ref/c_testctrl_always.html">SQLITE_TESTCTRL_ALWAYS</a>, ...) interface that
803 can be used to verify that the macros are correctly set to the first
804 form (the pass-through form) for deployment.</p>
805
806 <a name="testcase"></a>
807
808 <h2 id="forcing_coverage_of_boundary_values_and_boolean_vector_tests"><span>7.3. </span>Forcing coverage of boundary values and boolean vector tests</h2>
809
810 <p>Another macro used in conjunction with test coverage measurement is
811 the <tt>testcase()</tt> macro. The argument is a condition for which
812 we want test cases that evaluate to both true and false.
813 In non-coverage builds (that is to say, in release builds) the
814 <tt>testcase()</tt> macro is a no-op:</p>
815
816 <div class="codeblock"><pre>#define testcase(X)
817 </pre></div>
818
819 <p>But in a coverage measuring build, the <tt>testcase()</tt> macro
820 generates code that evaluates the conditional expression in its argument.
821 Then during analysis, a check
822 is made to ensure tests exist that evaluate the conditional to both true
823 and false. <tt>Testcase()</tt> macros are used, for example, to help verify
824 that boundary values are tested. For example:</p>
825
826 <div class="codeblock"><pre>testcase( a==b );
827 testcase( a==b+1 );
828 if( a>b && c!=25 ){ d++; }
829 </pre></div>
830
831 <p>Testcase macros are also used when two or more cases of a switch
832 statement go to the same block of code, to make sure that the code was
833 reached for all cases:</p>
834
835 <div class="codeblock"><pre>switch( op ){
836 case OP_Add:
837 case OP_Subtract: {
838 testcase( op==OP_Add );
839 testcase( op==OP_Subtract );
840 /* ... */
841 break;
842 }
843 /* ... */
844 }
845 </pre></div>
846
847 <p>For bitmask tests, <tt>testcase()</tt> macros are used to verify that every
848 bit of the bitmask affects the outcome. For example, in the following block
849 of code, the condition is true if the mask contains either of two bits
850 indicating either a MAIN_DB or a TEMP_DB is being opened.
851 The <tt>testcase()</tt>
852 macros that precede the if statement verify that both cases are tested:</p>
853
854 <div class="codeblock"><pre>testcase( mask & SQLITE_OPEN_MAIN_DB );
855 testcase( mask & SQLITE_OPEN_TEMP_DB );
856 if( (mask & (SQLITE_OPEN_MAIN_DB|SQLITE_OPEN_TEMP_DB))!=0 ){ ... }
857 </pre></div>
858
859 <p>The SQLite source code contains 1184
860 uses of the <tt>testcase()</tt> macro.</p>
861
862 <a name="mcdc"></a>
863
864 <h2 id="branch_coverage_versus_mc_dc"><span>7.4. </span>Branch coverage versus MC/DC</h2>
865
866 <p>Two methods of measuring test coverage were described above:
867 "statement" and "branch" coverage. There are many other test coverage
868 metrics besides these two. Another popular metric is "Modified
869 Condition/Decision Coverage" or MC/DC.
870 <a href="http://en.wikipedia.org/wiki/Modified_Condition/Decision_Coverage">Wikipedia</a>
871 defines MC/DC as follows:</p>
872
873 <ul>
874 <li> Each decision tries every possible outcome.
875 </li><li> Each condition in a decision takes on every possible outcome.
876 </li><li> Each entry and exit point is invoked.
877 </li><li> Each condition in a decision is shown to independently
878 affect the outcome of the decision.
879 </li></ul>
880
881 <p>In the C programming language
882 where <b><tt>&amp;&amp;</tt></b> and <b><tt>||</tt></b>
883 are "short-circuit" operators, MC/DC and branch coverage are very nearly
884 the same thing. The primary difference is in boolean vector tests.
885 One can test for any of several bits in bit-vector and still obtain
886 100% branch test coverage even though the second element of MC/DC - the
887 requirement that each condition in a decision take on every possible outcome -
888 might not be satisfied.</p>
889
890 <p>SQLite uses <tt>testcase()</tt> macros as described in the previous
891 subsection to make sure that every condition in a bit-vector decision takes
892 on every possible outcome. In this way, SQLite also achieves 100% MC/DC
893 in addition to 100% branch coverage.</p>
894
895 <h2 id="measuring_branch_coverage"><span>7.5. </span>Measuring branch coverage</h2>
896
897 <p>Branch coverage in SQLite is currently measured
898 using <a href="https://gcc.gnu.org/onlinedocs/gcc/Gcov.html">gcov</a> with the "-b"
899 option. First the test program is compiled using options
900 "-g -fprofile-arcs -ftest-coverage" and then the test program is run.
901 Then "gcov -b" is run to generate a coverage report.
902 The coverage report is verbose and inconvenient to read,
903 so the gcov-generated report is processed using
904 some simple scripts to put it into a more human-friendly format.
905 This entire process is automated using scripts, of course.
906
907 </p><p>Note that running SQLite with gcov is not a test of SQLite &mdash;
908 it is a test of the test suite. The gcov run does not test SQLite because
909 the -fprofile-args and -ftest-coverage options cause the compiler to
910 generate different code.
911 The gcov run merely verifies that the test suite provides 100% branch test
912 coverage. The gcov run is a test of the test - a meta-test.
913
914 </p><p>After gcov has been run to verify 100% branch test coverage,
915 then the test program is recompiled using delivery compiler options
916 (without the special -fprofile-arcs and -ftest-coverage options)
917 and the test program is rerun.
918 This second run is the actual test of SQLite.
919
920 </p><p>It is important to verify that the gcov test run
921 and the second real test run both give the same output. Any
922 differences in output indicate either the use of undefined or
923 indeterminate behavior in the SQLite code (and hence a bug),
924 or a bug in the compiler.
925 Note that SQLite has, over the previous decade, encountered bugs
926 in each of GCC, Clang, and MSVC. Compiler bugs, while rare, do happen,
927 which is why it is so important to test the code in an as-delivered
928 configuration.
929
930 <a name="mutationtests"></a>
931
932 </p><h2 id="mutation_testing"><span>7.6. </span>Mutation testing</h2>
933
934 <p>Using gcov (or similar) to show that every branch instruction is taken
935 at least once in both directions is a good measure of test suite quality.
936 But even better is showing that every branch instruction makes
937 a difference in the output. In other words, we want to show
938 not only that every branch instruction both jumps and falls through but also
939 that every branch is doing useful work and that the test suite is able
940 to detect and verify that work. When a branch is found that does not
941 make a difference in the output, that suggests that code associated with
942 the branch can be removed (reducing the size of the library and perhaps
943 making it run faster) or that the test suite is inadequately testing the
944 feature that the branch implements.
945
946 </p><p>SQLite strives to verify that every branch instruction makes a difference
947 using <a href="https://en.wikipedia.org/wiki/Mutation_testing">mutation testing</a>.
948 <a href="th3.html#muttest">A script</a>
949 first compiles the SQLite source code into assembly language
950 (using, for example, the -S option to gcc). Then the script steps through
951 the generated assembly language and, one by one, changes each branch
952 instruction into either an unconditional jump or a no-op, compiles the
953 result, and verifies that the test suite catches the mutation.
954
955 </p><p>
956 Unfortunately, SQLite contains many branch instructions that
957 help the code run faster without changing the output.
958 Such branches generate false-positives during mutation testing.
959 As an example, consider the following
960 <a href="https://sqlite.org/src/artifact/55b5fb474?ln=55-62">hash function</a>
961 used to accelerate table-name lookup:
962
963 </p><div class="codeblock"><pre>55 static unsigned int strHash(const char *z){
964 56 unsigned int h = 0;
965 57 unsigned char c;
966 58 while( (c = (unsigned char)*z++)!=0 ){ /*OPTIMIZATION-IF-TRUE*/
967 59 h = (h&lt;&lt;3) &#94; h &#94; sqlite3UpperToLower&#91;c&#93;;
968 60 }
969 61 return h;
970 62 }
971 </pre></div>
972
973 <p>
974 If the branch instruction that implements the "c!=0" test on line 58
975 is changed into a no-op, then the while-loop will loop forever and the
976 test suite will fail with a time-out. But if that branch is changed
977 into an unconditional jump, then the hash function will always return 0.
978 The problem is that 0 is a valid hash. A hash function that always
979 returns 0 still works in the sense that SQLite still always gets the correct
980 answer. The table-name hash table degenerates into a linked-list
981 and so the table-name lookups that occur while parsing SQL statements
982 might be a little slower, but the end result will be the same.
983
984 </p><p>
985 To work around this problem, comments of the form
986 "<code>/*OPTIMIZATION-IF-TRUE*/</code>" and
987 "<code>/*OPTIMIZATION-IF-FALSE*/</code>" are inserted into the SQLite
988 source code to tell the mutation testing script to ignore some branch
989 instructions.
990
991 <a name="thoughts1"></a>
992
993 </p><h2 id="experience_with_full_test_coverage"><span>7.7. </span>Experience with full test coverage</h2>
994
995 <p>The developers of SQLite have found that full coverage testing is an
996 extremely effective method for locating and preventing bugs.
997 Because every single branch
998 instruction in SQLite core code is covered by test cases, the developers
999 can be confident that changes made in one part of the code
1000 do not have unintended consequences in other parts of the code.
1001 The many new features and performance improvements that have been
1002 added to SQLite in recent years would not have been possible without
1003 the availability of full-coverage testing.</p>
1004
1005 <p>Maintaining 100% MC/DC is laborious and time-consuming.
1006 The level of effort needed to maintain full-coverage testing
1007 is probably not cost effective for a typical application.
1008 However, we think that full-coverage testing is justified for a
1009 <a href="mostdeployed.html">very widely deployed</a> infrastructure library
1010 like SQLite, and especially for a database library which by its very
1011 nature "remembers" past mistakes.
1012
1013
1014 <a name="dynamicanalysis"></a>
1015
1016 </p><h1 id="dynamic_analysis"><span>8. </span>Dynamic Analysis</h1>
1017
1018 <p>Dynamic analysis refers to internal and external checks on the
1019 SQLite code which are performed while the code is live and running.
1020 Dynamic analysis has proven to be a great help in maintaining the
1021 quality of SQLite.</p>
1022
1023 <a name="asserts"></a>
1024
1025 <h2 id="assert"><span>8.1. </span>Assert</h2>
1026
1027 <p>The SQLite core contains 6754 <tt>assert()</tt>
1028 statements that verify function preconditions and postconditions and
1029 loop invariants. Assert() is a macro which is a standard part of
1030 ANSI-C. The argument is a boolean value that is assumed to always be
1031 true. If the assertion is false, the program prints an error message
1032 and halts.</p>
1033
1034 <p>Assert() macros are disabled by compiling with the NDEBUG macro defined.
1035 In most systems, asserts are enabled by default. But in SQLite, the
1036 asserts are so numerous and are in such performance critical places, that
1037 the database engine runs about three times slower when asserts are enabled.
1038 Hence, the default (production) build of SQLite disables asserts.
1039 Assert statements are only enabled when SQLite is compiled with the
1040 SQLITE_DEBUG preprocessor macro defined.</p>
1041
1042 <p>See the <a href="assert.html">Use Of assert in SQLite</a> document
1043 for additional information about how SQLite uses assert().</p>
1044
1045 <a name="valgrind"></a>
1046
1047 <h2 id="valgrind"><span>8.2. </span>Valgrind</h2>
1048
1049 <p><a href="http://valgrind.org/">Valgrind</a> is perhaps the most amazing
1050 and useful developer tool in the world. Valgrind is a simulator - it simulates
1051 an x86 running a Linux binary. (Ports of Valgrind for platforms other
1052 than Linux are in development, but as of this writing, Valgrind only
1053 works reliably on Linux, which in the opinion of the SQLite developers
1054 means that Linux should be the preferred platform for all software development.)
1055 As Valgrind runs a Linux binary, it looks for all kinds of interesting
1056 errors such as array overruns, reading from uninitialized memory,
1057 stack overflows, memory leaks, and so forth. Valgrind finds problems
1058 that can easily slip through all of the other tests run against SQLite.
1059 And, when Valgrind does find an error, it can dump the developer directly
1060 into a symbolic debugger at the exact point where the error occurs, to
1061 facilitate a quick fix.</p>
1062
1063 <p>Because it is a simulator, running a binary in Valgrind is slower than
1064 running it on native hardware. (To a first approximation, an application
1065 running in Valgrind on a workstation will perform about the same as it
1066 would running natively on a smartphone.) So it is impractical to run the full
1067 SQLite test suite through Valgrind. However, the veryquick tests and
1068 the coverage of the TH3 tests are run through Valgrind prior to every
1069 release.</p>
1070
1071 <a name="memtesting"></a>
1072
1073 <h2 id="memsys2"><span>8.3. </span>Memsys2</h2>
1074
1075 <p>SQLite contains a pluggable
1076 <a href="malloc.html">memory allocation subsystem</a>.
1077 The default implementation uses system malloc() and free().
1078 However, if SQLite is compiled with <a href="compile.html#memdebug">SQLITE_MEMDEBUG</a>, an alternative
1079 memory allocation wrapper (<a href="malloc.html#memdebug">memsys2</a>)
1080 is inserted that looks for memory allocation
1081 errors at run-time. The memsys2 wrapper checks for memory leaks, of
1082 course, but also looks for buffer overruns, uses of uninitialized memory,
1083 and attempts to use memory after it has been freed. These same checks
1084 are also done by valgrind (and, indeed, Valgrind does them better)
1085 but memsys2 has the advantage of being much faster than Valgrind, which
1086 means the checks can be done more often and for longer tests.</p>
1087
1088 <a name="mutextesting"></a>
1089
1090 <h2 id="mutex_asserts"><span>8.4. </span>Mutex Asserts</h2>
1091
1092 <p>SQLite contains a pluggable mutex subsystem. Depending on
1093 compile-time options, the default mutex system contains interfaces
1094 <a href="c3ref/mutex_held.html">sqlite3_mutex_held()</a> and <a href="c3ref/mutex_held.html">sqlite3_mutex_notheld()</a> that detect
1095 whether or not a particular mutex is held by the calling thread.
1096 These two interfaces are used extensively within assert() statements
1097 in SQLite to verify mutexes are held and released at all the right
1098 moments, in order to double-check that SQLite does work correctly
1099 in multi-threaded applications.</p>
1100
1101 <a name="journaltest"></a>
1102
1103 <h2 id="journal_tests"><span>8.5. </span>Journal Tests</h2>
1104
1105 <p>One of the things that SQLite does to ensure that transactions
1106 are atomic across system crashes and power failures is to write
1107 all changes into the rollback journal file prior to changing the
1108 database. The TCL test harness contains an alternative
1109 <a href="vfs.html">OS backend</a> implementation that helps to
1110 verify this is occurring correctly. The "journal-test VFS" monitors
1111 all disk I/O traffic between the database file and rollback journal,
1112 checking to make sure that nothing is written into the database
1113 file which has not first been written and synced to the rollback journal.
1114 If any discrepancies are found, an assertion fault is raised.</p>
1115
1116 <p>The journal tests are an additional double-check over and above
1117 the crash tests to make sure that SQLite transactions will be atomic
1118 across system crashes and power failures.</p>
1119
1120 <a name="intoverflow"></a>
1121
1122 <h2 id="undefined_behavior_checks"><span>8.6. </span>Undefined Behavior Checks</h2>
1123
1124 <p>In the C programming language, it is very easy to write code that
1125 has "undefined" or "implementation defined" behavior.
1126 That means that the code might work during development, but then give
1127 a different answer on a different system, or when recompiled using different
1128 compiler options.
1129 Examples of undefined and implementation-defined behavior in
1130 ANSI C include:
1131 </p><ul>
1132 <li>Signed integer overflow. (Signed integer overflow does <u>not</u>
1133 necessarily wrap around, as most people expect.)
1134 </li><li>Shifting an N-bit integer by more than N bits.
1135 </li><li>Shifting by a negative amount.
1136 </li><li>Shifting a negative number.
1137 </li><li>Using the memcpy() function on overlapping buffers.
1138 </li><li>The order of evaluation of function arguments.
1139 </li><li>Whether or not "char" variables are signed or unsigned.
1140 </li><li>And so forth....
1141 </li></ul>
1142
1143 <p>Since undefined and implementation-defined behavior is non-portable
1144 and can easily lead to incorrect answers, SQLite works very hard to avoid it.
1145 For example,
1146 when adding two integer column values together as part of an SQL statement,
1147 SQLite does not simply add them together using the C-language "+" operator.
1148 Instead, it first checks to make sure the
1149 addition will not overflow, and if it will, it does the addition using
1150 floating point instead.
1151
1152 </p><p>To help ensure that SQLite does not make use of undefined or
1153 implementation defined behavior, the test suites are rerun using
1154 instrumented builds that try to detect undefined behavior. For example,
1155 test suites are run using the "-ftrapv" option of GCC. And they
1156 are run again using the "-fsanitize=undefined" option on Clang. And
1157 again using the "/RTC1" option in MSVC. Then the test suites are rerun
1158 using options like "-funsigned-char" and "-fsigned-char" to make sure
1159 that implementation differences do not matter either. Tests are then repeated
1160 on 32-bit and 64-bit systems and on big-endian and little-endian systems,
1161 using a variety of CPU architectures.
1162 Furthermore, the test suites are augmented with many test cases that are
1163 deliberately designed to provoke undefined behavior. For example:
1164 "<b>SELECT -1*(-9223372036854775808);</b>".
1165
1166 <a name="disopttest"></a>
1167
1168 </p><h1 id="disabled_optimization_tests"><span>9. </span>Disabled Optimization Tests</h1>
1169
1170 <p>The <a href="c3ref/test_control.html">sqlite3_test_control</a>(<a href="c3ref/c_testctrl_always.html">SQLITE_TESTCTRL_OPTIMIZATIONS</a>, ...) interface
1171 allows selected SQL statement optimizations to be disabled at run-time.
1172 SQLite should always generate exactly the same answer with optimizations
1173 enabled and with optimizations disabled; the answer simply arrives quicker
1174 with the optimizations turned on. So in a production environment, one always
1175 leaves the optimizations turned on (the default setting).</p>
1176
1177 <p>One verification technique used on SQLite is to run an entire test suite
1178 twice, once with optimizations left on and a second time with optimizations
1179 turned off, and verify that the same output is obtained both times. This
1180 shows that the optimizations do not introduce errors.</p>
1181
1182 <p>Not all test cases can be handled this way. Some test cases check
1183 to verify that the optimizations really are reducing the amount of
1184 computation by counting the number of disk accesses, sort operations,
1185 full-scan steps, or other processing steps that occur during queries.
1186 Those test cases will appear to fail when optimizations are disabled.
1187 But the majority of test cases simply check that the correct answer
1188 was obtained, and all of those cases can be run successfully with and
1189 without the optimizations, in order to show that the optimizations do not
1190 cause malfunctions.</p>
1191
1192
1193 <a name="cklist"></a>
1194
1195 <h1 id="checklists"><span>10. </span>Checklists</h1>
1196
1197 <p>The SQLite developers use an on-line checklist to coordinate testing
1198 activity and to verify that all tests pass prior to each SQLite release.
1199 <a href="https://sqlite.org/checklists/index.html">Past checklists</a>
1200 are retained for historical reference.
1201 (The checklists are read-only for anonymous internet viewers, but
1202 developers can log in and update checklist items in their web
1203 browsers.)
1204 The use of checklists for SQLite testing and other development activities
1205 is inspired by <i>
1206 <a href="http://atulgawande.com/book/the-checklist-manifesto/">The Checklist Manifesto</a>
1207 </i>.</p>
1208
1209 <p>The latest checklists contain approximately 200 items that are
1210 individually verified for each release. Some checklist items only take
1211 a few seconds to verify and mark off. Others involve test suites
1212 that run for many hours.</p>
1213
1214 <p>The release checklist is not automated: developers run each item on
1215 the checklist manually. We find that it is important to keep a human in
1216 the loop. Sometimes problems are found while running a checklist item
1217 even though the test itself passed. It is important to have a human
1218 reviewing the test output at the highest level, and constantly asking
1219 "Is this really right?"</p>
1220
1221 <p>The release checklist is continuously evolving. As new problems or
1222 potential problems are discovered, new checklist items are added to
1223 make sure those problems do not appear in subsequent releases. The
1224 release checklist has proven to be an invaluable tool in helping to
1225 ensure that nothing is overlooked during the release process.</p>
1226
1227
1228 <a name="staticanalysis"></a>
1229
1230 <h1 id="static_analysis"><span>11. </span>Static Analysis</h1>
1231
1232 <p>Static analysis means analyzing source code at compile-time to
1233 check for correctness. Static analysis includes compiler
1234 warning messages and more in-depth analysis engines such as the
1235 <a href="http://clang-analyzer.llvm.org/">Clang Static Analyzer</a>.
1236 SQLite compiles without warnings on GCC and Clang using
1237 the -Wall and -Wextra flags on Linux and Mac and on MSVC on Windows.
1238 No valid warnings are generated by the Clang Static Analyzer tool "scan-build"
1239 either (though recent versions of clang seem to generate many false-positives.)
1240 Nevertheless, some warnings might be generated by other
1241 static analyzers. Users are encouraged not to stress over these
1242 warnings and to instead take solace in the intense testing of SQLite
1243 described above.
1244 </p>
1245
1246 <p>Static analysis has not been helpful in finding
1247 bugs in SQLite. Static analysis has found a few bugs in SQLite, but
1248 those are the exceptions. More bugs have been
1249 introduced into SQLite while trying to get it to compile without
1250 warnings than have been found by static analysis.</p>
1251
1252 <a name="summary"></a>
1253
1254 <h1 id="summary"><span>12. </span>Summary</h1>
1255
1256 <p>SQLite is open source. This gives many people the idea that
1257 it is not well tested as commercial software and is perhaps unreliable.
1258 But that impression is false.
1259 SQLite has exhibited very high reliability in the field and
1260 a very low defect rate, especially considering how rapidly it is evolving.
1261 The quality of SQLite is achieved in part by careful code design and
1262 implementation. But extensive testing also plays a vital role in
1263 maintaining and improving the quality of SQLite. This document has
1264 summarized the testing procedures that every release of SQLite undergoes
1265 with the hope of inspiring confidence that SQLite is
1266 suitable for use in mission-critical applications.</p>
1267 <p align="center"><small><i>This page was last updated on 2026-04-21 10:28:59Z </small></i></p>
1268