Yikes! Index corruption after a sequence of valid SQL statements!
It has been many years since anything like this bug has snuck into an official SQLite release. But for the pasts seven months (version 3.8.7 through version 220.127.116.11) if you do an INSERT into a carefully crafted schema in which there are two nested triggers that convert an index key value from TEXT to INTEGER and then back to TEXT again, the INTEGER value might get inserted as the index key instead of the correct TEXT, resulting in index corruption. This patch release adds a single line of code to fix the problem.
If you do actually encounter this problem, running REINDEX on the damaged indexes will clear it.
The 3.8.10 release did not add the new SQLITE_ENABLE_DBSTAT_VTAB compile-time option to the sqlite3_compileoption_used() interface. This patch release fixes that omission. And while we are at it, the associated dbstat virtual table was enhanced slightly and a harmless compiler warning was fixed.
There is no reason to upgrade from version 3.8.10 unless you are using the new SQLITE_ENABLE_DBSTAT_VTAB compile-time option.
SQLite version 3.8.10 is a regularly scheduled maintenance release. This release features performance improvements, fixes to several arcane bugs found by the AFL fuzzer, the new "sqldiff.exe" command-line utility, improvements to the documentation, and other enhancements. See the release notes for additional information.
SQLite version 3.8.9 is a regularly scheduled maintenance release. New features in this release include the PRAGMA index_xinfo command, the sqlite3_status64() interface, and the ".dbinfo" command of the command-line shell. See the release notes for additional information.
The 18.104.22.168 patch release fixes an obscure problem in the SQLite code generator that can cause incorrect results when the qualifying expression of a partial index is used inside the ON clause of a LEFT JOIN. This problem has been in the code since support for partial indexes was first added in version 3.8.0. However, it is difficult to imagine a valid reason to every put the qualifying constraint inside the ON clause of a LEFT JOIN, and so this issue has never come up before.
Any applications that is vulnerable to this bug would have encountered problems already. Hence, upgrading from the previous release is optional.
The 22.214.171.124 patch release fixes a single minor problem: It ensures that the sqlite3_wal_checkpoint(TRUNCATE) operation will always truncate the write-ahead log even if log had already been reset and contained no new content. It is unclear if this is a bug fix or a new feature.
Something like this would normally go into the next regularly scheduled release, but a prominent SQLite user needed the change in a hurry so we were happy to rush it out via this patch.
There is no reason to upgrade unless you actually need the enhanced behavior of sqlite3_wal_checkpoint(TRUNCATE).
Within hours of releasing version 3.8.8, a bug was reported against the 10-month-old 3.8.4 release. As that bug exists in all subsequent releases, the decision was made to issue a small patch to the 3.8.8 before it came into widespread use.
See ticket f97c4637102a3ae72b7911 for a description of the bug.
The changes between versions 3.8.8 and 126.96.36.199 are minimal.
SQLite version 3.8.8 is a regularly schedule maintenance release of SQLite.
There are no dramatic new features or performance enhancements in this release, merely incremental improvements. Most of the performance gain in this release comes from refactoring the B-Tree rebalancing logic to avoid unnecessary memcpy() operations. New features include the PRAGMA data_version statement and the ability to accept a VALUES clause with no arbitrary limit on the number of rows. Several obscure bugs have been fixed, including some multithreading races and a work-around for a compiler bug on some Macs.
See the change log for a longer list of enhancements and bug fixes.
This release fixes adds in a mutex that is required by the changes of the 188.8.131.52 patch but was accidentally omitted. The mutex was not required by any of the internal SQLite tests, but Firefox crashes without it. Test cases have been added to ensure that mutex is never again missed.
This release fixes two obscure bugs that can result in incorrect query results and/or application crashes, but not (as far as we can tell) security vulnerabilities. Both bugs have been latent in the code across multiple prior releases and have never before been encountered, so they are unlikely to cause problems. Nevertheless it seems prudent to publish fixes for them both. See the change log for details.
SQLite version 184.108.40.206 is a patch and bug-fix release. Changes from the previous release are minimal.
The primary reason for this release is to enhance the ROLLBACK command so that it allows running queries on the same database connection to continue running as long as the ROLLBACK does not change the schema. In all previous versions of SQLite, a ROLLBACK would cause pending queries to stop immediately and return SQLITE_ABORT or SQLITE_ABORT_ROLLBACK. Pending queries still abort if the ROLLBACK changes the database schema, but as of this patch release, the queries are allowed to continue running if the schema is unmodified.
In addition to the ROLLBACK enhancement, this patch release also includes fixes for three obscure bugs. See the change log for details.
SQLite version 220.127.116.11 is a bug-fix release.
The primary reason for this bug-fix release is to address a problem with updating the value of fields at the end of a table that were added using ALTER TABLE ADD COLUMN. This problem 1 first appeared in the 3.8.7 release.
Another minor annoyance in the 3.8.7 release was the fact that the Android build tried to use the strchrnul() function from the standard C library but that function is not available on Android. Android builds had to add -DHAVE_STRCHRNUL=0 to work around the problem. This patch fixes that so that Android builds should now work without any changes.
The operation of PRAGMA journal_mode=TRUNCATE has been enhanced so that it invokes fsync() after truncating the journal file when PRAGMA synchronous=FULL. This helps to preserve transaction durability in the case of a power loss occurring shortly after commit.
Finally, a couple of long-standing and obscure problems associated with run UPDATE and DELETE on VIEWs were fixed.
The changes from 3.8.7 are minimal.
SQLite version 3.8.7 is a regularly scheduled maintenance release. Upgrading from all prior versions is recommended.
Most of the changes from the previous release have been micro-optimizations designed to help SQLite run a little faster. Each individual optimization has an unmeasurably small performance impact. But the improvements add up. Measured on a well-defined workload (which the SQLite developers use as a proxy for a typical application workload) using cachegrind on Linux and compiled with gcc 4.8.1 and -Os on x64 linux, the current release does over 20% more work for the same number of CPU cycles compared to the previous release. Cachegrind is not a real CPU, and the workload used for measurement is only a proxy. So your performance may vary. We expect to see about half the measured and reported improvement in real-world applications. 10% is less than 20% but it is still pretty good, we think.
This release includes a new set of C-language interfaces that have unsigned 64-bit instead of signed 32-bit length parameters. The new APIs do not provide any new capabilities. But they do make it easier to write applications that are more resistant to integer overflow vulnerabilities.
This release also includes a new sorter that is able to use multiple threads to help with large sort operations. (Sort operations are sometimes required to implement ORDER BY and/or GROUP BY clauses and are almost always required for CREATE INDEX.) The multi-threads sorter is turned off by default and must be enabled using the PRAGMA threads SQL command. Note that the multi-threaded sorter provides faster real-time performance for large sorts, but it also uses more CPU cycles and more energy.
SQLite version 3.8.6 is a regularly scheduled maintenance release. Upgrading from all previous versions is recommended.
This release contains the usual assortment of obscure bug fixes. One bug, however, deserves special attention. A problem appeared in the CREATE INDEX command beginning with version 3.8.2 (2013-12-06) that allowed, under some circumstances, a UNIQUE index to be created on a column that was not unique. Once the index was created, no new non-unique entries could be inserted, but preexisting non-unique entries would remain. See ticket 9a6daf340df99ba93c for further information. In addition to fixing this bug, the PRAGMA integrity_check command has been enhanced to detect non-uniqueness in UNIQUE indices, so that if this bug did introduce any problems in databases, those problems can be easily detected.
Other noteworthy changes include the addition of support for hexadecimal integers (ex: 0x1234), and performance enhancements to the IN operator which, according to mailing list reports, help some queries run up to five times faster.
Version 3.8.6 uses 25% fewer CPU cycles than version 3.8.0 from approximately one year ago, according to valgrind and the test/speedtest1.c test program. On the other hand, the compiled binary for version 3.8.6 is about 5% larger than 3.8.0. The size increase is due in part to the addition of new features such as WITHOUT ROWID tables and common table expressions.
SQLite version 3.8.5 is a regularly scheduled maintenance release. Upgrading from the previous version is recommended.
Version 3.8.5 fixes more than a dozen obscure bugs. None of these bugs should be a problem for existing applications. Nor do any of the bugs represent a security vulnerability. Nevertheless, upgrading is recommended to prevent future problems.
In addition to bug fixes, the 3.8.5 release adds improvements to the query planner, especially regarding sorting using indices and handling OR terms in the WHERE clause for WITHOUT ROWID tables. The ".system" and ".once" dot-commands were added to the command-line interface. And there were enhancements to the FTS4 and RTREE virtual tables. See the change log for details.
The optimizations added in version 3.8.4 caused some queries that involve subqueries in the FROM clause, DISTINCT, and ORDER BY clauses, to give an incorrect result. See ticket 98825a79ce145 for details. This release adds a one-character change to a single line of code to fix the problem.
The code changes that resulted in the performance improvements in version 3.8.4 missed a single buffer overflow test, which could result in a read past the end of a buffer while searching a database that is corrupted in a particular way. Version 18.104.22.168 fixes that problem using a one-line patch.
We are not aware of any problems in version 3.8.4 when working with well-formed database files. The problem fixed by this release only comes up when reading corrupt database files.
Both of these issues came to light within minutes of tagging the previous release. Neither issue is serious but they can be annoying. Hence, the decision was made to do a quick patch release to address both issues.
- Work around a C-preprocessor macro conflict that causes compilation problems for some configurations of Visual Studio.
- Adjust the cost computation for the skip-scan optimization for improved performance.
SQLite version 3.8.4 is a maintenance release featuring performance enhancements and fixes for a number of obscure bugs. There are no significant new features in SQLite version 3.8.4. However, the number of CPU cycles (measured by valgrind) needed to do many common operations has be reduced by about 12% relative to the previous release, and by about 25% relative to version 3.7.16 from approximately one year ago.
Version 3.8.4 of SQLite fixes several corner-case bugs that were found since the previous release. These bugs were unlikely to appear in practice, and none represent a security vulnerability. Nevertheless, developers are encouraged to upgrade from all prior releases.
SQLite version 22.214.171.124 fixes a bug present in versions 3.8.1, 3.8.2 and 3.8.3 that can cause queries to omit valid output rows. Upgrading from those versions is recommended.
The problem only comes up if SQLite is compiled with either the SQLITE_ENABLE_STAT3 or SQLITE_ENABLE_STAT4 compile-time options. In that case, if a query has a WHERE clause that contains expressions like this:WHERE (expr1 OR expr2 OR ... OR exprN) AND column IS NOT NULLWhere all of expr1 through exprN are suitable for use by indexes, then during query planning SQLite might mistakenly converted the "column IS NOT NULL" term into "column>NULL". But the latter term is never true, and so the query would return no rows.
The trouble ticket for this bug is [4c86b126f2]. It is recommended that all users upgrade to avoid this problem.
SQLite version 3.8.3 is a regularly scheduled maintenance release. Upgrading from the previous release is optional.
The most visible change in version 3.8.3 is the addition of support for common table expressions. It is now possible to write a single SELECT statement that will query a tree or graph, using either a depth-first or a breadth-first search. A single SQLite query will even solve Sudoku puzzles or compute the Mandelbrot set. As part of this change, SQLite now accepts a VALUES clause anyplace that a SELECT statement is valid.
This release also includes many small performance enhancements which should give a small speed boost to legacy applications. And there are other minor enhancements such as the addition of the printf() SQL function. See the change log for details.
SQLite version 3.8.2 is a regularly scheduled maintenance release. Upgrading from the previous release is optional.
Version 3.8.2 adds support for WITHOUT ROWID tables. This is a significant extension to SQLite. Database files that contain WITHOUT ROWID tables are not readable or writable by prior versions of SQLite, however databases that do not use WITHOUT ROWID tables are fully backwards and forwards compatible.
The 3.8.2 release contains a potentially incompatible change. In all prior versions of SQLite, a cast from a very large positive floating point number into an integer resulted in the most negative integer. In other words, CAST(+99.9e99 to INT) would yield -9223372036854775808. This behavior came about because it is what x86/x64 hardware does for the equivalent cast in the C language. But the behavior is bizarre. And so it has been changed effective with this release so that a cast from a floating point number into an integer returns the integer between the floating point value and zero that is closest to the floating point value. Hence, CAST(+99.9e99 to INT) now returns +9223372036854775807. Since routines like sqlite3_column_int64() do an implicit cast if the value being accessed is really a floating point number, they are also affected by this change.
Besides the two changes mentioned above, the 3.8.2 release also includes a number of performance enhancements. The skip-scan optimization is now available for databases that have been processed by ANALYZE. Constant SQL functions are now factored out of inner loops, which can result in a significant speedup for queries that contain WHERE clause terms like "date>datetime('now','-2 days')". And various high-runner internal routines have been refactored for reduced CPU load.