Doesn't this convert all data to strings? I think that makes some queries wonky.
Or does it search through each row and see that one element out of 1000 integers was a float, so the whole column should be floats?
Or does it search through each row and see that one element out of 1000 integers was a float, so the whole column should be floats?
I like SQLite a lot, but one of the funniest things about it is how they try to sell you that type affinity is a great thing [1] when really it's not at all what you want in 99% of RDBMS situations. There's a reason that everybody from MySQL and PostgreSQL to MS SQL Server, Oracle, and DB2 use static typing.
Edit: I assume most databases like Oracle (it's SQL packages looked bizzare to me until I started learning Ada and figured out it has the same syntax) use static typing for performance, query optimization, and error checking? Note that although I use SQL almost daily, I am no expert on the innards. SQLite has a different use case. It seems to be for embedded and ad-hoc analysis where dynamic typing makes more sense. This is just a guess though.