Documentation Source Text

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

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

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

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

  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

  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