On: SQLite 3.45 released with JSONB support

[abhibeckert]

I actually kinda like the fact that whatever data you write to the table will actually be written.

I semi-regularly fix a serious data loss bug that has been fixed with an alter table query. Maybe converting VARCHAR to TEXT or INT to BIGINT... of course it doesn't really "fix" your problem, because the data has already been lost/truncated.

What's a real world situation where completely the wrong type could be written to a column? Especially in modern software with good type safety checks/etc to ensure you don't have malicious data inserted into your database? If I ever did have that happen... at least the data hasn't been lost. You can run a simple script to clean up the "horrific" data.

[ncruces → abhibeckert]

I don't find a DB that losslessly stores what I told it to store regardless of types worrying at all.

So in fact AFAIC the misfeature of SQLite is not that it's typeless, IMO, rather it's that it has this notion of NUMERIC affinity that's all but lossless.

E.g. SQLite has a decimal extension that allows you to work with decimal numbers represented as TEXT, and so is appropriate to handle money without rounding issues. However, if you have a column where the declared type is DECIMAL, MONEY, NUMBER, NUMERIC or whatever it will have NUMERIC affinity. Then if you store a textual decimal number to it, it will deduce it looks like a FLOAT and convert, loosing precision.

Your only solution is to use BLOB affinity (declare no type), which is what I do, most of the time.

[sgarland → ncruces]

This is precisely the issue. Databases should not guess at what you want, nor be helpful and make your query work with incorrect types specified.

Schema is rigid; that’s the point. If the input is incorrect, log an error.

[ncruces → sgarland]

It's a bit hard to take your objection very seriously when the following spits out the same SQLite as in PostgreSQL:

    CREATE TABLE tbl (x integer, y real);
    INSERT INTO tbl VALUES ('001', ' 2.5 ');
    SELECT * FROM tbl;

    1|2.5

Numeric affinity was "invented" in SQLite to make it more compatible with PostgreSQL (et al.).

[sgarland → abhibeckert]

> What's a real world situation where completely the wrong type could be written to a column?

* App with incorrect schema definition is deployed

* App written in TS or Python has type checks disabled

* App written in JS does math, and its fun parsing system decides that 1 + “1” == “11”

I’ve seen all of these.

> You can run a simple script to clean up the "horrific" data.

Depends on scale, and the tables / columns. If there are billions of rows, and the columns with incorrect data aren’t indexed… that’s a bad time.