Current as of engine 14.0.0. Each entry here is pinned by a test in
the repository, so it leaves this page when the test
starts passing. The gaps that matter for standing in for a server are kept together in
DropInGapsEngineTests,
with the same treatment for ADO.NET
and migrations.
Read them if you want the list without the prose around it.
SQL
Some keywords cannot be used as bare column names, mostly type names: Text, Int, Decimal
and their relatives. SQLite accepts them unquoted and this engine does not. Quote the identifier and
they work.
The set has been shrinking as cases are found, and there is no list of it anywhere: whether an identifier needs quoting is answered by asking the parser whether it can read the word as one, in both the column and the table position, so the answer cannot drift away from the grammar. A word that fails in either position is quoted, since quoting is the conservative answer.
A column you GROUP BY cannot be reached from ORDER BY or HAVING unless it also appears in
the select list. A grouped row is built out of the select list and nothing else. The statement is
not refused; ORDER BY surfaces it as .NET's own Failed to compare two elements in the array.
Query planning
Range estimates assume values are spread evenly. The planner interpolates a bound between the index's smallest and largest key, so a skewed column gets an estimate that is wrong by the shape of the data. A predicate comparing against something other than a literal gets no estimate at all and falls back to a flat guess of 20%.
Partial indexes are never chosen. Working out whether a query's predicates imply an index's filter is not implemented, so a filtered index is passed over before cost is considered. It is still built and still maintained on every write that touches its table, which makes it a cost with no benefit. Use a full index on the same column.
Only some joins get a hash join. An INNER or LEFT join with an equality condition is a
candidate, and the planner decides from the estimated row counts on both sides. Everything else runs
as a nested loop: RIGHT, FULL and CROSS, anything joined on a range or an expression, and an
equi-join the estimates say is not worth building a table for. The cost grows with each table added
to the query. See benchmarks.
Connections and concurrency
A database file is held by one process, and several connections inside that process share one engine. That is a design decision rather than a gap, and the reasoning is on choosing an API. What follows are the consequences that surprise people.
Concurrent transactions in separate connections require MVCC. It is the provider default, so
this only bites if you turned it off: with MVCC=false a transaction holds a database-wide write
lock and a second session's BEGIN reports a lock-recursion error.
Two Data Source=:memory: connections are two databases. SQLite behaves the same way without
Cache=Shared. Sharing an in-memory database between connections would be an opt-in feature and
does not exist yet.
Schema changes
DDL is not undone by a rollback. CREATE TABLE and ALTER TABLE ADD COLUMN were both measured
surviving one, so a migration wrapped in a transaction cannot be abandoned partway. Plan a schema
change as something you finish or repeat, and take a copy first.
A primary key cannot be added to or dropped from an existing table, and a column that is part of one cannot be dropped. All three mean rebuilding the table.
Only DEFAULT and NOT NULL change in place. Everything else about a column is a type change or
a rebuild.
Durability
A statement that has not returned is not atomic against an abrupt end. A statement running in
autocommit puts its writes on the media as it goes: one UPDATE over 20,000 rows writes thousands
of pages before it finishes. Kill the process in the middle and some of them are there.
The rule this produces is worth acting on regardless: wrap a large write in an explicit transaction. Inside one, nothing reaches the media until the commit, so the statement becomes all-or-nothing against a process that dies. It is also faster, because one commit flushes once rather than once per statement.
An abrupt end can leave a file that will not open. Pages reach the disk by eviction, with nothing ordering them against the header, so a crash, a kill or a power cut can leave the header at one vintage and the pages at another. Reproduced on demand: run a couple of dozen DDL statements, kill the process, and the database does not reopen.
This belongs to the default configuration, and closing the window needs a journal the MVCC store does not currently keep. Ending the process cleanly is safe; ending it any other way is not, so take a copy of anything you cannot lose.
Once a statement has returned, its writes are durable, and that is verified by out-of-process crash tests rather than asserted.
Storage engines
The LSM store has no workload where choosing it currently pays. Multi-version concurrency control costs an order of magnitude more over it than over the B+Tree store, and MVCC is the default. See storage engines.
If you are holding an error message
Troubleshooting maps symptoms onto explanations, including the ones on this page.
Where this list comes from
The gaps between this engine and a full database server are kept as executable tests rather than as prose. A test that describes something WitDatabase does not do sits in the suite and fails, so the entry disappears from this page when the behaviour arrives instead of when somebody remembers to edit a paragraph.
The same applies to the SQL dialect. Statements are run against real database servers and the answers compared, which is how the keyword list above was produced and how differences are found before a user finds them.