Detecting DBMS Bugs by Constructing Equivalent Representations of Intermediate Query Results

arXiv:2608.30385v1 [cs.DB] 31 Aug 2026

Detecting DBMS Bugs by Constructing Equivalent Representations of Intermediate Query Results Xiaoxu Niu∗

Gong Chen∗

Wuhan University School of Computer Science Wuhan, China xiaoxuniu@whu.edu.cn

Wuhan University School of Computer Science Wuhan, China chengongcg@whu.edu.cn

Jinfu Chen

Xiaoyuan Xie†

Wuhan University School of Computer Science Wuhan, China jinfuchen@whu.edu.cn

Wuhan University School of Computer Science Wuhan, China xxie@whu.edu.cn

Abstract Database Management Systems (DBMSs) support multiple SQL mechanisms for representing intermediate query results, including VIEWs, Common Table Expressions (CTEs), and Temporary Tables (TEMPTs). When these mechanisms are used to represent the same intermediate query result, the corresponding queries are expected to produce consistent results. However, we observe that such queries can return inconsistent results, indicating potential DBMS logic bugs. Existing approaches for detecting DBMS logic bugs have never explored result consistency across such equivalent representations. In this paper, we propose ERIQ, a novel testing approach for detecting DBMS logic bugs from the perspective of checking result consistency across Equivalent Representations of Intermediate Query Results. ERIQ constructs SQL variants using a VIEW, a CTE, or a TEMPT to represent the same intermediate query result, executes these variants, and compares their returned results. We evaluated 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.

1

Introduction

Database Management Systems (DBMSs) provide various SQL mechanisms for representing intermediate query results, including VIEWs, Common Table Expressions (CTEs), and Temporary Tables (TEMPTs) [9, 19, 20, 25]. These mechanisms are widely used in database applications to simplify the construction of complex queries and improve query readability: VIEWs define reusable named queries, CTEs provide temporary result sets that can be referenced within a single statement, and TEMPTs store query results in temporary tables that can be reused by subsequent statements [19, 20, 25]. When different mechanisms are used to represent the same intermediate query result, the corresponding queries are expected to produce consistent results. However, we observe that such queries can return inconsistent results, as illustrated in Section 2.2. Such inconsistencies can indicate potential DBMS bugs, which may be ∗ These authors have contributed equally to this work. † Corresponding author.

exposed when the corresponding queries are processed through different paths within the DBMS. Many approaches have been proposed for detecting DBMS logic bugs. However, to the best of our knowledge, they have never explored the perspective of detecting such bugs by checking result consistency across equivalent representations of intermediate query results. For example, NoREC [29] compares the results of optimized and unoptimized queries; TLP [30] partitions queries based on ternary logic; PQS [31] checks whether a selected pivot row appears in the result of a generated query; Pinolo [8] checks result containment between synthesized queries and a seed query; EET [11] and CODDTest [42] apply equivalent transformations at the expression level; Radar [34] compares query results across databases with the same data but different metadata; and EDC [4] checks query consistency using precomputed results of data operation expressions. In this paper, we propose ERIQ, a novel testing approach for detecting DBMS logic bugs from the perspective of checking result consistency across Equivalent Representations of Intermediate Query Results. ERIQ constructs corresponding SQL variants that use a VIEW, a CTE, or a TEMPT to represent the same intermediate query result, executes these variants, and compares their returned results. If the results are inconsistent, ERIQ reports the inconsistency as a bug. We implemented ERIQ and evaluated it on four widely used open-source DBMSs: MySQL, MariaDB, Percona, and OceanBase. In total, ERIQ detected 64 bugs, including 29 in MySQL, 18 in MariaDB, 13 in Percona, and 4 in OceanBase. Developers confirmed 63 of them, including 54 unique and previously unknown logic bugs, 8 duplicates of other bugs reported by ERIQ, and one documentation issue. Among the 54 unique logic bugs, two have been fixed. We further compared ERIQ with four state-of-the-art DBMS logic bug detection approaches: EDC [4], Radar [34], EET [11], and TLP [30]. Among these 54 unique logic bugs, the best-performing existing approach could detect only 9 of them, showing that ERIQ detects many bugs missed by each existing approach and thus provides complementary bug detection capability. Under the 24-hour testing

Xiaoxu Niu, Gong Chen, Jinfu Chen, and Xiaoyuan Xie

budget on each supported DBMS, ERIQ detected 54 logic bugs, compared with 16 detected by the best-performing existing approach, demonstrating its stronger bug detection capability. This paper makes the following contributions: • We introduce a new perspective for detecting DBMS logic bugs by checking result consistency across equivalent representations of intermediate query results, which has never been explored by existing methods. • Based on this new perspective, we propose and implement ERIQ, a novel testing approach that constructs SQL variants using a VIEW, a CTE, or a TEMPT to represent the same intermediate query result and detects DBMS logic bugs by checking result consistency across these variants. • We conduct a comprehensive experimental study of ERIQ on four widely used open-source DBMSs, detecting 54 unique and previously unknown logic bugs and demonstrating that ERIQ complements existing approaches. The rest of this paper is organized as follows. Section 2 introduces the background and motivation. Section 3 describes the proposed approach. Section 4 presents the experimental evaluation. Section 5 discusses false positive mitigation, a documentation issue, and limitations. Section 6 reviews related work. Finally, Section 7 concludes the paper.

2

Background and Motivation

This section introduces different representations of intermediate query results and their representative processing paths, presents a real-world logic bug that motivates our work, and derives the key insight underlying our approach.

2.1

Representations of Intermediate Query Results

VIEWs, CTEs, and TEMPTs provide different ways to represent intermediate query results in SQL. Using MySQL as an example, Figure 1 illustrates their simplified representative processing paths. VIEWs define reusable named queries whose results can be referenced by other queries [20]. Processing a VIEW reference may involve resolving and expanding its definition, after which the optimizer may merge the VIEW’s defining query into the referencing query or use materialization during query execution [23]. CTEs provide temporary result sets that can be referenced within a single statement [25]. Processing a CTE may similarly involve resolving its statement-scoped name and selecting between merging and materialization during query optimization [23]. TEMPTs store query results in temporary tables that can be reused by subsequent statements [19]. For a TEMPT created using CREATE TEMPORARY TABLE ... AS SELECT, the DBMS derives the temporary-table schema from the output of the defining query, executes that query to populate the temporary table, and subsequently optimizes and executes a separate query over the temporary table [18]. Although these mechanisms differ in syntax, scope, lifetime, and materialization behavior, they can be used to represent the same intermediate query result. In this paper, equivalence refers to the semantic equivalence of the represented intermediate query result, rather than identical internal representations, optimization strategies, or execution

VIEW

WITH

CTE

TEMPT

Parse / Bind

Parse / Bind

Parse / Bind

View Reference Resolution / Expansion

CTE Name / Scope Resolution

Schema Derivation from SELECT Output

Mer ge / Mater ialization

Mer ge / Mater ialization

Execute SELECT & Populate TEMPT

Optimize & Execute Query

Optimize & Execute Query

Optimize & Execute Query

Figure 1: Simplified processing paths for VIEW, CTE, and TEMPT in MySQL. paths. When they are used in this way, the corresponding queries are expected to return consistent results.

2.2

Motivating Example

Listing 1 presents MDEV#40143, a real logic bug in MariaDB detected in our study and confirmed by the developers. The test case first creates a table t0 with an integer column and a spatial column, and inserts three rows. It then constructs three SQL variants that use a VIEW, a CTE, and a TEMPT, respectively, to represent the same intermediate query result. In all three variants, the intermediate query result is produced by the same SELECT query, which joins two instances of t0 using a RIGHT JOIN with a null-safe equality predicate (<=>), filters rows using ST_Dimension(b.c1) >= 0, and computes ST_IsValid(a.c1) as vc_0. Each variant then selects vc_0 from the corresponding representation and applies the same predicate vc_0 > 0. Listing 1: MDEV#40143. A real logic bug in MariaDB. CREATE INSERT INSERT INSERT

TABLE t0 ( c0 INT , c1 POINT ); INTO t0 VALUES ( -4 , Point (0 ,0)); INTO t0 VALUES (6 , NULL ); INTO t0 VALUES (9 , Point (1 ,1));

-- VIEW variant CREATE VIEW v0 AS SELECT ST_IsValid ( a . c1 ) AS vc_0 FROM t0 AS a RIGHT JOIN t0 AS b ON a . c0 <= > b. c0 WHERE ST_Dimension ( b . c1 ) >= 0; SELECT vc_0 FROM v0 WHERE vc_0 > 0; – Returns: 1 -- CTE variant WITH CTE AS ( SELECT ST_IsValid ( a . c1 ) AS vc_0 FROM t0 AS a RIGHT JOIN t0 AS b ON a . c0 <= > b. c0 WHERE ST_Dimension ( b . c1 ) >= 0) SELECT vc_0 FROM CTE WHERE vc_0 > 0; – Returns: NULL -- TEMPT variant CREATE TEMPORARY TABLE temp_t AS SELECT ST_IsValid ( a . c1 ) AS vc_0 FROM t0 AS a RIGHT JOIN t0 AS b ON a . c0 <= > b. c0 WHERE ST_Dimension ( b . c1 ) >= 0; SELECT vc_0 FROM temp_t WHERE vc_0 > 0; – Returns: 1, 1

According to the SQL semantics, the three variants should return the same result. However, MariaDB produces inconsistent results across the three variants: the VIEW variant returns only one row with value 1, the CTE variant returns one row with value NULL, and the TEMPT variant returns two rows with value 1. MariaDB developers confirmed that the expected result is two rows

Detecting DBMS Bugs by Constructing Equivalent Representations of Intermediate Query Results

1

Database and Quer y Gener ation Initial Database State D

2

Constr ucting Equivalent Representations of Inter mediate Quer y Results

Statement sequence

Inter mediate Quer y I

Referencing Quer y Q(x) SELECT query referencing the intermediate query result and producing the final query result

Execute SQL var iants

VIEW var iant

CREATE VIEW v AS I; Q(v); -- replace x with v

SELECT query producing an intermediate query result

Result Compar ison

Same Intermediate Query I and Referencing Query Q(x) Replace x with v, cte, or temp_t

Base table, column types, and inserted data

SELECT

3

CTE var iant WITH

VIEW Result

CTE Result

TEMPT Result

...

...

...

Single statement WITH cte AS (I) Q(cte); -- replace x with cte

Compare retur ned results

TEMPT var iant Statement sequence CREATE TEMPORARY TABLE temp_t AS I; Q(temp_t); -- replace x with temp_t

Consistent

Bug

Figure 2: Overview of ERIQ. with value 1: ST_Dimension(b.c1) >= 0 retains two joined rows, and ST_IsValid(a.c1) evaluates to 1 for both, so both rows satisfy vc_0 > 0. Therefore, the TEMPT variant returns the expected result, whereas the VIEW and CTE variants produce incorrect results. This example demonstrates that DBMS logic bugs can manifest as inconsistent results across queries constructed using equivalent representations of the same intermediate query result. MariaDB developers have fixed this bug (https://jira.mariadb.org/browse/MDEV40143), and its root cause is further analyzed in Section 4.5. Importantly, such logic bugs may silently produce incorrect query results without causing crashes or explicit errors, potentially propagating incorrect data to downstream applications.

query results provides an effective approach to detecting DBMS logic bugs.

3

In this paper, we propose ERIQ, a novel testing approach for detecting DBMS logic bugs by checking result consistency across Equivalent Representations of Intermediate Query Results. We first provide an overview of ERIQ and then describe database and query generation, the construction of equivalent representations of intermediate query results, and result comparison.

3.1 2.3

Key Insight

This observation motivates a new perspective for detecting DBMS logic bugs by checking result consistency across equivalent representations of the same intermediate query result. However, existing DBMS logic bug detection approaches have never explored this perspective. For example, existing approaches expose bugs by transforming, partitioning, or constructing queries [8, 29– 31], transforming query expressions [11, 42], varying database metadata [34], or using precomputed results of data operation expressions [4]. The rationale behind this perspective follows from the observation in Section 2.1: queries constructed using equivalent representations of the same intermediate query result may be processed through different paths within the DBMS. Consequently, an underlying DBMS logic bug can affect the corresponding queries differently, resulting in inconsistent query results, as demonstrated by the real-world bug presented in Section 2.2. Therefore, constructing such equivalent representations and comparing the corresponding

Approach

Overview of ERIQ

Figure 2 provides an overview of ERIQ, which consists of three stages. ① Database and Query Generation. ERIQ first generates an initial database state 𝐷, including a base table, its column types, and inserted data. It then generates an intermediate query 𝐼 , which is a SELECT query that produces an intermediate query result from the generated database. Based on the names and types of the columns produced by 𝐼 , ERIQ further generates a referencing query 𝑄 (𝑥), which references the intermediate query result through 𝑥 and produces the final query result, where 𝑥 is a placeholder for the name of the VIEW, CTE, or TEMPT used to represent the intermediate query result. ② Constructing Equivalent Representations of Intermediate Query Results. ERIQ constructs SQL variants that use a VIEW, a CTE, or a TEMPT to represent the result produced by the same intermediate query 𝐼 . For each representation, ERIQ replaces 𝑥 in the same referencing query 𝑄 (𝑥) with the name of the corresponding VIEW, CTE, or TEMPT. In this way, the SQL variants use equivalent representations of the same intermediate query result, while the resulting referencing queries differ only

Xiaoxu Niu, Gong Chen, Jinfu Chen, and Xiaoyuan Xie

in the relation name used to reference each representation. ③ Result Comparison. ERIQ executes the constructed SQL variants and compares their returned results. If the results are inconsistent, ERIQ reports the inconsistency as a bug. Algorithm 1 summarizes the workflow of ERIQ. Within a given time budget, ERIQ repeatedly generates an initial database state 𝐷, an intermediate query 𝐼 , and a referencing query 𝑄 (𝑥). It then constructs the corresponding SQL variants, executes them, and compares their returned results. An inconsistency report is generated when the compared variants return different results. Algorithm 1: The workflow of ERIQ Input: target DBMS 𝑀, time budget 𝑇 Output: inconsistency reports 𝑅𝑒𝑝𝑜𝑟𝑡𝑠 1 𝑅𝑒𝑝𝑜𝑟𝑡𝑠 ← ∅ 2 while the time budget 𝑇 is not exhausted do 3 Stage 1: Generate Test Inputs 4 𝐷 ← generateDatabase(𝑀) 5 𝐼 ← generateIntermediateQuery(𝐷, 𝑀) 6 𝑄 (𝑥) ← generateReferencingQuery(𝐼, 𝑀) Stage 2: Construct SQL Variants 𝑉 𝑎𝑟𝑖𝑎𝑛𝑡𝑠 ← ∅ foreach 𝑚 ∈ {VIEW, CTE, TEMPT} do 𝑠 ← buildVariant(𝐼, 𝑄, 𝑚, 𝑀) if 𝑠 can be legally constructed then add (𝑚, 𝑠) to 𝑉 𝑎𝑟𝑖𝑎𝑛𝑡𝑠

7 8 9 10 11 12

Stage 3: Compare Results 𝑅𝑒𝑠𝑢𝑙𝑡𝑠 ← ∅ foreach (𝑚, 𝑠) ∈ 𝑉 𝑎𝑟𝑖𝑎𝑛𝑡𝑠 do 𝑟 ← execute(𝑠, 𝐷, 𝑀) if execution succeeds and returns 𝑟 then 𝑛𝑟 ← normalize(𝑟 ) add (𝑚, 𝑛𝑟 ) to 𝑅𝑒𝑠𝑢𝑙𝑡𝑠

13 14 15 16 17 18 19

if |Results| ≥ 2 and all constructed variants return results and 𝑅𝑒𝑠𝑢𝑙𝑡𝑠 are inconsistent then add an inconsistency report to 𝑅𝑒𝑝𝑜𝑟𝑡𝑠

20

21

cleanup(𝐷, 𝑀)

22 23

return 𝑅𝑒𝑝𝑜𝑟𝑡𝑠

3.2

Database and Query Generation

This subsection describes how ERIQ generates the three core components of each test case: the initial database state 𝐷, the intermediate query 𝐼 , and the referencing query 𝑄 (𝑥). The initial database state 𝐷 provides the base-table schema and data used for testing. The intermediate query 𝐼 is a SELECT query that produces an intermediate query result from 𝐷, while the referencing query 𝑄 (𝑥) references this intermediate query result and produces the final query result. Database Generation. ERIQ first generates an initial database state 𝐷 consisting of a base-table schema and inserted data. To exercise type-related query-processing logic, ERIQ selects column types from a broad range of data types supported by the target DBMS,

including integer, floating-point, fixed-point, bit-value, temporal, string, JSON, and spatial types. After constructing the table schema, ERIQ generates type-compatible values and inserts them into the table. The generated data include ordinary random values, special and boundary-oriented values such as NULL, empty strings, zeros, negative numbers, large numeric values, and boundary temporal values, as well as representative JSON and spatial values. To improve the validity and diversity of generated SQL statements, ERIQ adopts a type-aware generation strategy for expressions and predicates. Rather than randomly combining columns, constants, operators, and functions, ERIQ considers their type compatibility when constructing expressions. For example, numeric columns are used in arithmetic and comparison expressions, string columns in pattern-matching and length expressions, and temporal columns in date/time extraction and interval-arithmetic expressions. JSON, spatial, and bit-value columns are similarly combined with compatible built-in functions and operators. This strategy reduces type-incompatible combinations and facilitates the generation of valid and structurally diverse SQL statements. Intermediate Query Generation. ERIQ generates an intermediate query 𝐼 , which is a SELECT query that produces an intermediate query result from the generated database. To exercise diverse DBMS query-processing logic, ERIQ generates intermediate queries with diverse structures, including projection, WHERE filtering, ORDER BY and LIMIT, DISTINCT, GROUP BY and aggregation, UNION and UNION ALL, inner and outer joins, IN/NOT IN subqueries, EXISTS/NOT EXISTS subqueries, scalar subqueries, derived tables, joins over derived tables, window functions, and aggregate queries over derived results. These structures exercise DBMS logic related to name resolution, type inference, predicate evaluation, join processing, subquery rewriting, set operations, aggregation, and window-function evaluation. ERIQ assigns stable aliases, such as vc_0 and vc_1, to the output expressions of 𝐼 . These aliases provide consistent column names for the intermediate query result when it is represented using a VIEW, a CTE, or a TEMPT. Referencing Query Generation. After generating 𝐼 , ERIQ generates a referencing query 𝑄 (𝑥). In 𝑄 (𝑥), 𝑥 is a placeholder for the name of the VIEW, CTE, or TEMPT used to represent the intermediate query result. 𝑄 (𝑥) references the intermediate query result through 𝑥 and produces the final query result. Based on the names and types of the columns produced by 𝐼 , ERIQ generates 𝑄 (𝑥) with diverse structures, including filtering, aggregation, joins, subqueries, window functions, and complex expressions and predicates. 𝑄 (𝑥) may also reference 𝑥 multiple times within the same statement. To obtain information about the columns produced by 𝐼 , ERIQ first creates the VIEW representation of 𝐼 and obtains its column names and types. This information is used only to generate a typevalid referencing query; the result returned by this VIEW is not used as an expected result during result comparison. When generating the intermediate query and the referencing query, ERIQ applies two additional constraints to reduce inconsistencies caused by nondeterminism. First, nondeterministic and time-dependent functions, such as RAND() and NOW(), are excluded from both the intermediate query and the referencing query because different executions may legitimately return different values.

Detecting DBMS Bugs by Constructing Equivalent Representations of Intermediate Query Results

Second, when ORDER BY is combined with LIMIT, ERIQ adds additional ordering expressions to reduce ties among rows with equal ordering keys, thereby reducing inconsistencies caused by nondeterministic row selection.

3.3

Constructing Equivalent Representations of Intermediate Query Results

Given the intermediate query 𝐼 and referencing query 𝑄 (𝑥), ERIQ constructs SQL variants that use a VIEW, a CTE, or a TEMPT to represent the same intermediate query result. For clarity, let 𝑆𝑉 (𝐼, 𝑄), 𝑆𝐶 (𝐼, 𝑄), and 𝑆𝑇 (𝐼, 𝑄) denote the VIEW, CTE, and TEMPT variants, respectively. Conceptually, they are constructed as follows: 𝑆𝑉 (𝐼, 𝑄) = [ CREATE VIEW v AS 𝐼 ; 𝑄 (v) ], 𝑆𝐶 (𝐼, 𝑄) = [ WITH cte AS (𝐼 ) 𝑄 (cte) ], 𝑆𝑇 (𝐼, 𝑄) = [ CREATE TEMPORARY TABLE

(1)

temp_t AS 𝐼 ; 𝑄 (temp_t) ]. Here, 𝑄 (v), 𝑄 (cte), and 𝑄 (temp_t) denote the same generated referencing query 𝑄 (𝑥), with 𝑥 replaced by v, cte, and temp_t, respectively. The brackets denote the SQL statement or statement sequence constituting one SQL variant. In the VIEW variant, the result produced by 𝐼 is represented using VIEW v, which is referenced by 𝑄 (v). In the CTE variant, the result produced by 𝐼 is represented using CTE cte, which is referenced by 𝑄 (cte) within the same SQL statement. In the TEMPT variant, the result produced by 𝐼 is stored in temp_t, which is referenced by 𝑄 (temp_t). The three variants are constructed from the same intermediate query 𝐼 and the same generated referencing query 𝑄 (𝑥). Their intended difference is the mechanism used to represent the intermediate query result. ERIQ therefore does not compare the result produced by 𝐼 in isolation. Instead, it compares the results returned by the SQL variants after the corresponding representation is referenced by 𝑄 (𝑥). When all three variants can be legally constructed and successfully return query results, their expected result consistency can be expressed as: 𝑅(𝑆𝑉 (𝐼, 𝑄), 𝐷) ≡ 𝑅(𝑆𝐶 (𝐼, 𝑄), 𝐷) ≡ 𝑅(𝑆𝑇 (𝐼, 𝑄), 𝐷),

(2)

where 𝑅(𝑆, 𝐷) denotes the query result returned by SQL variant 𝑆 under database state 𝐷, and ≡ denotes result equivalence according to the comparison procedure described in Section 3.4. Not every representation can be legally constructed for every referencing query. In particular, when 𝑄 (𝑥) references the intermediate query result multiple times within the same statement, a TEMPT variant may not be legally constructible on some target DBMSs because these systems prohibit multiple references to the same temporary table within a single statement [24]. In such cases, ERIQ excludes the TEMPT variant and compares the VIEW and CTE variants if both successfully return query results.

3.4

Result Comparison

After constructing the SQL variants, ERIQ executes all legally constructed variants. Result comparison is performed only when at least two variants are constructed and all constructed variants successfully return query results. If any constructed variant fails to

execute or does not return a query result, ERIQ discards the test case rather than treating the execution failure as a bug. Before comparison, ERIQ normalizes returned values to reduce spurious inconsistencies caused by representational and minor numerical differences. For representational differences, ERIQ converts values returned in different forms into stable comparable representations. For example, byte-string and byte-array values are converted into textual, hexadecimal, or numeric representations depending on their contents, while decimal values are canonicalized so that numerically equivalent forms have the same representation. For minor numerical differences, ERIQ normalizes finite floating-point values using a fixed-precision scientific-notation representation. This normalization addresses minor variations in floating-point results introduced by numerical precision and data-type conversions. Such variations can arise when approximate floating-point values are materialized into table columns, where data-type conversion may occur [18, 21]. ERIQ compares the normalized results under multiset semantics. Two results are considered equivalent if and only if they contain the same normalized rows with identical multiplicities. This comparison captures both differences in returned values and differences in the number of duplicate rows. For example, a result containing one row with value 1 is different from a result containing two rows with value 1, even though both contain the same distinct value. Differences in row order alone are not treated as inconsistencies. However, row ordering may affect which rows are returned when ORDER BY is combined with LIMIT. As described in Section 3.2, ERIQ adds additional ordering expressions to reduce ties among rows with equal ordering keys, thereby reducing inconsistencies caused by nondeterministic row selection. ERIQ classifies inconsistencies according to the number of compared variants and the relationships among their returned results. When all three variants are compared, an inconsistency is classified into one of four patterns: VIEW = TEMPT ≠ CTE, VIEW = CTE ≠ TEMPT, CTE = TEMPT ≠ VIEW, and all_diff, where the first three indicate that one variant differs from the equal results of the other two, and all_diff indicates that all three results are pairwise different. When the TEMPT variant cannot be legally constructed, ERIQ compares the VIEW and CTE variants; if their results differ, the inconsistency is recorded as VIEW ≠ CTE(TEMPT_SKIP). ERIQ therefore records five inconsistency patterns. For each detected inconsistency, ERIQ generates an inconsistency report containing the database schema, inserted data, intermediate query 𝐼 , referencing query 𝑄 (𝑥), constructed SQL variants, returned results and result-row counts, and the inconsistency pattern. ERIQ reports the inconsistency as a bug.

4

Evaluation

This section evaluates the effectiveness of ERIQ in detecting DBMS bugs. Specifically, we investigate the following research questions: • RQ1: How effective is ERIQ in detecting DBMS bugs? In this RQ, we analyze the detected bugs and their developerconfirmed status to evaluate ERIQ’s overall bug detection effectiveness. • RQ2: How does ERIQ perform compared with existing approaches? In this RQ, we compare ERIQ with existing

Xiaoxu Niu, Gong Chen, Jinfu Chen, and Xiaoyuan Xie

approaches to evaluate its complementary bug detection capability and its bug detection capability under the same testing budget. • RQ3: How do the three representations of intermediate query results contribute to ERIQ’s bug detection? In this RQ, we compare different combinations of VIEW, CTE, and TEMPT representations to analyze whether these representations provide complementary bug detection capability. • RQ4: What characteristics do the bugs detected by ERIQ exhibit? In this RQ, we analyze the SQL features involved in the detected bugs and examine representative root causes to understand the types of DBMS defects exposed by ERIQ.

This process took approximately two weeks, after which we obtained 64 bugs: 29 in MySQL, 18 in MariaDB, 13 in Percona, and 4 in OceanBase. Bug Status. We reported the 64 bugs to their respective DBMS communities and tracked their status. Table 1 summarizes the developer responses at the time of writing. Developers confirmed 63 of the 64 bugs: 54 were unique and previously unknown logic bugs, 8 were classified as duplicates of other bugs reported by ERIQ during our evaluation, and 1 was classified as a documentation issue. We discuss the documentation issue in Section 5. The remaining report was still under investigation. Among the 54 unique logic bugs, 2 have been fixed. These results show that ERIQ is effective at detecting real DBMS bugs. Table 1: Status of bugs detected by ERIQ.

4.1

Experimental Setup

Target DBMSs. We selected 4 widely used open-source DBMSs: MySQL [22], MariaDB [16], Percona [27], and OceanBase [26]. We tested the latest release versions available when the experiments began: MySQL 9.6.0, MariaDB 12.2.2, Percona 8.0.45-36, and OceanBase 4.4.2.0. These DBMSs have been extensively tested by many existing approaches [2, 4, 10, 14, 33, 41]. Therefore, finding new bugs in them is challenging. Baselines. We selected 4 state-of-the-art DBMS logic bug detection approaches as baselines: EDC [4], Radar [34], EET [11], and TLP [30]. These approaches have demonstrated their effectiveness by uncovering real-world DBMS logic bugs and represent different bug detection perspectives. EDC was selected as the primary baseline because it supports all target DBMSs in our evaluation. Radar, EET, and TLP were additionally included because they were used as baselines in EDC’s evaluation. These baselines enable us to evaluate whether ERIQ provides complementary bug detection capability by introducing a new testing perspective. Experimental infrastructure. We conducted all experiments on a server equipped with an Intel Xeon Gold 6254 processor (3.10 GHz, 18 cores, and 36 hardware threads), 64 GB of RAM, and approximately 1 TB of local storage. The server ran 64-bit Ubuntu 20.04.3 LTS. Each DBMS was deployed in a separate Docker container created from its official image and run with its default configuration.

DBMS

RQ1: Effectiveness of ERIQ

To answer RQ1, we evaluated ERIQ’s effectiveness based on the bugs it detected and their developer-confirmed status. We applied ERIQ to the 4 target DBMSs and ran it on each DBMS for 24 hours. Across all experiments, ERIQ generated 2,316 inconsistency reports. Because multiple reports may correspond to the same underlying bug, we minimized and internally deduplicated the reports before submitting them to the developers. Specifically, we first grouped the reports according to the structures of their intermediate queries and referencing queries to identify structurally similar cases. We then selected representative test cases from each group and minimized them using C-Reduce [28] together with manual simplification, while ensuring that the result inconsistency remained reproducible. Finally, we further deduplicated the minimized test cases by comparing their suspected root causes, involved functions, SQL keywords, and query structures.

Logic Bug Unique (Fixed)

Duplicate

Documentation Issue

Investigating

MySQL MariaDB Percona OceanBase

29 18 13 4

26 (1) 11 (1) 13 (0) 4 (0)

2 6 0 0

1 0 0 0

0 1 0 0

Total

64

54 (2)

8

1

1

Bug Severity. Among the 54 unique logic bugs, 48 were classified as Critical, Serious, Major, or High by developers. Specifically, MySQL developers classified 1 bug as Critical and 24 as Serious; MariaDB developers classified 10 bugs as Major; and Percona developers classified 13 bugs as High. Among the remaining 6 bugs, 1 MySQL bug was classified as Non-critical, 1 MariaDB bug was classified as Minor, and the 4 OceanBase bugs had not been assigned severity classifications. These results indicate that most of the unique logic bugs detected by ERIQ received relatively high severity classifications from the respective DBMS communities. Finding 1. ERIQ detected 64 bugs, 63 of which were confirmed by developers, and 2 have been fixed. Among the confirmed bugs, 54 were unique and previously unknown logic bugs, and 1 was a documentation issue. Of the 54 unique logic bugs, 48 received relatively high severity classifications.

4.3 4.2

Reported

RQ2: Comparison with Existing Approaches

To answer RQ2, we compared ERIQ with the 4 baselines introduced in Section 4.1: EDC [4], Radar [34], EET [11], and TLP [30]. We conducted two complementary experiments. First, we analyzed the distinctiveness of ERIQ’s 54 unique logic bugs by examining whether existing approaches could also detect them. Second, we compared ERIQ and the baselines under the same time budget to evaluate their practical bug detection capability. Distinctiveness of Bugs Detected by ERIQ. For each of the 54 unique logic bugs detected by ERIQ, we analyzed whether they could also be detected by existing approaches. Specifically, we started from each minimized test case and constructed corresponding test cases following the relation and construction procedure defined by each approach [4, 11, 30, 34]. A bug was considered detectable by a baseline if the corresponding test case violated the relation checked by that baseline; otherwise, it was considered not detectable by that baseline. Because this analysis evaluates the

Detecting DBMS Bugs by Constructing Equivalent Representations of Intermediate Query Results

relations checked by the baselines rather than their released implementations, it is independent of whether those implementations support the corresponding DBMSs. Table 2 presents the results.

Table 3: Number of logic bugs detected within 24 hours. “–” indicates that the approach does not support the DBMS. DBMS

Table 2: Number of ERIQ’s 54 unique logic bugs detectable by existing approaches. DBMS

ERIQ

EDC

Radar

EET

TLP

MySQL MariaDB Percona OceanBase

26 11 13 4

4 2 2 0

4 1 1 0

5 1 0 0

6 3 0 0

Total

54

8

6

6

9

Among the 54 unique logic bugs detected by ERIQ, EDC, Radar, EET, and TLP could detect 8, 6, 6, and 9 bugs, respectively, corresponding to 14.8%, 11.1%, 11.1%, and 16.7%. TLP detected the largest number of ERIQ-detected bugs in this analysis, but only 9 of the 54 bugs; the other baselines detected even fewer. For each baseline, at least 45 of the 54 bugs detected by ERIQ were not detected. These results show that ERIQ provides complementary bug detection capability by exposing many bugs that are not detected by existing approaches. Bug Detection Capability under the Same Time Budget. We further compared the practical bug detection capability of ERIQ and the baselines under the same time budget. We ran each tool for 24 hours on every target DBMS it supports. For ERIQ, we reused the results reported in Section 4.2. For each baseline, we used its publicly available implementation and executed it on all supported target DBMSs. All experiments used the same DBMS versions and experimental environment described in Section 4.1. For each baseline, we minimized and internally deduplicated the generated reports following the general procedure described in Section 4.2. The detected bugs were reported to the corresponding DBMS communities, and developer responses confirmed that they corresponded to real bugs. Table 3 presents the number of logic bugs detected by each tool within 24 hours. Across the 4 target DBMSs, ERIQ detected 54 logic bugs, whereas EDC, Radar, EET, and TLP detected 16, 2, 1, and 6 bugs, respectively. Among the baselines, EDC detected the largest number of bugs and, like ERIQ, was evaluated on all 4 target DBMSs. ERIQ detected 54 logic bugs compared with EDC’s 16, more than three times as many under the same time budget. At the time of writing, developers had not classified any of the bugs detected by the baselines as duplicates of the 54 unique logic bugs found by ERIQ. These results demonstrate that ERIQ detects more logic bugs than existing approaches under the same time budget and provide additional evidence that ERIQ complements these baselines. Finding 2. Existing approaches can detect at most 9 of the 54 unique logic bugs detected by ERIQ, indicating that many ERIQ-detected bugs are distinctive and that ERIQ complements existing approaches. Under the 24-hour budget, ERIQ detects 54 bugs versus 16 for the best-performing baseline, demonstrating its stronger bug detection capability.

4.4

ERIQ

EDC

Radar

EET

TLP

MySQL MariaDB Percona OceanBase

26 11 13 4

3 3 6 4

1 0 1 –

1 – – –

2 – 4 0

Total

54

16

2

1

6

RQ3: Contribution of the Three Representations of Intermediate Query Results

To answer RQ3, we analyzed how the VIEW, CTE, and TEMPT representations contribute to ERIQ’s bug detection. For each of the 54 unique logic bugs, we classified its minimized test case according to the result inconsistency patterns defined in Section 3.4. We then compared ERIQ’s strategy of considering all three representations and comparing all available SQL variants with three pairwise strategies: VIEW–CTE, VIEW–TEMPT, and CTE–TEMPT. The inconsistency patterns reveal which variants return different results and therefore allow us to determine how many bugs each pairwise strategy could expose without identifying which result is incorrect. Table 4 presents the distribution of the minimized test cases for the 54 unique logic bugs across the result inconsistency patterns. The pattern V ≠ C(T_SKIP) indicates that the TEMPT variant cannot be legally constructed because the referencing query references the intermediate query result multiple times within the same statement, so ERIQ compares only the VIEW and CTE variants and observes inconsistent results. When all three variants return results, V = T ≠ C, V = C ≠ T, and C = T ≠ V indicate that the CTE, TEMPT, and VIEW variants, respectively, return results different from the equal results of the other two variants. The pattern all_diff indicates that all three results are pairwise different. Table 4: Distribution of the minimized test cases for the 54 unique logic bugs across result inconsistency patterns. V = VIEW, C = CTE, and T = TEMPT.

DBMS

V≠C V=T V=C C=T all_diff Sum (T_SKIP) ≠ C ≠ T ≠ V

MySQL MariaDB Percona OceanBase

17 0 9 2

0 0 0 0

8 9 4 2

1 1 0 0

0 1 0 0

26 11 13 4

Total

28

0

23

2

1

54

The most frequent pattern is V ≠ C(T_SKIP), which accounts for 28 of the 54 test cases (51.9%). These cases demonstrate the contribution of the VIEW–CTE comparison when the TEMPT variant is unavailable. The second most frequent pattern is V = C ≠ T, which accounts for 23 cases (42.6%). Because the VIEW and CTE

Xiaoxu Niu, Gong Chen, Jinfu Chen, and Xiaoyuan Xie

variants return identical results in these cases, the inconsistency can be exposed only when the TEMPT variant is included in the comparison. The remaining test cases include 2 instances (3.7%) of C = T ≠ V and 1 instance (1.9%) of all_diff. Although none of the 54 test cases exhibits the V = T ≠ C pattern, ERIQ also generated a report with this pattern (MDEV#40246). Developers classified this report as a duplicate of MDEV#40143. The minimized test case for MDEV#40143 used in this analysis exhibits the all_diff pattern. We next quantified the advantage of considering all three representations over each pairwise strategy. Figure 3 summarizes the results. Bugs Exposed VIEW–CTE

Bugs Missed

31

23

VIEW–TEMPT

26

28

CTE–TEMPT

24

30

ERIQ

Table 5: SQL features involved in triggering result inconsistencies for the 54 unique logic bugs. A bug may involve multiple features. SQL Feature

54

0

10

20 30 40 Number of Unique Logic Bugs

50

Figure 3: Comparison of the three pairwise comparison strategies and ERIQ based on the minimized test cases for the 54 unique logic bugs. Based on these test cases, restricting the comparison to VIEW– CTE, VIEW–TEMPT, or CTE–TEMPT would expose 31, 26, and 24 bugs out of 54, respectively, leaving 23, 28, and 30 bugs undetected. In contrast, ERIQ considers all three representations and compares the results of all available SQL variants for each test case, thereby exposing all 54 bugs. Finding 3. The VIEW–CTE, VIEW–TEMPT, and CTE–TEMPT strategies expose 31, 26, and 24 bugs out of 54, respectively, while ERIQ exposes all 54 by considering the three representations, demonstrating their complementary contributions to ERIQ’s bug detection.

4.5

BY clauses, HAVING clauses, and aggregate function calls. Union includes UNION and UNION ALL operators. Subquery includes scalar subqueries, derived tables, and subqueries used with EXISTS, NOT EXISTS, IN, and NOT IN. Join includes explicit join operations such as JOIN, LEFT JOIN, and RIGHT JOIN. Distinct includes both SELECT DISTINCT clauses and aggregate functions qualified with DISTINCT. Order/Limit includes ORDER BY and LIMIT clauses. Window includes window-function calls containing an OVER clause. We counted each feature regardless of whether it appeared in the intermediate query or the referencing query. The categories are not mutually exclusive, and one SQL fragment may contribute to multiple categories. For example, COUNT(DISTINCT ...) is counted under both Group/Aggregate and Distinct, whereas COUNT(*) OVER (...) is counted under both Group/Aggregate and Window.

RQ4: Bug Analysis

To answer RQ4, we analyzed the SQL features involved in the minimized test cases for the 54 unique logic bugs and examined the root causes of representative bugs. We first classified the minimized test cases according to the SQL features involved in triggering their result inconsistencies. We then selected five representative bugs for in-depth root-cause analysis. Analysis of SQL Features Triggering Result Inconsistencies. We manually inspected the minimized test case for each bug and classified the SQL features involved in triggering its result inconsistency. Because one test case may contain multiple SQL features, we used a multi-label classification and counted each bug in every applicable category. We considered seven SQLfeature categories: Group/Aggregate, Union, Subquery, Join, Distinct, Order/Limit, and Window. Group/Aggregate includes GROUP

Group/Aggregate Union Subquery Join Distinct Order/Limit Window

MySQL

MariaDB

Percona

OceanBase

Total

13 14 10 6 3 1 1

4 1 3 3 3 2 1

6 5 5 5 2 1 0

1 3 1 0 1 0 0

24 23 19 14 9 4 2

Table 5 shows that the test cases triggering result inconsistencies involve diverse SQL features. Group/Aggregate is the most frequent category, appearing in 24 of the 54 bugs (44.4%), followed by Union in 23 bugs (42.6%), Subquery in 19 bugs (35.2%), and Join in 14 bugs (25.9%). Distinct, Order/Limit, and Window appear in 9 bugs (16.7%), 4 bugs (7.4%), and 2 bugs (3.7%), respectively. Overall, 47 of the 54 bugs (87.0%) involve at least one of the seven categories, and 37 bugs (68.5%) involve at least two categories. These results show that the bugs exposed by ERIQ involve a broad range of SQL features and that many involve multiple feature categories. The minimized test cases for the remaining seven bugs do not involve any of these seven categories. Among them, four involve temporal functions, two involve JSON-related expressions, and one involves an ENUM-valued CASE expression compared with a numeric value. These cases show that ERIQ can also expose bugs involving functions and expressions outside the seven SQL-feature categories considered above. Root-Cause Analysis of Representative Bugs. To further understand why the detected bugs produce incorrect results, we selected five representative bugs spanning diverse root causes and for which developer feedback or fixes provided sufficient evidence for in-depth analysis. Their identified causes include defects in function implementation, expression evaluation, derived merging, DISTINCT handling, and type information loss during materialization. Case 1: Incorrect state management across rows in the ST_IsValid function. Listing 1 presents a MariaDB bug (MDEV#40143) in which the VIEW, CTE, and TEMPT variants return one row with value 1, one row with value NULL, and two rows with value 1, respectively. Developer analysis confirmed that the root cause was a defect in

Detecting DBMS Bugs by Constructing Equivalent Representations of Intermediate Query Results

ST_IsValid, which retained stale nullability state across row evaluations. When derived_merge is disabled, both the VIEW and CTE variants return one row with value 1. Thus, derived merging affects how the defect manifests but does not eliminate it, because both variants still differ from the expected two-row result. Together with the correct TEMPT result, this observation shows that the same function-level defect can manifest differently across optimization and materialization paths. After the fix, all three variants consistently returned two rows with value 1. Case 2: Incorrect numeric evaluation of DAYNAME in a Boolean context. Listing 2 presents a MySQL bug (MySQL#120844) in which the VIEW variant returns an empty result, whereas the CTE variant returns the two expected rows. Because DAYNAME returns a nonnumeric string, its conversion in a Boolean context should yield zero, leaving the inner subquery empty. The corresponding MySQL worklog shows that Item_func_dayname inherited from the integer-valued Item_func_weekday, causing DAYNAME to use an integer-valued evaluation interface and return a weekday index when evaluated numerically. MySQL fixed the defect by changing its base class to Item_str_func. Listing 2: MySQL#120844. Incorrect numeric evaluation of MySQL’s DAYNAME in a Boolean context. CREATE TABLE t0 ( c1 VARCHAR (1) , c3 DATE ); INSERT INTO t0 VALUES ( 'x ' , ' 7056 -10 -15 ' ); -- VIEW variant CREATE VIEW v0 AS SELECT '' AS vc_0 , DAYNAME ( c3 ) AS vc_1 FROM t0 UNION SELECT c1 , DAYNAME ( c3 ) FROM t0 ; SELECT vc_0 FROM v0 WHERE vc_0 NOT IN ( SELECT vc_0 FROM v0 WHERE vc_1 ); – Returns: empty set × -- CTE variant WITH CTE AS ( SELECT '' AS vc_0 , DAYNAME ( c3 ) AS vc_1 FROM t0 UNION SELECT c1 , DAYNAME ( c3 ) FROM t0 ) SELECT vc_0 FROM CTE WHERE vc_0 NOT IN ( SELECT vc_0 FROM CTE WHERE vc_1 ); – Returns: ”, ’x’ ✓

Case 3: Incorrect evaluation of an expression involving a YEAR column during derived result processing. Listing 3 presents a Percona bug (PS#11410) in which the VIEW and CTE variants return 999.9999, whereas the TEMPT variant returns the expected value 1063.5000. Because COALESCE(b.c1, 0) evaluates to 2127, dividing it by 2 should yield 1063.5000. Developer analysis confirmed that the incorrect result was caused by incorrect expression evaluation during derived merging. The VIEW and CTE variants follow the derived-merging path, whereas the TEMPT variant materializes the intermediate query result before the subsequent computation and therefore does not exhibit this incorrect evaluation. Case 4: Incorrect result for multi-column DISTINCT with ORDER BY over a VIEW. Listing 4 presents a MariaDB bug (MDEV#40245) in which the VIEW variant returns only one row, whereas the CTE and TEMPT variants return the two expected rows. Because NULLIF(1, 0) evaluates to 1, the expression c0 / NULLIF(1, 0) evaluates to 2.0000 and 4.0000, respectively. The resulting rows are therefore distinct and should both be preserved by DISTINCT. Developer analysis identified the cause as incorrect DISTINCT handling in a

Listing 3: PS#11410. Incorrect evaluation of an expression involving a YEAR column during derived result processing in Percona. CREATE TABLE t0 ( c1 YEAR ); INSERT INTO t0 VALUES (2127); -- VIEW variant CREATE VIEW v0 AS SELECT COALESCE ( b . c1 , 0) AS vc_1 FROM t0 AS a JOIN t0 AS b ; SELECT vc_1 / 2 AS r FROM v0 ORDER BY 1; – Returns: 999.9999 × -- CTE variant WITH CTE AS ( SELECT COALESCE ( b . c1 , 0) AS vc_1 FROM t0 AS a JOIN t0 AS b ) SELECT vc_1 / 2 AS r FROM CTE ORDER BY 1; – Returns: 999.9999 × -- TEMPT variant CREATE TEMPORARY TABLE temp_t AS SELECT COALESCE ( b . c1 , 0) AS vc_1 FROM t0 AS a JOIN t0 AS b ; SELECT vc_1 / 2 AS r FROM temp_t ORDER BY 1; – Returns: 1063.5000 ✓

multi-column DISTINCT query with ORDER BY over a VIEW, where one projected column is constant-valued and another projected expression contains a constant-foldable subexpression. In this case, the VIEW variant fails to preserve both distinct rows, whereas the equivalent CTE and TEMPT variants return the expected results. Listing 4: MDEV#40245. Incorrect result for multi-column DISTINCT with ORDER BY over a VIEW in MariaDB. CREATE TABLE t0 ( c0 INT , c1 INT ); INSERT INTO t0 VALUES (2 , NULL ) , (4 , NULL ); -- VIEW variant CREATE VIEW v0 AS SELECT DISTINCT c1 AS vc_0 , c0 / NULLIF (1 , 0) AS vc_1 FROM t0 ORDER BY vc_0 , vc_1 ; SELECT * FROM v0 ; – Returns: (NULL, 2.0000) × -- CTE variant WITH CTE AS ( SELECT DISTINCT c1 AS vc_0 , c0 / NULLIF (1 , 0) AS vc_1 FROM t0 ORDER BY vc_0 , vc_1 ) SELECT * FROM CTE ; – Returns: (NULL, 2.0000), (NULL, 4.0000)✓ -- TEMPT variant CREATE TEMPORARY TABLE temp_t AS SELECT DISTINCT c1 AS vc_0 , c0 / NULLIF (1 , 0) AS vc_1 FROM t0 ORDER BY vc_0 , vc_1 ; SELECT * FROM temp_t ; – Returns: (NULL, 2.0000), (NULL, 4.0000)✓

Case 5: Loss of JSON Boolean type information during materialization. Listing 5 presents a MariaDB bug (MDEV#40252) in which the VIEW and CTE variants return one row with value true, whereas the TEMPT variant returns an empty result. Developer analysis confirmed that the defect occurs during the materialization of the JSON Boolean value returned by JSON_EXTRACT. During materialization, the JSON Boolean loses its original type information and is subsequently treated as a regular string. Therefore, when the predicate vc_1 OR 0 is evaluated, MariaDB applies string-to-number conversion, causing true to be converted to 0. As a result, the predicate evaluates to false and incorrectly filters out the row in the TEMPT variant.

Xiaoxu Niu, Gong Chen, Jinfu Chen, and Xiaoyuan Xie

Listing 5: MDEV#40252. Loss of JSON Boolean type information during materialization in MariaDB.

Listing 6: MySQL#120856. Different COERCIBILITY results between expression-derived and materialized values in MySQL.

CREATE TABLE t0 ( c2 JSON ); INSERT INTO t0 VALUES ( ' true ' );

CREATE TABLE t0 ( c2 BIGINT ); INSERT INTO t0 VALUES (100);

-- VIEW variant CREATE VIEW v0 AS SELECT JSON_EXTRACT ( c2 , '$ ') AS vc_1 FROM t0 ; SELECT * FROM v0 WHERE vc_1 OR 0; – Returns: true ✓

-- VIEW variant CREATE VIEW v0 AS SELECT BIN ( c2 ) AS vc FROM t0 ; SELECT COERCIBILITY ( vc ) FROM v0 ; – Returns: 4

-- CTE variant WITH CTE AS ( SELECT JSON_EXTRACT ( c2 , '$ ') AS vc_1 FROM t0 ) SELECT * FROM CTE WHERE vc_1 OR 0; – Returns: true ✓

-- CTE variant WITH CTE AS ( SELECT BIN ( c2 ) AS vc FROM t0 ) SELECT COERCIBILITY ( vc ) FROM CTE ; – Returns: 4

-- TEMPT variant CREATE TEMPORARY TABLE temp_t AS SELECT JSON_EXTRACT ( c2 , '$ ') AS vc_1 FROM t0 ; SELECT * FROM temp_t WHERE vc_1 OR 0; – Returns: empty set ×

-- TEMPT variant CREATE TEMPORARY TABLE temp_t AS SELECT BIN ( c2 ) AS vc FROM t0 ; SELECT COERCIBILITY ( vc ) FROM temp_t ; – Returns: 2

Finding 4. The minimized test cases for the 54 unique logic bugs involve diverse SQL features, and the five representative bugs exhibit varied root causes, showing that ERIQ can expose DBMS logic bugs with diverse characteristics.

5

Discussion

False Positive Mitigation. ERIQ takes several measures to mitigate false positives caused by the testing procedure itself. It excludes nondeterministic functions during query generation, thereby avoiding false positives arising from nondeterministic evaluations. ERIQ also compares only legally constructed SQL variants; when an equivalent TEMPT variant cannot be constructed under the current strategy, ERIQ skips the TEMPT variant and compares only the VIEW and CTE variants. In addition, ERIQ normalizes returned values and compares them using multiset semantics to mitigate inconsistencies caused solely by result representation or unspecified row ordering. In our evaluation, developers confirmed 63 of the 64 bugs; among them, 62 corresponded to logic bugs (54 unique and 8 duplicates), while one was classified as the documentation issue discussed below. None of the 63 confirmed bugs was identified as a false positive. Documentation Issue. One of the 64 bugs, MySQL#120856, was confirmed by MySQL developers and classified as a documentation issue. As shown in Listing 6, the VIEW and CTE variants return 4 for COERCIBILITY(vc), whereas the TEMPT variant returns 2. The MySQL developer explained that, in the VIEW and CTE variants, BIN(c2) is a character-string function and therefore has coercibility COERCIBLE (4). In contrast, after its result is materialized by CREATE TEMPORARY TABLE ... AS SELECT, it becomes a table column with coercibility IMPLICIT (2) [17]. The developer classified the report as a documentation issue because the coercibility of string functions was not clearly specified in the official documentation. Accordingly, we do not count this report as a logic bug; instead, it reveals a gap in MySQL’s documentation concerning the coercibility of string functions. Limitations. ERIQ has several limitations resulting from its equivalence requirements and result-comparison strategy. First,

nondeterministic functions are excluded to avoid inconsistent results caused by different evaluations, so bugs in such functions cannot currently be detected. Second, ERIQ does not generate recursive CTEs because the recursive self-references required by such CTEs cannot be represented by the equivalent VIEW and TEMPT forms currently constructed by ERIQ. Third, when an equivalent TEMPT variant cannot be legally constructed, ERIQ can compare only the VIEW and CTE variants, potentially missing bugs that require TEMPT for exposure. Finally, ERIQ compares results using multiset semantics and therefore does not target bugs that manifest solely as differences in row ordering.

6

Related Work

DBMS Logic Bug Detection. Existing approaches have proposed various techniques for detecting DBMS logic bugs [2, 4, 8, 11, 29– 31, 34–36, 38, 39, 42]. PQS [31] synthesizes queries that are guaranteed to return a selected pivot row and detects bugs when the pivot row is missing from the result. NoREC [29] rewrites a query into a form that inhibits DBMS optimizations and compares the results of the original and rewritten queries. TLP [30] partitions a query according to ternary logic and checks whether the combined results of the partitioned queries are consistent with the original query result. Pinolo [8] synthesizes queries whose results should be supersets or subsets of a seed query result and detects violations of the expected containment relation. EET [11] and CODDTest [42] apply equivalent transformations to query expressions and compare the results of the original and transformed queries. Radar [34] compares the results of the same query on databases containing the same data but different metadata. EDC [4] replaces data operation expressions with precomputed results and compares the original and transformed query results. In contrast to these approaches, ERIQ checks result consistency across equivalent representations of intermediate query results. It constructs SQL variants that use a VIEW, a CTE, or a TEMPT to represent the same intermediate query result and checks whether the variants return consistent results. ERIQ therefore explores a new perspective for detecting DBMS logic bugs, complementing existing approaches.

Detecting DBMS Bugs by Constructing Equivalent Representations of Intermediate Query Results

DBMS Test Case Generation. Existing DBMS test case generation techniques aim to improve the validity and diversity of generated SQL inputs [1, 3, 5–7, 10, 12, 13, 15, 32, 37, 40, 43]. SQLsmith [37] randomly constructs queries using database-schema information and AST-based rules. SQUIRREL [43] performs typebased mutations on an intermediate representation and uses dependency information to improve the syntactic and semantic validity of generated statements. Griffin [6] constructs metadata graphs that capture dependencies between statements and database objects and uses them for statement reshuffling and metadata-guided substitution. LEGO [13] uses coverage feedback to discover SQL type affinities and progressively generate diverse SQL statement sequences. SQLRight [15] combines coverage guidance with validity-oriented mutations, whereas DynSQL [10] dynamically collects databasestate information to incrementally construct complex and valid SQL inputs. QPG [1] uses query-plan diversity to guide databasestate mutations, and MIST [3] combines hierarchical SQL-feature guidance, error feedback, and coverage-guided Monte Carlo tree search for LLM-based DBMS test-case generation. ERIQ complements these approaches by constructing equivalent representations of intermediate query results and checking their result consistency. The diverse query-generation techniques employed by existing approaches could be incorporated into ERIQ to generate more diverse intermediate queries and referencing queries, potentially exposing additional DBMS logic bugs. Conversely, ERIQ’s representation-based consistency checking could be integrated with these generation techniques to further enhance their bug detection capability.

7

Conclusion

This paper presents ERIQ, a novel testing approach for detecting DBMS logic bugs by checking result consistency across equivalent representations of intermediate query results. ERIQ constructs SQL variants that use a VIEW, a CTE, or a TEMPT to represent the same intermediate query result and compares their returned results. By comparing results across these equivalent representations, ERIQ introduces a new perspective on DBMS logic bug detection that complements existing approaches. We evaluated ERIQ on 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. In future work, we plan to extend ERIQ to additional DBMSs, such as PostgreSQL, TiDB, and SQLite, and explore additional mechanisms for representing intermediate query results, such as derived tables, materialized views where supported, and persistent tables created from query results.

Acknowledgments This work was supported by the Key Program of the National Natural Science Foundation of China under Grant No. 62532008.

References [1] Jinsheng Ba and Manuel Rigger. 2023. Testing Database Engines via Query Plan Guidance. In 2023 IEEE/ACM 45th International Conference on Software Engineering (ICSE). IEEE, 2060–2071. https://doi.org/10.1109/icse48619.2023. 00174

[2] Jinsheng Ba and Manuel Rigger. 2024. Keep it simple: Testing Databases via Differential Query Plans. Proceedings of the ACM on Management of Data 2, 3 (2024), 1–26. https://doi.org/10.1145/3654991 [3] Yujia Chen, Yingli Zhou, Fangyuan Zhang, and Cuiyun Gao. 2026. LLM-Based Test Case Generation in DBMS through Monte Carlo Tree Search. arXiv preprint arXiv:2603.21530 (2026). https://doi.org/10.48550/arXiv.2603.21530 [4] Wenqian Deng, Jie Liang, Zhiyong Wu, Jingzhou Fu, and Yu Jiang. 2025. Detecting Logic Bugs in DBMSs via Equivalent Data Construction. Proceedings of the ACM on Management of Data 3, 6 (2025), 1–25. https://doi.org/10.1145/3769779 [5] Jingzhou Fu, Jie Liang, Zhiyong Wu, and Yu Jiang. 2024. Sedar: Obtaining Highquality Seeds for DBMS Fuzzing via Cross-DBMS SQL Transfer. In Proceedings of the IEEE/ACM 46th International Conference on Software Engineering (ICSE). 1–12. https://doi.org/10.1145/3597503.3639210 [6] Jingzhou Fu, Jie Liang, Zhiyong Wu, Mingzhe Wang, and Yu Jiang. 2022. Griffin: Grammar-free DBMS Fuzzing. In Proceedings of the 37th IEEE/ACM International Conference on Automated Software Engineering (ASE). 1–12. https://doi.org/10. 1145/3551349.3560431 [7] Xiyue Gao, Zhuang Liu, Jiangtao Cui, Hui Li, Hui Zhang, Kewei Wei, and Kankan Zhao. 2026. A Comprehensive Survey on Database Management System Fuzzing: Techniques, Taxonomy and Evaluation. Comput. Surveys 58, 10 (2026), 1–36. https://doi.org/10.1145/3799227 [8] Zongyin Hao, Quanfeng Huang, Chengpeng Wang, Jianfeng Wang, Yushan Zhang, Rongxin Wu, and Charles Zhang. 2023. Pinolo: Detecting Logical Bugs in Database Management Systems with Approximate Query Synthesis. In 2023 USENIX Annual Technical Conference (USENIX ATC 23). 345–358. https://www. usenix.org/conference/atc23/presentation/hao [9] International Organization for Standardization. 2023. Information technology – Database languages SQL – Part 2: SQL/Foundation. Technical Report ISO/IEC 9075-2:2023. International Organization for Standardization. https://www.iso. org/standard/76584.html [10] Zu-Ming Jiang, Jia-Ju Bai, and Zhendong Su. 2023. DynSQL: Stateful Fuzzing for Database Management Systems with Complex and Valid SQL Query Generation. In 32nd USENIX Security Symposium (USENIX Security 23). 4949–4965. https: //www.usenix.org/conference/usenixsecurity23/presentation/jiang-zu-ming [11] Zu-Ming Jiang and Zhendong Su. 2024. Detecting Logic Bugs in Database Engines via Equivalent Expression Transformation. In 18th USENIX Symposium on Operating Systems Design and Implementation (OSDI 24). 821–835. https: //www.usenix.org/conference/osdi24/presentation/jiang [12] Shadi Abdul Khalek, Bassem Elkarablieh, Yai O Laleye, and Sarfraz Khurshid. 2008. Query-aware Test Generation Using a Relational Constraint Solver. In 2008 23rd IEEE/ACM International Conference on Automated Software Engineering (ASE). IEEE, 238–247. https://doi.org/10.1109/ase.2008.34 [13] Jie Liang, Yaoguang Chen, Zhiyong Wu, Jingzhou Fu, Mingzhe Wang, Yu Jiang, Xiangdong Huang, Ting Chen, Jiashui Wang, and Jiajia Li. 2023. Sequenceoriented DBMS Fuzzing. In 2023 IEEE 39th International Conference on Data Engineering (ICDE). IEEE, 668–681. https://doi.org/10.1109/icde55515.2023.00057 [14] Jie Liang, Zhiyong Wu, Jingzhou Fu, Yiyuan Bai, Qiang Zhang, and Yu Jiang. 2024. WingFuzz: Implementing Continuous Fuzzing for DBMSs. In 2024 USENIX Annual Technical Conference (USENIX ATC 24). 479–492. https://www.usenix. org/conference/atc24/presentation/liang [15] Yu Liang, Song Liu, and Hong Hu. 2022. Detecting logical bugs of DBMS with Coverage-based Guidance. In 31st USENIX Security Symposium (USENIX Security 22). 4309–4326. https://www.usenix.org/conference/usenixsecurity22/ presentation/liang [16] MariaDB. 2026. MariaDB Server. https://mariadb.org/ Accessed: 2026-08-01. [17] MySQL. 2026. Collation Coercibility in Expressions. https://dev.mysql.com/doc/ refman/9.6/en/charset-collation-coercibility.html Accessed: 2026-04-01. [18] MySQL. 2026. CREATE TABLE ... SELECT Statement. https://dev.mysql.com/ doc/refman/9.6/en/create-table-select.html Accessed: 2026-04-01. [19] MySQL. 2026. CREATE TEMPORARY TABLE Statement. https://dev.mysql. com/doc/refman/9.6/en/create-temporary-table.html Accessed: 2026-04-01. [20] MySQL. 2026. CREATE VIEW Statement. https://dev.mysql.com/doc/refman/9. 6/en/create-view.html Accessed: 2026-04-01. [21] MySQL. 2026. Floating-Point Types (Approximate Value) - FLOAT, DOUBLE. https://dev.mysql.com/doc/refman/9.6/en/floating-point-types.html Accessed: 2026-04-01. [22] MySQL. 2026. MySQL Database. https://www.mysql.com/ Accessed: 2026-08-01. [23] MySQL. 2026. Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization. https://dev.mysql.com/doc/ refman/9.6/en/derived-table-optimization.html Accessed: 2026-04-01. [24] MySQL. 2026. Temporary Table Problems. https://dev.mysql.com/doc/refman/9. 6/en/temporary-table-problems.html Accessed: 2026-04-01. [25] MySQL. 2026. WITH (Common Table Expressions). https://dev.mysql.com/doc/ refman/9.6/en/with.html Accessed: 2026-04-01. [26] OceanBase. 2026. OceanBase Database. https://www.oceanbase.com/ Accessed: 2026-08-01. [27] Percona. 2026. Percona Database. https://www.percona.com/ Accessed: 202608-01.

Xiaoxu Niu, Gong Chen, Jinfu Chen, and Xiaoyuan Xie

[28] John Regehr, Yang Chen, Pascal Cuoq, Eric Eide, Chucky Ellison, and Xuejun Yang. 2012. Test-case Reduction for C Compiler Bugs. In Proceedings of the 33rd ACM SIGPLAN conference on Programming Language Design and Implementation (PLDI). 335–346. https://doi.org/10.1145/2254064.2254104 [29] Manuel Rigger and Zhendong Su. 2020. Detecting Optimization Bugs in Database Engines via Non-optimizing Reference Engine Construction. In Proceedings of the 28th ACM Joint Meeting on European Software Engineering Conference and Symposium on the Foundations of Software Engineering (ESEC/FSE). 1140–1152. https://doi.org/10.1145/3368089.3409710 [30] Manuel Rigger and Zhendong Su. 2020. Finding Bugs in Database Systems via Query Partitioning. Proceedings of the ACM on Programming Languages 4, OOPSLA (2020), 1–30. https://doi.org/10.1145/3428279 [31] Manuel Rigger and Zhendong Su. 2020. Testing Database Engines via Pivoted Query Synthesis. In 14th USENIX Symposium on Operating Systems Design and Implementation (OSDI 20). 667–682. https://www.usenix.org/conference/osdi20/ presentation/rigger [32] Donald R Slutz. 1998. Massive Stochastic Testing of SQL. In Proceedings of International Conference on Very Large Data Bases (VLDB), Vol. 98. 618–622. https://dl.acm.org/doi/abs/10.5555/645924.671199 [33] Jiansen Song, Wensheng Dou, Ziyu Cui, Qianwang Dai, Wei Wang, Jun Wei, Hua Zhong, and Tao Huang. 2023. Testing Database Systems via Differential Query Execution. In 2023 IEEE/ACM 45th International Conference on Software Engineering (ICSE). IEEE, 2072–2084. https://doi.org/10.1109/icse48619.2023. 00175 [34] Jiansen Song, Wensheng Dou, Yu Gao, Ziyu Cui, Yingying Zheng, Dong Wang, Wei Wang, Jun Wei, and Tao Huang. 2024. Detecting Metadata-Related Logic Bugs in Database Systems via Raw Database Construction. Proceedings of the VLDB Endowment 17, 8 (2024), 1884–1897. https://doi.org/10.14778/3659437.3659445 [35] Jiansen Song, Wensheng Dou, Yingying Zheng, Yu Gao, Ziyu Cui, Wei Wang, and Jun Wei. 2025. Detecting Schema-Related Logic Bugs in Relational DBMSs

via Equivalent Database Construction. Proceedings of the VLDB Endowment 18, 7 (2025), 2281–2294. https://doi.org/10.14778/3734839.3734861 [36] SQLancer. 2026. Automated Testing to Find Logic and Performance Bugs in Database Systems. https://github.com/sqlancer/sqlancer Accessed: 2026-08-01. [37] SQLsmith. 2026. A Random SQL Query Generator. https://github.com/anse1/ sqlsmith Accessed: 2026-08-01. [38] Xiu Tang, Sai Wu, Dongxiang Zhang, Feifei Li, and Gang Chen. 2023. Detecting Logic Bugs of Join Optimizations in DBMS. Proceedings of the ACM on Management of Data 1, 1 (2023), 1–26. https://doi.org/10.1145/3588909 [39] Xiu Tang, Shijie Yang, Sai Wu, Dongxiang Zhang, Wenchao Zhou, Feifei Li, and Gang Chen. 2025. Unveiling Logic Bugs in SPJG Query Optimizations within DBMS. ACM Transactions on Database Systems (TODS) 51, 1 (2025), 1–35. https://doi.org/10.1145/3764583 [40] Mingzhe Wang, Zhiyong Wu, Xinyi Xu, Jie Liang, Chijin Zhou, Huafeng Zhang, and Yu Jiang. 2021. Industry Practice of Coverage-guided Enterprise-level DBMS fuzzing. In 2021 IEEE/ACM 43rd International Conference on Software Engineering: Software Engineering in Practice (ICSE-SEIP). IEEE, 328–337. https://doi.org/10. 1109/icse-seip52600.2021.00042 [41] Zhiyong Wu, Jie Liang, Jingzhou Fu, Mingzhe Wang, and Yu Jiang. 2025. Puppy: Finding Performance Degradation Bugs in DBMSs via Limited-optimization Plan Construction. In 2025 IEEE/ACM 47th International Conference on Software Engineering (ICSE). IEEE, 679–690. https://doi.org/10.1109/icse55347.2025.00045 [42] Chi Zhang and Manuel Rigger. 2025. Constant Optimization Driven Database System Testing. Proceedings of the ACM on Management of Data 3, 1 (2025), 1–24. https://doi.org/10.1145/3709674 [43] Rui Zhong, Yongheng Chen, Hong Hu, Hangfan Zhang, Wenke Lee, and Dinghao Wu. 2020. Squirrel: Testing Database Management Systems with Language Validity and Coverage Feedback. In Proceedings of the 2020 ACM SIGSAC Conference on Computer and Communications Security (CCS). 955–970. https: //doi.org/10.1145/3372297.3417260

Related documents

• Detecting DBMS Bugs by Constructing Equivalent Representations of Intermediate Query Results#799589
• Towards Optimizing SQL Generation via LLM Routing#1108911
• DBRepro: Automated Database Synthesis via a Hybrid Constraint-Solving Approach for Reproducing Slow Queries#618639
• Property Testing for Recursive Query Languages#926776
• RAVEN: Retrieval-Augmented Vulnerability Exploration Network for Memory Corruption Analysis in User Code and Binary Programs#120419