Why does SQLite store value as an INT in a BLOB column?

To my SQL knowledge, the entire table column must have the same data type. Yet, in SQLite it seems to be possible to store any type of data in a blob column – i.e. a blob column is not actually a blob type, but rather a typed value, with “value blob” being a subtype of the “column blob” (nested type?). Is this by design and/or documented anywhere? Does it also mean that it is inherently unpredictable because casting an integer to a blob depends on encoding (utf8 vs be/le utf16)?

To test:

$ sqlite3
sqlite> create table tbl(value blob);
sqlite> INSERT INTO tbl VALUES (cast(1 as integer));
sqlite> INSERT INTO tbl VALUES (cast(2 as blob));
sqlite> INSERT INTO tbl VALUES (cast(3 as text));
sqlite> select value, typeof(value) from tbl;
1|integer
2|blob
3|text

With the exception of the special/normally hidden rowid column, or an alias thereof (a column which is the sole column in the primary key AND the column type is specifically INTEGER (case independent) (not e.g INT)); then any type of value can be stored in any affinity type.

It is an intentional flexibility feature of SQLite.

The column type, when resolved, to a type affinity (e.g. INT, TINT resolves to a type affinity of INTEGER), is just a likely indication of what data is stored in the column.

A column type can be virtually anything, as long as it does not conflict with other rules.

• e.g. thecolumn rumplestilskin is allowable, the resultant type affinity will be NUMERIC but a BLOB can be stored in the column.

There are 5 type affinities, INTEGER, REAL, TEXT, BLOB and NUMERIC (the latter being the type assigned if the rules for assigning a column type do not match any of the preceding rules). e.g. the first rule is if the column type include INT then the affinity is INTEGER.

However, there is another level and that is the STORAGE CLASS which is how the data is actually stored on disk/in memory, (typically this need not be understood as SQLite manages this according to the data being stored).

To reiterate the comment refer to https://www.sqlite.org/datatype3.html

Perhaps consider this simple demo:-

DROP TABLE IF EXISTS anycolumntypedemo;
CREATE TABLE IF NOT EXISTS anycolumntypedemo (
    id INTEGER PRIMARY KEY /* CAN ONLY HAVE INTEGER VALUE AS DATA TYPE MISMATCH ERROR */,
    col1 VARCHAR,
    col2 BLOB,
    col3 nearlyanythinggoestype,
    col4 INTEGER
);
INSERT INTO anycolumntypedemo VALUES
    (null,100,100,100, 100),
    (null,0987654321,' 1999', '  1234567890',' 0987654321'),
        (null,'FRED','BERT','HARRY','SUSAN'),
        (null,x'0102ff',datetime('now'),'',randomblob(10))
;
SELECT *,typeof(col1) AS c1type, typeof(col2) AS c2type, typeof(col3) AS c3type, typeof(col4) AS c4type FROM anycolumntypedemo;
DROP TABLE IF EXISTS anycolumntypedemo;

Which will output (the SELECT) something like:-

• id is an alias of the rowid IT CANNOT HAVE ANYTHING BUT AN INTEGER VALUE
• as can be seen various types of values have been stored in various columns, the columns having a type affinity (according to the rules of allocating a type affinity, so col3 has a type affinity of NUMERIC as none of the preceding affinity determination rules are met (see link for more)).
• However, the typeof function, returns a type (col?type columns) that is according to the type of actual value that is stored, so the type is not consistent as the type of the values stored are not consistent.

More recently SQLite has introduced STRICT tables, which may be more preferable if the normal flexibility of SQLite is a concern.