Documentation Source Text

<title>Release History Of SQLite</title>
<tcl>hd_keywords {release history} {complete list of SQLite releases}</tcl>
<h1 align=center>Release History</h1>

<p>
This page provides a high-level summary of changes to SQLite.
For more detail, see the Fossil checkin logs at
<a href="http://www.sqlite.org/src/timeline">
http://www.sqlite.org/src/timeline</a> and
<a href="http://www.sqlite.org/src/timeline?t=release">
http://www.sqlite.org/src/timeline?t=release</a>.
See the [chronology] a succinct listing of releases.
</p>

<tcl>
set nChng 0
proc chng {date desc {options {}}} {
  global nChng aChng
  set aChng($nChng) [list $date $desc $options]

chng {2000-09-21 (Not Released)} {
<li>Change the tclsqlite "eval" method to return a list of results if
    no callback script is specified.</li>
<li>Change tclsqlite.c to use the Tcl_Obj interface</li>
<li>Add tclsqlite.c to the libsqlite.a library</li>
}

chng {2000-09-13 (1.0.5)} {
<li>Changed the print format for floating point values from "%g" to "%.15g".
    </li>
<li>Changed the comparison function so that numbers in exponential notation
    (ex: 1.234e+05) sort in numerical order.</li>
}

chng {2000-08-28 (1.0.4)} {
<li>Added functions <b>length()</b> and <b>substr()</b>.</li>
<li>Fix a bug in the <b>sqlite</b> shell program that was causing
    a coredump when the output mode was "column" and the first row
    of data contained a NULL.</li>
}

chng {2000-08-22 (1.0.3)} {
<li>In the sqlite shell, print the "Database opened READ ONLY" message
    to stderr instead of stdout.</li>
<li>In the sqlite shell, now print the version number on initial startup.</li>
<li>Add the <b>sqlite_version&#91;&#93;</b> string constant to the library</li>
<li>Makefile updates</li>
<li>Bug fix: incorrect VDBE code was being generated for the following
    circumstance: a query on an indexed table containing a WHERE clause with
    an IN operator that had a subquery on its right-hand side.</li>
}

chng {2000-08-18 (1.0.1)} {
<li>Fix a bug in the configure script.</li>
<li>Minor revisions to the website.</li>
}

chng {2000-08-17 (1.0)} {
<li>Change the <b>sqlite</b> program so that it can read
    databases for which it lacks write permission.  (It used to
    refuse all access if it could not write.)</li>
}

chng {2000-08-09} {
<li>Treat carriage returns as white space.</li>

        regexp {([-0-9]+) \((3\.\d+\.[.0-9]+)[ a-zA-Z]*\)} [lindex $c2 0] all date vers
        set d2 [lindex $c2 1]
        regsub {(<p>[^\n]*\n)?<li>SQLITE_SOURCE_ID.*$} $d2 {} d2
        hd_resolve "<p><b>Prior changes from version $vers ($date):</b></p>"
        hd_resolve "<p><ul>$d2</ul></p>"
      }
    }
    hd_resolve {
      <p>A [complete list of SQLite releases]
      in a single page and a [chronology] are both also available.  
      A detailed history of every
      check-in is available at
      <a href="http://www.sqlite.org/src/timeline">
      SQLite version control site</a>.</p>
    }
    hd_close_aux
    hd_enable_main 1
    if {$i==0 && [file exists $DEST/$filename]} {
      file copy -force $DEST/$filename $DEST/releaselog/current.html
    }
  }
}

</tcl>
</dl>

<title>Chronology Of SQLite Releases</title>
<tcl>hd_keywords {chronology}</tcl>
<h1 align=center>Chronology Of SQLite Releases</h1>

<p>
The following is a listing of all historical releases of
SQLite, by date.  See the [release history] for more
details about each release.
</p>

<table border=0>
<tcl>
set chnglog_fn [file dir $::infile]/changes.in
set chnglog_in [open $chnglog_fn]
while {![eof $chnglog_in]} {
  set line [gets $chnglog_in]
  if {![regexp {^chng } $line]} continue
  regsub -all {[{}()]} $line {} line
  set date [lindex $line 1]
  set vers [lindex $line 2]
  if {![regexp {^\d\d\d\d-\d\d-\d\d$} $date]} continue
  if {![regexp {^\d\.\d} $vers]} continue
  set url https://www.sqlite.org/src/timeline
  append url ?ymd=$date&y=ci
  hd_puts "<tr><td><a href='$url'>$date</a></td>\n"
  hd_puts "<td width='20'></td>\n"
  if {[string index $vers 0]=="3"} {
    hd_resolve "<td>\[version $vers|$vers\]</td></tr>\n"
  } else {
    hd_puts "<td>$vers</td></tr>\n"
  }
}
close $chnglog_in
</tcl>
</table>

<title>Most Widely Deployed SQL Database Engine</title>
<tcl>hd_keywords {most widely deployed}</tcl>

<h2>Most Widely Deployed and Used Database Engine</h2>

<p>SQLite is likely used more than all other database engines
combined.  Billions and billions of copies
of SQLite exist in the wild.  SQLite is found in:

<ul>
<li>Every Android device
<li>Every iPhone and iOS device
<li>Every Mac
<li>Every Windows10 machine
<li>Every Firefox, Chrome, and Safari web browser
<li>Every instance of Skype
<li>Every instance of iTunes
<li>Every Dropbox client
<li>Every TurboTax and QuickBooks
<li>PHP and Python
<li>Most television sets and set-top cable boxes
<li>Most automotive multimedia systems
<li>Countless millions of other applications
</ul>

<h2>Most Widely Deployed Software Module of Any Type?</h2>

<p>SQLite is probably one of the top five most deployed
software modules of any description.  
Other libraries with similar reach include:

<ul>
<li>
The original <a href="http://zlib.net">zlib</a> implementation by
Jean-loup Gailly and Mark Adler.
<li>
The original reference implementation for
<a href="http://www.libpng.org">libpng</a>
<li>
Libjpeg from the <a href="http://ijg.org">Independent JPEG Group</a>
</ul>








<p>
Libc is omitted from the above list even though
it is more common than SQLite because
libc is not a single software component but rather several
competing implementations (ex: BSD vs. GNU) with similar interfaces.
There are also independent implementations of libjpeg and libpng,
though in those cases the canonical implementations are very popular.
</p>

<p>
Precise numbers are difficult to obtain and so exact rankings
are impossible.  But our best guess is that SQLite is the second
mostly widely deployed software library, after libz.
Some commentators observe that SQLite tends
to be statically linked and thus have multiple instances on
each machine, whereas libz tend sto have just
a single instance per machine in the form of a shared library or DLL.
So even though the number of devices containing libz
may be greater than the number of
devices that contain SQLite, the total number of instances per device
<em>might</em> be higher for SQLite and so SQLite <em>might</em>
be the single most widely deployed and used software component.
</p>

  hd_puts "<h3>$date - $title</h3>"
  regsub -all "\n( *\n)+" $text "</p>\n\n<p>" txt
  regsub -all {[Tt]icket #(\d+)} $txt \
      {<a href="http://www.sqlite.org/cvstrac/tktview?tn=\1">\0</a>} txt
  hd_resolve "<blockquote>$txt</blockquote>"
  hd_puts "<hr width=\"50%\">"
}

newsitem {2015-10-19} {Release 3.9.0} {
<p>SQLite version 3.9.0 is a regularly schedule maintenance release.
Key changes include:
<ul>
<li>Begin using [http://semver.org/|semantic versioning].
<li>[json1|JSON SQL functions]
<li>The [FTS5] full-text search engine
<li>Support for [indexes on expressions]
<li>Support for [table-valued functions]
</ul>
See the [version 3.9.0|change log] for a long and more complete list
of changes.
}

newsitem {2015-07-29} {Release 3.8.11.1} {
<p>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.
}

   Several obscure bugs have been fixed, including some multithreading
   races and a work-around for a compiler bug on some Macs.

<p>See the [version 3.8.8|change log] for a longer list of
   enhancements and bug fixes.
}


























































































































































































































































































</tcl>

<a href="oldnews.html">Old news...</a>

<?xml version="1.0" encoding="Windows-1252"?>
<title>SQLite Older News</title>

<tcl>
proc newsitem {date title text} {
  regsub -all {[^a-z0-9]} $date _ tag
  hd_puts "<a name=\"$tag\"></a>"
  hd_puts "<h3>$date - $title</h3>"
  regsub -all "\n( *\n)+" $text "</p>\n\n<p>" txt
  hd_resolve "<blockquote>$txt</blockquote>"
  hd_puts "<hr width=\"50%\">"
}


newsitem {2014-12-09} {Release 3.8.7.4} {
<p>SQLite [version 3.8.7.4] an unscheduled bug-fix release.  Changes from
   the previous release and from [version 3.8.7] are minimal.

<p>This release fixes adds in a mutex that is required by the changes of
   the 3.8.7.3 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.
}

newsitem {2014-12-06} {Release 3.8.7.3} {
<p>SQLite [version 3.8.7.3] an unscheduled bug-fix release.  Changes from
   the previous release and from [version 3.8.7] are minimal.

<p>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.
}

newsitem {2014-11-19} {Release 3.8.7.2} {
<p>SQLite [version 3.8.7.2] is a patch and bug-fix release.  Changes from
   the previous release are minimal.

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

<p>In addition to the ROLLBACK enhancement, this patch release also
   includes fixes for three obscure bugs.  See the 
   [version 3.8.7.2|change log] for details.
}

newsitem {2014-10-30} {Release 3.8.7.1} {
<p>SQLite [version 3.8.7.1] is a bug-fix release.

<p>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|ALTER TABLE ADD COLUMN].  This problem
   [https://www.sqlite.org/src/info/43107840f1c02|1] first appeared in the
   3.8.7 release.

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

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

<p>Finally, a couple of long-standing and obscure problems associated with run
   UPDATE and DELETE on VIEWs were fixed.

<p>The [https://www.sqlite.org/src/vdiff?from=e4ab094f8afce0817f4074e823fabe59fc29ebb4&to=83afe23e553e802c0947c80d0ffdd120423e7c52&sbs=1|changes from 3.8.7] are minimal.
}

newsitem {2014-10-17} {Release 3.8.7} {
<p>SQLite [version 3.8.7] is a regularly scheduled maintenance release.
   Upgrading from all prior versions is recommended.

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

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

<p>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.
}

newsitem {2014-08-15} {Release 3.8.6} {
<p>SQLite [version 3.8.6] is a regularly scheduled maintenance release.
   Upgrading from all previous versions is recommended.

<p>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
   [http://www.sqlite.org/src/info/9a6daf340df99ba93c|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.

<p>Other noteworthy changes include the addition of support for
   [hexadecimal integers] (ex: 0x1234), and performance enhancements
   to the [IN operator] which, according to 
   [http://www.mail-archive.com/sqlite-users%40sqlite.org/msg85350.html|mailing list reports],
   help some queries run up to five times faster.

<p>Version 3.8.6 uses 25% fewer CPU cycles than version 3.8.0 from
   approximately one year ago, according to [http://valgrind.org/|valgrind]
   and the
   [http://www.sqlite.org/src/artifact/d29c8048beb7e|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].
}

newsitem {2014-06-04} {Release 3.8.5} {
<p>SQLite [version 3.8.5] is a regularly scheduled maintenance release.
   Upgrading from the previous version is recommended.

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

<p>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.
}

newsitem {2014-04-03} {Release 3.8.4.3} {
<p>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
   [http://www.sqlite.org/src/info/98825a79ce145686392d8074032ae54863aa21a3| ticket 98825a79ce145]
   for details.
   This release adds a 
   [http://www.sqlite.org/src/fdiff?sbs=1&v1=7d539cedb1c&v2=ebad891b7494d&smhdr|one-character change]
   to a single line of code to fix the problem.
}

newsitem {2014-03-26} {Release 3.8.4.2} {
<p>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 3.8.4.2] fixes that
   problem using a
   [http://www.sqlite.org/src/fdiff?v1=e45e3f9daf38c5be&v2=714df4e1c82f629d&sbs=1|one-line patch].

<p>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.
}

newsitem {2014-03-11} {Release 3.8.4.1} {
<p>SQLite [version 3.8.4.1] is a patch against [version 3.8.4] that fixes
   two minor issues:
<ol>
<li> Work around a C-preprocessor macro conflict that causes compilation
     problems for some configurations of Visual Studio.
<li> Adjust the cost computation for the [skip-scan optimization] for
     improved performance.
</ol>
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.
}

newsitem {2014-03-10} {Release 3.8.4} {
<p>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.

<p>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.
}

newsitem {2014-02-11} {Release 3.8.3.1} {
<p>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 output rows.
   Upgrading from those versions is recommended.
   
<p>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:
   <blockquote>
WHERE (expr1 OR expr2 OR ... OR exprN) AND column IS NOT NULL
   </blockquote>
   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.

<p>The trouble ticket for this bug is
   &#91;[http://www.sqlite.org/src/info/4c86b126f2|4c86b126f2]&#93;.
   It is recommended that all users upgrade to avoid this problem.
}

newsitem {2014-02-03} {Release 3.8.3} {
  <p>SQLite [version 3.8.3] is a regularly scheduled maintenance release.
  Upgrading from the previous release is optional.

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

  <p>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 [version 3.8.3|change log] for details.
}

newsitem {2013-12-06} {Release 3.8.2} {
  <p>SQLite [version 3.8.2] is a regularly scheduled maintenance release.
  Upgrading from the previous release is optional.

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

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

  <p>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.
}

newsitem {2013-10-17} {Release 3.8.1} {
  <p>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 
  [http://www.sqlite.org/src/info/a5c8ed66ca|bug] related to partial
  indices in the previous release that could result in an incorrect answer
  for count(*) queries.