Detecting DBMS Bugs by Constructing Equivalent Representations of Intermediate Query Results

Database Management Systems (DBMSs) provide multiple SQL mechanisms for representing intermediate query results, notably VIEWs, Common Table Expressions (CTEs), and Temporary Tables (TEMPTs). When these mechanisms are used to express the same intermediate result, the corresponding queries are semantically equivalent and should return consistent results. However, the authors observe that such queries can return inconsistent results, which indicates potential logic bugs in the DBMS. Existing approaches for detecting DBMS logic bugs have never explored result consistency across these equivalent representations. This paper proposes ERIQ, a novel testing approach that detects DBMS logic bugs by checking result consistency across Equivalent Representations of Intermediate Query Results. The core insight is that if a DBMS correctly implements the semantics of VIEW, CTE, and TEMPT, then any query that uses one of these mechanisms to represent a given intermediate result should produce the same final output as a query that uses another mechanism for the same intermediate result. Any discrepancy reveals a logic bug in the DBMS. The paper evaluates ERIQ on four widely used open-source DBMSs: MySQL, MariaDB, Percona, and OceanBase. In total, ERIQ detected 64 bugs, 63 of which were confirmed by developers, and two have been fixed. Among the confirmed bugs, 54 were unique and previously unknown logic bugs, and one was a documentation issue. This work introduces a new dimension for DBMS testing and demonstrates its effectiveness in practice.

ERIQ operates by constructing SQL variants that represent the same intermediate query result using a VIEW, a CTE, or a TEMPT. The approach consists of three main steps: (1) generating a base query that computes an intermediate result, (2) rewriting the query to use each of the three mechanisms to represent that intermediate result, and (3) executing the variants and comparing their returned results. If the results differ, a potential logic bug is reported. The process can be formalized as follows. Let $Q$ be a query that computes an intermediate result $R$. ERIQ generates three equivalent queries: $Q_{\text{VIEW}}$, $Q_{\text{CTE}}$, and $Q_{\text{TEMPT}}$, each representing $R$ using the respective mechanism. The expected behavior is that for any database instance $D$, the result sets satisfy: