5 ms·

Lots of people saying that SQLite is super high quality and finding a bug is so rare. It’s not. I found one on a simple query utilising WHERE EXISTS [1]. Report

by orf 4y ago

Lots of people saying that SQLite is super high quality and finding a bug is so rare. It’s not. I found one on a simple query utilising WHERE EXISTS [1]. Reporting it to a weird forum was also a horrible experience. It’s high quality software, don’t get me wrong, but the infamous 100% test coverage doesn’t make it somehow immune to issues, or imply that the issues you do find are of a certain level of complexity. Nothing is back and white like that. 1. https://sqlite.org/forum/forumpost/452888d3b1?t=c&unf https://sqlite.org/forum/forumpost/452888d3b1?t=c&unf

qbasic_forever 4y ago
What exactly was a 'horrible experience' with the report you filed? Richard himself promptly replied in less than 24 hours that the issue was resolved and previously seen by Firefox too. I am at a genuine loss, what was horrible here??
- orf 4y ago
  It was a while ago, but if I recall I ended up having to sign up several times, the post failed to be created for some reason and something else. There was a captcha involved I think. I don’t remember the specifics, but I do remember coming away from it with a feeling of “wow, that was an atrocious experience. I wonder what the drop off rate is”
  - devwastaken 4y ago
    Sounds about right, most forum software is buggy and fails, but those failures don't get sent to the maintainers. It just waits until someone whom knows the maintainers says "uhhh it's broke.".
  - zoomablemind 4y ago
    Perhaps a while ago... For quite some time SQLite official forum has been running its own version of Forum software (part of Fossil). It allows Anonymous login, with captcha (again, Fossil handles), yeah, but this is nothing unexpected. So in general, the path to Forum presence is fairly unimpeded, well, it is moderated. Reasonably stated issues seem to get attention. Though, some issues indeed need a push to be recognized as such, as it's a public forum, so other users may express their "other" opinions... All in all it's Freedom of Reasonable speech in action. I believe there's a different channel for reporting security-related issues. Again, it's through the Forum, but there's a private message feature for signed-in users.
  - asah 4y ago
    in situations like this, I typically report bugs directly to members of the core team individually, with gory details, and explina that I tried the forum approach and it failed for me.
    - weird-eye-issue 4y ago
      I'm sure they love that
bobleeswagger 4y ago
> Lots of people saying that SQLite is super high quality I think the point most of those folks are making, is that SQLite is good enough where most developers think "Psh, I will use [HEAVIER DB SYSTEM THAT SLOWS OVERALL DEVELOPMENT TIME]" even if it is a better long term solution. It's about bikeshedding, SQLite really is good enough for most projects and its a shame it still has such negative connotations.
- maxbond 4y ago
  For what it's worth I think it's largely overcome the reputation of being a "toy" database.
  - robertlagrant 4y ago
    It never was a toy, so that's good.
    - maxbond 4y ago
      I'll plead guilty to having this impression until fairly recently, but it's an incredible database. While we were all not taking it seriously, SQLite was quietly getting better and better, year after year. You do that for a few decades and you've got an incredible piece of software.
- eduction 4y ago
  “Heavier” and “lightweight” are such abstract terms in software. If “heavier” just means more LoC — sure, there’s more complexity in more LoC but also more problems solved. There’s a reason people tend to use the latest Linux/macos/Windows as opposed to the very lightweight Apple II OS from 1978. Defaulting to, say, Postgres doesn’t seem so bad to me. It solves more problems than SQLite and “lightweight” is not really a concrete benefit for SQLite. It’s at least one level removed from speaking to a real problem.
  - robertlagrant 4y ago
    It also causes problems. Now I have to worry about multiple processes instead of just one, and I need to upgrade it separately and test them both together.
tiffanyh 4y ago
> Reporting it to a weird forum was also a horrible experience. What was so “horrible”? After you posted the bug, the second comment (and only 6-hours later) had a new release and fix.
- robertlagrant 4y ago
  Evidence of a wonderfully comfortable life. We should all hope to have horrible experiences that are that inconsequential!
- setr 4y ago
  He’s talking about the forum software, not the forum community.
eduction 4y ago
It’s high quality software that is being pushed well past its intended use case. Maybe it will work out fine but rqlite is taking something designed as an on disk file format for one program and trying to use it as a network distributed concurrent database system. It would be surprising if they didn’t expose bugs in SQLite. There are database systems that have been around for many years built from the ground up for this use case.
- otoolep 4y ago
  rqlite[1] author here. To be clear rqlite is using SQLite in a completely conventional manner. Nothing about the distributed nature of rqlite impacts on SQLite, since each rqlite node runs its own complete copy of SQLite. This bug can affect anybody using an in-memory version of a SQLite database. That was the point of writing the C unit test. [1] https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite
- infamia 4y ago
  > It’s high quality software that is being pushed well past its intended use case. Maybe it will work out fine but rqlite is taking something designed as an on disk file format for one program and trying to use it as a network distributed concurrent database system. It would be surprising if they didn’t expose bugs in SQLite. Expensify is pushing millions of queries/sec by layering Bedrockdb over top of SQLite. You can go a long way and do amazing, unexpected things with a very solid foundation. https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-qps-on-a-single-server/ https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q...
LVB 4y ago
> but the infamous 100% test coverage doesn’t make it somehow immune to issues Infamous in what way? While I totally get that 100% coverage may be impractical for many projects, I’m also not seeing how less coverage would have improved things. And I highly doubt the SQLite team ever claimed they were immune to bugs!
- lmm 4y ago
  > While I totally get that 100% coverage may be impractical for many projects, I’m also not seeing how less coverage would have improved things. The argument is generally that language-level correctness would achieve more than emphasising test coverage so heavily.
playingalong 4y ago
Not sure of SQLite's interpretation, but in general 100% test coverage "only" means all lines are executed in some piece of test. It doesn't necessarily indicate correctness of each line being checked. Not to mention one can have multiple logic branches in a line. Or bugs relevant to only some subset of inputs (e.g. works fine for positive numbers but fails for negative is a classic example)
- strictfp 4y ago
  Yup. If you consider that a simple int can have billions of states, and that you can have 100% test coverage while testing just one of those, 100% really doesn't mean much. And with several variables, the total state space quickly becomes almost infinite.
- CodesInChaos 4y ago
  SQLite uses a variant of branch coverage. So "multiple logic branches in a line" isn't a problem. The rest of your comment still applies though. https://www.sqlite.org/testing.html#test_coverage https://www.sqlite.org/testing.html#test_coverage
pigbearpig 4y ago
The first couple of responses seemed a bit dismissive and impolite to me. Quickly remedied by a courteous and professional response by Richard Hipp. I'm talking about things like, "You should not expect this to get a lot of attention on a Sunday. That's a slow day here.". I didn't see anything in the initial post that implied OP was expecting an immediate answer. And then the snarky, "This missing return makes me think you dislike or ignore warnings, which makes me want to eagle-eye your code more closely.". I don't know, maybe I'm reading more into that than I should.

by orf 4y ago

qbasic_forever 4y ago

What exactly was a 'horrible experience' with the report you filed? Richard himself promptly replied in less than 24 hours that the issue was resolved and previously seen by Firefox too. I am at a genuine loss, what was horrible here??

orf 4y ago
It was a while ago, but if I recall I ended up having to sign up several times, the post failed to be created for some reason and something else. There was a captcha involved I think. I don’t remember the specifics, but I do remember coming away from it with a feeling of “wow, that was an atrocious experience. I wonder what the drop off rate is”
- devwastaken 4y ago
  Sounds about right, most forum software is buggy and fails, but those failures don't get sent to the maintainers. It just waits until someone whom knows the maintainers says "uhhh it's broke.".
- zoomablemind 4y ago
  Perhaps a while ago... For quite some time SQLite official forum has been running its own version of Forum software (part of Fossil). It allows Anonymous login, with captcha (again, Fossil handles), yeah, but this is nothing unexpected. So in general, the path to Forum presence is fairly unimpeded, well, it is moderated. Reasonably stated issues seem to get attention. Though, some issues indeed need a push to be recognized as such, as it's a public forum, so other users may express their "other" opinions... All in all it's Freedom of Reasonable speech in action. I believe there's a different channel for reporting security-related issues. Again, it's through the Forum, but there's a private message feature for signed-in users.
- asah 4y ago
  in situations like this, I typically report bugs directly to members of the core team individually, with gory details, and explina that I tried the forum approach and it failed for me.
  - weird-eye-issue 4y ago
    I'm sure they love that

bobleeswagger 4y ago

> Lots of people saying that SQLite is super high quality I think the point most of those folks are making, is that SQLite is good enough where most developers think "Psh, I will use [HEAVIER DB SYSTEM THAT SLOWS OVERALL DEVELOPMENT TIME]" even if it is a better long term solution. It's about bikeshedding, SQLite really is good enough for most projects and its a shame it still has such negative connotations.

maxbond 4y ago
For what it's worth I think it's largely overcome the reputation of being a "toy" database.
- robertlagrant 4y ago
  It never was a toy, so that's good.
  - maxbond 4y ago
    I'll plead guilty to having this impression until fairly recently, but it's an incredible database. While we were all not taking it seriously, SQLite was quietly getting better and better, year after year. You do that for a few decades and you've got an incredible piece of software.
eduction 4y ago
“Heavier” and “lightweight” are such abstract terms in software. If “heavier” just means more LoC — sure, there’s more complexity in more LoC but also more problems solved. There’s a reason people tend to use the latest Linux/macos/Windows as opposed to the very lightweight Apple II OS from 1978. Defaulting to, say, Postgres doesn’t seem so bad to me. It solves more problems than SQLite and “lightweight” is not really a concrete benefit for SQLite. It’s at least one level removed from speaking to a real problem.
- robertlagrant 4y ago
  It also causes problems. Now I have to worry about multiple processes instead of just one, and I need to upgrade it separately and test them both together.

tiffanyh 4y ago

> Reporting it to a weird forum was also a horrible experience. What was so “horrible”? After you posted the bug, the second comment (and only 6-hours later) had a new release and fix.

robertlagrant 4y ago
Evidence of a wonderfully comfortable life. We should all hope to have horrible experiences that are that inconsequential!
setr 4y ago
He’s talking about the forum software, not the forum community.

eduction 4y ago

It’s high quality software that is being pushed well past its intended use case. Maybe it will work out fine but rqlite is taking something designed as an on disk file format for one program and trying to use it as a network distributed concurrent database system. It would be surprising if they didn’t expose bugs in SQLite. There are database systems that have been around for many years built from the ground up for this use case.

otoolep 4y ago
rqlite[1] author here. To be clear rqlite is using SQLite in a completely conventional manner. Nothing about the distributed nature of rqlite impacts on SQLite, since each rqlite node runs its own complete copy of SQLite. This bug can affect anybody using an in-memory version of a SQLite database. That was the point of writing the C unit test. [1] https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite
infamia 4y ago
> It’s high quality software that is being pushed well past its intended use case. Maybe it will work out fine but rqlite is taking something designed as an on disk file format for one program and trying to use it as a network distributed concurrent database system. It would be surprising if they didn’t expose bugs in SQLite. Expensify is pushing millions of queries/sec by layering Bedrockdb over top of SQLite. You can go a long way and do amazing, unexpected things with a very solid foundation. https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-qps-on-a-single-server/ https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q...

LVB 4y ago

> but the infamous 100% test coverage doesn’t make it somehow immune to issues Infamous in what way? While I totally get that 100% coverage may be impractical for many projects, I’m also not seeing how less coverage would have improved things. And I highly doubt the SQLite team ever claimed they were immune to bugs!

lmm 4y ago
> While I totally get that 100% coverage may be impractical for many projects, I’m also not seeing how less coverage would have improved things. The argument is generally that language-level correctness would achieve more than emphasising test coverage so heavily.

playingalong 4y ago

Not sure of SQLite's interpretation, but in general 100% test coverage "only" means all lines are executed in some piece of test. It doesn't necessarily indicate correctness of each line being checked. Not to mention one can have multiple logic branches in a line. Or bugs relevant to only some subset of inputs (e.g. works fine for positive numbers but fails for negative is a classic example)

strictfp 4y ago
Yup. If you consider that a simple int can have billions of states, and that you can have 100% test coverage while testing just one of those, 100% really doesn't mean much. And with several variables, the total state space quickly becomes almost infinite.
CodesInChaos 4y ago
SQLite uses a variant of branch coverage. So "multiple logic branches in a line" isn't a problem. The rest of your comment still applies though. https://www.sqlite.org/testing.html#test_coverage https://www.sqlite.org/testing.html#test_coverage

pigbearpig 4y ago

The first couple of responses seemed a bit dismissive and impolite to me. Quickly remedied by a courteous and professional response by Richard Hipp. I'm talking about things like, "You should not expect this to get a lot of attention on a Sunday. That's a slow day here.". I didn't see anything in the initial post that implied OP was expecting an immediate answer. And then the snarky, "This missing return makes me think you dislike or ignore warnings, which makes me want to eagle-eye your code more closely.". I don't know, maybe I'm reading more into that than I should.