4 ms·

Section 3 under "quirks" gives more reasoning about this design choice, especially: "In retrospect, perhaps it would have been better if SQLite had merely impl

Section 3 under "quirks" gives more reasoning about this design choice, especially: "In retrospect, perhaps it would have been better if SQLite had merely implemented an ANY datatype so that developers could explicitly state when they wanted to use flexible typing, rather than making flexible typing the default. But that is not something that can be changed now without breaking the millions of applications and trillions of database files that already use SQLite's flexible typing feature." from https://sqlite.org/quirks.html#flexible_typing https://sqlite.org/quirks.html#flexible_typing

skrebbel 5y ago
I wonder why they're not considering type modifiers, eg "TEXT" for dynamically-typed TEXT like now, and something like "STRICT TEXT" for erroring out when a non-string is inserted. Or a "use strict" style pragma / parameter to sqlite3_open or sqlite3_exec, which errors out for that session (resp query) if something doesn't match the expected type.
- mumblemumble 5y ago
  My guess is that, given the nature of what SQLite is and the kinds of people who use it (IIRC, it was originally developed for military applications), they've also made a decision that mucking with such a core portion of the database's implementation has such a great potential for unseen consequences that it's just not worth the risk. A little bit like how, at least according to rumor, Python hasn't done anything about its famous global interpreter lock because, when they looked into it, they found that, while it would be a big performance boost in some areas, it would also cause a large performance degradation for the silent majority of people who aren't writing multithreaded Python code. It might be fun to fork it and try implementing a feature like this just to see, though. But I personally would rather see it happen as a (perhaps temporary) fork rather than a change to the core engine, so that people who are happy with SQLite's current type discipline don't have to be taken along for the ride.
  - Zababa 5y ago
    > A little bit like how, at least according to rumor, Python hasn't done anything about its famous global interpreter lock because, when they looked into it, they found that, while it would be a big performance boost in some areas, it would also cause a large performance degradation for the silent majority of people who aren't writing multithreaded Python code. OCaml is going through something like that. It currently has a GIL, and they are working to remove it and allow multithreaded code. However, they're also constantly benchmarking single core performance because it's still the main use case, and they don't want too much regression.
mumblemumble 5y ago
> Some commentators say that SQLite is "weakly typed" and that other SQL databases are "strongly typed". We consider these terms to be inaccurate and pejorative. I would personally say that they're accurate, and not necessarily pejorative. And that the distinction describes a real thing about SQLite's behavior that is distinct from, if related to, its dynamic typing. (On the other hand, their use of the word word "judgmental" to describe strong static typing is unambiguously pejorative. I realize a lot of people can be judgmental about type disciplines, but leave the poor type disciplines. They're not judging you, they're just obeying their designs.) I realize that the term is very fuzzy and can mean all sorts of things, but, to me, "weakly typed" means that data of one type can be treated as if it were another type, without explicit type conversions. And, while that kind of behavior is rather unfashionable nowadays, it does introduce some behaviors that at least some people find desirable. As the section goes on to describe, not everyone likes their tools to be pedantic. That said, I am one of those people who would prefer more rigid typing. I'm cautious about using SQLite at work specifically because I've been burned by that first example it gives. I had a situation where a client needed to use all-numeric IDs where the leading zeros were significant. (For example, "1234" and "01234" were two different IDs.) It was insisting on converting the string "01234" to the integer 1234. So when I tried to get it back out, it would give me the (non-equivalent) string "1234." I had a bugger of a time dealing with this - I eventually had to implement a workaround where all of these IDs were prefixed with a "#" character simply to force SQLite to stop trying to be helpful. I agree with them that this feature should not be changed now, but the fear of having a bug like this, which proved to be very costly for us, prevents me from using SQLite for similar purposes in the future. It's fine if you have tight control over what data goes into the database, and are aware of this behavior, and can design accordingly. For the vast majority of cases where I need a database, it's the one I'd prefer to use. But if you're in a situation where you might just be the custodian of someone else's data and you have little ability to curate it, the defensive coding that this feature entails can be burdensome. Not really because there's a lot of it. My sense is that the flexible typing is actually a net gain on this front. More because it's so different from the kind of defensive coding that you have to do with other SQL databases, so people tend not to have as good of an instinct for it, or as much explicit training on the subject, and are therefore maybe more likely to get it wrong. And also because it tends to lead to silent failures instead of noisy ones, so it's less likely that you'll discover your mistakes before they've created a big mess to clean up.

skrebbel 5y ago

I wonder why they're not considering type modifiers, eg "TEXT" for dynamically-typed TEXT like now, and something like "STRICT TEXT" for erroring out when a non-string is inserted. Or a "use strict" style pragma / parameter to sqlite3_open or sqlite3_exec, which errors out for that session (resp query) if something doesn't match the expected type.

mumblemumble 5y ago
My guess is that, given the nature of what SQLite is and the kinds of people who use it (IIRC, it was originally developed for military applications), they've also made a decision that mucking with such a core portion of the database's implementation has such a great potential for unseen consequences that it's just not worth the risk. A little bit like how, at least according to rumor, Python hasn't done anything about its famous global interpreter lock because, when they looked into it, they found that, while it would be a big performance boost in some areas, it would also cause a large performance degradation for the silent majority of people who aren't writing multithreaded Python code. It might be fun to fork it and try implementing a feature like this just to see, though. But I personally would rather see it happen as a (perhaps temporary) fork rather than a change to the core engine, so that people who are happy with SQLite's current type discipline don't have to be taken along for the ride.
- Zababa 5y ago
  > A little bit like how, at least according to rumor, Python hasn't done anything about its famous global interpreter lock because, when they looked into it, they found that, while it would be a big performance boost in some areas, it would also cause a large performance degradation for the silent majority of people who aren't writing multithreaded Python code. OCaml is going through something like that. It currently has a GIL, and they are working to remove it and allow multithreaded code. However, they're also constantly benchmarking single core performance because it's still the main use case, and they don't want too much regression.

mumblemumble 5y ago

> Some commentators say that SQLite is "weakly typed" and that other SQL databases are "strongly typed". We consider these terms to be inaccurate and pejorative. I would personally say that they're accurate, and not necessarily pejorative. And that the distinction describes a real thing about SQLite's behavior that is distinct from, if related to, its dynamic typing. (On the other hand, their use of the word word "judgmental" to describe strong static typing is unambiguously pejorative. I realize a lot of people can be judgmental about type disciplines, but leave the poor type disciplines. They're not judging you, they're just obeying their designs.) I realize that the term is very fuzzy and can mean all sorts of things, but, to me, "weakly typed" means that data of one type can be treated as if it were another type, without explicit type conversions. And, while that kind of behavior is rather unfashionable nowadays, it does introduce some behaviors that at least some people find desirable. As the section goes on to describe, not everyone likes their tools to be pedantic. That said, I am one of those people who would prefer more rigid typing. I'm cautious about using SQLite at work specifically because I've been burned by that first example it gives. I had a situation where a client needed to use all-numeric IDs where the leading zeros were significant. (For example, "1234" and "01234" were two different IDs.) It was insisting on converting the string "01234" to the integer 1234. So when I tried to get it back out, it would give me the (non-equivalent) string "1234." I had a bugger of a time dealing with this - I eventually had to implement a workaround where all of these IDs were prefixed with a "#" character simply to force SQLite to stop trying to be helpful. I agree with them that this feature should not be changed now, but the fear of having a bug like this, which proved to be very costly for us, prevents me from using SQLite for similar purposes in the future. It's fine if you have tight control over what data goes into the database, and are aware of this behavior, and can design accordingly. For the vast majority of cases where I need a database, it's the one I'd prefer to use. But if you're in a situation where you might just be the custodian of someone else's data and you have little ability to curate it, the defensive coding that this feature entails can be burdensome. Not really because there's a lot of it. My sense is that the flexible typing is actually a net gain on this front. More because it's so different from the kind of defensive coding that you have to do with other SQL databases, so people tend not to have as good of an instinct for it, or as much explicit training on the subject, and are therefore maybe more likely to get it wrong. And also because it tends to lead to silent failures instead of noisy ones, so it's less likely that you'll discover your mistakes before they've created a big mess to clean up.