SQLite Changelog

What's new in SQLite 3.30.1

Oct 15, 2019
  • This is a bug-fix release that addresses a problem that can occur when an aggregate function in a nested query makes use of the new FILTER clause capability. Some addition patches for various obscure issues are also included, for completeness.

New in SQLite 3.30.0 (Oct 7, 2019)

  • Add support for the FILTER clause on aggregate functions.
  • Add support for the NULLS FIRST and NULLS LAST syntax in ORDER BY clauses.
  • The index_info and index_xinfo pragmas are enhanced to provide information about the on-disk representation of WITHOUT ROWID tables.
  • Add the sqlite3_drop_modules() interface, allowing applications to disable automatically loaded virtual tables that they do not need.
  • Improvements to the .recover dot-command in the CLI so that it recovers more content from corrupt database files.
  • Enhance the RBU extension to support indexes on expressions.
  • Change the schema parser so that it will error out if any of the type, name, and tbl_name columns of the sqlite_master table have been corrupted and the database connection is not in writable_schema mode.
  • The PRAGMA function_list, PRAGMA module_list, and PRAGMA pragma_list commands are now enabled in all builds by default. Disable them using -DSQLITE_OMIT_INTROSPECTION_PRAGMAS.
  • Add the SQLITE_DBCONFIG_ENABLE_VIEW option for sqlite3_db_config().
  • Added the TCL Interface config method in order to be able to disable SQLITE_DBCONFIG_ENABLE_VIEW as well as control other sqlite3_db_config() options from TCL.
  • Added the SQLITE_DIRECTONLY flag for application-defined SQL functions to prevent those functions from being used inside triggers and views.
  • The legacy SQLITE_ENABLE_STAT3 compile-time option is now a no-op.

New in SQLite 3.29.0 (Jul 12, 2019)

  • Added the SQLITE_DBCONFIG_DQS_DML and SQLITE_DBCONFIG_DQS_DDL actions to sqlite3_db_config() for activating and deactivating the double-quoted string literal misfeature. Both default to "on" for legacy compatibility, but developers are encouraged to turn them "off", perhaps using the -DSQLITE_DQS=0 compile-time option.
  • -DSQLITE_DQS=0 is now a recommended compile-time option.
  • Improvements to the query planner:
  • Improved optimization of AND and OR operators when one or the other operand is a constant.
  • Enhancements to the LIKE optimization for cases when the left-hand side column has numeric affinity.
  • Added the "sqlite_dbdata" virtual table for extracting raw low-level content from an SQLite database, even a database that is corrupt.
  • Enhancements to the CLI:
  • Add the ".recover" command which tries to recover as much content as possible from a corrupt database file.
  • Add the ".filectrl" command useful for testing.
  • Add the long-standing ".testctrl" command to the ".help" menu.
  • Added the ".dbconfig" command

New in SQLite 3.28.0 (Apr 17, 2019)

  • This release fixes an obscure security issue. Applications using older versions of SQLite may be vulnerable if
  • SQLite is compiled with certain optional extensions enabled, and
  • the attacker is able to inject arbitrary SQL.
  • We are not aware of any applications that are vulnerable to this problem. On the other hand, we do not know about every application that uses SQLite. If your application allows unauthenticated users on the internet (and hence potential attackers) to run arbitrary SQL and if you build SQLite with any of the optional extensions enabled, then you should take this upgrade at your earliest opportunity.

New in SQLite 3.27.2 (Feb 26, 2019)

  • This is a patch release that fixes a two bugs and various documentation errors found in the version 3.27.1.

New in SQLite 3.27.1 (Feb 10, 2019)

  • After release 3.27.0 was tagged, but before the build could be completed and uploaded, a long-standing bug in the query optimizer was reported via System.Data.SQLite. Since people will be upgrading anyhow, we decided publish the fix for this newly discovered problems right away. Hence, 3.27.1 was released less than 24 hours after 3.27.0.

New in SQLite 3.26.0 (Dec 13, 2018)

  • Optimization: When doing an UPDATE on a table with indexes on expressions, do not update the expression indexes if they do not refer to any of the columns of the table being updated.
  • Allow the xBestIndex() method of virtual table implementations to return SQLITE_CONSTRAINT to indicate that the proposed query plan is unusable and should not be given further consideration.
  • Added the SQLITE_DBCONFIG_DEFENSIVE option which disables the ability to create corrupt database files using ordinary SQL.
  • Added support for read-only shadow tables when the SQLITE_DBCONFIG_DEFENSIVE option is enabled.
  • Added the PRAGMA legacy_alter_table command, which if enabled causes the ALTER TABLE command to behave like older version of SQLite (prior to version 3.25.0) for compatibility.
  • Added PRAGMA table_xinfo that works just like PRAGMA table_info except that it also shows hidden columns in virtual tables.
  • Added the explain virtual table as a run-time loadable extension.
  • Add a limit counter to the query planner to prevent excessive sqlite3_prepare() times for certain pathological SQL inputs.
  • Added support for the sqlite3_normalized_sql() interface, when compiling with SQLITE_ENABLE_NORMALIZE.
  • Enhanced triggers so that they can use table-valued functions that exist in schemas other than the schema where the trigger is defined.
  • Enhancements to the CLI:
  • Improvements to the ".help" command.
  • The SQLITE_HISTORY environment variable, if it exists, specifies the name of the command-line editing history file
  • The --deserialize option associated with opening a new database cause the database file to be read into memory and accessed using the sqlite3_deserialize() API. This simplifies running tests on a database without modifying the file on disk.
  • Enhancements to the geopoly extension:
  • Aways stores polygons using the binary format, which is faster and uses less space.
  • Added the geopoly_regular() function.
  • Added the geopoly_ccw() function.
  • Enhancements to the session extension:
  • Added the SQLITE_CHANGESETAPPLY_INVERT flag
  • Added the sqlite3changeset_start_v2() interface and the SQLITE_CHANGESETSTART_INVERT flag.
  • Added the changesetfuzz.c test-case generator utility.

New in SQLite 3.25.3 (Nov 8, 2018)

  • SQLite version 3.25.3 is a third patch against 3.25.0 that fixes various problems that have come to light and which seem serious enough to justify a patch.

New in SQLite 3.25.2 (Sep 26, 2018)

  • SQLite version 3.25.2 is another patch against 3.25.0 that fixes still more problems associated with the new window function feature and the ALTER TABLE enhancements. Of particular note is the new PRAGMA legacy_alter_table=ON command, which causes the ALTER TABLE RENAME command to behave in the same goofy way that it did before the enhancements found in version 3.25.0 → references to renamed tables that are inside the bodies of triggers and views are not updated. The legacy behavior is arguably a bug, but some programs depend on the older buggy behavior. The 3.25.2 release also contains a fix to window function processing for VIEWs. There also a slew of other minor fixes that affect obscure compile-time options.

New in SQLite 3.25.1 (Sep 19, 2018)

  • SQLite version 3.25.1 is a patch against version 3.25.0 that contains two one-line fixes for bug that were introduced in version 3.25.0.

New in SQLite 3.25.0 (Sep 17, 2018)

  • Support for window functions was added, using the PostgreSQL documentation as the baseline.
  • The ALTER TABLE command was enhanced to support renaming of columns, and so that column and table renames are propagated into trigger bodies and views.
  • In addition, there are various performance enhancements and minor fixes.

New in SQLite 3.24.0 (Jun 5, 2018)

  • Highlights of this release include support for PostgreSQL-style UPSERT and improved performance, especially for ORDER BY LIMIT queries.

New in SQLite 3.23.1 (Apr 13, 2018)

  • The version 3.23.1 release fixes a bug in the new LEFT JOIN strength reduction optimization added to version 3.23.0. A few other minor and obscure fixes were also inserted, as well as a small performance optimization. Code changes relative to version 3.23.0 are minimal.

New in SQLite 3.22.0 (Jan 23, 2018)

  • The output of sqlite3_trace_v2() now shows each individual SQL statement run within a trigger.
  • Add the ability to read from WAL mode databases even if the application lacks write permission on the database and its containing directory, as long as the -shm and -wal files exist in that directory.
  • Added the rtreecheck() scalar SQL function to the R-Tree extension.
  • Added the sqlite3_vtab_nochange() and sqlite3_value_nochange() interfaces to help virtual table implementations optimize UPDATE operations.
  • Added the sqlite3_vtab_collation() interface.
  • Added support for the "^" initial token syntax in FTS5.
  • New extensions:
  • The Zipfile virtual table can read and write a ZIP Archive.
  • Added the fsdir(PATH) table-valued function to the fileio.c extension, for listing the files in a directory.
  • The sqlite_btreeinfo eponymous virtual table for introspecting and estimating the sizes of the btrees in a database.
  • The Append VFS is a VFS shim that allows an SQLite database to be appended to some other file. This allows (for example) a database to be appended to an executable that then opens and reads the database.
  • Query planner enhancements:
  • The optimization that uses an index to quickly compute an aggregate min() or max() is extended to work with indexes on expressions.
  • The decision of whether to implement a FROM-clause subquery as a co-routine or using query flattening now considers whether the result set of the outer query is "complex" (if it contains functions or expression subqueries). A complex result set biases the decision toward the use of co-routines.
  • The planner avoids query plans that use indexes with unknown collating functions.
  • The planner omits unused LEFT JOINs even if they are not the right-most joins of a query.
  • Other performance optimizations:
  • A smaller and faster implementation of text to floating-point conversion subroutine: sqlite3AtoF().
  • The Lemon parser generator creates a faster parser.
  • Use the strcspn() C-library routine to speed up the LIKE and GLOB operators.
  • Improvements to the command-line shell:
  • The ".schema" command shows the structure of virtual tables.
  • Added support for reading and writing SQL Archive files using the .archive command.
  • Added the experimental .expert command
  • Added the ".eqp trigger" variant of the ".eqp" command
  • Enhance the ".lint fkey-indexes" command so that it works with WITHOUT ROWID tables.
  • If the filename argument to the shell is a ZIP archive rather than an SQLite database, then the shell automatically opens that ZIP archive using the Zipfile virtual table.
  • Added the edit() SQL function.
  • Added the .excel command to simplify exporting database content to a spreadsheet.
  • Databases are opened using Append VFS when the --append flag is used on the command line or with the .open command.
  • Enhance the SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option so that it works for WITHOUT ROWID tables.
  • Provide the sqlite_offset(X) SQL function that returns the byte offset into the database file to the beginning of the record holding value X, when compiling with -DSQLITE_ENABLE_OFFSET_SQL_FUNC.
  • Bug fixes:
  • Infinite loop on an UPDATE that uses an OR operator in the WHERE clause. Problem introduced with 3.17.0 and reported on the mailing list about one year later. Ticket 47b2581aa9bfecec.
  • Incorrect query results when the skip-ahead-distinct optimization is used. Ticket ef9318757b152e3a.
  • Incorrect query results on a join with a ORDER BY DESC. Ticket 123c9ba32130a6c9.
  • Inconsistent result set column names between CREATE TABLE AS and a simple SELECT. Ticket 3b4450072511e621
  • Assertion fault when doing REPLACE on an index on an expression. Ticket dc3f932f5a147771
  • Assertion fault when doing an IN operator on a constant index. Ticket aa98619ad08ddcab

New in SQLite 3.21.0 (Oct 29, 2017)

  • Take advantage of the atomic-write capabilities in the F2FS filesystem when available, for greatly reduced transaction overhead. This currently requires the SQLITE_ENABLE_BATCH_ATOMIC_WRITE compile-time option.
  • Allow ATTACH and DETACH commands to work inside of a transaction.
  • Allow WITHOUT ROWID virtual tables to be writable if the PRIMARY KEY contains exactly one column.
  • The "fsync()" that occurs after the header is written in a WAL reset now uses the sync settings for checkpoints. This means it will use a "fullfsync" on macs if PRAGMA checkpoint_fullfsync set on.
  • The sqlite3_sourceid() function tries to detect if the source code has been modified from what is checked into version control and if there are modifications, the last four characters of the version hash are shown as "alt1" or "alt2". The objective is to detect accidental and/or careless edits. A forger can subvert this feature.
  • Improved de-quoting of column names for CREATE TABLE AS statements with an aggregate query on the right-hand side.
  • Fewer "stat()" system calls issued by the unix VFS.
  • Enhanced the LIKE optimization so that it works with an ESCAPE clause.
  • Enhanced PRAGMA integrity_check and PRAGMA quick_check to detect obscure row corruption that they were formerly missing. Also update both pragmas so that they return error text rather than SQLITE_CORRUPT when encountering corruption in records.
  • The query planner now prefers to implement FROM-clause subqueries using co-routines rather using the query flattener optimization. Support for the use of co-routines for subqueries may no longer be disabled.
  • Pass information about !=, IS, IS NOT, NOT NULL, and IS NULL constraints into the xBestIndex method of virtual tables.
  • Enhanced the CSV virtual table so that it accepts the last row of input if the final new-line character is missing.
  • Remove the rarely-used "scratch" memory allocator. Replace it with the SQLITE_CONFIG_SMALL_MALLOC configuration setting that gives SQLite a hint that large memory allocations should be avoided when possible.
  • Added the swarm virtual table to the existing union virtual table extension.
  • Added the sqlite_dbpage virtual table for providing direct access to pages of the database file. The source code is built into the amalgamation and is activated using the -DSQLITE_ENABLE_DBPAGE_VTAB compile-time option.
  • Add a new type of fts5vocab virtual table - "instance" - that provides direct access to an FTS5 full-text index at the lowest possible level.
  • Remove a call to rand_s() in the Windows VFS since it was causing problems in Firefox on some older laptops.
  • The src/shell.c source code to the command-line shell is no longer under version control. That file is now generated as part of the build process.
  • Miscellaneous microoptimizations reduce CPU usage by about 2.1%.
  • Bug fixes:
  • Fix a faulty assert() statement discovered by OSSFuzz. Ticket cb91bf4290c211d
  • Fix an obscure memory leak in sqlite3_result_pointer(). Ticket 7486aa54b968e9b
  • Avoid a possible use-after-free error by deferring schema resets until after the query planner has finished running. Ticket be436a7f4587ce5
  • Only use indexes-on-expressions to optimize ORDER BY or GROUP BY if the COLLATE is correct. Ticket e20dd54ab0e4383
  • Fix an assertion fault that was coming up when the expression in an index-on-expressions is really a constant. Ticket aa98619ad08ddca
  • Fix an assertion fault that could occur following PRAGMA reverse_unordered_selects. Ticket cb91bf4290c211d
  • Fix a segfault that can occur for queries that use table-valued functions in an IN or EXISTS subquery. Ticket b899b6042f97f5
  • Fix a potential integer overflow problem when compiling a particular horrendous common table expression. This was another problem discovered by OSSFuzz. Check-in 6ee8cb6ae5.
  • Fix a potential out-of-bound read when querying a corrupt database file, a problem detected by Natalie Silvanovich of Google Project Zero. Check-in 04925dee41a21f.

New in SQLite 3.20.1 (Sep 4, 2017)

  • The version 3.20.1 patch release changes two lines of code in the sqlite3_result_pointer() interface in order to fix a rare memory leak.

New in SQLite 3.19.3 (Jun 12, 2017)

  • Version 3.19.3 is an emergency patch release to fix a bug in auto_vacuum logic that can lead to database corruption. The bug was introduced in version 3.16.0 (2017-01-02). Though the bug is obscure and rarely encountered, upgrading is recommended for all users, and especially for users who turn on auto_vacuum.

New in SQLite 3.19.2 (May 25, 2017)

  • Still more problems have been found in the LEFT JOIN flattening optimization that was added in the 3.19.0 release. This patch release fixes all known issues with that optimization and adds new test cases. Hopefully this will be the last patch.

New in SQLite 3.19.1 (May 24, 2017)

  • One of the new query planner optimizations in the 3.19.0 release contained bugs. The 3.19.1 patch release fixes them.
  • Beginning with 3.19.0, subqueries and views on the right-hand side of a LEFT JOIN operator could sometimes be flattened into the main query. The new optimization worked well for all of the test cases that the developers devised, and for millions of legacy test cases, but once 3.19.0 was released, users found some other cases where the optimization failed. Ticket cad1ab4cb7b0fc344 contains examples.
  • These problems exist only in 3.19.0. Users of SQLite 3.19.0 should upgrade, but users of all prior versions of SQLite are safe.

New in SQLite 3.19.0 (May 23, 2017)

  • The emphasis on this release is improvements to the query planner. There are also some obscure bug fixes. There is no reason to upgrade unless you are having problems with a prior release.

New in SQLite 3.18.0 (Mar 31, 2017)

  • This release features an initial implementation the "PRAGMA optimize" command. This command can now be used to cause ANALYZE to be run on an as-needed basis. Applications should invoke "PRAGMA optimize" just before closing the database connection. The "PRAGMA optimize" statement will likely be enhanced to do other kinds of automated database maintenance in future releases.
  • The Fossil version control system that is used to manage the SQLite project has been upgraded to use SHA3-256 hashes instead of SHA1. Therefore, the version identifications for SQLite now show a 64-hex-digit SHA3-256 hash rather than the 40-hex-digit SHA1 hash.

New in SQLite 3.17.0 (Feb 15, 2017)

  • Most of the changes in this release are performance optimizations. Optimizations to the R-Tree extension are especially noticeable.
  • In this release, the default size of the lookaside buffer allocated for each database connection is increased from 64,000 to 120,000 bytes. This provides improved performance on many common workloads in exchange for a small increase in memory usage. Applications that value a small memory footprint over raw speed can change the lookaside buffer size back to its old value (or to zero) using the SQLITE_DEFAULT_LOOKASIDE compile-time option, or the sqlite3_config(SQLITE_CONFIG_LOOKASIDE) start-time setting, or the sqlite3_db_config(SQLITE_DBCONFIG_LOOKASIDE) run-time setting.

New in SQLite 3.16.2 (Jan 11, 2017)

  • One of the performance optimizations added in 3.16.0 caused triggers and foreign keys to malfunction for the REPLACE statement on WITHOUT ROWID tables that lack secondary indexes. This patch release fixes the problem.

New in SQLite 3.16.1 (Jan 4, 2017)

  • SQLite version 3.16.1 fixes a bug in the row-value logic for UPDATE statements inside of triggers. The bug has been there since row-values were added by release 3.15.0, but was not discovered until just a few minutes after the 3.16.0 release was published, and so it was not fixed by 3.16.0. This patch release is version 3.16.0 with the row-value bug fix.

New in SQLite 3.16.0 (Jan 3, 2017)

  • This release includes many microoptimizations that collectively reduce the CPU cycle count by about 9%, add there have been important enhancements to the command-line shell.
  • Support for PRAGMA functions is added, so that many pragma statements can be used as part of a larger SQL query. This is considered an experimental feature. We do not anticipate any changes to the PRAGMA function interface, but will keep continue to call this interface "experimental" for a few release cycles in case unforeseen issues arise.

New in SQLite 3.15.2 (Nov 29, 2016)

  • Multiple bug fixes to the row value logic that was introduced in version 3.15.0.
  • Fix a NULL pointer dereference in ATTACH/DETACH following a maliciously constructed syntax error. Ticket 2f1b168ab4d4844.
  • Fix a crash that can occur following an out-of-memory condition in the built-in instr() function.
  • In the JSON extension, fix the JSON validator so that it correctly rejects invalid backslash escapes within strings.

New in SQLite 3.15.1 (Nov 7, 2016)

  • Added support for row values.
  • Allow deterministic SQL functions in the WHERE clause of a partial index.
  • Added the "modeof=filename" URI parameter on the unix VFS
  • Added support for SQLITE_DBCONFIG_MAINDBNAME.
  • Added the ability to VACUUM an ATTACH-ed database.
  • Enhancements to the command-line shell:
  • Add the ".testcase" and ".check" dot-commands.
  • Added the --new option to the ".open" dot-command, causing any prior content in the database to be purged prior to opening.
  • Enhance the fts5vocab virtual table to handle "ORDER BY term" efficiently.
  • Miscellaneous micro-optimizations reduce CPU usage by more than 7% on common workloads. Most optimization in this release has been on the front-end (sqlite3_prepare_v2()).
  • Bug Fixes:
  • The multiply operator now correctly detects 64-bit integer overflow and promotes to floating point in all corner-cases. Fix for ticket 1ec41379c9c1e400.
  • Correct handling of columns with redundant unique indexes when those columns are used on the LHS of an IN operator. Fix for ticket 0eab1ac759.
  • Skip NULL entries on range queries in indexes on expressions. Fix for ticket 4baa46491212947.
  • Ensure that the AUTOINCREMENT counters in the sqlite_sequence table are initialized doing "Xfer Optimization" on "INSERT ... SELECT" statements. Fix for ticket 7b3328086a5c116c.
  • Make sure the ORDER BY LIMIT optimization (from check-in 559733b09e) works with IN operators on INTEGER PRIMARY KEYs. Fix for ticket 96c1454c

New in SQLite 3.15.0 (Oct 16, 2016)

  • SQLite version 3.15.0 is a regularly scheduled maintenance release. The key feature in this release is the added support for row values. There are also other enhancements and fixes for a number of obscure bugs.
  • The 3.15.0 release uses about 7% fewer CPU cycles than 3.14.2. Most of the improvement in this release is in the SQL parser, query planner, and byte-code generator (the front-end) corresponding to the sqlite3_prepare_v2() interface. Overall, version 3.15.0 uses about half as much CPU time as version 3.8.1 (2013-10-17). These performance measurements are made using the "speedtest1.c" workload on x64 compiled with gcc and -Os. Performance improvements may vary with different platforms and workloads.

New in SQLite 3.14.2 (Sep 12, 2016)

  • Improved support for using the STDCALL calling convention in winsqlite3.dll.
  • Fix the sqlite3_trace_v2() interface so that it is disabled if either the callback or the mask arguments are zero, in accordance with the documentation.
  • Fix commenting errors and improve the comments generated on EXPLAIN listings when the -DSQLITE_ENABLE_EXPLAIN_COMMENTS compile-time option is used.
  • Fix the ".read" command in the command-line shell so that it understands that its input is not interactive.
  • Correct affinity computations for a SELECT on the RHS of an IN operator. Fix for ticket 199df4168c.
  • The ORDER BY LIMIT optimization is not valid unless the inner-most IN operator loop is actually used by the query plan. Fix for ticket 0c4df46116e90f92.
  • Fix an internal code generator problem that was causing some DELETE operations to no-op. Ticket ef360601

New in SQLite 3.14.1 (Aug 12, 2016)

  • SQLite version 3.14.1 adds a small patch to improve the performance of the pcache1TruncateUnsafe() routine for cases when the only a few pages on the end of the cache are being removed. This causes COMMITs to run faster when there is a very large page cache. Upgrading from version 3.14 is optional.

New in SQLite 3.14.0 (Aug 8, 2016)

  • Added support for WITHOUT ROWID virtual tables.
  • Improved the query planner so that the OR optimization can be used on virtual tables even if one or more of the disjuncts use the LIKE, GLOB, REGEXP, MATCH operators.
  • Added the CSV virtual table for reading RFC 4180 formatted comma-separated value files.
  • Added the carray() table-valued function extension.
  • Enabled persistent loadable extensions using the new SQLITE_OK_LOAD_PERMANENTLY return code from the extension entry point.
  • Added the SQLITE_DBSTATUS_CACHE_USED_SHARED option to sqlite3_db_status().
  • Add the vfsstat.c loadable extension - a VFS shim that measures I/O together with an eponymous virtual table that provides access to the measurements.
  • Improved algorithm for running queries with both an ORDER BY and a LIMIT where only the inner-most loop naturally generates rows in the correct order.
  • Enhancements to Lemon parser generator, so that it generates a faster parser.
  • The PRAGMA compile_options command now attempts to show the version number of the compiler that generated the library.
  • Enhance PRAGMA table_info so that it provides information about eponymous virtual tables.
  • Added the "win32-none" VFS, analogous to the "unix-none" VFS, that works like the default "win32" VFS except that it ignores all file locks.
  • The query planner uses a full scan of a partial index instead of a full scan of the main table, in cases where that makes sense.
  • Allow table-valued functions to appear on the right-hand side of an IN operator.
  • Created the dbhash.exe command-line utility.
  • Added two new C-language interfaces: sqlite3_expanded_sql() and sqlite3_trace_v2(). These new interfaces subsume the functions of sqlite3_trace() and sqlite3_profile() which are now deprecated.
  • Added the json_quote() SQL function to the json1 extension.
  • Disable the authorizer callback while reparsing the schema.
  • Added the SQLITE_ENABLE_UNKNOWN_SQL_FUNCTION compile-time option and turned that option on by default when building the command-line shell.
  • Bug Fixes:
  • Fix the ALTER TABLE command so that it does not corrupt descending indexes when adding a column to a legacy file format database. Ticket f68bf68513a1c15f
  • Fix a NULL-pointer dereference/crash that could occurs when a transitive WHERE clause references a non-existent collating sequence. Ticket e8d439c77685eca6.
  • Improved the cost estimation for an index scan which includes a WHERE clause that can be partially or fully evaluated using columns in the index and without having to do a table lookup. This fixes a performance regression that occurred for some obscure queries following the ORDER BY LIMIT optimization introduced in version 3.12.0.

New in SQLite 3.13.0 (May 18, 2016)

  • SQLite version 3.13.0 is a regularly schedule maintenance release containg performance enhancements and fixes for obscure bugs.

New in SQLite 3.12.2 (Apr 18, 2016)

  • The 3.12.0 and 3.12.1 releases contain a backwards compatibility bug! Tables that declare a column with type "INTEGER" PRIMARY KEY (where the datatype name INTEGER is quoted) generate an incompatible database file. The mistake came about because the developers have never thought to put a typename in quotes before, and so there was no documentation of that capability nor any tests. (There are tests now, though, of course.) Instances of quoting the datatype name are probably infrequent in the wild, so we do not expect the impact of this bug to be too severe. Upgrading is still strongly recommended.
  • Fixes for three other minor issues were included in this patch release. The other issues would have normally been deferred until the next scheduled release, but since a patch release is being issued anyhow, they might as well be included.

New in SQLite 3.12.1 (Apr 8, 2016)

  • SQLite version 3.12.1 is an emergency patch release to address a crash bug that snuck into version 3.12.0. Upgrading from version 3.12.0 is highly recommended.
  • Another minor problem involving datatypes on view columns, and a query planner deficiency are fixed at the same time. These two issues did not justify a new release on their own, but since a release is being issued to deal with the crash bug, we included these other fixes for good measure.

New in SQLite 3.12.0 (Mar 29, 2016)

  • Potentially Disruptive Change:
  • The SQLITE_DEFAULT_PAGE_SIZE is increased from 1024 to 4096. The SQLITE_DEFAULT_CACHE_SIZE is changed from 2000 to -2000 so the same amount of cache memory is used by default. See the application note on the version 3.12.0 page size change for further information.
  • Performance enhancements:
  • Enhancements to the Lemon parser generator so that it creates a smaller and faster SQL parser.
  • Only create master journal files if two or more attached databases are all modified, do not have PRAGMA synchronous set to OFF, and do not have the journal_mode set to OFF, MEMORY, or WAL.
  • Only create statement journal files when their size exceeds a threshold. Otherwise the journal is held in memory and no I/O occurs. The threshold can be configured at compile-time using SQLITE_STMTJRNL_SPILL or at start-time using sqlite3_config(SQLITE_CONFIG_STMTJRNL_SPILL).
  • The query planner is able to optimize IN operators on virtual tables even if the xBestIndex method does not set the sqlite3_index_constraint_usage.omit flag of the virtual table column to the left of the IN operator.
  • The query planner now does a better job of optimizing virtual table accesses in a 3-way or higher join where constraints on the virtual table are split across two or more other tables of the join.
  • More efficient handling of application-defined SQL functions, especially in cases where the application defines hundreds or thousands of custom functions.
  • The query planner considers the LIMIT clause when estimating the cost of ORDER BY.
  • The configure script (on unix) automatically detects pread() and pwrite() and sets compile-time options to use those OS interfaces if they are available.
  • Reduce the amount of memory needed to hold the schema.
  • Other miscellaneous micro-optimizations for improved performance and reduced memory usage.
  • New Features:
  • Added the SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER option to sqlite3_db_config() which allows the two-argument version of the fts3_tokenizer() SQL function to be enabled or disabled at run-time.
  • Added the sqlite3rbu_bp_progress() interface to the RBU extension.
  • The PRAGMA defer_foreign_keys=ON statement now also disables RESTRICT actions on foreign key.
  • Added the sqlite3_system_errno() interface.
  • Added the SQLITE_DEFAULT_SYNCHRONOUS and SQLITE_DEFAULT_WAL_SYNCHRONOUS compile-time options. The SQLITE_DEFAULT_SYNCHRONOUS compile-time option replaces the SQLITE_EXTRA_DURABLE option, which is no longer supported.
  • Enhanced the ".stats" command in the command-line shell to show more information about I/O performance obtained from /proc, when available.
  • Bug fixes:
  • Make sure the sqlite3_set_auxdata() values from multiple triggers within a single statement do not interfere with one another. Ticket dc9b1c91.
  • Fix the code generator for expressions of the form "x IN (SELECT...)" where the SELECT statement on the RHS is a correlated subquery. Ticket 5e3c886796e5512e.
  • Fix a harmless TSAN warning associated with the sqlite3_db_readonly() interface.

New in SQLite 3.11.1 (Mar 7, 2016)

  • SQLite version 3.11.1 is a patch release that fixes problems in the new FTS5 extension and increases a default setting in the spellfix1 extension, and implements enhancements to some of the Windows makefiles. The SQLite core is unchanged from 3.11.0. Upgrading is optional.

New in SQLite 3.11.0 (Feb 15, 2016)

  • General improvements:
  • Enhanced WAL mode so that it works efficiently with transactions that are larger than the cache_size.
  • Added the FTS5 detail option.
  • Added the "EXTRA" option to PRAGMA synchronous that does a sync of the containing directory when a rollback journal is unlinked in DELETE mode, for better durability. The SQLITE_EXTRA_DURABLE compile-time option enables PRAGMA synchronous=EXTRA by default.
  • Enhanced the query planner so that it is able to use a covering index as part of the OR optimization.
  • Avoid recomputing NOT NULL and CHECK constraints on unchanged columns in UPDATE statement.
  • Many micro-optimizations, resulting in a library that is faster than the previous release.
  • Enhancements to the command-line shell:
  • By default, the shell is now in "auto-explain" mode. The output of EXPLAIN commands is automatically formatted.
  • Added the ".vfslist" dot-command.
  • The SQLITE_ENABLE_EXPLAIN_COMMENTS compile-time option is now turned on by default in the standard builds.
  • Enhancements to the TCL Interface:
  • If a database connection is opened with the "-uri 1" option, then URI filenames are honored by the "backup" and "restore" commands.
  • Added the "-sourceid" option to the "sqlite3" command.
  • Makefile improvements:
  • Improved pthreads detection in configure scripts.
  • Add the ability to do MSVC Windows builds from the amalgamation tarball.
  • Bug fixes:
  • Fix an issue with incorrect sharing of VDBE temporary registers between co-routines that could cause incorrect query results in obscure cases. Ticket d06a25c84454a.
  • Fix a problem in the sqlite3_result_subtype() interface that could cause problems for the json1 extension under obscure circumstances. Fix for ticket f45ac567eaa9f9.
  • Escape control characters in JSON strings. Fix for ticket ad2559db380abf8.
  • Reenable the xCurrentTime and xGetLastError methods in the built-in unix VFSes as long as SQLITE_OMIT_DEPRECATED is not defined.
  • Backwards Compatibility:
  • Because of continuing security concerns, the two-argument version of of the seldom-used and little-known fts3_tokenizer() function is disabled unless SQLite is compiled with the SQLITE_ENABLE_FTS3_TOKENIZER.

New in SQLite 3.10.2 (Jan 21, 2016)

  • An optimization attempt gone bad resulted in a bug in the LIKE operator which is fixed by this patch release. Three other minor but low-risk fixes are also included in the patch.

New in SQLite 3.10.1 (Jan 14, 2016)

  • SQLite version 3.10.1 is a bug-fix release primarily targeting the fix for the query planner bug cb3aa0641d9a4 discovered by Mapscape. Also included is a minor API enhancement requested by the Firefox developers at Mozilla. The differences from version 3.10.0 are minimal.

New in SQLite 3.10.0 (Jan 7, 2016)

  • General improvements:
  • Added support for LIKE, GLOB, and REGEXP operators on virtual tables.
  • Added the colUsed field to sqlite3_index_info for use by the sqlite3_module.xBestIndex method.
  • Enhance the PRAGMA cache_spill statement to accept a 32-bit integer parameter which is the threshold below which cache spilling is prohibited.
  • On unix, if a symlink to a database file is opened, then the corresponding journal files are based on the actual filename, not the symlink name.
  • Added the "--transaction" option to sqldiff.
  • Added the sqlite3_db_cacheflush() interface.
  • Added the sqlite3_strlike() interface.
  • When using memory-mapped I/O map the database file read-only so that stray pointers and/or array overruns in the application cannot accidently modify the database file.
  • Added the experimental sqlite3_snapshot_get(), sqlite3_snapshot_open(), and sqlite3_snapshot_free() interfaces. These are subject to change or removal in a subsequent release.
  • Enhance the 'utc' modifier in the date and time functions so that it is a no-op if the date/time is known to already be in UTC. (This is not a compatibility break since the behavior has long been documented as "undefined" in that case.)
  • Added the json_group_array() and json_group_object() SQL functions in the json extension.
  • Added the SQLITE_LIKE_DOESNT_MATCH_BLOBS compile-time option.
  • Many small performance optimizations.
  • Portability enhancements:
  • Work around a sign-exension bug in the optimizer of the HP C compiler on HP/UX. (details)
  • Enhancements to the command-line shell:
  • Added the ".changes ON|OFF" and ".vfsinfo" dot-commands.
  • Translate between MBCS and UTF8 when running in cmd.exe on Windows.
  • Enhancements to makefiles:
  • Added the --enable-editline and --enable-static-shell options to the various autoconf-generated configure scripts.
  • Omit all use of "awk" in the makefiles, to make building easier for MSVC users.
  • Important fixes:
  • Fix inconsistent integer to floating-point comparison operations that could result in a corrupt index if the index is created on a table column that contains both large integers and floating point values of similar magnitude. Ticket 38a97a87a6.
  • Fix an infinite-loop in the query planner that could occur on malformed common table expressions.
  • Various bug fixes in the sqldiff tool.

New in SQLite 3.9.2 (Nov 8, 2015)

  • SQLite version 3.9.2 is a patch release fixing two obscure bugs.

New in SQLite 3.9.1 (Oct 16, 2015)

  • SQlite version 3.9.1 is a small patch to version 3.9.0 that includes a few simple build script and #ifdef tweaks to make the code easier to compile on a wider variety of platform. There are no functional changes, except for a single minor bug-fix in the json1 extension to stop it from recognizing form-feed (ASCII 0x0c) as a whitespace character, in conformance with RFC7159.

New in SQLite 3.9.0 (Oct 14, 2015)

  • Begin using semantic versioning.
  • JSON SQL functions
  • The FTS5 full-text search engine
  • Support for indexes on expressions
  • Support for table-valued functions

New in SQLite 3.8.11.1 (Jul 29, 2015)

  • SQLite version 3.8.11.1 is a patch release that fixes two arcane issues that were reported shortly after 3.8.11 was released. Upgrade from 3.8.11 only in the unlikely event that one of these obscure issues affect your code.

New in SQLite 3.8.11 (Jul 28, 2015)

  • Added the experimental RBU extension. Note that this extension is experimental and subject to change in incompatible ways.
  • Added the experimental FTS5 extension. Note that this extension is experimental and subject to change in incompatible ways.
  • Added the sqlite3_value_dup() and sqlite3_value_free() interfaces.
  • Enhance the spellfix1 extension to support ON CONFLICT clauses.
  • The IS operator is now able to drive indexes.
  • Enhance the query planner to permit automatic indexing on FROM-clause subqueries that are implemented by co-routine.
  • Disallow the use of "rowid" in common table expressions.
  • Added the PRAGMA cell_size_check command for better and earlier detection of database file corruption.
  • Added the matchinfo 'b' flag to the matchinfo() function in FTS3.
  • Improved fuzz-testing of database files, with fixes for problems found.
  • Add the fuzzcheck test program and automatically run this program using both SQL and database test cases on "make test".
  • Added the SQLITE_MUTEX_STATIC_VFS1 static mutex and use it in the Windows VFS.
  • The sqlite3_profile() callback is invoked (by sqlite3_reset() or sqlite3_finalize()) for statements that did not run to completion.
  • Enhance the page cache so that it can preallocate a block of memory to use for the initial set page cache lines. Set the default preallocation to 100 pages. Yields about a 5% performance increase on common workloads.
  • Miscellaneous micro-optimizations result in 22.3% more work for the same number of CPU cycles relative to the previous release. SQLite now runs twice as fast as version 3.8.0 and three times as fast as version 3.3.9. (Measured using cachegrind on the speedtest1.c workload on Ubuntu 14.04 x64 with gcc 4.8.2 and -Os. Your performance may vary.)
  • Added the sqlite3_result_zeroblob64() and sqlite3_bind_zeroblob64() interfaces.
  • Important bug fixes:
  • Fix CREATE TABLE AS so that columns of type TEXT never end up holding an INT value. Ticket f2ad7de056ab1dc9200
  • Fix CREATE TABLE AS so that it does not leave NULL entries in the sqlite_master table if the SELECT statement on the right-hand side aborts with an error. Ticket 873cae2b6e25b
  • Fix the skip-scan optimization so that it works correctly when the OR optimization is used on WITHOUT ROWID tables. Ticket 8fd39115d8f46
  • Fix the sqlite3_memory_used() and sqlite3_memory_highwater() interfaces so that they actually do provide a 64-bit answer.

New in SQLite 3.8.10.2 (May 21, 2015)

  • 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 3.8.10.1) 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.

New in SQLite 3.8.10.1 (May 9, 2015)

  • 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.

New in SQLite 3.8.10 (May 8, 2015)

  • 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.

New in SQLite 3.8.9 (Apr 9, 2015)

  • New features in this release include the PRAGMA index_xinfo command, the sqlite3_status64() interface, and the ".dbinfo" command of the command-line shell.

New in SQLite 3.8.8.3 (Feb 26, 2015)

  • The 3.8.8.3 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.

New in SQLite 3.8.8.2 (Jan 31, 2015)

  • The 3.8.8.2 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).

New in SQLite 3.8.8.1 (Jan 21, 2015)

  • Fix a bug in the sorting logic, present since version 3.8.4, that can cause output to appear in the wrong order on queries that contains an ORDER BY clause, a LIMIT clause, and that have approximately 60 or more columns in the result set. Ticket f97c4637102a3ae72b79.
  • SQLITE_SOURCE_ID: "2015-01-20 16:51:25 f73337e3e289915a76ca96e7a05a1a8d4e890d55"
  • SHA1 for sqlite3.c: 33987fb50dcc09f1429a653d6b47672f5a96f19e

New in SQLite 3.8.8 (Jan 18, 2015)

  • New Features:
  • Added the PRAGMA data_version command that can be used to determine if a database file has been modified by another process.
  • Added the SQLITE_CHECKPOINT_TRUNCATE option to the sqlite3_wal_checkpoint_v2() interface, with corresponding enhancements to PRAGMA wal_checkpoint.
  • Added the sqlite3_stmt_scanstatus() interface, available only when compiled with SQLITE_ENABLE_STMT_SCANSTATUS.
  • The sqlite3_table_column_metadata() is enhanced to work correctly on WITHOUT ROWID tables and to check for the existence of a a table if the column name parameter is NULL. The interface is now also included in the build by default, without requiring the SQLITE_ENABLE_COLUMN_METADATA compile-time option.
  • Added the SQLITE_ENABLE_API_ARMOR compile-time option.
  • Added the SQLITE_REVERSE_UNORDERED_SELECTS compile-time option.
  • Added the SQLITE_SORTER_PMASZ compile-time option and SQLITE_CONFIG_PMASZ start-time option.
  • Added the SQLITE_CONFIG_PCACHE_HDRSZ option to sqlite3_config() which makes it easier for applications to determine the appropriate amount of memory for use with SQLITE_CONFIG_PAGECACHE.
  • The number of rows in a VALUES clause is no longer limited by SQLITE_LIMIT_COMPOUND_SELECT.
  • Added the eval.c loadable extension that implements an eval() SQL function that will recursively evaluate SQL.
  • Performance Enhancements:
  • Reduce the number of memcpy() operations involved in balancing a b-tree, for 3.2% overall performance boost.
  • Improvements to cost estimates for the skip-scan optimization.
  • The automatic indexing optimization is now capable of generating a partial index if that is appropriate.
  • Bug fixes:
  • Ensure durability following a power loss with "PRAGMA journal_mode=TRUNCATE" by calling fsync() right after truncating the journal file.
  • The query planner now recognizes that any column in the right-hand table of a LEFT JOIN can be NULL, even if that column has a NOT NULL constraint. Avoid trying to optimize out NULL tests in those cases. Fix for ticket 6f2222d550f5b0ee7ed.
  • Make sure ORDER BY puts rows in ascending order even if the DISTINCT operator is implemented using a descending index. Fix for ticket c5ea805691bfc4204b1cb9e.
  • Fix data races that might occur under stress when running with many threads in shared cache mode where some of the threads are opening and closing connections.
  • Fix obscure crash bugs found by american fuzzy lop. Ticket a59ae93ee990a55.
  • Work around a GCC optimizer bug (for gcc 4.2.1 on MacOS 10.7) that caused the R-Tree extension to compute incorrect results when compiled with -O3.
  • Other changes:
  • Disable the use of the strchrnul() C-library routine unless it is specifically enabled using the -DHAVE_STRCHRNULL compile-time option.
  • Improvements to the effectiveness and accuracy of the likelihood(), likely(), and unlikely() SQL hint functions.
  • SQLITE_SOURCE_ID: "2015-01-16 12:08:06 7d68a42face3ab14ed88407d4331872f5b243fdf"
  • SHA1 for sqlite3.c: 91aea4cc722371d58aae3d22e94d2a4165276905

New in SQLite 3.8.7.4 (Dec 9, 2014)

  • This release fixes adds in a mutex that is required by the changes of the 3.8.7.3 patch but was accidently 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.

New in SQLite 3.8.7.3 (Dec 6, 2014)

  • Bug fix: Ensure the cached KeyInfo objects (an internal abstraction not visible to the application) do not go stale when operating in shared cache mode and frequently closing and reopening some database connections while leaving other database connections on the same shared cache open continuously. Ticket e4a18565a36884b00edf.
  • Bug fix: Recognize that any column in the right-hand table of a LEFT JOIN can be NULL even if the column has a NOT NULL constraint. Do not apply optimizations that assume the column is never NULL. Ticket 6f2222d550f5b0ee7ed.
  • SQLITE_SOURCE_ID: "2014-12-05 22:29:24 647e77e853e81a5effeb4c33477910400a67ba86"
  • SHA1 for sqlite3.c: 3ad2f5ba3a4a3e3e51a1dac9fda9224b359f0261

New in SQLite 3.8.7.2 (Nov 19, 2014)

  • 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.

New in SQLite 3.8.7.1 (Oct 30, 2014)

  • 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.

New in SQLite 3.8.7 (Oct 17, 2014)

  • 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.

New in SQLite 3.8.3.1 (Feb 12, 2014)

  • SQLite version 3.8.3.1 fixes a bug present in versions 3.8.1, 3.8.2 and 3.8.3 that can cause queries to omit valid out 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 NULL
  • Where 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.

New in SQLite 3.8.3 (Feb 4, 2014)

  • Added support for common table expressions and the WITH clause.
  • Added the printf() SQL function.
  • Added SQLITE_DETERMINISTIC as an optional bit in the 4th argument to the sqlite3_create_function() and related interfaces, providing applications with the ability to create new functions that can be factored out of inner loops when they have constant arguments.
  • Add SQLITE_READONLY_DBMOVED error code, returned at the beginning of a transaction, to indicate that the underlying database file has been renamed or moved out from under SQLite.
  • Allow arbitrary expressions, including function calls and subqueries, in the filename argument to ATTACH.
  • Allow a VALUES clause to be used anywhere a SELECT statement is valid.
  • Reseed the PRNG used by sqlite3_randomness(N,P) when invoked with N==0. Automatically reseed after a fork() on unix.
  • Enhance the spellfix1 virtual table so that it can search efficiently by rowid.
  • Performance enhancements.
  • Improvements to the comments in the VDBE byte-code display when running EXPLAIN.
  • Add the "%token_class" directive to LEMON parser generator and use it to simplify the grammar.
  • Change the LEMON source code to avoid calling C-library functions that OpenBSD considers dangerous. (Ex: sprintf).
  • Bug fix: In the command-line shell CSV import feature, do not end a field when an escaped double-quote occurs at the end of a CRLN line.
  • SQLITE_SOURCE_ID: "2014-02-03 13:52:03 e816dd924619db5f766de6df74ea2194f3e3b538"
  • SHA1 for sqlite3.c: 98a07da78f71b0275e8d9c510486877adc31dbee

New in SQLite 3.8.2 (Dec 6, 2013)

  • 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.

New in SQLite 3.8.1 (Oct 18, 2013)

  • SQLite version 3.8.1 is a regularly scheduled maintenance release. Upgrading from the previous release is optional, though you should upgrade if you are using partial indices as there was a bug related to partial indices in the previous release that could result in an incorrect answer for count(*) queries.
  • The next generation query planner that was premiered in the previous release continues to work well. The new query planner has been tweaked slightly in the current release to help it make better decisions in some cases, but is largely unchanged. Two new SQL functions, likelihood() and unlikely(), have been added to allow developers to give hints to the query planner without forcing the query planner into a particular decision.
  • Version 3.8.1 is the first SQLite release to take into account the estimated size of table and index rows when choosing a query plan. Row size estimates are based on the declared datatypes of columns. For example, a column of type VARCHAR(1000) is assumed to use much more space than a column of type INT. The datatype-based row size estimate can be overridden by appending a term of the form "sz=NNN" (where NNN is the average row size in bytes) to the end of the sqlite_stat1.stat record for a table or index. Currently, row sizes are only used to help the query planner choose between a table or one of its indices when doing a table scan or a count(*) operation, though future releases are likely to use the estimated row size in other contexts as well. The new PRAGMA stats statement can be used to view row size estimates.
  • Version 3.8.1 adds the SQLITE_ENABLE_STAT4 compile-time option. STAT4 is very similar to STAT3 in that it uses samples from indices to try to guess how many rows of the index will be satisfy by WHERE clause constraints. The difference is that STAT4 samples all columns of the index whereas the older STAT3 only sampled the left-most column. Users of STAT3 are encouraged to upgrade to STAT4. Application developers should use STAT3 and STAT4 with caution since both options, by design, violate the query planner stability guarantee, making it more difficult to ensure uniform performance is widely-deployed and mass-produced embedded applications.

New in SQLite 3.8.0.1 (Aug 31, 2013)

  • SQLite version 3.8.0.1 fixes some obscure bugs that were uncovered by users in the 3.8.0 release. Changes from 3.8.0 are minimal.

New in SQLite 3.8.0 (Aug 26, 2013)

  • Add support for partial indexes
  • Cut-over to the next generation query planner for faster and better query plans.
  • The EXPLAIN QUERY PLAN output no longer shows an estimate of the number of rows generated by each loop in a join.
  • Added the FTS4 notindexed option, allowing non-indexed columns in an FTS4 table.
  • Added the SQLITE_STMTSTATUS_VM_STEP option to sqlite3_stmt_status().
  • Added the cache_spill pragma.
  • Added the query_only pragma.
  • Added the defer_foreign_keys pragma and the sqlite3_db_status(db, SQLITE_DBSTATUS_DEFERRED_FKS,...) C-language interface.
  • Added the "percentile()" function as a loadable extension in the ext/misc subdirectory of the source tree.
  • Added the SQLITE_ALLOW_URI_AUTHORITY compile-time option.
  • Add the sqlite3_cancel_auto_extension(X) interface.
  • A running SELECT statement that lacks a FROM clause (or any other statement that never reads or writes from any database file) will not prevent a read transaction from closing.
  • Add the SQLITE_DEFAULT_AUTOMATIC_INDEX compile-time option. Setting this option to 0 disables automatic indices by default.
  • Issue an SQLITE_WARNING_AUTOINDEX warning on the SQLITE_CONFIG_LOG whenever the query planner uses an automatic index.
  • Added the SQLITE_FTS3_MAX_EXPR_DEPTH compile-time option.
  • Added an optional 5th parameter defining the collating sequence to the next_char() extension SQL function.
  • The SQLITE_BUSY_SNAPSHOT extended error code is returned in WAL mode when a read transaction cannot be upgraded to a write transaction because the read is on an older snapshot.
  • Enhancements to the sqlite3_analyzer utility program to provide size information separately for each individual index of a table, in addition to the aggregate size.
  • Allow read transactions to be freely opened and closed by SQL statements run from within the implementation of application-defined SQL functions if the function is called by a SELECT statement that does not access any database table.
  • Disable the use of posix_fallocate() on all (unix) systems unless the HAVE_POSIX_FALLOCATE compile-time option is used.
  • Update the ".import" command in the command-line shell to support multi-line fields and correct RFC-4180 quoting and to issue warning and/or error messages if the input text is not strictly RFC-4180 compliant.
  • Bug fix: In the unicode61 tokenizer of FTS4, treat all private code points as identifier symbols.
  • Bug fix: Bare identifiers in ORDER BY clauses bind more tightly to output column names, but identifiers in expressions bind more tightly to input column names. Identifiers in GROUP BY clauses always prefer output column names, however.
  • Bug fixes: Multiple problems in the legacy query optimizer were fixed by the move to NGQP.
  • SQLITE_SOURCE_ID: "2013-08-26 04:50:08 f64cd21e2e23ed7cff48f7dafa5e76adde9321c2"
  • SHA1 for sqlite3.c: b7347f4b4c2a840e6ba12040093d606bd16ea21e

New in SQLite 3.7.17 (May 20, 2013)

  • Add support for memory-mapped I/O.
  • Add the sqlite3_strglob() convenience interface.
  • Assigned the integer at offset 68 in the database header as the Application ID for when SQLite is used as an application file-format. Added the PRAGMA application_id command to query and set the Application ID.
  • Report rollback recovery in the error log as SQLITE_NOTICE_RECOVER_ROLLBACK. Change the error log code for WAL recover from SQLITE_OK to SQLITE_NOTICE_RECOVER_WAL.
  • Report the risky uses of unlinked database files and database filename aliasing as SQLITE_WARNING messages in the error log.
  • Added the SQLITE_TRACE_SIZE_LIMIT compile-time option.
  • Increase the default value of SQLITE_MAX_SCHEMA_RETRY to 50 and make sure that it is honored in every place that a schema change might force a statement retry.
  • Add a new test harness called "mptester" used to verify correct operation when multiple processes are using the same database file at the same time.
  • Enhance the extension loading mechanism to be more flexible (while still maintaining backwards compatibility) in two ways:
  • If the default entry point "sqlite3_extension_init" is not present in the loadable extension, also try an entry point "sqlite3_X_init" where "X" is based on the shared library filename. This allows every extension to have a different entry point, which allows them to be statically linked with no code changes.
  • The shared library filename passed to sqlite3_load_extension() may omit the filename suffix, and an appropriate architecture-dependent suffix (".so", ".dylib", or ".dll") will be added automatically.
  • Added many new loadable extensions to the source tree, including amatch, closure, fuzzer, ieee754, nextchar, regexp, spellfix, and wholenumber. See header comments on each extension source file for further information about what that extension does.
  • Enhance FTS3 to avoid using excess stack space when there are a huge number of terms on the right-hand side of the MATCH operator. A side-effect of this change is that the MATCH operator can only accommodate 12 NEAR operators at a time.
  • Enhance the fts4aux virtual table so that it can be a TEMP table.
  • Added the fts3tokenize virtual table to the full-text search logic.
  • Query planner enhancement: Use the transitive property of constraints to move constraints into the outer loops of a join whenever possible, thereby reducing the amount of work that needs to occur in inner loops.
  • Discontinue the use of posix_fallocate() on unix, as it does not work on all filesystems.
  • Improved tracing and debugging facilities in the Windows VFS.
  • Bug fix: Fix a potential database corruption bug in shared cache mode when one database connection is closed while another is in the middle of a write transaction. Ticket e636a050b7
  • Bug fix: Only consider AS names from the result set as candidates for resolving identifiers in the WHERE clause if there are no other matches. In the ORDER BY clause, AS names take priority over any column names. Ticket 2500cdb9be05
  • Bug fix: Do not allow a virtual table to cancel the ORDER BY clause unless all outer loops are guaranteed to return no more than one row result. Ticket ba82a4a41eac1.
  • Bug fix: Do not suppress the ORDER BY clause on a virtual table query if an IN constraint is used. Ticket f69b96e3076e.
  • Bug fix: The command-line shell gives an exit code of 0 when terminated using the ".quit" command.
  • Bug fix: Make sure PRAGMA statements appear in sqlite3_trace() output.
  • Bug fix: When a compound query that uses an ORDER BY clause with a COLLATE operator, make sure that the sorting occurs according to the specified collation and that the comparisons associate with the compound query use the native collation. Ticket 6709574d2a8d8.
  • Bug fix: Makes sure the authorizer callback gets a valid pointer to the string "ROWID" for the column-name parameter when doing an UPDATE that changes the rowid. Ticket 0eb70d77cb05bb2272
  • Bug fix: Do not move WHERE clause terms inside OR expressions that are contained within an ON clause of a LEFT JOIN. Ticket f2369304e4
  • Bug fix: Make sure an error is always reported when attempting to preform an operation that requires a collating sequence that is missing. Ticket 0fc59f908b
  • SQLITE_SOURCE_ID: "2013-05-20 00:56:22 118a3b35693b134d56ebd780123b7fd6f1497668"
  • SHA1 for sqlite3.c: 246987605d0503c700a08b9ee99a6b5d67454aab

New in SQLite 3.7.15.2 (Jan 10, 2013)

  • SQLite version 3.7.15.2 is a patch release that fixes a single bug that was introduced in version version 3.7.15. The fix is a 4-character edit to a single line of code. Other than this 4-character change and the update of the version number, nothing has changed from version 3.7.15.1.

New in SQLite 3.7.14.1 (Oct 5, 2012)

  • Fix a bug (ticket [d02e1406a58ea02d]]) that causes a segfault on a LEFT JOIN that includes an OR in the ON clause.
  • Work around a bug in the optimizer in the VisualStudio-2012 compiler that causes invalid code to be generated when compiling SQLite on ARM.
  • Fix the TCL interface so that the "nullvalue" setting is honored for TCL implementations of SQL functions.
  • SQLITE_SOURCE_ID: "2012-10-04 19:37:12 091570e46d04e84b67228e0bdbcd6e1fb60c6bdb"
  • SHA1 for sqlite3.c: 62aaecaacab3a4bf4a8fe4aec1cfdc1571fe9a44

New in SQLite 3.7.12.1 (May 23, 2012)

  • Fix a bug (ticket c2ad16f997) in the 3.7.12 release that can cause a segfault for certain obscure nested aggregate queries.
  • Fix various other minor test script problems.
  • SQLITE_SOURCE_ID: "2012-05-22 02:45:53 6d326d44fd1d626aae0e8456e5fa2049f1ce0789"
  • SHA1 for sqlite3.c: d494e8d81607f0515d4f386156fb0fd86d5ba7df

New in SQLite 3.7.10 (Jan 17, 2012)

  • The SQLITE_CONFIG_PCACHE mechanism has been replaced with SQLITE_CONFIG_PCACHE2. If you do not know what this mechanism is (it is an extreme corner-case and is seldom used) then this change will not effect you in the least.
  • The default schema format number for new database files has changed from 1 to 4. SQLite has been able to generate and read database files using schema format 4 for six years. But up unto now, the default schema format has been 1 so that older versions of SQLite could read and write databases generated by newer versions of SQLite. But those older versions of SQLite have become so scarce now that it seems reasonable to make the new format the default.
  • SQLite is changing some of the assumptions it makes above the behavior of disk drives and flash memory devices during a sudden power loss. This change is completely transparent to applications. Read about the powersafe overwrite property for additional information.
  • Lots of new interfaces have been added in this release:
  • sqlite3_db_release_memory()
  • PRAGMA shrink_memory
  • sqlite3_db_filename()
  • sqlite3_stmt_busy()
  • sqlite3_uri_boolean()
  • sqlite3_uri_int64()
  • The PRAGMA cache_size statement has been enhanced. Formerly, you would use this statement to tell SQLite how many pages of the database files it should hold in its cache at once. The total memory requirement would depend on the database page size. Now, if you give PRAGMA cache_size a negative value -N, it will allocate roughly N kibibytes of memory to cache, divided up according to page size. This enhancement allows programs to more easily control their memory usage.
  • There have been several obscure bug fixes. One noteworthy bug, ticket ff5be73dee, could in theory result in a corrupt database file if a power loss occurred at just the wrong moment on an unusually cantankerous disk drive. But that is mostly a theoretical concern and is very unlikely to happen in practice. The bug was found during laboratory testing and has never been observed to occur in the wild.

New in SQLite 3.7.7 (Jun 24, 2011)

  • Add support for URI filenames
  • Add the sqlite3_vtab_config() interface in support of ON CONFLICT clauses with virtual tables.
  • Add the xSavepoint, xRelease and xRollbackTo methods in virtual tables in support of SAVEPOINT for virtual tables.
  • Update the built-in FTS3/FTS4 and RTREE virtual tables to support ON CONFLICT clauses and REPLACE.
  • Avoid unnecessary reparsing of the database schema.
  • Added support for the FTS4 prefix option and the FTS4 order option.
  • Allow WAL-mode databases to be opened read-only as long as there is an existing read/write connection.
  • Added support for short filenames.
  • SQLITE_SOURCE_ID: "2011-06-23 19:49:22 4374b7e83ea0a3fbc3691f9c0c936272862f32f2"
  • SHA1 for sqlite3.c: 5bbe79e206ae5ffeeca760dbd0d66862228db551

New in SQLite 3.7.6.1 (Apr 15, 2011)

  • Fix a bug in 3.7.6 that only appears if the SQLITE_FCNTL_SIZE_HINT file control is used with a build of SQLite that makes use of the HAVE_POSIX_FALLOCATE compile-time option and which has SQLITE_ENABLE_LOCKING_MODE turned off.
  • SQLITE_SOURCE_ID: "2011-04-13 14:40:25 a35e83eac7b185f4d363d7fa51677f2fdfa27695"
  • SHA1 for sqlite3.c: b81bfa27d3e09caf3251475863b1ce6dd9f6ab66

New in SQLite 3.7.5 (Feb 1, 2011)

  • Added the sqlite3_vsnprintf() interface.
  • Added the SQLITE_DBSTATUS_LOOKASIDE_HIT, SQLITE_DBSTATUS_LOOKASIDE_MISS_SIZE, and SQLITE_DBSTATUS_LOOKASIDE_MISS_FULL options for the sqlite3_db_status() interface.
  • Added the SQLITE_OMIT_AUTORESET compile-time option.
  • Added the SQLITE_DEFAULT_FOREIGN_KEYS compile-time option.
  • Updates to sqlite3_stmt_readonly() so that its result is well-defined for all prepared statements and so that it works with VACUUM.
  • Added the "-heap" option to the command-line shell
  • Fix a bug involving frequent changes in and out of WAL mode and VACUUM that could (in theory) cause database corruption.
  • Enhance the sqlite3_trace() mechanism so that nested SQL statements such as might be generated by virtual tables are shown but are shown in comments and without parameter expansion. This greatly improves tracing output when using the FTS3/4 and/or RTREE virtual tables.
  • Change the xFileControl() methods on all built-in VFSes to return SQLITE_NOTFOUND instead of SQLITE_ERROR for an unrecognized operation code.
  • The SQLite core invokes the SQLITE_FCNTL_SYNC_OMITTED file control to the VFS in place of a call to xSync if the database has PRAGMA synchronous set to OFF.

New in SQLite 3.7.4 (Dec 8, 2010)

  • Added the sqlite3_blob_reopen() interface to allow an existing sqlite3_blob object to be rebound to a new row.
  • Use the new sqlite3_blob_reopen() interface to improve the performance of FTS.
  • VFSes that do not support shared memory are allowed to access WAL databases if PRAGMA locking_mode is set to EXCLUSIVE.
  • Enhancements to EXPLAIN QUERY PLAN.
  • Added the sqlite3_stmt_readonly() interface.
  • Added PRAGMA checkpoint_fullfsync.
  • Added the SQLITE_FCNTL_FILE_POINTER option to sqlite3_file_control().
  • Added support for FTS4 and enhancements to the FTS matchinfo() function.
  • Added the test_superlock.c module which provides example code for obtaining an exclusive lock to a rollback or WAL database.
  • Added the test_multiplex.c module which provides an example VFS that provides multiplexing (sharding) of a DB, splitting it over multiple files of fixed size.
  • A very obscure bug associated with the or optimization was fixed.

New in SQLite 3.7.2 (Sep 14, 2010)

  • Fix an old and very obscure bug that can lead to corruption of the database free-page list when incremental_vacuum is used.

New in SQLite 3.7.0 (Jul 22, 2010)

  • Added support for write-ahead logging.
  • Query planner enhancement - automatic transient indices are created when doing so reduces the estimated query time.
  • Query planner enhancement - the ORDER BY becomes a no-op if the query also contains a GROUP BY clause that forces the correct output order.
  • Add the SQLITE_DBSTATUS_CACHE_USED verb for sqlite3_db_status().
  • The logical database size is now stored in the database header so that bytes can be appended to the end of the database file without corrupting it and so that SQLite will work correctly on systems that lack support for ftruncate().

New in SQLite 3.6.23.1 (Mar 30, 2010)

  • SQLite version 3.6.23.1 is a patch release to fix a bug in the offsets() function of FTS3 at the request of the Mozilla.

New in SQLite 3.6.23 (Mar 10, 2010)

  • Added the secure_delete pragma.
  • Added the sqlite3_compileoption_used() and sqlite3_compileoption_get() interfaces as well as the compile_options pragma and the sqlite_compileoption_used() and sqlite_compileoption_get() SQL functions.
  • Added the sqlite3_log() interface together with the SQLITE_CONFIG_LOG verb to sqlite3_config(). The ".log" command is added to the Command Line Interface.
  • Improvements to FTS3.
  • Improvements and bug-fixes in support for SQLITE_OMIT_FLOATING_POINT.
  • The integrity_check pragma is enhanced to detect out-of-order rowids.
  • The ".genfkey" operator has been removed from the Command Line Interface.
  • Updates to the co-hosted Lemon LALR(1) parser generator. (These updates did not effect SQLite.)
  • Various minor bug fixes and performance enhancements.