Mozi: Discovering DBMS Bugs via Configuration-Based Equivalent Transformation
Read the paper · doi:10.1145/3597503.3639112
What this paper does with SQLancer
SQLancer's three oracles are the point of contrast and the baseline: Mozi argues that TLP applies only to queries using particular clauses and that NoREC's equivalent-query construction is limited by how well SQL semantics can be modelled, then runs PQS, NoREC and TLP against itself and reports 21 to 25 more bugs than each.
Mozi finds DBMS bugs by comparing one system against itself under different configurations rather than by comparing semantically equivalent queries. Its motivation is that metamorphic testing needs a precise grasp of the SQL specification to build equivalent inputs, and the specification is vague and intricate enough to make modelling query semantics hard. Mozi analyses the query plan, changes configuration to produce an equivalent system, re-executes and compares: different results indicate correctness bugs, faster execution with optimisations closed indicates performance bugs. It found 101 previously unknown bugs across four DBMSs.
How it was classified
uses infrastructure — no
SQLancer is run as a baseline with its three oracles; nothing shown says Mozi is built on it.
extends technique — no
Configuration-based equivalent transformation replaces the equivalent-query idea rather than extending it.
compares with — yes
Mozi is compared against SQLancer running PQS, NoREC and TLP, with branch coverage and bug counts reported against each.
Pivoted Query Synthesis (PQS)Non-optimizing Reference Engine Construction (NoREC)Ternary Logic Partitioning (TLP)
We also used SQLancer [ 45] (with test oracles PQS [ 48], TLP [ 47], and NoREC [ 46]), Apollo [25], and Amoeba [33] to test these DBMSs, but they can only find a subset of these bugs (shown in Section 5.
Specifically, we compared Mozi𝑐𝑜𝑟against SQLancer using three logic test oracles, namely PQS [ 48], NoREC [ 46], and TLP [ 47] for correctness bug detection.
Compared to PQS, NoREC, and TLP, Mozi𝑐𝑜𝑟 found 25, 22, and 21 more bugs, respectively.
describes as state of the art — no
The oracles are described by their limitations rather than as the state of the art.
SQLancer publications it cites (6)
Bibliography entries that resolved to a SQLancer publication, or to a paper by one of the project's authors. A sentence citing one of these numbers is a reference to SQLancer even when it never writes the name.
| # | Entry | Matched as |
|---|---|---|
| 3 | Jinsheng Ba and Manuel Rigger. 2023. Testing database engines via query plan guidance. In Proceedings of International Conference on Software Engineering (ICSE). | sqlancer publication · QPG |
| 44 | Manuel Rigger. 2024. Bugs found in Database Management Systems. https: //www.manuelrigger .at/dbms-bugs. Accessed: January 18, 2024. | project authored |
| 45 | Manuel Rigger. 2024. SQLancer Website. https://github .com/sqlancer/sqlancer. Accessed: January 18, 2024. | sqlancer publication |
| 46 | Manuel Rigger and Zhendong Su. 2020. Detecting optimization bugs in database engines via non-optimizing reference engine construction. In Proceedings of the 28th ACM Joint Meeting on European Software Engineering Conf... | sqlancer publication · NOREC |
| 47 | Manuel Rigger and Zhendong Su. 2020. Finding bugs in database systems via query partitioning. Proceedings of the ACM on Programming Languages 4, OOPSLA (2020), 1–30. | sqlancer publication · TLP |
| 48 | Manuel Rigger and Zhendong Su. 2020. Testing database engines via pivoted query synthesis. In 14th USENIX Symposium on Operating Systems Design and Implementation OSDI 20). 667–682. | sqlancer publication · PQS |
Every place it refers to SQLancer (24)
24 sentences, each stored verbatim from the extracted text with where it was found and how. “Citation marker” means the sentence names no tool at all and was reached through a reference number that resolved to a SQLancer publication.
| Id | Sentence | Found by | Where |
|---|---|---|---|
| M1 | It is crucial to ensure the correctness, reliability, and performance of DBMSs [ 4,8, 10,40,44]. |
citation marker project authored |
1 INTRODUCTION page 1 |
| M2 | Metamorphic testing is a prevalent approach to testing DBMS, which focuses on building metamorphic relations [5, 6, 32, 46,47,49]. |
citation marker |
1 INTRODUCTION page 1 |
| M3 | In 24-hour experiments, Mozi covers 64,973, 54,464, 43,236, 28,499, and 14,084 more branches, detects 25, 22, 21, 24, and 26 more bugs than PQS [ 48], NoREC [ 46], TLP1[47],Apollo [25], and Amoeba [33], respectively. |
technique |
1 INTRODUCTION page 2 |
| M4 | 1PQS, NoREC, and TLP are three correctness test oracles utilized in SQLancer. |
name |
2 BACKGROUND ANDMOTIVATION page 2 |
| M5 | For example, the NoREC [ 46] method of SQLancer detects the logic bugs of DBMS optimizer by constructing equivalent non-optimization SQL query which can not be optimized by DBMSs based on the existing SQL query. |
name |
2 BACKGROUND ANDMOTIVATION page 3 |
| M6 | For instance, the TLP [ 47] method can only be tested for queries that use WHERE ,HAVING, GROUP BY, aggregation functions, and DISTINCT features. |
technique |
2 BACKGROUND ANDMOTIVATION page 3 |
| M7 | We also used SQLancer [ 45] (with test oracles PQS [ 48], TLP [ 47], and NoREC [ 46]), Apollo [25], and Amoeba [33] to test these DBMSs, but they can only find a subset of these bugs (shown in Section 5. |
name |
5.2 DBMS Vulnerability Detection page 7 |
| M8 | Specifically, SQLancer and Amoeba detect the bugs by constructing the equivalent SQL query, which does not change the configuration of DBMS. |
name |
5.2 DBMS Vulnerability Detection page 7 |
| M9 | Specifically, we compared Mozi𝑐𝑜𝑟against SQLancer using three logic test oracles, namely PQS [ 48], NoREC [ 46], and TLP [ 47] for correctness bug detection. |
name |
5.3 Comparison with Other Techniques page 8 |
| M10 | In the following text, we will refer to these three implementations of SQLancer by their respective oracle names. |
name |
5.3 Comparison with Other Techniques page 8 |
| M11 | Compared to PQS, NoREC, and TLP, Mozi𝑐𝑜𝑟 found 25, 22, and 21 more bugs, respectively. |
technique |
5.3 Comparison with Other Techniques page 8 |
| M12 | This disparity arises because PQS, NoREC, and TLP employ highly customized test oracles, with the latter two incorporating statement changes not utilized in Mozi𝑐𝑜𝑟. | technique | 5.3 Comparison with Other Techniques page 8 |
| M13 | Corr ectness Performance DBMSPQS NoREC TLP Mozi𝑐 𝑜𝑟Apollo Amoeba Mozi𝑝 𝑒𝑟 MySQL 1 2 2 9 1 – 11 MariaDB 2 3 2 10 0 – 13 Clickhouse 0 1 3 6 1 – 2 Postgr eSQL 1 1 1 4 1 1 1 T otal 4 7 8 29 3 1 27 Impr ovement 25↑ 22↑ 21↑ - 24↑ 26↑ -Table 4 shows the number of branches covered by each method in 24 hours. | technique | 5.3 Comparison with Other Techniques page 8 |
| M14 | Specifically, compared to three correctness bug testing methods, Mozi𝑐𝑜𝑟covered a total of 64,973, 54,464, and 43,236 more branches than PQS, NoREC, and TLP, respectively. | technique | 5.3 Comparison with Other Techniques page 8 |
| M15 | Corr ectness Performance DBMSPQS NoREC TLP Mozi𝑐 𝑜𝑟Apollo Amoeba Mozi𝑝 𝑒𝑟 MySQL 50,294 52,984 60,274 75,293 70,923 – 74,836 MariaDB 40,154 42,938 45,792 54,982 46,365 – 55,387 Clickhouse 65,980 69,842 69,928 79,852 72,388 – 80,142 Postgr eSQL 59,811 60,984 61,982 71,085 63,748 56,844 70,928 T otal 216,239 226,748 23... | technique | 5.3 Comparison with Other Techniques page 8 |
| M16 | Specifically, PQS, NoREC, TLP, andAmoeba are constructed based on specific features to create test cases, which limits the SQL grammar they can support. | technique | 5.3 Comparison with Other Techniques page 8 |
| M17 | For example, NoREC detects logic bugs in the DBMS optimizer by constructing equivalent optimized and unoptimized queries based on the optimizer rules. | technique | 5.3 Comparison with Other Techniques page 8 |
| M18 | As a result, Mozi can support more SQL grammar and cover more branches compared to PQS, NoREC, and TLP, which explains why it can detect more correctness bugs in these DBMSs. | technique | 5.3 Comparison with Other Techniques page 8 |
| M19 | Most fuzzers [ 3,16,24,28,30,50,57,59,62] focus on generating complex and valid SQL test cases to find the memory safety bugs. | citation marker | 7 RELATED WORK page 10 |
| M20 | QPG [ 3] in SQLancer introduces a mutation technique to use DDL (e. | name | 7 RELATED WORK page 10 |
| M21 | Many works [ 30,33,46–48,53] detect errors in a DBMS by verifying whether the execution results of SQL statements conform to predefined rules. | citation marker | 7 RELATED WORK page 11 |
| M22 | NOREC [ 46] and TLP [ 47] are two test oracles used in SQLancer. | name | 7 RELATED WORK page 11 |
| M23 | NoREC converts a SQL query to one that cannot be optimized by optimizers and compares the two’s execution results to find optimizer bugs. | technique | 7 RELATED WORK page 11 |
| M24 | TLP employs the concept of partitioning, wherein a problem is identified by partitioning the original query into multiple more complex queries. | technique | 7 RELATED WORK page 11 |