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.
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.
> but it makes me feel like the millions of tests sound better than they are in reality
What it should make you wonder is if software as well-tested as SQLite still has bugs like this, how much worse is the situation in software with fewer tests?
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.
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.
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.
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.
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.
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.
> 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.
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
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.
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.
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)
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...
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.
> 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.
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.
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.
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....
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 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
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!
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…
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.
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?
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.
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.
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".
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'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.
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.
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?
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.
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.
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!
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.
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.
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.
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”
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.
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.
> 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'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.
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 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.
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.
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).
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.
> 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.
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
And on the receiving end:
1. receive the run command and attachment
2. unarchive the data directory into a folder
3. run the container with the provided command
It's not perfect, but it's one of the more portable methods I've found (though there are file system issues between Linux and Windows sometimes, like when running PHP apps).
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.
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
> 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!
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.
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.
> 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.
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)
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 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
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.
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.
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.
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.
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.
It was also promptly fixed, but it makes me feel like the millions of tests sound better than they are in reality …