Fixes and new tests logic to ensure that the btree overflow page cache is
only used when it is consistent. This resolves the malfunction observed
in [forum:/forumpost/284955a3cd454a15|forum post 284955a3cd454a15].
The read-only CHECK-constraint optimization of [34ddf02d3d21151b] inhibits the
xfer optimization for tables with CHECK constraints. However, the xfer
optimization is required for correct operation of VACUUM INTO on tables that
contain generated columns. Fix this by ignoring CHECK constraints when
qualifying the xfer optimization while running VACUUM. Problem reported by
[forum:/forumpost/3ec177d68fe7fa2c|forum post 3ec177d68fe7fa2c].
Check-in [a9657c87c53c1922] is wrong: the IndexedExpr.bMaybeNullRow flag is
required for virtual columns if they are part of an outer join. Add a
test case (derived from dbsqlfuzz b9e65e2f110df998f1306571fae7af6c01e4d92b)
to prove it.
When compiling with SQLITE_ALLOW_ROWID_IN_VIEW, if the RETURNING clause of
an UPDATE of a view specifies a rowid, then return NULL for the value of
that rowid. dbsqlfuzz 7863696e9e5ec10b29bcf5ab2681cd6c82a78a4a.
stephan [Thu, 4 Apr 2024 22:53:09 +0000 (22:53 +0000)]
Optimize sqlite3.oo1.DB.exec() for the rowMode='object' case to avoid converting the object property keys (column names) from native code to JS for each row. This speeds up large data sets considerably and addresses the report in [forum:3632183d2470617d|forum post 3632183d2470617d].
Add the "interstage-heuristic" that attempts to avoid wildly inefficient
queries that use table scans instead of index lookups because the output
row estimates are inaccurate.
Fix typos in comments. Provided ".wheretrace" debugging output for the
interstage heuristic module. Do omit automatic index loops in the
interstage heuristic.
Add a heuristic in between the two solver() passes of the query planner that
tries to prevent a very slow query plan in cases where the output row count
estimate is imprecise.
drh [Mon, 25 Mar 2024 20:35:14 +0000 (20:35 +0000)]
The RAISE() operator is not a constant expression and cannot participate in
the VALUE-as-coroutine optimization.
dbsqlfuzz 74cf7c9904360322a6c917e4934b127543d1cd51
drh [Mon, 25 Mar 2024 18:24:28 +0000 (18:24 +0000)]
Revert the previous change. Instead, do a pre-check of the CREATE TABLE
statement that is the second argument to sqlite3_declare_vtab() and if
the first two keywords are not "CREATE" and "TABLE", then raise an
SQLITE_MISUSE error.
stephan [Mon, 25 Mar 2024 10:28:10 +0000 (10:28 +0000)]
Flag sqlite3_trace() and sqlite3_profile() as deprecated so that the doc generator for funclist.html sees them as such, and add 'Deprecated' to their page's title for consistency with other deprecated APIs. Comment changes only. Addresses [forum:0901025836|forum post 0901025836].
drh [Sun, 24 Mar 2024 21:10:45 +0000 (21:10 +0000)]
Fix the xBestIndex method of the pragma virtual table so that it correctly
gives a higher cost to plans where the schema hidden parameter is
unconstrained. Fix for the problem reported by
[forum:/forumpost/85b6a8b6705fb77a|forum post 85b6a8b6705fb77a].
stephan [Thu, 21 Mar 2024 15:52:52 +0000 (15:52 +0000)]
Add comments (only, no code) to the JS sqlite3_config() bindings reminding us why SQLITE_CONFIG_ROWID_IN_VIEW is specifically not included. JNI does not need this treatment because it explicitly supports only a very small subset of config options.
drh [Thu, 21 Mar 2024 11:46:52 +0000 (11:46 +0000)]
Enhancements to testrunner.tcl:
(1) Add the "--config CONFIGS" option, to that only configuration named
on the comma-separated list CONFIGS are run.
(2) Add the "--omit CONFIGS" to omit configuration on the CONFIGS list
(3) Add the Android configuration to "release"
dan [Wed, 20 Mar 2024 16:56:12 +0000 (16:56 +0000)]
Change EXPLAIN QUERY PLAN output to say "USE TEMP B-TREE FOR LAST TERM OF ORDER BY", or "LAST N TERMS OF ORDER BY", instead of "RIGHT PART OF ORDER BY".
dan [Wed, 20 Mar 2024 16:27:08 +0000 (16:27 +0000)]
Change EXPLAIN QUERY PLAN output to say "USE TEMP B-TREE FOR LAST TERM OF ORDER BY", or "LAST N TERMS OF ORDER BY", instead of "RIGHT PART OF ORDER BY".
drh [Wed, 20 Mar 2024 10:45:13 +0000 (10:45 +0000)]
Fix the SQLITE_ALLOW_ROWID_IN_VIEW bug-compatibility option so that it works
again. Add the SQLITE_CONFIG_ROWID_IN_VIEW sqlite3_config() value
to optionally disable the bug-compatibility mode at start-time. Enable
testing of SQLITE_ALLOW_ROWID_IN_VIEW.
drh [Tue, 19 Mar 2024 23:01:56 +0000 (23:01 +0000)]
Change the SQLITE_CONFIG_NO_ROWID_IN_VIEW configuration option to be
just SQLITE_CONFIG_ROWID_IN_VIEW (without the "_NO_" in the middle) and give
it the ability to turn the option on and off. Otherwise, it is difficult
to test.
drh [Tue, 19 Mar 2024 16:34:32 +0000 (16:34 +0000)]
In the name resolver when SQLITE_ALLOW_ROWID_IN_INDEX is enabled, if there
are multiple views that might resolve to the "rowid" but only one real table,
then use that one real table and ignore the views.
drh [Tue, 19 Mar 2024 14:38:39 +0000 (14:38 +0000)]
Fix the fuzzinvariants.c test module so that it does not generate
invariants that depend on rowid when SQLITE_ALLOW_ROWID_IN_VIEW is used,
since rowid values can be unreliable in that context.
drh [Tue, 19 Mar 2024 13:31:54 +0000 (13:31 +0000)]
When compiled with SQLITE_ALLOW_ROWID_IN_VIEW, rowid-in-view is on by default
but can now be turned off using SQLITE_TESTCTRL_ROWID_IN_VIEW. Without the
compile-time option, rowid-in-view is always off.
drh [Tue, 19 Mar 2024 10:16:17 +0000 (10:16 +0000)]
Fix another problem from [c63e26e705f5e967] involving infinities and the
Kahan-Babushka-Neumaier summation algorithm. Problem reported by
[forum:/forumpost/23b8688ef4|forum post 23b8688ef4].
drh [Tue, 19 Mar 2024 02:30:24 +0000 (02:30 +0000)]
First steps toward getting -DSQLITE_ALLOW_ROWID_IN_VIEW to work again. That
compile-time option is untested, undocumented, and unsupported. But it was
mentioned in the release notes for version 3.36.0, so I think that means we
need to support it forever.
drh [Mon, 18 Mar 2024 18:00:17 +0000 (18:00 +0000)]
Add the "VALUES-as-coroutine" optimization. Large VALUES clauses on an
INSERT, for example, prepare and run in about half the time and with half
the memory. This check-in also includes enhancements to the internal
sqlite3ExprIsConstant() routine to recognize pure SQL functions as constant
if they have constant arguments.