Documentation Source Text

set nChng 0
proc chng {date desc {options {}}} {
  global nChng aChng xrefChng
  set aChng($nChng) [list $date $desc $options]
  set xrefChng($date) $nChng
  incr nChng
}



















chng {2017-08-01 (3.20.0)} {
<li> Update the text of error messages returned by [sqlite3_errmsg()] for some
     error codes.
<li> Add new [pointer passing interfaces].
<li> Backwards-incompatible changes to some extensions in order to take 
     advantage of the improved security offered by the new 

set nChng 0
proc chng {date desc {options {}}} {
  global nChng aChng xrefChng
  set aChng($nChng) [list $date $desc $options]
  set xrefChng($date) $nChng
  incr nChng
}

chng {2017-11-01 (3.21.0)} {
<li> Take advantage of the atomic-write capabilities of the 
     [https://en.wikipedia.org/wiki/F2FS|F2FS filesystem] when available, for
     greatly reduced transaction overhead.  This currently requires the
     [SQLITE_ENABLE_BATCH_ATOMIC_WRITE] compile-time option.
<li> Allow [ATTACH] and [DETACH] commands to work inside of a transaction.
<li> Query planner enhancements:
<ol type="a">
<li> Enhanced the [LIKE optimization] so that it works with an ESCAPE clause.
</ol>
<li> Miscellaneous [microoptimizations] reduce CPU usage by about 0.4%.
<li> Bug fixes:
<ol type="a">
<li> Fix a faulty assert() statement discovered by OSSFuzz.
     Ticket [https://sqlite.org/src/info/cb91bf4290c211d|cb91bf4290c211d]
</ol>
}

chng {2017-08-01 (3.20.0)} {
<li> Update the text of error messages returned by [sqlite3_errmsg()] for some
     error codes.
<li> Add new [pointer passing interfaces].
<li> Backwards-incompatible changes to some extensions in order to take 
     advantage of the improved security offered by the new 

#    ORDER BY mtime DESC;
#
# A small amount of manual editing and de-duplication followed.
#
# Manually edit the list for each subsequent release.
#      
foreach line [split {

9501e22dfe|2017-08-01|Version 3.20.0
036ebf729e|2017-06-17|Version 3.18.2
77bb46233d|2017-06-16|Version 3.18.1
0ee482a1e0|2017-06-08|Version 3.19.3
edb4e819b0|2017-05-25|Version 3.19.2
f6d7b988f4|2017-05-24|Version 3.19.1
28a94eb282|2017-05-22|Version 3.19.0

#    ORDER BY mtime DESC;
#
# A small amount of manual editing and de-duplication followed.
#
# Manually edit the list for each subsequent release.
#      
foreach line [split {
xxxxxxxxxx|pending|Version 3.21.0
9501e22dfe|2017-08-01|Version 3.20.0
036ebf729e|2017-06-17|Version 3.18.2
77bb46233d|2017-06-16|Version 3.18.1
0ee482a1e0|2017-06-08|Version 3.19.3
edb4e819b0|2017-05-25|Version 3.19.2
f6d7b988f4|2017-05-24|Version 3.19.1
28a94eb282|2017-05-22|Version 3.19.0

  or written.  On filesystems that support atomic writes, this
  optimization can result in significant speed improvements for
  small updates.  However, few filesystems support this capability
  and the code paths that check for this capability slow down write
  performance on systems that lack atomic write capability, so this
  feature is disabled by default.
}




















COMPILE_OPTION {SQLITE_ENABLE_COLUMN_METADATA} {
  When this C-preprocessor macro is defined, SQLite includes some
  additional APIs that provide convenient access to meta-data about
  tables and queries.  The APIs that are enabled by this option are:

  <ul>

  or written.  On filesystems that support atomic writes, this
  optimization can result in significant speed improvements for
  small updates.  However, few filesystems support this capability
  and the code paths that check for this capability slow down write
  performance on systems that lack atomic write capability, so this
  feature is disabled by default.
}

COMPILE_OPTION {SQLITE_ENABLE_BATCH_ATOMIC_WRITE} {
  This compile-time option enables SQLite to take advantage batch
  atomic write capabilities in the underlying filesystem.  As of
  SQLite version 3.21.0 ([dateof:3.21.0]) this is only supported on
  [https://en.wikipedia.org/wiki/F2FS|F2FS].  However, the interface
  is implemented generically, using [sqlite3_file_control()] with
  [SQLITE_FCNTL_BEGIN_ATOMIC_WRITE] and [SQLITE_FCNTL_COMMIT_ATOMIC_WRITE]
  so the capability can be added to other filesystem times in the
  future.  When this option is enabled, SQLite automatically detects
  that the underlying filesystem supports batch atomic writes, and
  when it does so it avoids writing the [rollback journal] for transaction
  control.  This can make transactions over twice as fast, while
  simultaneously reducing wear on SSD storage devices.
<p>
  Future versions of SQLite might enable the batch-atomic-write
  capability by default, at which point this compile-time option
  will become superfluous.
}

COMPILE_OPTION {SQLITE_ENABLE_COLUMN_METADATA} {
  When this C-preprocessor macro is defined, SQLite includes some
  additional APIs that provide convenient access to meta-data about
  tables and queries.  The APIs that are enabled by this option are:

  <ul>

[full-featured SQL|full-featured], [public-domain],
SQL database engine.
SQLite is the [most used] database engine in the world.
<a class="button" href="about.html">More Info</a></p>

<hr class="xhr">
<span class="hdrfont">Latest Release:&nbsp;&nbsp;</span>
<a href="releaselog/3_20_0.html">Version 3.20.0</a> ([dateof:3.20.0]).
<a class="button" href="download.html">Download</a>
<a class="button" href="chronology.html">Prior Releases</a>

<div class="mobileonly">
<hr class="xhr">
<h3>Common Links</h3>
<tcl>common_links</tcl>

[full-featured SQL|full-featured], [public-domain],
SQL database engine.
SQLite is the [most used] database engine in the world.
<a class="button" href="about.html">More Info</a></p>

<hr class="xhr">
<span class="hdrfont">Latest Release:&nbsp;&nbsp;</span>
<a href="releaselog/3_21_0.html">Version 3.21.0</a> ([dateof:3.21.0]).
<a class="button" href="download.html">Download</a>
<a class="button" href="chronology.html">Prior Releases</a>

<div class="mobileonly">
<hr class="xhr">
<h3>Common Links</h3>
<tcl>common_links</tcl>

^When not in [shared cache mode], 
it is possible to have the same database file attached multiple times using 
different names, and detaching one connection to a file will leave the 
others intact.</p>
^In [shared cache mode], attempting to attach the same database file more
than once results in an error.

<p>^This statement will fail if SQLite is in the middle of a transaction.</p>


<tcl>
##############################################################################
Section {DROP INDEX} dropindex {{DROP INDEX}}

RecursiveBubbleDiagram drop-index-stmt
</tcl>

^When not in [shared cache mode], 
it is possible to have the same database file attached multiple times using 
different names, and detaching one connection to a file will leave the 
others intact.</p>
^In [shared cache mode], attempting to attach the same database file more
than once results in an error.




<tcl>
##############################################################################
Section {DROP INDEX} dropindex {{DROP INDEX}}

RecursiveBubbleDiagram drop-index-stmt
</tcl>

  There are many conditions on this optimization:
}
PARAGRAPH {
  <ol>
  <li>^The right-hand side of the LIKE or GLOB must be either a string literal
      or a [parameter] bound to a string literal
      that does not begin with a wildcard character.</li>
  <li>^The ESCAPE clause cannot appear on the LIKE operator.</li>
  <li>It must not be possible to make the LIKE or GLOB operator true by
      having a numeric value (instead of a string or blob) on the
      left-hand side. This means that either:
      <ol type="A">
      <li> the left-hand side of the LIKE or GLOB operator is the name
           of an indexed column with [affinity | TEXT affinity], or</li>
      <li> the right-hand side pattern argument does not begin with a
           minus sign ("-") or a digit.</li>
      </ol>
      This constraint arises from the fact that numbers do not sort in
      lexicographical order.  For example: 9&lt;10 but '9'&gt;'10'.</li>
  <li>^The built-in functions used to implement LIKE and GLOB must not
      have been overloaded using the sqlite3_create_function() API.</li>
  <li>^For the GLOB operator, the column must be indexed using the 
      built-in BINARY collating sequence.</li>
  <li>^For the LIKE operator, if [case_sensitive_like] mode is enabled then
      the column must indexed using BINARY collating sequence, or if
      [case_sensitive_like] mode is disabled then the column must indexed
      using built-in NOCASE collating sequence.</li>


  </ol>
}
PARAGRAPH {
  The LIKE operator has two modes that can be set by a
  [case_sensitive_like | pragma].  ^The
  default mode is for LIKE comparisons to be insensitive to differences
  of case for latin1 characters.  ^(Thus, by default, the following
................................................................................
  character then this optimization is attempted.  ^If the right-hand side 
  is a [parameter] that is bound to a string, then this optimization is
  only attempted if the [prepared statement] containing the expression
  was compiled with [sqlite3_prepare_v2()] or [sqlite3_prepare16_v2()].
  ^The LIKE optimization is not attempted if the
  right-hand side is a [parameter] and the statement was prepared using
  [sqlite3_prepare()] or [sqlite3_prepare16()].
  ^The LIKE optimization is not attempted if there is an ESCAPE phrase
  on the LIKE operator.
}
PARAGRAPH {
  Suppose the initial sequence of non-wildcard characters on the right-hand
  side of the LIKE or GLOB operator is <i>x</i>.  We are using a single 
  character to denote this non-wildcard prefix but the reader should
  understand that the prefix can consist of more than 1 character.
  Let <i>y</i> be the smallest string that is the same length as /x/ but which
................................................................................
  started.  This results in a slightly faster sort.  But the bigger
  advantages are that many fewer rows need to be held in memory,
  reducing memory requirements, and outputs can begin to appear before
  the core query has run to completion.
}

HEADING 1 {Subquery flattening} flattening
hd_keywords {flattening optimization}

PARAGRAPH {
  When a subquery occurs in the FROM clause of a SELECT, the simplest
  behavior is to evaluate the subquery into a transient table, then run
  the outer SELECT against the transient table.  But such a plan
  can be suboptimal since the transient table will not have any indices
  and the outer query (which is likely a join) will be forced to do a

  There are many conditions on this optimization:
}
PARAGRAPH {
  <ol>
  <li>^The right-hand side of the LIKE or GLOB must be either a string literal
      or a [parameter] bound to a string literal
      that does not begin with a wildcard character.</li>

  <li>It must not be possible to make the LIKE or GLOB operator true by
      having a numeric value (instead of a string or blob) on the
      left-hand side. This means that either:
      <ol type="A">
      <li> the left-hand side of the LIKE or GLOB operator is the name
           of an indexed column with [affinity | TEXT affinity], or</li>
      <li> the right-hand side pattern argument does not begin with a
           minus sign ("-") or a digit.</li>
      </ol>
      This constraint arises from the fact that numbers do not sort in
      lexicographical order.  For example: 9&lt;10 but '9'&gt;'10'.</li>
  <li>^The built-in functions used to implement LIKE and GLOB must not
      have been overloaded using the [sqlite3_create_function()] API.</li>
  <li>^For the GLOB operator, the column must be indexed using the 
      built-in BINARY collating sequence.</li>
  <li>^For the LIKE operator, if [case_sensitive_like] mode is enabled then
      the column must indexed using BINARY collating sequence, or if
      [case_sensitive_like] mode is disabled then the column must indexed
      using built-in NOCASE collating sequence.</li>
  <li>If the ESCAPE option is used, the ESCAPE character must be ASCII,
      or a single-byte character in UTF-8.
  </ol>
}
PARAGRAPH {
  The LIKE operator has two modes that can be set by a
  [case_sensitive_like | pragma].  ^The
  default mode is for LIKE comparisons to be insensitive to differences
  of case for latin1 characters.  ^(Thus, by default, the following
................................................................................
  character then this optimization is attempted.  ^If the right-hand side 
  is a [parameter] that is bound to a string, then this optimization is
  only attempted if the [prepared statement] containing the expression
  was compiled with [sqlite3_prepare_v2()] or [sqlite3_prepare16_v2()].
  ^The LIKE optimization is not attempted if the
  right-hand side is a [parameter] and the statement was prepared using
  [sqlite3_prepare()] or [sqlite3_prepare16()].


}
PARAGRAPH {
  Suppose the initial sequence of non-wildcard characters on the right-hand
  side of the LIKE or GLOB operator is <i>x</i>.  We are using a single 
  character to denote this non-wildcard prefix but the reader should
  understand that the prefix can consist of more than 1 character.
  Let <i>y</i> be the smallest string that is the same length as /x/ but which
................................................................................
  started.  This results in a slightly faster sort.  But the bigger
  advantages are that many fewer rows need to be held in memory,
  reducing memory requirements, and outputs can begin to appear before
  the core query has run to completion.
}

HEADING 1 {Subquery flattening} flattening
hd_keywords {flattening optimization} {query flattener}

PARAGRAPH {
  When a subquery occurs in the FROM clause of a SELECT, the simplest
  behavior is to evaluate the subquery into a transient table, then run
  the outer SELECT against the transient table.  But such a plan
  can be suboptimal since the transient table will not have any indices
  and the outer query (which is likely a join) will be forced to do a