Extensions: Bags, Nulls & Formal Verification

• Extensions: Bags, Nulls, and Formal Verification are frameworks that model SQL's multiset behavior and null-handling to support precise automated query reasoning.
• They employ SMT theories with specialized bag, set, and nullable sorts to formalize operations such as union, intersection, and join under three-valued logic.
• Empirical evaluations using tools like cvc5 and Coq mechanizations demonstrate robust equivalence checking and optimization grounded in rigorous meta-theoretical proofs.

Extensions of logic and formal verification frameworks to accommodate SQL’s distinctive constructs—bag semantics, null values, and the requirements of real-world database query analysis—have led to fundamental developments in automated reasoning, abstract semantics, and mechanized correctness proofs. The integration of these features into SMT (Satisfiability Modulo Theories) and proof assistants such as Coq enables rigorous reasoning over SQL queries with operational fidelity to both bag (multiset) and set semantics, flexible null handling, and tool-assisted equivalence checking.

1. Bag Semantics: Theories and Calculi

Bag or multiset semantics is essential for accurately modeling SQL’s default behavior, where duplicate rows are preserved unless explicitly eliminated. The SMT theory for finite bags introduces:

• Core Sorts: $\text{Int}$; $\text{Tuple}(\tau_0,\dots,\tau_k)$ as tuple sorts; $\text{Bag}(\tau)$ for finite multisets; $$\text{Table}(\tau_0,\ldots,\tau_k) \equiv \text{Bag}(\text{Tuple}(\tau_0,\ldots,\tau_k))$$.
• Signature: Constructors and operations include $\text{bag.empty}$, singleton $\text{bag}(e, n)$, multiplicity $m(e, s)$, duplicate-elimination $\text{bag.setof}(s)$, $\text{bag.union_disjoint}$ ($\sqcup$), $\text{Tuple}(\tau_0,\dots,\tau_k)$0 ($\text{Tuple}(\tau_0,\dots,\tau_k)$1), $\text{Tuple}(\tau_0,\dots,\tau_k)$2 ($\text{Tuple}(\tau_0,\dots,\tau_k)$3), two forms of difference, membership $\text{Tuple}(\tau_0,\dots,\tau_k)$4, subbag $\text{Tuple}(\tau_0,\dots,\tau_k)$5, and higher-order $\text{Tuple}(\tau_0,\dots,\tau_k)$6 and $\text{Tuple}(\tau_0,\dots,\tau_k)$7 functions.

Key axioms capture precise semantics:

$\text{Tuple}(\tau_0,\dots,\tau_k)$8

Additional rewriting rules and tableau calculi enable symbolic reasoning about query equivalence, join, and projection. Practical implementation in cvc5 is achieved via a dedicated bag-theory solver supporting operations and reasoning for bags as multirelations (Mohamed et al., 2024).

2. Set Semantics: Relation Theory and Embedding

Set semantics, in which duplicates are not preserved, is formalized as a theory of finite relations with:

• Core Sorts: $\text{Tuple}(\tau_0,\dots,\tau_k)$9, $\text{Bag}(\tau)$0.
• Operations: $\text{Bag}(\tau)$1, $\text{Bag}(\tau)$2, $\text{Bag}(\tau)$3, $\text{Bag}(\tau)$4, $\text{Bag}(\tau)$5, $\text{Bag}(\tau)$6, $\text{Bag}(\tau)$7, and lifted $\text{Bag}(\tau)$8 (filter) and $\text{Bag}(\tau)$9 (map) opreations.

The inference calculus and tableau system extend previous frameworks to support projection. Crucially, set semantics can be embedded in bag semantics by constraining all multiplicities: $$\text{Table}(\tau_0,\ldots,\tau_k) \equiv \text{Bag}(\text{Tuple}(\tau_0,\ldots,\tau_k))$$0; the $$\text{Table}(\tau_0,\ldots,\tau_k) \equiv \text{Bag}(\text{Tuple}(\tau_0,\ldots,\tau_k))$$1 operator enables duplicate elimination (Mohamed et al., 2024).

Table: Core Operators and Semantics

Construct	Bag Semantics	Set Semantics
Union	$$\text{Table}(\tau_0,\ldots,\tau_k) \equiv \text{Bag}(\text{Tuple}(\tau_0,\ldots,\tau_k))$$2	$$\text{Table}(\tau_0,\ldots,\tau_k) \equiv \text{Bag}(\text{Tuple}(\tau_0,\ldots,\tau_k))$$3
Intersection	$$\text{Table}(\tau_0,\ldots,\tau_k) \equiv \text{Bag}(\text{Tuple}(\tau_0,\ldots,\tau_k))$$4	$$\text{Table}(\tau_0,\ldots,\tau_k) \equiv \text{Bag}(\text{Tuple}(\tau_0,\ldots,\tau_k))$$5
Difference	$$\text{Table}(\tau_0,\ldots,\tau_k) \equiv \text{Bag}(\text{Tuple}(\tau_0,\ldots,\tau_k))$$6	$$\text{Table}(\tau_0,\ldots,\tau_k) \equiv \text{Bag}(\text{Tuple}(\tau_0,\ldots,\tau_k))$$7
Projection	$$\text{Table}(\tau_0,\ldots,\tau_k) \equiv \text{Bag}(\text{Tuple}(\tau_0,\ldots,\tau_k))$$8	$$\text{Table}(\tau_0,\ldots,\tau_k) \equiv \text{Bag}(\text{Tuple}(\tau_0,\ldots,\tau_k))$$9
Join	$\text{bag.empty}$0	$\text{bag.empty}$1

3. Nullable Sorts: Formalization of SQL NULL

Null values in SQL, representing missing or inapplicable data, introduce three-valued logic and nonstandard predicate behavior. This is addressed through specialized "nullable sorts," formalized as a parametric datatype:

• $\text{bag.empty}$2 with $\text{bag.empty}$3 and $\text{bag.empty}$4.
• Selector and predicates: $\text{bag.empty}$5, $\text{bag.empty}$6, $\text{bag.empty}$7, and a polymorphic lift operator:

$\text{bag.empty}$8

Boolean connectives and predicates are lifted to accommodate three-valued logic. For example, $\text{bag.empty}$9 evaluates to true if $\text{bag}(e, n)$0 is true, otherwise unknown, preserving the SQL logic table (Mohamed et al., 2024, Ricciotti et al., 2020).

4. Encoding SQL Constructs for Formal Reasoning

The verified translation of SQL fragments exploits the above theoretical frameworks:

• Selection: SQL selection maps to SMT’s $\text{bag}(e, n)$1 operator, lifting and unwrapping nullable logic as needed.
• Projection: Realized as $\text{bag}(e, n)$2 over tuples.
• Joins: Cross-product is modeled as bag or set product; inner and outer joins require selection over product with nuanced handling of null-padding for unmatched rows. Outer joins use unions and explicit insertion of null-valued tuples.
• Subqueries and Set Operations: In, exists, and scalar subqueries are modeled using bag or set membership, union, intersection, and difference with careful adherence to null semantics.
• Three-Valued Logic: All atomic predicates are lifted, with explicit handling of unknown results per SQL tradition.

In the Coq mechanization, inductive datatypes encode terms, conditions, and queries. The denotational semantics for bags formalizes relations as $\text{bag}(e, n)$3 functions with finite support, equipped with axioms ensuring extensionality and compositionality (Ricciotti et al., 2020).

5. Automated Verification of SQL Query Equivalence

Automated equivalence checking is realized by encoding SQL queries into the bag/set/nullable SMT logics:

• Bag-Equivalence: Query equivalence is defined as $\text{bag}(e, n)$4 for corresponding SMT encodings. Disequality is checked for satisfiability; unsat implies equivalence, sat yields a counterexample instance.
• Set-Equivalence: Mutual inclusion ($\text{bag}(e, n)$5 and $\text{bag}(e, n)$6) is checked under set semantics.
• Correctness Guarantees: Theories are equipped with refutation-soundness (closed derivations imply unsatisfiability) and solution-soundness (saturated leaves imply satisfiable models). Termination is proven for restricted fragments (excluding cross-products, joins, and map in the quantifier-free bag fragment).

The cvc5 implementation integrates a bag-theory solver (with nonlinear integer arithmetic), extended set-theory solver, and datatype (nullable) solver, all leveraging higher-order reasoning and quantifier instantiation for non-injective mappings (Mohamed et al., 2024).

6. Metatheoretical Foundations and Mechanized Proofs

Mechanization in Coq delivers key meta-results for denotational semantics:

• Functional Determinism: Each well-typed query or condition has a unique meaning (Lemma funeval).
• Compositionality: Semantics respect individual syntactic constructs.
• Extensionality: Relations denoting the same multiplicity function are equal.
• Soundness of Rewrite Rules: Common SQL rewrite equivalences (join reordering, unnesting) are proved by extensionality and functional semantics.
• Equivalence of Three-Valued to Two-Valued Logic: For every well-formed SQL query, a corresponding query can be constructed that, under two-valued logic, yields the same denotation as the original under three-valued logic.
• Certified RC-to-SQL Translation: A verified translation from a flat relational calculus with nulls to SQL preserves semantics, enabling reasoning about query transformations and optimizing rewrites in a compositional, verifier-friendly manner (Ricciotti et al., 2020).

7. Empirical Evaluation and Practical Implications

Benchmarking with public SQL equivalence problems (notably from Apache Calcite’s optimizer rules) grounds these frameworks in practical relevance:

Tool/Semantics	# Equiv Proven	# InEq Proven	# Unknown
SQLSolver (bag)	87	1	0
SPES (bag)	54	0	34
cvc5 (bag)	42	2	44
cvc5 (set)	83	1	4

Mutation-based evaluation shows cvc5’s generality accommodates richer theories (including null-handling and arithmetic) at some performance cost relative to specialized bag solvers. Timeouts occur on queries exhibiting highly nonlinear or non-injective projections.

This suggests that SMT-based frameworks supporting bags, sets, and nulls offer a principled, extensible basis for mechanized SQL verification, with soundness and correctness grounded in rigorous meta-theory (Mohamed et al., 2024, Ricciotti et al., 2020).