Constant Optimization Driven Database System Testing

TL;DR

This paper introduces CODDTest, a novel database testing approach inspired by compiler optimizations, which effectively detects previously unknown logic bugs in mature DBMSs by applying constant folding techniques.

Contribution

The paper presents CODDTest, a new testing method leveraging constant folding to identify logic bugs in DBMSs, revealing 45 bugs including 24 logic bugs, with some only detectable by this approach.

Findings

Detected 45 bugs in five mature DBMSs.

Identified 24 unique logic bugs, 11 of which are only found by CODDTest.

Proved that CODDTest is effective and easy to implement.

Abstract

Logic bugs are bugs that can cause database management systems (DBMSs) to silently produce incorrect results for given queries. Such bugs are severe, because they can easily be overlooked by both developers and users, and can cause applications that rely on the DBMSs to malfunction. In this work, we propose Constant-Optimization-Driven Database Testing (CODDTest) as a novel approach for detecting logic bugs in DBMSs. This method draws inspiration from two well-known optimizations in compilers: constant folding and constant propagation. Our key insight is that for a certain database state and query containing a predicate, we can apply constant folding on the predicate by replacing an expression in the predicate with a constant, anticipating that the results of this predicate remain unchanged; any discrepancy indicates a bug in the DBMS. We evaluated CODDTest on five mature and extensively-tested DBMSs-SQLite, MySQL, CockroachDB, DuckDB, and TiDB-and found 45 unique, previously unknown bugs in them. Out of these, 24 are unique logic bugs. Our manual analysis of the state-of-the-art approaches indicates that 11 logic bugs are detectable only by CODDTest. We believe that CODDTest is easy to implement, and can be widely adopted in practice.