drh [Tue, 7 Mar 2023 19:39:18 +0000 (19:39 +0000)]
A proposed change to [44135d6ea84f7ba6] that retains the
historical datatype ("INT", not "NUM") for a table created as follows:
"<tt>CREATE TABLE t1 AS SELECT CAST(123 AS INT) AS value;</tt>".
stephan [Tue, 7 Mar 2023 12:59:20 +0000 (12:59 +0000)]
In the JS sqlite3.vfs/vtab utility APIs, use a local reference to StructBinder instead of sqlite3.StructBinder, as that object is removed from the sqlite3 namespace during the final steps of API initialization. Based on feedback from [forum:d19d96183badca70|forum post d19d96183badca70].
drh [Mon, 6 Mar 2023 23:38:44 +0000 (23:38 +0000)]
Improvements to query invariant testing such that it uses the new
SQLITE_DBCONFIG_REVERSE_SCANORDER opcode to sqlite3_db_config() to make more
accurate judgements about when a query is ambiguous, and hence when query
invariant testing is approprate.
stephan [Sun, 5 Mar 2023 07:44:23 +0000 (07:44 +0000)]
Rename sqlite3-worker1-bundler-friendly.js to sqlite3-worker1-bundler-friendly.mjs and refactor it to work as an ES6 module, based on feedback in [forum post a255f89c2eadf4c4|forum:a255f89c2eadf4c4].
drh [Sat, 4 Mar 2023 15:36:51 +0000 (15:36 +0000)]
Fix to check-in [b9190d3da70c4171] - the agg-with-indexed-expr optimization
requested by ticket [99378177930f87bd] - that can cause an incorrect answer
if an aggregate subquery has a GROUP BY clause, and that GROUP BY contains a
term that is not in the result set, and the outer query makes use of expression
indexes. Problem reported by
[forum:/forumpost/a68313d054|forum post a68313d054].
drh [Fri, 3 Mar 2023 18:35:00 +0000 (18:35 +0000)]
Enhance PRAGMA integrity_check so that it can detect when there are extra
bytes at the end of an index record, which might cause OP_IdxRowid to
malfunction. dbsqlfuzz c1aa3986534d5feab8d21f28b3c1712df2ef358ba. Test case
in TH3.
dan [Fri, 3 Mar 2023 16:25:18 +0000 (16:25 +0000)]
When it is known when preparing a statement that X cannot be NULL, transform the expression (X IS NULL) to integer value 1 instead of 'true'. This is because under some circumstances, "Y IS TRUE" may not be equivalent to "Y IS 1".
drh [Thu, 2 Mar 2023 13:49:50 +0000 (13:49 +0000)]
When flattening the right operand of a LEFT JOIN
(check-in [41c27bc0ff1d3135]), ensure that the OP_IfNullRow opcode does not
NULL-out a subquery result that was computed within OP_Once. This fixes
the problem problem reported by
[forum:/forumpost/402f05296d|forum post 402f05296d].
stephan [Thu, 2 Mar 2023 06:58:55 +0000 (06:58 +0000)]
Resolve a parallel build timing issue when building sqlite3.c/h from ext/wasm. For the time being, do not add sqlite3_wasm_extra_init.c to fiddle.wasm because it can cause duplicate definitions of extensions which are already built into the shell (a better resolution for this conflict is pending). No longer add sqlite3_wasm_extra_init.c to speedtest1.wasm because it's useless there.
drh [Wed, 1 Mar 2023 20:23:46 +0000 (20:23 +0000)]
When flattening a view that is the right operand of a LEFT JOIN, using
the optimization of check-in [41c27bc0ff1d3135], always insert the
TK_IF_NULL_ROW expression nodes, even for TK_COLUMN expressions, as
the TK_COLUMN might be a column from an outer query and hence still need
to be NULLed out. This fixes the problem described by
[forum:/forumpost/26387ea7ef|forum post 26387ea7ef].
drh [Wed, 1 Mar 2023 15:21:53 +0000 (15:21 +0000)]
Follow-up to [bbaf1f2eb1e1637b]: Make sure subtypes do not cross a subquery
boundary even if the function that returned the value with a subtype is
buried down inside a larger expression. This fixes a problem identified
by [forum:/forumpost/37dd14a538|forum post 37dd14a538].
dan [Tue, 28 Feb 2023 19:39:59 +0000 (19:39 +0000)]
Add an sqlite3_db_config() option - SQLITE_DBCONFIG_STMT_SCANSTATS - for enabling and disabling the collection of sqlite3_stmt_scanstats() statistics in SQLITE_ENABLE_STMT_SCANSTATUS builds. Collection of statistics is disabled by default.
drh [Tue, 28 Feb 2023 14:28:54 +0000 (14:28 +0000)]
In the Bloom filter optimization, hash all strings and blobs into the same
value, because we do not know if two different strings might compare equal
even if they have different byte sequences, due to collating functions.
Formerly, the hash of a string or blob was just its length. This could
all be improved. Fix for the issue reported by
[forum:/forumpost/0846211821|forum post 0846211821].
drh [Mon, 27 Feb 2023 14:48:54 +0000 (14:48 +0000)]
Further to [46639f682975dac6], the parameters of a table valued function that
is on the left side of a RIGHT JOIN do not need to be in the ON clause. Add
new test cases and tags to associated test cases with the code.
stephan [Mon, 27 Feb 2023 07:12:28 +0000 (07:12 +0000)]
Extend wasm build to enable inclusion of client-custom C code, initialized via the SQLITE_EXTRA_INIT mechanism, per discussion in [forum:1e1c04f3ed1bc96b|forum post 1e1c04f3ed1bc96b].
drh [Sun, 26 Feb 2023 20:57:09 +0000 (20:57 +0000)]
When a table-valued function appears as the right table of a RIGHT JOIN,
the argument constraints on the table-valued function should be considered
part of the ON clause of the RIGHT JOIN. Fix for the problem reported
by [forum:/forumpost/422e635f3beafbf6|forum post 422e635f3beafbf6]. Test
cases in TH3. Possibly related to the enhancement at [501609eddf2a46d5].
drh [Sun, 26 Feb 2023 11:36:35 +0000 (11:36 +0000)]
In the [/info/7c2d3406000dc8ac|omit-unused-subquery-columns optimization], be
sure to remove the EP_Skip and EP_Unlikely flags from the result set expressions
that get nulled-out. dbsqlfuzz bf1d3ed6e0e0dd8766027797d43db40c776d2b15.
drh [Fri, 24 Feb 2023 16:04:05 +0000 (16:04 +0000)]
Modify the sqlite3_config() interface so that the SQLITE_CONFIG_LOG and
SQLITE_CONFIG_PCACHE_HDRSZ opcodes can be called after sqlite3_initialize().
Enhancement request [0b75886e6d48f7c2].
drh [Fri, 24 Feb 2023 11:54:40 +0000 (11:54 +0000)]
Slightly faster and more precise floating-point to decimal conversion.
See [forum:/forumpost/d1387c3979c7f557|forum thread d1387c3979c7f557] for
discussion.
drh [Thu, 23 Feb 2023 21:18:47 +0000 (21:18 +0000)]
Increased precision of floating-point to decimal conversions when the
floating point value has no fractional part.
[forum:/forumpost/d1387c3979c7f557|Forum post d1387c3979c7f557]
drh [Thu, 23 Feb 2023 14:22:29 +0000 (14:22 +0000)]
Allow the sqlite3_config() interface to be invoked at any time for a few
choosen options: SQLITE_CONFIG_LOG, SQLITE_CONFIG_URI, and
SQLITE_CONFIG_PCACHE_HDRSZ. This list will likely change before release.
drh [Thu, 23 Feb 2023 01:52:54 +0000 (01:52 +0000)]
Provide -DHAVE_LOG2=0 and -DHAVE_LOG10=0 compile-time options for use on systems
that lack the log2() and log10() standard math library routines, to cause
SQLite to substitute its own alternatives.
drh [Wed, 22 Feb 2023 20:42:15 +0000 (20:42 +0000)]
Remove the push-down optimization restriction that was added by
check-in [1ad41840c5e0fa70] because it is no longer needed after
the enhancements to compound query processing in check-in [27655c9353620aa5]
This resolves the performance regression reported by
[forum:/forumpost/bcc4375032|forum post bcc4375032].
drh [Tue, 21 Feb 2023 12:30:18 +0000 (12:30 +0000)]
Fix formatting and improved documentation on the various sqlite3_file_control()
opcodes. Other minor documentation changes. No changes to working code.
drh [Thu, 16 Feb 2023 18:04:49 +0000 (18:04 +0000)]
Provide an optimization-disable mask for this optimization. Do not do the
optimization if the subquery is an aggregate or is distinct, but allow it to
be an ephemeral subquery. Do not omit columns that are used in the ORDER BY
of the subquery.
drh [Wed, 15 Feb 2023 17:53:17 +0000 (17:53 +0000)]
Do not compute result columns of subqueries that are never used. Make those
columns NULL instead. This optimization potentially resolves the enhancement
request described by [/tktview/baa5bb76c35a124c|ticket baa5bb76c35a124c].
drh [Mon, 13 Feb 2023 12:46:22 +0000 (12:46 +0000)]
In the LIKE optimization, do not analyze the new virtual WHERE clause terms
until both have been added, since they are expected to be consecutive and
the analysis might add complementary terms.
This fixes a problem caused by [44200596aa943963] and discovered by dbsqlfuzz
and recorded as case 7e3b5983727d843b910b2d9ab556e4afcd777cfb.
drh [Fri, 10 Feb 2023 21:53:33 +0000 (21:53 +0000)]
Do a better job of detecting when a WHERE clause term might be useful to
an expression index. Fix for performance regression reported by
[forum:/forumpost/e65800d8cb|forum thread e65800d8cb].
dan [Fri, 10 Feb 2023 17:17:04 +0000 (17:17 +0000)]
Fix a problem with the fts5 trigram tokenizer and LIKE or GLOB patterns for which contain runs of 2 or fewer non-wildcard characters that are 3 or more bytes when encoded as utf-8.
drh [Thu, 9 Feb 2023 15:32:24 +0000 (15:32 +0000)]
Disable the double-quoted string misfeature by default in CLI builds.
DQS can be reenabled at run-time using the ".dbconfig dqs_dml" and
".dbconfig dqs_ddl" dot-commands.
drh [Wed, 8 Feb 2023 20:29:48 +0000 (20:29 +0000)]
Back out the 'txn' enhancement to date/time functions. The duration of a
"transaction" is confused and needs to be straightened out prior to moving
forward with this change.