Documentation Source Text

Check-in [ea0f0b4c4a]
Login

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

Overview
Comment:Add a sentence to the FTS3/4 documentation to make it clear that only columns that are part of the FTS3/4 table declaration exist, even if an external content table with more columns is in use.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: ea0f0b4c4af09bf9ecd5eab905c9f283292360025b3b31ff5c79e86cfd45c901
User & Date: dan 2018-07-02 08:00:45.590
Context
2018-07-05
21:13
Fix typo in the RANGE documentation of windows functions. (check-in: 463da73728 user: drh tags: trunk)
2018-07-02
08:00
Add a sentence to the FTS3/4 documentation to make it clear that only columns that are part of the FTS3/4 table declaration exist, even if an external content table with more columns is in use. (check-in: ea0f0b4c4a user: dan tags: trunk)
2018-06-30
20:30
New entries in the change log. (check-in: 5ef0fc4e97 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/fts3.in.
1676
1677
1678
1679
1680
1681
1682


1683
1684
1685
1686
1687
1688
1689
1690
1691
  [ATTACH] nor may one of the FTS table and the external content be in the
  TEMP database when the other is in a persistent database file such as MAIN.

<p>
  When a users query on the FTS table requires a column value other than
  docid, FTS attempts to read the requested value from the corresponding column of
  the row in the content table with a rowid value equal to the current FTS


  docid. Or, if such a row cannot be found in the content table, a NULL
  value is used instead. For example:

<codeblock>
  CREATE TABLE t2(id INTEGER PRIMARY KEY, a, b, c);
  CREATE VIRTUAL TABLE t3 USING fts4(content="t2", b, c);
  
  INSERT INTO t2 VALUES(2, 'a b', 'c d', 'e f');
  INSERT INTO t2 VALUES(3, 'g h', 'i j', 'k l');







>
>
|
|







1676
1677
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
1690
1691
1692
1693
  [ATTACH] nor may one of the FTS table and the external content be in the
  TEMP database when the other is in a persistent database file such as MAIN.

<p>
  When a users query on the FTS table requires a column value other than
  docid, FTS attempts to read the requested value from the corresponding column of
  the row in the content table with a rowid value equal to the current FTS
  docid. Only the subset of content-table columns duplicated in the FTS/34
  table declaration can be queried for - to retrieve values from any other
  columns the content table must be queried directly. Or, if such a row cannot
  be found in the content table, a NULL value is used instead. For example:

<codeblock>
  CREATE TABLE t2(id INTEGER PRIMARY KEY, a, b, c);
  CREATE VIRTUAL TABLE t3 USING fts4(content="t2", b, c);
  
  INSERT INTO t2 VALUES(2, 'a b', 'c d', 'e f');
  INSERT INTO t2 VALUES(3, 'g h', 'i j', 'k l');
Changes to pages/windowfunctions.in.
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79

<p>Named <i>&lt;window-definitions&gt;</i> may also be added to a SELECT 
statement using a WINDOW clause and then refered to by name within window
function invocations. For example, the following SELECT statement contains
two named <i>&lt;window-definitions&gt;</i>, "win1" and "win2":

<codeblock>
  SELECT x, y, row_number() OVER <b>win1</b>, rank OVER <b>win2</b> FROM t0 
  WINDOW <b>win1</b> AS (ORDER BY y RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
         <b>win2</b> AS (PARTITION BY y ORDER BY x)
  ORDER BY x;
</codeblock>

<p>The WINDOW clause, when one is present, comes after any HAVING clause and
before any ORDER BY.







|







65
66
67
68
69
70
71
72
73
74
75
76
77
78
79

<p>Named <i>&lt;window-definitions&gt;</i> may also be added to a SELECT 
statement using a WINDOW clause and then refered to by name within window
function invocations. For example, the following SELECT statement contains
two named <i>&lt;window-definitions&gt;</i>, "win1" and "win2":

<codeblock>
  SELECT x, y, row_number() OVER <b>win1</b>, rank() OVER <b>win2</b> FROM t0 
  WINDOW <b>win1</b> AS (ORDER BY y RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
         <b>win2</b> AS (PARTITION BY y ORDER BY x)
  ORDER BY x;
</codeblock>

<p>The WINDOW clause, when one is present, comes after any HAVING clause and
before any ORDER BY.