Quirks, Caveats, and Gotchas in SQLite
sqlite.org
sqlite.org
wtf. who would ever want that?
I'm often exploring data where either there's no defined standard or the use of the data has drifted from the standard. Now, I could go over this line-by-line, but instead my go-to has been "Hey, let's throw this into SQLite, then run stats on it!" See what shakes out. SQLite kindly and obediently takes all of this mystery data, which ends up being nothing like what I was told, and just accepting it. Then I can begin prodding it and see what is actually going on.
This is something that has come up for me for at least a decade: chuck it in SQLite, then figure out what the real standard is.
E.g. a command line argument when creating a DB, which then taints this database as modern. Something like what html did with "<!DOCTYPE html>".
Or simply accept that the time has come to make a version 4 of sqlite.
https://sqlite.org/src4/doc/trunk/www/index.wiki
> SQLite4 was an experimental rewrite of SQLite that was active from 2012 through 2014.
I know to enable foreign key checking, and I learned strict tables from this thread. What are the "other modern features" you were referring to?
EDIT: In TFA, section 8 is relevant here I guess: SQLite accepts double-quoted strings as a historical misfeature, which can be disabled with the appropriate C API functions. This is one of the "other modern features" I guess; TIL.
So the solution your after would be some additional calls when initializing the database to enable the FK checks (alongside any other app related PRAGMA calls like checking the data_version, configuring journal or page size), and ensure any tables created have STRICT applied.
Is sqlite converting an iso8601 to a timestamp, or would it just store as given when type coercion fails due to dashes and spaces? From memory, it's the latter.
It would be slightly more brittle, but surely this metadata should be at app/orm layer rather than the database?
I cannot fathom why you would say this, because the way I see it of course it belongs in the database as part of the table definition, as it’s obviously part of the logical schema. Sure, you can’t actually enforce invariants for individual types so that they would be just conventional aliases for the underlying affinities, but that doesn’t mean you should avoid specifying meaningful types.
Which of these would you prefer:
CREATE TABLE example (
id BLOB PRIMARY KEY,
created INTEGER,
data TEXT
);
CREATE TABLE example (
id uuid PRIMARY KEY,
created timestamp,
data json,
);
(Actually on the DATE thing I was forgetting and thinking that was a regular feature but it’s actually just the fallback affinity where a type is specified but not matched by any other rule, NUMERIC. Strike out that example as a canonical definition or anything, then. But the rest of the point still stands.)
Why? Complaining about a rational and logical thing is orthogonal to something's popularity or how excellent it is otherwise.
State your opinions freely.
Short of doing that, cleaning very dirty data has no satisfactory solution, I think. Optional typing is a nice middle ground between untyped and slow (R, Python) or strictly typed and tedious (all other DB engines).
Maybe instead you mean to use the database functionality to identify problems like that in the original source data. If somebody hands me a string "11101100101001" I would not attempt to interpret it by parsing it into a binary number, but I think that's because I really like strong, simple typing.
Anyway, different ways to use the tool, for sure! And in some cases one would definitely need to be attuned to the issue you're raising. In the kind of situation I'm thinking of the data is usually so dirty anyway, a bit of string->integer conversion won't hurt (probably).
id,location,name
1,90210,Tori Spelling
2,Schitt's Creek,Eugene Levy
and you use sqlite to quickly explore the loaded data to check the set of types used in a column, and maybe even glean what the meaning is.
In the example you give, when you've done all the exploration you need, there's a program interpreting that CSV that ensures the location column values are strings. At least, that's how I do it!
Yes, that's exactly my use case.
As of last year there is an option to make things more strict (https://www.sqlite.org/stricttables.html) though as SQLite doesn't have real date types, unless you are using one of the integer storage options code could insert invalid dates like mysql used to allow.
--
EDIT: having actually read the linked article, it explicitly mentions the date type issue also.
SQLite has been rock-solid since attaining DO-178B, and commonly isn't ever upgraded in many installations.
CentOS 7 is using 3.7.17. Since the v3 database format is standardized, the older version can utilize a STRICT database file, but will not have the capability to alter datatype behavior.
For these cases, implementing both STRICT and the relevant CHECK constraints is advisable.
And the reason changing column types is so hard is because, uh, SQLite stores its schema as human readable plaintext (easier to keep compatibility between versions) and not normalized tables like other databases.
As much as I love sqlite, its table model is really confusing to me coming from a postgres mentality: "WITHOUT ROWID is found only in SQLite and is not compatible with any other SQL database engine, as far as we know. In an elegant system, all tables would behave as WITHOUT ROWID tables even without the WITHOUT ROWID keyword. However, when SQLite was first designed, it used only integer rowids for row keys to simplify the implementation. This approach worked well for many years. But as the demands on SQLite grew, the need for tables in which the PRIMARY KEY really did correspond to the underlying row key grew more acute. The WITHOUT ROWID concept was added in order to meet that need without breaking backwards compatibility with the billions of SQLite databases already in use at the time (circa 2013)."
Each and every biostatistician on this planet. Especially those touching clinical data. Personally, I was saddened to learn that DuckDB did not include dynamically typed, or at least untyped, columns. Happily my data loads are usually small enough for a row-oriented data store.
CHECK constraints, now in conjunction with STRICT tables, are the best invention since sliced bread! If I could improve on one thing, it would be to remove any type notion from non-STRICT tables.
Slightly more so than in non-strict tables in fact: it will store exactly what you give it without trying to reinterpret it e.g. in non-strict, a quoted literal composed of digits will be parsed and stored as an integer, in strict, as a string.
Strict mode also doesn’t influence the parser, so e.g. inserting a `true` in a strict table will work, because `true` is interpreted as an integer literal.
This avoids two useless conversions:
- first from a C string loaded from SQLite to a Swift Unicode string (with UTF8 validation).
- next from this Swift Unicode string to a UTF8 memory buffer (so that JSONDecoder can do its job).
SQLite is smart enough to strip the trailing \0 when you load a blob from a string stored in the database :-)
Does anyone see a massive pro-sqlite movement going on? Sort of like what happens in JS-ecosystem. Everyone is bandwagoning on it. Criticism of SQLite is much welcomed, specifically exemplifying what its role is and which use cases it serves really well.
Have you met non-computer scientists?
Anything and everything is fair game and it is probably for the best.
Job security anyway
I figured "sqlite is better than fopen, let's use that!", but between directory permissions, all the WAL files, probably the sqlite3 python lib and Diskcache (https://pypi.org/project/diskcache/) not helping things, it was a real pain, where regularly under different race conditions, we would get permission denied errors. I managed to paper it over with retries on each side, but I still wonder if there was a missing option or setting I should have used.
Other than that I still love SQLite.
You'll hit that elsewhere as well. “Papering over with retries” is standard MS advice for Azure SQL and a number of other services: https://learn.microsoft.com/en-us/azure/architecture/best-pr...
That is by far my biggest annoyance with sqlite.
Not only that, but the FK enforcement must be enabled on a per-connection basis. Unlike the WAL, you can’t just set it once and know that your FKs will be checked, every client must set the pragma on every connection. Such a pain in the ass.
The error reporting on FK constraint errors is also not great (at least when using implicit constraints via REFERENCES sqlite just reports that an FK constraint failed, no mention of which or why, good luck, have fun).
More generally, I find sqlite to have worse error messages than postgres when feeding it invalid SQL.
The problem is that you have to carefully remember to do that in every project, and if other applications need write access that they do so as well.
I didn’t realise because it was just what I’d expected, but without strict tables I’d have had to debug strange errors on retrieval rather than the type error on insertion I got.
Setting a value with an update is a bitwise or, and checking a value is a bitwise and.
$ sqlite3
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select 2 | 1;
3
sqlite> select 3 & 1;
1
create or replace function bitor(p_dec1 number, p_dec2 number) return number is
begin if p_dec1 is null then return p_dec2;
else return p_dec1-bitand(p_dec1,p_dec2)+p_dec2;
end if;
end;
/
Searches on these will likely require full table scans, a definite performance disadvantage.
SELECT MAX(salary) OVER (), first_name, last_name FROM employee;
SQL bugs are very hard to detect, when the query return a result that looks right, and because the language is declarative its easy to do those mistakes
Columns that are not part of the group, an aggregate of the group, or a functional dependency of the group aren't stable — there are multiple rows that could supply the value, and they needn't have the same value.
Though in a world where “eventual consistency” is accepted, may be “eeny, meeny, miny, moe” will be more generally considered OK at some point :)
At least SQLite tries to be consistent (taking the value from the row where the aggregate found its value) where that is possible (which it often isn't) which mySQL (which also allows non-grouped non-aggregated columns in the projection list) does not.
SELECT max(salary), first_name, last_name FROM employee;
This returns one row! AFAIK all other databases would return one row per record in the table where first_name, last_name would be from the row while max(salary) would be the value from the row with max salary. Is this SQL ANSI compatible?
<SQLite's behaviour is> "Be liberal in what you accept". This used to be considered good design - that a system would accept dodgy inputs and try to do the best it could without complaining too much. But lately, people have come to realize that it is sometimes better to be strict in what you accept, so as to more easily find errors in the input.Any raw byte sequence is allowed in text strings.
Though there is an issue that some of sqlite's own functions are unaware of this and will end early if a NUL is encountered: https://www.sqlite.org/nulinstr.html
I expect text to be text (i.e., Unicode, these days).
Though I can understand people from mostly C/C-alike background where NUL termination is the norm for strings less uncomfortable with that.
The SQLite docs say this about text values or columns, though it's a bit muddy which is which. (But it doesn't really matter.)
> TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).
But it's the best reference I have for "what are the set of values that a `text` value can have".
E.g.,
sqlite> PRAGMA encoding = 'UTF-8';
sqlite> CREATE TABLE test (a text);
sqlite> INSERT INTO test (a) VALUES ('a' || X'FF' || 'a');
sqlite> SELECT typeof(a), a from test;
text|a�a
Presumably for this reason, the Rust bindings I use have Text value carrying a [u8], i.e., raw bytes. Its easy enough to turn that into a String, in practice. (But it is fallible, and the example above would fail. In practice, it gets rolled into all the other "it's not what I expect" cases, like if the value was an int. But having a language have a text type that's more or less just a bytestring is still regrettable.)
¹borrowing Rust or Python's syntax for byte strings.
That's not flexible typing, that's user-hostile behavior.
If it’s ensuring that the data is never larger than X, then you can use a CHECK constraint. It has no impact on storage in sqlite, or in postgres for that matter.
TEXT - text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).
BLOB - blob of data, stored exactly as it was input.
NUMERIC - generic number, attempts to devolve to integer or real.
INTEGER - signed, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
REAL - floating point value, stored as an 8-byte IEEE-754 format.
Any CHAR variant is really text. If you really need it to be 50 characters, then a suitable CHECK constraint must be in place.
You can also create tables with unknown types:
CREATE TABLE foo(bar razzamataz);
With some gymnastics, you can see the affinity assigned to this column:
create table bar as select * from foo where 1=0;
sqlite> .dump bar
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE bar(
bar NUM
);
COMMIT;