Documentation Source Text

Check-in [dcccfa67e1]
Login

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

Overview
Comment:Update the change log. Better fragments for matchinfo() flags.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: dcccfa67e188fda55ca10328ef06208fe6814c6e
User & Date: drh 2015-06-17 15:59:38.931
Context
2015-06-17
16:05
Further tweaks to the matchinfo() documentation. (check-in: d0b390051e user: drh tags: trunk)
15:59
Update the change log. Better fragments for matchinfo() flags. (check-in: dcccfa67e1 user: drh tags: trunk)
2015-06-10
16:28
Update the "Differences from FTS3/4" section of fts5.html. (check-in: 22655b73bd user: dan tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/changes.in.
22
23
24
25
26
27
28

29
30
31
32
33
34
35




36
37
38
39
40
41
42
<li>Added the [sqlite3_value_dup()] and [sqlite3_value_free()] interfaces.
<li>The [IS operator] is now able to drive indexes.
<li>Enhance the query planner to permit [automatic indexing] on FROM-clause
    subqueries that are implemented by co-routine.
<li>Disallow the use of "rowid" in [common table expressions].
<li>Added the [PRAGMA cell_size_check] command for better and earlier
    detection of database file corruption.

<li>Improved fuzz-testing of database files, with fixes for problems found.
<li>Add the fuzzcheck test program and automatically run this program
    using both SQL and database test cases on "make test".
<p><b>Important bug fixes:</b>
<li>Fix to CREATE TABLE AS so that columns of type TEXT never end up
    holding an INT value.  Ticket
    [https://www.sqlite.org/src/info/f2ad7de056ab1dc9200|f2ad7de056ab1dc9200]




<li>Fix the [sqlite3_memory_used()] and [sqlite3_memory_highwater()] interfaces
    so that they actually do provide a 64-bit answer.
}

chng {2015-05-20 (3.8.10.2)} {
<li>Fix an index corruption issue introduced by [version 3.8.7].  An index
    with a TEXT key can be corrupted by an [INSERT] into the corresponding 







>




|


>
>
>
>







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
<li>Added the [sqlite3_value_dup()] and [sqlite3_value_free()] interfaces.
<li>The [IS operator] is now able to drive indexes.
<li>Enhance the query planner to permit [automatic indexing] on FROM-clause
    subqueries that are implemented by co-routine.
<li>Disallow the use of "rowid" in [common table expressions].
<li>Added the [PRAGMA cell_size_check] command for better and earlier
    detection of database file corruption.
<li>Added the [matchinfo 'b' flag] to the [matchinfo()] function in [FTS3].
<li>Improved fuzz-testing of database files, with fixes for problems found.
<li>Add the fuzzcheck test program and automatically run this program
    using both SQL and database test cases on "make test".
<p><b>Important bug fixes:</b>
<li>Fix [CREATE TABLE AS] so that columns of type TEXT never end up
    holding an INT value.  Ticket
    [https://www.sqlite.org/src/info/f2ad7de056ab1dc9200|f2ad7de056ab1dc9200]
<li>Fix [CREATE TABLE AS] so that it does not leave NULL entries in the
    [sqlite_master table] if the SELECT statement on the right-hand side
    aborts with an error.  Ticket
    [https://www.sqlite.org/src/info/873cae2b6e25b|873cae2b6e25b]
<li>Fix the [sqlite3_memory_used()] and [sqlite3_memory_highwater()] interfaces
    so that they actually do provide a 64-bit answer.
}

chng {2015-05-20 (3.8.10.2)} {
<li>Fix an index corruption issue introduced by [version 3.8.7].  An index
    with a TEXT key can be corrupted by an [INSERT] into the corresponding 
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
<li>SQLITE_SOURCE_ID: "2015-05-09 12:14:55 05b4b1f2a937c06c90db70c09890038f6c98ec40"
<li>SHA1 for sqlite3.c: 85e4e1c08c7df28ef61bb9759a0d466e0eefbaa2
} {patchagainst 1}

chng {2015-05-07 (3.8.10)} {
<li>Added the [sqldiff.exe] utility program for computing the differences between two
    SQLite database files.
<li>Added the <a href="fts3.html#matchinfo-y">y format string</a> to the 
    [matchinfo()] function of [FTS3].
<li>Performance improvements for [ORDER BY], [VACUUM], [CREATE INDEX],
    [PRAGMA integrity_check], and [PRAGMA quick_check].
<li>Fix many obscure problems discovered while [SQL fuzzing].
<li>Identify all methods for important objects in the interface documentation.
    ([sqlite3_context|example])
<li>Made the [American Fuzzy Lop fuzzer]







|







62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
<li>SQLITE_SOURCE_ID: "2015-05-09 12:14:55 05b4b1f2a937c06c90db70c09890038f6c98ec40"
<li>SHA1 for sqlite3.c: 85e4e1c08c7df28ef61bb9759a0d466e0eefbaa2
} {patchagainst 1}

chng {2015-05-07 (3.8.10)} {
<li>Added the [sqldiff.exe] utility program for computing the differences between two
    SQLite database files.
<li>Added the [matchinfo y flag] to the 
    [matchinfo()] function of [FTS3].
<li>Performance improvements for [ORDER BY], [VACUUM], [CREATE INDEX],
    [PRAGMA integrity_check], and [PRAGMA quick_check].
<li>Fix many obscure problems discovered while [SQL fuzzing].
<li>Identify all methods for important objects in the interface documentation.
    ([sqlite3_context|example])
<li>Made the [American Fuzzy Lop fuzzer]
Changes to pages/fts3.in.
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119

<table striped=1>
  <tr><th>Character<th>Values<th>Description
  <tr><td>p <td>1 <td>The number of matchable phrases in the query.
  <tr><td>c <td>1 <td>The number of user defined columns in the FTS
    table (i.e. not including the docid or the [FTS hidden column]).
  <tr><td>x <td style="white-space:nowrap">3 * <i>cols</i> * <i>phrases</i> 
    <td>
      For each distinct combination of a phrase and table column, the
      following three values:
      <ul>
        <li> In the current row, the number of times the phrase appears in 
             the column.
        <li> The total number of times the phrase appears in the column in
             all rows in the FTS table.







|







1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119

<table striped=1>
  <tr><th>Character<th>Values<th>Description
  <tr><td>p <td>1 <td>The number of matchable phrases in the query.
  <tr><td>c <td>1 <td>The number of user defined columns in the FTS
    table (i.e. not including the docid or the [FTS hidden column]).
  <tr><td>x <td style="white-space:nowrap">3 * <i>cols</i> * <i>phrases</i> 
    <td><tcl>hd_fragment matchinfo-x {matchinfo x flag}</tcl>
      For each distinct combination of a phrase and table column, the
      following three values:
      <ul>
        <li> In the current row, the number of times the phrase appears in 
             the column.
        <li> The total number of times the phrase appears in the column in
             all rows in the FTS table.
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
      etc. In other words, the data for occurrences of phrase <i>p</i> in
      column <i>c</i> may be found using the following formula:
<pre>
          hits_this_row  = array&#91;3 * (c + p*cols) + 0&#93;
          hits_all_rows  = array&#91;3 * (c + p*cols) + 1&#93;
          docs_with_hits = array&#91;3 * (c + p*cols) + 2&#93;
</pre>
  <tr><td><a name="matchinfo-y">y</a> <td style="white-space:nowrap"><i>cols</i> * <i>phrases</i> 
    <td>
      For each distinct combination of a phrase and table column, the
      number of usable phrase matches that appear in the column. This is
      usually identical to the first value in each set of three returned by the
      'x' directive (see above). However, the number of hits reported by the
      'y' directive is zero for any phrase that is part of a sub-expression
      that does not match the current row. This makes a difference for
      expressions that contain AND operators that are descendants of OR







|
|







1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
      etc. In other words, the data for occurrences of phrase <i>p</i> in
      column <i>c</i> may be found using the following formula:
<pre>
          hits_this_row  = array&#91;3 * (c + p*cols) + 0&#93;
          hits_all_rows  = array&#91;3 * (c + p*cols) + 1&#93;
          docs_with_hits = array&#91;3 * (c + p*cols) + 2&#93;
</pre>
  <tr><td>y<td style="white-space:nowrap"><i>cols</i> * <i>phrases</i> 
    <td><tcl>hd_fragment matchinfo-y {matchinfo y flag}</tcl>
      For each distinct combination of a phrase and table column, the
      number of usable phrase matches that appear in the column. This is
      usually identical to the first value in each set of three returned by the
      'x' directive (see above). However, the number of hits reported by the
      'y' directive is zero for any phrase that is part of a sub-expression
      that does not match the current row. This makes a difference for
      expressions that contain AND operators that are descendants of OR
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176

1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
<pre>
          hits_for_phrase_p_column_c  = array&#91;c + p*cols&#93;
</pre>
      For queries that use OR expressions, or those that use LIMIT or return
      many rows, the 'y' matchinfo option may incur significantly less overhead
      than 'x'.

<tr><td><a name="matchinfo-b">b</a> <td style="white-space:nowrap"><i>(cols+31)/32</i> * <i>phrases</i> 
<td>

  The 'b' flag provides similar information to the 'y' flag, but in a more

  compact form. Instead of the precise number of hits, 'b' provides a single
  boolean flag for each phrase/column combination. If the phrase is present in
  the column at least once (i.e. if the corresponding integer output of 'y' would
  be non-zero), the corresponding flag is set. Otherwise cleared.

<p style="margin-left:0;margin-right:0">
  If the table has 32 or fewer columns, a single unsigned integer is output for
  each phrase in the query. Th least significant bit of the integer is set if the
  phrase appears at least once in column 0. The second least significant bit is
  set if the phrase appears once or more in column 1. And so on.

<p style="margin-left:0;margin-right:0">
  If the table has more than 32 columns, an extra integer is added to the output
  of each phrase for each extra 32 columns or part thereof. Integers
  corresponding to the same phrase are clumped together. For example, if a table







|
|

|
>







|







1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
<pre>
          hits_for_phrase_p_column_c  = array&#91;c + p*cols&#93;
</pre>
      For queries that use OR expressions, or those that use LIMIT or return
      many rows, the 'y' matchinfo option may incur significantly less overhead
      than 'x'.

<tr><td>b<td style="white-space:nowrap"><i>(cols+31)/32</i> * <i>phrases</i> 
<td><tcl>hd_fragment matchinfo-b {matchinfo b flag}</tcl>

  The matchinfo 'b' flag provides similar information to the
  [matchinfo 'y' flag], but in a more
  compact form. Instead of the precise number of hits, 'b' provides a single
  boolean flag for each phrase/column combination. If the phrase is present in
  the column at least once (i.e. if the corresponding integer output of 'y' would
  be non-zero), the corresponding flag is set. Otherwise cleared.

<p style="margin-left:0;margin-right:0">
  If the table has 32 or fewer columns, a single unsigned integer is output for
  each phrase in the query. The least significant bit of the integer is set if the
  phrase appears at least once in column 0. The second least significant bit is
  set if the phrase appears once or more in column 1. And so on.

<p style="margin-left:0;margin-right:0">
  If the table has more than 32 columns, an extra integer is added to the output
  of each phrase for each extra 32 columns or part thereof. Integers
  corresponding to the same phrase are clumped together. For example, if a table
Changes to pages/lang.in.
856
857
858
859
860
861
862

863
864
865
866
867
868
869
  if the "IF NOT EXISTS" clause is specified.

<p>^It is not an error to create a table that has the same name as an 
  existing [CREATE TRIGGER|trigger].

<p>^Tables are removed using the [DROP TABLE] statement.  </p>


<h3>CREATE TABLE ... AS SELECT Statements</h3>

<p>^(A "CREATE TABLE ... AS SELECT" statement creates and populates a database
table based on the results of a SELECT statement.)^ ^(The table has the same
number of columns as the rows returned by the SELECT statement. The name of
each column is the same as the name of the corresponding column in the result
set of the SELECT statement.)^ ^(The declared type of each column is determined







>







856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
  if the "IF NOT EXISTS" clause is specified.

<p>^It is not an error to create a table that has the same name as an 
  existing [CREATE TRIGGER|trigger].

<p>^Tables are removed using the [DROP TABLE] statement.  </p>

<tcl>hd_fragment createtabas {CREATE TABLE AS}</tcl>
<h3>CREATE TABLE ... AS SELECT Statements</h3>

<p>^(A "CREATE TABLE ... AS SELECT" statement creates and populates a database
table based on the results of a SELECT statement.)^ ^(The table has the same
number of columns as the rows returned by the SELECT statement. The name of
each column is the same as the name of the corresponding column in the result
set of the SELECT statement.)^ ^(The declared type of each column is determined