Database-centric systems strongly rely on SQL queries to manage and manipulate their data. These SQL commands can range from very simple selections to queries that involve several tables, subqueries, and grouping operations. And, as with any important piece of code, developers should properly test SQL queries. In order to completely test a SQL query, developers need to create test data that exercise all possible coverage targets in a query, e.g., JOINs and WHERE predicates. And indeed, this task can be challenging and time-consuming for complex queries. Previous studies have modeled the problem of generating test data as a constraint satisfaction problem and, with the help of SAT solvers, generate the required data. However, such approaches have strong limitations, such as partial support for queries with JOINs, subqueries, and strings (which are commonly used in SQL queries). In this paper, we model test data generation for SQL queries as a search-based problem. Then, we devise and evaluate three different approaches based on random search, biased random search, and genetic algorithms (GAs). The GA, in particular, uses a fitness function based on information extracted from the physical query plan of a database engine as search guidance. We then evaluate each approach in 2,135 queries extracted from three open source software and one industrial software system. Our results show that GA is able to completely cover 98.6% of all queries in the dataset, requiring only a few seconds for each query. Moreover, it does not suffer from the limitations affecting state-of-the art techniques.

Software testing is an important, well-researched field.
With the majority of modern-day applications using relational databases to manipulate their data, it is crucial that database interactions are tested as well.
This is a complex task to perform manually, and thus researchers have been attempting to tackle this problem by means of automated test data generation.
In their studies, they apply constraint-based techniques using SAT solvers to generate the test data.
However, these techniques have known limitations such as solving subqueries.

In this thesis, we present a novel search-based approach that uses a Genetic Algorithm to generate test data for SQL queries, which overcomes the limitations of previous research.
We provide an implementation of our approach, EvoSQL.
In our implementation, we instrument a real database to extract all the information necessary for the fitness function. By doing so, we support all queries using standard SQL syntax.
We evaluate our approach on 2,135 queries from 4 real-world systems, of which EvoSQL is able to cover over 96% fully.