Documentation Source Text

Check-in [79f3efbc86]
Login

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

Overview
Comment:Fix typos. Clarification to the CoC in response to criticism.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 79f3efbc86ea690c4afd605386748b54d53476b9335c2e56dc508f119dd4cbb2
User & Date: drh 2018-03-23 11:47:35
Context
2018-03-23
14:30
Add TRUE and FALSE to the syntax diagrams as new literal values. check-in: 1d1c0fc6ec user: drh tags: trunk
11:47
Fix typos. Clarification to the CoC in response to criticism. check-in: 79f3efbc86 user: drh tags: trunk
2018-03-22
18:55
Further enhancement to the optimizer overview document, giving the change log for 3.23.0 something to link to. check-in: 219fa13637 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/changes.in.

38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
     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 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.







|







38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
     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.

Changes to pages/codeofconduct.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
<title>Code Of Conduct</title>

<fancy_format>

<h1>Overview</h1>

<p>The code of conduct for developers of the SQLite 


project is 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 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 applies to the SQLite developers only.

Everyone is free to use the SQLite source code, object code,

and/or documentation 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 life.
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 (1 Peter 2:17).
<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.






|
>
>
|



|
|













|
>

>
|



|


>












|







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

Changes to pages/optoverview.in.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
....
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
....
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
....
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
<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 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 in order to make the transient
  table smaller, or (if there were an index on t1.b) help the subquery
  to run faster.  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 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].


................................................................................
  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 constraint 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







|







 







|
|
|







 







|







 







|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
....
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
....
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
....
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
<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].


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