@FixFor( "MODE-869" )
@Test
public void shouldBeAbleToCreateAndExecuteJcrSql2QueryWithSubqueryInCriteria() throws RepositoryException {

    String sql = "SELECT * FROM [car:Car] WHERE [car:maker] IN (SELECT [car:maker] FROM [car:Car] WHERE [car:year] >= 2008)";
    Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2);
    QueryResult result = query.execute();
    // there are 13 cars made by makers that made cars in or after 2008
    // The only car made before 2008 is Toyota Land Rover, but Toyota also makes the Highlander and Prius in 2008.
    validateQuery().rowCount(13).hasColumns(carColumnNames("car:Car")).validate(query, result);
}