Documentation Source Text

Check-in [4e286cee85]
Login

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

Overview
Comment:Added a 3.8.1 change log. Added documentation on SQLITE_MINIMUM_FILE_DESCRIPTOR.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 4e286cee85a1fc3626375ba1932fcfa3203fdaef
User & Date: drh 2013-08-30 14:28:53
Context
2013-08-31
14:32
Add documentation for the fts4 unicode61 tokenizer option "remove_diacritics=0". check-in: f5d1d7106f user: dan tags: trunk
2013-08-30
14:28
Added a 3.8.1 change log. Added documentation on SQLITE_MINIMUM_FILE_DESCRIPTOR. check-in: 4e286cee85 user: drh tags: trunk
14:03
Include all changes since 3.7.17 in the 3.8.0.1 change log. Typos fixed in the NGQP document. check-in: 3f23dd6ba9 user: drh tags: branch-3.8.0
2013-08-29
16:51
If the user tries a download hyperlink without javascript, send them to an explanation page. check-in: 32aeca2648 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to pages/changes.in.

    42     42       hd_enable_main 1
    43     43       incr nChng
    44     44       if {$nChng==1 && [file exists $DEST/$filename]} {
    45     45         file copy -force $DEST/$filename $DEST/releaselog/current.html
    46     46       }
    47     47     }
    48     48   }
           49  +
           50  +chng {2013-10-?? (3.8.1)} {
           51  +<li>Add support for SQLITE_ENABLE_STAT4
           52  +<li>Add the [SQLITE_MINIMUM_FILE_DESCRIPTOR] compile-time option
           53  +<li>Add the win32-longpath VFS on windows.
           54  +}
           55  +
           56  +chng {2013-08-29 (3.8.0.1)} {
           57  +<li>Add support for [partial indexes]</li>
           58  +<li>Cut-over to the [next generation query planner] for faster and better query plans.
           59  +<li>The [EXPLAIN QUERY PLAN] output no longer shows an estimate of the number of 
           60  +    rows generated by each loop in a join.
           61  +<li>Added the [FTS4 notindexed option], allowing non-indexed columns in an FTS4 table.
           62  +<li>Added the [SQLITE_STMTSTATUS_VM_STEP] option to [sqlite3_stmt_status()].
           63  +<li>Added the [cache_spill pragma].
           64  +<li>Added the [query_only pragma].
           65  +<li>Added the [defer_foreign_keys pragma] and the
           66  +    [sqlite3_db_status](db, [SQLITE_DBSTATUS_DEFERRED_FKS],...) C-language interface.
           67  +<li>Added the "percentile()" function as a [loadable extension] in the ext/misc
           68  +    subdirectory of the source tree.
           69  +<li>Added the [SQLITE_ALLOW_URI_AUTHORITY] compile-time option.
           70  +<li>Add the [sqlite3_cancel_auto_extension(X)] interface.
           71  +<li>A running SELECT statement that lacks a FROM clause (or any other statement that
           72  +    never reads or writes from any database file) will not prevent a read
           73  +    transaction from closing.
           74  +<li>Add the [SQLITE_DEFAULT_AUTOMATIC_INDEX] compile-time option.  Setting this option
           75  +    to 0 disables automatic indices by default.
           76  +<li>Issue an [SQLITE_WARNING_AUTOINDEX] warning on the [SQLITE_CONFIG_LOG] whenever
           77  +    the query planner uses an automatic index.
           78  +<li>Added the [SQLITE_FTS3_MAX_EXPR_DEPTH] compile-time option.
           79  +<li>Added an optional 5th parameter defining the collating sequence to the 
           80  +    next_char() extension SQL function.
           81  +<li>The [SQLITE_BUSY_SNAPSHOT] extended error code is returned in WAL mode when
           82  +    a read transaction cannot be upgraded to a write transaction because the read is
           83  +    on an older snapshot.
           84  +<li>Enhancements to the sqlite3_analyzer utility program to provide size
           85  +    information separately for each individual index of a table, in addition to
           86  +    the aggregate size.
           87  +<li>Allow read transactions to be freely opened and closed by SQL statements run 
           88  +    from within the implementation of [application-defined SQL functions] if the
           89  +    function is called by a SELECT statement that does not access any database table.
           90  +<li>Disable the use of posix_fallocate() on all (unix) systems unless the
           91  +    HAVE_POSIX_FALLOCATE compile-time option is used.
           92  +<li>Update the ".import" command in the [command-line shell] to support multi-line
           93  +    fields and correct RFC-4180 quoting and to issue warning and/or error messages
           94  +    if the input text is not strictly RFC-4180 compliant.
           95  +<li>Bug fix: In the [unicode61] tokenizer of [FTS4], treat all private code points
           96  +    as identifier symbols.
           97  +<li>Bug fix: Bare identifiers in ORDER BY clauses bind more tightly to output column
           98  +    names, but identifiers in expressions bind more tightly to input column names.
           99  +    Identifiers in GROUP BY clauses always prefer output column names, however.
          100  +<li>Bug fixes: Multiple problems in the legacy query optimizer were fixed by the 
          101  +    move to [NGQP].
          102  +</ul><p>The above are changes since [version 3.7.17].  The differences
          103  +between 3.8.0 and 3.8.0.1 are as follows:</p><ul>
          104  +<li>Fix an off-by-one error that caused quoted empty string at the end of a 
          105  +CRNL-terminated line of CSV input to be misread by the command-line shell.
          106  +<li>Fix a query planner bug involving a LEFT JOIN with a BETWEEN or LIKE/GLOB
          107  +constraint and then another INNER JOIN to the right that involves an OR constraint.
          108  +<li>Fix a query planner bug that could result in a segfault when querying tables
          109  +with a UNIQUE or PRIMARY KEY constraint with more than four columns.
          110  +
          111  +<li>SQLITE_SOURCE_ID: 
          112  +    "2013-08-29 17:35:01 352362bc01660edfbda08179d60f09e2038a2f49"
          113  +<li>SHA1 for sqlite3.c: 99906bf63e6cef63d6f3d7f8526ac4a70e76559e
          114  +}
    49    115   
    50    116   chng {2013-08-26 (3.8.0)} {
    51    117   <li>Add support for [partial indexes]</li>
    52    118   <li>Cut-over to the [next generation query planner] for faster and better query plans.
    53    119   <li>The [EXPLAIN QUERY PLAN] output no longer shows an estimate of the number of 
    54    120       rows generated by each loop in a join.
    55    121   <li>Added the [FTS4 notindexed option], allowing non-indexed columns in an FTS4 table.

Changes to pages/compile.in.

   191    191     in that if one thread is constantly changing the schema, another thread
   192    192     might spin on reparses and repreparations of a prepared statement and
   193    193     never get any real work done.  This parameter prevents an infinite loop
   194    194     by forcing the spinning thread to give up after a fixed number of attempts
   195    195     at recompiling the prepared statement.  The default setting is 50 which is
   196    196     more than adequate for most applications.
   197    197   }
          198  +
          199  +COMPILE_OPTION {SQLITE_MINIMUM_FILE_DESCRIPTOR=<i>N</i>} {
          200  +  The unix [VFS] will never use a file descriptor less than <i>N</i>.  The
          201  +  default value of <i>N</i> is 3.
          202  +  <p>
          203  +  Avoiding the use of low-numbered file descriptors is a defense against
          204  +  accidental database corruption.  If a database file was opened using
          205  +  file descriptor 2, for example, and then an assert() failed and invoked
          206  +  write(2,...), that would likely cause database corruption.  Using only
          207  +  higher-valued file descriptors avoids that problem.  The protection against
          208  +  using low-numbered file descriptiors can be disabled by setting this
          209  +  compile-time option to 0.
          210  +}
   198    211   
   199    212   COMPILE_OPTION {SQLITE_POWERSAFE_OVERWRITE=<i>&lt;0 or 1&gt;</i>} {
   200    213     This option changes the default assumption about [powersafe overwrite]
   201    214     for the underlying filesystems for the unix and windows [VFSes].
   202    215     Setting SQLITE_POWERSAFE_OVERWRITE to 1 causes SQLite to assume that
   203    216     application-level writes cannot changes bytes outside the range of
   204    217     bytes written even if the write occurs just before a power loss.

Changes to pages/index.in.

    91     91   
    92     92   </td>
    93     93   <td width="20"></td><td bgcolor="#044a64" width="1"></td><td width="20"></td>
    94     94   <td valign="top">
    95     95   <h3>Current Status</h3>
    96     96   
    97     97   <p><ul>
    98         -<li><a href="releaselog/3_8_0.html">Version 3.8.0</a>
           98  +<li><a href="releaselog/3_8_1.html">Version 3.8.1</a>
    99     99   of SQLite is recommended for all new development.
   100         -Upgrading from version 3.7.17 is optional.
          100  +Upgrading from version 3.7.17 and 3.8.0.1 is optional.
   101    101   Upgrading from all other prior versions of SQLite
   102    102   is recommended.</li>
   103    103   </ul></p>
   104    104   
   105    105   <h3>Common Links</h3>
   106    106   
   107    107   <p><ul>

Changes to pages/news.in.

    14     14     hd_puts "<h3>$date - $title</h3>"
    15     15     regsub -all "\n( *\n)+" $text "</p>\n\n<p>" txt
    16     16     regsub -all {[Tt]icket #(\d+)} $txt \
    17     17         {<a href="http://www.sqlite.org/cvstrac/tktview?tn=\1">\0</a>} txt
    18     18     hd_resolve "<blockquote>$txt</blockquote>"
    19     19     hd_puts "<hr width=\"50%\">"
    20     20   }
           21  +
           22  +newsitem {2013-08-29} {Release 3.8.0.1} {
           23  +  <p>SQLite [version 3.8.0.1] fixes some obscure bugs that were uncovered by
           24  +  users in the 3.8.0 release.  Changes from 3.8.0 are minimal.
           25  +}
    21     26   
    22     27   newsitem {2013-08-26} {Release 3.8.0} {
    23     28     <b>Do not fear the zero!</b>
    24     29   
    25     30     <p>SQLite [version 3.8.0] might easily have been called "3.7.18" instead.
    26     31     However, this release features the cutover of the
    27     32     [next generation query planner] or [NGQP], and there is a small chance of

Changes to pages/queryplanner-ng.in.

   202    202   nodes in the graph.</p>
   203    203   
   204    204   <p>The problem of finding the best query plan is equivalent to finding
   205    205   a minimum-cost path through the graph that visits each node
   206    206   exactly once.</p>
   207    207   
   208    208   <p>(Side note:  The costs estimates in the TPC-H Q8 graph were computed
   209         -by the query planner in SQLite 3.7.16 and converted using a base-10 logarithm.)
          209  +by the query planner in SQLite 3.7.16 and converted using a natural logarithm.)
   210    210   </p>
   211    211   
   212    212   <h3>3.2 Complications</h3>
   213    213   
   214    214   <p>The presentation of the query planner problem above is a simplification.
   215    215   The costs are estimates.  We cannot
   216    216   know what the true cost of running a loop is until we actually run the loop.
................................................................................
   254    254   GROUP BY, or DISTINCT clause. So for TPC-H Q8,
   255    255   the graph above is a reasonable representation of what needs to be computed.
   256    256   The general case involves a lot of extra complication, which for clarity
   257    257   is neglected in the remainder of this article.</p>
   258    258   
   259    259   <h3>3.3 Finding The Best Query Plan</h3>
   260    260   
   261         -<p>Prior to version 3.8.0, SQLite always used the
          261  +<p>Prior to version 3.8.0, SQLite always used
   262    262   the "Nearest Neighbor" or "NN" heuristic when searching for the best query plan.
   263    263   The NN heuristic makes a single traversal of the graph, always choosing
   264    264   the lowest-cost arc as the next step.  
   265    265   The NN heuristic works surprisingly well in most cases.
   266    266   And NN is fast, so that SQLite is able to quickly find good plans
   267    267   for even large 64-way joins.  In contrast, other SQL database engines that
   268    268   do more extensive searching tend to bog down when the
................................................................................
   273    273   The notation
   274    274   in the previous sentence means that the R table is run in the outer loop,
   275    275   N1 is in the next inner loop, N2 is in the third loop, and so forth down
   276    276   to P which is in the inner-most loop.  The shortest path through the
   277    277   graph (as found via exhaustive search) is  P-L-O-C-N1-R-S-N2
   278    278   with a cost of 27.38.  The difference might not seem like much, but 
   279    279   remember that
   280         -the costs are logarithmic, so the shortest path is nearly 14,000 times
          280  +the costs are logarithmic, so the shortest path is nearly 750 times
   281    281   faster than that path found using the NN heuristic.</p>
   282    282   
   283    283   <p>One solution to this problem is to change SQLite to do an exhaustive
   284    284   search for the best path.  But an exhaustive search requires time 
   285    285   proportional to
   286    286   K! (where K is the number of tables in the join) and so when you get 
   287    287   beyond a 10-way join, the time
................................................................................
   561    561   <center>
   562    562   <img src="images/qp/fqp1.gif">
   563    563   </center>
   564    564   
   565    565   <p>
   566    566   In the "without ANALYZE" case on the left, the NN algorithm chooses 
   567    567   loop P (PLINK) as the outer loop because 4.9 is less than 5.2, resulting
   568         -in path P-T which is algorithm-1. NN only looks a the single best choice
          568  +in path P-T which is algorithm-1. NN only looks at the single best choice
   569    569   at each step so it completely misses the fact that 
   570    570   5.2+4.4 makes a slightly cheaper plan than 4.9+4.8. But the N3 algorithm
   571    571   keeps track of the 5 best paths for a 2-way join, so it ends up
   572    572   selecting path T-P because of its slightly lower overall cost.
   573    573   Path T-P is algorithm-2.
   574    574   </p>
   575    575   
................................................................................
   587    587   in the TPC-H Q8 graph.)</p>
   588    588   
   589    589   <h3>4.2 Fixing The Problem</h3>
   590    590   
   591    591   <p>Running [ANALYZE] on the repository database immediately fixed the
   592    592   performance problem.  However, we want Fossil to be robust and to always
   593    593   work quickly regardless of whether or not its repository has been analyzed.
   594         -For this reason, the query was modify to use the CROSS JOIN operator 
          594  +For this reason, the query was modified to use the CROSS JOIN operator 
   595    595   instead of the plain JOIN operator.
   596    596   SQLite will not reorder the tables of a CROSS JOIN.
   597    597   This is a long-standing feature of SQLite that is specifically designed
   598    598   to allow knowledgeable programmers
   599    599   to enforce a particular loop nesting order.  Once the join
   600    600   was changed to CROSS JOIN (the addition of a single keyword) the NGQP was
   601    601   forced to chose the faster algorithm-1 regardless of whether or not