Documentation Source Text

Check-in [bdbea7dc63]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Start a change log for 3.15.0. Begin writing documentation for row values and for the generate_series table-valued function.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: bdbea7dc63fcdc21006534da8398ef7b103d83a0
User & Date: drh 2016-09-14 04:12:49
Context
2016-09-14
16:27
Continuing work on the row value documentation. (check-in: ee61092516 user: drh tags: trunk)
04:12
Start a change log for 3.15.0. Begin writing documentation for row values and for the generate_series table-valued function. (check-in: bdbea7dc63 user: drh tags: trunk)
03:45
Add the 3.14.2 release to the chronology. (check-in: 54fe7773db user: drh tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/changes.in.

16
17
18
19
20
21
22







23
24
25
26
27
28
29
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-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.







>
>
>
>
>
>
>







16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
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.

Changes to pages/index.in.

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
[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_14_2.html">Version 3.14.2</a> on
2016-09-12.
<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>







|
|







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
[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>

Changes to pages/lang.in.

3393
3394
3395
3396
3397
3398
3399
3400
3401
3402
3403
3404
3405
3406
3407

<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 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:







|







3393
3394
3395
3396
3397
3398
3399
3400
3401
3402
3403
3404
3405
3406
3407

<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:

Added pages/rowvalue.in.

















































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
<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>

Added pages/series.in.

































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
<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>