Testing Database Systems via Differential Query Execution

Jiansen Song, Wensheng Dou, Ziyu Cui, Qianwang Dai, Wei Wang, Jun Wei, Hua Zhong, Tao Huang. 2023. International Conference on Software Engineering.

Read the paper · doi:10.1109/icse48619.2023.00175

What this paper does with SQLancer

DQE is built on SQLancer. The paper states that its implementation is based on SQLancer and that its database generation is mainly adopted from it, and it notes that DQE, PQS, NoREC and TLP are all built on SQLancer and so share similar query generation -- offered as the explanation for their comparable code coverage. PQS, NoREC and TLP are also its baselines: it reports coverage and bug counts against all three, and its motivation is that none of them can be applied to UPDATE and DELETE statements, which is the gap DQE fills. DQE tests DBMSs by checking that the rows a SELECT identifies are the same rows an UPDATE or DELETE with the same predicate acts on. The gap it addresses is that existing logic-bug oracles only examine SELECT: a predicate evaluated correctly in a query can still be evaluated wrongly when it drives a data-modifying statement, and no oracle looked there. Applied to MySQL, MariaDB and TiDB it found 32 bugs, 30 confirmed and 21 fixed. Written by claude-opus-5 from the 21 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 — yes (implementation)

M8 states DQE is implemented based on SQLancer, M7 that its database generation is mainly adopted from it, and M16 that DQE is built on SQLancer alongside PQS, NoREC and TLP. This is the codebase, not a workload.

Our database generation is mainly adopted from SQLancer [27]. M7 · B Database Generation · page 4

EVALUATION We implement DQE based on SQLancer [27], which is implemented in Java. M8 · IV EVALUATION · page 7

This is reasonable, since DQE, PQS, NoREC and TLP are all built on SQLancer, which share the similar query generation. M16 · D Other Experimental Statistics · page 9

extends technique — no

DQE is a new oracle rather than an extension of one. M5 and M6 give the relationship: PQS, NoREC and TLP cannot be adopted on UPDATE and DELETE queries, and DQE addresses that by comparing a SELECT against a data-modifying statement -- a different oracle, not a generalisation of theirs.

compares with — yes

M9 names PQS, NoREC and TLP as the existing approaches DQE is compared with, and M10 and M12 report the outcome in bugs found and line coverage.

Pivoted Query Synthesis (PQS)Non-optimizing Reference Engine Construction (NoREC)Ternary Logic Partitioning (TLP)

, PQS [9], NoREC [10] and TLP [11]) that aim to detect logic bugs in DBMSs. M9 · C Comparing with Existing Approaches · page 8

Thus, they cannot detect 18 logic bugs related to this kind of errors TABLE VCOVERAGE INFORMATION Tool MySQL MariaDB PQS 19 NoREC-18 TLP 18 DQE 15 21 in SELECT queries. M10 · C Comparing with Existing Approaches · page 8

, PQS [9], NoREC [10] and TLP [11]. M12 · D Other Experimental Statistics · page 8

describes as state of the art — no

The techniques are described by what they do and by their limits, not as the state of the art.

Its artifact

JensonSung/dqetool carries renamed sqlancer package, sqlancer source content match.

Repository names this paper.
Replication package for "Testing Database Systems via Differential Query Execution", accepted at ICSE 2023 # DQETool DQETool is the implementation of differential query execution in paper. # Getting Started Requirements: * Java 11 or above
source

src/dqetool/Randomly.java is SQLancer's Randomly.java, with the package renamed to dqetool (8 of 8 identifiers match: StringGenerationStrategy, SOPHISTICATED, cachedLongs, cachedStrings, cachedDoubles).
private static StringGenerationStrategy stringGenerationStrategy = StringGenerationStrategy.SOPHISTICATED;
source

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
9	M. Rigger and Z. Su, “Testing database engines via pivoted query synthesis,” in Proceedings of USENIX Symposium on Operating Systems Design and Implementation (OSDI), 2020, pp. 667–682.	sqlancer publication · PQS
10	——, “Detecting optimization bugs in database engines via nonoptimizing reference engine construction,” in Proceedings of ACM Joint European Software Engineering Conference and Symposium on the F oundations of Software...	sqlancer publication · NOREC
11	——, “Finding bugs in database systems via query partitioning,” in Proceedings of ACMSIGPLAN Conference on Object-Oriented Programming Systems, Languages, and Applications (OOPSLA), vol. 4, 2020.	sqlancer publication · TLP
27	“SQLancer homepage,” https://github.com/sqlancer/sqlancer, 2022.	sqlancer publication
50	J. Ba and M. Rigger, “Testing database engines via query plan guidance,” inProceedings of International Conference on Software Engineering (ICSE), 2023.	sqlancer publication · QPG
53	M. Kamm, M. Rigger, C. Zhang, and Z. Su, “Testing graph database engines via query partitioning,” in Proceedings of ACMSIGSOFT International Symposium on Software Testing and Analysis (ISSTA), 2023.	project authored

Every place it refers to SQLancer (21)

21 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	Recently, researchers have proposed some approaches to detect logic bugs in DBMSs [8]–[11].	citation marker background	I INTRODUCTION page 1
M2	PQS [9] generates SELECT queries that fetch Wensheng Dou and Hua Zhong are the corresponding authors.	technique definition	I INTRODUCTION page 1
M3	NoREC [10] rewrites a SELECT query as another equivalent one that cannot be optimized by the DBMS, and then detects difference in their query results.	technique definition	I INTRODUCTION page 1
M4	TLP [11] decomposes a SELECT query into three partitioning queries, and merges these partitioning queries’ results into acombined result, which is expected to be the same as theoriginal query’s result.	technique definition	I INTRODUCTION page 1
M5	, PQS [9], NoREC [10] and TLP [11], cannot be adopted on UPDATE and DELETE queries.	technique motivation	I INTRODUCTION page 1
M6	, PQS [9], NoREC [10] and TLP [11].	technique motivation	I INTRODUCTION page 2
M7	Our database generation is mainly adopted from SQLancer [27].	name reuse component	B Database Generation page 4
M8	EVALUATION We implement DQE based on SQLancer [27], which is implemented in Java.	name reuse implementation	IV EVALUATION page 7
M9	, PQS [9], NoREC [10] and TLP [11]) that aim to detect logic bugs in DBMSs.	technique baseline	C Comparing with Existing Approaches page 8
M10	Thus, they cannot detect 18 logic bugs related to this kind of errors TABLE VCOVERAGE INFORMATION Tool MySQL MariaDB PQS 19 NoREC-18 TLP 18 DQE 15 21 in SELECT queries.	technique result comparison	C Comparing with Existing Approaches page 8
M11	Existing works [9]–[11] also face the same problem.	citation marker background	D Other Experimental Statistics page 8
M12	, PQS [9], NoREC [10] and TLP [11].	technique result comparison	D Other Experimental Statistics page 8
M13	PQS achieves 19% line coverage in MySQL.	technique result comparison	D Other Experimental Statistics page 9
M14	NoREC achieves 18% line coverage in MariaDB.	technique result comparison	D Other Experimental Statistics page 9
M15	TLP achieves 18% line coverage in MySQL.	technique result comparison	D Other Experimental Statistics page 9
M16	This is reasonable, since DQE, PQS, NoREC and TLP are all built on SQLancer, which share the similar query generation.	name reuse implementation	D Other Experimental Statistics page 9
M17	Note that, in SQLancer, NoREC does not support testing MySQL, PQS and TLP do not support testing MariaDB.	name incidental	D Other Experimental Statistics page 9
M18	Database and SQL query generation have been widely explored by existing works [21]–[26], [29], [50]–[57].	citation marker background	VI RELATED WORK page 11
M19	PQS [9] synthesizes a SELECT query, which is computed to fetch a randomly-selected pivot row, and checks whether the pivot row is contained in its query result.	technique definition	VI RELATED WORK page 11
M20	NoREC [10] rewrites a SELECT query as an equivalent one that the DBMS cannot optimize, and compares their results.	technique definition	VI RELATED WORK page 11
M21	TLP [11] leverages the ternary property of predicate evaluation, where the evaluation result is one of TRUE ,FALSE and NULL, to partition a SELECT query into three partitioning queries, whose combined query results are equal to the original query’s query result.	technique definition	VI RELATED WORK page 11

This page is rendered from _data/papers/paper_doi_10_1109_icse48619_2023_00175.json, extracted from supplied pdf. 13 pages, 60 references parsed.