Explaining Wrong Queries Using Small Examples

Abstract

For testing the correctness of SQL queries, e.g., evaluating student submissions in a database course, a standard practice is to execute the query in question on some test database instance and compare its result with that of the correct query. Given two queries Q₁ and Q₂, we say that a database instance D is a counterexample (for Q₁ and Q₂) if Q₁(D) differs from Q₂(D); such a counterexample can serve as an explanation of why Q₁ and Q₂ are not equivalent. While the test database instance may serve as a counterexample, it may be too large or complex to read and understand where the inequivalence comes from. Therefore, in this paper, given a known counterexample D for Q₁ and Q₂, we aim to find the smallest counterexample D′ ⊆ D where Q₁(D′) ≠ Q₂(D′). The problem in general is NP-hard. We give a suite of algorithms for finding the smallest counterexample for different classes of queries, some more tractable than others. We also present an efficient provenance-based algorithm for SPJUD queries that uses a constraint solver, and extend it to more complex queries with aggregation, group-by, and nested queries. We perform extensive experiments indicating the effectiveness and scalability of our solution on student queries from an undergraduate database course and on queries from the TPC-H benchmark. We also report a user study from the course where we deployed our tool to help students with an assignment on relational algebra.

1. INTRODUCTION

Correctness of database queries is often validated by evaluating the queries with respect to a reference query and a reference database instance for testing. A primary application is in teaching students how to write SQL queries in database courses in academic institutions and evaluating their solutions. Typically, there is a test database instance D, and a correct query Q₁. The correctness of the query Q₂ submitted by a student is validated by checking whether Q₁(D) = Q₂(D). Assuming that Q₂ is at least syntactically correct and its output schema is compatible with that of Q₂ (which can be easily verified), if Q₂ does not solve the intended problem, then there will be at least one tuple in Q₁(D) and not in Q₂(D), or in Q₂(D) but not in Q₁(D). Another application scenario is when people rewrite complex SQL queries to obtain better performance. One approach for checking the correctness of complex rewritten queries is regression testing: execute the rewritten query Q₂ on test instances D to make sure that Q₂ returns the same results as the original query Q₁. Finding an answer tuple differentiating two queries and providing an explanation for its existence helps students or developers understand the error and fix their queries.

In both applications above, if the test database D is large–either because it is a large real data set or it is synthesized to be large enough to test scalability or ensure coverage of numerous corner cases–it would take much effort to understand where the inequivalence of two queries came from. Suppose a database course in a university uses the DBLP database [26] in an assignment on SQL or relational algebra (RA). The DBLP database has more than 5 million entries, and giving this entire database (or the outputs) to students as a counterexample to their query is not much effective. In practice, the mistakes in most of the queries can be explained with only a small number of tuples, which is much more useful as a counterexample for debugging.

Of course, one could generate a completely different counterexample D′ altogether, but using the test database instance D to help generate a counterexample has some distinct advantages. First, it helps to preserve the same context for users by using the same data values and relationships. Second, knowing that the original instance D is already a counterexample can help create the counterexample D′ more efficiently. This motivates the problem we study in this paper: given a reference database D, a reference query Q₁, and a test query Q₂ such that Q₁(D) ≠ Q₂(D), find a counterexample as a subinstance D′ ⊆ D such that Q₁(D′) ≠ Q₂(D′) and the size of D′ is minimized. We illustrate the setting with an example.

EXAMPLE 1.

Consider the following two relation schema storing information about students and course registrations in a university: Registration(name, course, dept, grade) and Student(name, major). In a database course, suppose the instructor asked the students to write a SQL query to find students who registered for exactly one Computer Science (CS) course. The test instances S, R of these two tables are given in Figure 1. The following query Q₁ solves this problem correctly:

Q1 : SELECT s.name, s . major
FROM Student s, Registration r
WHERE s.name = r.name AND r.dept = ‘ CS ’
EXCEPT
SELECT s. name, s. major
FROM Student s, Registration r1,
Registration r2
WHERE s.name = r1.name AND s. name =
   r2 . name AND r1.course <> r2 . course AND
   r1 . dept = ‘ CS ’ AND r2 . dept = ‘ CS ’

Figure 1:

Toy instances of tables in Example 1. Identifiers are shown for all tuples.

However, one student wrote Q₂, which actually finds students who registered for one or more CS courses.

Q2 : SELECT s.name, s . major
FROM Student s, Registration r
WHERE s.name = r.name AND r.dept = ‘ CS ’

The results of queries Q₁ and Q₂ are given in Figure 2. The tuples r₂ = (Mary, CS) and r₃ = (Jesse, CS) are in the output of Q₂ but not in the output of Q₁. To convince the student that his query is wrong, the instructor can provide the instances S, R as a counter example comprising 11 tuples. However, a smaller and better counterexample can simply contain three tuples (e.g., t₁, t₄, t₅) to illustrate the inequivalence of Q₁, Q₂. The benefit will be much larger if we consider a real enrollment database from a university, whereas the size of the counterexample would remain the same.

Figure 2:

Results of Q₁, Q₂ in Example 1

Prior work in the database community mainly focused on the theoretical study of decidability [13, 29] or generating a comprehensive set of test databases to “kill” as many erroneous queries as possible [11], but does not pay much attention to explaining why two queries are inequivalent. There are recent systems that aim to generate counterexamples for SQL queries. Cosette developed by Chu et al.[12] used formal methods that encodes SQL queries into logic formulas to generate a counterexample that proves two SQL queries are inequivalent. It generates counterexamples iteratively, so it must return the smallest one. XData by Chandra et al.[11] generates test data using mutation techniques. However, counterexamples generated by such systems can lead to arbitrary values, which may not be meaningful to the user. Our approach instead ensures that the user sees familiar values and relationships already present in the test database instances.

Our contributions.

We make the following contributions in this paper.

• We formally define the smallest counterexample problem, and connect it to data provenance with the definition of the smallest witness problem (Section 2).

• We give complexity results (NP-hardness proofs and poly-time algorithms) in terms of both data and combined complexity for different subclasses of SPJUDA queries (Section 3).

• We give practical algorithms for SPJUD queries using SAT and SMT solvers, and discuss a suite of optimizations to improve the efficiency (Section 4).

• For aggregate queries, we illustrate the new challenges, and propose new approaches to address these challenges, which includes applying provenance for aggregate queries [2], adapting the problem definition by parameterizing the queries, and rewriting the aggregate queries to reduce the number of tuples involved in the constraints to the SMT solver (Section 5).

• We describe our implementation of the end-to-end RATEST system, which has been deployed in an undergraduate course (Section 6).

• We give extensive experimental results in Section 7 to show how our approach can scale to large datasets (100K tuples for queries from the course and scale-1 for TPC-H queries). Also, we demonstrate that our optimizations reduce the size of the counterexample.

• We provide a large, thorough user study from the undergraduate database course, where we let students use RATEST to debug their RA queries in a homework. Quantitative analysis of usage statistics and homework scores shows that use of RATest improved student performance; anonymous survey of the students also indicates that they found RATEST helpful to their learning (Section 8).

2. PRELIMINARIES

We consider the class of Select (S)-Project (P)-Join (J)-Union(U)-Difference(D)-Aggregate(A) queries expressed as relational algebra (RA) expressions extended with aggregates. However, we will use RA form and SQL form of queries interchangeably. A subset of these operators using abbreviations will denote the corresponding subclass of such queries; e.g., PJ queries will denote queries involving only projection and join operations.

For a database instance D (involving one or more relational tables) and a query Q, Q(D) will denote the output of Q on D. Let Γ denote a set of integrity constraints on the schema of the database instance D. We consider the following standard integrity constraints: keys, foreign keys, not null, and functional dependencies. If D satisfies Γ, we write D |= Γ. We use |D| to denote the total number of tuples in D.

We will use unique identifiers to refer to the tuples in the database and query answers. In our example tables, they are written in the right-most column (see Figures 1 and 2), e.g., in Figure 1, t₁ refers to the tuple Student(Mary, CS).

2.1. Smallest Counterexample Problem

Consider two queries Q₁ and Q₂ such that Q₁(D) ≠ Q₂(D) on a database instance D such that D |= Γ for a given set of integrity constraints Γ. In other words, D explains why Q₁ and Q₂ are inequivalent. Based on D, we want to find a small counterexample D′ ⊆ D that also explains the inequivalence of Q₁ and Q₂.

DEFINITION 1 (COUNTEREXAMPLE AND THE SMALLEST COUNTEREXAMPLE PROBLEM).

Given a database instance D, a set of integrity constraints Γ s.t. D |= Γ, two queries Q₁, Q₂ where Q₁(D) ≠ Q₂(D), a counterexample is a subinstance D′ ⊆ D s.t. D′ = Γ and Q₁(D′) ≠ Q₂(D′). In particular, D is a trivial counterexample.

The goal of the smallest counterexample problem (SCP(D, Q₁, Q₂)) is to find a counterexample D′ ⊆ D such that the total number of tuples in D′ is minimized (i.e., for all counterexamples D” ⊆ D, |D”| ≥ |D′|).

In the above definition, we assume that the results of the two queries are union-compatible (i.e., Q₁(D), Q₂(D) have the same schema), which is easy to check syntactically (otherwise the difference in their schema serves as the reason of their inequivalence).

Note that keys, functional dependencies, and not null constraints are closed under subinstances, i.e., for such constraints Γ, if D |= Γ, then ∀D′ ⊆ D, D′ ⊨ Γ. Therefore, for such constraints, no additional consideration is needed. This is not true for referential constraints or foreign keys, which we explicitly consider in our algorithms. From now on, where it is clear from the context, we will implicitly assume that the D′ ⊆ D discussed as counterexamples satisfy the given constraints Γ.

EXAMPLE 2.

In Example 1 and Figure 1, the given test instances S and R of input relations Student and Registration already form a counterexample for Q₁ and Q₂. However, some subinstances of S, R are also counterexamples. Among these subinstances, S’ = {t₁}, R’ = {t₄, t₅}; or S” = {t₃}, R” = {t₉, t₁₀} are two smallest counterexamples (there are two other smallest counter examples varying the two courses of Jesse), i.e. there are no counterexamples with less than 3 tuples.

Our goal is to explain the query inequivalence to users by showing the smallest counterexample over which the two queries return different results. Even in our running example with a toy database instance, this reduced the number of tuples from 11 to only 3, whereas the benefit is likely to be much more for test database instances in practice as observed in our experiments. The brute-force method to find the smallest counterexample is to enumerate all subinstances of D, and search for the smallest subinstance D′ where Q₁(D′) and Q₂(D′) are different. However, enumerating all possible subinstances is inefficient and it does not utilize the information that D is already a counterexample. Therefore, to solve this problem more efficiently, we relate this problem to the concepts of witnesses and data provenance as discussed in the next two subsections.

2.2. Smallest Witness Problem

Buneman et al. [10] proposed the concept of witnesses to capture why-provenance of a query answer. Intuitively, a witness is a collection of input tuples that provides a proof for a given output tuple. Formally, given a database instance D, a query Q, and a tuple t ∈ Q(D), a witness for t w.r.t. Q and D is a subinstance D′ ⊆ D where t ∈ Q(D′). For instance, in Example 1, {t₁, t₄}, {t₁, t₅}, and {t₁, t₄, t₅} are three witnesses of the output tuple r₂ w.r.t. Q₂ and D. We use W(Q, D, t) to denote the set of all witnesses for t ∈ Q(D) w.r.t. Q and D.

In the smallest counterexample problem SCP(D, Q₁, Q₂), since Q₁(D) ≠ Q₂(D), there must exist a tuple t such that t ∈ Q₁(D)\Q₂(D), or, t ∈ Q₂(D)\Q₁(D). Since Q₁, Q₂ are assumed to be union-compatible, we can construct two queries Q′₁ = Q₁ − Q₂ and Q′₂ = Q₂ − Q₁. Therefore, for any counterexample D′ ⊆ D for Q₁ and Q₂, ∃t such that t ∈ Q′₁(D′), or, t ∈ Q′₂(D′). Given such an answer tuple t differentiating Q₁, Q₂, we say that D′ witnesses the tuple t in the result of Q′₁ or Q′₂.

A witness may contain many tuples and is sensitive to the query structure. Buneman et al. [10] defined minimal witness as a minimal element of W(Q, D, t), i.e., for a minimal witness w ∈ W(Q, D, t), there exist no other witnesses w′ ∈ W(Q, D, t) such that w′ ⊂ w. In Example 1, { t₁, t₄} and { t₁, t₅} are minimal witnesses of the output tuple r₂ w.r.t Q₂ and D, but { t₁, t₄, t₅} is not. In particular, a witness with the smallest cardinality must be a minimal witness.

DEFINITION 2 (SMALLEST WITNESS PROBLEM).

Given a database instance D, two union-compatible queries Q₁ and Q₂ s.t. Q₁(D) ≠ Q₂(D), and a tuple t s.t. t ∈ Q₁ (D) \ Q₂(D) or t ∈ Q₂(D) \ Q₁(D), the goal of the smallest witness problem (SWP(D, Q₁, Q₂, t)) is to find a witness w ∈ $\mathcal{W}$(Q₁ − Q₂, D, t) ∪ $\mathcal{W}$(Q₂ − Q₁, D, t) such that the total number of tuples in w is minimized.

We can reduce the smallest counterexample problem SCP(D, Q₁, Q₂) into the smallest witness problem SWP(D, Q₁, Q₂, t) by enumerating all possible output tuples in the difference of Q₁(D) and Q₂ (D), solving SWP(D, Q₁, Q₂, t), and finding the globally minimum witness across all such t-s.

$$\text{SCP}\left(D,Q_1,Q_2\right)=\underset{t\in \left(Q_1(D)\backslash Q_2(D)\right)\cup \left(Q_2(D)\backslash Q_1(D)\right)}{\min }\text{SWP}\left(D,Q_1,Q_2,t\right)$$

From now on, without loss of generality, we will assume that in the smallest counterexample problem SCP(D, Q₁ Q₂), there exists a tuple t ∈ Q₁(D) but t ∉ Q₂(D). In the rest of the paper, we will mainly focus on the smallest witness problem SWP(D, Q₁, Q₂, t) for such a tuple, primarily due to the fact that it provides more efficient solutions and allows optimizations compared to SCP. We further discuss the connection between SCP and SWP in Section 4 and in Section 7.

2.3. Boolean How-Provenance

Buneman et al.[10] formally introduced the why-provenance model that captures witnesses for a tuple t in the result of a query Q on a database instance D. However, it lacks an efficient method to compute the smallest witness from why-provenance. In order to compute the smallest witness efficiently for general SPJUD queries, we use the concept of how-provenance or lineage [1, 19]. How-provenance encodes how a given output tuple is derived from the given input tuples using a Boolean expression, and its first use can be traced back to Imilienski and Lipski [21] who used it to describe incomplete databases or c-tables. The computation of how-provenance of an output tuple t ∈ Q(D), denoted by Prv_Q(D)(t) or Prv(t) when clear from the context, is well known and intuitive: tuples in the given input relations are annotated with unique identifiers (as shown in the rightmost columns in Figure 1). As the query Q executes, for joint usages of sub-expressions (joins), their annotations are combined with conjunction (∧ or ·), and for alternative usages of sub-expressions (projections or unions), the annotations are combined with disjunction (V or +). For simplicity, we use + for disjunction, and omit symbols for conjunction. For instance, in Example 1, in Q₂(D),

$${\text{Prv}}_{Q_2(D)}\left(r_2\right)=t_1{t}_4+t_1{t}_5=t_1\left(t_4+t_5\right)={\varphi }_1(\text{say})$$ (1)

For set difference operation, consider R = R₁ − R₂, where all tuples in R₁, R₂ are annotated with how-provenance. If a tuple t appears in R, it must appear in R₁. Suppose Prv_R1(t) = ϕ. If t does not appear in R₂, Prv_R(t) = ϕ. If t does appear in R₂ with Prv_R2(t) = ψ, then Prv_R(t) = ϕ · $\stackrel{\_}{\psi }$, where $\stackrel{\_}{\psi }$ = |⌝ψ denotes the negation of the Boolean expression ψ. This implies t appears in the final results of R if t appears in R₁ but not in R₂.

Example 2.1.

In Example 1, consider the following RA expressions for Q₂ and Q₁, using abbreviations S and R for Students and Registration, where ⋈ denotes natural join (abusing the form of RA for simplicity).

$$Q_2={\pi }_{\text{name, major}}{\sigma }_{\text{dept}{=}^{\prime }C{S}^{\prime }}(\text{S}\bowtie \text{R})$$ (2)

Suppose Q₃ = π_name,majorσ_η(S ⋈ R r1 ⋈ R r2), where η denotes the selection condition: r1.dept =′ CS′ ∧ r2.dept =′ CS′ ∧ r1.course! = r2.course. Then Q₁ = Q₂ − Q₃. Consider the result tuple r₂ = (Mary, CS), which is in (Q₂ − Q₁)(D) (Figure 2). The provenance of r₂ = (Mary, CS) in Q₂(D) is given in Equation (1). It does not appear in Q₁(D) since it appears in both Q₂, Q₃ in (2). For Q₃, Prv_Q3(D)(r2) = t₁t₄t₅ = ϕ₂(say). Hence, Prv_Q1(D)(r₂) = ϕ₁ · ${\stackrel{\_}{\varphi }}_2$, and Prv_(Q2‒Ql)(D)(r₂) = ϕ₁ · $\stackrel{\_}{[{\varphi }_1·{\stackrel{\_}{\varphi }}_2]}$ = ϕ₁ · [${\stackrel{\_}{\varphi }}_1$ + ϕ₂] = ϕ₁ · ϕ₂ = (t₁(t₄ + t₅)) · (t₁t₄t₅) = t₁t₄t₅. In other words, the tuple (Mary, CS) can distinguish the queries Q₁, Q₂ in a small witness S′ = {t₁}, R′ = {t₄, t₅}, which solves both SWP and SCP problems.

For the above example, the smallest witness or the smallest counterexample could be found by inspection, since Q₁, Q₂ are similar. For arbitrary and more complex queries, how-provenance gives a systematic approach to find a small witness as we will discuss in the following two sections.

Aggregates.

In the next two sections, we discuss algorithms and complexity results for SPJUD queries. As we discuss in Section 5, aggregate queries entail new challenges, where we adapt the definitions of optimization problems accordingly and discuss solutions.

3. COMPLEXITY FOR SPJUD QUERIES

Table 1 summarizes the complexity of the smallest witness problem (SWP) for any subclass of SPJUD queries. In terms of complexity, we consider data complexity (fixed query size), query complexity (fixed data size), and combined complexity (in terms of both data and query size) [37]. Thus polynomial combined complexity indicates polynomial data complexity.

Table 1:

Complexity dichotomy of finding smallest witness for a result tuple w.r.t. the difference of two queries Q₁ − Q₂. The class JU* has the restriction that all unions appear after all joins. The class SPJUD* is defined as: Q → q⁺|Q − Q, where q⁺ is a terminal that represents SPJU queries. Proofs are given in Appendix A.

Query Class of Q1, Q2	Data Complexity	Combined Complexity
SJ	P (Thm. 1)	P (Thm. 1)
SPU	P (Thm. 2)	P (Thm. 2)
PJ	P (Thm. 6)	NP-hard (Thm. 3)
JU	P (Thm. 6)	NP-hard (Thm. 4)
JU*	P (Thm. 5)	P (Thm. 5)
SPJUD*	P (Thm. 7)	NP-hard if falls into class PJ or JU
PJD	NP-hard (Thm. 8)	NP-hard (Thm. 8)

For queries involving PJ, in general even the query evaluation problem is NP-hard in query complexity. However, we construct acyclic queries that can be evaluated in poly-time in combined complexity. It’s the same for queries involving JU, however, the problem is in poly-time for the subclass JU*, because we can directly look into the join-only parts of a JU* query. For general SPJU queries, the problem has polytime data complexity, and thus we can provide a poly-time algorithm for SPJUD* queries in data complexity.

What is noteworthy is that for the class of queries involving projection, join, and difference, it is already NP-hard in data complexity to find the smallest witness for a result tuple; and the result holds even when the queries are of bounded sizes and the database instance only contains two relations.

While in the complexity results, we assume both Q₁, Q₂ belong to the same query class, if t ∈ Q₁(D) \ Q₂(D), for all monotone cases the exact class of Q₂ does not matter as long as it is monotone.

4. A CONSTRAINT-BASED GENERAL SOLUTION FOR SPJUD QUERIES

In the previous section, we showed that for a number of query classes, the smallest witness problem is poly-time solvable in data complexity. However, the problem is still NP-hard in general, even when the queries are of bounded size; further, the poly-time algorithms we discussed are not efficient for practical purposes. To address these challenges, we introduce a constraint-based approach to the smallest witness problem. We map the problem into the min-ones satisfiability problem [25] by tracking the Boolean provenance of output tuples. The min-ones satisfiability problem is an extension of the classic Satisfiability (SAT) problem: given a Boolean formula ϕ, it checks whether ϕ is satisfiable with at most k variables set to true. This problem can be solved by either using a SAT solver (e.g., MiniSAT[36], and CaDiCaL[8]), or an SMT Solver (e.g., CVC4[5]and Z3[15]). Satisfiability Modulo Theories (SMT) is a form of constraint satisfaction problem. It refers to the problem of determining whether a first-order formula is satisfiable w.r.t. other background first-order formulas, and is a generalization of the SAT problem[7]). SAT and SMT problems are known to be NP-hard with respect to the number of clauses, constraints, and undetermined variables. However, there is a variety of solvers that work very well in practice for different real world applications, and with these solvers we can find a small solution to a SWP instance. The rest of this section will describe how to encode the how-provenance of an output tuple, and then use a state-of-the-art solver to find the smallest witness for the output tuple. The implementation details will be discussed in Section 6.

4.1. Passing How-Provenance to a Solver

As discussed in Section 2.3, the how-provenance Prv(t) is true ⇔ tuple t is in the query result. Since Prv(t) is composed of a combination of Boolean variables annotating tuples in the input relations, a Boolean variable is true ⇔ the corresponding tuple is present in the input relation in the witness. Then an instance of the smallest witness problem is mapped to an instance of the min-ones satisfiability problem: find a satisfying model to Prv(t) with least number of variables set to true, and the variables set to true in the satisfying model indicate tuples in the smallest witness. The pseudocode of the algorithm to solve SCP and SWP is given in Algorithm 1.

Example 3 illustrates how we can get the smallest witness using a SAT solver. Since the solver will return an arbitrary satisfying model, to get the minimum model we need to ask the solver to return a different model every time we rerun it (line 6). We set a maximum number of runs to limit the running time, and the algorithm stops when there is no more satisfying models or it has reached the maximum number of runs. It may not find the minimum model when it stops, but it is likely to find one that is small if given enough time.

EXAMPLE 3 (HOW-PROVENANCE AND SAT SOLVER). Consider Example 1.

(Mary, CS) and (Jesse, CS) is in the result of Q₂ but not in the result of Q₁, and the how-provenance for them w.r.t. Q₂ − Q₁ and D can be computed based on these results. E.g., Prv_(Q2−Q1)(D)(Jesse, CS) = ${\text{Prv}}_{Q_2(D)}(Jesse,CS)\wedge {\text{Prv}}_{Q_1(D)}(Jesse,CS)=$(t₃(t₉ + t₁₀ + t₁₁)) $\stackrel{\_}{(t_3{t}_9+t_3{t}_{10}+t_3{t}_{11})}$ $\stackrel{\_}{\stackrel{\_}{t_3{t}_9{t}_{10}+t_3{t}_9{t}_{11}+t_3{t}_{10}{t}_{11}}}$ = t₃t₉t₁₀ + t₃t₉t₁₁ + t₃t₁₀t₁₁. Then we can get a model {t₃ : True, t₉ : True, t₁₀ : True, t₁₁ : True} to Prv_(Q2−Q1(D)(Jesse, CS) bypassing it to a SAT solver. We will get any of {t₃, t₉, t₁₀}, {t₃, t₉, t₁₁}, and {t₃, t₁₀, t₁₁} as the smallest witness after running the solver for multiple times (in the first run, it may return a bigger solution like {t₃, t₉, t₁₀, t₁₁}).

4.2. Optimizing the Basic Approach

The basic algorithm given in Algorithm 1 has two limitations: (a) it cannot find the smallest witness until it searches all possible models that satisfy Prv(t); (b) In order to solve SCP, it iterates over all tuples in Q₁(D) \ Q₂(D) and calculates the provenance for each tuple, which leads to large overheads. Therefore, we propose two optimizations. The first one is to pick only one tuple t from the query results of Q₁(D) \ Q₂(D) (i.e., we only solve SWP), and only compute the provenance of t by adding an additional selection operator to select tuples equal to t on top of the query tree of Q₁ - Q₂. The other optimization is to treat this problem as an optimization problem instead of finding different models with a SAT or SMT solver. However, integer linear programming solvers can not be applied because transforming how-provenance into linear constraints can be exponential. To solve this problem, we use optimizing SMT solvers that are now available with recent advances in the programming languages and verification research community [9, 27]. Given a formula ϕ and an objective function $\mathcal{F}$, an optimizing SMT Solver finds a satisfying assignment of ϕ that maximizes or minimizes the value of $\mathcal{F}$.

Algorithm 1.

Basic: The SAT-solver-based approach

SMALLEST-WITNESS-BASIC(Prv(t), Δ)
1	ϕ = Prv(t)
2	η* = null
3	δ = 0
4	while ϕ is satisfiable and δ < Δ
5	Use a SAT solver to find a model η for ϕ
6	ϕ = ϕ ∧ ⌝η
7	if η* is null or # true variables in η is less than η*
8	η* = η
9	δ = δ + 1
10	Return a set of tuples Dη* = {t′ | η*(t′) is true}
SMALLEST-COUNTETEXAMPLE-BASIC(Q1, Q2, D, Δ)
1	D* = D
2	Δ is the maximum number of trials.
3	for t ∈ (Q1 − Q2)(D)
4	Prv(t) = the how-provenance of tuple t w.r.t. (Q1 − Q2)(D).
5	D′ = SMALLEST-WITNESS-BASIC(Prv (t), Δ)
6	if |D′| < |D*|
7	D* = D′
8	Return D*

Algorithm 2.

Opt_σ: The optimized algorithm with selection pushdown

SMALLEST-COUNTEREXAMPLE-OPTIMIZED(Q1, Q2, D)
1	Pick one tuple t in the result of Q1 (D) \ Q2(D),
2	A1...Ak = the attributes of t.
3	Q′ = σA1 = t.A1, A2 = t.A2,...,Ak = t.Ak (Q1 − Q2)
4	ϕ = the how-provenance of tuple t w.r.t. Q ′(D).
5	obj = the number of true values in ϕ
6	η = OptSMT_Solver(ϕ, obj)
7	Return a set of tuples Dη = {t′ | η(t′) is true}

Algorithm 2 describes the solution with these two optimizations. A selection operator on the value of t is added to Q₁ − Q₂ (line 2–3). Again, we add Prv(t) as the constraint of the optimizing SMT solver, set the number of true variables as the objective function, and get the optimal model (line 4–6). Our SMT formulation includes only Boolean variables, so we encode the number of true variables by first converting the variables into 0 or 1 and then summing them up.

The SQL query optimizer is likely to push down the additional selection operator to accelerate the computation of how-provenance. Moreover, since a how-provenance may involve many tuples, solving it with an optimizer will reduce the solving time significantly, since the optimizer will return an answer as soon as it finds a solution, but the naive algorithm requires enumerating all possible models to obtain the model with least number of variables set to true.

1 (declare-const t1 Bool)
2 ...
3 (declare-const t11 Bool)
4 (define-fun b2i ((× Bool)) Int (ite × 1 θ))
5 (assert (and (or t4 t5) (not (and (or (and t1 t4) (and t1
        t5)) (not (and t1 (and t4 t5)))))))
6 (minimize (+ (b2i t1) (b2i t2) ... (b2i t11)))

Listing 1: SMT-LIB Input for Example 3

The above listing illustrates how we encode the provenance and constraints into the SMT-LIB standard format [6] as the input to a SMT solver to find the satisfying model for Example 3. In the sample SMT-LIB format input above, first we defined Boolean variables for each tuple from line 1 to line 4, then at line 4 we defined function b2i to convert Boolean variables for each tuple into 0 and 1. At line 5 we added the how-provenance as a constraint. Then with function b2i we take the sum of 0–1 variables to get the number of true variables in the model, and set the sum as the objective function (line 6).

4.3. Handling Database Constraints

Since we output a subinstance of the input database instance as the witness, database constraints like keys, not null, and functional dependencies are trivially satisfied if the input instance is valid. On the other hand, foreign key constraints can be naturally represented as Boolean formulas like provenance expressions. For instance, in our running example in Figure 1, the name column in the Registration table may refer to the name column in the Student table. So, if we want to keep any tuple in the Registration table, we must also keep the tuple with the same name value in the Student table. This constraint can be expressed in the a ⇒ b form, e.g., $t_1+\overline{t_4},t_2+\overline{t_7},\dots ,$ etc., corresponding to the constraint that the tuples in the Registration table cannot exist unless the tuple it refers to exists in the Student table). Then, for each tuple that appears in the provenance expression added to the SAT or SMT solver, we add its foreign key constraint expression to the solver as a constraint.

5. AGGREGATE QUERIES

So far, we have focused on SPJUD queries. In this section we extend our discussion to aggregate queries. First we will demonstrate the challenges that arise for aggregate queries, and then propose our solutions to overcome them. We make some assumptions on the form of aggregate queries: (1) no aggregate values or NULL values are allowed in the group by attributes; (2) selection predicates involving aggregate values (HAVING) are in the simple form expr relop expr; (3) there is no difference operation above an aggregate operator.

5.1. Challenges with Aggregate Queries

Witness is too strict.

Remember that for SPJUD queries, we find the smallest counterexample by first picking an output tuple in (Q₁ − Q₂)(D) and then finding the smallest witness for this tuple w.r.t. Q₁ − Q₂ and D. However, for aggregate queries, if we still look for witnesses for output tuples, it is likely that we are unable to find any witnesses smaller than the input database instance — the aggregate value may change if any tuple is removed from the input. Following Example 4 illustrates this issue.

EXAMPLE 4 (CHALLENGE WITH WITNESS FOR AGGREGATE VALUES).

Suppose we have two aggregate queries Q₁ and Q₂ aimed at computing the average grade of students in CS courses, using the two tables in Figure 1.

Q1 :SELECT s.name, avg(r.grade) as avg_grade
FROM Student s, Registration r
WHERE s.name=r.name AND r.dept=‘CS’
GROUP BY s.name
Q2 :SELECT s.name, avg(r.grade) as avg_grade
FROM Student s, Registration r
WHERE s.name=r.name
GROUP BY s. name

Result of Q₁(D):

name	avg_grade
Mary	87.5
John	90
Jesse	92.5

Result of Q₂(D):

name	avg_grade
Mary	90
John	89
Jesse	92.5

In this example, Q₂ forgets to select on departments. To find a counterexample through finding a witness, we have to keep all records of the student as the witness. E.g., we can only return all Mary’s registration records as the witness W to keep (Mary, 90) in Q₂(W) but not in Q₁ (W). However, to show that Q₁ will return a different result from Q₂ over some counterexample C, C can contain only one tuple (Mary, 208D, ECON, 88), and Q₁(C) is empty while Q₂(C) returns (Mary, 88).

Computation overhead by how-provenance.

We cannot directly apply Basic or Opt_σ (Section 4) for aggregate queries because: (i) the why-provenance model does not consider aggregate queries; (ii) while how-provenance can be extended to support aggregate queries by storing all possible combinations of grouping tuples [34], it leads to exponential overhead and thus is impractical if there exist large groups.

Selection predicates with aggregate values.

When the queries contain selection predicates with aggregate functions COUNT or SUM, it is possible that we have to keep all tuples in one group to make the result tuple satisfy the selection predicates. See Example 5.

EXAMPLE 5 (CHALLENGE WITH SELECTION ON AGGREGATE VALUES).

Continued with Example 4, but both queries are extended to find the average grade of CS courses of students who registered at least 3 CS courses.

Q1 :SELECT s.name, AVG(r.grade) as avg_grade
FROM Student s, Registration r
WHERE s.name=r.name AND r.dept=‘CS’
GROUP BY s. name
HAVING COUNT(r.course)>=3
Q2 :SELECT s.name, AVG(r.grade) as avg_grade
FROM Student s, Registration r
WHERE s.name=r.name
GROUP BY s. name
HAVING COUNT(r.course)>=3

Result of Q₁(D):

name	avg_grade
Jesse	90

Result of Q₂(D):

name	avg_grade
Mary	90
Jesse	90

Again, Q₂ returns (Mary, 90) that should not be in the correct result, because it does not select on departments. And we have to return all three courses Mary registered to make (Mary, 90) still in the result of Q₂ but not in the result of Q₁. Therefore, when the selection predicate involves the comparison between count or sum with a large constant number, we have to return a large fraction of tuples in the test database instance in order to make the output tuple satisfy the selection predicate.

5.2. Applying Provenance for Aggregates

To address the first two challenge in Section 5.1 (the third challenge is discussed in Section 5.3), we consider applying provenance for aggregated queries by Amsterdamer et al.[2]. Their approach annotates the provenance information with the individual values within tuple using commutative monoid (for aggregate) and commutative semirings (for annotation). The tuples in the input relations are regarded as symbolic variables and thus the aggregate values can be encoded as symbolic expressions. The selection predicates that involve aggregate values can be encoded as symbolic logical expressions. Then we can express Q₁(D′) ≠ Q₂(D′) using symbolic inequality expressions: assert that a group only exists in one of the query results, or the group exists in both query results but the aggregate values are different. Table 2 shows the provenance of aggregate queries for Example 5. For instance, t₄ ⊗ 100 +_AVG t₅ ⊗ 75 represents the AVG value of a group containing two tuples t₄ and t₅ in the original query result, and the value of the attribute in the AVG function of tuple t₄ if 100, and the value is 75 for t₅. If t₄ is removed from the input relations, then t₄ ⊗ 100 will not contribute to the AVG value. Like the how-provenance, (t₁ (t₄ + t₅)) (t₄ ⊗ 1 +sum t₅ ⊗ 1 ≥ 3) indicates how the result tuple is derived from the input or intermediate tuples: t₁(t₄ + t₅) means that the group exists iff t₁ exists and one of t₄ and t₅ exists; t₄ ⊗ 1 + _SUM t₅ ⊗ 1 ≥ 3 represents the selection criterion: the COUNT (a special case of SUM) value should be greater or equal to 3. Based on these provenance expressions, a counterexample for Q₁ and Q₂ w.r.t. tuple (Mary, 90) can be given by solving the constraint (prv₄ ⊕ prv₁) V (val₄ ≠ val₁), and we can iterate over all output tuples to find the smallest counterexample, instead of finding a global smallest witness of tuples in Q₁(D) \ Q₂(D).

Table 2:

Provenance for Aggregate Queries in Example 5

Q1
name	avg_grade		provenance
Mary John Jesse	t4 ⊗ 100 +AVG t5 ⊗ 75 t7 ⊗ 90 t9 ⊗ 95 +AVG t10 ⊗ 90	val1 val2 val3	(t1(t4 + t5)) (t4 ⊗ 1 +SUM t5 ⊗ 1 ≥ 3) (t2t7) (t7 ⊗ 1 ≥ 3) ((t3 (t9 + t10)) (t9 ⊗ 1 +SUM t10 ⊗ 1 +SUM t11 ⊗ 1 ≥ 3)	prv1 prv2 prv3
Q2
name	avg_grade		provenance
Mary John Jesse	t4 ⊗ 100 +AVG t5 ⊗ 75 +AVG t6 ⊗ 95 t7 ⊗ 90 +AVG t ⊗ 88 t9 ⊗ 95 +AVG t10 ⊗ 90	val4 val5 val6	(t1(t4 + t5 + t6)) (t4 ⊗ 1 +SUM t5 ⊗ 1 +SUM t6 ⊗ 1 ≥ 3) (t2(t7 + t8)) (t7 ⊗ 1 +SUM t8 ⊗ 1 ≥ 3) (t3(t9 + t10 + t11)) (t9 ⊗ 1 +SUM t10 ⊗ 1 +SUM t11 ⊗ 1 ≥ 3)	prv4 prv5 prv6

5.3. Optimizations

The provenance-based approach can be optimized further.

5.3.1. Parameterizing the Queries.

To address the third challenge, when the queries involve comparisons on aggregate values with constant numbers, we modify the definition of our problem by parameterizing the queries. We replace the constants in selection predicates with symbolic variables when passing the provenance expressions to the solver. Then we are expected to get a smaller counterexample with different constant values in the selection predicates, compared to the one under the original parameter settings.

DEFINITION 3 (SMALLEST PARAMETERIZED COUNTEREXAM-PLE PROBLEM).

Given two parameterized queries Q₁ and Q₂, and a parameter setting λ and a database instance D, where Q₁(λ, D) ≠ Q₂(λ, D), the smallest parameterized counterexample problem (SPCP) is to find a parameter setting λ′ and a subinstance D′ of D, such that Q₁(λ′, D′) ≠ Q₂(λ′, D′), and the total number of tuples in D′ is minimized.

EXAMPLE 6 (SMALLEST PARAMETERIZED COUNTEREXAMPLE).

Here we show an example of parameterized queries based on Example 5 by making the number of CS courses in the queries a parameter.

Q1 : SELECT s.name, AVG(r.grade) as avg_grade
FROM Student s, Registration r
WHERE s.name=r.name AND r.dept=‘CS’
GROUP BY s.name
HAVING COUNT(r.course)>= @numCS
Q2 : SELECT s.name, AVG(r.grade) as avg_grade
FROM Student s, Registration r
WHERE s.name=r.name
GROUP BY s. name
HAVING COUNT(r.course)>=@numCS

These two queries return:

Q₁(num_CS = 3, D):

name	avg_grade
Jesse	90

Q₂(num_CS = 3, D):

name	avg_grade
Mary	90
Jesse	90

By using a different parameter setting, the size of counterexample can be reduced. When @num_cs = 3, the smallest counterexample C is t₁, t₄, t₅, t₆. But if @num_cs = 1, we only need to return t₁, t₆.

Below is an example illustrating how to encode the provenance for aggregate queries to SMT formulas.

1 (declare-const t1 Bool)
2 ...
3 (declare-const t11 Bool)
4 (declare-const num_CS Int)
5 (define-fun b2i ((× Bool)) Int (ite × 1 0))
6 (assert
7   (or
8     (distinct
9       (and
10	  (and t1 (or t4 t5))
11	  (>= (+ (b2i t4) (b2i t5)) num_CS))
12	(and
13	  (and t1 (or t4 t5 t6))
14	  (>= (+ (b2i t4) (b2i t5) (b2i t6)) num_CS)))
15    (not
16	(=
17	  (\ (+ (* (b2i t4) 100) (* (b2i t5) 75)) (+ (b2i
	       t4) (b2i t5)))
18	  (\ (+ (* (b2i t4) 100) (* (b2i t5) 75) (* (b2i t6
	       ) 95)) (+ (b2i t4) (b2i t5) (b2i t6)))
19 ))))
20 (minimize (+ (b2i t1) (b2i t2) ... (b2i t11)))

5.3.2. A Heuristic Approach.

The provenance-based solution may not scale very well when a group contains too many tuples and thus the SMT formulas involve too many variables, even if we choose the group with the least number of tuples. Assume that the aggregate functions and attributes are the same in two queries, to reduce the number of variables in SMT formulas, we decide to look into the different tuples between two groups. E.g., for simplicity, assume that both Q₁ and Q₂ are in the form of ${\gamma }_{{\text{G}}_1,{\text{agg}}_1\left({\text{A}}_1\right),{\text{agg}}_2\left({\text{A}}_2\right),\dots ,{\text{agg}}_{\text{k}}\left({\text{A}}_{\text{k}}\right)}\left(Q_1^{\prime }(D)\right)\text{and}{\gamma }_{{\text{G}}_2,{\text{agg}}_1\left({\text{A}}_1\right),{\text{agg}}_2\left({\text{A}}_2\right),\dots ,{\text{agg}}_{\text{k}}\left({\text{A}}_{\text{k}}\right)}\left(Q_2^{\prime }(D)\right)$ (the aggregations are done at last), one of the following two cases must be true: (i) the group in Q₁(D) that generates t does not exist in Q₂(D) (grouping attributes G₁ may or may not be equal to G₂); (ii) the group in Q₁ that generates t also exists in Q2, but one of the aggregate values are different. In either case, we can directly compare the result of $Q_1^{\prime }(D)\text{and}{Q}_2^{\prime }(D)$ and find at least one tuple that exists in only one of them. The following example illustrates how this method works. Note that $Q_1^{\prime }\text{and}{Q}_2^{\prime }$ can include nested aggregate queries, as long as there are no aggregate values in their schema — aggregate values can be involved in selections or joins.

EXAMPLE 7 (HEURISTIC APPROACH ON EXAMPLE 4).

$Q_1^{\prime }$:

SELECT s.name, r.grade
FROM Student s, Registration r
WHERE s.name = r.name AND r.dept=‘CS’

Algorithm 3.

Agg_opt : The heuristic algorithm for aggregate queries

SMALLEST-COUNTEREXAMPLE-AGGREGATE-HEU(Q1, Q2, D, Λ)
1	Λ = {λ1,...} is the original parameter setting
2	Q1 = ${\sigma }_{ag{g}_1\left(A_1\right)op{\lambda }_1{\gamma }_{{\text{G}}_1},{\text{agg}}_1\left({\text{A}}_1\right),{\text{agg}}_2\left({\text{A}}_2\right),\dots ,{\text{agg}}_{\text{k}}\left({\text{A}}_{\text{k}}\right)}\left(Q_1^{\prime }(D)\right)$,
3	$Q_2={\sigma }_{ag{g}_1\left(A_1\right)}{}_{op}{}_{{\lambda }_1{\gamma }_{{\text{G}}_2},{\text{agg}}_1\left({\text{A}}_1\right),{\text{agg}}_2\left({\text{A}}_2\right),\dots ,{\text{agg}}_{\text{k}}\left({\text{A}}_{\text{k}}\right)}\left(Q_2^{\prime }(D)\right)$
4	repeat
5	Pick one tuple t in the result of $Q_1^{\prime }(D)\backslash Q_2^{\prime }(D)$,
6	$A_1^{\prime }\dots A_k^{\prime }$ = the attributes of t
7	$Q^{\prime }={\sigma }_{A_1^{\prime }=t.A_1^{\prime },A_2^{\prime }=t.A_2^{\prime },\dots ,A_k^{\prime }=t.A_k^{\prime }}\left(Q_1^{\prime }-Q_2^{\prime }\right)$
8	ϕ = the prv. for agg. queries of tuple t w.r.t. Q′(D)
9	obj = #true_values in ϕ
10	η = OptSMT_Solver(ϕ, obj)
11	Dη = {t′ | η(t′) is true}
12	Set Λ′ according to values in Dη
13	until Q1(Dη, Λ′) ≠ Q2(Dη, Λ′)
14	Return Dη

Q2: SELECT s.name, r. grade
FROM Student s, Registration r
WHERE s. name =r. name

$Q_2^{\prime }$(D):

name	grade		name	grade
Mary	100		Jesse	95
Mary	75		Jesse	90
John	90		Jesse	85

$Q_2^{\prime }$(D):

name	grade		name	grade
Mary	100		John	88
Mary	95		Jesse	95
Mary	75		Jesse	90
John	90		Jesse	85

$Q_2^{\prime }$ does not select on departments so it returns some additional tuples comparing to $Q_1^{\prime }$ (Mary, 95) and (John, 88). And now we can apply the method for SPJUD queries in the previous section and then return either {t₁, t₆} or {t₂, t₈} as the counterexample ‒ they can explain why the aggregate value on Mary or John are different in Q₁ and Q₂

When the queries involve comparisons on aggregate values at the top of the query tree, e.g., ${\sigma }_{ag{g}_1\left(A_1\right)}{}_{opconst}{}_{{\gamma }_{{\text{G}}^1},{\text{agg}}_1\left({\text{A}}_1\right),{\text{agg}}_2\left({\text{A}}_2\right),\dots ,{\text{agg}}_{\text{k}}\left({\text{A}}_{\text{k}}\right)}\left(Q_1^{\prime }(D)\right)$, we can also apply the heuristic approach by parameterizing the queries and directly looking into $Q_1^{\prime }$ and $Q_2^{\prime }$. After finding the smallest counterexample C for $Q_1^{\prime }$ and $Q_2^{\prime }$, the next step is to make sure $Q_1(C)\ne Q_2(C)$ otherwise it fails to distinguish the original queries. On one hand, if aggregate values are involved in the selection predicate at the top of the query tree, we parameterize the original queries and set a reasonable number such that the results of at least one of $Q_1^{\prime }(C)$ and $Q_2^{\prime }(C)$ will pass the selection. For COUNT we set the parameter in the predicate to be 1 or 0 if the operator is ‘=’ or ‘>’, while for SUM we set the parameter to be the maximum value or the minimum value of the attribute in the aggregate function. And it is similar for MAX, MIN, and AVG. On the other hand, if both Q₁(C) and Q₂(C) are not empty, their results may happen to be the same since we do not add any constraints on the aggregate values — the only guarantee is $Q_1^{\prime }(C)\ne Q_2^{\prime }(C)$. Therefore we have to evaluate the queries on the counterexample we find, and if Q₁ (C) = Q₂(C), we re-run the SMT Solver on the same formulas but asking the solver to return a different model until we get a satisfying counterexample.

6. IMPLEMENTATION

Provenance has been extensively studied in the database community, not only theoretically [2, 10, 19], but also there are systems that can capture different forms of provenance [4,17, 18, 24, 31, 35]. However, to the best of our knowledge, there are no systems available that support how-provenance for general SPJUD and aggregate queries. Since building a comprehensive system to efficiently capture provenance is not the goal of this paper, for simplicity, we implemented our system, called RATest, in Python 3.6 with a relational algebra interpreter [3]. This interpreter translates relational algebra queries into SQL common table expression (CTE) queries, and each relational algebra operator is translated into a SQL subquery. RATest has a web UI built using HTML, CSS, and JavaScript. It runs on Ubuntu 16.04 and uses Microsoft SQLServer 2017 as the underlying DBMS.

First, RATest takes two queries in Relational Algebra as input. Then the relational algebra interpreter interprets them and generates two SQL queries consisting of multiple subqueries. Next, it rewrites each subquery by adding one additional column of provenance expression. For aggregate queries, all columns of aggregate values are also rewritten to symbolic expressions. These expressions are stored as strings in the SMT-LIB format. For each input relation, we added one additional ‘prv’ column of tuple identifiers. The rewriting rules are listed below by the relational algebra operator:

Select, Project, Union.

For selection/projection/union, we directly select the prv column from the input relation. If the selection predicate involves aggregate values (i.e., HAVING), we construct a symbolic logical expression with the operands, and take the conjunction of the prv column and the symbolic logical expression. Duplicates from projection/union will be considered in de-duplicate discussed below.

Join.

We take the AND (∧) of the prv column of the two joining tuples.

Difference.

Remember that for difference operator, there are two cases while evaluating R − S: (1) t € R, t € S. (2) t € R, t ∉ S. In the first case, the query is transformed into a join query where the join predicate is that the tuple in R should equal to the tuple in S (excluding the prv column); In the second case, we add a ‘NOT EXISTS’ subquery to find those tuples in R but not in S, and the prv column is the same as those in R; then we take a union of these two cases.

SELECT R.A, R.B FROM R EXCEPT SELECT S.A, S.B FROM S
is rewritten to:
(SELECT R.A, R.B,
      ‘ (and⎵’ || R.prv || ‘ ⎵(not⎵’ || S.prv || ‘ ))’
FROM R, S WHERE R.A = S.A AND R.B = S.B) UNION
(SELECT R.A, R.B, R. prv
FROM R WHERE NOT EXISTS(
     SELECT S.A, S. B
     FROM S
     WHERE S.A = R.A AND S.B = R.B))

De-duplicate.

Duplicate tuples may arise from projection or union. We add a group by clause that contains all columns in the select clause except the prv column. The prv column is computed using ‘string_agg’ function:

‘(or⎵’ || stri ng_agg(R.prv, ‘⎵’) || ‘)’

Once the queries are rewritten, RATest applies the algorithms in Section 4 and Section 5 according to their query classes, to generate SMT constraints. Then, RATest passes the constraints to the Z3 SMT Solver (an efficient optimizing SMT Solver by Microsoft Research)[9, 15] 4.7.1, and sets “minimizing the number of variables set to true” as the objective function. Finally, the satisfying model returned by the Solver represents the counterexample, and the counterexample is shown on the web UI with the query results of two input queries over this counterexample.

7. EXPERIMENTS

We present experiments to evaluate our algorithms in this section. The input queries used in our initial experiment for SPJUD queries were collected from student submissions to a relational algebra assignment in an undergraduate database course in a US university in Fall 2017; therefore, the wrong queries were “real,” although test database instances are synthetic. In the experiment for aggregate queries, we use the TPC-H benchmark[14]. We generate tables at scale 1, and manually translated several TPC-H queries into Relational Algebra and created some wrong queries ourselves. The system runs locally on a 64-bit Ubuntu 16.04 LTS with 3.60 GHz Intel Core i7–4790 CPU and 16 GB 1600 MHz DDR3 RAM.

7.1. Real World SPJUD Queries

In this subsection we evaluate the efficacy of our algorithms in Section 4 for SPJUD queries on the course dataset. The dataset comes from one relational algebra assignment in Fall 2017, which asked students to write SPJUD queries using the relational algebra interpreter. It includes 8 questions and 141 students in total, and the queries are evaluated over the test instances we generated. The test instance may not be able to differentiate all incorrect queries, although there are more incorrect queries discovered when the test instance gets larger (see Table 3). Some questions involve very complex queries to find tuples satisfying conditions with universal quantification or uniqueness quantification requiring multiple uses of difference (see Section 8 for concrete examples), and solicited some extremely complex student solutions with scores of operators; we are not aware of any directly related work that is able to handle this level of query complexity. We had to drop two overly complicated student queries that involved massive cross products.

Table 3:

|D| vs. # of wrong queries discovered

# of Tuples in DB	# of incorrect queries	# of students with incorrect queries
1,000	111	76
4,000	167	87
10,000	168	88
40,000	169	88
100,000	170	88

SCP vs. SWP. As discussed in Section 2, a poly-time solution for SWP also gives a poly-time solution for SCP if we consider data complexity, since we can iterate over all tuples t in Q₁(D)\Q₂(D) to find the global optimal solution. The number of output tuples is polynomial in |D|, but can be exponential in query size (e.g., when we join k tables that form a cross product), and therefore it does not necessarily give a poly-time solution in terms of combined complexity. However, the standard practice is to consider data complexity, since the size of the query is expected to be a small constant. For practical purposes, even polynomial combined complexity may not give interactive performance. Here we experimented on the algorithms for SPJUD queries in Section 4 to compare SCP and SWP in practice: The Basic algorithm using Z3 SMT optimizer instead of a SAT solver and the Opt_σ algorithm. See Table 4. Surprisingly, all smallest witnesses returned by Opt_σ are of the same size as the smallest counterexamples returned by Basic, i.e., Basic reaches the global optimum on the first output tuple. This may be a coincidence, but for 168 of 170 wrong queries we discovered, the size of the smallest witnesses of all output tuples are the same. The result indicates that in most cases, we can use Opt_σ for better performance (6.9x faster) with only a small probability of not reaching the global minimum. Given this result, in the rest of this section, we will only experiment on SWP.

Table 4:

SCP vs. SWP, # tuples = 100k

	Mean Runtime (sec.)	Mean Size of Counterexample
SCP— BASIC	26.29	3.52
SWP— Optσ	3.80	3.52

Size of the data vs. time.

We vary the number of tuples in the input relations of 1,000: 4,000: 10,000: 40,000: 100,000. See figure 4: raw is for evaluating queries Q₁ - Q₂, the difference of students’ query and the standard query; prov-all is for evaluating rewritten queries Q₁ ‒ Q₂ that also store provenance; prov-sp is for provenance queries with selection on one tuple; solver-naive-128 is for finding the smallest witness with an SAT solver that tries at most 128 different models; solver-opt is for finding the smallest witness of the first result tuple with Z3 SMT optimizer; solver-opt-all is for finding the smallest witness of all result tuples with Z3 SMT optimizer. The running time of rewritten provenance queries with selection pushdown is much faster than the raw queries (29× when |D| = 100K) and the provenance query without selection on tuples (42× when |D| = 100K). This is what we expect: computing provenance expression will cause huge overheads, but only for one single tuple is definitely affordable.

Figure 4:

Average Running Time of Each Component.

Query complexity vs. time.

Figure 3 shows the running time of each component of Opt_σ vs. different metrics of the query complexity (number of operators, number of differences, and height of the query tree). The running time increases roughly as the complexity of queries increases. Note that when height of the query or the number of operators in the query reaches the maximum, the provenance query dominates the running time, however, in most cases, evaluating the raw CTE SQL query is the slowest part.

Figure 3:

Query complexity vs. time, algorithm=Opt_σ, #tuples=100k; raw is for evaluating queries Q₁ − Q₂; prov-sp is for provenance queries with selection on one tuple; solver-opt is for finding the smallest witness with Z3 SMT optimizer; total is the total running time of Opt_σ.

Solver strategy vs. witness size.

Since our goal is to find the smallest witness, the metric for evaluating the quality of the witnesses as explanations is the size. We experiment different constraint-solving strategies: Naive-* is to use the Z3 SMT solver to get satisfying models to the Boolean formula of how-provenance, until there are no more satisfying models or it finishes enumerating M different models (we chose M=128); Opt is to use Z3 SMT optimizer to directly find the model with least number of variables set to true. Naive-* is not satisfying because there is no guarantee on the model size it finds. Figure 5 summarizes the results. Since the SAT solver used by Naive-* can return an arbitrary model every time, we repeat each experiment with Naive-* 10 times and report the average minimum witness size found among the 10 repetitions. Opt always return a smaller witness compared to Naive-*, while the runtime overhead compared to even Naive-1 is negligible. Of course, performance of these approaches heavily depends on the solver implementation; a comprehensive evaluation would be beyond the scope of this paper. Here, we simply observe that our implementation of Opt provides good performance and solution quality in practice, as it cannot be easily beaten by simply enumerating a number of models.

Figure 5:

(a) Witness size vs. solver strategies and Queries, #tuples=100K; (b) Witness size vs. solver strategies and runtime, #tuples = 100K

7.2. Synthetic Aggregate Queries

We experimented on the TPC-H benchmark database generated at scale 1 on queries Q4, Q16, Q18, Q21, and a modified Q21-S with an additional selection on aggregate value at the top of the query tree. We choose these queries because they do not involve arithmetic operations on aggregate functions. First we dropped the ORDER BY operator and rewrote these queries using the relational algebra interpreter, then we experimented both the provenance for aggregate queries approach (AGG-BASIC) and the heuristic approach (AGG-OPT) discussed in Section 5. We also experimented provenance for aggregate queries approach with parameterization (AGG-PARAM) on Q18 (it has a selection predicate with aggregate value). We intentionally made two wrong queries for each query, of which the errors include different selection conditions, incorrect use of difference, and incorrect position of projection. These are common errors in the students queries from the previous experiment.

Figure 6 includes the runtime of our algorithms to find the smallest counterexample for each TPC-H query we experiment. We present a breakdown of the execution time of our solutions: raw query evaluation time, provenance query evaluation time, SMT-solver running time. We find that the heuristic algorithm performs well for queries where the aggregation operators are at the top of the query tree. While the performance of the provenance for aggregate query algorithm decreases as the database size increases, and is significantly affected by the number of tuples in the group (The SMT solver does not scale well).

Figure 6:

Computation time (sec.), for the TPC-H benchmark, scale=1, timeout = not finishing after 2 hours

For Q18, since it involves an aggregate predicate, we experiment the effectiveness of the algorithm with parameterization. Figure 7 shows the solver runtime and the size of the counterexample of the provenance for aggregate query algorithm with/without parameterization. The size of the counterexample is reduced by 70% while the runtime only increases from 0.0134 seconds to 0.0210 seconds.

Figure 7:

Effectiveness of algorithm with parameterization, on TPC-H Q18, scale-factor = 1

8. USER STUDY

Since one motivation of our work is to provide small examples as explanations of why queries are incorrect, we built our RATest as a web-based teaching tool and deployed in an undergraduate database class in a US university in Fall 2018 with about 170 students. For one homework assignment, students needed to write relational algebra queries to answer 10 questions against a database of six tables about bars, beers, drinkers, and their relationships. The difficulties of these 10 problems range from simple to very difficult. The students had a small sample database instance to try their queries on. Their submissions were tested by an auto-grader against a large, hidden database instance designed to exercise more corner cases and catch more errors; if these answers differed from those returned by the correct queries (also hidden), the students would see the failed tests with some addition information about the error (but not the hidden database instance or the correct queries). The final submissions were then graded manually informed by the auto-grader results; partial credits were given. For the purpose of this user study, we normalize the student score for each question to [0,100].

We did not wish to create unfair advantages for or undue burdens on students with our user study. This consideration constrained our user study design. For example, we ruled out the option of dividing students into groups where only some of them benefit from RATest; we also ruled out creating additional homework problems without counting them towards the course grades. Therefore, we made the use of RATest completely optional (and with no extra incentives other than the help RATesT offers itself). RATesT was given the correct queries and the same database instance used by the auto-grader for testing. If a student query returned an incorrect result, RATest would show a small database instance (a subset of the hidden one), together with the results of the incorrect query and the hidden correct query on this small instance. We made RATest available for only 5 out of the 10 problems. Leaving some problems out allowed us to study the same student’s performance on different problems might be influence by the use of RATest. The 5 problems were chosen to cover the entire range of difficulties:

• (b) Find drinkers who frequent any bar serving Corona.

• (d) Find drinkers who frequent both JJ Pub and Satisfaction.

• (e) Find bars frequented by either Ben or Dan, but not both.

• (g) For each bar, find the drinker who frequents it the greatest number of times.

• (h) Find all drinkers who frequent only those bars that serve some beers they like.

Students must use basic relational algebra; in particular, they were not allowed to use aggregation. Problems (g) and (i) are more challenging than others: (g) involves non-trivial uses of self-join and difference; (i) involves two uses of difference.

We released RATest a week in advance of the homework due date. We collected usage patterns on RATest, as well as how students eventually scored on the homework problems. Ideally, we wanted to answer the following questions: i) Did students who used RATest do better than those who didn’t? ii) For students who used RATest, how did they do on problems with and without RATest’s help? We should note upfront that we expected no simple answers to these questions, as scores could be impacted by a variety of factors, including the inherent difficulty of a question itself, individual students’ abilities and motivation, as well as the learning effect (where one gets better at writing queries in general after more exercises). Therefore, to supplement quantitative analysis of usage patterns and scores, we also gave an optional, anonymous questionnaire to all students after the homework due date.

Quantitative Analysis of Usage Patterns and Scores.

Before exploring the impact of RATest on student scores, let us examine some basic usage statistics, summarized in Figure 8. Overall, 137 students (more than 80% of the class) attempted a total of 3,146 submissions to RATest. The sheer volume of the usage speaks to the demand for tools like RATest, and the sustained usage (across problems) suggests that the students found RATest useful. It is also worth noting that number of attempts reflects problem difficulty; for example, (i), the most difficult problem, took far more attempts than other problems. We also note that while RATest helped the vast of majority of its users get the correct queries in the end; some users never did. We observed in the usage log some unintended uses of RATest: e.g., one student made more than a hundred incorrect attempts on a problem, most of which contained basic errors (such as syntax); apparently, RATest was used to just try queries out as opposed to debugging queries after they failed the auto-grader. Such outliers explain the phenomenon shown in Figure 8 where the overall average # of attempts were much higher than the average # before a correct attempt.

Figure 8:

Statistics on RATEST usage.

Next, we examine how the use of RATest helps improve student scores. Table 5 compares the scores achieved by students who did not use RATest versus those who did, on problem for which we made RATest available. For simple problems such as (b), (d), and (e), there is no little or no difference at all, because nearly everybody got perfect scores with or without help from RATest. However, for more difficult problems, (g) and (i), students who used RATest had a clear advantage, with average scores improved from 92.38 to 97.98 and from 89.80 to 94.40, respectively. Of course, within the constraints of our user study, it is still difficult to conclude how much of this improvement comes from RATest itself; it is conceivable that students who opted to use RATest were simply more diligent and therefore would generally perform better than others. While we cannot definitively attribute all improvement in student performance to RATest, we next provide some evidence that it did help in a significant way.

Table 5:

Comparison of performance between students who did not use RATest and those who did, on problems for which RATest was available.

Did the student use RATest?		No	Yes
Problem (b)	# of students Mean score Std. dev.	67 100.00 0.00	102 100.00 0.00
Problem (d)	# of students Mean score Std. dev.	76 100.00 0.00	93 100.00 0.00
Problem (e)	# of students Mean score Std. dev.	69 99.03 5.63	100 99.67 3.33
Problem (g)	# of students Mean score Std. dev.	70 92.38 26.11	99 97.98 14.14
Problem (i)	# of students Mean score Std. dev.	49 89.80 30.58	120 94.40 19.00

Here, we zoom in on the three most difficult problems, (h), (i), and (j); RATest was only made available for (i). Problem (h) (find all drinkers who frequent only those bars that serve some beers they like) is quite similar to (i) (the difference being “some beers” vs. “only beers”). Problem (j) (find all (bar1, bar2) pairs where the set of beer served at bar1 is a proper subset of those served at bar2) on other hand requires very different solution strategy. Between those who did not use RATest for (i) and those who did, Figure 9 (focus on the first three columns and ignore the rest for now) compares their scores on (h) and (j). We see that for the similar problem (h), those who used RATest on (i) significantly improved their scores on (h), with a degree comparable to the improvement on (i). For the dissimilar problem (j), those who used RATest no (i) showed no improvement in their scores on (j)—the two score distributions are practically the same. We make two observations here. First, it is clear that not all improvement in student performance can be explained by student “diligence” alone; otherwise we would have seen higher performance on (j) for students who used RATest on (i). Second, there is clearly a learning effect: using RATest for one problem can help with a similar problem: (i) helps (h).

Figure 9:

Comparison of performance on (h) and (j) between students whether they used RATEST for (i) or not.

Figure 9, in its last four columns, also breaks down the statistics by when a student started to work on Problem (i). Not surprisingly, we see that “procrastinators” (those who started very close to the due date) performed clearly worse than others. If somebody started to work on (i) using RATest only the day before the homework was due, this individual would be expected to perform even worse than an “average” student who opted not to use RATest at all, especially for the last problem. It would have been nice if we can similarly break down the statistics for students who opted not to use RATest at all, but it was not possible in that case to know when they started to work on the problems. We could only conjecture that a similar trend might exist for procrastinators, so using RATest did not hurt any individual’s performance.

Results of Anonymous Questionnaire.

We collected 134 valid responses to our anonymous questionnaire; Figure 10 summarizes these responses. The feedback was largely positive. For instance, 69.4% of the respondents agree or strongly agree that the explanation by counterexamples helped them understand or fix the bug in their queries, and 93.2% would like to use similar tools in the future for assignments on querying databases. We also asked students which problems they found RATest to be most helpful (multiple choices were allowed): 58% voted for (g) and 94% voted for (i), which were indeed the most challenging ones. We also solicited open-ended comments on RATest. These comments were overwhelming positive and reinforces our conclusions from the quantitative analysis, e.g.:

Figure 10:

Results of student feedback.

• “It was incredibly useful debugging edge cases in the larger dataset not provided in our sample dataset with behavior not explicitly described in the problem set.”

• “Overall, very helpful and would like to see similar testers for future assignments.”

• “I liked how it gave us a concise example showing what we did wrong.”

Summary.

Overall, the conclusion of our user study is positive. Students who used RATest did better, and their improvement cannot be attributed all to merely the fact that they opted to use an additional tool—RATest did add real value. Also, using RATest on one problem could also help with another problem, provided that the problems are similar. Finally, most students found RATest very useful and would like to use similar systems in the future.

9. RELATED WORK

Test data generation.

Cosette[12], which targets at deciding SQL equivalence without any test instances, encodes SQL queries to constraints using symbolic execution, and uses a constraint solver to find counterexamples over which the two queries return different results. Cosette uses incremental solving to dynamically increase the size of each symbolic relation, thus it will return counterexamples with least number of distinct tuples, but the total number of tuples is not minimized. ALso, it deals with only integer domain and returns counterexamples of arbitary values, which may be hard for people to read. XData[11] generates test data by covering different types of query mutants of the standard query, without looking into wrong queries. Qex[38] is a tool for generating input relations and parameter values for a parameterized SQL query that also uses the SMT solver Z3, which aims at unit testing of SQL queries. It does not support nested queries and set operations and hence it cannot work for our problem because of our use of difference.

Provenance and witness.

Data provenance has primarily been studied for non-aggregate queries: Buneman et al.[10] defined why-provenance of an output tuple in the result set, which they call the witness basis. Green et al.[19] introduced how-provenance with the general framework of provenance semiring. Sarma et al.[34] gave algorithms for computing how-provenance over various RA operators in the Trio system. Amsterdamer et al. [2] extended the provenance semiring framework[19] to support aggregate queries. Besides these theoretical works, there are systems that capture different forms of provenance [4, 17, 18, 24, 31, 35]. However, to the best of our knowledge, no prior work considered SWP/SCP, and there are no systems available that support provenance for general SPJUD and aggregate queries.

Teaching or grading tool for programming.

Due to popularity of students taking programming-related courses, teaching and grading tools for programming assignments that automatically generate feedback for submissions are receiving a lot of attention[20, 23, 30]. In the database community, Chandra et al. built XData[11] that can be used for grading by generating multiple test cases for different query mutants, as well as giving immediate feedback to student. The latter is similar to our RATest tool. Jiang and Nandi[22, 28] designed and prototyped interactive electronic textbook to help students get rapid feedbacks from querying the database with novel interaction techniques.

Explanations for query answers.

Explanations based on tuples in the provenance has been recently studied by Wu-Madden [39] and Roy-Suciu [33]. These works take an aggregate query and a user question as input, find tuples whose removal will change the answer in the opposite direction, and returns a compact summary as explanations.

A. PROOFS OF THEOREMS IN SECTION 3

We will give the proofs of theorems in Table 1 in this section.

A.1. SJ and SPU Queries

Given t € Q₁(D) \ Q₂(D), the poly-time algorithm for SJ and SPU queries involve finding a smallest witness of t in D for Q₁, and using the fact that Q₂ is monotone and t ∉ Q₂(D), ∀D′ ⊆ D, t ∉ Q₂(D′).

THEPREM 1.

The SWP for two SJ queries is poly-time solvable in combined complexity.

PROOF.

Let R₁,..., R_k be all the relations that participate in the SJ query Q₁. For each relation R_i, i € [1, k], there must exist exactly one tuple t_i = t.R_i (the R_i component of t), which is part of the witness of t (under set semantic). Since each t_i must satisfy all selection conditions for t to appear in Q₁(D), the set D_t = {t_i|i € [1, k]} ensures that t € Q₁(D_t), and must be minimal. Since Q₂ is monotone and t ∉ Q₂(D), we have t ∉ Q₂(D_t); hence t € (Q₂ ‒ Q₁)(D_t). The running time to find D_t is polynomial in k, giving polynomial combined complexity. □

When projection is allowed, an output tuple may have multiple minimal witnesses, and we pick any one of them.

THEOREM 2.

The SWP for two SPU queries is polynomialtime solvable in combined complexity.

PROOF.

We first consider SP queries. Given an output tuple t in Q₁(D), we scan the input relation to find a tuple t′ that satisfies the selection condition and whose projected attributes equal to t. The smallest witness D_t only consists of only t′. For SPU queries, we do the same procedure as SP queries. At least one relation will return t′. Since Q₂ is monotone and t ∉ Q₂(D), we have t ∉ Q₂(D_t). The running time to find D_t = {t′} is polynomial in k. □

A.2. PJ Queries

For queries involving both projection and join, we show that it is NP-hard in query complexity to find the smallest witness, even when the query can be evaluated in poly-time.

THEOREM 3.

The SWP for two PJ queries is NP-hard in query complexity.

PROOF.

We prove the theorem by a reduction from the vertex cover problem with vertex degree at most 3, which is known to be NP-complete [16] and is defined as follows: Given an undirected graph G( V, E) with vertex set V and edge set E, where the degree of every vertex is at most 3, decide whether there exists a vertex cover C of at most p vertices such that each edge in E is adjacent to at least one vertex in the set.

Construction.

Given G(V, E), suppose V = {v₁,..., v_n}, and E = {e₁,...,e_m}. We encode each vertex as a tuple in the relation R(A, Z, E₁, E₂, E₃). For each vertex v_i € V, R contains a tuple t_i = (v_i, z, e_i1, e_i2, e_i3), where e_i1, e_i2, e_i3 are identifiers of edges adjacent to v_i, i₁ < i₂ < i₃. If the degree of v_i is less than 3, the identifiers are replaced by a null symbol “*”. The attribute Z = z is a constant for all tuples. In addition to R, we have m relations S₁,..., S_m. Each S_i, i € [1, m], has schema S_i;(E, Z). For the edge e_i € E, S_i contains a single tuple (e_i, z). Let D = (R, S₁,...,S_m) be the database instance.

Next, we construct Q₁ involving PJ that consist of m subqueries as follows: For all i € [1, m], let q_i = π_Z(R ⋈ R.E₁=S_i.EVR.E₂=S_i.EVR.E₃=S_i), which operates on S_i and R, checks for match of R.E₁, R.E₂, or R.E₃ with S_i.E, and then projects on to Z. Then we construct Q₁ = q₁ ⋈ q₂ ⋈ ... ⋈ q_m] using natural join on Z. All queries q_i and Q₁ have a single attribute Z. Note that, initially, q_i(D) = {(z)} for all i € [1, m], and therefore Q₁(D) = {(z)} as well. The query Q₂ also outputs the attribute Z, but not the tuple {(z)}. We set Q₂ = π_z(R ⋈ _{R.Z≠S1 Z} S₁) (the choice of S₁ is arbitrary), and therefore Q₂(D) = {} is empty. The tuple t for which we want to find the smallest witness in (Q₁ - Q₂)(D) is (z). In other words, the goal is to find a subinstance $D^{\prime }=\left(R^{\prime },S_1^{\prime },\dots ,S_m^{\prime }\right)$, $R^{\prime }\subseteq R,S_1^{\prime }\subseteq S_1,\dots ,S_m^{\prime }\subseteq S_m$, such that (z) € Q₁(D) \ Q₂(D).

Below we argue that G has a vertex cover of size ≤ p, if and only if the SWP instance above has a witness D′ of size ≤ p + m where m is the number of edges in G.

The “Only If” direction.

Suppose we are given a vertex cover C with at most p vertices in G. We construct $R_i^{\prime }=\left\{t_j|v_j\in C\right\}$, and $S_i^{\prime }=S_i$ for all i € [1, m]. Since |C| ≤ p, |D′| ≤ p + m since each S_i contains a single tuple. Since C is a vertex cover, for all edge e_i = (v_j, v_{$\mathcal{l}$}) € E, either v_j € C or v_{$\mathcal{l}$} € C. Suppose without loss of generality v_j € C. Then (wlog.) assume $t_j=\left(v_j,z,e_i,e^{\prime },e^{\prime \prime }\right)$ where e′, e″ are other two adjacent edges on v_j (they could be * as well if the degree of v_j is < 3). The tuple t_j and the tuple S_i(e_i, z) will satisfy the join condition of q_i (irrespective of the position of e_i in t_i), and the projection will output (z). Since C is a vertex cover, for all i € [1, m], q_i(D′) = {(z)}. Therefore, Q₁(D′) = {(z)}. Q₂(D′) remains empty. Hence (z) € Q₁(D′) \ Q₂(D′) Therefore, D′ is a witness of (z) of size at most p + m.

The “If” direction.

For the opposite direction, consider a witness $D^{\prime }=\left(R^{\prime },S_1^{\prime },\dots ,S_m^{\prime }\right)$ where $R^{\prime }\subseteq R,S_1^{\prime }\subseteq S_1,\dots ,S_m^{\prime }\subseteq S_m,\left|R^{\prime }\right|+\left|S_1^{\prime }\right|+\dots +\left|S_m^{\prime }\right|\le p+m$, such that $(z)\in Q_1\left(D^{\prime }\right)\backslash Q_2\left(D^{\prime }\right)$, i.e., $(z)\in Q_1\left(D^{\prime }\right)$. We construct $C=\left\{v_i|t_i\in R^{\prime }\right\}$. Note that if $(z)\in Q_1\left(D^{\prime }\right)$, (z) must be in the result of all subqueries q_i (D′), i € [1, m]. And q_i (D′) returns (z) if and only if (a) $S_i^{\prime }$ is not empty (i.e., $S_i^{\prime }=S_i$ since S_i had only one tuple), and (b) if e_i = (v_j, v_{$\mathcal{l}$}), at least one of t_j or t_{$\mathcal{l}$} must appear in R′ to satisfy the join condition in q_i otherwise q_i returns an empty result and thus Q₁ returns an empty result. Therefore, all $S_t^{\prime }$ must be equal to $S_i,\left|S_i^{\prime }\right|=1$. Then we have $\left|S_1^{\prime }\right|+\dots +\left|S_m^{\prime }\right|=m.since\left|D^{\prime }\right|\le p+m,\left|R^{\prime }\right|\le p$, and thus we get a vertex cover C of size at most p.

An example reduction is shown in Figure 11. □

Figure 11:

Example reduction in Theorem 3

A.3. JU Queries

THEOREM 4.

The SWP for two JU queries is NP-hard in query complexity.

PROOF.

We reduce from the vertex cover problem.

Construction.

Suppose V = {v₁,..., v_n} and E = {e₁,...,e_m}. For each vertex v_i in G, there is a relation R_i(Z) which consists of a single tuple (z). For each edge e_i = (v_j, v_{$\mathcal{l}$}) € E, we construct a query q_i = R_j ∪ R_{$\mathcal{l}$}. Then we construct a query Q₁ = q₁ ⋈ ... ⋈ q_m, where the join is a natural join on Z. We construct Q₂ = R₁ ⋈_R1.z±r2.z R₂ (the choice of R₁, R₂ is arbitrary). Hence D = (R₁, ... , R_n), Q₁(D) = {(z)}, and Q₂(D) = {}. The output tuple (z) € Q₁(D) \ Q₂(D), and the goal is to find a witness $D^{\prime }=(R_1^{\prime },\cdots R_n^{\prime })$ for (z) where $R_i^{\prime }\subseteq R_i$ for all i € [1, n].

We show that there exists a vertex cover C in G of size ≤ p if and only if there is a witness D′ for (z) of size ≤ p.

The “Only If” direction.

Consider a vertex cover C of G such that |C| ≤ p. If v_i € C, then $R_i^{\prime }=\{(z)\}$, otherwise $R_i^{\prime }=\left\{\right\}$. Since C is a vertex cover, all edges must be covered. For an edge e_i = (v_j, v_{$\mathcal{l}$}), suppose wlog. v_j € C. Hence the subquery q_i = R_j ∪ R_{$\mathcal{l}$} returns (z) on D′. Therefore, $Q_1\left(D^{\prime }\right)=(z),Q_2\left(D^{\prime }\right)=\left\{\right\},(z)\in Q_1\left(D^{\prime }\right)\backslash Q_2\left(D^{\prime }\right)$, i.e., $D^{\prime }$ is a witness for (z), and |D′| = |C| ≤ p.

The “If” direction.

Consider any witness $D^{\prime }=\left(R_1^{\prime },\dots ,R_n^{\prime }\right)$ where $R_1^{\prime }\subseteq R_1,\dots ,R_n^{\prime }\subseteq R_n$ and $\left|R_1^{\prime }\right|+\dots +\left|R_n^{\prime }\right|\le p$, such that $(z)\in Q_1\left(D^{\prime }\right)\backslash Q_2\left(D^{\prime }\right)$, i.e., $(z)\in Q_1\left(D^{\prime }\right)$. Since R_i had only one tuple (z), either $R_i^{\prime }$ has (z) or it is empty. If tuple $(z)\in R_i^{\prime }$, then we add vertex v_i to a set C. If (z) is in the result of Q₁(D′), (z) must be in the result of all subqueries q_i (D′) for all i € [1, m]. For e_i = (v_j, v_{$\mathcal{l}$}), q_i(D′) returns (z) if and only if at least one of $R_j^{\prime }$ and $R_{\mathcal{l}}^{\prime }$ is not empty; otherwise q_i returns an empty result and thus Q₁ returns an empty result. Therefore, for each edge e_i € E, at least one of its adjacent vertices v_j or v_{$\mathcal{l}$} must exist in C. Hence C is a vertex cover, and |C| = |D′| ≤ p. □

On the other hand, the following theorem shows that if all unions appear after all joins (which we call JU* queries), then the SWP can be solved in poly-time in combined complexity.

THEOREM 5.

The SWP for two JU* queries is polynomial time solvable in combined complexity.

PROOF.

Let t ∈ Q₁(D)\Q₂(D). According to Theorem 1, the SWP for SJ queries is polynomial time solvable in combined complexity. Hence, we look for the smallest witness of t in join-only part of Q₁, and choose the one with smallest number of tuples. The running time is polynomial in both n = |D| and k. □

A.4. Size-Bounded SPJU Queries

Theorem 6 shows that if the SPJU queries are of bounded size (i.e. considering more standard data complexity), there is a polynomial time algorithm for SWP. We prove this theorem using Proposition A.1, which is intuitive and known (e.g., [32]). We use m-DNF to refer to a DNF where each minterm has at most m literals.

PROPOSITION A.1.

Given an SPJU query Q, a database instance D, and an output tuple t ∈ Q(D), the how-provenance of t in Q(D) can be transformed into a k + 1-DNF in polynomial time when Q is of bounded size, where k is the number of join operations in Q.

THEOREM 6.

The SWP for two SPJU queries is polynomialtime solvable in data complexity.

PROOF.

Let t be an output tuple in Q₁(D) \ Q₂(D). Since Q₂ is monotone, t ∉ Q₂(D′) for any D′ ⊆ D. According to Proposition A.1, we can compute the how-provenance Prv_(Q1-Q2)(D)(t) in DNF in poly-time in data complexity. Then we scan the DNF to find the minterm with least number of literals, and this minterm represents the smallest witness for t in Q₁(D) − Q₂(D). The literals in this clause are the identifiers of tuples in the smallest witness. □

For instance, if Prv(t) = a + bc, then a forms the smallest witness.

A.5. Queries Involving Difference

Before discussing general SPJUD queries, let’s focus on one special class of SPJUD queries where all differences appear after all SPJU operators (which we call SPJUD* queries). More formally, we define this class using formal grammar: Q → q⁺|Q‒Q, where q⁺ is a terminal that represents SPJU queries. For instance, queries Q₁ and Q₂ in Example 1 are SPJUD* queries. The following theorem shows that the SWP can be solved in poly-time for SPJUD* queries.

THEOREM 7.

The SWP for two SPJUD* queries is polynomialtime solvable in data complexity.

PROOF.

Let t be an output tuple in Q₁(D) \ Q₂(D). Since Q₁ and Q₂ are SPJUD* queries that can be written as nested differences of queries like q₁ − q₂ − (q₃ − (q₄ − q₅))−..., where all q_i-s are SPJU queries, Q₁ − Q₂ is also an SPJUD* query. The output tuple t must be either in or not in the result of each q_i. We find the smallest witness by enumerating the minimal witnesses of t w.r.t. every q_i and D. If t is in the result of q_i(D), let w_i be the set of minimal witnesses of t w.r.t. q_i and D. Then we pick one element from every w_i ∪ {∅}, and construct w as the union of all witnesses or the empty set we picked. We evaluate Q₁ and Q₂ on w to see whether it is a witness for t, and record the w of the smallest size. We finish this procedure until we enumerate all combinations.

This procedure will return the smallest witness because: (i) if t ∉ q_i(D), t will also not be in q_i(w) for any w ⊆ D due to monotonicity, so we don’t need to consider such q_i-s; (ii) Assume that w′ is a smallest witness of t w.r.t. Q₁ − Q₂ and D, for all q₁ where t ∈ q_i(w′), w′ must be a superset of a minimal witness of t w.r.t. q_i and D. Hence w′ must be the union of minimal witnesses of t w.r.t. these q_i-s and D; otherwise, if w′ is a strict superset of the union of minimal witnesses of t, we can always remove tuples not belong to any minimal witness of t w.r.t. q_i-s and D from w′, without affecting t to be in or not in any q_i, which contradicts the assumption that w′ is a smallest witness. Therefore a smallest witness of t w.r.t. Q₁ − Q₂ and D must be union of minimal witness of t w.r.t. q_i and D, and thus it must be enumerated during the enumeration procedure.

The time complexity of entire enumeration process is $O\left({\Pi }_i{m}^{k_i}\right)=O\left(m^{kd}\right)$, where d is the number of difference operators, m is the max size of relations, k is the max complexity of each SPJU query q_i. When queries are of bounded sizes, i.e., fix d and k, the SWP for two SPJUD queries that can be written as nested differences of SPJU queries is polynomial-time solvable. □

SWP is NP-hard in general even for bounded-size queries.

THEOREM 8.

The SWP for two SPJUD queries Q₁ and Q₂ is NP-hard in data complexity.

PROOF.

We again give a reduction from the vertex cover problem with vertex degree at most 3 (see Theorem 3).

Construction.

Suppose in G = (V, E), V = {v₁,..., v_n}, E = {e₁,...,e_m}. We construct two relations R(A, Z, E₁, E₂, E₃) and S(B, C, Z). For each vertex v_i ∈ V, R contains a tuple t_i = (v_i, e_i1, e_i2, e_i3, z), where e_i1, e_i2, e_i3 are the identifiers of edges adjacent to v_i, i₁ < i₂ < i₃. If the degree of v_i is less than 3, the identifiers are replaced by a null symbol “*”. Here z is a constant. For each edge e_i ∈ E, S contains a tuple $\left(e_i,e_{(i\%m)+1},z,z\right)$, where $e_{(i\%m)+1}$ is the identifier of the next edge in the edge list (the next edge of e_m is e₁). Let D = (R, S) be the database instance.

Next, we construct an SPJUD query that consists of several subqueries as follows: Let $q_1(\text{ on }S)={\pi }_Z(S)$; $q_2(\text{ on }S)={\pi }_{B,Z}(S)$; q₃ (on R, S) = π_S.C,S.Z(S ⋈_{S.C=E1vS.C=E2vS.C=E3} R). Then we construct Q₁ = q₁, hence Q₁(D) = {(z)}. We also construct Q₂ = π_Z(q₂ \ q₃) (assume C in q₃ is renamed to B). For edge e_i = (v_j, v_{$\mathcal{l}$}), the edge e_i appears for both tuples t_j, t_{$\mathcal{l}$} (in one of E₁, E₂, E₃ attributes), and therefore, (e_i,z) appears in the result of q₃(D) for every i ∈ [1, m]. Hence q3(D) = π_B,Z(S). So q₂(D) \ q₃(D) = ∅. Then (Q₁ − Q₂)(D) = {(z)}, and the goal is to find the smallest witness for (z). For the vertex cover instance in Figure 11(a), R will be as given in Figures 11(c), and S will contain tuples {(e₁, e₂, z), (e₂, e₃, z), ... (e₇, e₁, z)}.

We now show that there exists a vertex cover C of size at most p in the graph G if and only if there is a witness D′ = (R′, S′) where |R′| + |S′| ≤ p + m.

The “Only If” direction.

Suppose we are given a vertex cover C of G with at most k vertices. Construct R′{t_i | v_i ∈ C}, and S′ = S. Q₁(D) = Q₁(D′) = {(z)} since S is unchanged. Similarly, q₂(D′) = π_B,Z(S)) is unchanged. Since C is a vertex cover, for every edge e_i = (v_j, v_{$\mathcal{l}$}) either t_i or t_{$\mathcal{l}$} is in R′, and hence q₃(D′) = q₃(D), i.e., each (e_i, z), i ∈ [1, m] appears in q₃(D′). Hence Q₁ - Q₂ will output tuple (z) on D′, |R′| = |C| ≤ p, |S′| = |S| = m, and we get a witness of at most p + m tuples.

The “If” direction.

Consider any witness D′ = (R′, S′) where R′ ⊆ R, S′ ⊆ S, |R′| + |S′| ≤ p + m, such that (z) ∈ Q₁(D′) \ Q₂(D′). We construct C = {v_i | t_i ∈ R′}. Since (z) is in Q₁(D′) \ Q₂(D′), (z) must be in the result of q₁(S′), and not in the result of q₂(S′) ′ q₃(R′, S′), hence S′ must contain at least one tuple. Therefore, q₂(S′) outputs at least one tuple (e_i, z) since S′ is not empty. In turn, q₃(R′, S′) must output all tuples in q₂(S′) to make q₂(S′) − q₃(R′, S′) empty. (a) We argue that S′ = S. Suppose S′ contains at least one tuple, say wlog, (e₁, e₂, z). Then to remove (e₁, z) from q₂(S′)\q₃(R′, S′), q₃(R′, S′) must contain (e₁, z), which can generate only from S(e_m, e₁, z). Hence (e_m, e₁, z) ∈ S′. In turn, (e_m, z) ∈ q₂(S′). To remove it, we need S(e_m-1, e_m, z) in S′. Continuing this argument (by induction), we get S = S′. (b) Consider any tuple, say wlog., (e₁, e₂, z) in S′. Then to remove (e₁, z) from q₂(S′) \ q₃(R′, S′), not only the tuple (e_m, e₁, z) ∈ S′, it also has to satisfy the join condition with R. This will hold only if for one of the end points v_j, v_{$\mathcal{l}$} of e₁ = (v_j, v_{$\mathcal{l}$}), t, ∈ R′ or t_{$\mathcal{l}$} ∈ R′. This should hold for all edges, and therefore the set C we constructed is a vertex cover. Since |S′| = |S | = m, |R′| = |C| ≤ p, therefore, we get a vertex cover in G of size at most p.

The queries we constructed during the reduction are all of bounded size, therefore the SWP for two SPJUD queries is NP-hard in data complexity even for queries of bounded size. □