PaperlayerView exact revision on arXiv

Source-linked AI summary

Detecting DBMS Bugs by Constructing Equivalent Representations of Intermediate Query Results

Xiaoxu Niu, Gong Chen, Jinfu Chen, Xiaoyuan Xie

arXiv:2608.30385v1cs.DBcs.SE

TL;DR

DBMSs can return inconsistent results when VIEWs, CTEs, and TEMPTs represent the same intermediate query result, a perspective existing bug detectors had not explored. ERIQ constructs and compares these variants, detecting 64 bugs across four open-source DBMSs, 63 confirmed by developers and 54 unique and previously unknown logic bugs.

  • Problem

    Existing DBMS logic-bug detection approaches had not checked result consistency across equivalent representations of intermediate query results.

  • Method

    ERIQ constructs VIEW, CTE, and TEMPT SQL variants from the same intermediate query and referencing query, then compares their returned results.

  • Results

    64 bugs were detected across MySQL, MariaDB, Percona, and OceanBase; developers confirmed 63, including 54 unique previously unknown logic bugs.

  • Takeaways & Limitations

    ERIQ provides complementary DBMS logic-bug detection capability by exposing bugs missed by existing approaches.

  • Takeaways & Limitations

    ERIQ excludes nondeterministic functions and recursive CTEs, may omit TEMPT-dependent bugs, and does not target bugs manifested solely as row-order differences.

Abstract

from arXiv · show

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

DBMSs offer VIEWs, CTEs, and TEMPTs for representing intermediate query results, but equivalent representations can produce inconsistent outputs through different processing paths. ERIQ addresses this unexplored consistency perspective and found confirmed bugs across four open-source DBMSs.

  • Motivation: VIEWs, CTEs, and TEMPTs represent intermediate query results, yet equivalent representations can return inconsistent results when processed through different DBMS paths.VIEWs define reusable queries, CTEs provide statement-scoped result sets, and TEMPTs store results in temporary tables.
  • Research gap: Existing DBMS logic-bug detectors had not explored checking result consistency across equivalent intermediate-result representations.Prior methods used query transformations, partitions, containment checks, expression transformations, metadata variation, or precomputed results.
  • Approach: ERIQ constructs VIEW, CTE, and TEMPT SQL variants for the same intermediate result, executes them, and reports inconsistent returned results as bugs.The variants differ in the representation mechanism while preserving the underlying intermediate query.
  • Evaluation: 64 bugs were detected across MySQL, MariaDB, Percona, and OceanBase, including 54 unique previously unknown logic bugs.The total comprised 29 MySQL, 18 MariaDB, 13 Percona, and 4 OceanBase bugs.
  • Evaluation: 63 detected bugs were confirmed by developers, including one documentation issue, and two of the 54 unique logic bugs have been fixed.ERIQ also detected many bugs missed by existing approaches.

2 Background and Motivation

VIEWs, CTEs, and TEMPTs use different DBMS processing paths even when representing the same intermediate result. A confirmed MariaDB example shows that this equivalence can expose logic bugs through inconsistent results, motivating ERIQ's consistency-checking strategy.

  • Representations: VIEWs, CTEs, and TEMPTs differ in syntax, scope, lifetime, and processing, although they can represent the same intermediate query result.VIEWs and CTEs may be merged or materialized, whereas TEMPTs materialize results into temporary tables before a separate query runs.
  • Motivating example: MariaDB returned one row with value 1 for the VIEW variant, one NULL row for the CTE variant, and two rows with value 1 for the TEMPT variant.The variants used the same intermediate SELECT and were expected to return the same result.
  • Motivating example: MariaDB developers confirmed that the expected result was two rows, establishing the discrepancy as a real logic bug rather than an intended semantic difference.The bug was later fixed by MariaDB developers.
  • Motivating example: The example shows that equivalent-representation bugs can silently produce incorrect query results without crashes or explicit errors.Such incorrect results may propagate to downstream applications.
  • Key insight: Because equivalent representations may traverse different DBMS paths, comparing their query results provides a basis for detecting logic bugs.Existing approaches had not explored this perspective.

3 Approach

ERIQ repeatedly generates databases and queries, constructs VIEW, CTE, and TEMPT variants for one intermediate result, and compares their outputs. Any inconsistency becomes an inconsistency report for the target DBMS.

  • Workflow: ERIQ generates an initial database state, an intermediate SELECT query, and a referencing query over the intermediate result.The workflow runs repeatedly within a specified time budget.
  • Equivalent representations: ERIQ constructs VIEW, CTE, and TEMPT variants from the same intermediate query and substitutes each representation into the same referencing query.The resulting variants differ in the relation name used to reference the representation.
  • Result comparison: ERIQ executes the SQL variants and compares their returned results, generating an inconsistency report when the results differ.The target DBMS and time budget are workflow inputs, while inconsistency reports are outputs.

13 Stage 3: Compare Results

ERIQ generates a database state, intermediate query, and referencing query, then constructs equivalent VIEW, CTE, and TEMPT variants from the same inputs. It executes legally constructible variants, normalizes and compares successful results, and classifies inconsistencies as potential bugs.

  • Test-case generation: ERIQ generates an initial database state, a SELECT-based intermediate query, and a referencing query that produces the final result.The intermediate query produces the represented result, while the referencing query consumes it.
  • Test-case generation: Intermediate queries cover projections, filters, joins, subqueries, set operations, aggregation, derived tables, and window functions to exercise diverse DBMS logic.These structures target name resolution, type inference, predicate evaluation, join processing, rewriting, aggregation, and window-function evaluation.
  • Consistency controls: ERIQ excludes nondeterministic functions and adds ordering expressions with LIMIT to reduce inconsistencies caused by legitimate execution variability.RAND() and NOW() are excluded, while additional ORDER BY expressions reduce ties among rows with equal ordering keys.
  • Equivalent representations: The same intermediate and referencing queries are represented through VIEW, CTE, and TEMPT variants, differing only in the representation mechanism.ERIQ compares the final results after the representation is referenced, rather than comparing the intermediate result alone.
  • Result comparison: ERIQ compares normalized results only when at least two legally constructed variants successfully return results, discarding failed executions.Normalization reduces spurious differences from representational and minor numerical variations.
  • Result comparison: Under multiset semantics, ERIQ detects value or duplicate-count differences and classifies three-variant inconsistencies by their equality pattern.It distinguishes VIEW = TEMPT ≠ CTE, VIEW = CTE ≠ TEMPT, CTE = TEMPT ≠ VIEW, and all_diff patterns.

4 Evaluation

ERIQ detected and validated numerous DBMS bugs across four systems, while revealing distinctive inconsistencies that existing approaches often miss. Comparing all available VIEW, CTE, and TEMPT variants exposed more bugs than pairwise strategies and covered diverse SQL features.

  • RQ1: Effectiveness of ERIQ: 48 of the 54 unique logic bugs received relatively high severity classifications.These classifications included Critical, Serious, Major, or High.
  • RQ1: Effectiveness of ERIQ: 64 bugs were detected across MySQL, MariaDB, Percona, and OceanBase; developers confirmed 63, including 54 unique previously unknown logic bugs.Two of the 54 unique logic bugs had been fixed.
  • RQ2: Comparison with Existing Approaches: Existing approaches detected at most 9 of ERIQ’s 54 unique logic bugs, leaving at least 45 undetected by each baseline.EDC, Radar, EET, and TLP detected 8, 6, 6, and 9 bugs, respectively.
  • RQ2: Comparison with Existing Approaches: Under the 24-hour budget, ERIQ detected 54 bugs versus 16 for the best-performing baseline.The compared baselines detected 16, 2, 1, and 6 bugs, respectively, for EDC, Radar, EET, and TLP.
  • RQ3: Contribution of the Three Representations: The V ≠C(T_SKIP) pattern covered 28 of 54 cases (51.9%), while V = C ≠T covered 23 cases (42.6%).The T_SKIP pattern reflects unavailable TEMPT variants; the second pattern requires TEMPT because VIEW and CTE return identical results.
  • RQ3: Contribution of the Three Representations: VIEW–CTE, VIEW–TEMPT, and CTE–TEMPT exposed 31, 26, and 24 bugs, respectively, whereas ERIQ exposed all 54.Considering all three representations therefore captured cases missed by every individual pairwise strategy.
  • RQ4: Bug Analysis: Group/Aggregate, Union, Subquery, and Join appeared in 24, 23, 19, and 14 of the 54 bugs, respectively.Overall, 47 bugs (87.0%) involved at least one of seven analyzed categories, and 37 (68.5%) involved at least two; seven other cases involved temporal, JSON, or ENUM-related expressions.
  • RQ4: Bug Analysis: Developer analysis linked representative inconsistencies to stale nullability state, incorrect expression evaluation, and DISTINCT handling across representation paths.The examples include MariaDB’s ST_IsValid bug, Percona’s YEAR-expression bug, and MariaDB’s multi-column DISTINCT bug.

5 Discussion

ERIQ’s evaluation exposed a documentation issue involving COERCIBILITY after TEMPT materialization and several scope limitations arising from its equivalence and comparison requirements.

  • Documentation issue: One confirmed report was classified as a documentation issue because MySQL’s documentation did not clearly specify coercibility for string functions.VIEW and CTE variants returned 4, while the TEMPT variant returned 2 after materialization.
  • Documentation issue: MySQL returned COERCIBILITY 4 for VIEW and CTE variants but 2 for the TEMPT variant because materialization changed the result into a table column.The developer explained that BIN(c2) is a character-string function in the VIEW and CTE variants, whereas CREATE TEMPORARY TABLE ... AS SELECT materializes it as a column.
  • Limitations: ERIQ excludes nondeterministic functions, so bugs in those functions cannot currently be detected.The exclusion avoids inconsistent results caused by different evaluations.
  • Limitations: ERIQ does not generate recursive CTEs because their required self-references cannot be represented by the equivalent VIEW and TEMPT forms it constructs.
  • Limitations: When a legally equivalent TEMPT variant cannot be constructed, ERIQ compares only VIEW and CTE variants and may miss bugs requiring TEMPT exposure.
  • Limitations: Multiset comparison means ERIQ does not target bugs that manifest solely as differences in row ordering.

6 Related Work

ERIQ introduces a distinct DBMS logic-bug detection perspective by checking result consistency across equivalent VIEW, CTE, and TEMPT representations, complementing existing detection and query-generation techniques.

  • DBMS logic bug detection: Existing DBMS logic-bug detectors use transformations, ternary-logic partitioning, pivot rows, containment, and other relations rather than equivalent intermediate-result representations.
  • DBMS logic bug detection: ERIQ constructs VIEW, CTE, and TEMPT variants for the same intermediate query result and checks whether their returned results are consistent.
  • DBMS logic bug detection: ERIQ complements existing approaches by introducing representation-based consistency checking as a new perspective for DBMS logic-bug detection.
  • DBMS test case generation: Existing query-generation techniques could supply more diverse intermediate and referencing queries to ERIQ, while ERIQ’s consistency checking could be integrated into those techniques.

7 Conclusion

The paper presents ERIQ, which detects DBMS logic bugs by comparing equivalent VIEW, CTE, and TEMPT representations of intermediate query results. Across four open-source DBMSs, it found 64 bugs, including 54 unique previously unknown logic bugs among developer-confirmed reports.

  • Approach: ERIQ detects DBMS logic bugs by comparing returned results from VIEW, CTE, and TEMPT variants representing the same intermediate query result.
  • Results: 64 bugs were detected across MySQL, MariaDB, Percona, and OceanBase, with 63 confirmed by developers and two fixed.
  • Results: Among the confirmed bugs, 54 were unique and previously unknown logic bugs, while one was a documentation issue.
  • Future work: Future work will extend ERIQ to additional DBMSs and additional mechanisms for representing intermediate query results.
Loading 2608.30385v1…