drh [Fri, 3 May 2024 19:35:43 +0000 (19:35 +0000)]
The coalesce(), ifnull(), and iif() functions pass through subtype values
from their arguments, and hence need to have the SQLITE_RESULT_SUBTYPE flag
set. This fixes an corner-case for the patch at [ba789a7804ab96d8].
drh [Thu, 2 May 2024 12:14:31 +0000 (12:14 +0000)]
Fix the CREATE TABLE performance issue described by
[forum:/forumpost/4cf69794d9dfff7c|forum thread 4cf69794d9dfff7c] in two
different ways: (1) Omit the call to PRAGMA integrity_check('X') that was
being done after CREATE TABLE "X" because the result was being ignored and
the integrity_check was not doing anything other than burning CPU cycles.
(2) Do not interpret the argument to PRAGMA integrity_check as a number if it
is in fact a string that looks like a number.
drh [Thu, 2 May 2024 11:52:31 +0000 (11:52 +0000)]
Do not accept a string that looks like a number used as an argument to
PRAGMA integrity_check as a number. Treat it as a table name that just
happens to look like a number.
drh [Thu, 2 May 2024 11:51:26 +0000 (11:51 +0000)]
Omit the OP_SqlExec to "PRAGMA integrity_check" added by [348fa7aaf7958b3f]
because it is a no-op. Even if the integrity_check failes, the CREATE TABLE
is stull successful. The OP_SqlExec just burns CPU cycles for no reason.
Fix issues in [/info/1e227ad9f413227f|LIMIT/OFFSET support for virtual tables].
The first problem was reported by
[forum:/forumpost/c243b8f856|forum post c243b8f856]. That report prompted
an enhancement to the generate_series() (also included in this merge) which
in turn identified other similar issues.
Enhance the generated_series() table-valued-function to respond to
LIMIT and OFFSET. Use this to add new test cases for LIMIT and OFFSET
on virtual tables in a compound SELECT.
Improvement to the way that affinity is determined for columns of a
compound subquery. The affinity is the affinity of the left-most
arm of the compound subquery that has an affinity other than NONE, adjusted
to accommodate the data types coming out of the other arms.
Further improvements to the computation of affinity for compound subqueries:
make sure that the selected affinity is compatible with a literal values in
arms to the left of the arm that is used to determine affinity.
Back out the previous change on this branch. In its place: Determine the
affinity of a subquery by the left-most arm of the subquery that has an
affinity other than NONE. In other words, scan from left to right looking
for an arm of the compound subquery with an affinity of BLOB, TEXT, INTEGER,
or REAL and pick the first one found. Or stay with NONE if no arm has a
defined affinity. Test cases added.
stephan [Thu, 25 Apr 2024 06:52:19 +0000 (06:52 +0000)]
wasm: add a makefile comment about the static sqlite3.h/c version info injected into the JS files possibly differing from the runtime-emited version info when a user provides their own sqlite3.c.
If a RETURNING clause contains a subquery that references the table that is
being updated in the statement that includes the RETURNING clause, then mark
the subquery as correlated sot hat it is recomputed for each result and not
just computed once and reused. See
[forum:/forumpost/2c83569ce8945d39|forum post 2c83569ce8945d39].
Ensure that temporary SrcItem objects created by trigger processing have
either SrcItem.zName or SrcItem.pSelect defined. Every SrcItem should have
one or the other.
stephan [Mon, 22 Apr 2024 16:46:37 +0000 (16:46 +0000)]
Extend the JS/WASM SEE build support by (A) filtering SEE-related bits out of the JS when not building with SEE and (B) accepting an optional key/textkey/hexkey option to the sqlite3.oo1.DB and subclass constructors to create/open SEE-encrypted databases with. Demonstrate SEE in the test app using the kvvfs. This obviates the changes made in [5c505ee8a7].
stephan [Mon, 22 Apr 2024 11:48:03 +0000 (11:48 +0000)]
For sqlite3.oo1.DB JavaScript classes, bypass execution of any on-open() SQL in SEE-capable builds because it would necessarily run before the client has an opportunity to provide their decryption key, which would leave the db handle in an unusable state and cause the ctor to throw. This currently affects only the OPFS VFSes. We may want to consider extending the ctor options object to optionally accept an SEE key and apply it when opening the db.
Continuation of the fix at [8c0f69e0e4ae0a44]: If a viaCoroutine FROM clause
term is participating in a RIGHT or FULL JOIN, we have to create an
always-NULL pseudo-cursor for that term when processing the RIGHT join.
dbsqlfuzz 6fd1ff3a64bef4a6c092e8d757548e95698b0df5.
dan [Sat, 20 Apr 2024 15:14:06 +0000 (15:14 +0000)]
Fix a problem where an expression like (a, b) IN (SELECT ...) might not use an index on (a, b) if the affinities and collation sequences of "a" and "b" are not identical.
Correct handling of OUTER JOIN when on or the other operand is a subquery
implemented using the VALUES-as-coroutine optimization.
dbsqlfuzz bde3bf80aedf25afa56e2997a0545a314765d3f8.
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.