I found a bug in SQLite(philipotoole.com)
philipotoole.com
I found a bug in SQLite
https://www.philipotoole.com/how-i-found-a-bug-in-sqlite/
168 comments
Tests usually cannot prove absence of defects. But they can show presence of defects. So yes, a test suite that runs without errors tells you “nothing” in a way. But it's a very good safeguard to ensure that code changes don't inadvertently introduce unexpected changes.
For me it's also the case that I think much more thorough about what inputs could be possible and potentially problematic, so there's often an extra set of test cases around boundaries of input values that would have never been tried when just quickly throwing together a demo application to showcase and experiment with a new feature.
But the fundamental problem that lots of bugs don't appear in testing since those code paths are never tested also means that testing alone isn't sufficient. But I guess we all know that by now, and combining different kinds of tests with other approaches like code reviews (actual proofs are probably beyond the scope for the vast majority of software projects) is being done all the time to not bet everything on a misguided 100 % code coverage unit test approach that's both expensive and fairly useless.
For me it's also the case that I think much more thorough about what inputs could be possible and potentially problematic, so there's often an extra set of test cases around boundaries of input values that would have never been tried when just quickly throwing together a demo application to showcase and experiment with a new feature.
But the fundamental problem that lots of bugs don't appear in testing since those code paths are never tested also means that testing alone isn't sufficient. But I guess we all know that by now, and combining different kinds of tests with other approaches like code reviews (actual proofs are probably beyond the scope for the vast majority of software projects) is being done all the time to not bet everything on a misguided 100 % code coverage unit test approach that's both expensive and fairly useless.
Additionally, bug fixes should pretty much always come with tests. If the software has a lot of tests adding a few additional cases to cover the bug is trivial. But if tests are sparse, it may require a lot more time to figure out how to test that code altogether.
Testing also influences code design. 100% coverage requires planning and forethought and it will inevitably be reflected in code quality. Bugs are inevitable, but not all bugs.
Testing also influences code design. 100% coverage requires planning and forethought and it will inevitably be reflected in code quality. Bugs are inevitable, but not all bugs.
I'm watching engineers re-implement error handling code I wrote line by line over the weeks after they "optimized" my error handling into a oneliner. And as the old bugs crop up when API error responses are weirdly formed, they find themselves adding the edge case code I had added previously.
If I had been granted time to add unit tests, those would just function as a source of truth: "sometimes the API returns this kinda weird error, so we handle it. Sometimes this one, so we handle it." Unit tests are nice for that, all things this given program (the UI in this case) needs to worry about from the various things it talks to (it could talk to a couple different APIs who all have different quirks).
I wasn't granted time because the API quirks are considered bugs that are being fixed... one day... hence why the oneliner "refactor" was allowed, but regardless, it has been my go to object lesson in why I finally find unit tests useful.
If I had been granted time to add unit tests, those would just function as a source of truth: "sometimes the API returns this kinda weird error, so we handle it. Sometimes this one, so we handle it." Unit tests are nice for that, all things this given program (the UI in this case) needs to worry about from the various things it talks to (it could talk to a couple different APIs who all have different quirks).
I wasn't granted time because the API quirks are considered bugs that are being fixed... one day... hence why the oneliner "refactor" was allowed, but regardless, it has been my go to object lesson in why I finally find unit tests useful.
Reminds me me https://www.joelonsoftware.com/2000/04/06/things-you-should-... search for "floppy"
The tests don't prevent bugs, but they do make fixes a lot faster to get out, since you can be confident your fix doesn't break any functionality.
Not if a simple test requires rewriting hundreds of tests (aka "fragile tests").
Yes, one shouldn't be writing fragile tests, but usually from what I seen at projects with great test coverage, is that it often slows bugfix releases, and especially any bigger changes, as it's very wearisome to also change hundreds of tests.
So I believe there should be some balance between tests/code ratio, as well as attention paid to tests brittleness.
Yes, one shouldn't be writing fragile tests, but usually from what I seen at projects with great test coverage, is that it often slows bugfix releases, and especially any bigger changes, as it's very wearisome to also change hundreds of tests.
So I believe there should be some balance between tests/code ratio, as well as attention paid to tests brittleness.
Definitely, some real world cases research show that this balance stands around 80% for unit tests- you don't earn more quality as you add more test above that.
Last time I heard 80% ratio is optimal for Java, but any dynamic typing language (JS, Python, Ruby) it's around 100% test LOC / code LOC.
Any functionality that is tested :)
"if it's not tested, it's not functionality"
Hyrum would want to have a word...
If only there was a way to run all the tests of all the software that depend on your change...
Fwiw, that's what monorepos are good for.
Sadly it's hard to make a "world monorepo"
Fwiw, that's what monorepos are good for.
Sadly it's hard to make a "world monorepo"
If you pin your dependency versions and the dependency maintainers run tests before releasing, you're already almost there (excluding bugs that might arise only in your specific environment). If dependencies don't have tests or test automation, you can always contribute them.
As for the platform-specific bugs, monorepos only help if the way to run tests in all the components is standardised. But this is something you can just as easily implement across repos. Could be as simple as having a testme.sh in the root.
As for the platform-specific bugs, monorepos only help if the way to run tests in all the components is standardised. But this is something you can just as easily implement across repos. Could be as simple as having a testme.sh in the root.
> > "if it's not tested, it's not functionality"
This suggests you may have not 100% test coverage in your tests. But 100% coverage if what? What is the specification you're defining your behaviour against?
The comment above suggests that you could treat your tests as if they were the ones that actually define your contract.
> Hyrum would like to have a word.
This is a reference to "Hyrum's law" which says:
"With a sufficient number of users of an API, it does not matter what you promise in the contract: all observable behaviors of your system will be depended on by somebody."
This comment, in response to the previous about defining tests as the source of truth for your contract, remarks that sadly you can't do that because no matter what contract you wish you define, ultimately the behaviour of your existing software becomes its effective contract.
> [My comment about monorepos]
Here I suggest that if you extend the notion of what is the test corpus to include the test corpus of all of the software that depend on you (not your dependencies! The code whom your code is a dependency) then you could detect if a (yet unmerged) change you're making is actually going to affect any existing code.
This suggests you may have not 100% test coverage in your tests. But 100% coverage if what? What is the specification you're defining your behaviour against?
The comment above suggests that you could treat your tests as if they were the ones that actually define your contract.
> Hyrum would like to have a word.
This is a reference to "Hyrum's law" which says:
"With a sufficient number of users of an API, it does not matter what you promise in the contract: all observable behaviors of your system will be depended on by somebody."
This comment, in response to the previous about defining tests as the source of truth for your contract, remarks that sadly you can't do that because no matter what contract you wish you define, ultimately the behaviour of your existing software becomes its effective contract.
> [My comment about monorepos]
Here I suggest that if you extend the notion of what is the test corpus to include the test corpus of all of the software that depend on you (not your dependencies! The code whom your code is a dependency) then you could detect if a (yet unmerged) change you're making is actually going to affect any existing code.
Totally misread the "depend on you" as "you depend on" part, sorry.
Interesting idea, but monorepos still don't help on their own. You need a way to detect which modules depend on yours and a way to run and interpret their tests. Whether they're in an adjacent folder or another repo changes very little.
GitHub actually already has a dependency scanning thingy that builds cross-repo dependency graphs [0] and package managers have been doing that for years. The missing parts wouldn't be that much effort to build, but getting everyone to set up their repos to work with it would probably be prohibitively difficult.
[0] https://docs.github.com/en/code-security/supply-chain-securi...
Interesting idea, but monorepos still don't help on their own. You need a way to detect which modules depend on yours and a way to run and interpret their tests. Whether they're in an adjacent folder or another repo changes very little.
GitHub actually already has a dependency scanning thingy that builds cross-repo dependency graphs [0] and package managers have been doing that for years. The missing parts wouldn't be that much effort to build, but getting everyone to set up their repos to work with it would probably be prohibitively difficult.
[0] https://docs.github.com/en/code-security/supply-chain-securi...
Monorepos are useless unless you have good tooling, including a build system that is aware of module dependencies inside the monorepo.
An example build system that can do that is https://bazel.build
An example build system that can do that is https://bazel.build
Correct. Another interesting one:
https://github.com/just-buildsystem/justbuild
https://github.com/just-buildsystem/justbuild
[deleted]
That's exactly what the Nixpkgs Hydra instance does—caveat being your library and its consumers all need to be in Nixpkgs. I'm hopeful that there will be a way to keep the tracking part of that going forward, as Flakes are adopted and the community decentralises.
There was an interesting comment a few months ago [1]
> Not mentioned is that the full test sqlite test suite is proprietary and you need a super expensive sqlite foundation membership to get access to it.
According to Dr Hipp [2], no one bought the test suite. So there are definitely deficiencies in the test suite which may have been better addressed if the full test suite was open.
[1] https://news.ycombinator.com/item?id=33346661
[2] https://corecursive.com/066-sqlite-with-richard-hipp/#billio...
> Not mentioned is that the full test sqlite test suite is proprietary and you need a super expensive sqlite foundation membership to get access to it.
According to Dr Hipp [2], no one bought the test suite. So there are definitely deficiencies in the test suite which may have been better addressed if the full test suite was open.
[1] https://news.ycombinator.com/item?id=33346661
[2] https://corecursive.com/066-sqlite-with-richard-hipp/#billio...
From the second link:
> We still maintain the first one, the TCL tests. They’re still maintained. They’re still out there in the public. They’re part of the source tree. Anybody can download the source code and run my test and run all those. They don’t provide 100% test coverage but they do test all the features very thoroughly. The 100% MCD tests, that’s called TH3. That’s proprietary. I had the idea that we would sell those tests to avionics manufacturers and make money that way. We’ve sold exactly zero copies of that so that didn’t really work out. It did work out really well for us in that it keeps our product really solid and it enables us to turn around new features and new bug fixes very fast.
> We still maintain the first one, the TCL tests. They’re still maintained. They’re still out there in the public. They’re part of the source tree. Anybody can download the source code and run my test and run all those. They don’t provide 100% test coverage but they do test all the features very thoroughly. The 100% MCD tests, that’s called TH3. That’s proprietary. I had the idea that we would sell those tests to avionics manufacturers and make money that way. We’ve sold exactly zero copies of that so that didn’t really work out. It did work out really well for us in that it keeps our product really solid and it enables us to turn around new features and new bug fixes very fast.
It also makes forking SQLite infeasible since any new changes will be woefully under tested.
[deleted]
SQLite's free test suite is far in excess of most other projects'. What do you think the test coverage is in the Linux kernel, GCC, or Emacs?
Right, but it makes the fork strictly worse from a reliability perspective than SQLite given it will be less tested.
If there wasn't a competitive advantage given it has no sales, wouldn't they have open sourced it by now?
If there wasn't a competitive advantage given it has no sales, wouldn't they have open sourced it by now?
> wouldn't they have open sourced it by now?
If there's minimal value in it, why put in the work to open source an extremely complex test environment?
If there's minimal value in it, why put in the work to open source an extremely complex test environment?
Just for completeness sake, they do offer a SQLite Consortium Membership for $120k, which I guess includes all their test suites as a selling point:
https://www.sqlite.org/prosupport.html
(Embedded) database has stricter requirements than a text editor or a compiler.
Obviously not, if nobody was willing to buy the test suite for their internal forks.
That's a non-sequitur argument.
It follows fine, but you want to debate instead of think.
If the 100% MC/DC coverage was critical to forks, the companies that fork (there's lots of them!) would have bought it.
Nobody bought it, so it's not that important to maintaining a fork compared to the regular test suite even for such environments. A test suite which, to go back to my first comment, is still leagues ahead of the dozen other pieces of lynchpin software most companies have no problem depending on.
Meanwhile, for the 99.9% of us out here not building aircraft and merely shipping a billion browsers or phones...
If the 100% MC/DC coverage was critical to forks, the companies that fork (there's lots of them!) would have bought it.
Nobody bought it, so it's not that important to maintaining a fork compared to the regular test suite even for such environments. A test suite which, to go back to my first comment, is still leagues ahead of the dozen other pieces of lynchpin software most companies have no problem depending on.
Meanwhile, for the 99.9% of us out here not building aircraft and merely shipping a billion browsers or phones...
> If the 100% MC/DC coverage was critical to forks, the companies that fork (there's lots of them!) would have bought it.
Again, that's a non-sequitur (or perhaps strawman, you can choose), because I wasn't addressing the proprietary test set, merely the comparison between a text editor and a database, which is completely absurd since the tolerance for failures is drastically different.
Again, that's a non-sequitur (or perhaps strawman, you can choose), because I wasn't addressing the proprietary test set, merely the comparison between a text editor and a database, which is completely absurd since the tolerance for failures is drastically different.
> I wasn't addressing the proprietary test set
Then perhaps you're in the wrong thread to be saying anything at all.
Then perhaps you're in the wrong thread to be saying anything at all.
What was the incentive to buy these tests if they've already been used to improve the product?
You can then audit the tests to ensure they test some condition that you are concerned about.
In reality nobody audits source code like that (see heartbleed for an unrelated example of critical code that didn't get proper audits from people who should have cared)
In reality nobody audits source code like that (see heartbleed for an unrelated example of critical code that didn't get proper audits from people who should have cared)
But the testsuite being proprietary is part of their business model.
At least the tests help avoid regressions, you can bet these bugs won't come back again.
I found this interesting (in another blog post of the same author):
> Keep your integration testing for smoke tests — to make sure your database actually starts and that you haven’t missed anything basic. Only when there is no way to exercise the code except when an actual full instance of the software is running should an end-to-end test be used.
This is the complete opposite of my experience. But I guess this is because he is developing a library-like software, and I'm mostly working on application code. I found unit tests mostly useless and a waste of time. But I'm sure that for a database library they are absolutely key...
> Keep your integration testing for smoke tests — to make sure your database actually starts and that you haven’t missed anything basic. Only when there is no way to exercise the code except when an actual full instance of the software is running should an end-to-end test be used.
This is the complete opposite of my experience. But I guess this is because he is developing a library-like software, and I'm mostly working on application code. I found unit tests mostly useless and a waste of time. But I'm sure that for a database library they are absolutely key...
When I've heard people making similar claims, what I've usually found is they're testing "glue" code: controllers, routers, etc. Personally I find this a near total waste of time: it's hard to write the tests, almost never actually catches a bug, and failures in this code are totally obvious during a smoke test - automated or not.
I write a lot of "application" code (cli, service and back-end) and a lot of tests. Parsing, calculations, file generation, regex .. that catches lots of bugs.
The value comes from keeping the complex code separate from the glue, and of course testing it. And you can easily test dozens of cases, which is usually not true of integration tests due to complexity and run time.
I write a lot of "application" code (cli, service and back-end) and a lot of tests. Parsing, calculations, file generation, regex .. that catches lots of bugs.
The value comes from keeping the complex code separate from the glue, and of course testing it. And you can easily test dozens of cases, which is usually not true of integration tests due to complexity and run time.
> failures in this code are totally obvious during a smoke test - automated or not.
Yes, but if your codebase is large enough then a non-automated smoke test can be a very slow process, especially if things are configurable. It would have taken 3-4 days to smoke test all functionality manually at my last workplace.
Automated tests could make that 5-10 minutes.
Yes, but if your codebase is large enough then a non-automated smoke test can be a very slow process, especially if things are configurable. It would have taken 3-4 days to smoke test all functionality manually at my last workplace.
Automated tests could make that 5-10 minutes.
Any time I make claims like this, people look at me like I'm insane. But here I am, year after year, meeting release targets with robust software while the teams that chase test coverage and other optics don't. I would assume I'm missing something if this industry hasn't give me a million reasons not to believe in the best practices typically put forward.
It really depends on the type of software. An ETL pipeline, for example, is obviously way easier to develop and maintain through tests (record real system inputs, compare with desired outputs). But that logic doesn't extend to all other types of software.
rqlite creator here.
I'm not developing libraries, I'm developing an entire RDBMS. In my experience -- and this is broader than rqlite -- integration and end-to-end tests seem like they are great - at the start. But as you rely more and more on them they become costly to maintain and really hard to debug. A single test failure (often due to a flaky, timing-sensitive issue) means wading through layers and layers of code to identify the issue.
Overly relying on integration and end-to-end testing (note I said over-reliance, there is absolutely a need for them) becomes exponentially more costly over time (measured in development velocity and time to debug) as the test suite grows. If you find you're having difficulty identifying a place for them it may that you're not decomposing your software properly in the first place. All this is probably manageable if you're a solo developer, but when a team is building the software it can become really painful.
For more details see the talk I gave to CMU[1] on my testing strategy.
[1]https://youtu.be/JLlIAWjvHxM?t=2067
I'm not developing libraries, I'm developing an entire RDBMS. In my experience -- and this is broader than rqlite -- integration and end-to-end tests seem like they are great - at the start. But as you rely more and more on them they become costly to maintain and really hard to debug. A single test failure (often due to a flaky, timing-sensitive issue) means wading through layers and layers of code to identify the issue.
Overly relying on integration and end-to-end testing (note I said over-reliance, there is absolutely a need for them) becomes exponentially more costly over time (measured in development velocity and time to debug) as the test suite grows. If you find you're having difficulty identifying a place for them it may that you're not decomposing your software properly in the first place. All this is probably manageable if you're a solo developer, but when a team is building the software it can become really painful.
For more details see the talk I gave to CMU[1] on my testing strategy.
[1]https://youtu.be/JLlIAWjvHxM?t=2067
Thats exactly the point of a regression test suite and shows that millions of tests work at giving confidence in a major SQL provider to release a fix to prod so quickly....
I feel frustration that the first response was to tell you it why it wasn't a bug and why it didn't need fixing.
Tests demonstrate the current behavior of the software. If you don't have tests then all you'd need to do is manually run through millions of test cases manually, hardly better. What you've done is be the first person to go through a test case. Once there's a test that behavior will be documented, which I would much prefer to having to wait yet again for someone to find this test case.
That reminds me of caddy templates. Caddy templates cannot have untested code. I think go text templates can. When in doubt, treat templates as code that has access to private stuff. With caddy that includes files and environment variables.
I didn't get bitten by this because I read the docs, but I noticed how easy it would be to misconfigure.
I didn't get bitten by this because I read the docs, but I noticed how easy it would be to misconfigure.
What really bugs me is the denialism as a recurring pattern. If you hadn't persisted, maintainer would just pretend there are no security issues.
If that's how it makes you feel then why not go on security bugs hunting spree. If you found one, surely you can find at least 10 more others.
Does that fall within the scope of the core library? It looks like a bug in the CLI program (shell.c) rather than the sqlite lib.
[deleted]
[deleted]
SQLite is probably very well tested, but this "millions of tests" argument is tiring. Most of these tests are algorithmically produced.
I understand the comment about "intellectual honesty" about those pesky transient bugs.
It's really hard to know where a hard to reproduce bug is on the cost benefit spectrum - and that is the crux - not knowing enough about the bug to determine it's negative weight means you are essentially guessing both sides of the equation.
It's probably not the best idea, it waiting till users find it at east gives a good idea of the prior
It's really hard to know where a hard to reproduce bug is on the cost benefit spectrum - and that is the crux - not knowing enough about the bug to determine it's negative weight means you are essentially guessing both sides of the equation.
It's probably not the best idea, it waiting till users find it at east gives a good idea of the prior
Yeah the old "boss, I have spent 2 days investigating the bug, and I think it is best overall if we don't fix it. maybe show a better message".
The lesson here is never ignore your own "Huh, that's weird." experience when using software. :-)
Yes, I refer to this as cognitive dissonance. You know that something isn't right, and you can't readily think of an innocent explanation that really holds up, but it's possible to ignore in the short term because you can work around it or it's not a complete show stopper. Those things almost always seem to eventually come back to bite you!
> Those things almost always seem to eventually come back to bite you!
Or maybe you only remember the ones that bit you, and forget the ones that didn’t.
Or maybe you only remember the ones that bit you, and forget the ones that didn’t.
A French proverb is « Il n’y a pas de fumée sans feu », means there is no smoke without fire. I use it a lot in my day work when I try not to watch a suspicious behavior…
The usual form of that proverb in English is "When there's smoke, there's fire".
Is it? Interestingly I’ve only heard it as “no smoke without fire”
"Where there's smoke, there's fire" is how I've heard that proverb
“Where there’s smoke, there’s fire” is what I’ve heard (from fire heavy Southern California).
This is how we phrase it in Australia.
I'm Australian and have only heard, "where there's smoke, there's fire".
I've heard both.
Smoke is absolutely possible without a fire. Have you ever seared any meat on an electric stovetop? Overheated an empty frying pan?
By what means was electricity generated to be delivered to your stovetop? ;)
("solar panels!" sun is a ball of fire)
("nuclear power plant!" via what means was the steel in the brace for the control rods forged then? :D )
("solar panels!" sun is a ball of fire)
("nuclear power plant!" via what means was the steel in the brace for the control rods forged then? :D )
Since we seem to have started a competition about who can provide the least relevant and most pedantic correction, the sun is not a ball of fire. It is a plasma heated by nuclear fusion. There is no oxygen to support combustion.
Congrats, you have now proved that everything is fire. So "no smoke" also means fire if we follow your logic. Was that your intent?
The point of the saying is that if there's evidence (smoke) of something normally worth paying attention to (fire), said thing is indeed present.
If something is smoking on your stove, it's worth paying attention to, whether it's because you're actively searing something on an electric or because your pan is empty.
My OP was the one you should come at with this tone lol, they were the ones that sidetracked off whether or not "if there's smoke, there's fire" is a useful idiom. Maybe a better question would have been, "though there's no actual fire, would not those instances of smoke still be worth noting?"
Shall we start thinking of times smoke is present, and not worth anyone's attention?
If something is smoking on your stove, it's worth paying attention to, whether it's because you're actively searing something on an electric or because your pan is empty.
My OP was the one you should come at with this tone lol, they were the ones that sidetracked off whether or not "if there's smoke, there's fire" is a useful idiom. Maybe a better question would have been, "though there's no actual fire, would not those instances of smoke still be worth noting?"
Shall we start thinking of times smoke is present, and not worth anyone's attention?
They sidetracked by taking too binary of an interpretation, sure, but your rebuttal was flat-out broken.
I don't feel the need to reply to every post I disagree with.
I don't feel the need to reply to every post I disagree with.
The definition of fire is kind of not precise, Humans are on fire (burning calories with oxygen) well nearly everything in contact with oxygen is "on traditional fire", burning alcohol is invisible and no smoke, and the sun does not need oxygen (fusion), so you can say everything that "decays" is on "fire"....even your mixtape.
Exactly the same in Russian: "Нет дыма без огня".
I guess there was something they could share during the Moscow fire in 1812.
[deleted]
I think you have to ignore some of these, especially at a large company, where there may be bugs everywhere. Otherwise you will never get your features done and just fix bugs the whole time.
The code for databases such as MySQL and SQLite is so huge that there are probably 100s of bugs, some get fixed and some don't.
Tis good advice, but in most cases I'd question my own code first and
then be confused by general complexity - is it the threading library,
or the API, or the database? Rarely do we get a chance to pare back to
an isolated behaviour. Also there's a voice that says "surely someone
much smarter than me already knows about this".
Eh, depends.
I found a bug once in Clojure's implementation of format. It was perfectly reproducible, Clojure's behavior was out of spec, and it didn't match SBCL's output from an identical call to format.
I wanted to report it, but I had to give up on that; the process was too opaque.
I found a bug once in Clojure's implementation of format. It was perfectly reproducible, Clojure's behavior was out of spec, and it didn't match SBCL's output from an identical call to format.
I wanted to report it, but I had to give up on that; the process was too opaque.
Hire me a full time bug reporting assistant then please :-)
I usually only ignore things when I'm just too busy to care, unless it stops me from doing what I'm currently doing.
I'd be interested to read some discussion on why this wasn't caught earlier. When you have some thing with supports read + write and supports access from multiple threads/processes, I'd expect "read as fast as possible from one thread, write as fast as possible from another" to be one of the most obvious tests to write.
Seems like it was particularly the lock upgrade path in the memdb vfs — so a little weird case that must have not been covered adequately.
https://www.sqlite.org/src/info/15f0be8a640e7bfa
https://www.sqlite.org/src/info/15f0be8a640e7bfa
I vaguely recall some variant of MC/DC coverage that also treats each bit in any bit flag checks as independent boolean conditions. It seems like there could be a similar variant that requires checking each value of such a "leveled" enum independently; if your enum says you can take on values 0-5 and the logic checks <= 2 and <= 5, nonetheless there should be a test case for 0, 1, 3, and 4.
[deleted]
Also this is not even a silent bug, so I would have expected that this would have been caught by someone in production.
I've experienced this a few times before but I just ignored it because I never had access issues after waiting for the other process to finish writing. I wonder what the patch will really do, is it just a better error message or will SQLITE show only committed data?
The top comment is...sad yet funny:
Euphorbium
December 11, 2022 at 2:31 pm
I think I hit the same bug in django, and it took them 5 years to fix it. Django tagline is “webframework for perfectionists with deadlines”. I was fired because of this bug.Even though SQLite bugs are rare, those can be found occasionally.
I remember a bug finder took the sqlite documentation off their website. Collected all their keywords, made up millions of jumbled up queries of random combination between keywords and then ran those overnight to find 10 bugs where the engine crashed. And yes those were also reported and fixed quickly.
I remember a bug finder took the sqlite documentation off their website. Collected all their keywords, made up millions of jumbled up queries of random combination between keywords and then ran those overnight to find 10 bugs where the engine crashed. And yes those were also reported and fixed quickly.
Sounds a lot like fuzzing, which is a really good way to find really esoteric bugs against a spec! I did something like this in college when I was implementing a compiler and it caught a lot of really weird bugs in a lot of peoples codes. The beautiful part of some fuzzers though is they can automatically simplify their complicated test cases to something you can actually look at and reason about.
>Sounds a lot like fuzzing, which is a really good way to find really esoteric bugs against a spec!
QuickCheck style testing is maybe also worth a mention here. Instead of using any possible inputs, like in fuzzing, you restrict yourself to legal inputs, like the keywords here, to get maybe less random crashes, but more likely to find useful corner cases because of the restriction on the search space.
QuickCheck style testing is maybe also worth a mention here. Instead of using any possible inputs, like in fuzzing, you restrict yourself to legal inputs, like the keywords here, to get maybe less random crashes, but more likely to find useful corner cases because of the restriction on the search space.
SQLite is continuosly fuzz tested by at least 2-3 professional teams[0], I am really amazed you could find 10 bugs in one day. Would love to read your write up in this!
[0]: https://www.sqlite.org/testing.html
[0]: https://www.sqlite.org/testing.html
[deleted]
so basically, fuzzing.
https://en.m.wikipedia.org/wiki/Fuzzing
https://en.m.wikipedia.org/wiki/Fuzzing
Sounds more like smart Monkey testing, much older, less feedback.
https://en.m.wikipedia.org/wiki/Monkey_testing
Edit: looks like some consider these the same nowadays.
https://en.m.wikipedia.org/wiki/Monkey_testing
Edit: looks like some consider these the same nowadays.
I wonder if ChatGPT would be good at generating various fuzz testing queries. Maybe too slow? Maybe too repetitive? Maybe too unvarying? Sorry for mentioning ChatGPT (to those who are sick of the infection).
Actually, with one of the problems being faced in that space at present, especially for applications involving factual answers, being "hallucinations" ( ie. essentially as I understand it the level of "creativity" in responses) such "creativity" may well be quite suited to finding those various unusual edge cases.
You know this common trope in fiction and jokes, that advanced computers & AIs can be easily defeated by feeding them a logical inconsistency?
Guess what, we now have a chatbot AI that not only doesn't mind working with nonsense input, it will happily produce logically-inconsistent statements on its own, and can do it so sneakily and convincingly, that it's the human operator who could end up believing logically inconsistent statements, without even realizing it, and possibly end up in serious trouble some time later.
Guess what, we now have a chatbot AI that not only doesn't mind working with nonsense input, it will happily produce logically-inconsistent statements on its own, and can do it so sneakily and convincingly, that it's the human operator who could end up believing logically inconsistent statements, without even realizing it, and possibly end up in serious trouble some time later.
No such thing as software without bugs, but given the incredibly widespread use that SQLite sees the quality as evidenced by the fact that finding a bug is news by itself is extremely high. Something to strive for.
It's news only because the author wrote a blogpost about it.
There are bugs fixed in every SQLite release.
There are bugs fixed in every SQLite release.
I have seen similar error messages of the "database is corrupted ..." type with MariaDB when I simply typed in my SQL incorrectly.
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
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
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??
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”
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”
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.
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.
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.".
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.
I'm sure they love that
> 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.
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.
For what it's worth I think it's largely overcome the reputation of being a "toy" database.
It never was a toy, so that's good.
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.
“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.
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.
I read them as "more/less complexity" not "more/less code". Postgres is heavier weight because it has more complexity; it has more features, it's networked, it has a more involved configuration process. They both have different advantages and can with in problem spaces the other can't.
This is quite unlike the Apple II, which is outmoded and requires a dedicated hobbyist to get working.
Postgres is an excellent default, but preferring lighter solutions does solve problems. It eliminates failure modes and cognitive load. As engineers we seek to eliminate the irrelevant to focus on the interesting. If you can use SQLite and avoid shipping a series of containers, and instead ship a single binary, you've eliminated things to think about.
Neither of them is a silver bullet and you'll be a better engineer if you can do both.
This is quite unlike the Apple II, which is outmoded and requires a dedicated hobbyist to get working.
Postgres is an excellent default, but preferring lighter solutions does solve problems. It eliminates failure modes and cognitive load. As engineers we seek to eliminate the irrelevant to focus on the interesting. If you can use SQLite and avoid shipping a series of containers, and instead ship a single binary, you've eliminated things to think about.
Neither of them is a silver bullet and you'll be a better engineer if you can do both.
This is an enlightening answer. Part of my issue with “lightweight” is that is is vague. I see it used all the time. In this case, if people mean “way less configuration than Postgres,” and “does not need its own service and process(es) to manage,” that makes total sense. And it also gives a hint as to the tradeoffs.
This made me smile, thanks for keeping an open mind, stranger.
Lightweight means I don't need to install some shit on a server somewhere and hope that its reachable at the time I need to access it. If I'm a process running then I loaded from a disk somewhere which means I can in-process sqlite to write to that disk.
Less stuff == less to go wrong == lightweight.
Less stuff == less to go wrong == lightweight.
Not really. Heavier and lightweight usually refer to the amount of features and requirements of a piece of software.
In that regard, it's easier to see which of PostgreSQL and SQLite is lighter. PostgreSQL requires a separate process running with its own config, plus the library to communicate with it, plus all the things Postgres does... On the other hand, SQLite is just a library that reads files in a certain format.
> It solves more problems than SQLite and “lightweight” is not really a concrete benefit for SQLite.
But it is a concrete benefit. Sometimes you'll have restricted environments because either by power or by permissions, you can't install Postgres or any other database server (e.g., mobile phones or embedded software). Or sometimes you just don't want the user to configure their postgres instance and your software for just a few tables (e.g., system utilities/small services that just need a simple database).
In that regard, it's easier to see which of PostgreSQL and SQLite is lighter. PostgreSQL requires a separate process running with its own config, plus the library to communicate with it, plus all the things Postgres does... On the other hand, SQLite is just a library that reads files in a certain format.
> It solves more problems than SQLite and “lightweight” is not really a concrete benefit for SQLite.
But it is a concrete benefit. Sometimes you'll have restricted environments because either by power or by permissions, you can't install Postgres or any other database server (e.g., mobile phones or embedded software). Or sometimes you just don't want the user to configure their postgres instance and your software for just a few tables (e.g., system utilities/small services that just need a simple database).
Here's one concept of heavy vs light: I have often wished I could just email a small Pg db to a colleague. In the same way I can just attach a single file to an email and transmit an entire SQLite DB.
I'm not dissing Pg. I really love Pg and I understand that it's built the way it is for good reasons. But it sure would be awesome to pg_dump and have a single tar that could be "run" with a single pg command without worrying about which version is required, what configuration is required, etc.
When that convenience is the most important requirement, SQLite wins. But that is hardly ever the biggest consideration in which RDBMS I choose.
I'm not dissing Pg. I really love Pg and I understand that it's built the way it is for good reasons. But it sure would be awesome to pg_dump and have a single tar that could be "run" with a single pg command without worrying about which version is required, what configuration is required, etc.
When that convenience is the most important requirement, SQLite wins. But that is hardly ever the biggest consideration in which RDBMS I choose.
> Here's one concept of heavy vs light: I have often wished I could just email a small Pg db to a colleague. In the same way I can just attach a single file to an email and transmit an entire SQLite DB.
The closest to this I've gotten is being able to run MySQL/MariaDB/PostgreSQL/other solutions in containers locally and sending archived data directories, with which they can be launched anywhere else locally, or on server.
I actually had a blog post about how that looks on my servers for other applications: https://blog.kronis.dev/articles/how-i-migrate-apps-between-...
For PostgreSQL, it could look like:
The closest to this I've gotten is being able to run MySQL/MariaDB/PostgreSQL/other solutions in containers locally and sending archived data directories, with which they can be launched anywhere else locally, or on server.
I actually had a blog post about how that looks on my servers for other applications: https://blog.kronis.dev/articles/how-i-migrate-apps-between-...
For PostgreSQL, it could look like:
1. run PostgreSQL in a container, e.g. https://hub.docker.com/_/postgres , use a bind mount for the data directory, /var/lib/postgresql/data
2. once you want to share, use tar/something else to archive the local bind mount directory
3. send the archive and the command to run the container through e-mail or whatever else you prefer
1. receive the run command and attachment
2. unarchive the data directory into a folder
3. run the container with the provided command
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.
[deleted]
I remember switching from postgres to MySQL and there was definitely a learning curve, so even without deployment costs (admittedly the same for mysql -- which were a real thing back then before RDS or Aurora or whatever hosted postgres)... It probably would have been better to use sqlite
It's SQLite… like a mineral/rock. Graphite, Titanite, Erythrite.
Not "SQ Lite".
Not "SQ Lite".
> 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!
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!
> 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.
The argument is generally that language-level correctness would achieve more than emphasising test coverage so heavily.
> 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.
What was so “horrible”?
After you posted the bug, the second comment (and only 6-hours later) had a new release and fix.
He’s talking about the forum software, not the forum community.
Evidence of a wonderfully comfortable life. We should all hope to have horrible experiences that are that inconsequential!
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.
There are database systems that have been around for many years built from the ground up for this use case.
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
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
> 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-q...
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-q...
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)
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)
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
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.
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.
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.
> I found a bug in SQLite
Well, good thing it wasn't a bug in the C compiler you were building sqlite with... even those can come up occasionally.
Well, good thing it wasn't a bug in the C compiler you were building sqlite with... even those can come up occasionally.
It's impressive to find a bug in something like sqlite, which is famous for its larger than the codebase test suite
> larger than the codebase test suite
This tend to be true for most serious projects, that the amount of test code is greater than that of the code that is being exercised.
I think what they are famous for is the quality of the testing suite, rather than the amount.
This tend to be true for most serious projects, that the amount of test code is greater than that of the code that is being exercised.
I think what they are famous for is the quality of the testing suite, rather than the amount.
> This tend to be true for most serious projects, that the amount of test code is greater than that of the code that is being exercised.
Reading this comment, I was thinking "Oh that must mean the test code is 2x or maybe even 3x the amount of source code"
Going to the SQLite web site, I was surprised to find that the test code is 600x larger than the source code. Impressive.
Is this 600:1 ratio typical for other projects? The ones that I have seen are more like 1x or 2x, but I have not worked with many open source systems.
Reading this comment, I was thinking "Oh that must mean the test code is 2x or maybe even 3x the amount of source code"
Going to the SQLite web site, I was surprised to find that the test code is 600x larger than the source code. Impressive.
Is this 600:1 ratio typical for other projects? The ones that I have seen are more like 1x or 2x, but I have not worked with many open source systems.
They count post-codegen test LOC, the checked in ratio is much smaller - used to be something like 5:1. Still super impressive!
Test to regular code ratio shouldn’t be impressive by itself anyway, considering how repetitive, duplicated, and setup-heavy test code can be.
I think a good way of looking at the quality of their tests is how they categorize their tests, which focuses on the different types of problems they want to solve: power loss, concurrency, API issues, logic, etc. https://www.sqlite.org/testing.html
>... larger than the codebase test suite
I wonder if they directly test the concurrent use?
It appears that the fix [1] of the OP bug did not lead to any addition/changes in resp. tests.
[1]:https://www.sqlite.org/src/info/15f0be8a640e7bfa
P.S. looks like Fossil still has issues with content scrolling and wrapping to screen size (mobile).
I wonder if they directly test the concurrent use?
It appears that the fix [1] of the OP bug did not lead to any addition/changes in resp. tests.
[1]:https://www.sqlite.org/src/info/15f0be8a640e7bfa
P.S. looks like Fossil still has issues with content scrolling and wrapping to screen size (mobile).
Actually, they added a unit test in a follow-on change:
https://www.sqlite.org/src/info/dc7dd2d3e50e7cc4
https://www.sqlite.org/src/info/dc7dd2d3e50e7cc4
Indeed, thanks for pointing this one out.
The test seems to test a shared access in rather a serial order. I wonder if underneath this is actually running as concurrent processes?
The test seems to test a shared access in rather a serial order. I wonder if underneath this is actually running as concurrent processes?
I may not be following your point, but I don't believe the root cause of this issue was a race condition, or anything that might be related to concurrency.
The fundamental cause AFAIK was a SQLite connection was attempting to make a state transition (from one type of locking state to another) which shouldn't be allowed under certain circumstances, but the implementation didn't actually enforce this rule. So the added test really does test the root cause.
The fundamental cause AFAIK was a SQLite connection was attempting to make a state transition (from one type of locking state to another) which shouldn't be allowed under certain circumstances, but the implementation didn't actually enforce this rule. So the added test really does test the root cause.
My first time coming across rqlite. Looks awesome. I just finished writing a poor man’s centralized store of SQLite writing the DB file to Minio (s3 compliant) storage but of course has race conditions all over the place. Gonna take a look at replacing with rqlite.
[deleted]
[deleted]
Did you have to get baptized first to be allowed reporting a bug?
Obviously. This is a consecrated codebase[1].
[1]: https://www.sqlite.org/codeofethics.html
[1]: https://www.sqlite.org/codeofethics.html
still just submit a bugreport instead of making a fuss of it.
People are allowed to be proud of their accomplishments. SQLite is extremely high quality software. Identifying a mistake is practically equivalent to receiving a Knuth check.
Maybe this post will inspire others on how to locate other bugs, improving the world for the rest of us.
Maybe this post will inspire others on how to locate other bugs, improving the world for the rest of us.
I found a bug in mySQL and after the mySQL team fixed it, I included a summary of it in my annual employee review. Bug fixing widely-used software is not part of my role, but the company recognized it as an accomplishment.
It is impressive for very popular software to find bugs.
But even if it wasnt, its still a blog post. The entire point is to talk about what you have been doing. Personally, My blog is super inane.
But even if it wasnt, its still a blog post. The entire point is to talk about what you have been doing. Personally, My blog is super inane.
This is all rather tasteful. Making a fuss would be registering a domain name and making a whole freaking website just for the bug.
Long long ago I lost my SMS database on my first Android phone to corruption.
The worst part was if the app encountered an error opening the database, it just deleted it and started over -- no chance of repair to rescue any of the data. I don't think this is done this way anymore.
After that I have installed SMS Backup+ first thing on every new phone.
The worst part was if the app encountered an error opening the database, it just deleted it and started over -- no chance of repair to rescue any of the data. I don't think this is done this way anymore.
After that I have installed SMS Backup+ first thing on every new phone.
It was also promptly fixed, but it makes me feel like the millions of tests sound better than they are in reality …