Conjunctive Queries with Arithmetic Comparisons

• CQAC is an extension of classical conjunctive queries that integrates arithmetic comparisons to enable precise filtering and advanced query rewriting.
• The approach leverages hypergraph characterizations and min/max predicates to delineate tractability boundaries and establish fine-grained complexity dichotomies.
• Applications span analytical SQL querying, OLAP, and view-based data management, while ongoing research explores query containment and efficient evaluation techniques.

A conjunctive query with arithmetic comparisons (CQAC) is a fundamental extension of classical conjunctive queries (CQs) that augments relational atoms with built-in arithmetic comparisons—inequalities, equalities, and disequalities—between variables and constants. The CQAC framework underpins both theoretical and practical questions in database theory, especially around the expressiveness, evaluation complexity, and containment of query languages, and is critically involved in query rewriting and certain-answer computation over incomplete data. Of particular recent interest is the subclass of CQACs involving minimum or maximum constraints (e.g., $x \leq \min X$), which yields intricate complexity dichotomies in ranked enumeration, direct access, and predicate elimination for self-join-free queries.

1. Formal Definitions and Syntax

A conjunctive query with arithmetic comparisons (CQAC) over a relational schema $\mathcal{R}$ is written: $$Q(\bar x) \; :\!-\; R_1(\bar y_1), \ldots, R_m(\bar y_m), \; \beta$$ where each $R_i(\bar y_i)$ is a relational atom, and $\beta$ is a finite conjunction of arithmetic atoms of the form $X \;\theta\; Y$ with $\theta \in \{ <,>,\leq,\geq,=,\neq \}$, $X,Y \in \mathrm{Var} \cup \mathrm{Const}$. The set $\bar x$ of distinguished (head) variables must appear in the body. All variables in the head or in any comparison must also occur in at least one relational atom (the "safety" restriction).

The semantics of CQAC is standard: for a database instance $D$ and assignment $\mathcal{R}$0, $\mathcal{R}$1 if $\mathcal{R}$2 makes all relational atoms true on $\mathcal{R}$3 and all arithmetic atoms true in the natural order or arithmetic structure ($\mathcal{R}$4, $\mathcal{R}$5, or $\mathcal{R}$6 as domain).

Special attention is given to semi-interval comparisons:

• Left semi-interval (LSI): $\mathcal{R}$7 or $\mathcal{R}$8
• Right semi-interval (RSI): $\mathcal{R}$9 or $$Q(\bar x) \; :\!-\; R_1(\bar y_1), \ldots, R_m(\bar y_m), \; \beta$$0

For CQACs with min/max, one further allows min-predicates ($$Q(\bar x) \; :\!-\; R_1(\bar y_1), \ldots, R_m(\bar y_m), \; \beta$$1) and max-predicates ($$Q(\bar x) \; :\!-\; R_1(\bar y_1), \ldots, R_m(\bar y_m), \; \beta$$2), where $$Q(\bar x) \; :\!-\; R_1(\bar y_1), \ldots, R_m(\bar y_m), \; \beta$$3 is a set of variables. If $$Q(\bar x) \; :\!-\; R_1(\bar y_1), \ldots, R_m(\bar y_m), \; \beta$$4, then $$Q(\bar x) \; :\!-\; R_1(\bar y_1), \ldots, R_m(\bar y_m), \; \beta$$5 is equivalent to $$Q(\bar x) \; :\!-\; R_1(\bar y_1), \ldots, R_m(\bar y_m), \; \beta$$6 (Carmeli et al., 22 Oct 2025, Afrati et al., 12 Sep 2025, Afrati et al., 2020).

2. Structural Properties and Hypergraph Characterization

The evaluation and tractability of CQACs—especially with min/max—are strongly governed by the structural properties of their relational hypergraphs:

• The hypergraph $$Q(\bar x) \; :\!-\; R_1(\bar y_1), \ldots, R_m(\bar y_m), \; \beta$$7 has as vertices the variables of $$Q(\bar x) \; :\!-\; R_1(\bar y_1), \ldots, R_m(\bar y_m), \; \beta$$8 and as edges the variable sets of each atom $$Q(\bar x) \; :\!-\; R_1(\bar y_1), \ldots, R_m(\bar y_m), \; \beta$$9.
• $R_i(\bar y_i)$0 is acyclic if $R_i(\bar y_i)$1 admits a join-tree (α-acyclicity).
• $R_i(\bar y_i)$2 is free-connex if $R_i(\bar y_i)$3, i.e., the hypergraph augmented with a hyperedge for the distinguished variables, is also acyclic.

The tractability of many key CQAC tasks depends on the absence of bad paths, a notion formalized as follows for self-join-free CQs with min/max predicates:

• There is a bad path between $R_i(\bar y_i)$4 and $R_i(\bar y_i)$5 (for $R_i(\bar y_i)$6) if there exists a chordless path of length at least three in the hypergraph connecting $R_i(\bar y_i)$7 to some $R_i(\bar y_i)$8 (Carmeli et al., 22 Oct 2025).

3. Fine-Grained Complexity Dichotomies

Research has established a precise landscape of tractability for natural computational problems concerning CQACs—especially those with min/max comparisons—in terms of structural and comparison-pattern restrictions.

Table: Complexity Dichotomies for CQAC Tasks with Min/Max (Carmeli et al., 22 Oct 2025)

Task	Tractable Class	Complexity (Tractable)	Intractable If
Counting	Free-connex acyclic, no bad path	$R_i(\bar y_i)$9 time	Not free-connex acyclic or bad path exists
Ranked Enumeration	Free-connex acyclic	$\beta$0 preprocessing + $\beta$1 delay	Otherwise
Direct Access	Free-connex acyclic, no bad path	$\beta$2 access	Otherwise
Predicate Elimination	Free-connex acyclic, no bad path	$\beta$3 time	Otherwise

Here, "no bad path" refers to the absence of chordless length-$\beta$4 paths between critical min/max variables, and $\beta$5 hides polylogarithmic factors. Lower bounds are conditioned on Hypotheses such as SETH (Strong Exponential Time Hypothesis) and Hyperclique (Carmeli et al., 22 Oct 2025).

4. Algorithmic and Proof Techniques

Positive results for CQAC evaluation leverage several key procedural insights. For acyclic queries:

• Semiring Aggregation: Aggregating over commutative semirings (sum-product, min-max, etc.) on full acyclic queries enables $\beta$6 bottom-up computation (Yannakakis-style algorithms).
• Min-predicate elimination: Partition total orders on variables into a constant-size set of partial orders making $\beta$7 minimal. For each, pick a join tree that enforces the cover-inequalities, then apply semijoin and filter steps to reduce to standard acyclic CQs, allowing decomposition of $\beta$8 into a small union of simpler queries (Carmeli et al., 22 Oct 2025).
• Direct access and enumeration: Utilization of secondary data structures for variable-lexicographic order, prefix sums, and repeated parallel enumeration with duplicate suppression ensure constant or logarithmic output delay given acyclicity and absence of bad paths.

Lower bounds use standard reductions:

• Enumeration or access hardness for non-free-connex queries reduces to Boolean matrix multiplication or triangle-detection. Counting for queries with a chordless path (length $\beta$9) on equality incurs SETH/Hyperclique lower bounds (Carmeli et al., 22 Oct 2025).

Containment and certain answer computation employ containment-implication conditions and Datalog transformations (Afrati et al., 12 Sep 2025, Afrati et al., 2020):

• Containment $X \;\theta\; Y$0 reduces to testing $X \;\theta\; Y$1 for all containment mappings $X \;\theta\; Y$2 of bodies.
• For RSI1 CQACs (at most one RSI, possibly multiple LSIs, all closed), containment is in NP; for arbitrary CQACs, $X \;\theta\; Y$3-complete (Afrati et al., 12 Sep 2025, Afrati et al., 2020).
• Maximally contained rewriting (MCR) and certain answers computation transform CQAC to Datalog$X \;\theta\; Y$4, run the inverse-rule algorithm, and re-express as CQAC or unions thereof; RSI1 admits PTIME certain answers (Afrati et al., 12 Sep 2025, Afrati et al., 2020).

For count-distinct aggregates, equivalence is determined by computing cores and their "flips," the latter reversing inequalities inside equal-sets; decision lies in the third level of the polynomial hierarchy, $X \;\theta\; Y$5, and is strictly harder than core-isomorphism for standard CQAC equivalence (Hariri et al., 2015).

5. Syntactic Restrictions and Complexity Boundaries

The expressiveness and tractability of CQACs pivot on the pattern and number of arithmetic comparisons. Key restriction classes include:

• RSI1: At most one RSI plus any number of LSIs (all closed). Containment falls in NP, certain answer computation in PTIME (Afrati et al., 12 Sep 2025, Afrati et al., 2020).
• One-AC: Containing CQAC with at most one non-equality AC: containment in NP (Afrati et al., 12 Sep 2025).
• Semi-interval-only: All comparisons are SI (open/closed). If containing CQ is only open SI (with or without $X \;\theta\; Y$6), containment is $X \;\theta\; Y$7-complete (Afrati et al., 12 Sep 2025).
• General CQACs: Allowing joins between head/body vars or multiple (open/closed) ACs, the containment and certain answer problems are $X \;\theta\; Y$8-complete (Afrati et al., 12 Sep 2025, Afrati et al., 2020).
• Min/Max: For CQAC with predicates of the form $X \;\theta\; Y$9 or $\theta \in \{ <,>,\leq,\geq,=,\neq \}$0, near-ideal complexity is obtained precisely under the free-connex acyclic/no-bad-path regime (Carmeli et al., 22 Oct 2025).

These dichotomies delineate the boundary between efficient and intractable cases—there is no PTIME algorithm for containment or MCR in the general case unless the polynomial hierarchy collapses.

6. Applications, Open Problems, and Research Directions

CQACs with arithmetic constraints model a variety of analytical SQL queries with filters (e.g., time windows, price ranges, top-$\theta \in \{ <,>,\leq,\geq,=,\neq \}$1, or sliding aggregates) and arise naturally in data integration, OLAP, and view-based data management. The fine-grained dichotomies directly inform query optimizer strategies and complexity-aware data infrastructure.

Key open research directions include:

• Extension to multiple min/max or mixed min/max predicates; current dichotomies treat single min/max only (Carmeli et al., 22 Oct 2025).
• Generalization to arbitrary linear or polynomial arithmetic comparisons (e.g., $\theta \in \{ <,>,\leq,\geq,=,\neq \}$2) and aggregates beyond min/max (e.g., $\theta \in \{ <,>,\leq,\geq,=,\neq \}$3th smallest, HAVING clauses, SUM/AVG), requiring new combinatorial and algorithmic techniques (Carmeli et al., 22 Oct 2025, Hariri et al., 2015).
• Relaxation of self-join-free and bounded-treewidth conditions—quantifying complexity as join patterns grow more intricate (Carmeli et al., 22 Oct 2025).
• Improved understanding of containment and certain answers for count-distinct and other aggregates, especially over discrete domains (Hariri et al., 2015).

The development of semiring-aggregation in full acyclic CQs, order-partitioning of min/max constraints, and the reciprocal use of Datalog$\theta \in \{ <,>,\leq,\geq,=,\neq \}$4 transformations are anticipated to drive advances for CQAC in both expressiveness and computational tractability. The study of CQAC thus remains a central point of interplay between descriptive complexity, database theory, and practical querying (Carmeli et al., 22 Oct 2025, Afrati et al., 12 Sep 2025, Afrati et al., 2020, Hariri et al., 2015).