Documentation Source Text

transactions.  ^An attempt to invoke the BEGIN command within
a transaction will fail with an error, regardless of whether
the transaction was started by [SAVEPOINT] or a prior BEGIN.
^The COMMIT command and the ROLLBACK command without the TO clause
work the same on [SAVEPOINT] transactions as they do with transactions
started by BEGIN.</p>

<tcl>hd_fragment immediate {BEGIN IMMEDIATE} {BEGIN EXCLUSIVE}</tcl>
<p>
^Transactions can be deferred, immediate, or exclusive.  
^The default transaction behavior is deferred.
^Deferred means that no locks are acquired
on the database until the database is first accessed.  ^Thus with a
deferred transaction, the BEGIN statement itself does nothing to the
filesystem.  ^Locks

funcdef {printf(FORMAT,...)} {} {
  ^(The printf(FORMAT,...) SQL function works like the [sqlite3_mprintf()] C-language
  function and the printf() function from the standard C library.)^
  The first argument is a format string that specifies how to construct the output
  string using values taken from subsequent arguments.  ^If the FORMAT argument is
  missing or NULL then the result is NULL.  ^The %n format is silently ignored and
  does not consume an argument.  ^The %p format is an alias for %X.  ^The %z format
  is interchangeable with %s.  ^(If there are too few arguments in the argument list,
  missing arguments are assumed to have a NULL value, which is translated into
  0 or 0.0 for numeric formats or an empty string for %s.)^
}
  

funcdef {quote(X)} {} {
  ^The quote(X) function returns the text of an SQL literal which

RecursiveBubbleDiagram with-clause
</tcl>

<p>Common Table Expressions or CTEs are temporary views or tables that exist
only for the duration of a single SQL statement.  There are two kinds of
common table expressions: "ordinary" and "recursive". Ordinary 
common table expressions are helpful for making
queries easier to understand by factoring
subqueries out of the main SQL statement.
Recursive common table expression
provide the ability to do a hierarchical or
recursive queries of trees and graphs, a capability
that is not otherwise available in the SQL language.

All common table expressions (ordinary and recursive) are 
created by prepending a WITH clause in front of a [SELECT], [INSERT], [DELETE],
or [UPDATE] statement.  A single WITH clause can specify one or more
common table expressions.

<tcl>hd_fragment ordinarycte {ordinary common table expressions}</tcl>

<p>Call the cte-table-name for a recursive common table expression
the "recursive table".  In the bubble diagram above, the recursive
table must appear exactly once in the FROM clause of the recursive-select
and must not appear anywhere else in either the initial-select or the
recursive-select, including subqueries.  The initial-select may be
a compound-select, but it may not include an ORDER BY, LIMIT, or OFFSET.
The recursive-select must be a simple select, not a compound.  The
recursive-select is allowed to include an ORDER BY, LIMIT, and/or OFFSET.

<p>The basic algorithm for computing the content of the recursive table
is as follows:

<ol>
<li> Run the initial-select and add the results to a queue.

  with LIMIT, a limit of zero means that no rows are ever added to the
  recursive table, and a negative limit means an unlimited number of rows
  may be added to the recursive table.)
<li><p>
  The OFFSET clause, if it is present and has a positive value N, prevents the
  first N rows from being added to the recursive table.
  The first N rows are still processed by the recursive-select; they
  just are not added to the recursive table.  Rows are not counted toward
  fulfilling the LIMIT until all OFFSET rows have been skipped.
<li><p>
  If an ORDER BY clause is present, it determines the order in which rows
  are extracted from the queue in step 2a.  If there is no ORDER BY clause,
  then the order in which rows are extracted is undefined.  (In the current
  implementation, the queue becomes a FIFO if the ORDER BY clause is omitted,
  but applications should not depend on that fact since it might change.)
</ul>

<tcl>hd_fragment rcex1</tcl>
<h4>Recursive Query Examples</h4>

<p>The following query returns all integers between 1 and 1000000:

<blockquote><pre>
WITH RECURSIVE
  cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x<1000000)
SELECT x FROM cnt;
</pre></blockquote>

<p>Consider how this query works.  The initial-select, which is this
case is just "VALUES(1)", runs first and returns a single row
with a single column "1".  This one row is added to the queue.  In
step 2a, that one row is extracted from the queue and added to "cnt".
Then the recursive query is run in accordance with step 2c generating
a single new row with value "2" to add to the queue.  The queue still
has one row, so step 2 repeats.  The 2 is extracted and added to the
recursive table by steps 2a and 2b.  Then the row containing 2 is used 
as if where the complete content of the recursive table and the 

recursion is stopped by a LIMIT rather than a WHERE clause.  The use of
LIMIT means that when the one-millionth row is added to the "cnt" table
(and immediately returned by the SELECT, thanks to the query optimizer)
then the recursion stops immediately regardless of how many rows might be
left in the queue.  On more complex queries, it can sometimes be
difficult to ensure that the WHERE clause will eventually cause the
queue to drain and the recursion to terminate.  But the LIMIT clause will
always stop the recursion.  So it is good practice to always include a
LIMIT clause as a safety if an upper bound on the size of the recursion 
is known.

<tcl>hd_fragment rcex2</tcl>
<h4>Hierarchical Query Examples</h4>

<p>Consider a table that describes the members of an organization as
well as the chain-of-command within that organization.

<blockquote><pre>
CREATE TABLE org(
  name TEXT PRIMARY KEY,
  boss TEXT REFERENCES org,
  height INT,
  -- other content omitted
);
</pre></blockquote>

<p>Every member in the organization has a name, and most members have
a single boss.  The head of the organization has no boss and so the 
boss field would be NULL for that one row.  The rows of the "org" table
form a tree.

<p>Here is a query that computes the average height over everyone
in Alice's organization, including Alice:

<blockquote><pre>
WITH RECURSIVE
  works_for_alice(n) AS (
    VALUES('Alice')
    UNION
    SELECT name FROM org, works_for_alice
     WHERE org.boss=works_for_alice.n
  )
SELECT avg(height) FROM org
 WHERE org.name IN works_for_alice;
</pre></blockquote>

<p>Next we consider an example that uses two 
common table expressions in a single WITH clause.  
The following table records a family tree:

<blockquote><pre>
CREATE TABLE family(
  name TEXT PRIMARY KEY,
  mom TEXT REFERENCES family,
  dad TEXT REFERENCES family,
  born DATETIME,

     ORDER BY checkin.mtime DESC
     LIMIT 20
  )
SELECT * FROM checkin JOIN ancestor ON id=xfrom;
</pre></blockquote>

<p>
The "ORDERBY checkin.mtime DESC" term in the recursive-select makes
the query run much faster by preventing it from following
branches that merge checkins
from long ago.  The ORDER BY forces the recursive-select to focus
on the most recent checkins, the ones we want.  Without the ORDER BY
on the recursive-select, we would be forced to find all ancestors
of the desired check-in, sort them all by mtime, then take the top
twenty.  The ORDER BY essentially sets up a priority queue that
forces the recursive query to look at the most recent ancestors first,
allowing us to insert a LIMIT close and restrict the scope of the
query to just the checkins of interest.

<tcl>hd_fragment rcex3</tcl>
<h4>Outlandish Recursive Query Examples</h4>

  a(t) AS (
    SELECT group_concat( substr(' .+*#', 1+min(iter/7,4), 1), '') 
    FROM m2 GROUP BY cy
  )
SELECT group_concat(rtrim(t),x'0a') FROM a;
</pre></blockquote>

<p>In this query, the "xaxis" and "yaxis" CTEs define the grid of points for
which the Mandelbrot Set will be approximated.  Each row in the
"m(iter,cx,cy,x,y)" CTE means that after "iter" iterations, the Mandelbrot
iteration starting at cx,cy has reached point x,y.  The number of iterations
in this example is limited to 28 (which severely limits the resolution of
the computation, but this is just an example, after all).
The "m2(iter,cx,cy)" CTE holds the maximum number of iterations reached when
starting at point cx,cy.
Finally, the entry in the "a(t)" CTE holds a string 

                                    +.
</pre></blockquote>

<tcl>hd_fragment mandelbrot</tcl>
<p>This next query solves a Sudoku puzzle.  The state of the puzzle is
defined by an 81-character string formed by reading entries from the
puzzle box row by row from left to right and then from top to bottom.
Blank squares in the puzzle are denoted by a "." character.  
Thus the input string

<blockquote>
53..7....6..195....98....6.8...6...34..8.3..17...2...6.6....28....419..5....8..79
</blockquote>

<p>Corresponds to a puzzle like this:

position.  The complicated "NOT EXISTS" subquery is the magic that
figures out whether or not each candidate "s" string is a valid
sudoku puzzle or not.

<p>The final answer is found by looking for a string with ind==0.
If the original sudoku problem did not have a unique solution, then
the query will return all possible solutions.  If the original problem
was unsolvable, then no rows will be returned.  In this case, the
answer of:

<blockquote>
534678912672195348198342567859761423426853791713924856961537284287419635345286179
</blockquote>

<p>Is computed in about 300 milliseconds.

<h3>Limitations And Caveats</h3>

<ul>
<li><p>
The WITH clause cannot be used within a [CREATE TRIGGER].
<li><p>

the way the data returned by a SELECT statement is determined as a series of
steps. It is important to keep in mind that this is purely illustrative -
in practice neither SQLite nor any other SQL engine is required to follow 
this or any other specific process.

<h3>Simple Select Processing</h3>


<p>Generating the results of a simple SELECT
statement is presented as a four step process in the description below:

<ol>
  <li> <p>[FROM clause] processing: The input data for the simple SELECT is
       determined. The input data is either implicitly a single row with 0
       columns (if there is no FROM clause) or is determined by the FROM

       clause.
  <li> <p>[WHERE clause] processing: The input data is filtered using the WHERE
       clause expression.  
  <li> <p>[GROUP BY|GROUP BY, HAVING and result-column expression] processing: 
       The set of result rows is computed by aggregating the data according to
       any GROUP BY clause and calculating the result-set expressions for the
       rows of the filtered input dataset.  
  <li> <p>[DISTINCT|DISTINCT/ALL keyword] processing: If the query is a "SELECT

<p>^(If the FROM clause is omitted from a simple SELECT statement, then the 
input data is implicitly a single row zero columns wide)^ (i.e. <i>N</i>=1 and
<i>M</i>=0).

<p>If a FROM clause is specified, the data on which a simple SELECT query
operates comes from the one or more tables or subqueries (SELECT statements
in parenthesis) specified following the FROM keyword. ^A subquery specified
in the table-or-subquery following the FROM clause in a 
simple SELECT statement is
handled as if it was a table containing the data returned by executing the
subquery statement. ^Each column of the subquery has the
[collation|collation sequence] and [affinity] of the corresponding expression
in the subquery statement.

<p>^If there is only a single table or subquery in the FROM
clause, then the input data used by the SELECT statement is the contents of the
named table. ^If there is more than one table or subquery in FROM clause
then the contents of all tables and/or subqueries
are joined into a single dataset for the simple SELECT statement to operate on.
Exactly how the data is combined depends on the specific [join-operator] and
[join-constraint] used to connect the tables or subqueries together.

<p>All joins in SQLite are based on the cartesian product of the left and
right-hand datasets. ^The columns of the cartesian product dataset are, in 
order, all the columns of the left-hand dataset followed by all the columns
of the right-hand dataset. ^There is a row in the cartesian product dataset
formed by combining each unique combination of a row from the left-hand 
and right-hand datasets. ^(In other words, if the left-hand dataset consists of
<i>Nlhs</i> rows of <i>Mlhs</i> columns, and the right-hand dataset of
<i>Nrhs</i> rows of <i>Mrhs</i> columns, then the cartesian product is a
dataset of <i>Nlhs.Nrhs</i> rows, each containing <i>Mlhs+Mrhs</i> columns.)^

<p>^If the join-operator is "CROSS JOIN", "INNER JOIN", "JOIN" or a comma
(",") and there is no ON or USING clause, then the result of the join is
simply the cartesian product of the left and right-hand datasets. 
If join-operator does have ON or USING clauses, those are handled according to
the following bullet points:

<ul>
  <li> <p>^(If there is an ON clause then the ON expression is
       evaluated for each row of the cartesian product as a 
       [boolean expression]. Only rows for which the expression evaluates to 
       true are included from the dataset.)^

  <li> <p>^If there is a USING clause
       then each of the column names specified must exist in the datasets to 
       both the left and right of the join-operator. ^(For each pair of named
       columns, the expression "lhs.X = rhs.X" is evaluated for each row of
       the cartesian product as a [boolean expression]. Only rows for which
       all such expressions evaluates to true are included from the
       result set.)^ ^When comparing values as a result of a USING clause, the
       normal rules for handling affinities, collation sequences and NULL
       values in comparisons apply. ^The column from the dataset on the
       left-hand side of the join-operator is considered to be on the left-hand
       side of the comparison operator (=) for the purposes of collation 
       sequence and affinity precedence.

       <p>^For each pair of columns identified by a USING clause, the column
       from the right-hand dataset is omitted from the joined dataset. ^This 
       is the only difference between a USING clause and its equivalent ON
       constraint.

  <li> <p>^(If the NATURAL keyword is in the join-operator then an
       implicit USING clause is added to the join-constraints. The implicit
       USING clause contains each of the column names that appear in both
       the left and right-hand input datasets.)^ ^If the left and right-hand
       input datasets feature no common column names, then the NATURAL keyword
       has no effect on the results of the join. ^A USING or ON clause may
       not be added to a join that specifies the NATURAL keyword.

  <li> <p>^(If the join-operator is a "LEFT JOIN" or "LEFT OUTER JOIN", then
       after
       the ON or USING filtering clauses have been applied, an extra row is 
       added to the output for each row in the original left-hand input 
       dataset that corresponds to no rows at all in the composite
       dataset (if any).)^ ^The added rows contain NULL values in the columns
       that would normally contain values copied from the right-hand input
       dataset.  
</ul>

<p>^Instead of a separate OFFSET clause, the LIMIT clause may specify two
scalar expressions separated by a comma. ^In this case, the first expression
is used as the OFFSET expression and the second as the LIMIT expression.
This is counter-intuitive, as when using the OFFSET clause the second of
the two expressions is the OFFSET and the first the LIMIT. This is intentional
- it maximizes compatibility with other SQL database systems.

<tcl>hd_fragment values {VALUES clause}</tcl>
<h3>VALUES clauses</h3>

<p>The phrase "VALUES(<i>expr-list</i>)" means the same thing
as "SELECT <i>expr-list</i>".  The phrase
"VALUES(<i>expr-list-1</i>),...,(<i>expr-list-N</i>)" means the same
thing as "SELECT <i>expr-list-1</i> UNION ALL ... UNION ALL
SELECT <i>expr-list-N</i>".  There is no advantage to using one form
over the other.  Both forms yield the same result and both forms use
the same amount of memory and processing time.


<h3>The WITH Clause</h3>

<p>SELECT statements may be optional preceded by a single
[WITH clause] that defines one or more [common table expressions]
for using within the SELECT statement.

<tcl>
##############################################################################
Section UPDATE update {UPDATE *UPDATEs}

RecursiveBubbleDiagram update-stmt
</tcl>

particular named index on a [DELETE], [SELECT], or [UPDATE] statement.
The INDEXED BY phrase is an extension that is particular to SQLite and
is not portable to other SQL database engines.
The INDEXED BY phrase can be seen in the following syntax
diagrams:</p>

<tcl>
RecursiveBubbleDiagram qualified-table-name

</tcl>

<p>^The "INDEXED BY index-name" phrase specifies that the named index
must be used in order to look up values on the preceding table.
^If index-name does not exist or cannot be used for the query, then
the preparation of the SQL statement fails.
^(The "NOT INDEXED" clause specifies that no index shall be used when