A Comprehensive Study of Bugs in Relational DBMS

Shuang Liu, Ruifeng Wang, Yuanfeng Xie, Junjie Chen, Wei Lu, Xiao Zhang, Quanqing Xu, Chuanhui Yang, Xiaoyong Du. 2025. IEEE Transactions on Software Engineering.

Read the paper · doi:10.1109/tse.2025.3625300

What this paper does with SQLancer

SQLancer is both the study's reference generator and the base of its proof-of-concept tool. The study repeatedly measures bug-triggering SQL against what SQLancer can generate -- it does not support partition-related syntax at all, and it lacks bias toward diverse data types or high-risk constructs such as nested casts. SQLT is then built as an extension of SQLancer, adding BIT and JSON support that SQLancer under-supported, and running NoREC and TLP as its oracles. The two are compared directly over 24 hours on MySQL and SQLite, where SQLT found 6 bugs to SQLancer's 4, including one in MySQL that SQLancer did not reach. An empirical study of 777 general bugs across widely used relational DBMSs reported between 2021 and 2023, larger and broader than the prior studies it tabulates, which covered concurrency and transaction bugs only. It characterises what triggers these bugs and finds a recurring gap: much of the bug-triggering SQL uses syntax that existing generators do not produce at all. To show the gap is actionable the authors build a proof-of-concept tool, SQLT. Written by claude-opus-5 from the 17 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)

M7 states SQLT is an extension of SQLancer, M8 that it extends SQLancer with data types SQLancer under-supported, and M10 that NoREC and TLP were incorporated as oracles within SQLT. The tool is built on the codebase.

Design and Motivation of SQLTSQLT is an extension of the widely-used database testing tool, SQLancer [12]. M7 · V APOOF-OF- CONCEPT TOOLSQLT · page 13

SQLT extends SQLancer by introducing new data types such as BIT and JSON, which were undersupported by SQLancer. M8 · V APOOF-OF- CONCEPT TOOLSQLT · page 13

Given that bugs related to SQL types often result in result inconsistencies—silent bugs without explicit error messages—we incorporated two existing metamorphic testing approaches, NOREC [14] and TLP [19], as oracles within SQLT. M10 · V APOOF-OF- CONCEPT TOOLSQLT · page 13

extends technique — no

What SQLT extends is the tool's type and syntax coverage, not an oracle: M10 records NoREC and TLP being incorporated as they are, to serve as SQLT's oracles.

compares with — yes

M11 states SQLT was compared with SQLancer on MySQL and SQLite over 24 hours, M13 gives 6 bugs against 4, and M14 that one MySQL bug was found only by SQLT.

We’ve also compared SQLT with SQLancer on the latest version of MySQL and SQLite (MySQL 8. M11 · B Evaluation of SQLT · page 13

SQLT detects 6 bugs and SQLancer detects 4 bugs. M13 · B Evaluation of SQLT · page 14

Among these results, SQLT uncovered 1 additional bug in MySQL that were not detected by SQLancer. M14 · B Evaluation of SQLT · page 14

describes as state of the art — no

M16 calls SQLancer one of the most popular generation-based RDBMS testing approaches. Popularity is recognition but not a claim about being the most effective or the state of the art, and the study's own findings argue the opposite about its coverage.

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
12	M. Rigger and Z. Su, “Testing database engines via pivoted query synthesis,” in Proc. 14th USENIX Symp. Operating Syst. Des. Implementation (OSDI), 2020, pp. 667–682.	sqlancer publication · PQS
14	M. Rigger and Z. Su, “Detecting optimization bugs in database engines via non-optimizing reference engine construction,” in Proc. 28th ACM Joint Meeting Eur. Softw. Eng. Conf. Symp. Found. Softw. Eng., 2020, pp. 1140–...	sqlancer publication · NOREC
19	M. Rigger and Z. Su, “Finding bugs in database systems via query partitioning,” Proc. ACM Program. Lang., vol. 4, no. OOPSLA, pp. 1– 30. 2020.	sqlancer publication · TLP
21	Z.-M. Jiang, S. Liu, M. Rigge r, and Z. Su, “Detecting transactional bugs in database engines via graphbased oracle construction,” in Proc. 17th USENIX Symp. Operating Sys t. Des. Implemen tation (OSDI), 2023, pp. 397...	project authored
26	J. Ba and M. Rigger, “CERT: Finding performance issues in database systems through the lens of cardinality estimation,” in Proc. IEEE/ACM 46th Int. Conf. Softw. Eng., 2024, pp. 1–13.	sqlancer publication · CERT

Every place it refers to SQLancer (17)

17 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	: ACOMPREHENSIVE STUDY OFBUGS INRELATIONAL DBMS 3655 TABLE ICOMPARISON WITHPRIOR EMPIRICAL STUDIES ONRDBMS BUGS Study Bug Type Duration # Bugs Fonseca [22] concurrency bugs 2003-2009 80 Cui[23] transaction bugs 2018-2022 140 ours general bugs 2021-2023 777 [12],[13],[14],[15],[16],[17],[18],[19],[20],[21] aiming at ...	citation marker background	I INTRODUCTION page 2
M2	We ﬁrst consulted the DBEngines Ranking [24] to identify ranked and widely adopted RDBMS, and surveyed existing testing approaches [11],[12], [13],[14],[15],[16],[17],[18],[19],[20],[21],[25],[26] as well as empirical studies on RDBMS bugs [23],[27].	citation marker background	B Databases to be Studied page 3
M3	, the SQL statements—focusing speciﬁcally on those that are not supported by existing test case generation tools such as SQLancer and SQLSmith.	name motivation	F Bug-Triggering SQLs page 11
M4	However, existing test generation tools such as SQLancer and SQLSmith do not suppor t partition-related syntax at all, making it fundamentally impossible for them to generate test cases involving partition logic.	name motivation	F Bug-Triggering SQLs page 11
M5	Recent works on RDBMS transaction testing [20],[21],[25] offer insights into testing efﬁcacy.	citation marker project authored background	A Implications page 12
M6	However,extending existing syntax-directed tools like SQLancer to incorporate such functionality is non-trivial, as t hese featur es often require complex semantic correctness checks.	name motivation	A Implications page 12
M7	Design and Motivation of SQLTSQLT is an extension of the widely-used database testing tool, SQLancer [12].	name reuse implementation	V APOOF-OF- CONCEPT TOOLSQLT page 13
M8	SQLT extends SQLancer by introducing new data types such as BIT and JSON, which were undersupported by SQLancer.	name reuse implementation	V APOOF-OF- CONCEPT TOOLSQLT page 13
M9	0 Duplicate TABLE IXCOMPARISON OFSQLT WITH SQLANCER FOR 24H RDBMS SQLT SQLancer MySQL 4 3 SQLite 1 1 database-speciﬁc functions that are prone to type conversion issues, such as STRING_AGG ,UNIXEPOCH, and TIMEDIFF in MySQL/openGauss, and RANDOM ,IIF,OCTET_LENGTH, and UNHEX in SQLite.	name result comparison	V APOOF-OF- CONCEPT TOOLSQLT page 13
M10	Given that bugs related to SQL types often result in result inconsistencies—silent bugs without explicit error messages—we incorporated two existing metamorphic testing approaches, NOREC [14] and TLP [19], as oracles within SQLT.	technique reuse component	V APOOF-OF- CONCEPT TOOLSQLT page 13
M11	We’ve also compared SQLT with SQLancer on the latest version of MySQL and SQLite (MySQL 8.	name baseline	B Evaluation of SQLT page 13
M12	0) 3for 24 hours and adopt NoREC [14] as the default 3We did not run openGauss as SQLancer does not support openGauss.	name baseline	B Evaluation of SQLT page 13
M13	SQLT detects 6 bugs and SQLancer detects 4 bugs.	name result comparison	B Evaluation of SQLT page 14
M14	Among these results, SQLT uncovered 1 additional bug in MySQL that were not detected by SQLancer.	name result comparison	B Evaluation of SQLT page 14
M15	Notably, SQLancer does not actively avoid type mismatches; rather, itstest generation lacks sufﬁcient bias toward diverse data types and fails to synthesize high-risk constructs like nested casts or ambiguous comparisons.	name result comparison	B Evaluation of SQLT page 14
M16	[12],[14], [19] proposed SQLancer, which is one of the most popular generation-based RDBMS testing approach, and it incorporates three metamorphic methods, namely PQS [12], NoREC [14], and TLP [19], as oracles to detect logical bugs.	name definition	VI RELATED WORK page 14
M17	Troc [20] and TxCheck [21] are metamorphic testing approaches focusing on detect transactional bugs in RDBMSs.	citation marker project authored background	VI RELATED WORK page 14

This page is rendered from _data/papers/paper_doi_10_1109_tse_2025_3625300.json, extracted from supplied pdf. 15 pages, 42 references parsed.