Documentation Source Text

     message preference over internally-generated messages.
<li> Added the -A command-line option to the [CLI] to make it easier to manage
     [SQLite Archive files].
<li> Query optimizer enhancements:
<ol type='a'>
  <li> Improve the [omit-left-join optimization] so that it works in cases where
       the right-hand table is UNIQUE but not necessarily NOT NULL.
  <li> Improve the [push-down optimization] so that it works for many LEFT JOINs.
  <li> Add the [LEFT JOIN strength reduction optimization] that converts a LEFT
       JOIN into an ordinary JOIN if there exist terms in the WHERE clause
       that would prevent the extra all-NULL row of the LEFT JOIN from
       appearing in the output set.
  <li> Avoid unnecessary writes to the sqlite_sequence table when an
       [AUTOINCREMENT] table is updated with an rowid that is less than the
       maximum.

<title>Code Of Conduct</title>

<fancy_format>

<h1>Overview</h1>

<p>Having been encouraged by commercial clients to adopt a written
code of conduct, the SQLite developers have elected to govern their
interactions with each other and with the larger SQLite user community in
accordance with the 72 "instruments of good works" from the 6th century
[https://en.wikipedia.org/wiki/Rule_of_Saint_Benedict|Rule of St. Benedict],
shown below.
This code of conduct has proven its mettle in use by many thousands of
communities for over 1,500 years, and has served as a baseline for many
civil law codes since the time of Charlemagne.

<p>
The SQLite developers are not monks.
None are able to adhere perfectly to this code of conduct.
All will fall short.  The goal of this code of conduct is
not to describe the minimum acceptable behavior below which
none transgress,
but rather to describe the highest standard of behavior
to which all aspire.
We grant grace to one another and request forgiveness and
promise to amend our ways when we stumble.

<p>
This code of conduct is freely accepted by all current SQLite developers.
But the rule is not transitive.
Everyone is free to use the SQLite source code, object code,
and/or documentation and/or submit patches and improvement
suggestions to the same without having to agree
with the behavioral standards outlined below.
You are encouraged to follow these standards as
in doing so you will live a happier, healthier, and more
productive lives.
Nevertheless, this is a choice that you must make for yourself,
and is not a precondition for using SQLite.


<h1>The Code</h1>

<ol>
<li> First of all, love the Lord God with your whole heart,
     your whole soul, and your whole strength.
<li> Then, love your neighbor as yourself.
<li> Do not murder.
<li> Do not commit adultery.
<li> Do not steal.
<li> Do not covet.
<li> Do not bear false witness.
<li> Honor all.
<li> Do not do to another what you would not have done to yourself.
<li> Deny oneself in order to follow Christ.
<li> Chastise the body.
<li> Do not become attached to pleasures.
<li> Love fasting.
<li> Relieve the poor.
<li> Clothe the naked.

<title>The SQLite Query Optimizer Overview</title>
<tcl>hd_keywords {optimizer} {query planner} {SQLite query planner}</tcl>

<table_of_contents>

<h1>Introduction</h1>
<p>
  This document provides an overview of how the query planner and optimizer
  for SQLite works.


<p>
  Given a single SQL statement, there might be dozens, hundreds, or even
  thousands of ways to implement that statement, depending on the complexity
  of the statement itself and of the underlying database schema.  The 

</codeblock>

<p>
  The view v1 cannot be [flattened] because it is DISTINCT.  It must
  instead be run as a subquery with the results being stored in a
  transient table, then the join is performed between t2 and the
  transient table.  The push-down optimization pushes down the
  "b BETWEEN 10 AND 20" term into the view.  This makes the transient
  table smaller, and helps the subquery to run faster if there
  is an index on t1.b.  The resulting evaluation is like this:

<codeblock>
  SELECT x, y, b
    FROM t2
    JOIN (SELECT DISTINCT a, b FROM t1 WHERE b BETWEEN 10 AND 20)
   WHERE b BETWEEN 10 AND 10;
</codeblock>

      ) recent
      ON u.source_seed_id = recent.xyz OR u.url_id = recent.xyz
   WHERE
       DATETIME(recent.rtime) > DATETIME('now', '-5 days');
</codeblock>

<p>
  It is possible that future enhancements to the prover might enable it
  to recognize that NULL inputs to certain built-in functions
  always result in a NULL answer.  But not all built-in
  functions have that property (for example [coalesce()]) and, of
  course, the prover will never be able to reason about
  [application-defined SQL functions].


<tcl>hd_fragment omitnoopjoin {omit-left-join optimization}</tcl>
<h1>The Omit LEFT JOIN Optimization</h1>

<p>
  Sometimes a LEFT JOIN can be completely omitted from a query without
  changing the result.  This can happen if all of the following are
  true:

<p>
  <ol>
  <li> The query is not an aggregate
  <li> Either the query is DISTINCT or else the ON or USING clause
       on the LEFT JOIN must constrains the join such that it matches
       only a single row
  <li> The right-hand table of the LEFT JOIN must not be used anywhere
       in the query outside of its own USING or ON clause.
  </ol>

<p>
  LEFT JOIN elimination often comes up when LEFT JOINs are used