Data Generation for Testing and Grading SQL Queries

Given an SQL query $Q$, XDataxdata:icde11 generates multiple datasets. The first dataset is designed to generate non-empty datasets for $Q$, wherever feasible, which itself kills several mutations that would generate an empty result on that dataset. Each of the remaining datasets is targeted to kill one or more mutations of the query; i.e. on each dataset the given query returns a result that is different from those returned by each of the mutations targeted by that dataset. The number of possible mutations is very large, but the number of datasets generated to kill these mutations is small.

To generate a particular dataset, XData does the following:

1. 1.

   It generates a set of constraint variables, where each tuple in the target dataset is represented by a tuple of constraint variables.

2. 2.

   It generates a set of constraints between these variables. For example, selection conditions, join conditions, primary key and foreign key conditions are all mapped to constraints on these variables. Different datasets are designed to catch different mutations; the exact set of constraints generated (as also the set of constraints variables) is different for each dataset, as described shortly.

3. 3.

   It then invokes a constraint (SMT) solver smt ¹¹1A constraint solver takes as input a set of constraints and produces a result that satisfies the constraints. to solve the constraints; the solution given by the solver assigns values to each constraint variable, thereby defining a specific dataset.

In order to kill mutations, the goal of XData is to generate datasets that produce different results on the query and its mutation. To produce different results, constraints are added in a manner so as to ensure that the mutation in a node of a query tree is reflected above leading to different results for the query and its mutation. For example consider the following query:

This query has two predicates course INNER JOIN takes USING (course_id) and course.credits >= 6. When generating datasets to kill the mutations of join predicates we need to ensure that $course.credits>=6$ is satisfied for the tuple generated for the course table. In case $course.credits>=6$ is not satisfied, both the query and the mutant could give empty results.

The mutation space considered consisted of the following

1. 1.

   Join Type Mutations: A join type mutations involves replacing one of { INNER, LEFT OUTER, RIGHT OUTER } JOIN with another. Consider the mutation from department INNER JOIN course to department LEFT OUTER JOIN course. In order to kill this mutation, we need to ensure that there is a tuple in department relation that does not satisfy the join condition with any tuple in course relation. The INNER JOIN query would not output that tuple in the department relation while the LEFT OUTER JOIN would.

   In SQL, a join query can be specified in a join order independent fashion, with many equivalent join orders for a given query. Hence, the number of join type mutations across all these orders is exponential. From the join conditions specified in the query, XData forms equivalence classes of $<$relation, attribute$>$ pairs such that elements in the same equivalence class need to be assigned the same value to meet (one or more) join conditions. Using these equivalence classes, XData generates a linear number of datasets to kill join type mutations across all join orderings. If a pair of relations involve multiple join conditions XData nullifies each join condition separately.

2. 2.

   Selection Predicate Mutations: For selection conditions XData considers mutations of the relational operator where any occurrence of one of $\{=,<>,<,>,\leq,\geq\}$ is replaced by another. For killing mutations for the selection condition $A_{1}$ relop $A_{2}$, XData generates 3 datasets (1) $A_{1}>A_{2}$, (2) $A_{1}<A_{2}$, and (3) $A_{1}=A_{2}$. These three datasets kill all non-equivalent mutations from one relop to another relop. These datasets also kill mutations because of missing selection conditions.

3. 3.

   Unconstrained Aggregation Mutation: Aggregations at the root of the query tree are not constrained to satisfy any condition. The aggregation function can be mutated among MAX, MIN, SUM, AVG, COUNT and their DISTINCT versions. In order to kill these mutations, a dataset with three tuples is generated; two with the same value (non-zero) and another with a different value in the aggregate column.

We now describe our techniques for constraint generation. Our current implementation uses the CVC3 constraint solver CVC3 . We are working on implementing the constraints in the SMT-LIB format smtlib so that we can potentially use several constraint solvers compatible with SMT-LIB.

In CVC3, text attributes are modeled as enumerated types while numeric attributes are modeled as subtypes of integers or rationals. The data type declarations in CVC3 are as follows. For each attribute of each relation, we specify a set of acceptable values, taken from an input database, as datatypes in CVC3. While the input database is not necessary for data generation, its use makes for improved readability and comprehension of the query results. In case an input database is not specified we get the range from the data type of the corresponding column.

A tuple type is created for each relation, where each element is a constraint variable of the specified type. A relation is represented as an array of constraint variables; the size of the array has to be determined before solving the constraints, and constraints have to be specified for each attribute of each tuple.

Consider an input database which has CS-101, BIO-301, CS-312 and PHY-101 as course_id, and credits is an integer constrained to be between 2 and 10. Then, this translates to the following the declarations in CVC3.

DATATYPE
course_id = CS-101 | BIO-301 | CS-312 | PHY-101 END;
credits:TYPE = SUBTYPE (LAMBDA (x: INT):
        x > 1 AND x < 11);
course_tuple_type:TYPE = [course_id,credits];
course: ARRAY INT OF course_tuple_type;

Tuple attributes are referenced by position, not by name; thus, course[2].0 refers to the value of the first attribute, which is course_id, of the second tuple in course.

To ensure a non-empty result for the query in Example 1, we need a tuple in course which matches a tuple in takes on attribute course_id and where the course.credits >= 6. This is done by creating a tuple for each of the relations and adding the following constraints:
ASSERT course[1].0 = takes[1].1;
ASSERT course[1].1 >= 6;

Primary key constraints are enforced by constraints that ensure that if two tuples match on the primary key, then the values of the remaining attributes for those two tuples should also match. Foreign key constraints are enforced by adding extra tuples that satisfy the foreign key condition. Foreign key constraints for the foreign key from takes.course_id to course.course_id are specified as:

ASSERT FORALL(i: takes_index):
EXISTS (j: course_index): takes[i].1 = course[j].0;

where takes_index and course_index give the index range for the takes and course arrays; takes[i].1 stands for dept_name of the i^th tuple of course. In our example an extra tuple would be generated for course for each tuple in takes, although in this case the first tuple of course itself ensures the foreign key constraint is satisfied for the first tuple of takes.

The above constraints are given to CVC3 which generates satisfying values (assuming the constraints are satisfiable).

As explained earlier in this section, to kill a mutation of the inner join to right outer join, we need a value in course.course_id which does not match any value in takes.course_id. To do so we replace the earlier equality constraint
ASSERT course[1].0 = takes[1].1;
with:
    ASSERT NOT EXISTS(i:course_index):
     (course[i].0 = takes[1].1);
and generate the required dataset using CVC3. Datasets for killing other mutations are generated similarly.

Tuya et al. in Riva:2010 presented techniques for killing mutations in the presence of disjunctions.

For killing a where clause mutation of a query, the mutation should be reflected as a change at the root of the query tree. Consider the $where$ clause $P_{1}$ $or$ $P_{2}$ , where $P_{1}$ and $P_{2}$ are conjuncts of selection conditions. If a condition in $P_{1}$ is mutated, $P_{2}$ should be false so that the change in the condition of $P_{1}$ affects the output of the query. For example, let $P_{1}$ be $(a>50$ AND $b=40)$. If we mutate the first condition in $P_{1}$ to $a<50$ we need to ensure that $b=40$ is satisfied while $P_{2}$ is not satisfied. If $P_{2}$ is satisfied there would be no change in the output of the query. Although not mentioned in Riva:2010 , the above technique not only kills mutations of atomic selection conditions (such as comparisons) but also kills mutations of conjunction operations to disjunctions and vice versa.

The XData system has been extended to implement the above technique for killing selection predicate mutations in the presence of disjunctions.

For string comparisons, we consider the following class of string constraints: $S_{1}$ relop constant, and $S_{1}$ relop $S_{2}$, where $S_{1}$ and $S_{2}$ are string variables, and relop operators are $=,<,\leq,>,\geq,<>$ and case-insensitive equality denoted by $\sim=$. We support LIKE constraints of the form $S$ likeop pattern, where likeop is one of LIKE, ILIKE (case insensitive like), NOT LIKE and NOT ILIKE. We also support strlen($S$) relop  constant where relop is one of $=,<,\leq,>,\geq$ or $<>$. We do not support constraints of the form $S_{1}$ likeop $S_{2}$, where both $S_{1}$ and $S_{2}$ are variables.

We support the string functions upper and lower in queries where these functions can be rewritten using one of the operators described above; for example upper($S$) = ‘ABC’ can be rewritten as $S\sim=$ ‘ABC’, and similarly upper($S$) LIKE pattern can be replaced by $S$ ILIKE pattern. We rewrite these conditions as a pre-processing step. Conditions like $upper$ ($S$) = constant or $upper$ ($S$) LIKE pattern, where the constant or pattern contains at least one lower case character, cannot be satisfied. Hence for such conditions we do not change the operators but return an empty dataset. If these functions are used on a constant string, we convert the string to upper or lower according to the function.

There are several available string solvers that we considered, including Hampi HAMPI , Kaluza KAL , SUSHI SUSHI and Rex REX . However, we found that Hampi and Kaluza were rather slow, and while they handled regular expressions and length constraints, they could not handle constraints such as $S1<S2$, where both $S1$ and $S2$ are variables. Rex and SUSHI, though much faster, could not handle constraints involving multiple string variables. Hence, we built our own solver which is described in Appendix B. Subsequent to the implementation of our string solver the latest version of CVC (CVC4) has also provided some support for solving string constraints cvc4:string , but it has some limitations currently²²2Although there are some limitations in CVC4 currently; in future we may use CVC4 as an integrated solver for both string constraints and other constraints.. Refer the experimental section in Appendix B for details.

Once the values for string variables are obtained we solve the non-string constraints using CVC3 and get an overall solution as follows: enumeration types are created in CVC3 for string variables, with the enumeration names being the (suitably encoded) strings generated by the string solver. For example, consider a query which has a single string constraint: $S_{1}$ like $`Bio\%^{\prime}$. Let the string that satisfies the constraint be Biology, then the constraint is specified as

ASSERT(table[index].pos = Biology)

in CVC3, where table[index].pos is the corresponding CVC3 variable of $S_{1}$. We then add constraints in CVC3 equating each string variable to its corresponding enumeration name, add other non-string constraints as described in Section 2 and invoke CVC3 to get a suitable dataset.

If there are disjunctions in the selection predicate, it is not possible to separate the string constraints since not all string constraints may need to be satisfied.

There can be different types of string mutations depending on whether the string condition is a comparison condition or a LIKE condition.

If a foreign key attribute $fk$, is nullable then the foreign key constraint is encoded in the SMT solver by forcing values of $fk$ to be either values from the corresponding primary key values or NULL values; this allows the SMT solver to assign NULLS to foreign keys if required. Nullable foreign keys allow us to kill more mutants than is possible if the foreign key attribute as not nullable. (Our implementation handles multi-attribute foreign and primary keys.)

If the query contains a condition $R.a$ IS NULL, we explicitly assign (a different) NULL to attribute $a$ for each tuple $R[i]$ if the query contains only inner joins or only a single relation (provided the attribute is nullable; attributes declared as primary key or as not null cannot be assigned a NULL value).

However in case the query contains an outer join there may be multiple ways to ensure that an attribute has NULL value. Let us consider the join condition E1 $\mathrel{\hbox{${{}_{\raise 3.0pt\hbox{--}}^{\hbox{--}}}$}\hskip-4.41017pt\Join}$ E2. If the IS NULL condition is on an attribute of E1 we need to ensure that the value of that attribute is NULL. If the IS NULL condition is on an attribute on E2 we need to ensure that either (a) that attribute is NULL (which may not be possible if E1 is a relation and the attribute is not nullable) or (b) for that tuple in E1 there does not exist any matching tuple in E2; this can be done by a minor change in the algorithm to handle NOT EXISTS subqueries as described in Section 7.1 (Algorithm 1). We omit details for brevity.

We consider mutation from IS NULL to NOT IS NULL. The first dataset (the one that generates non-empty results on the original query) kills the mutation of IS NULL to NOT IS NULL if the IS NULL condition is present in the form of conjunctions with other conditions. In the presence of disjunctions, we generate a dataset such that the IS NULL condition is satisfied while the conditions present in disjunction with the IS NULL condition are not satisfied. If the query contains an IS NULL then the dataset will give a non-empty result whereas the NOT IS NULL mutant will generate an empty result and vice versa. We also consider the mutation where the mutant query does not contain the IS NULL condition In order to kill this mutation we generate a tuple with the IS NULL condition being replaced by NOT IS NULL (with the conditions present in disjunction with the IS NULL not being satisfied). The original query gives an empty result while the mutant gives a non-empty result.

If the query contains the condition NOT IS NULL the corresponding mutations can be killed in a similar manner.

To kill the mutation from COUNT(attr) to COUNT(*), where attr is a set of attributes, we create a dataset such that all tuples in a group have attr as NULL (provided all attributes in attr are nullable and none of them is forced to be non-nullable by selection or join conditions). COUNT(attr) gives a count of 0 while COUNT(*) gives a count of equal to the total number of tuples.

In order to kill mutations of COUNT(*) to COUNT (attr), for any set of attributes attr, we create a dataset such that all nullable columns (columns that can be assigned NULL values and do not have conditions that force them to be not NULL) have NULL values. If any attribute in attr is not nullable, COUNT(*) and COUNT(attr) are equivalent mutations.

We now consider how to estimate the number of values (tuples), $n$, needed to satisfy aggregation constraints. For each attribute, $A$, on which there are aggregate constraints we consider the following for estimating $n$.

1. 1.

   Aggregation Properties: Constraint variables sum_A, min_A, max_A, avg_A, count_A respectively correspond to the results of aggregation operators SUM, MIN, MAX, AVG and COUNT on attribute $A$. Note that count_A also indicates the number of tuples at the input to the aggregation. We add the following conditions

   • •

     Since the value of each tuple cannot be less than min_A and greater than max_A, it follows that min_A$*$count_A $\leq$ sum_A $\leq$ max_A$*$count_A.

   • •

     If the domain of $A$ is integer and $A$ is unique,
     min_A + (min_A + 1) + … + (min_A + $count_{A}$ - 1) $\leq$ $sum_{A}$ $\leq$ ($max_{A}$ - $count_{A}$ + 1) + ($max_{A}$ - $count_{A}$ + 2) + … + (max_A - $count_{A}$ + ($count_{A}$ - 1)) +($max_{A}$). We use the simplified form of the above expression for constraint generation.

   • •

     $($$avg_{A}$$*$$count_{A}$$)=$ sum_A

2. 2.

   Domain Constraints: Constraint variables dmin_A, dmax_A correspond to the minimum and maximum value in the domain of attribute $A$. We add the following constraints

   • •

     dmin_A $\leq$ min_A $\leq$ max_A $\leq$ dmax_A. This constraint states that min_A cannot be less than the domain minimum or greater than max_A.

3. 3.

   Aggregation Constraints: Aggregation constraints specified by the query (e.g. sum_A $\leq$ $10$).

4. 4.

   Selection Conditions: If the query contains non-aggregate constraints on any attribute $A$, we add these to the tuple estimation constraints. For example, consider the query,

   SELECT dept_name,SUM(credits) FROM
   course INNER JOIN dept USING (dept_name)
   WHERE credits <= 4 GROUP BY dept_name
   HAVING SUM(credits) < 13
   

   Here, because credits column has a selection condition on it, its limit is constrained. Hence, max_credits $\leq 4$ is also added to the list of constraints above.

The solver returns a value for the count which satisfies all the constraints above, but the value may not be the minimum. Since we are interested in small datasets, we want the count to be as small as possible. Hence, we run CVC3 with the count fixed to different values, ranging from $1$ to MAX_TUPLES and choose the smallest value of the count for which CVC3 gives a valid answer.³³3Since we are interested in small datasets, we set MAX_TUPLES to $32$ in our experiments. We borrow the idea of calculating the number of tuples, using multiple tries, for the aggregation constraint from RQP RQP . However, note that the problem is different here, since, unlike RQP, we do not know the value of the aggregation in the query result. Note that the above procedure works even in case of multiple aggregates on the same column or on different columns.

In case the aggregate is on a single relation the number of tuples estimated is assigned to the only relation. For each result tuple generated by an aggregation operator, we create a tuple of constraint variables where group by attributes are equated to the corresponding values in the inputs and aggregation results are replaced by arithmetic expressions. For example, $sum(r.x)$ is replaced by $R[i].x+R[i+1].x+\ldots+R[i+k].x$, where R[i] to R[i+k] are the tuples assigned for a particular group. We also add constraints to ensure that no two tuples in R[i]…R[i+k] are the same if the relation has a primary key.

The tuple variables created as above can be used for other operations e.g. selection or join that use the aggregation result as inputs.

In case data generation for multiple groups is required we add constraints to ensure that at least one of the GROUP BY attributes is distinct across groups.

Consider the query,

SELECT id, COUNT(*) FROM takes
WHERE grade = ‘A+’ AND year = 2010
GROUP BY id HAVING COUNT(*) < 3

For this query the number of tuples in the group is estimated to be 1. We assign a single tuple to the takes relation and add constraints to ensure that grade for this tuple is ‘A+’ and year is 2010.

Note that if the XData system generates additional tuples for the takes relation (for example because this query is part of a subquery and there may be other instances of the takes relation outside the subquery or takes is referenced by some other relation and we need to generate additional tuples to satisfy foreign key dependencies) the value of COUNT() in the having clause may change and the constrained aggregation may no longer be satisfied. In order to ensure that the HAVING clause is not affected we need to ensure that no other tuple in the takes relation belongs to the same group.

In general, to ensure that the additional tuples generated do not cause problems we add constraints to ensure that for any additional tuple either has a different value for the GROUP BY attribute and hence belongs to a different group or fails at least one of the selection conditions. In the above example, we assert that either the id is different for the additional tuple or $year\neq 2010$. In practice, the conditions are generated by Algorithm 5 described in Appendix C, which handles the general case of aggregation on join results, to assert these constraints as described in Section 6.3.2.

In case the aggregate is on a join result we need to assign tuples to each of the relations such that the join results in the required number of tuples. In this section, we address this issue.

Techniques for killing aggregation mutations were described in xdata:icde11 (summarized in Section 2.2). A dataset to kill aggregation mutations is generated by creating multiple tuples per group using techniques of constrained aggregation described above. Different mutations of the aggregate operator will produce different values on this dataset. To ensure that the value difference due to aggregate mutation will cause a difference in the constraint aggregate result, we need to ensure that only one of the query or its mutation satisfies the aggregation constraint. For some cases, we have implemented constraints to ensure that there is a difference in the constraint aggregate result. Implementing this in general is an area of future work.

Datasets for killing mutations of comparison operators in aggregation constraint (e.g. having clause) are generated using existing techniques in XData for handling comparison mutations. Killing mutations due to additional and missing group by attributes is discussed in Section 9.2.

In this section, we consider subqueries that have aggregation. Constraints involving aggregation can be in the inner query (e.g. HAVING clause) or in outer query (e.g. r.s $<$ (SELECT agg(s.b…)))

We also need to generate test data for killing mutations in subquery blocks. For the EXISTS connective and for scalar subqueries we treat a subquery block as a normal query and generate sets of constraints to kill mutations in the subquery block. For each constraint set, we also add constraints to ensure a non-empty result on the outer query block.

For killing selection (comparison mutations, string mutations, NULL mutations), JOIN, and HAVING clause mutations the techniques described in xdata:icde11 and this paper generate datasets that produce empty result on either the query or the mutant but not both. Thus for these mutations the subquery will satisfy the EXISTS condition or the comparison operator (for scalar subqueries) for either the subquery or its mutation enabling XData to kill the mutation.

Extra tuples may get generated for the subquery if there are relations in the subquery that are repeated in the query or are referenced by other relation through foreign keys. Because of these extra tuples, an empty result may turn into a non-empty result or vice versa. To prevent this, we add constraints using Algorithm 5 described in Appendix C where (a)$T$ - query tree of the subquery (b)$AT$ - tuples created for the subquery (c)$ASel$ - correlation conditions with correlation variables being passed as parameters. The constraints ensure that the extra tuples do not affect the result of the subquery, preventing the extra tuples from turning an empty result into a non-empty result or vice versa.

In case there are disjunctions with the subquery, we add constraints to ensure that other conditions in disjunction with the subquery (e.g. P or EXISTS(Q)) are not satisfied as described in Section 2.4.

Mutations like distinct or aggregation mutation in the project clause of the subquery create equivalent mutants of the query and hence need not be killed.

If the subquery uses the NOT EXISTS connective, we generate the datasets for killing mutations in the subquery treating the NOT EXISTS as an EXISTS conditions. Out of the original query and the mutant, the query that produces empty results on the subquery satisfies the NOT EXISTS conditions and produces non-empty results for the outer query. The query that does not produce empty results does not satisfy the NOT EXISTS condition and produces an empty result in the outer query. Thus, these mutations can be killed.

Subquery connectives IN, NOT IN, ANY and ALL are converted to EXISTS and NOT EXISTS as described earlier. Mutations in the subquery are killed after the conversion.

Set queries are of the form, P SETOP Q where SETOP is a set operator, and P and Q are queries that may be simple or compound queries themselves.

In order to generate a dataset that produces a non-empty result on this query if the SETOP is UNION(ALL) we add constraints to ensure non-empty results for P or Q or both (P and Q may have conflicting constraints so for both to have non-empty results may not always be possible).

Data generation for INTERSECT(ALL) is done in a similar manner as the EXISTS subquery described in Section 7.1. We treat the query as

SELECT * FROM (P) WHERE EXISTS
    (SELECT * FROM Q WHERE pred)

where predicate pred equates each projected attribute of P to the corresponding attribute of Q. For each tuple in P, we generate a corresponding tuple in Q that satisfies the correlation condition, pred, as described in Section 7.1. Data generation for EXCEPT(ALL) is done in a similar manner using NOT EXISTS instead of EXISTS, using the techniques described earlier for the NOT EXISTS operator.

In order to kill the mutations among the different operators (UNION(ALL), INTERSECT(ALL), EXCEPT (ALL)) we generate datasets as described below (summarized in Table 2 along with the results for various set operators).

1. 1.

   Generate a dataset that has exactly one tuple $t_{1}$ for P. Add constraints to ensure that one matching tuple exists in Q.

2. 2.

   Generate a dataset that has one tuple $t_{1}$ for P. Add constraints to ensure that $t_{1}$ does not exist in Q.

3. 3.

   Generate a dataset which has at least two identical tuples $\exists^{>1}t_{1}$ for Q. Add constraints to create one matching tuple $t_{1}$ for P.

4. 4.

   Generate a dataset that has one tuple $t_{1}$ for Q. Add constraints to ensure that $t_{1}$ does not exist in P.

5. 5.

   Generate a dataset which has at least two identical tuples $\exists^{>1}t_{1}$ for Q. Add constraints to ensure not matching tuples for P.

6. 6.

   Generate a dataset that has at least two identical tuples, $\exists^{>1}t_{1}$ for P. Add constraints to ensure that there is exactly one matching tuple $t_{1}$ in Q.

7. 7.

   Generate a dataset that has at least two identical tuples, $\exists^{>1}t_{1}$ for P. Add constraints to ensure that $t_{1}$ does not exist in Q.

8. 8.

   Generate a dataset that has at least two identical tuples, $\exists^{>1}t_{1}$ for both P and Q.

We call kill a mutation between a pair of set operators if for a dataset the results of the query as shown in Table 2 differ. Note that it may not be possible to generate some datasets because of query/integrity constraints; in particular primary key constraints may prevent generation of datasets with duplicates. It may not be necessary to generate all datasets to kill all mutations. As an optimization we can stop generation of datasets if we have successfully generated at least one of the datasets for killing each of the mutations.

For both P and Q we have three options; either generate no tuple, one tuple or more than one tuple. Table 2 exhaustively covers all combinations (except for the case where both P and Q are empty, since if both P an Q are empty all operators would give an empty result and no mutation would be killed). Hence, these datasets are sufficient to kill all pairs mutations. For example the mutation between INTERSECT and INTERSECT ALL can only be killed if there is more than one matching tuple between P and Q. Dataset 8 covers this case. The only mutation that may be missed is the mutation between EXCEPT ALL and other operators except UNION ALL since for dataset 8, we cannot guarantee whether the result would be $\exists t_{1}$, $\nexists t_{1}$ or $\exists^{>1}t_{1}$. Dataset 8 would still be able to kill the mutation between UNION ALL and EXCEPT ALL since UNION ALL would produce more tuples in the result than EXCEPT ALL. Hence, if it is possible to only generate dataset 8, mutations of other operators with EXCEPT ALL may not get killed. In order to provide completeness guarantees for killing mutations involving EXCEPT ALL, we need to generate specific number of tuples for P and Q. This is an area of future work.

To ensure that a tuples of one relation does not exist in the other, constraints are added using the NOT EXISTS technique described in Algorithm 1 of Section 7.1. To ensure that a tuple in one relation exists in the other, we use the EXISTS technique described in Section 7.1.

To create at least two identical tuples in the result of a subquery, we assert constraints to imply that the number of tuples is more than one. Then using the techniques described in Section 6 for constrained aggregation we estimate the required number of tuples for each base relation. We treat the projected attributes in the select clause as the group by attributes in constrained aggregation, which ensures that these have the same value across tuples. Data generation is done using techniques for constrained aggregation described in Section 6.2 and Section 6.3.2.

We also need to kill mutations in the input to the set operator. For this, we need to ensure that the effect of the mutation makes a difference in the result of the set operator.

If the set operator is UNION/UNION ALL and the mutation to the query is in P, we add constraints to ensure that the mutation in P is killed. In addition to ensure that there are no tuples from Q that mask the changes in the result we add constraints similar to NOT EXISTS subquery for Q. Similarly data generation can be done for killing mutations in Q.

We treat INTERSECT and EXCEPT queries as EXISTS and NOT EXISTS respectively as described earlier. Mutations of P can be killed by datasets to kill mutations of the outer query block while the mutations in Q can be killing by killing mutations in the subquery block as described in Section 7.4.

Consider the tables student (id, name, dept_name), course (course_id, course_name and dept_name) and takes (id, course_id, sec_id, semester, year) from the University schema in dbconcepts2010 . Consider the query,

SELECT course_id,course_name
FROM student INNER JOIN takes ON(id)
INNER JOIN course ON(course_id)
WHERE student.id = 1234

SELECT course_id,course_name
FROM student INNER JOIN takes ON(id)
INNER JOIN course ON(course_id, dept_name)
WHERE student.id = 1234

Such errors are common when using natural joins. For example, if natural join was used in place of .. INNER JOIN course ON(course_id) resulting in student.dept_name being equal to course.dept_name.

In order to kill such mutations, we do the following. Let the relations being joined be $R_{i}$ and $R_{j}$. For every attribute $p\in R_{i}$ such that (a) there is an attribute $q\in R_{j}$ with identical names and (b) there is no join condition involving $p$ and $q$ in the original query, we assert that the values held by the two attributes are not equal. The original query without the join condition would give a non-empty result while the mutation would give an empty result.

Similarly, there could be mutants such that the mutant query contains some missing join conditions. Such mutations can be killed by the datasets that kill join type mutations (INNER / LEFT OUTER / RIGHT OUTER) described in Section 2.2.

In this section, we discuss the mutation of the query due to the presence of additional attributes or absence of some attributes in the group by clause.

Users may erroneously omit the DISTINCT keyword in the projection list of a select clause. For example, consider the following query from dbconcepts2010 that finds the department names of all instructors.
SELECT DISTINCT dept_name FROM instructor

In this query, the absence of the DISTINCT keyword would lead to the same department name being repeated which is not desired. We term mutations that add or delete the DISTINCT keyword to the select as distinct mutations (DISTINCT of aggregates is covered in Section 2.2). To kill such mutations we need a dataset which results in at least two tuples in the output such that these tuples are identical on the projected attributes. We use the technique described in Section 8.2 for generating tuples with identical projected attributes. For such a dataset, the query with the DISTINCT keyword will give only a single tuple as output while the one without, will give at least two tuples.

In case the constraints are not satisfiable, it is not possible to have multiple tuples with the same value of the projected attribute(s). This could happen if one of the projected attributes is a primary key for the input to the DISTINCT clause or if the projected attributes are also used as GROUP BY attributes in the same query block. For such cases, the DISTINCT mutation is equivalent.

In Section 6.3.1 we described our approach for estimating the number of tuples for the purpose of data generation for queries containing constrained aggregation. In this section, we provide experimental results on the estimation of number of tuples per relation and subsequent data generation for a number of queries containing constrained aggregation. The objective is to see if the tuple assignment technique (Section 6.3.1) assigns tuples in a manner that (a) can produce datasets to generate to non-empty result on the original query (this is the first dataset as mentioned in Section 2) and (b) kill mutations related to the HAVING clause (aggregate mutation and comparison operator mutation of the HAVING clause).

For this experiment, queries which involve constraints on aggregate operators along with one or more GROUP BY attributes were chosen. (The list of queries is provided in Appendix E.) Aggregates in both outer query block and subqueries are considered. We also manually generated non-equivalent mutations by mutating the comparison operator (20 mutations) and aggregate operator (16 mutations) for the chosen queries, to test if the datasets could kill these mutations⁵⁵5We do not use any automated tool to generate mutations. The mutations generated by an automated tool may or may not be equivalent to the original query. If our dataset fails to kill some of the mutations we would not be sure if that was because of the incompleteness of our tool or because of equivalence of mutation and the original query..

The results are shown in Table 3. For each constrained aggregation, the Tuples column shows the number of tuples assigned to each base relation. The columns Comparison Mutations and Aggregate Mutations show if all the non-equivalent mutations of comparison operator in HAVING clause and aggregate mutation respectively were killed by the generated datasets or not. Query CA8 had two constrained aggregations, one in a subquery and one in the outer query block which are labeled as CA8a and CA8b respectively.

The datasets generated by XData was able to produce non-empty results on all queries. In terms of killing mutations, 35 out of the 36 mutations were killed. The mutation from MAX to MIN was not caught for Test Case CA2. For killing mutation on MAX to MIN we need two distinct tuples, one which satisfies the aggregate constraint and one which does not. Our tuple assignment method assigned only one tuple to the relation that had the MAX aggregate value and hence this mutation was not caught. Handling such cases is an area of future work.

In Section 7 we described various techniques for generating test data and killing mutations for queries containing where clause subqueries. For this experiment, we chose queries involving various subquery connectives both with and without aggregates (The list of all queries is provided in Appendix E) and check to see if XData is able to generate a dataset that produces non-empty result on the original query. We also manually generated non-equivalent mutants by mutating the subquery connective (20 mutations) and the conditions in the subquery (20 mutations) to test if the datasets could kill these mutations⁵.

For all the queries considered XData could generate a dataset that produced non-empty result on the original query. The datasets generated by XData were able to kill all of the 40 query mutations that we considered.

We also tried generating test data and killing mutations for queries from the TPC-H benchmark. We asked a few volunteers (who had not worked on the XData project) to generate specific types of query mutants. We tested to check if the datasets generated by XData could kill these mutations or not. In case, XData was not able to kill the mutations we examined to check if the mutant was equivalent to the original query or not. We only used non-equivalent mutants for measuring the performance of XData.

Since our parser did not support certain query constructs we made minor changes (mainly syntactic) to the queries so that it could be parsed and the datasets could be generated. However, for checking whether the datasets generated a non-empty result or not, and for generation and killing of mutations we used the original queries.

We were able to successfully generate datasets for 17 out of the 22 queries. Of the 5 queries for which our techniques failed to generate correct datasets, 4 queries had query constructs which are not currently handled (subqueries that have aggregates with expressions, aggregate value compared to a subquery and aggregate in a from clause subquery). One query failed because the CVC3 solver crashed while generating datasets for that query. Extending our system to handle these construct and migrating to newer version of CVC or using a different solver such as Z3 is an area of ongoing work.

The number of the different types of mutations killed across all queries is shown in Table 4. In addition to the mutations that our techniques explicitly target, we also tested queries with mutations of arithmetic expressions (replacing one arithmetic operator with another).

Overall XData was able to kill over 95% of the non-equivalent mutants that we obtained. For TPCH query 4, XData could not generate a dataset for killing extra group by attribute mutations and hence the corresponding mutation was not caught. Of the 13 queries with arithmetic operator mutations all but one were killed even though we do not explicitly target these mutations; explicitly targeting them is an area of future work.

We use the tool described in Section 11 to grade student queries. In order to compare grading done by XData to fixed datasets and the grading done by TAs, we used 14 SQL assignments, each of which was answered by students of an undergraduate database course at IIT Bombay. We omit questions which asked students to create DDL statements.

For each question, a correct SQL query $CQi$ was used to generate datasets. The correct SQL queries are shown in Appendix E. For the 9th assignment question, the query could be written in 2 quite different ways which we denote CQ9a and CQ9b; we generate datasets for both query formulations, and the results for CQ9 are using the combined sets of datasets. Query $CQ3$ was assigned at a point in the course where students had not been taught about the DISTINCT clause, and hence we set the testing tool parameters to ignore duplicates in the results of the correct query and the student query.

The time taken for generating all the datasets for these queries (including the time taken by our code and the CVC3 solver) ranged from 11 to 90 seconds, on a computer with an Intel(R) Core(TM) i5-2500K 3.30GHz CPU, and 8 GB of memory, running Ubuntu. The number of datasets generated ranged from 2 for CQ1 to 25 for CQ9a. Each dataset had a very small number of tuples, typically less than 5 per relation. The maximum number of tuples for a relation was 16.

As comparison points, we also tested the queries with two sample University databases provided with the textbook by Silberschatz et al. dbconcepts2010 , and with the result of manual correction by course TAs. The first University database, which we call USm is a small database which was manually created by the authors of dbconcepts2010 to catch common errors; the second larger database, which we call ULg is a larger database. The TAs used a combination of testing against sample databases they created, and their own reading of the queries.

We also tried an alternate way to grade student queries, by comparing the optimal query plans of the correct query with the optimal query plans for the student queries. If the plans match we flag the query as correct. We use PostgreSQL with the VERBOSE flag set to ensure that we get projected attributes of the query as well. Note that equivalent queries may not have identical plans. For example, a condition $x>3$ is equivalent to $x>=2$ when $x$ is an integer, but plans using these alternatives would be considered different. Also, the optimizer could find different plans for different ways of expressing the same query (especially true with subqueries). In our experiments we found that most of the student queries did not have the same plan as the correct query, even if they were correct (verified manually on sample cases). For CQ3 the optimizer chose different join plans and hence most of the queries did not match. Same was the case with CQ7, CQ8, CQ13 and CQ14. For these queries, less than 5% of the queries were marked correct.

The result of the evaluations is shown in Figure 1. Detailed evaluation is shown in Table 7 in Appendix E. For XData, USm and ULg the query is marked as incorrect iff there is a dataset that produces different results on the correct query and the student query. Hence for these methods we can guarantee that a student query is marked incorrect only when it is not equivalent to the correct query. Consequently, the number of queries marked incorrect can be used as a measure of the effectiveness of the technique. We also tried to use the combination of USm and ULg grade queries. The number of incorrect queries caught turned out to be the maximum of the number of incorrect queries caught by USm and ULg.

These results indicate that, overall, the datasets generated by XData were able to catch more incorrect queries than both USm and ULg, the two University datasets from dbconcepts2010 . For CQ5, CQ8 and CQ14, in particular, our tool was significantly more efficient than the University datasets.

As compared to TAs, our datasets performed significantly better on many queries, including CQ3, CQ4, CQ5, CQ6, CQ8 and CQ9. The actual effectiveness of TAs is a little better than what the table indicates, since there were some queries where students made minor errors such as including extra attributes, which the TAs decided to ignore as irrelevant, but which were caught by all the datasets.⁶⁶6If students had been told that their queries would be graded by a tool, they would have probably taken more care to avoid such errors.

For query CQ5 and CQ8, some students had performed joins on the wrong tables, but these queries gave a correct result on datasets created by the TAs for checking the queries, and were marked as correct.

For CQ8, the University dataset did not have any course taught by two different instructors in Spring 2010, and hence a missing distinct keyword in the select clause was not detected. The TAs too did not enforce the check for distinct, which was required for this query.

In contrast, for CQ4, the University dataset USm had a student who had taken CS-101 twice and hence performed as well as XData. Again, the TAs had ignored the absence of a distinct specification. For CQ5, again the University datasets USm and ULg both had some courses with two sections, which caught missing distinct specifications; in this case the TAs did check for the presence of the distinct specification.

For CQ9a a large number of incorrect queries were caught by XData based on missing group by attributes and missing distinct clause. For CQ14, the data generation and mutation killing technique for NOT EXISTS was essential for catching a large number of student query errors.