SQLFpc - Generation of Full Predicate Coverage Rules for testing SQL database queries (MCDC for SQL)

SQLFpc is a coverage criterion for SQL database queries. It is based on Modified Condition Decision Coverage (MCDC) for SQL, which takes into account a wide range of the syntax and semantics of SQL, including selection, joining, grouping, aggregations, subqueries, case expressions and null values. The criterion assesses the coverage of the test data in relation to the query that is executed and it is expressed as a set of rules that are automatically generated and efficiently evaluated against a (even large) test database.

The SQLFpc tool is used to generate the coverage rules. It can be used from a browser using a simple Web interface, or from other applications that consume a Web Service. The user only has to specify the SQL query and some information about the database schema.

The coverage rules and the tool are described in the following article:

This is an experimental system intended to be used by the researchers in database applications testing. We welcome feedback and comments about errors encountered or suggestions for its improvement. You can submit a Request for Enhancement or Software Problem Report using the Web interface.

Release Notes

In addition to the features originally described in the article, the latest supports the following features:

Version Date Changes 2017.02.25 Build update (Java 8/IKVM OpenJDK 7u6) 2015.06.28 rfe.26366 to 26371 Filters out a number of unsolvable conditions due to coupled conditions and/or joins (if nounsolvable option is set)
1.1.69 2011.05.24 rfe.24955 LIMIT keyword in MySQL
rfe.24966 Remove blank before function argument
1.1.66 2010.06.25 Groups in queries without GROUP BY
1.1.65 2010.05.03 Derived column list in derived tables
SQL86 outer joins
1.1.64 2009.11.18 Minor updates
1.1.63 2009.08.05 First public version
1.0.60 2009.02.28 Preliminary version

Derived column list in derived tables

A derived query may have a table alias and a derived column list in the form: ( <derived_query> ) [ [AS] <table_alias> [ ( <derived_column_list> ) ] ]

SQL86 outer joins

In the original version, when a SQL86 join is encountered, the query is first transformed by replacing every SQL86 join by its SQL92 equivalent inner join (the join conditions are removed from the WHERE and an INNER JOIN with the join conditions is placed instead of the comma. The SQL86 standard does not specify outer joins. However, there are some DBMS vendor specific ways to indicate outer joins which are now supported. Given a join condition in the WHERE over columns a and b of the joined tables, the following outer joins are supported:

These joins are transformed in the same way than the SQL 86 inner joins, but the join is first transformed into LEFT or RIGHT.

Groups in queries without GROUP BY

Queries with aggregate functions in the select list but not a GROUP BY clause allways generate a single group. In previous versions the generated coverage rules didn't transform the select list, leading to rules that returned a row even if they where not covered. As of version 1.1.66, for these queries the select list is modified leading to rules that can be evaluated in the same way than other rules.