4 ms·

Combined with the correctness fuzzing results by SQLancer [1] where SQLite had 179 issues revealed (vs. 11 in PostgreSQL), I think the 100% coverage is a lot le

Combined with the correctness fuzzing results by SQLancer [1] where SQLite had 179 issues revealed (vs. 11 in PostgreSQL), I think the 100% coverage is a lot less meaningful than I had thought. Robustness is not necessarily correctness and if the unit tests are just mirror images of the code, their results may help against regressions but little for correctness. [1]: https://www.manuelrigger.at/dbms-bugs/ https://www.manuelrigger.at/dbms-bugs/

simlevesque 6y ago
They don't just have full coverage. They have 3 completely different test suites.
- jbverschoor 6y ago
  Coverage doesn't mean correct behavior. It's a vanity metric, which will prevent programming errors, such as not checking for null, division by zero. I've seen so many instances of useless tests which don't actually test properly, but simply walk the lines. Of course, for sqlite I assume the tests are very decent.
dathinab 6y ago
Many code coverage tools tell you just if a line/"statement" or similar had been executed during any test. But that bit might have many possible variants in which you can execute it. E.g. if you have two if/else blocks in a row there are potentially 4 path of execution (if,if; if,else;else,if;else,else). Only two of them need to be hit to get what corresponds to 100% test coverage in most tools. If you consider that you likely call functions in that if and else statements which can have further branching and similar you might understand why firstly 100% coverage doesn't say to much and secondly why a "real" 100% Coverage if all possible execution paths is normally infeasible. (Through by using a more powerful type system combined with proofs around the type system and your usage of it you likely can massively prune the number of paths which need testing reachable, but at this point you probably should go full in with the proofs instead of using tests ;=)
- moonchild 6y ago
  At least with gcov, it tells you if a given line of code had all branches taken or only some.
masklinn 6y ago
> Combined with the correctness fuzzing results by SQLancer [1] where SQLite had 179 issues revealed (vs. 11 in PostgreSQL), I think the 100% coverage is a lot less meaningful than I had thought. That's really not a honest view of it, the SQLancer page specifically notes: > The SQLite3 developers were most responsive and very appreciative of our bug reports. They fixed the bugs we reported at an impressive speed, which is why we concentrated on testing this DBMS. They don't provide run times, but they're empathetically stating most of their runtime is on SQLite. For instance they only have 5 issues for MariaDB because… they stopped testing it as the devs were not responsive.
- ysleepy 6y ago
  I am aware. The thought I had could maybe better expressed as: "If even SQLite, with its almost unsurpassed testing, has so many 'issues', then that approach of testing cannot be seen as a strong indicator of correctness." Which is sort of a truism, but seeing the trend of more and more unit tests instead of also investing in other strategies made me blurt that out. That said, I will continue to use sqlite without hesitation, the robustness is just soothing in a world of terrible libraries. We can argue about the weak typing though :P
  - pdimitar 6y ago
    I love sqlite but I wish they invested in a strict mode. Although thinking about it that's not really "a strict mode of the existing sqlite3" and it's more likely to be "an entirely different library altogether, with some shared code"... I am basically after an embedded Potgres I suppose. I really wish sqlite went into that space. Or anyone else, really.
pizza234 6y ago
> where SQLite had 179 issues revealed (vs. 11 in PostgreSQL) While I think the skepticism towards the 100% coverage effectiveness is valid, I'm not sure how to make meaningful conclusions from the number of bugs per product reported by that analysis (the analysis itself is certainly a really good job). MySQL's bug tracker for example is huge (but I don't imply anything negative). During my last two work days, I've experienced two bugs, one of whom was new and caused a server crash with a SELECT. A couple of months ago I've found another server-crashing SELECT, which was also a new bug. I've filed a number of other bugs in the order of the dozens, over the last years. There is a significant number of bugs reported in the SQLite fulltext functionality (if I understand "FTS" correctly). I don't see any in the MySQL section, and I'm perplexed, since the MySQL FT implementation is (relatively) poor, and I'd be surprised if an in-depth exam wouldn't find any bug. Possibly, they've dedicated a significant amount of resources specifically to SQLite, either in terms of time spent testing or time spent tooling specific to it.
- mrigger 6y ago
  Yes, you are right, the results are not comparable between DBMS that we tested. For MySQL, the large number of existing bugs prevented us from going further, and also testing more advanced functionality (like FTS). For SQLite (and DuckDB), bugs were typically fixed within hours, which is why we could comprehensively test them. For SQLite, we also tested three extensions (besides FTS, also R*Tree and DBSTAT).
mrigger 6y ago
For DBMS, the authors of the APOLLO system found the following [1]: "Code coverage is a frequently used feedback mechanism in fuzzing engines [22, 27]. We found that this metric is not particularly useful for fuzzing DBMSs, since the core components of DBMS (e.g., query optimizer) already have high coverage (e.g., >95%) after running tens of queries." [1] APOLLO: Automatic Detection and Diagnosis of Performance Regressions in Database Systems http://www.vldb.org/pvldb/vol13/p57-jung.pdf http://www.vldb.org/pvldb/vol13/p57-jung.pdf

simlevesque 6y ago

They don't just have full coverage. They have 3 completely different test suites.

jbverschoor 6y ago
Coverage doesn't mean correct behavior. It's a vanity metric, which will prevent programming errors, such as not checking for null, division by zero. I've seen so many instances of useless tests which don't actually test properly, but simply walk the lines. Of course, for sqlite I assume the tests are very decent.

dathinab 6y ago

Many code coverage tools tell you just if a line/"statement" or similar had been executed during any test. But that bit might have many possible variants in which you can execute it. E.g. if you have two if/else blocks in a row there are potentially 4 path of execution (if,if; if,else;else,if;else,else). Only two of them need to be hit to get what corresponds to 100% test coverage in most tools. If you consider that you likely call functions in that if and else statements which can have further branching and similar you might understand why firstly 100% coverage doesn't say to much and secondly why a "real" 100% Coverage if all possible execution paths is normally infeasible. (Through by using a more powerful type system combined with proofs around the type system and your usage of it you likely can massively prune the number of paths which need testing reachable, but at this point you probably should go full in with the proofs instead of using tests ;=)

moonchild 6y ago
At least with gcov, it tells you if a given line of code had all branches taken or only some.

masklinn 6y ago

> Combined with the correctness fuzzing results by SQLancer [1] where SQLite had 179 issues revealed (vs. 11 in PostgreSQL), I think the 100% coverage is a lot less meaningful than I had thought. That's really not a honest view of it, the SQLancer page specifically notes: > The SQLite3 developers were most responsive and very appreciative of our bug reports. They fixed the bugs we reported at an impressive speed, which is why we concentrated on testing this DBMS. They don't provide run times, but they're empathetically stating most of their runtime is on SQLite. For instance they only have 5 issues for MariaDB because… they stopped testing it as the devs were not responsive.

ysleepy 6y ago
I am aware. The thought I had could maybe better expressed as: "If even SQLite, with its almost unsurpassed testing, has so many 'issues', then that approach of testing cannot be seen as a strong indicator of correctness." Which is sort of a truism, but seeing the trend of more and more unit tests instead of also investing in other strategies made me blurt that out. That said, I will continue to use sqlite without hesitation, the robustness is just soothing in a world of terrible libraries. We can argue about the weak typing though :P
- pdimitar 6y ago
  I love sqlite but I wish they invested in a strict mode. Although thinking about it that's not really "a strict mode of the existing sqlite3" and it's more likely to be "an entirely different library altogether, with some shared code"... I am basically after an embedded Potgres I suppose. I really wish sqlite went into that space. Or anyone else, really.

pizza234 6y ago

> where SQLite had 179 issues revealed (vs. 11 in PostgreSQL) While I think the skepticism towards the 100% coverage effectiveness is valid, I'm not sure how to make meaningful conclusions from the number of bugs per product reported by that analysis (the analysis itself is certainly a really good job). MySQL's bug tracker for example is huge (but I don't imply anything negative). During my last two work days, I've experienced two bugs, one of whom was new and caused a server crash with a SELECT. A couple of months ago I've found another server-crashing SELECT, which was also a new bug. I've filed a number of other bugs in the order of the dozens, over the last years. There is a significant number of bugs reported in the SQLite fulltext functionality (if I understand "FTS" correctly). I don't see any in the MySQL section, and I'm perplexed, since the MySQL FT implementation is (relatively) poor, and I'd be surprised if an in-depth exam wouldn't find any bug. Possibly, they've dedicated a significant amount of resources specifically to SQLite, either in terms of time spent testing or time spent tooling specific to it.

mrigger 6y ago
Yes, you are right, the results are not comparable between DBMS that we tested. For MySQL, the large number of existing bugs prevented us from going further, and also testing more advanced functionality (like FTS). For SQLite (and DuckDB), bugs were typically fixed within hours, which is why we could comprehensively test them. For SQLite, we also tested three extensions (besides FTS, also R*Tree and DBSTAT).

mrigger 6y ago

For DBMS, the authors of the APOLLO system found the following [1]: "Code coverage is a frequently used feedback mechanism in fuzzing engines [22, 27]. We found that this metric is not particularly useful for fuzzing DBMSs, since the core components of DBMS (e.g., query optimizer) already have high coverage (e.g., >95%) after running tens of queries." [1] APOLLO: Automatic Detection and Diagnosis of Performance Regressions in Database Systems http://www.vldb.org/pvldb/vol13/p57-jung.pdf http://www.vldb.org/pvldb/vol13/p57-jung.pdf