Data generation for testing and grading SQL queries

Abstract

Correctness of SQL queries is usually tested by executing the queries on one or more datasets. Erroneous queries are often the results of small changes or mutations of the correct query. A mutation Q$'$ of a query Q is killed by a dataset D if Q(D) $\ne$ Q$'$(D). Earlier work on the XData system showed how to generate datasets that kill all mutations in a class of mutations that included join type and comparison operation mutations. In this paper, we extend the XData data generation techniques to handle a wider variety of SQL queries and a much larger class of mutations. We have also built a system for grading SQL queries using the datasets generated by XData. We present a study of the effectiveness of the datasets generated by the extended XData approach, using a variety of queries including queries submitted by students as part of a database course. We show that the XData datasets outperform predefined datasets as well as manual grading done earlier by teaching assistants, while also avoiding the drudgery of manual correction. Thus, we believe that our techniques will be of great value to database course instructors and TAs, particularly to those of MOOCs. It will also be valuable to database application developers and testers for testing SQL queries.

Appendices

Appendix

Appendix A: Cardinality estimation for join inputs

The tuple estimation for each relation for constrained aggregation on join result is done in three steps. First we construct a join graph. Then we infer attributes to be added to uniqueElements and singleValuedAttributes. In the third step, we assign cardinality to each relation such that the resulting number of tuples is n.

Step 1: Construct Join Graph

We construct a join graph G = (R, E), with each relation in the query as a vertex. The join conditions from one table to another are represented by a single edge between the nodes. Figure 2 shows a join graph involving relations A, B and C. There are join conditions between A and B, and between B and C. However, there are no join conditions between A and C. Inferred join equalities are also added to the graph. For example, the join conditions A.a = B.b and B.b = C.c imply that A.a = C.c is also a join condition and hence it would be added to the graph. Note that this may introduce a cycle in the graph; our algorithm can work with cyclic join graphs.

Fig. 2

Join graph

Step 2: Infer Attribute Properties

Next we apply the following sets of rules to infer properties of attributes

Rule 1::

Every group by attribute is a single-valued attribute.

Rule 2::

Every set of attributes declared as primary key or unique key is unique in the group.

Rule 3::

Every attribute which appears in conjuncts of the form A.a=constant is a single-valued attribute.

Rule 4::

If each attribute of any uniqueElements[$R_{i}$] is a single-valued attribute, then all attributes of that relation are single-valued attributes.

Rule 5::

If any attribute, $R_{i}.x$, is a single-valued attribute, then every attribute of equivalence class (Sect. 2.2) in which $R_{i}.x$ is present becomes a single-valued attribute. For example, if the join condition is A.a = B.a and A.a is single-valued, B.a also becomes single-valued.

Rule 6::

If an attribute of a unique element is single-valued, then remaining attributes of unique element become unique. We apply this rule recursively on the unique element to get a minimal unique element. We then drop all non-minimal sets from uniqueElements. For example, if (A.a, A.b, A.c) is unique and A.a is single-valued, then (A.b, A.c) is unique and is added to $uniqueElements[R_i]$. In this case (A.a, A.b, A.c) is dropped from $uniqueElements[R_i]$.

The rules are applied according to Algorithm 2 to infer which attributes are added to uniqueElements and which to singleValuedAttributes.

Step 3: Assign Cardinality

We define some more terms

• $joinAttributes[R_{i},R_{j}]$: attributes of relation $R_{i}$ that are involved in join conditions with relation $R_{j}$.

• $unique[R_{i},R_{j}]$: $\{ S_k \mid S_k \subseteq \text{ joinAttributes }[R_i,R_j] \wedge S_k \in \text{ uniqueElements }[R_i] \}$.

• $n_{R_i}$: number of tuples assigned to relation $R_i$.

In order to find the number of tuples for each relation, we use the attributes inferred using Algorithm 2 along with the following rules.

Rule 7::

If $n_{R_{i}}$=$n,n>1$ and $unique[R_{i}, R_{j}]\ne \emptyset$ then $n_{R_{j}}$ is set to n. We also infer further unique elements as follows. For each $S_k \in \textit{unique}[R_i, R_j]$, let $S'_k$ be the attributes from $R_j$ that are equated to $S_k$. Then add $S'_k$ to uniqueElements[$R_j$].

The intuition behind Rule 7 is as follows. Consider the join of two relations A and B. Let the join condition be $A.a=B.a$ and suppose that $\{A.a\} \in$ uniqueElements[A]. Here joinAttributes [A, B]={A.a}, joinAttributes [B, A]={B.a}, unique [A, B]={A.a} and unique [B, A]=$\emptyset$. If the cardinality of A is n, since A.a is unique, it must have n different values. The relation B has join condition with A.a which belongs to uniqueElements[A]. So B must contain n tuples with distinct values for the attribute B.a across n tuples and each value matches with the value of A.a for one of the tuples in $R_i$. So the cardinality of B becomes n and B.a becomes a unique attribute.

Implementation Rule 1: If $n_{R_{i}}$=$n,n>1$ and $R_{i}$ has a multi-attribute unique element, mu, such that every attribute of mu participates in some join conditions but joinAttributes $[R_i,R_j]\subset$ mu for all j, then for at least one relation $R_k$ that joins with $R_i$ joinAttributes $[R_i,R_k]$ is unique and $n_{R_k}=n$. One such $R_k$ is picked, and we add joinAttributes $[R_i,R_k]$ to uniqueElements $[R_i]$ and joinAttributes $[R_k,R_i]$ to $uniqueElemen-ts[R_k]$.

The intuition is as follows. Consider the join graph shown in Fig. 2. Let joinConds[A, B]={A.a=B.a}, joinConds[B, C]={B.b=C.b}. Let (B.a, B.b) be unique. Here, joinAttributes [A, B] = {A.a}, joinAttributes [B, A] = {B.a}, joinAttributes [B, C]={B.b} and joinAttributes [C, B]={C.b}. Further, unique [A, B]=$\emptyset$, unique [B, A]=$\emptyset$, unique [B, C]=$\emptyset$, unique [C, B]=$\emptyset$.

Suppose cardinality of B is n. Since unique[B, A] = $\emptyset$, it is possible that $n_{A}$ =1 such that A.a matches with all values of B.a across n tuples. Here B.a contains same value across n tuples. Similarly, we can choose $n_{C}$ = 1, and B.b will have the same across n tuples. Now both B.a and B.b have same values across all n tuples. But (B.a, B.b) must be unique across n tuples. So the assignment of cardinalities is incorrect. Hence, at least one of B.a or B.b must be chosen to be unique, and this will cause one $n_A$ or $n_B$ to be n.

Note that in this example had (B.a, B.b, B.c) been unique, every attribute of mu does not participate in any of the join conditions. In this case, the rule is not applicable and both A and C may have a cardinality of 1. To generate n tuples for B such that the join results in n tuples, B.c can have n distinct values, while B.a and B.b have same values corresponding to A.a, C.b, respectively.

We differentiate this rule from others since this rule can have several possible outcomes as opposed to the other rules for which the outcome is definite and unique. One outcome is chosen. The choice of which of the joining relations is assigned cardinality as n can be made by the solver or as heuristic the choice can be made arbitrarily; we describe these below.

Cardinality Inference Algorithm

Let the aggregated attribute be R.a. For getting the cardinality of each relation, using the rules and the given join conditions of the relations we can encode the tuple assignment problem in the form of constraints in CVC3. We add the following constraints in CVC3.

• constraints ascertaining singleValuedAttributes and uniqueElements for each relation

• for each relation such that all attributes are single-valued (Rule 4) constraints to ensure that the number of tuples is 1

• constraints for Rule 7 and the Implementation Rule 1 for all the relations in the query as applicable

• constraints to ensure that the final count after joining the tables is n

• in case n values are required for some attribute R.a to satisfy some aggregate condition, we add constraints to ensure that the relation R has n tuples. For example, consider a case where $SUM(R.a) = 17$, where a is an integer attribute and there is a constraint $R.a\le 5$, we need at least four tuples for the given group of R and they cannot all be the same. It is not possible to satisfy the aggregation condition if we assign a single tuple to R, the join of R with other relations produces four tuples for the group. Similar is the case with SUM DISTINCT on an integer attribute.

On solving this set of constraints, we get the number of tuples for each relation.

The constraint approach for tuple generation works well if the number of attributes is not very large. In practice, we use a simple and fast heuristic approach described as follows. If any non-empty set of attributes of a relation forms a unique element and every attribute of that unique element is a single-valued attribute, then that relation must contain a single tuple (explained in Rule 4). For such relations, the only possible choice of cardinality is 1. Of the remaining relations, the heuristic algorithm chooses one relation and assigns to it a cardinality of n, making it the root node. The count of all other nodes of the join graph, $n_{R_{i}}$ is initialized as 1. The root node ($R_{r}$) is then used as a starting relation to calculate the actual cardinality for other relations using Rule 7 and Implementation Rule 1. The procedure for this is described Algorithm 4 of Appendix C (Online resource). If the heuristic fails, we use the constraint approach.