@FixFor( "MODE-1873" )
@Test
public void shouldBeAbleToCreateAndExecuteJcrSql2QueryWithSubqueryInCriteriaWhenSubquerySelectsPseudoColumn()
    throws RepositoryException {

    String sql = "SELECT [jcr:path] FROM [nt:unstructured] WHERE PATH() LIKE '/Other/%'";
    Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2);
    QueryResult result = query.execute();
    validateQuery().rowCount(4).hasColumns("jcr:path").validate(query, result);
    sql = "SELECT [jcr:path],[pathProperty] FROM [nt:unstructured] WHERE [pathProperty] = '/Other/NodeA'";
    query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2);
    result = query.execute();
    validateQuery().rowCount(1).hasColumns("jcr:path", "pathProperty").validate(query, result);
    sql = "SELECT [jcr:path],[pathProperty] FROM [nt:unstructured] WHERE [pathProperty] IN ('/Other/NodeA[2]', '/Other/NodeA', '/Other/NodeC', '/Other/NodeA[3]')";
    query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2);
    result = query.execute();
    validateQuery().rowCount(1).hasColumns("jcr:path", "pathProperty").validate(query, result);
    sql = "SELECT [jcr:path] FROM [nt:unstructured] WHERE [pathProperty] IN (SELECT [jcr:path] FROM [nt:unstructured] WHERE PATH() LIKE '/Other/%')";
    query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2);
    result = query.execute();
    validateQuery().rowCount(1).hasColumns("jcr:path").validate(query, result);
}