From 3640b21e686fef7e3f25dc775112c7d4be43f197 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Mon, 3 Jun 2024 07:16:48 +0200 Subject: Merging upstream version 3.46.0. Signed-off-by: Daniel Baumann --- www/appfunc.html | 4 +- www/c3ref/c_source_id.html | 6 +- www/c3ref/funclist.html | 8 +- www/c3ref/io_methods.html | 3 +- www/c3ref/keyword_check.html | 2 +- www/c3ref/profile.html | 4 +- www/c3ref/sqlite3.html | 4 +- www/c3ref/update_hook.html | 6 + www/c3ref/vtab_distinct.html | 47 +- www/capi3ref.html | 78 ++-- www/changes.html | 56 ++- www/chronology.html | 14 +- www/cli.html | 4 +- www/compile.html | 28 +- www/copyright.html | 6 +- www/crew.html | 12 +- www/different.html | 7 +- www/doc_backlink_crossref.html | 164 +++++-- www/doc_keyword_crossref.html | 2 +- www/doc_pagelink_crossref.html | 40 +- www/doc_target_crossref.html | 31 +- www/doclist.html | 1 + www/eqp.html | 2 +- www/fileformat.html | 4 +- www/fileformat2.html | 4 +- www/flextypegood.html | 2 +- www/fts5.html | 244 ++++++++-- www/index.html | 2 +- www/json1.html | 89 ++-- www/keyword_index.html | 25 +- www/lang.html | 3 + www/lang_analyze.html | 204 +++++++-- www/lang_datefunc.html | 206 +++++---- www/lang_expr.html | 317 +++++++------ www/lang_returning.html | 150 +++---- www/lang_select.html | 598 +++++++++++++------------ www/loadext.html | 20 +- www/news.html | 16 +- www/opcode.html | 58 ++- www/optoverview.html | 285 +++++++----- www/pragma.html | 151 ++++--- www/queryplanner-ng.html | 196 ++++---- www/quirks.html | 93 +++- www/releaselog/3_13_0.html | 2 +- www/releaselog/3_42_0.html | 2 +- www/releaselog/3_46_0.html | 175 ++++++++ www/releaselog/3_9_0.html | 2 +- www/releaselog/3_9_1.html | 2 +- www/releaselog/3_9_2.html | 2 +- www/releaselog/3_9_3.html | 2 +- www/releaselog/current.html | 135 ++---- www/search | 2 +- www/search.d/search.db | Bin 9785344 -> 9916416 bytes www/serverless.html | 2 +- www/session.html | 20 + www/session/changegroup.html | 1 + www/session/funclist.html | 1 + www/session/sqlite3changegroup_add_change.html | 139 ++++++ www/sessionintro.html | 2 +- www/sitemap.html | 3 + www/syntax/numeric-literal.html | 224 +++++---- www/syntaxdiagrams.html | 224 +++++---- www/toc.db | Bin 90112 -> 90112 bytes www/vfs.html | 2 +- www/wal.html | 14 +- www/whentouse.html | 70 ++- www/whybytecode.html | 435 ++++++++++++++++++ www/windowfunctions.html | 272 +++++------ 68 files changed, 3297 insertions(+), 1632 deletions(-) create mode 100644 www/releaselog/3_46_0.html create mode 100644 www/session/sqlite3changegroup_add_change.html create mode 100644 www/whybytecode.html (limited to 'www') diff --git a/www/appfunc.html b/www/appfunc.html index 998bcd0..b3167a5 100644 --- a/www/appfunc.html +++ b/www/appfunc.html @@ -524,7 +524,7 @@ where an attacker might be able to surreptiously invoke them by modifying a database schema:

@@ -1400,12 +1405,12 @@ on secondary or tertiary markings in the document or query terms.

Auxiliary functions are similar to SQL scalar functions, except that they may only be used within full-text queries (those that use -the MATCH operator) on an FTS5 table. Their results are calculated based not -only on the arguments passed to them, but also on the current match and -matched row. For example, an auxiliary function may return a numeric value -indicating the accuracy of the match (see the bm25() function), -or a fragment of text from the matched row that contains one or more -instances of the search terms (see the snippet() function). +the MATCH operator, or LIKE/GLOB with the trigram tokenizer) on an FTS5 table. +Their results are calculated based not only on the arguments passed to them, +but also on the current match and matched row. For example, an auxiliary +function may return a numeric value indicating the accuracy of the match (see +the bm25() function), or a fragment of text from the matched row +that contains one or more instances of the search terms (see the snippet() function).

To invoke an auxiliary function, the name of the FTS5 table should be specified as the first argument. Other arguments may follow the first, @@ -1437,7 +1442,10 @@ the following section. Applications may also implement fragment of text from one of the columns of the matched row and returns it with each instance of a queried term surrounded by markup in the same manner as the highlight() function. The fragment of text is - selected so as to maximize the number of queried terms it contains. + selected so as to maximize the number of distinct queried terms it + contains. Higher weight is given to snippets that occur at the start + of a column value, or that immediately follow "." or ":" characters + in the text. @@ -2349,18 +2357,19 @@ replaced. returns an SQLite error code. In this case the xDestroy function is not invoked. -

The final three arguments passed to the auxiliary function callback are -similar to the three arguments passed to the implementation of a scalar SQL -function. All arguments except the first passed to the auxiliary function are -available to the implementation in the apVal[] array. The +

The final three arguments passed to the auxiliary function callback +(pCtx, nVal and apVal above) are similar to the three arguments passed to the +implementation of a scalar SQL function. The apVal[] array contains all +SQL arguments except the first passed to the auxiliary function. The implementation should return a result or error via the content handle pCtx.

The first argument passed to an auxiliary function callback is a pointer -to a structure containing methods that may be invoked in order to obtain -information regarding the current query or row. The second argument is an -opaque handle that should be passed as the first argument to any such method -invocation. For example, the following auxiliary function definition returns -the total number of tokens in all columns of the current row: +to a structure (pApi above) containing methods that may be invoked +in order to obtain information regarding the current query or row. The second +argument is an opaque handle (pFts above) that should be passed as the +first argument to any such method invocation. For example, the following +auxiliary function returns the total number of tokens in all columns of the +current row: 

/*
 ** Implementation of an auxiliary function that returns the number
@@ -2388,10 +2397,185 @@ static void column_size_imp(
 implementations in detail. Further examples may be found in the "fts5_aux.c"
 file of the source code.
 
-

-
7.2.1. 
-  Custom Auxiliary Functions API Reference
-

+

+
7.2.1. Custom Auxiliary Functions API Overview

+
+
This section provides an overview of the capabilities of the auxiliary
+function API. It does not describe every function. Refer to the reference text below for a
+complete description.
+
+
When invoked, an auxiliary function implementation has access to APIs that
+allow it to query FTS5 for various information. Some of these APIs return
+information relating to the current row of the FTS5 table being visited,
+some relating to the entire set of rows that will be visited by the FTS5
+query, and some relating to the FTS5 table. Given an FTS5 table populated as
+follows:
+
+
CREATE VIRTUAL TABLE ft USING fts5(a, b);
+INSERT INTO ft(rowid, a, b) VALUES
+        (1, 'ab cd', 'cd de one'),
+        (2, 'de fg', 'fg gh'),
+        (3, 'gh ij', 'ij ab three four');
+

+
+
and the query:
+
+
SELECT my_aux_function(ft) FROM ft('ab')
+

+
+
then the custom auxiliary function will be invoked for rows 1 and 3 (all
+rows that contain the token "ab" and therefore match the query). 
+
+
Number of rows/columns in table: xRowCount, xColumnCount
+
+
The system may be queried for the total number of rows in the FTS5 table
+using the xRowCount API. This provides the total number
+of rows in the table, not the number that match the current query.
+
+
Table columns are numbered from left to right starting from 0. The
+"rowid" column does not count - only user declared columns - so in the example
+above column "a" is column 0 and column "b" is column 1. From within an
+auxiliary function implementation, the xColumnCount
+API may be used to determine how many columns the table being queried has. If
+the xColumnCount() API is invoked from within the implementation of the
+auxiliary function my_aux_function in the example above, it returns 2.
+
+
Data From Current Row: xColumnText, xRowid
+
+
The xRowid API may be used to find the rowid value
+for the current row. The xColumnText may be used
+to obtain the text stored in a specified column of the current row.
+
+
Token Counts: xColumnSize, xColumnTotalSize
+
+
FTS5 divides documents inserted into an fts table into tokens. These are
+usually just words, perhaps folded to either upper or lower case and with any
+punctuation removed. For example, the default 
+unicode61 tokenizer tokenizes the text "The
+tokenizer is case-insensitive" to a list of 5 tokens - "the", "tokenizer", is",
+"case" and "insensitive". Exactly how tokens are extracted from text is 
+determined by the tokenizer.
+
+
The auxiliary functions API provides functions to query for both the number
+of tokens in a specified column of the current row (the 
+xColumnSize API), or for the number of tokens in a 
+specified column of all rows of the table (the xColumnTotalSize API). For the example at the
+top of this section, when visiting row 1, xColumnSize returns 2 for column 0
+and 3 for column 1. xColumnTotalSize returns 6 for column 0 and 9 for column 1
+regardless of the current row.
+
+
The Current Full-Text Query: xPhraseCount, xPhraseSize, xQueryToken
+
+
An FTS5 query contains one or more phrases. The 
+xPhraseCount, xPhraseSize
+and xQueryToken APIs allow an auxiliary function
+implementation to query the system for details of the current query. The
+xPhraseCount API returns the number of phrases in the current query. For
+example, if an FTS5 table is queried as follows:
+
+
SELECT my_aux_function(ft) FROM ft('ab AND "cd ef gh" OR ij + kl')
+

+
+
and the xPhraseCount() API invoked from within the implementation of the
+auxiliary function, it returns 3 (the three phrases being "ab", "ce ef gh" and
+"ij kl"). 
+
+
Phrases are numbered in order of appearance within a query starting from 0.
+The xPhraseSize() API may be used to query for the number of tokens in a
+specified phrase of the query. In the example above, phrase 0 contains 1 token,
+phrase 1 contains 3 tokens, and phrase 2 contains 2.
+
+
The xQueryToken API may be used to access the text of a specified token
+within a specified phrase of the query. Tokens are numbered within their
+phrases from left to right starting from 0. For example, if the xQueryToken
+API is used to request token 1 of phrase 2 in the example above, it returns
+the text "kl". Token 0 of phrase 0 is "ab".
+
+
Phrase Hits in the Current Row: xPhraseFirst, xPhraseNext
+
+
These two API functions may be used to iterate through the matches for
+a specified phrase of the query within the current row. Phrase matches are
+identified by the column and token offset within the current row. For
+example, say the following example table:
+
+
CREATE VIRTUAL TABLE ft2 USING fts5(x, y);
+INSERT INTO ft2(rowid, x, y) VALUES
+        (1, 'xxx one two xxx five xxx six', 'seven four'),
+        (2, 'five four four xxx six', 'three four five six four five six');
+

+
+
is queried with:
+
+
SELECT my_aux_function(ft2) FROM ft2(
+    '("one two" OR "three") AND y:four NEAR(five six, 2)'
+);
+

+
+
The query above contains 5 phrases - "one two", "three", "four", 
+"five" and "six". It matches all rows of the table, so the auxiliary 
+function is invoked for each row.
+
+
In row 1, for phrase 0, "one two", there is exactly one match to iterate
+through -  at column 0 token offset 1. The column number is 0 because the 
+match appears in the left most column. The token offset is 1 because there 
+is exactly one token ("xxx") before the phrase match in the column value.
+For phrase 1, "three", there are no matches. Phrase 2, "four", has one
+match, at column 1, token offset 0. Phrase 3, "five", has one match at
+column 0, token offset 4, and phrase 4, "six", has one match at column 0
+token offset 6.
+
+
The set of matches for each phrase in each row of the example is presented
+in the table below. Each match is notated as (column-number, token-offset):
+
+

+  
RowPhrase 0Phrase 1Phrase 2Phrase 3Phrase 4
+  
1(0, 1)    (1, 1)(0, 4)(0, 6)
+  
2(1,0)(1, 1), (1,4)(1, 2), (1, 5)(1, 3), (1, 6)
+

+
+
The second row is slightly more complicated. There were no occurrences of
+phrase 0. Phrase 1 ("three") appears once, at column 1 token offset 0. Although
+there are instances of phrase 2 ("four") in column 0, none of them are reported
+by the API, as phrase 4 has a column filter -
+"y:". Matches that are filtered out by column filters do not count. Similarly,
+although phrases 3 and 4 do occur in column "x" of row 2, they are filtered
+out by the NEAR filter. Matches that are
+filtered out by NEAR filters do not count either.
+
+
Phrase Hits in the Current Row (2): xInstCount, xInst
+
+
The xInstCount and xInst APIs
+provide access to the same information as the xPhraseFirst and xPhraseNext
+described above. The difference is that instead of iterating through the
+matches for a single, specified phrase, the xInstCount/xInst APIs collate
+all matches into a single flat array, sorted in order of occurrence within
+the current row. Elements of this array may then be accessed randomly.
+
+
Each array element consists of three values:
+
+
    
+  
  •  A phrase number,
+  
  •  A column number, and
+  
  •  A token offset
+

+
+
Using the same example data and query as for xPhraseFirst/xPhraseNext
+above, the array accessible via xInstCount/xInst consists of the following
+entries for each row:
+
+

+  
RowxInstCount/xInst array
+  
1(0, 0, 1), (3, 0, 4), (4, 0, 6), (2, 1, 1)
+  
2(1, 1, 0), (2, 1, 1), (3, 1, 2), (4, 1, 3), (2, 1, 4), (3, 1, 5), (4, 1, 6)
+

+
+
Each entry of the array is called a phrase match. Phrase matches are
+numbered in order, starting from 0. So, in the example above, in row 2, phrase
+match 3 is (4, 1, 3) - phrase 4 of the query matches at column 1, token offset
+3.
+
+

+
7.2.2. Custom Auxiliary Functions API Reference

 
 
struct Fts5ExtensionApi {
   int iVersion;                   /* Currently always set to 3 */
@@ -2443,8 +2627,8 @@ file of the source code.
 

 void *(*xUserData)(Fts5Context*)

 

-Return a copy of the context pointer the extension function was 
-   registered with.
+Return a copy of the pUserData pointer passed to the xCreateFunction()
+   API when the extension function was registered.
 

 

 

@@ -3090,9 +3274,9 @@ the subset of matches with rowids in the required range.
 
 
    
   
  •  The special structure record,
-       stored with id=1.
-  
  •  The special averages record,
        stored with id=10.
+  
  •  The special averages record,
+       stored with id=1.
   
  •  A record to store each segment b-tree
        leaf and doclist index leaf and
        internal node. See below for how id values are calculated for these
@@ -3612,7 +3796,7 @@ CREATE VIRTUAL TABLE t1 USING fts5(
 
 
     FTS5 has no matchinfo() or offsets() function, and the snippet() function
 is not as fully-featured as in FTS3/4. However, since FTS5 does provide
-an API allowing applications to create custom auxiliary functions, any
+an API allowing applications to create custom auxiliary functions, any
 required functionality may be implemented within the application code.
 
 
     The set of built-in auxiliary functions provided by FTS5 may be
@@ -3714,5 +3898,5 @@ using FTS5.
        the amount of processing that may take place within any given
        INSERT, UPDATE or DELETE operation.
 

-
This page last modified on  2024-03-27 20:33:18 UTC 

+
This page last modified on  2024-05-22 18:42:01 UTC 

 
diff --git a/www/index.html b/www/index.html
index 1bdbef9..55e8d59 100644
--- a/www/index.html
+++ b/www/index.html
@@ -189,7 +189,7 @@ is in the public-domain and is free to
 everyone to use for any purpose.
 
 
Latest Release

-Version 3.45.3 (2024-04-15).
+Version 3.46.0 (2024-05-23).
 Download
 Prior Releases
 
diff --git a/www/json1.html b/www/json1.html
index dacfb08..f768fd6 100644
--- a/www/json1.html
+++ b/www/json1.html
@@ -154,14 +154,15 @@ Table Of Contents
 
4.13. The jsonb_object() function

 
4.14. The json_patch() function

 
4.15. The jsonb_patch() function

-
4.16. The json_remove() function

-
4.17. The jsonb_remove() function

-
4.18. The json_type() function

-
4.19. The json_valid() function

-
4.20. The json_quote() function

-
4.21. Array and object aggregate functions

-
4.22. The json_each() and json_tree() table-valued functions

-
4.22.1. Examples using json_each() and json_tree()

+
4.16. The json_pretty() function

+
4.17. The json_remove() function

+
4.18. The jsonb_remove() function

+
4.19. The json_type() function

+
4.20. The json_valid() function

+
4.21. The json_quote() function

+
4.22. Array and object aggregate functions

+
4.23. The json_each() and json_tree() table-valued functions

+
4.23.1. Examples using json_each() and json_tree()
@@ -128,8 +156,8 @@ ANALYZE

1. Overview

analyze-stmt: -

-
+

+
@@ -181,7 +209,7 @@ ANALYZE indices and stores the collected information in internal tables of the database where the query optimizer can access the information and use it to help make better query planning choices. -If no arguments are given, all attached databases are +If no arguments are given, the main database and all attached databases are analyzed. If a schema name is given as the argument, then all tables and indices in that one database are analyzed. If the argument is a table name, then only that table and the @@ -190,24 +218,121 @@ is an index name, then only that one index is analyzed.

- +

2. Recommended usage patterns

+ +

The use of ANALYZE is never required. However, if an application +makes complex queries that have many possible query plans, the query +planner will be better able to pick the best plan if ANALYZE has +been run. This can result it significant performance improvements for +some queries. + +

Two recommended approaches for when and how to run ANALYZE are +described in the next subsections, in order of preference. + + + +

2.1. Periodically run "PRAGMA optimize"

+ +

The PRAGMA optimize command will automatically run ANALYZE when +needed. Suggested use: + +

    +

  1. +Applications with short-lived database connections should run +"PRAGMA optimize;" once, just prior to closing each database connection. + +

  2. +Applications that use long-lived database connections should run +"PRAGMA optimize=0x10002;" when the connection is first opened, and then +also run "PRAGMA optimize;" periodically, perhaps once per day, or more if +the database is evolving rapidly. + +

  3. All applications should run "PRAGMA optimize;" after a schema change, +especially after one or more CREATE INDEX statements. +

+ +

+ +

The PRAGMA optimize command is usually a no-op but it will occasionally +run one or more ANALYZE subcommands on individual tables of the database +if doing so will be useful to the query planner. +Since SQLite version 3.46.0 (2024-05-23), the "PRAGMA optimize" command +automatically limits the scope of ANALYZE subcommands so that +the overall "PRAGMA optimize" command completes quickly even on enormous +databases. There is no need to use PRAGMA analysis_limit. This is the +recommended way of running ANALYZE moving forward. + +

The PRAGMA optimize command will normally only consider running ANALYZE on +tables that have been previously queried by the same database connection or +that do not have entries in the sqlite_stat1 table. +However, if the 0x10000 bit is added to the argument, PRAGMA optimize will +examine all tables to see if they can benefit from ANALYZE, not just those +that have been recently queried. +There is no query history when a database connection first opens, and +that is why adding the 0x10000 bit is recommended when running PRAGMA optimize +on a fresh database connection. + +

See the Automatically Running ANALYZE and +Approximate ANALYZE For Large Databases sections below for additional +information. + + + +

2.2. Fixed results of ANALYZE

+ +

Running ANALYZE can cause SQLite to choose different query plans +for subsequent queries. This is almost always a positive thing, as the +query plans chosen after ANALYZE will in nearly every case be better than +the query plans picked before ANALYZE. That is the whole point of ANALYZE. +But there can be no proof of running ANALYZE will always be beneficial. +One can construct pathological cases where running +ANALYZE could make some subsequent queries run slower. + +

Some developers prefer that once the design of an application is frozen, +SQLite will always pick the same query plans as it did during +development and testing. +Then if a millions of copies of the application are shipped to customers, +the developers are assured that all of those millions of copies are running +the same query plans regardless of what data the individual customers insert +into their particular databases. This can help in reproducing complaints +of performance problems coming back from the field. + +

To achieve this objection, never run a full ANALYZE nor the +"PRAGMA optimize" command in the application. +Rather, only run ANALYZE during development, manually using the +command-line interface or similar, on a test database +that is similar in size and content to live databases. Then capture +the result of this one-time ANALYZE using a script like the +following: + +

.mode list
+SELECT 
+  'ANALYZE sqlite_schema;' ||
+  'DELETE FROM sqlite_stat1;' ||
+  'INSERT INTO sqlite_stat1(tbl,idx,stat)VALUES' ||
+  (SELECT group_concat(format('(%Q,%Q,%Q)',tbl,idx,stat),',')
+    FROM sqlite_stat1) ||
+  ';ANALYZE sqlite_schema;';
+
-

Applications with long-lived databases that use complex -queries should consider running the following commands just prior -to closing each database connection: +

When creating a new instance of the database in deployed instances of +the application, or perhaps every time the application is started up in +the case of long-running applications, run the commands generated by +script above. This will populate the sqlite_stat1 table exactly as +it was during development and testing and ensure that the query plans +selected in the field are same has those selected during testing in the +lab. Maybe copy/paste the string generated by the script above into +a static string constant named "zStat1Init" and then invoke: -

PRAGMA analysis_limit=400;
-PRAGMA optimize;
+
sqlite3_exec(db, zStat1Init, 0, 0, 0);
 

 
-
The optimize pragma is usually a no-op but it will occasionally
-run ANALYZE if it seems like doing so will be useful to the query planner.
-The analysis_limit pragma limits the scope of any ANALYZE command that
-the optimize pragma runs so that it does not consume too many CPU cycles.
-The constant "400" can be adjusted as needed.  Values between 100 and
-1000 work well for most applications.
+
Perhaps also add "BEGIN;" at the start of the string constant and
+"COMMIT;" at the end, depending on the context in which the script is run.
 
-
2. Details

+
See the query planner stability guarantee for addition information.
+
+
3. Details

 
 
 The default implementation stores all statistics in a single
 table named "sqlite_stat1".  
@@ -238,7 +363,7 @@ ANALYZE command.
 See "Manual Control Of Query Plans Using SQLITE_STAT Tables" for
 further information.

 
-
 Statistics gathered by ANALYZE are not automatically updated as
+
 Statistics gathered by ANALYZE are not updated as
 the content of the database changes.  If the content of the database
 changes significantly, or if the database schema changes, then one should
 consider rerunning the ANALYZE command in order to update the statistics.

@@ -252,12 +377,15 @@ can force the query planner to reread the statistics tables by running
 
 
 
-
3. Automatically Running ANALYZE

+
4. Automatically Running ANALYZE

 
 
The PRAGMA optimize command will automatically run ANALYZE on individual
 tables on an as-needed basis.  The recommended practice is for applications
 to invoke the PRAGMA optimize statement just before closing each database
-connection.

+connection.  Or, if the application keeps a single database connection open
+for a long time, then it should run "PRAGMA optimize=0x10002" when the
+connection is first opened and run "PRAGMA optimize;" periodically thereafter,
+perhaps once per day or even once per hour.

 
 
Each SQLite database connection records cases when the query planner would
 benefit from having accurate results of ANALYZE at hand.  These records
@@ -274,22 +402,23 @@ is recommended that PRAGMA optimize be
 is closing and has thus had an opportunity to accumulate as much usage information
 as possible.  It is also reasonable to set a timer to run PRAGMA optimize
 every few hours, or every few days, for database connections that stay open
-for a long time.

-
-
Applications that desire more control can run PRAGMA optimize(0x03) to 
-obtain a list of ANALYZE commands that SQLite thinks are appropriate to run,
-but without actually running those commands.  If the returned set is 
-non-empty, the application can then make a decision about whether or not
-to run the suggested ANALYZE commands, perhaps after prompting the user
-for guidance.

+for a long time.  When running PRAGMA optimize immediately after a
+database connection is opened, one can add the 0x10000 bit to the bitmask
+argument (thus making the command read "PRAGMA optimize=0x10002") which
+causes all tables to be examined, even tables that have not been
+queried during the current connection.

 
 
The PRAGMA optimize command was first introduced with 
 SQLite 3.18.0 (2017-03-28) and is a no-op for all prior releases
-of SQLite.

+of SQLite.  The PRAGMA optimize command was significantly enhanced
+in SQLite 3.46.0 (2024-05-23) and the advice given in this
+documentation is based on those enhancements.  Applications that
+use earlier versions of SQLite should consult the corresponding
+documentation for better advice on the best ways to use PRAGMA optimize.

 
 
 
-
4. Approximate ANALYZE For Large Databases

+
5. Approximate ANALYZE For Large Databases

 
 
By default, ANALYZE does a full scan of every index.  This can be slow for
 large databases.  So beginning with SQLite version 3.32.0 (2020-05-22), the
@@ -335,16 +464,19 @@ is noticeably inferior to a full-scan ANALYZE, but such cases are rare in
 real-world problems.
 
 
A good rule of thumb seems to be to always set "PRAGMA analysis_limit=N"
-for N between 100 and 1000 prior to running either "ANALYZE" or
-"PRAGMA optimize".  The results are not quite as precise, but they
-are precise enough, and the fact that the results are computed so much
-faster means that developers are more likely to compute them.  An
-approximate ANALYZE is better than not running ANALYZE at all.
+for N between 100 and 1000 prior to running either "ANALYZE".  It used
+to be that this was also recommended  prior to running
+"PRAGMA optimize", but since version 3.46.0 (2024-05-23) that
+happens automatically.  The results are not quite as precise when using
+PRAGMA analysis_limit, but they are precise enough, and the fact that
+the results are computed so much faster means that developers are more
+likely to compute them.  An approximate ANALYZE is better than not
+running ANALYZE at all.
 
-
4.1. Limitations of approximate ANALYZE

+
5.1. Limitations of approximate ANALYZE

 
 
The content in the sqlite_stat4 table cannot be computed with
 anything less than a full scan.  Hence, if a non-zero analysis limit
 is specified, the sqlite_stat4 table is not computed.
-
This page last modified on  2024-02-21 13:43:47 UTC 

+
This page last modified on  2024-05-05 15:23:53 UTC 

 
diff --git a/www/lang_datefunc.html b/www/lang_datefunc.html
index 24d0338..7f4ff38 100644
--- a/www/lang_datefunc.html
+++ b/www/lang_datefunc.html
@@ -126,7 +126,6 @@ Date And Time Functions
 Table Of Contents
 
 
1. Overview

-
1.1. Timediff()

 
2. Time Values

 
3. Modifiers

 
4. Examples

@@ -169,47 +168,63 @@ SQLite supports seven scalar date and time func
 
 
 

-The first six date and time functions take an optional time value as an argument, followed
-by zero or more modifiers.
+The first six date and time functions take an optional time-value as an argument, followed
+by zero or more modifiers.
 The strftime() function also takes a format string as its first argument.
-The timediff() function takes exactly two arguments which are both time values.
+The timediff() function takes exactly two arguments which are both time-values.
 

 
 

-Date and time values can be stored as
-
    
-
  •  text in a subset of the ISO-8601 format,
-
  •  numbers representing the Julian day, or
-
  •  numbers representing the number of seconds since (or before) 1970-01-01 00:00:00 UTC
-     (the unix timestamp).
-

+SQLite does not have a dedicated date/time datatype.  Instead,
+date and time values can stored as any of the following:
+
+

+
+
+
+
+
ISO-8601
+A text string that is an ISO 8601 date/time value.
+Example: '2025-05-29 14:16:00'
+
Julian day number
+The number of days including fractional days since -4713-11-24 12:00:00
+Example: 2460825.09444444
+
Unix timestamp
+The number of seconds including fractional seconds since 1970-01-01 00:00:00
+Example: 1748528160
+

+

 
 

-All of the date time functions access time-values as either ISO-8601 strings or
-Julian day numbers.  They also access unix timestamps with optional arguments
-(the 'auto' and 'unixepoch' modifiers described below).  Since the timediff()
-function does not accept any optional argument, it can only use ISO-8601 and
-Julian day number time values.
+These three formats are collectively known as time-values.
+All of the date time functions accept time-values as either ISO-8601 text or
+as Julian day numbers.  They can also be made to accept unix timestamps by
+adding optional modifiers arguments 'auto' or
+'unixepoch'.
+Since the timediff() function does not accept modifiers,
+it can only use ISO-8601 and julian day number time-values.
 
 
 
 

-The date() function returns the date as text in this format: YYYY-MM-DD.
+The date() function returns the date as text in this format: YYYY-MM-DD.
 
 
 
 

-The time() function returns the time as text in this format: HH:MM:SS.
+The time() function returns the time as text in formatted as HH:MM:SS or as HH:MM:SS.SSS if
+the subsec modifier is used.
 
 
 
 

-The datetime() function returns the date and time as text in this formats: YYYY-MM-DD HH:MM:SS.
+The datetime() function returns the date and time formatted as YYYY-MM-DD HH:MM:SS or
+as YYYY-MM-DD HH:MM:SS.SSS if the subsec modifier is used.
 
 
 
 

-The julianday() function returns the 
+The julianday() function returns the 
 Julian day - the
 fractional number of days since noon in Greenwich on November 24, 4714 B.C. 
 (Proleptic Gregorian calendar).
@@ -217,7 +232,7 @@ fractional number of days since noon in Greenwich on November 24, 4714 B.C.
 
 
 

-The unixepoch() function returns a unix timestamp - the number of seconds
+The unixepoch() function returns a unix timestamp - the number of seconds
 since 1970-01-01 00:00:00 UTC.  The unixepoch() function normally returns
 an integer number of seconds, but with the optional subsec modifier it
 will return a floating point number which is the fractional number of seconds.
@@ -225,22 +240,27 @@ will return a floating point number which is the fractional number of seconds.
 
 
 

-The strftime() routine returns the date formatted according to 
+The strftime() function returns the date formatted according to 
 the format string specified as the first argument.
 The format string supports the most common substitutions found in the 
 strftime() function
 from the standard C library plus two new substitutions, %f and %J.
-The following is a complete list of valid strftime() substitutions:
+The following is a complete list of valid strftime() substitutions
+as of version 3.46.0 (2024-05-23).  Earlier versions of SQLite
+might not support all substitutions.  If an undefined or unsupported
+substitution is seen, the result is NULL.
 

 
 

 
 
-

 
 %d  day of month: 00-31
-
 %e  day of month without leading zero: 0-31
+
 %d  day of month: 01-31
+
 %e  day of month without leading zero: 1-31
 
 %f  fractional seconds: SS.SSS
 
 %F  ISO 8601 date: YYYY-MM-DD
+
 %G  ISO 8601 year corresponding to %V
+
 %g  2-digit ISO 8601 year corresponding to %V
 
 %H  hour: 00-24
 
 %I  hour for 12-hour clock: 01-12
 
 %j  day of year: 001-366
@@ -255,9 +275,11 @@ The following is a complete list of valid strftime() substitutions:
 
 %s  seconds since 1970-01-01
 
 %S  seconds: 00-59
 
 %T  ISO 8601 time: HH:MM:SS
+
 %U  week of year (00-53) - week 01 starts on the first Sunday
 
 %u  day of week 1-7 with Monday==1
+
 %V  ISO 8601 week of year
 
 %w  day of week 0-6 with Sunday==0
-
 %W  week of year: 00-53
+
 %W  week of year (00-53) - week 01 starts on the first Monday
 
 %Y  year: 0000-9999
 
 %%  %
 

@@ -270,12 +292,12 @@ in terms of strftime():
 
 

 
-
FunctionEquivalent (or nearly) strftime()
+
FunctionEquivalent strftime()
 
   date(...)        strftime('%F', ...)
 
   time(...)        strftime('%T', ...)
 
   datetime(...)    strftime('%F %T', ...)
-
   julianday(...)   strftime('%J', ...) -- (numeric return)
-
   unixepoch(...)   strftime('%s', ...) -- (numeric return)
+
   julianday(...)   CAST(strftime('%J', ...) as REAL)
+
   unixepoch(...)   CAST(strftime('%s', ...) as INT)
 

 

 
@@ -296,10 +318,8 @@ of the '%J' or '%s' format specifiers with the strftime() function.
 
 
 
-
1.1. Timediff()

-
 

-The timediff(A,B) routine returns a string that describes the amount
+The timediff(A,B) function returns a string that describes the amount
 of time that must be added to B in order to reach time A.  The format of
 the timediff() result is designed to be human-readable.  The format is:
 

@@ -307,7 +327,7 @@ the timediff() result is designed to be human-readable.  The format is:
 

 

 This time difference string is also an allowed modifier for the other
-date/time functions.  The following invariant holds for time values A
+date/time functions.  The following invariant holds for time-values A
 and B:
 

 datetime(A) = datetime(B, timediff(A,B))
@@ -342,9 +362,11 @@ between two julianday() or unixepoch() calls.
 

 
 
+
+
 
2. Time Values

 
-
A time value can be in any of the following formats shown below.
+
A time-value can be in any of the following formats shown below.
 The value is usually a string, though it can be an integer or floating
 point number in the case of format 12.
 
@@ -378,7 +400,7 @@ Format 12 is the
 Julian day number
 expressed as an integer or floating point value.
 Format 12 might also be interpreted as a unix timestamp if it is immediately followed
-either the 'auto' or 'unixepoch' modifier.
+either the 'auto' or 'unixepoch' modifier.
 

 
 

@@ -386,7 +408,7 @@ Formats 2 through 10 may be optionally followed by a timezone indicator of the f
 "[+-]HH:MM" or just "Z".  The date and time functions use UTC or "zulu"
 time internally, and so the "Z" suffix is a no-op.  Any non-zero "HH:MM" suffix is
 subtracted from the indicated date and time in order to compute zulu time.
-For example, all of the following time values are equivalent:
+For example, all of the following time-values are equivalent:
 

 
 

@@ -417,9 +439,9 @@ value of 'now' is assumed.
 
3. Modifiers

 
 
For all date/time functions other than timediff(),
-the time value argument can be followed by zero or more modifiers that 
+the time-value argument can be followed by zero or more modifiers that 
 alter date and/or time. Each modifier
-is a transformation that is applied to the time value to its left.
+is a transformation that is applied to the time-value to its left.
 Modifiers are applied from left to right; order is important.
 The available modifiers are as follows.

 
@@ -437,17 +459,19 @@ The available modifiers are as follows.

 
  •  ±YYYY-MM-DD HH:MM
 
  •  ±YYYY-MM-DD HH:MM:SS
 
  •  ±YYYY-MM-DD HH:MM:SS.SSS
-
  •  start of month
-
  •  start of year
-
  •  start of day
-
  •  weekday N
-
  •  unixepoch
-
  •  julianday
-
  •  auto
-
  •  localtime
-
  •  utc
-
  •  subsec
-
  •  subsecond
+
  •  ceiling
+
  •  floor
+
  •  start of month
+
  •  start of year
+
  •  start of day
+
  •  weekday N
+
  •  unixepoch
+
  •  julianday
+
  •  auto
+
  •  localtime
+
  •  utc
+
  •  subsec
+
  •  subsecond
 
    • 
 
 
    The first thirteen modifiers (1 through 13) 
@@ -455,53 +479,58 @@ add the specified amount of time to the date and time
 specified by the arguments to its left.
 The 's' character at the end of the modifier names in 1 through 6 is optional.
 The NNN value can be any floating point number, with an optional '+' or '-' prefix.
-Note that "±NNN months" works by rendering the original date into
-the YYYY-MM-DD format, adding the ±NNN to the MM month value, then
-normalizing the result.  Thus, for example, the date 2001-03-31 modified
-by '+1 month' initially yields 2001-04-31, but April only has 30 days
-so the date is normalized to 2001-05-01.  A similar effect occurs when
-the original date is February 29 of a leapyear and the modifier is
-±N years where N is not a multiple of four.
    
 
 
 
-
    The time shift modifiers (7 through 13) move the time value by the
+
    The time shift modifiers (7 through 13) move the time-value by the
 number of years, months, days, hours, minutes, and/or seconds specified.
 An initial "+" or "-" is required for formats 10 through 13 but is optional
 for formats 7, 8, and 9.  The changes are applies from left to right.
 First the year is shifted by YYYY, then the month by MM, and then day
-by DD, and so forth.  The normalization and rounding due to differing month
-lengths and leap years is applied after each step.  The
+by DD, and so forth.  The
 timediff(A,B) function returns a time shift in format 13 that shifts
-the time value B into A.
    
-
-
    The "start of" modifiers (14 through 16) shift the date backwards 
+the time-value B into A.
    
+
+
+
+
    Because the length of a month or year changes from one month or year
+to the next, ambiguities can arise when shifting a date by months and/or years.
+For example, what is the date one year after 2024-02-29?  Is it 2025-02-28 
+or 2025-03-01? Or what is the date that is two months after 2023-12-31?
+Is it 2024-02-29 or 2024-03-02?  There is no consensus on how to resolve
+this ambiguity, so the "ceiling" and "floor" modifiers
+(14 and 15) are available to
+let the programmer decide.  If the next modifier after a time shift is
+"ceiling", then any ambiguity in the date is resolved by choosing the
+later date.  The "floor" modifier resolves ambiguities
+by resolving to the last day of the previous month.  The default
+behavior is "ceiling".
+
+
    The "start of" modifiers (16 through 18) shift the date backwards 
 to the beginning of the subject month, year or day.
    
 
-
    The "weekday" modifier advances the date forward, if necessary,
+
    The "weekday" modifier advances the date forward, if necessary,
 to the next date where the weekday number is N. Sunday is 0, Monday is 1,
 and so forth.
 If the date is already on the desired weekday, the "weekday" modifier
 leaves the date unchanged.  
    
 
-
    The "unixepoch" modifier (18) only works if it immediately follows 
-a time value in the DDDDDDDDDD format. 
+
+
+
    The "unixepoch" modifier (20) only works if it immediately follows 
+a time-value in the DDDDDDDDDD format. 
 This modifier causes the DDDDDDDDDD to be interpreted not 
 as a Julian day number as it normally would be, but as
 Unix Time - the 
 number of seconds since 1970.  If the "unixepoch" modifier does not
-follow a time value of the form DDDDDDDDDD which expresses the number
+follow a time-value of the form DDDDDDDDDD which expresses the number
 of seconds since 1970 or if other modifiers
 separate the "unixepoch" modifier from prior DDDDDDDDDD then the
 behavior is undefined.
-For SQLite versions before 3.16.0 (2017-01-02), 
-the "unixepoch" modifier only works for
-dates between 0000-01-01 00:00:00 and 5352-11-01 10:52:47 (unix times
-of -62167219200 through 106751991167).
    
 
 
 
-
    The "julianday" modifier must immediately follow the initial
+
    The "julianday" modifier must immediately follow the initial
 time-value which must be of the form DDDDDDDDD.  Any other use of
 the 'julianday' modifier is an error and causes the function to return NULL.
 The 'julianday' modifier forces the time-value number to be interpreted
@@ -512,7 +541,7 @@ a NULL to be returned if any other time-value format is used.
 
 
 
-
    The "auto" modifier must immediately follow the initial time-value.
+
    The "auto" modifier must immediately follow the initial time-value.
 If the time-value is numeric (the DDDDDDDDDD format) then the 'auto'
 modifier causes the time-value to interpreted as either a julian day
 number or a unix timestamp, depending on its magnitude.  If the value
@@ -523,35 +552,36 @@ values outside of the range of valid julian day numbers, but within
 the range of -210866760000 to 253402300799, the 'auto' modifier causes
 the value to be interpreted as a unix timestamp.  Other numeric values
 are out of range and cause a NULL return.  The 'auto' modifier is a no-op 
-for text time-values.
-
-
    The 'auto' modifier can be used to work with date/time values even in
-cases where it is not known if the julian day number or unix timestamp
-formats are in use.  The 'auto' modifier will automatically select the
-appropriate format.  However, there is a region of ambiguity.  Unix
+for ISO 8601 text time-values.
+The "auto" modifier is designed to work with time-values even in
+cases where it is not known which time-value format
+is stored in the database file, or in cases where the same column
+stores time-values in different formats on different rows.
+The 'auto' modifier will automatically select the
+appropriate format.  However, there is some ambiguity.  Unix
 timestamps for the first 63 days of 1970 will be interpreted as julian
 day numbers.  The 'auto' modifier is very useful when the dataset is
-guaranteed to not contain any dates within that region, but should be
+guaranteed to contain no dates within that range, but should be
 avoided for applications that might make use of dates in the opening
 months of 1970.
 
 
 
-
    The "localtime" modifier (21) assumes the time value to its left is in
+
    The "localtime" modifier assumes the time-value to its left is in
 Universal Coordinated Time (UTC) and adjusts that time
 value so that it is in localtime.  If "localtime"
 follows a time that is not UTC, then the behavior is undefined.
-The "utc" modifier is the opposite of "localtime".  
-"utc" assumes that the time value
-to its left is in the local timezone and adjusts that time value to be in UTC.
+The "utc" modifier is the opposite of "localtime".  
+"utc" assumes that the time-value
+to its left is in the local timezone and adjusts that time-value to be in UTC.
 If the time to the left is not in localtime, then the result of "utc" is
 undefined.
    
 
 
 
 
    
-The "subsecond" modifier (which may be abbreviated as just
-"subsec") increases the resolution of the output for
+The "subsecond" modifier (which may be abbreviated as just
+"subsec") increases the resolution of the output for
 datetime(), time(), and unixepoch(), and for the "%s"
 format string in strftime().  The "subsecond"
 modifier has no effect on other date/time functions.
@@ -563,12 +593,10 @@ followed by a decimal point and one or more digits to show
 fractional seconds.  When "subsec" is used with unixepoch(),
 the result is a floating point value which is the number of
 seconds and fractional seconds since 1970-01-01.
-
-
    
 The "subsecond" and "subsec" modifiers have the special property
 that they can occur as the first argument to date/time functions
 (or as the first argument after the format string for strftime()).
-When this happens, the time value that is normally in the first
+When this happens, the time-value that is normally in the first
 argument is understood to be "now".  For example, a short cut to
 get the current time in seconds since 1970 with millisecond
 precision is to say:
@@ -674,5 +702,5 @@ getting results correct back to 1986, when the rules were also changed.
    
 Gregorian calendar
 system.  They also assume that every
 day is exactly 86400 seconds in duration; no leap seconds are incorporated.
    
-
+
    This page last modified on  2024-04-16 16:29:07 UTC 
    
 
diff --git a/www/lang_expr.html b/www/lang_expr.html
index e2ea564..7edca23 100644
--- a/www/lang_expr.html
+++ b/www/lang_expr.html
@@ -163,8 +163,8 @@ mk.innerHTML = "►";
 
    1. Syntax
    
 
 
    expr:
-
    
- 
    
+
    
+ 
    
  
    
 
 
@@ -645,8 +645,8 @@ mk.innerHTML = "►";
 
 
    
 
    filter-clause:
-
    
-