> Why on earth would I want weird rows (that violate the _expressly declared_ type of a table) to be merrily allowed into persistent storage?
Some problems are easy to solve given a column of type VARIANT: 1. simple sparse columns instead of a multi-type EAV table, 2. a BIG Table style schema that is easily range partitioned.
The main reason in SQLite is that most SQL dialects just work. SQLite becomes a universal desktop workbench for SQL. This flexibility also applies to things like delimited identifiers [1].
The problem is not that SQLite uses VARIANT datatypes, the problem is that once a datatype is specified it is not enforced by the engine. SQLite could/should add a PRAGMA to enable Domain Integrity much like it does with Foreign Keys (PRAGMA foreign_keys = ON;).
I'm not sure of the original inspiration but SQLite's core storage types with variable length encoding but it is the right way to implement a row store or a data serialization format, in my opinion.
Fixed length data types in SQL were a premature optimization that led to verbose syntax like LONG VARCHAR FOR BIT DATA [1] and incompatibilities tied to internal implementation details. There is no reason why more specific constraints can't be specified on top of the core storage types.
One can argue that SQLite is sorely missing an exact NUMERIC type but that holds true for most/all language runtimes as well. Datetime is a bit weird too but the format of the DB file is a thing of beauty.
Some problems are easy to solve given a column of type VARIANT: 1. simple sparse columns instead of a multi-type EAV table, 2. a BIG Table style schema that is easily range partitioned.
The main reason in SQLite is that most SQL dialects just work. SQLite becomes a universal desktop workbench for SQL. This flexibility also applies to things like delimited identifiers [1].
The problem is not that SQLite uses VARIANT datatypes, the problem is that once a datatype is specified it is not enforced by the engine. SQLite could/should add a PRAGMA to enable Domain Integrity much like it does with Foreign Keys (PRAGMA foreign_keys = ON;).
[1] https://sqlite.org/lang_keywords.html