4 ms·

I had a look at SQLite around the time sqlite3 appeared, and my notes say I decided I didn't like it because of the lax and sometimes inconsistent way it can tr

I had a look at SQLite around the time sqlite3 appeared, and my notes say I decided I didn't like it because of the lax and sometimes inconsistent way it can treat its input. I thought I'd have another go at it today (sqlite 3.34) and I'm mildly surprised that the things I didn't like are still there: sqlite> select '1 apple' + '2 oranges'; 3 sqlite> select count(*) where 0.9; 1 sqlite> select count(*) where 1.1; 1 sqlite> select count(*) where not 0.9; 1 sqlite> select count(*) where not 1.1; 0

gunapologist99 4y ago
The debate over type conversion seems somewhat like arguing language features (such as weak typing vs strong), which has been a spirited debate since forever. The fact that this is debatable at all means that there are arguments and counter-arguments that serve both sides well, which further implies that there are use cases where some pros outweigh the cons on each side. (Of course, this entirely neglects the point that it's usually fun and even educational to have a technical argument.)
- mjw1007 4y ago
  I don't have any great objection to a database that lets you store a value of any type in any column. And if they want to say '3' + 5 = 8, I could imagine why that might be fine. But I don't think that's a good reason to have behaviour like the above. I think it's more likely that they wouldn't choose to do those things again today: I think this is what fell out of the initial TCL-based implementation and they feel they're stuck with keeping it backwards-compatible.
simonw 4y ago
"I'm mildly surprised that the things I didn't like are still there" That's because SQLite doesn't ship changes that break existing applications.
infogulch 4y ago
You may be interested in STRICT Tables [1], introduced just 6 months ago: > Some developers appreciate the freedom that SQLite's flexible typing rules provide and use that freedom to advantage. But other developers are aghast at SQLite's flagrant rule-breaking and prefer the traditional rigid type system found in all other SQL database engines and in the SQL standard. For this latter group, SQLite supports a strict typing mode, as of version 3.37.0 (2021-11-27), that is enabled separately for each table. [1]: https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html Related past discussions: Strict Tables – Column type constraints in SQLite - Draft | 2021-08-21 | 331 points, 108 comments | https://news.ycombinator.com/item?id=28259104 https://news.ycombinator.com/item?id=28259104 SQLite Release 3.37.0 | 2021-11-27 | 333 points, 112 comments | https://news.ycombinator.com/item?id=29363054 https://news.ycombinator.com/item?id=29363054

gunapologist99 4y ago

The debate over type conversion seems somewhat like arguing language features (such as weak typing vs strong), which has been a spirited debate since forever. The fact that this is debatable at all means that there are arguments and counter-arguments that serve both sides well, which further implies that there are use cases where some pros outweigh the cons on each side. (Of course, this entirely neglects the point that it's usually fun and even educational to have a technical argument.)

mjw1007 4y ago
I don't have any great objection to a database that lets you store a value of any type in any column. And if they want to say '3' + 5 = 8, I could imagine why that might be fine. But I don't think that's a good reason to have behaviour like the above. I think it's more likely that they wouldn't choose to do those things again today: I think this is what fell out of the initial TCL-based implementation and they feel they're stuck with keeping it backwards-compatible.

simonw 4y ago

"I'm mildly surprised that the things I didn't like are still there" That's because SQLite doesn't ship changes that break existing applications.

infogulch 4y ago

You may be interested in STRICT Tables [1], introduced just 6 months ago: > Some developers appreciate the freedom that SQLite's flexible typing rules provide and use that freedom to advantage. But other developers are aghast at SQLite's flagrant rule-breaking and prefer the traditional rigid type system found in all other SQL database engines and in the SQL standard. For this latter group, SQLite supports a strict typing mode, as of version 3.37.0 (2021-11-27), that is enabled separately for each table. [1]: https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html Related past discussions: Strict Tables – Column type constraints in SQLite - Draft | 2021-08-21 | 331 points, 108 comments | https://news.ycombinator.com/item?id=28259104 https://news.ycombinator.com/item?id=28259104 SQLite Release 3.37.0 | 2021-11-27 | 333 points, 112 comments | https://news.ycombinator.com/item?id=29363054 https://news.ycombinator.com/item?id=29363054