I get it…I’ve never been the maintainer of a codebase that’s deployed on trillions of devices, and backwards compatibility is something to be taken seriously and responsibly when you’re that prolific. I do not begrudge SQLite or any large projects when they make decisions in service to that.
However
It always makes me feel oddly icky when known bugs (particularly of the footgun variety) become the new standard that the project intentionally upholds.
I’m so confused that the same people can say “why does everyone get their undies in a bunch that we happily accept putting arbitrary data in columns regardless of type, that’s good, it’s flexible, but fine, we’ll put in a ‘strict’ keyword if you really want column types to mean something” and also “every other SQL says 1==‘1’ but this is madness, strings aren’t integers, what is everyone else thinking?!”
Strongly typed is orthogonal to strictly typed, so these two properties alone are not contradictory.
However, it is a bit unsettling that, if a column has an INTEGER type affinity, and you try to put a string in it, then the string is implicitly converted to an integer if it represents an integer and just stored silently as-is otherwise.
But they silently converted the string ‘1’ into the number 1. So now in my same code, I want to select back my stringy ‘1’ that I putted in the type affined INTEGER column.
And you are telling me its normal that I don’t get it back ? Or maybe I’m misunderstanding something?
To clarify: I am saying that it is not “normal” that the type you get back out is not only not necessarily the type that you put in, but may be different depending on the value that you put in. Put another way, sqlite is strongly typed unless you mistakenly thought that type affinities by themselves made it be strictly typed, in which case it becomes neither strictly nor strictly typed.
I think one of them should be “strongly”, but I understood your point.
Thinking back, I don’t have the doc easily accessible (on phone), but I think the C API state the type you want to read. Like get_int(smt, VALUE_INDEX, …), so at least in the C API, most of this should not be visible. Maybe only the SELECT1='1' part (or others comparaison fully done in the SQL string)?
I get it…I’ve never been the maintainer of a codebase that’s deployed on trillions of devices, and backwards compatibility is something to be taken seriously and responsibly when you’re that prolific. I do not begrudge SQLite or any large projects when they make decisions in service to that.
However
It always makes me feel oddly icky when known bugs (particularly of the footgun variety) become the new standard that the project intentionally upholds.
I’m so confused that the same people can say “why does everyone get their undies in a bunch that we happily accept putting arbitrary data in columns regardless of type, that’s good, it’s flexible, but fine, we’ll put in a ‘strict’ keyword if you really want column types to mean something” and also “every other SQL says 1==‘1’ but this is madness, strings aren’t integers, what is everyone else thinking?!”
Strongly typed is orthogonal to strictly typed, so these two properties alone are not contradictory.
However, it is a bit unsettling that, if a column has an INTEGER type affinity, and you try to put a string in it, then the string is implicitly converted to an integer if it represents an integer and just stored silently as-is otherwise.
But they silently converted the string ‘1’ into the number 1. So now in my same code, I want to select back my stringy ‘1’ that I putted in the type affined INTEGER column.
And you are telling me its normal that I don’t get it back ? Or maybe I’m misunderstanding something?
To clarify: I am saying that it is not “normal” that the type you get back out is not only not necessarily the type that you put in, but may be different depending on the value that you put in. Put another way, sqlite is strongly typed unless you mistakenly thought that type affinities by themselves made it be strictly typed, in which case it becomes neither strictly nor strictly typed.
I think one of them should be “strongly”, but I understood your point.
Thinking back, I don’t have the doc easily accessible (on phone), but I think the C API state the type you want to read. Like
get_int(smt, VALUE_INDEX, …), so at least in the C API, most of this should not be visible. Maybe only theSELECT 1 = '1'part (or others comparaison fully done in the SQL string)?It’s not on trillions of devices, just billions. But e.g. a typical android phone has 1000s of sqlite db’s for different purposes.
You’re right, that’s a distinction I failed to make