I found a bug in SQLite
philipotoole.com
philipotoole.com
It was also promptly fixed, but it makes me feel like the millions of tests sound better than they are in reality …
> 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...
> 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.
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...
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.
Then perhaps you're in the wrong thread to be saying anything at all.
If there wasn't a competitive advantage given it has no sales, 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?
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)
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.
Fwiw, that's what monorepos are good for.
Sadly it's hard to make a "world monorepo"
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.
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...
An example build system that can do that is https://bazel.build
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.
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?
> 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...
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.
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.
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.
I didn't get bitten by this because I read the docs, but I noticed how easy it would be to misconfigure.
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.
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.
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
("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 )
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?
I don't feel the need to reply to every post I disagree with.
Or maybe you only remember the ones that bit you, and forget the ones that didn’t.
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.
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.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.
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.
https://en.m.wikipedia.org/wiki/Monkey_testing
Edit: looks like some consider these the same nowadays.
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.
There are bugs fixed in every SQLite release.
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.
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”
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.
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.
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.
Less stuff == less to go wrong == lightweight.
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.
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).
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.
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
Not "SQ Lite".
What was so “horrible”?
After you posted the bug, the second comment (and only 6-hours later) had a new release and fix.
There are database systems that have been around for many years built from the ground up for this use case.
This bug can affect anybody using an in-memory version of a SQLite database. That was the point of writing the C unit test.
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...
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!
The argument is generally that language-level correctness would achieve more than emphasising test coverage so heavily.
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)
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.
Well, good thing it wasn't a bug in the C compiler you were building sqlite with... even those can come up occasionally.
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.
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.
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).
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 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.
Maybe this post will inspire others on how to locate other bugs, improving the world for the rest of us.
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.
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.