Logo
ScienceStack
Table of Contents
Fetching ...
Sign In

Testing Database Engines via Query Plan Guidance

Jinsheng Ba, Manuel Rigger

TL;DR

This work tackles the challenge of finding logic bugs in DBMSs by leveraging query plan diversity. It introduces Query Plan Guidance (QPG), a black-box testing approach that mutates database state to encourage generation of new and more complex query plans, using metamorphic oracles (NoREC and TLP) to validate results. Through extensive evaluation on SQLite, TiDB, and CockroachDB, QPG discovers 53 previously unknown bugs (35 fixed), and substantially increases coverage of unique query plans compared to naive generation and code-coverage guidance. The results demonstrate that focusing on query plan diversity is an effective, generalizable strategy for DBMS testing, with potential applications beyond bug finding such as assessing test-suite quality.

Abstract

Database systems are widely used to store and query data. Test oracles have been proposed to find logic bugs in such systems, that is, bugs that cause the database system to compute an incorrect result. To realize a fully automated testing approach, such test oracles are paired with a test case generation technique; a test case refers to a database state and a query on which the test oracle can be applied. In this work, we propose the concept of Query Plan Guidance (QPG) for guiding automated testing towards "interesting" test cases. SQL and other query languages are declarative. Thus, to execute a query, the database system translates every operator in the source language to one of potentially many so-called physical operators that can be executed; the tree of physical operators is referred to as the query plan. Our intuition is that by steering testing towards exploring diverse query plans, we also explore more interesting behaviors-some of which are potentially incorrect. To this end, we propose a mutation technique that gradually applies promising mutations to the database state, causing the DBMS to create diverse query plans for subsequent queries. We applied our method to three mature, widely-used, and extensively-tested database systems-SQLite, TiDB, and CockroachDB-and found 53 unique, previously unknown bugs. Our method exercises 4.85-408.48X more unique query plans than a naive random generation method and 7.46X more than a code coverage guidance method. Since most database systems-including commercial ones-expose query plans to the user, we consider QPG a generally applicable, black-box approach and believe that the core idea could also be applied in other contexts (e.g., to measure the quality of a test suite).

Testing Database Engines via Query Plan Guidance

TL;DR

This work tackles the challenge of finding logic bugs in DBMSs by leveraging query plan diversity. It introduces Query Plan Guidance (QPG), a black-box testing approach that mutates database state to encourage generation of new and more complex query plans, using metamorphic oracles (NoREC and TLP) to validate results. Through extensive evaluation on SQLite, TiDB, and CockroachDB, QPG discovers 53 previously unknown bugs (35 fixed), and substantially increases coverage of unique query plans compared to naive generation and code-coverage guidance. The results demonstrate that focusing on query plan diversity is an effective, generalizable strategy for DBMS testing, with potential applications beyond bug finding such as assessing test-suite quality.

Abstract

Database systems are widely used to store and query data. Test oracles have been proposed to find logic bugs in such systems, that is, bugs that cause the database system to compute an incorrect result. To realize a fully automated testing approach, such test oracles are paired with a test case generation technique; a test case refers to a database state and a query on which the test oracle can be applied. In this work, we propose the concept of Query Plan Guidance (QPG) for guiding automated testing towards "interesting" test cases. SQL and other query languages are declarative. Thus, to execute a query, the database system translates every operator in the source language to one of potentially many so-called physical operators that can be executed; the tree of physical operators is referred to as the query plan. Our intuition is that by steering testing towards exploring diverse query plans, we also explore more interesting behaviors-some of which are potentially incorrect. To this end, we propose a mutation technique that gradually applies promising mutations to the database state, causing the DBMS to create diverse query plans for subsequent queries. We applied our method to three mature, widely-used, and extensively-tested database systems-SQLite, TiDB, and CockroachDB-and found 53 unique, previously unknown bugs. Our method exercises 4.85-408.48X more unique query plans than a naive random generation method and 7.46X more than a code coverage guidance method. Since most database systems-including commercial ones-expose query plans to the user, we consider QPG a generally applicable, black-box approach and believe that the core idea could also be applied in other contexts (e.g., to measure the quality of a test suite).
Paper Structure (13 sections, 2 equations, 7 figures, 7 tables)

This paper contains 13 sections, 2 equations, 7 figures, 7 tables.

Table of Contents

  1. Introduction
  2. Background
  3. Query Plan Study
  4. Approach
  5. Database States. (①)
  6. Query Generation and Validation. (②)
  7. Query Plan Collection. (③)
  8. Database State Mutation. (④)
  9. Implementation
  10. Evaluation
  11. Related Work
  12. Conclusion
  13. Data Availability

Figures (7)

  • Figure 1: Overview of QPG. The dashed lines refer to the data affected by ④ in the next iteration.
  • Figure 2: The workflow of measuring the known gain at ④.
  • Figure 3: The average number of unique query plans across 10 runs in 24 hours. We could run TiDB and CockroachDB only for 6 hours due to crashes.
  • Figure 4: The average number of covered unique query plans to evaluate the contributions of algorithm components across 10 runs in 24 hours.
  • Figure 5: The average number of covered unique query plans by the NoREC oracle across 10 runs in 24 hours. The y axis uses a log scale.
  • ...and 2 more figures