SQLMutation - Release Notes & New Features

Release Notes

Version Date Changes 2017.02.25 Build update (Java 8/IKVM OpenJDK 7u6)
2012.07.30 spr.25193 Operator type IRD
1.2.69 2011.05.24 rfe.24955 LIMIT keyword in MySQL
rfe.24956 FULL OUTER JOIN in MySQL
rfe.24966 Remove blank before function argument
1.2.65 2010.05.03 Derived tables in FROM clauses
SQL86 inner and outer joins 2009.10.25 INTERVAL data type and EXTRACT function
1.2.63 2009.08.05 rfe.24540 NL mutants for non nullable attributes in Left/Right Outer Increment
rfe.24541 Equivalent mutant detection mutants in joins when where conditions can't return any LOI/ROI column
rfe.24576 Equivalent mutants in GROUP BY clause
1.2.61 2009.03.23 New release (supersedes 1.2.53)
Oracle SYSDATE (spr.24533) and SQLServer CONVERT
1.2.59 2009.01.09 System Variables supported
Detection of equivalent mutants in joins with referential integrity
Some additional options added to the web service
1.2.57 2008.06.09 Stand-alone tool for generating the database schema in xml: XDBSchema
Case sensitivity in table/column identifiers reviewed
Support for qualified table names
Support for Oracle named parameters
1.2.54 2007.12.21 Support for nested joins
spr.23992 Column scope in order by with union
spr.24047 Wrong IR replacement in union of different tables with same alias
1.2.53 2007.12.03 Web service support for SOAP 1.2 and Basic WS-I Profile 1.0
Support for table and column identifiers enclosed in double quotes
spr.24062/4 Incorrect parse of literal string when not followed by space or close to unknown keyword
1.1.50 2007.03.16 Case expressions (CASE, COALESCE, NULLIF)
Cast specifications
String concatenation
ABS and ROUND functions. Unknown functions
Data compression in Web service returned mutants
1.1.48 2007.01.04 SQL92 date and time literals
More SQL92, SQL Server and Oracle datatypes
Performance improvements
1.1.46 2006.08.11 Web service released
1.1.45 2006.07.24 Conformance with HTML 4.01 transitional and W3C-WAI Accessibility Level Double-A
spr.23504 Parse error in count() within ORDER BY with DESC
1.1.42 2006.05.12 Web interface released
1.0.29 2005.06.28 First version of the SQL Mutants

New Features

In addition to the features originally described in the article Mutating Database Queries, the latest version supports the following features:

SQL Features and Mutants

Qualified table names

The general form of a table identifier is a qualfied name in the form [[catalog.]schema.]name. Usually, a given catalog and schema are taken by default so as in most of queries table identifiers are not qualified. As of version qualified table names are supported. For instance, if you declare a table name as myschema.mytable, you may refer to a table like myschema.mytable or a column like myschema.mytable.mycolumn in the queries.

Case sensitivity of table/column identifiers

Table and column names are case insensitive, however in previous versions the table and column identifieres were changed to upppercase. As of version identifiers are still case insensitive, but the case of each identifier is preserved as declared in the query and/or schema. Quoted identifiers (enclosed in double quotes) are also supported

Case expressions

A new mutation operator CSE (Case expressions) has been defined for:

Cast specifications


In addition to the aggregate functions (set function types) the following functions are supported:

Unknown Functions

Any other function-like expression in the form f(args) such that fis neither an aggregate function nor any of the above functions is denoted as an unknown function and do not causes a parse error. Each of the args expressions (arguments) is mutated as usual. The result of f is assumed to have an unknown data type and not nullable. Unkwnown functions without arguments are supported as well (provided that they include the brackets).

String concatenation

The string concatenation using the concatenation operator (| |) is mutated by applying the leftop and rightop operator. The SQL Server syntax (using a plus operator) is also supported.

NL mutants for non nullable attributes in the LOI or ROI

Consider a join on two tables: table1 LEFT JOIN table2 on table1.a=table2.b. If there is a WHERE clause which allows rows in the outer increment and references any column in the outer increment such as WHERE table1.a='a' OR table2.b='b', the value table2.b may be null even if it is not nullable. In this case the NL mutants are generated for table2.b.

Derived tables in FROM clauses

A derived query is a query specification that behaves like a physical table or view. Derived tables are now supported and mutated in the same way than the main query.

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

SQL86 inner and outer joins

The SQL86 standard specifies the inner joins by separating the tables by commas and specifying the join conditions in the where. When a query includes this type of joins, it is mutated in the usual way and other mutants are added for the join. These mutants are obtained by applyiing the usual join operators to a transformed query which replaces every SQL86 join by its SQL92 equivalent (the join conditions are removed from the WHERE and an INNER JOIN with the join conditions is placed instead of the comma. The kind of join conditions supported are equijoins (one or more conditions in the form a=b, where a and b are columns that belong to each of the joined queries.

The SQL86 standard does not specify outer joins. However, there are some DBMS vendor specific ways to indicate outer joins. 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 mutated in the same way than the SQL86 inner joins, but the join is first transformed into LEFT or RIGHT.

Equivalent mutants

Equivalent mutants in joins with foreign keys

Assume that tables T and U are joined, and there is a foreign key on the joined columns from T to U. Then the following equivalent mutants are detected:

A symmetric situation is given for a foreign key on the joined columns U to T.

Equivalent mutants in joins when where conditions can't return any LOI/ROI column

Consider a join on two tables: table1 INNER JOIN table2. If the WHERE clause requires a value in table2.x to be true or false (but not null), the mutation to LEFT JOIN is equivalent, as it returns all rows from the INNER JOIN, and does not return any row from the Left Outer Increment (LOI) because of table2.x must be not null in order to satisfy the WHERE condition.

Equivalent mutants in GROUP BY

If for any table the  attributes in the GROUP BY contain all its primary keys and some other non key attributes, the mutants are not generated for these on key attributes (because they are dependent on the primary keys).

Platform dependent features

System variables


If the schema has been defined with the attribute dbms="oracle" the following System Variables and Oracle Pseudocolumns are supported: USER, CURRENT_DATE, CURRENT_TIMESTAMP, ROWNUM, ROWID, SYSDATE.

If the schema has been defined with the attribute dbms="sql server" the following System Variables and SQL Server System Functions are supported: CURRENT_USER, USER, SESSION_USER, SYSTEM_USER, CURRENT_TIMESTAMP.


IRD Operator

SQLMutation tool calls IRD the operator that is called IRH in the article Mutating Database Queries