Detecting Logic Bugs of Join Optimizations in DBMS

TL;DR

This paper introduces TQS, a novel testing framework that effectively detects logic bugs in join optimization of DBMSs by generating multi-table queries with ground-truth verification, significantly improving debugging capabilities.

Contribution

The paper presents TQS, a new framework with data-guided schema and knowledge-guided query exploration for detecting join-related logic bugs in multi-table queries.

Findings

Detected 115 bugs across four DBMSs within 24 hours.

Effectively generates ground-truth query-result pairs for multi-table joins.

Successfully identified numerous logic bugs in industry-grade database systems.

Abstract

Generation-based testing techniques have shown their effectiveness in detecting logic bugs of DBMS, which are often caused by improper implementation of query optimizers. Nonetheless, existing generation-based debug tools are limited to single-table queries and there is a substantial research gap regarding multi-table queries with join operators. In this paper, we propose TQS, a novel testing framework targeted at detecting logic bugs derived by queries involving multi-table joins. Given a target DBMS, TQS achieves the goal with two key components: Data-guided Schema and Query Generation (DSG) and Knowledge-guided Query Space Exploration (KQE). DSG addresses the key challenge of multi-table query debugging: how to generate ground-truth (query, result) pairs for verification. It adopts the database normalization technique to generate a testing schema and maintains a bitmap index for result tracking. To improve debug efficiency, DSG also artificially inserts some noises into the generated data. To avoid repetitive query space search, KQE forms the problem as isomorphic graph set discovery and combines the graph embedding and weighted random walk for query generation. We evaluated TQS on four popular DBMSs: MySQL, MariaDB, TiDB and the gray release of an industry-leading cloud-native database, anonymized as X-DB. Experimental results show that TQS is effective in finding logic bugs of join optimization in database management systems. It successfully detected 115 bugs within 24 hours, including 31 bugs in MySQL, 30 in MariaDB, 31 in TiDB, and 23 in X-DB respectively.