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

    String sql = "SELECT [car:maker] FROM [car:Car] WHERE PATH() LIKE '%/Hybrid/%'";
    Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2);
    QueryResult result = query.execute();
    validateQuery().rowCount(3).hasColumns("car:maker").validate(query, result);
    sql = "SELECT * FROM [car:Car] WHERE [car:maker] IN ('Toyota','Nissan')";
    query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2);
    result = query.execute();
    // the 4 kinds of cars made by makers that make hybrids
    validateQuery().rowCount(4).hasColumns(carColumnNames("car:Car")).validate(query, result);
    sql = "SELECT * FROM [car:Car] WHERE [car:maker] IN (SELECT [car:maker] FROM [car:Car] WHERE PATH() LIKE '%/Hybrid/%')";
    query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2);
    result = query.execute();
    // the 4 kinds of cars made by makers that make hybrids
    validateQuery().rowCount(4).hasColumns(carColumnNames("car:Car")).validate(query, result);
}