How SQLite Is Tested
sqlite.org
sqlite.org
Static analysis has not proven to be helpful in finding bugs. We cannot call to mind a single problem in SQLite that was detected by static analysis that was not first seen by one of the other testing methods described above. On the other hand, we have on occasion introduced new bugs in our efforts to get SQLite to compile without warnings. </quote>
The sort of testing that the SQLite developers do is, while depressingly uncommon, not that unreasonable for any sort of software been around for a few years. If your bug fixing methodology is "write test to reproduce bug, then fix; repeat" you end up with a pile of test cases as a result.
This is where it goes into the commercial software bit: while that's a great regression test policy, I don't believe it's at all common. There are places where testing isn't really done. Probably most places. If you have no tests or bad tests, then static analysis is better than nothing.
I've also never used SQLite on an embedded system.
Engler (whose students founded Coverity) et al. had an OSDI "best paper" last year on using static analysis (with constraint solvers etc.) to automatically generate test cases, which actually beats hand written test cases for glibc with years of development.
It seems MySQL could share a good chunk of SQLite's tests. If I were MySQL, I'd run these in addition to their own test suites. It was nice of SQLite to set it up for them ;)
Just goes to show how hard testing all cases is! How many cases do you need to get full branch coverage on "a>b && c!=25"? I'm thinking 5, but I'm not very sure of that.
For instance, .quit doesn't exit the program when running a script from the command-line (or C's system() ) via .init.
Also, have more than 2 inner joins and a query takes 10 minutes. I fixed this by writing some custom queries.
D. Richard Hipp (the author) refuses to accept these are bugs, when they obviously are. Also, people like Mozilla get first call on bug fixes, so the 'little guy' gets no support for no $$ - I read it costs $75,000 for 'special' status and $1500 a year for support.
I can understand if somebody who wrote an incredibly useful system and released it into the public domain isn't always willing to take the time to fix bugs they consider minor, without being compensated to do so. (I'm glad he's sharing it, at all.) I don't think the issue is Ulrich Drepper-like behavior here.
About the inner joins - SQLite can be small because it doesn't have the tremendous amount of query optimization that e.g. Postgresql does. That's like complaining that a bicycle makes a lousy tow truck; significantly improving its query optimizer would require changing it into a completely different kind of tool, and a big part of its utility comes from being small enough for embedded use.
My complaint isn't that he won't fix the bug for free, it's that he won't acknowledge it's a bug, and thus won't fix it even in a year's time.
Also, I'd pay for the bug to be fixed, by hours, but I saw it costs from $1500 to $75,000 for special service. So I had to fix the bug myself.
The .quit bug, I found already mentioned on the Internet, but unsolved. I fixed that one myself with a simple hack [.quit = exit(0) ] and recompiled SQLite.
What I realised is that some free libraries are worth the effort of having to cope with odd bugs. For instance, I'd rather spend 5 hours writing a 'cascading filter' which does its own inner joining but on top of SQLite, than 6 months writing an SQL database engine!
Also, I didn't change a line of SQLite code, it's all in my queries.
In SQLite:
2 inner joins took about 10 seconds. 3 inner joins in one query were taking 10 minutes.
I was using inner joins to filter - ie shrink the table - when a row had multiple fields in one column (eg, a house has 1 front door but 3 bathrooms).
What should happen in SQLite is that each progressive inner join should make the search table smaller, not bigger. So a 3-join query should be quicker than a 2-join query.
The solution was to create some temp tables, do 1 inner join at a time, and shrink the temp tables down myself.
Now a query with 4 inner joins takes 8 seconds, without changing a line of SQLite. You can't tell me that's not a bug! :-)
The current implementation of SQLite uses only loop joins. That is to say, joins are implemented as nested loops.
Know your tools.
Sqlite doesn't intend to include a very smart query planner simply because that would make it much larger and more complex. That means it'll do stupid things for some queries sometimes, but in return, you get a small library that's quick for common queries.