QTRAN: Extending Metamorphic-Oracle Based Logical Bug Detection Techniques for Multiple-DBMS Dialect Support
Read the paper · doi:10.1145/3728908
What this paper does with SQLancer
SQLancer's oracles are precisely what QTRAN extends: NoREC and TLP are two of the four state-of-the-art techniques it takes and carries to eight DBMSs, and the paper tabulates which systems each currently supports to show the gap it closes. SQLancer is also its measure of the per-DBMS adaptation cost.
QTRAN extends existing metamorphic-oracle logic bug detection techniques to DBMSs they were never written for. The obstacle is that these techniques depend on a specific DBMS's grammar to build valid statement pairs, so only a few systems are supported and extending them takes considerable manual work -- the paper counts the lines of code each tool needs per system. QTRAN uses an LLM in two phases: a transfer phase that identifies dialect differences and uses SQL documentation to translate the original queries, and a mutation phase using a fine-tuned model to mutate them while preserving the metamorphic relation. Over 99% of transferred pairs satisfied the relations, and it found 24 logic bugs.
How it was classified
uses infrastructure — uncertain
QTRAN takes statement pairs from existing implementations of these oracles, which implies running them, but no mention read says its own tool is built on SQLancer's codebase.
extends technique — yes
M7 states four state-of-the-art metamorphic oracles were selected for extension, NoREC and TLP among them, and M8 and M9 describe each before it is carried to DBMSs it did not support. Extending them is the paper's contribution.
Non-optimizing Reference Engine Construction (NoREC)Ternary Logic Partitioning (TLP)
In our evaluation, we selected four state-of-the-art MOLT s for extension: NoRec [ 37], TLP [38], Pinolo [20], and DQE [44].
Below, we detail each of these tools: (1)NoRec [37]: This technique involves transferring predicates from the WHERE clause to the SELECT clause.
(2)TLP [38]: This method decomposes a single query into three separate queries, each isolated by its predicates.
compares with — uncertain
The oracles are extended rather than run against QTRAN as competitors; the mentions read do not show a head-to-head comparison.
describes as state of the art — yes
M3 says the metamorphic testing approach has been recognised as state-of-the-art in DBMS testing for logical bug detection, and M7 calls the four selected oracles state-of-the-art.
Notably, the metamorphic testing based approach MOLT has been recognized to be state-of-the-art in DBMS testing for logical bug detection [ 20,38,40].
In our evaluation, we selected four state-of-the-art MOLT s for extension: NoRec [ 37], TLP [38], Pinolo [20], and DQE [44].
SQLancer publications it cites (5)
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 | 2023. SQLancer. https://github.com/sqlancer/sqlancer. Accessed: 2024-10-24. | sqlancer publication |
| 37 | 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 |
| 38 | 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 |
| 39 | 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 |
| 40 | Manuel Rigger and Zhendong Su. 2022. Intramorphic testing: A new approach to the test oracle problem. In Proceedings of the 2022 ACMSIGPLAN International Symposium on New Ideas, New Paradigms, and Reflections on Progr... | project authored |
Every place it refers to SQLancer (22)
22 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 | However, the complex code logic and diverse functionalities of DBMSs often make them susceptible to bugs, especially logical bugs that result in incorrect result sets being returned without obvious symptoms [ 8,20,24,26,37–39]. |
citation marker |
1 Introduction page 2 |
| M2 | Notably, metamorphic oracle, a widelyused method that constructs SQL statements maintaining either exact [ 25,28,29,37–39,44] or approximate equivalence [ 20] with the original query, has been recognized as state-of-the-art in DBMS testing for logical bug detection [ 20,38,40]. |
citation marker |
1 Introduction page 2 |
| M3 | Notably, the metamorphic testing based approach MOLT has been recognized to be state-of-the-art in DBMS testing for logical bug detection [ 20,38,40]. |
citation marker |
2 Background and Motivation page 4 |
| M4 | Table 1 shows the lines of extra code of SQLancer, SQRight, Pinolo and DQE to adapt for SQLite, PostgreSQL, MySQL, and MariaDB. |
name |
2 Background and Motivation page 5 |
| M5 | The lines of code of SQLancer, SQRight, Pinolo and DQE to adapt for SQLite, PostgreSQL, MySQL, and MariaDB. |
name |
2 Background and Motivation page 5 |
| M6 | LoC SQLancer SQLRight Pinolo DQE SQLite 10,020 17,120 - 7,606 PostgreSQL 8,012 93,671 - MySQL 6,512 76,067 8,055 4,225 MariaDB 2,085 - 8,055 2,092 More importantly, there are 423 different DBMSs on the market [ 43], most of which use different grammar. |
name |
2 Background and Motivation page 5 |
| M7 | In our evaluation, we selected four state-of-the-art MOLT s for extension: NoRec [ 37], TLP [38], Pinolo [20], and DQE [44]. |
technique |
4 Evaluation page 11 |
| M8 | Below, we detail each of these tools: (1)NoRec [37]: This technique involves transferring predicates from the WHERE clause to the SELECT clause. |
technique |
4 Evaluation page 11 |
| M9 | (2)TLP [38]: This method decomposes a single query into three separate queries, each isolated by its predicates. |
technique |
4 Evaluation page 11 |
| M10 | Applicability of existing MOLT s for the selected DBMSs DBMS NoREC TLP Pinolo DQE MySQL ✗ ✓ ✓ ✓ MariaDB ✓ ✗ ✓ ✓ TiDB ✗ ✓ ✓ ✓ PostgreSQL ✓ ✗ ✗ ✗ SQLite ✓ ✓ ✗ ✓ MonetDB ✗ ✗ ✗ ✗ DuckDB ✗ ✓ ✗ ✗ ClickHouse ✗ ✗ ✗ ✗ Proc. |
technique |
4 Evaluation page 11 |
| M11 | Specifically, NoREC uses SQLite, DQE uses TiDB, while TLP and Pinolo use MySQL. |
technique |
4.1 Q1. Effectiveness and Efficiency page 12 |
| M12 | The source statement pair is derived from the NoREC oracle, which detects bugs by moving the predicate from the “ WHERE ” clause into the “ SUM” function, ensuring semantic consistency between the original and mutated statements. | technique | 4.2 Q2. Bug Detection page 14 |
| M13 | Sub-figure (a) demonstrates a SQL statement pair generated by NoREC in SQLite without errors. | technique | 4.2 Q2. Bug Detection page 15 |
| M14 | , NoREC, TLP, Pinolo, and DQE) and then directly transfer both original and mutated queries to the target DBMS in transfer phase. | technique | 4.4 Q4. Contributions of Fine-tuning page 17 |
| M15 | However, existing studies [ 1,13,35,37,38,42] highlight that differential testing is limited in its applicability, as not all DBMSs share the same SQL grammar or operation semantics, despite supporting the core SQL syntax, with each DBMS forming its own dialect [ 42], thus restricting the generality of differential ... | citation marker | 6 Related Work page 19 |
| M16 | The second category is the oracle-guided synthesis approach [ 39]. | citation marker | 6 Related Work page 19 |
| M17 | PQS synthesizes a query that guarantees to return a specific row using its manually implemented interpreter. | technique | 6 Related Work page 19 |
| M18 | If the tested DBMS fails to fetch the row, PQS identifies a logical bug. | technique | 6 Related Work page 19 |
| M19 | To avoid the drawbacks of the differential oracle, many fuzzing methods [ 20,28,37,38] use the metamorphic oracle to detect logical bugs. | citation marker | 6 Related Work page 19 |
| M20 | NoREC [ 37] creates a query by transferring predicates from the WHERE clause to the SELECT clause; the discovery of a logical bug occurs if results vary post-movement. | technique | 6 Related Work page 19 |
| M21 | Similarly, TLP [ 38] breaks down a single query into three, isolating each by its predicates, asserting a logical bug if the collective results diverge from the original query’s outcome. | technique | 6 Related Work page 19 |
| M22 | SQLancer [ 3] integrates the above techniques and has been deployed to test various DBMSs. | name | 6 Related Work page 19 |