10 ms·

> Before rendering judgment, please read SQLite's own page on the subject. I think you will see that the choice wasn't sloppy but thoughtfully designed, https:/

by piaste 5y ago

> Before rendering judgment, please read SQLite's own page on the subject. I think you will see that the choice wasn't sloppy but thoughtfully designed, https://sqlite.org/datatype3.html https://sqlite.org/datatype3.html I've read the whole page. It's an excellent piece of documentation and reference, but it says nothing about the reasoning behind that design choice, nor show that it was a design choice at all (rather than being e.g. a TCL legacy as some other commenters have hypothesized). The introduction says "However, the dynamic typing in SQLite allows it to do things which are not possible in traditional rigidly typed databases", but it doesn't say what those things are, or explain why they're considered a good trade-off against the well-known footguns of implicit type coercion. (Indeed, if SQLite had rigid static typing, all the detailed rules on that page wouldn't need to exist!)

jbverschoor 5y ago
It’s the classic correct vs lazy to use things. MySQL, mongo, JavaScript. They all are incorrect in their behavior, but because of their defaults and ease of getting started, they got big.
- amarshall 5y ago
  For MySQL and JS I’d say they got big off just being the only thing that was there. Browser? JS, no choice. Shared hosting? MySQL, usually no choice.
  - meltedcapacitor 5y ago
    Chicken and egg issue here: why did shared hosting providers choose MySql? (I guess because of tight integration with PHP, but then same question with PHP.)
    - de_keyboard 5y ago
      I think cost drove MySQL adoption. Remember it arrived when most companies were using Oracle / IBM / MS databases.
      - Macha 5y ago
        Also while they've converged somewhat in the years since, when you were comparing MySQL 4 to whatever Postgres version was in the 00s, MySQL's tradeoff of performance over correctness vs Postgres tradeoff of correctness over performance would have been very appealing to shared providers looking to bundle the most customers on the least hardware
        jbverschoor 5y ago
        But that's the things.. There's always a reason to compromise correctness over something else.
  - zdragnar 5y ago
    JavaScript had competition in the form of activx, java, shockwave / flash / actionscript, and for a very brief moment silverlight. It was really the iPhone that killed everything else off- flash / flex was still in heavy use right up until it seemed smartphones were here to stay and iPhones wouldnt support any plugins
smartmic 5y ago
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.
jolmg 5y ago
It seems beneficial for my case. I have a light wrapper around SQLite called cq[1], to make querying CSVs with SQL more convenient. The point of the tool is to facilitate making ad-hoc, one-off queries on CSV data. CSVs don't specify the types of their columns, and they're either text or numeric. Having to be explicit on what the columns are would be a downside for me. Many CSVs have a ton of columns and I often care only about a few columns for one particular query I'll write and run once and never again. I've fallen into the trap[2] where imported columns compare alphabetically despite them being numbers. I've been casting in my queries and was considering adding argument syntax for specifying the types of certain columns, but now that I see this, I think the behavior on specifying everything as NUMERIC is more desirable. It's more like Excel/LibreOfficeCalc, where you can just use numbers and they behave like numbers without having to be super-explicit. [1] https://github.com/jolmg/cq https://github.com/jolmg/cq [2] https://news.ycombinator.com/item?id=28071615 https://news.ycombinator.com/item?id=28071615

by piaste 5y ago

jbverschoor 5y ago

It’s the classic correct vs lazy to use things. MySQL, mongo, JavaScript. They all are incorrect in their behavior, but because of their defaults and ease of getting started, they got big.

amarshall 5y ago
For MySQL and JS I’d say they got big off just being the only thing that was there. Browser? JS, no choice. Shared hosting? MySQL, usually no choice.
- meltedcapacitor 5y ago
  Chicken and egg issue here: why did shared hosting providers choose MySql? (I guess because of tight integration with PHP, but then same question with PHP.)
  - de_keyboard 5y ago
    I think cost drove MySQL adoption. Remember it arrived when most companies were using Oracle / IBM / MS databases.
    - Macha 5y ago
      Also while they've converged somewhat in the years since, when you were comparing MySQL 4 to whatever Postgres version was in the 00s, MySQL's tradeoff of performance over correctness vs Postgres tradeoff of correctness over performance would have been very appealing to shared providers looking to bundle the most customers on the least hardware
      - jbverschoor 5y ago
        But that's the things.. There's always a reason to compromise correctness over something else.
- zdragnar 5y ago
  JavaScript had competition in the form of activx, java, shockwave / flash / actionscript, and for a very brief moment silverlight. It was really the iPhone that killed everything else off- flash / flex was still in heavy use right up until it seemed smartphones were here to stay and iPhones wouldnt support any plugins

smartmic 5y ago

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.

jolmg 5y ago

It seems beneficial for my case. I have a light wrapper around SQLite called cq[1], to make querying CSVs with SQL more convenient. The point of the tool is to facilitate making ad-hoc, one-off queries on CSV data. CSVs don't specify the types of their columns, and they're either text or numeric. Having to be explicit on what the columns are would be a downside for me. Many CSVs have a ton of columns and I often care only about a few columns for one particular query I'll write and run once and never again. I've fallen into the trap[2] where imported columns compare alphabetically despite them being numbers. I've been casting in my queries and was considering adding argument syntax for specifying the types of certain columns, but now that I see this, I think the behavior on specifying everything as NUMERIC is more desirable. It's more like Excel/LibreOfficeCalc, where you can just use numbers and they behave like numbers without having to be super-explicit. [1] https://github.com/jolmg/cq https://github.com/jolmg/cq [2] https://news.ycombinator.com/item?id=28071615 https://news.ycombinator.com/item?id=28071615