Documentation Source Text

Check-in [4cadcc5b9c]
Login

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

Overview
Comment:Begin a change log entry for the 3.21.0 release.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 4cadcc5b9c5eda7860e784e900d19ff681afe3759223d05f56690ff735d91bb1
User & Date: drh 2017-08-03 20:39:01
Context
2017-08-04
14:25
Add the 2009-2018 speed graph relative to 3.20.0 to the speed-size spreadsheet. check-in: 4df296b435 user: drh tags: trunk
2017-08-03
20:39
Begin a change log entry for the 3.21.0 release. check-in: 4cadcc5b9c user: drh tags: trunk
15:47
Fix another typo in the bindptr.html document. check-in: 8d2c7aa89d user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Show Whitespace Changes 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 {2017-08-01 (3.20.0)} {
<li> Update the text of error messages returned by [sqlite3_errmsg()] for some
     error codes.
<li> Add new [pointer passing interfaces].
<li> Backwards-incompatible changes to some extensions in order to take 
     advantage of the improved security offered by the new 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







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
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 {2017-11-01 (3.21.0)} {
<li> Take advantage of the atomic-write capabilities of the 
     [https://en.wikipedia.org/wiki/F2FS|F2FS filesystem] when available, for
     greatly reduced transaction overhead.  This currently requires the
     [SQLITE_ENABLE_BATCH_ATOMIC_WRITE] compile-time option.
<li> Allow [ATTACH] and [DETACH] commands to work inside of a transaction.
<li> Query planner enhancements:
<ol type="a">
<li> Enhanced the [LIKE optimization] so that it works with an ESCAPE clause.
</ol>
<li> Miscellaneous [microoptimizations] reduce CPU usage by about 0.4%.
<li> Bug fixes:
<ol type="a">
<li> Fix a faulty assert() statement discovered by OSSFuzz.
     Ticket [https://sqlite.org/src/info/cb91bf4290c211d|cb91bf4290c211d]
</ol>
}

chng {2017-08-01 (3.20.0)} {
<li> Update the text of error messages returned by [sqlite3_errmsg()] for some
     error codes.
<li> Add new [pointer passing interfaces].
<li> Backwards-incompatible changes to some extensions in order to take 
     advantage of the improved security offered by the new 

Changes to pages/chronology.in.

24
25
26
27
28
29
30

31
32
33
34
35
36
37
#    ORDER BY mtime DESC;
#
# A small amount of manual editing and de-duplication followed.
#
# Manually edit the list for each subsequent release.
#      
foreach line [split {

9501e22dfe|2017-08-01|Version 3.20.0
036ebf729e|2017-06-17|Version 3.18.2
77bb46233d|2017-06-16|Version 3.18.1
0ee482a1e0|2017-06-08|Version 3.19.3
edb4e819b0|2017-05-25|Version 3.19.2
f6d7b988f4|2017-05-24|Version 3.19.1
28a94eb282|2017-05-22|Version 3.19.0







>







24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
#    ORDER BY mtime DESC;
#
# A small amount of manual editing and de-duplication followed.
#
# Manually edit the list for each subsequent release.
#      
foreach line [split {
xxxxxxxxxx|pending|Version 3.21.0
9501e22dfe|2017-08-01|Version 3.20.0
036ebf729e|2017-06-17|Version 3.18.2
77bb46233d|2017-06-16|Version 3.18.1
0ee482a1e0|2017-06-08|Version 3.19.3
edb4e819b0|2017-05-25|Version 3.19.2
f6d7b988f4|2017-05-24|Version 3.19.1
28a94eb282|2017-05-22|Version 3.19.0

Changes to pages/compile.in.

766
767
768
769
770
771
772



















773
774
775
776
777
778
779
  or written.  On filesystems that support atomic writes, this
  optimization can result in significant speed improvements for
  small updates.  However, few filesystems support this capability
  and the code paths that check for this capability slow down write
  performance on systems that lack atomic write capability, so this
  feature is disabled by default.
}




















COMPILE_OPTION {SQLITE_ENABLE_COLUMN_METADATA} {
  When this C-preprocessor macro is defined, SQLite includes some
  additional APIs that provide convenient access to meta-data about
  tables and queries.  The APIs that are enabled by this option are:

  <ul>







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
  or written.  On filesystems that support atomic writes, this
  optimization can result in significant speed improvements for
  small updates.  However, few filesystems support this capability
  and the code paths that check for this capability slow down write
  performance on systems that lack atomic write capability, so this
  feature is disabled by default.
}

COMPILE_OPTION {SQLITE_ENABLE_BATCH_ATOMIC_WRITE} {
  This compile-time option enables SQLite to take advantage batch
  atomic write capabilities in the underlying filesystem.  As of
  SQLite version 3.21.0 ([dateof:3.21.0]) this is only supported on
  [https://en.wikipedia.org/wiki/F2FS|F2FS].  However, the interface
  is implemented generically, using [sqlite3_file_control()] with
  [SQLITE_FCNTL_BEGIN_ATOMIC_WRITE] and [SQLITE_FCNTL_COMMIT_ATOMIC_WRITE]
  so the capability can be added to other filesystem times in the
  future.  When this option is enabled, SQLite automatically detects
  that the underlying filesystem supports batch atomic writes, and
  when it does so it avoids writing the [rollback journal] for transaction
  control.  This can make transactions over twice as fast, while
  simultaneously reducing wear on SSD storage devices.
<p>
  Future versions of SQLite might enable the batch-atomic-write
  capability by default, at which point this compile-time option
  will become superfluous.
}

COMPILE_OPTION {SQLITE_ENABLE_COLUMN_METADATA} {
  When this C-preprocessor macro is defined, SQLite includes some
  additional APIs that provide convenient access to meta-data about
  tables and queries.  The APIs that are enabled by this option are:

  <ul>

Changes to pages/index.in.

10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
[full-featured SQL|full-featured], [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_20_0.html">Version 3.20.0</a> ([dateof:3.20.0]).
<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>







|







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
[full-featured SQL|full-featured], [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_21_0.html">Version 3.21.0</a> ([dateof:3.21.0]).
<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.

1622
1623
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
^When not in [shared cache mode], 
it is possible to have the same database file attached multiple times using 
different names, and detaching one connection to a file will leave the 
others intact.</p>
^In [shared cache mode], attempting to attach the same database file more
than once results in an error.

<p>^This statement will fail if SQLite is in the middle of a transaction.</p>


<tcl>
##############################################################################
Section {DROP INDEX} dropindex {{DROP INDEX}}

RecursiveBubbleDiagram drop-index-stmt
</tcl>







<
<







1622
1623
1624
1625
1626
1627
1628


1629
1630
1631
1632
1633
1634
1635
^When not in [shared cache mode], 
it is possible to have the same database file attached multiple times using 
different names, and detaching one connection to a file will leave the 
others intact.</p>
^In [shared cache mode], attempting to attach the same database file more
than once results in an error.




<tcl>
##############################################################################
Section {DROP INDEX} dropindex {{DROP INDEX}}

RecursiveBubbleDiagram drop-index-stmt
</tcl>

Changes to pages/optoverview.in.

320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346


347
348
349
350
351
352
353
...
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
....
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
  There are many conditions on this optimization:
}
PARAGRAPH {
  <ol>
  <li>^The right-hand side of the LIKE or GLOB must be either a string literal
      or a [parameter] bound to a string literal
      that does not begin with a wildcard character.</li>
  <li>^The ESCAPE clause cannot appear on the LIKE operator.</li>
  <li>It must not be possible to make the LIKE or GLOB operator true by
      having a numeric value (instead of a string or blob) on the
      left-hand side. This means that either:
      <ol type="A">
      <li> the left-hand side of the LIKE or GLOB operator is the name
           of an indexed column with [affinity | TEXT affinity], or</li>
      <li> the right-hand side pattern argument does not begin with a
           minus sign ("-") or a digit.</li>
      </ol>
      This constraint arises from the fact that numbers do not sort in
      lexicographical order.  For example: 9&lt;10 but '9'&gt;'10'.</li>
  <li>^The built-in functions used to implement LIKE and GLOB must not
      have been overloaded using the sqlite3_create_function() API.</li>
  <li>^For the GLOB operator, the column must be indexed using the 
      built-in BINARY collating sequence.</li>
  <li>^For the LIKE operator, if [case_sensitive_like] mode is enabled then
      the column must indexed using BINARY collating sequence, or if
      [case_sensitive_like] mode is disabled then the column must indexed
      using built-in NOCASE collating sequence.</li>


  </ol>
}
PARAGRAPH {
  The LIKE operator has two modes that can be set by a
  [case_sensitive_like | pragma].  ^The
  default mode is for LIKE comparisons to be insensitive to differences
  of case for latin1 characters.  ^(Thus, by default, the following
................................................................................
  character then this optimization is attempted.  ^If the right-hand side 
  is a [parameter] that is bound to a string, then this optimization is
  only attempted if the [prepared statement] containing the expression
  was compiled with [sqlite3_prepare_v2()] or [sqlite3_prepare16_v2()].
  ^The LIKE optimization is not attempted if the
  right-hand side is a [parameter] and the statement was prepared using
  [sqlite3_prepare()] or [sqlite3_prepare16()].
  ^The LIKE optimization is not attempted if there is an ESCAPE phrase
  on the LIKE operator.
}
PARAGRAPH {
  Suppose the initial sequence of non-wildcard characters on the right-hand
  side of the LIKE or GLOB operator is <i>x</i>.  We are using a single 
  character to denote this non-wildcard prefix but the reader should
  understand that the prefix can consist of more than 1 character.
  Let <i>y</i> be the smallest string that is the same length as /x/ but which
................................................................................
  started.  This results in a slightly faster sort.  But the bigger
  advantages are that many fewer rows need to be held in memory,
  reducing memory requirements, and outputs can begin to appear before
  the core query has run to completion.
}

HEADING 1 {Subquery flattening} flattening
hd_keywords {flattening optimization}

PARAGRAPH {
  When a subquery occurs in the FROM clause of a SELECT, the simplest
  behavior is to evaluate the subquery into a transient table, then run
  the outer SELECT against the transient table.  But such a plan
  can be suboptimal since the transient table will not have any indices
  and the outer query (which is likely a join) will be forced to do a







<












|






>
>







 







<
<







 







|







320
321
322
323
324
325
326

327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
...
403
404
405
406
407
408
409


410
411
412
413
414
415
416
....
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
  There are many conditions on this optimization:
}
PARAGRAPH {
  <ol>
  <li>^The right-hand side of the LIKE or GLOB must be either a string literal
      or a [parameter] bound to a string literal
      that does not begin with a wildcard character.</li>

  <li>It must not be possible to make the LIKE or GLOB operator true by
      having a numeric value (instead of a string or blob) on the
      left-hand side. This means that either:
      <ol type="A">
      <li> the left-hand side of the LIKE or GLOB operator is the name
           of an indexed column with [affinity | TEXT affinity], or</li>
      <li> the right-hand side pattern argument does not begin with a
           minus sign ("-") or a digit.</li>
      </ol>
      This constraint arises from the fact that numbers do not sort in
      lexicographical order.  For example: 9&lt;10 but '9'&gt;'10'.</li>
  <li>^The built-in functions used to implement LIKE and GLOB must not
      have been overloaded using the [sqlite3_create_function()] API.</li>
  <li>^For the GLOB operator, the column must be indexed using the 
      built-in BINARY collating sequence.</li>
  <li>^For the LIKE operator, if [case_sensitive_like] mode is enabled then
      the column must indexed using BINARY collating sequence, or if
      [case_sensitive_like] mode is disabled then the column must indexed
      using built-in NOCASE collating sequence.</li>
  <li>If the ESCAPE option is used, the ESCAPE character must be ASCII,
      or a single-byte character in UTF-8.
  </ol>
}
PARAGRAPH {
  The LIKE operator has two modes that can be set by a
  [case_sensitive_like | pragma].  ^The
  default mode is for LIKE comparisons to be insensitive to differences
  of case for latin1 characters.  ^(Thus, by default, the following
................................................................................
  character then this optimization is attempted.  ^If the right-hand side 
  is a [parameter] that is bound to a string, then this optimization is
  only attempted if the [prepared statement] containing the expression
  was compiled with [sqlite3_prepare_v2()] or [sqlite3_prepare16_v2()].
  ^The LIKE optimization is not attempted if the
  right-hand side is a [parameter] and the statement was prepared using
  [sqlite3_prepare()] or [sqlite3_prepare16()].


}
PARAGRAPH {
  Suppose the initial sequence of non-wildcard characters on the right-hand
  side of the LIKE or GLOB operator is <i>x</i>.  We are using a single 
  character to denote this non-wildcard prefix but the reader should
  understand that the prefix can consist of more than 1 character.
  Let <i>y</i> be the smallest string that is the same length as /x/ but which
................................................................................
  started.  This results in a slightly faster sort.  But the bigger
  advantages are that many fewer rows need to be held in memory,
  reducing memory requirements, and outputs can begin to appear before
  the core query has run to completion.
}

HEADING 1 {Subquery flattening} flattening
hd_keywords {flattening optimization} {query flattener}

PARAGRAPH {
  When a subquery occurs in the FROM clause of a SELECT, the simplest
  behavior is to evaluate the subquery into a transient table, then run
  the outer SELECT against the transient table.  But such a plan
  can be suboptimal since the transient table will not have any indices
  and the outer query (which is likely a join) will be forced to do a