← Research building on SQLancer

Jiaqi Li, Ke Wang, Yaoguang Chen, Yajin Zhou, Lei Wu, Jiashui Wang. 2023. arXiv.org.

Read the paper · arXiv:2312.04941

What this paper does with SQLancer

SQLancer is Kangaroo's baseline and the source of the limitation it targets. The paper describes the three oracles -- PQS, NoREC and TLP -- as each placing limitations on the SQL queries they can judge, and works through why NoREC in particular cannot detect the motivating bug. In the comparison against Squirrel, SQLancer and SQLRight, SQLancer found 1 bug to Kangaroo's 17, and Kangaroo reached several times more program states than what the paper calls the rule-based tool SQLancer. Kangaroo tests DBMSs with context-sensitive instantiation and multi-plan execution. Its argument is that the existing oracles constrain which SQL they can judge -- NoREC needs a WHERE clause, so it reaches only bugs in WHERE optimisation -- and that they explore only part of a query's plan space. Kangaroo instantiates queries against the live schema and forces execution down several plans. Over 24 hours on three DBMSs it found 17 bugs. Written by claude-opus-5 from the 36 places this paper refers to SQLancer. The quotations below are the paper's own words, stored verbatim when the text was extracted.

How it was classified

uses infrastructure — no

SQLancer is run as a baseline; Kangaroo's instantiation and multi-plan execution are its own.

extends technique — no

The oracles are described as constraining what they can judge, which is the gap Kangaroo addresses with a different mechanism rather than a development of theirs.

compares with — yes

M4 names SQLancer among the leading tools compared against, M5 gives the 24-hour bug counts -- 17 against SQLancer's 1 -- and M6 the difference in program states reached.

We also compared Kangaroo with leading DBMS testing tools, such as Squirrel, SQLancer, and SQLRight. M4 · 1 Introduction · page 2
Specifically, after conducting a 24-hour test on the three DBMSs, Kangaroo successfully detected 17 bugs, while SQLancer, Squirrel, and SQLRight only identified 1, 3, and 6 bugs, respectively. M5 · 1 Introduction · page 2
3x more program states than the rule-based tool SQLancer. M6 · 1 Introduction · page 2

describes as state of the art — no

SQLancer is called a leading DBMS testing tool and a rule-based one; the paper stops short of calling it the state of the art, and its argument is about what the oracles cannot reach.

SQLancer publications it cites (4)

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.

#EntryMatched as
14 M. Rigger and Z. Su, “Testing database engines via pivoted query synthesis,” in 14th USENIX Symposium on Operating Systems Design and Implementation (OSDI 20), 2020, pp. 667–682. sqlancer publication · PQS
15 ——, “Detecting optimization bugs in database engines via non-optimizing reference engine construction,” in Proceedings of the 28th ACM Joint Meeting on European Software Engineering Conference and Symposium on the Fou... sqlancer publication · NOREC
16 ——, “Finding bugs in database systems via query partitioning,” Proceedings of the ACM on Programming Languages, vol. 4, no. OOPSLA, pp. 1–30, 2020. sqlancer publication · TLP
28 “Bugs found in database management systems,” https: //www.manuelrigger.at/dbms-bugs/, 2022. project authored

Every place it refers to SQLancer (36)

36 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 Second, oracles used by existing systems [13–17] to detect logic bugs have strict requirements on the SQL statements. citation marker
motivation 		1 Introduction
page 1
M2 For example, NoREC [15] requires that the effective SQL query in a test case has WHERE clauses, thus it can only detect logic bugs due to the optimization of WHERE clauses. technique
motivation 		1 Introduction
page 1
M3 Furthermore, both NoREC and TQS attempt to explore multiple query plans of queries but are only capable of covering partial query plans. technique
motivation 		1 Introduction
page 1
M4 We also compared Kangaroo with leading DBMS testing tools, such as Squirrel, SQLancer, and SQLRight. name
baseline 		1 Introduction
page 2
M5 Specifically, after conducting a 24-hour test on the three DBMSs, Kangaroo successfully detected 17 bugs, while SQLancer, Squirrel, and SQLRight only identified 1, 3, and 6 bugs, respectively. name
result comparison 		1 Introduction
page 2
M6 3x more program states than the rule-based tool SQLancer. name
result comparison 		1 Introduction
page 2
M7 The rule-based ones [14–17, 24] generate test cases following a predefined model to ensurethe generated SQL queries can pass the SQL parser. citation marker
background 		2.4 DBMS Testing
page 3
M8 NoREC [15] is an oracle for logic bug detection. technique
definition 		2.4 DBMS Testing
page 3
M9 Although this approach has been effective in detecting bugs in widely-used DBMS [28], it can only be applied to a subset of SQL that can be translated. citation marker project authored
background 		2.4 DBMS Testing
page 3
M10 proposed three oracles for DBMS logic bug detection, including PQS [14], NoREC [15], and TLP [16], all of them put limitations on the SQL queries. technique
motivation 		3.2 Multi-Plan Execution
page 4
M11 In addition, both TQS and NoREC attempt to explore different query plans for queries. technique
definition 		3.2 Multi-Plan Execution
page 4
M12 NoREC only compares two distinct plans by transforming queries into semantically similar ones. technique
definition 		3.2 Multi-Plan Execution
page 4
M13 Why Existing Works Cannot Detect the Bug NoREC is one of the most effective DBMS logic bug detection oracles which requires the SELECT statements to satisfy some predefined rules, e. technique
motivation 		3.2 Multi-Plan Execution
page 5
M14 in previous works [13–17], since other statements typically lack returning results to be checked. citation marker 4.5 Multi-Plan Execution
page 9
M15 We use NoREC for SQLancer and SQLRight. name 5.2 Generating Valid Queries
page 11
M16 Since SQLancer does not implement NoREC for MySQL, we use TLP instead. name 5.2 Generating Valid Queries
page 11
M17 Comparisons with Existing Tools We compare Kangaroo with three state-of-the-art and open source systems: Squirrel, SQLancer, and SQLRight. name 5.3 Comparisons with Existing Tools
page 11
M18 SQLancer is a generate-based tool that does not require any initial inputs. name 5.3 Comparisons with Existing Tools
page 11
M19 Squirrel and SQLancer detected five and one memory bugs, respectively. name 5.3 Comparisons with Existing Tools
page 11
M20 SQLancer, the only generationbased tool, found the least bugs across all comparisons, demonstrating the advantage of the mutation-based method to detect DBMS bugs. name 5.3 Comparisons with Existing Tools
page 11
M21 It explores 52%, 44%, and 14% more edges than SQLancer, Squirrel, and SQLRight on average, respectively. name 5.3 Comparisons with Existing Tools
page 11
M22 As shown in Figure 6def, SQLancer achieves the highest query validity. name 5.3 Comparisons with Existing Tools
page 11
M23 This result is reasonable because SQLancer follows very limited grammar rules to generate SQL statements. name 5.3 Comparisons with Existing Tools
page 11
M24 For example, SQLancer does not support generating subqueries that are prone to semantic errors. name 5.3 Comparisons with Existing Tools
page 11
M25 Oracle Comparsion To eliminate the benefit from context-sensitive instantiation, We build Kangaroo MPE by replacing the context-sensitive instantiation with type-sensitive instantiation in Kangaroo, and compare Kangaroo MPE to SQLRight NoREC and SQLRight TLP. technique 5.3 Comparisons with Existing Tools
page 12
M26 Doing an automatic comparison of the MPE and PQS is difficult because PQS requires a generation method to generate statements. technique 5.3 Comparisons with Existing Tools
page 12
M27 Considering that the generation of test cases is also one of the evaluation metrics of the oracle, we include SQLancer PQS as the comparison target. name 5.3 Comparisons with Existing Tools
page 12
M28 After 5 rounds of 24-hour testing, Kangaroo MPE finds three logic bugs in SQLite and two logic bugs in MySQL, and SQLRight NoREC finds one logic bug in SQLite and one in MySQL. technique 5.3 Comparisons with Existing Tools
page 12
M29 Besides, all logic bugs found by NoREC are covered by MPE. technique 5.3 Comparisons with Existing Tools
page 12
M30 SQLRight TLP and SQLancer PQS fail to detect any logic bug. name 5.3 Comparisons with Existing Tools
page 12
M31 We use Squirrel as a baseline since it performs better than SQLancer in detecting memory bugs. name 5.4 Benefits of the Two Key Techniques
page 12
M32 NoREC [15] generates equivalent queries by shifting the conditions in the WHERE clause to the SELECT expression. technique 7 Related Work
page 13
M33 TLP [16] partitions a query lacking where clause into three subqueries whose where clause are x IS TRUE ,x IS FALSE, and x IS NULL. technique 7 Related Work
page 13
M34 To simplify the ground truth generation, Pivoted Query Synthesis(PQS) [14] only partly validates a query’s result. technique 7 Related Work
page 13
M35 By checking whether this row is fetched, PQS can detect logic bugs in the DBMS. technique 7 Related Work
page 13
M36 The generation-based approach [7, 14–17, 27, 37, 38] is effective in generating syntax-correct test cases since it typically follows a grammar model that describes the format of the input. citation marker 7 Related Work
page 13

This page is rendered from _data/papers/paper_arxiv_2312_04941.json, extracted from arxiv. 15 pages, 46 references parsed.