the fallback column affinity should be BLOB, not NUMERIC
(1) By hanshenrik on 2020-03-26 23:34:02 [link] [source]
consider this:
CREATE TABLE lel(foo STRING);
INSERT INTO lel VALUES('0123');
SELECT * FROM lel;
here SQLite will corrupt the data. you will get 123
instead of 0123
back.
why? because the string-type is called TEXT
not STRING
, and SQLite doesn't understand STRING
, and what does SQLite do when it doesn't understand the column type? it fallbacks on treating the column as NUMERIC
. and when you do
SELECT CAST('0123' AS NUMERIC)
you get 123
NUMERIC
is a stupid fallback, and can lead to data corruption. IMO the fallback-type should be BLOB
, not NUMERIC
. when SQLite doesn't understand the type, it should just store the data as-is and make no assumptions about it, AKA BLOB
.
(2) By anonymous on 2020-03-27 01:11:52 in reply to 1 [source]
For compatibility reasons it will have to stay how it is, I think.