> On 19 Mar 2015, at 3:19pm, Paul wrote: > > > This may cause very nasty hard-to-find bugs, since SQLite allows to store > > any content inside BLOB field: > > > > sqlite> create table foo(a int, b int, primary key(a, b)); > > sqlite> insert into foo(a, b) VALUES(1, ''), (1, x''); > > sqlite> select *, length(b) from foo; > > True, but the content which is stored is not of BLOB type unless you stored a > BLOB. Do > > select *, typeof(b), length(b) from foo; > > and you'll find that you get out what you put in. >
Yeah, that's true. But still, this can cause nasty bugs. It feels unnatural, because there is implicit conversion from string to int: sqlite> select * from foo where a = '1'; a b ---------- ---------- 1 1 sqlite> select * from foo where a = '1.0'; a b ---------- ---------- 1 1 sqlite> select * from foo where a = 1.000000000000000001; a b ---------- ---------- 1 1 yet not to BLOB.