Logo
ScienceStack
Table of Contents
Fetching ...
Sign In

Constant Optimization Driven Database System Testing

Chi Zhang, Manuel Rigger

TL;DR

CODDTest introduces a metamorphic, black-box testing framework for DBMSs that leverages constant folding and constant propagation to generate folded queries from original ones. By comparing the results of original and folded queries under the same database state, it detects logic bugs that other approaches may miss, including those involving subqueries, JOIN ON clauses, and language features beyond predicates. Evaluated on five mature DBMSs via integration with SQLancer, CODDTest uncovered 45 previously unknown bugs, of which 24 were logic bugs, with 11 logic bugs uniquely detectable by CODDTest. The study demonstrates substantial coverage of complex SQL constructs and yields practical evidence that CODDTest complements existing test oracles, offering a scalable and effective addition to DBMS bug-detection toolkits.

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.

Constant Optimization Driven Database System Testing

TL;DR

CODDTest introduces a metamorphic, black-box testing framework for DBMSs that leverages constant folding and constant propagation to generate folded queries from original ones. By comparing the results of original and folded queries under the same database state, it detects logic bugs that other approaches may miss, including those involving subqueries, JOIN ON clauses, and language features beyond predicates. Evaluated on five mature DBMSs via integration with SQLancer, CODDTest uncovered 45 previously unknown bugs, of which 24 were logic bugs, with 11 logic bugs uniquely detectable by CODDTest. The study demonstrates substantial coverage of complex SQL constructs and yields practical evidence that CODDTest complements existing test oracles, offering a scalable and effective addition to DBMS bug-detection toolkits.

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.
Paper Structure (60 sections, 2 equations, 3 figures, 3 tables, 1 algorithm)

This paper contains 60 sections, 2 equations, 3 figures, 3 tables, 1 algorithm.

Table of Contents

  1. Introduction
  2. Background
  3. Predicates
  4. Subqueries.
  5. SQL expression.
  6. Metamorphic testing.
  7. Approach
  8. Metamorphic relation.
  9. Approach overview.
  10. Algorithmic sketch.
  11. Folding Independent Expressions
  12. Folding Dependent Expressions
  13. Constant folding.
  14. Constant propagation.
  15. Construction of the Original Query
  16. ...and 45 more sections

Figures (3)

  • Figure 1: Overview of approach. CODDTest generates pairs of equivalent queries by applying constant folding and propagation to the expression $\phi$. The application of constant propagation and folding differs for independent and dependent expressions.
  • Figure 2: The impact of expression complexity on query execution time and test throughput.
  • Figure 3: The impact of expression complexity on unique query plans.