Understanding and Detecting SQL Function Bugs: Using Simple Boundary Arguments to Trigger Hundreds of DBMS Bugs
Read the paper · doi:10.1145/3689031.3696064
What this paper does with SQLancer
SQLancer is one of three baselines, described as a state-of-the-art DBMS testing tool widely used in industry, and run in PQS mode with default settings. The result is stark and reported twice: over 24 hours SQLancer found no SQL function bugs at all, against Soft's 2,956, and Soft covered many times more branches in the function components. The paper's explanation is concrete rather than dismissive -- supporting a new function in SQLancer means writing a function model in Java, and it only generates random values for function arguments, so the boundary values these bugs need are not something it produces.
Soft targets built-in SQL functions, testing them with boundary arguments -- extreme, empty and edge-case values -- rather than random ones. The insight is that function implementations fail at their argument boundaries, and that generators producing random values almost never land there. Across PostgreSQL, MySQL, MariaDB, ClickHouse and MonetDB it found hundreds of SQL function bugs and covered far more of the function code.
How it was classified
uses infrastructure — no
SQLancer is run as a baseline in PQS mode; Soft's boundary-argument generation is its own.
extends technique — no
No SQLancer oracle is extended. Soft changes which argument values are generated, and M7 gives that as precisely the difference from SQLancer's random values.
compares with — yes
M3 states Soft was compared against SQLancer among three tools, M2 records it being run in PQS mode with default configurations, and M5 and M9 report the bug counts and that SQLancer found no SQL function bugs in 24 hours.
Pivoted Query Synthesis (PQS)
We also used the latest versions of Sqirrel [63], SQLsmith [ 53], and SQLancer in PQS mode [ 51] with their default configurations to test these DBMSs, but they did not find any SQL function bugs.
5 Comparison with Other Testing Works To demonstrate the effectiveness of our methods, we compared Soft against three state-of-the-art DBMS testing tools, namely Sqirrel, SQLancer, and SQLsmith, which are widely used in the industry.
DBMS Sqirrel SQLancer SQLsmith Soft PostgreSQL 29 123 417 456 MySQL 23 35 – 323 MariaDB 22 20 – 279 ClickHouse – 24 – 711 MonetDB – – 29 171 Total 74 202 446 2,956 Increment* 984 1,567 181 – *Increments are calculated only for commonly supported DBMSs.
Sqirrel, SQLancer, and SQLsmith did not find any SQL function bugs in 24 hours.
describes as state of the art — yes
M3 calls SQLancer one of three state-of-the-art DBMS testing tools, widely used in the industry.
5 Comparison with Other Testing Works To demonstrate the effectiveness of our methods, we compared Soft against three state-of-the-art DBMS testing tools, namely Sqirrel, SQLancer, and SQLsmith, which are widely used in the industry.
SQLancer publications it cites (3)
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 |
|---|---|---|
| 49 | Manuel Rigger and Zhendong Su. 2020. Detecting optimization bugs in database engines via non-optimizing reference engine construction. InProceedings of the 28th ACM Joint Meeting on European Software Engineering Confe... | sqlancer publication · NOREC |
| 50 | 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 |
| 51 | 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 (15)
15 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 | 86% more code branches of the SQL function components of DBMSs in 24 hours than Sqirrel, SQLancer, and SQLsmith, respectively. |
name |
1 Introduction page 2 |
| M2 | We also used the latest versions of Sqirrel [63], SQLsmith [ 53], and SQLancer in PQS mode [ 51] with their default configurations to test these DBMSs, but they did not find any SQL function bugs. |
name |
7.3 Detected DBMS Vulnerabilities page 10 |
| M3 | 5 Comparison with Other Testing Works To demonstrate the effectiveness of our methods, we compared Soft against three state-of-the-art DBMS testing tools, namely Sqirrel, SQLancer, and SQLsmith, which are widely used in the industry. |
name |
7.5 Comparison with Other Testing Works page 12 |
| M4 | Among the DBMSs we tested, Sqirrel supports PostgreSQL, MySQL, and MariaDB; SQLsmith supports PostgreSQL and MonetDB; while SQLancer supports PostgreSQL, MySQL, MariaDB, and ClickHouse. |
name |
7.5 Comparison with Other Testing Works page 12 |
| M5 | DBMS Sqirrel SQLancer SQLsmith Soft PostgreSQL 29 123 417 456 MySQL 23 35 – 323 MariaDB 22 20 – 279 ClickHouse – 24 – 711 MonetDB – – 29 171 Total 74 202 446 2,956 Increment* 984 1,567 181 – *Increments are calculated only for commonly supported DBMSs. |
name |
7.5 Comparison with Other Testing Works page 12 |
| M6 | 86% more branches in built-in SQL function components than Sqirrel, SQLancer, and SQLsmith, respectively. |
name |
7.5 Comparison with Other Testing Works page 12 |
| M7 | For example, SQLancer requires writing function models in Java code to support the generation of a new function, and it only supports generating random values for SQL function arguments. |
name |
7.5 Comparison with Other Testing Works page 12 |
| M8 | DBMS Sqirrel SQLancer SQLsmith Soft PostgreSQL 2,106 6,106 11,768 13,334 MySQL 1,105 1,927 – 6,914 MariaDB 1,758 1,732 – 6,283 ClickHouse – 26,655 – 45,836 MonetDB – – 551 1,431 Total 4,969 36,420 12,319 73,798 Increment* 21,562 35,947 2,446 – *Increments are calculated only for commonly supported DBMSs. |
name |
7.5 Comparison with Other Testing Works page 13 |
| M9 | Sqirrel, SQLancer, and SQLsmith did not find any SQL function bugs in 24 hours. |
name |
7.5 Comparison with Other Testing Works page 13 |
| M10 | Unlike Sqirrel, SQLancer, and SQLsmith, our tool Soft specifically targets boundary values of SQL function arguments. |
name |
7.5 Comparison with Other Testing Works page 13 |
| M11 | , TLP [50]) and transformation (e. |
technique |
8 Discussion page 13 |
| M12 | , NoREC [49]). |
technique |
8 Discussion page 13 |
| M13 | DBMS correctness testing [ 15,49–51,54] aims to verify that the DBMS accurately executes queries. |
citation marker |
9 Related Work page 13 |
| M14 | For example, PQS [ 51] detects whether the pivot row exists in the preset query results. |
technique |
9 Related Work page 13 |
| M15 | NoREC [ 49] detects inconsistencies between query results before and after optimization. |
technique |
9 Related Work page 13 |