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 {2016-10-00 (3.15.0)} {
<li> Added support for [row values].
<li> Added the "modeof=<i>filename</i>" URI parameter on the unix VFS
<li> Added support for [SQLITE_DBCONFIG_MAINDBNAME].
<li> Added the ability to [VACUUM] an [ATTACH|ATTACH-ed] database.
}

chng {2016-09-12 (3.14.2)} {
<li> Improved support for using the STDCALL calling convention in winsqlite3.dll.
<li> 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.
<li> Fix commenting errors and improve the comments generated on [EXPLAIN] listings
when the [-DSQLITE_ENABLE_EXPLAIN_COMMENTS] compile-time option is used.

[serverless|embedded], [zero-configuration], [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_15_0.html">Version 3.15.0</a> on
2016-10-XX.
<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>

<p>A WITH clause can contain ordinary common table expressions even if
it includes the RECURSIVE keyword.  The use of RECURSIVE does not force
common table expressions to be recursive.

<tcl>
hd_fragment recursivecte {recursive common table expressions} \
{recursive common table expression} {recursive query}
</tcl>
<h3>Recursive Common Table Expressions</h3>

<p>A recursive common table expression can be used to write a query that
walks a tree or graph.  A recursive common table expression has the same
basic syntax as an ordinary common table expression, but with the following
additional features:

<title>Row Values</title>
<tcl>hd_keywords rowvalue {row values} {vectors}</tcl>

<table_of_contents>

<h1>What Is A "Row Value"</h1>

<p>A "value" is a number or a string or a BLOB or a NULL.
A "row value" is an ordered list of two or more values &mdash; a "vector".
The "size" of a row value is the number of simple values the row value contains.
The size of a row value is always at least 2.

<p>SQLite allows row values to be expressed in two ways:
<ol>
<li>A parenthesized, comma-separated list of values.
<li>A subquery expression with two or more result columns.
</ol>
<p>Both of these row value formats will be demonstrated in the examples below.

<p>SQLite can use row values in two contexts:
<ol>
<li>Two row values of the same size 
can be compared using operators &lt;, &lt;=, &gt;, &gt;=,
=, &lt;&gt;, IS, IS NOT, IN, NOT IN, BETWEEN, or CASE.
<li>In an [UPDATE] statement, a list of column names can be set to a row value of
the same size.
</ol>

<h2>Row Value Comparisons</h2>

<p>Two row values are compared by comparing individual values from left to right.
A NULL value has the usual meaning of "unknown".  The following query demonstrates
some row value comparisons:

<codeblock>
SELECT
  (1,2,3) = (1,2,3),          -- 1
  (1,2,3) = (1,NULL,3),       -- NULL
  (1,2,3) = (1,NULL,4),       -- 0
  (1,2,3) &lt; (2,3,4),          -- 1
  (1,2,3) &lt; (1,2,4),          -- 1
  (1,2,3) &lt; (1,3,NULL),       -- 1
  (1,2,3) &lt; (1,2,NULL),       -- NULL
  (1,3,5) &lt; (1,2,NULL),       -- 0
  (1,2,NULL) IS (1,2,NULL);   -- 1
</codeblock>

<p>Any of the row values in the previous example could be replace by a
subquery that returns three columns and the same answer would result.
For example:

<codeblock>
CREATE TABLE t1(a,b,c);
INSERT INTO t1(a,b,c) VALUES(1,2,3);
SELECT (1,2,3)=(SELECT * FROM t1); -- 1
</codeblock>

<p>For a row-value [IN operator], the left-hand side (hereafter "LHS") can be either
a parenthesized list of values or a subquery with multiple columns.  But the
right-hand side (hereafter "RHS") must be a subquery expression.

<codeblock>
CREATE TABLE t2(x,y,z);
INSERT INTO t2(x,y,z) VALUES(1,2,3),(2,3,4),(1,NULL,5);
SELECT
   (1,2,3) IN (SELECT * FROM t2),  -- 1
   (7,8,9) IN (SELECT * FROM t2),  -- 0
   (1,3,5) IN (SELECT * FROM t2);  -- NULL
</codeblock>

<h2>Row Values In UPDATE Statements</h2>

<p>Row values can also be used in the SET clause of an [UPDATE] statement.
The RHS must be a list of column names.  The LHS can be any row value.
For example:

<codeblock>
UPDATE tab3 
   SET (a,b,c) = (SELECT x,y,z
                    FROM tab4
                   WHERE tab4.w=tab3.d);
 WHERE tab3.e BETWEEN 55 AND 66;
</codeblock>

<h1>Examples Uses Of Row Values</h1>

<h2>Scrolling Window Queries</h2>

<p>Imagine an application that wants to display a list of contacts
in alphabetical order by last_name, firstname, in a scrolling window
that can only show 7 contacts at a time.  Initialize the scrolling
window to the first 7 entries is easy:

<codeblock>
SELECT * FROM contacts
 ORDER BY last_name, first_name
 LIMIT 7;
</codeblock>

<p>When the user scrolls down, the application needs to find the
second set of 7 entries.  One way to do this is to use the OFFSET clause:

<codeblock>
SELECT * FROM contacts
 ORDER BY last_name, first_name
 LIMIT 7 OFFSET 7;
</codeblock>

<p>OFFSET gives the correct answer.  However, OFFSET requires time
proportional to the offset value.  So as the window scrolls down toward
the bottom of a long list, each successive offset computation takes
long and longer.

<p>A more efficient approach is to remember the current last entry
in the scroll window and then use a row value comparison in the WHERE
clause:

<codeblock>
SELECT * FROM contacts
 WHERE (last_name,first_name) &gt; (?1,?2)
 ORDER BY last_name, first_name
 LIMIT 7;
</codeblock>

<p>If the last_name and first_name of the bottom row of the previous
screen are bound to ?1 and ?2 then the query above computes the next
7 rows.  And, assuming there is an appropriate index, it computes it
very efficiently &mdash; much more efficiently than OFFSET.

<h2>Comparison of dates stored in separate fields</h2>

<p>The usual way of storing a date in a database table is as a single
field, as either a unix timestamp, a julian day number, or an ISO-8601
dates string.  But some application might store dates as three separate
fields for the year, month, and day.  

<codeblock>
CREATE TABLE info(
  year INT,          -- 4 digit year
  month INT,         -- 1 through 12
  day INT,           -- 1 through 31
  other_stuff BLOB   -- blah blah blah
);
</codeblock>

<p>When dates are stored this way, row value comparisons provide a
convenient way to compare dates:

<codeblock>
SELECT * FROM info
 WHERE (year,month,day) BETWEEN (2015,9,12) AND (2016,9,12);
</codeblock>

<title>The generate_series Table-Valued Function</title>
<tcl>hd_keywords series {generate_series}</tcl>

<table_of_contents>

<h1>Overview</h1>

<p>The generate_series(START,END,STEP) [table-valued function] is a
[loadable extension] included in the SQLite source tree, and compiled into
the [command-line shell].  The generate_series() table has a single
result column named "value" holding integer values
and a number of rows determined by the
parameters START, END, and STEP.  The first row of the table has
a value of START.  Subsequent rows increase by STEP up to END.

<p>Omitted parameters take on default values.  STEP defaults to 1.
END defaults to 9223372036854775807.  START defaults to 0.

<h2>Equivalent Recursive Common Table Expression</h2>

<p>The generate_series table can be simulated using a
[recursive common table expression].  If the three parameters
are $start, $end, and $step, then the equivalent common table
expression is:

<codeblock>
WITH RECURSIVE generate_series(value) AS (
  SELECT $start
  UNION ALL
  SELECT value+$step FROM generate_series
   WHERE value+$step&lt;=$end
) ...
</codeblock>

<p>The common table expression works without having to load an
extension.  On the other hand, the extension is easier to program
and faster.

<h1>Usage Examples</h1>

<p>Generate all multiples of 5 less than or equal to 100:

<codeblock>
SELECT value FROM generate_series(5,100,5);
</codeblock>

<p>Generate the 20 random integer values:

<codeblock>
SELECT random() FROM generate_series LIMIT 20;
</codeblock>

<p>Find the name of every customer whose account number
   is an even multiple of 100 between 10000 and 20000.

<codeblock>
SELECT customer.name
  FROM customer, generate_series(10000,20000,100)
 WHERE customer.id=value;
/* or */
SELECT name FROM customer
 WHERE id IN (SELECT value
                FROM generate_series(10000,20000,200));
</codeblock>