Conformance Testing of Relational DBMS Against SQL Specifications

2024-06-13

arXiv.org

AI Summary

This work addresses the challenge of verifying relational database management systems’ (RDBMS) compliance with SQL semantics at the standard specification level. We present the first executable Prolog reference implementation grounded in the complete formal SQL semantics defined by ISO/IEC 9075, integrated with differential fuzz testing for semantic-level black-box validation. Unlike prior approaches relying solely on crash detection or meta-transformation, our method enables end-to-end verifiable modeling of SQL standard semantics. Empirical evaluation across MySQL, TiDB, SQLite, and DuckDB uncovered 19 previously unknown vulnerabilities and 11 semantic inconsistencies—each traceable to explicit violations, omissions, or ambiguities in the SQL standard. Our approach significantly enhances the decidability and interpretability of SQL implementation correctness.

Technology Category

• Data Mining & Knowledge Management: Representing, Reasoning, and Using Provenance, Trust
• Constraint Satisfaction and Optimization: Satisfiability Modulo Theories
• Knowledge Representation and Reasoning: Logic Programming

Application Category

• Semantics and Knowledge: Provenance, trust, security and privacy, and ethical issues in managing semantic data
• Security and Privacy: Data transparency and provenance
• Search and Retrieval-Augmented AI: Web evaluation methodologies and metrics

Abstract

A Relational Database Management System (RDBMS) is one of the fundamental software that supports a wide range of applications, making it critical to identify bugs within these systems. There has been active research on testing RDBMS, most of which employ crash or use metamorphic relations as the oracle. Although existing approaches can detect bugs in RDBMS, they are far from comprehensively evaluating the RDBMS's correctness (i.e., with respect to the semantics of SQL). In this work, we propose a method to test the semantic conformance of RDBMS i.e., whether its behavior respects the intended semantics of SQL. Specifically, we have formally defined the semantics of SQL and implemented them in Prolog. Then, the Prolog implementation serves as the reference RDBMS, enabling differential testing on existing RDBMS. We applied our approach to four widely-used and thoroughly tested RDBMSs, i.e., MySQL, TiDB, SQLite, and DuckDB. In total, our approach uncovered 19 bugs and 11 inconsistencies, which are all related to violating the SQL specification or missing/unclear specification, thereby demonstrating the effectiveness and applicability of our approach.

Problem

• Testing RDBMS semantic conformance to SQL specifications
• Formally defining SQL semantics for reference implementation
• Detecting bugs and inconsistencies in major RDBMS systems

Innovation

• Formally defined SQL semantics in Prolog
• Used Prolog as reference for differential testing
• Uncovered bugs via SQL specification violations