SQL 2 query

Hi Team,

On v6.4.4.0, I am running a simple SQL query in crx/de tools and I get the expected results.

select * from [cq:PageContent] where ISDESCENDANTNODE('/content/xxx/xxx/xxx') AND [customProperty] IS NOT NULL

When I do the same with my servlet, I am getting "No pages found matching the query" exception. Below is the snippet:

String queryStr =  "select * from [cq:PageContent] where ISDESCENDANTNODE('"+ path + "') AND [" + customProperty + "] IS NOT NULL";

try {

    Query query =  resourceResolver.adaptTo(Session.class).getWorkspace().getQueryManager().createQuery(queryStr, Query.JCR_SQL2);

    TagManager tagManager = resourceResolver.adaptTo(TagManager.class);

    QueryResult results = query.execute();

    NodeIterator resultsNodeIterator = results.getNodes();

    if (resultsNodeIterator.getSize() > 0) {

        while (resultsNodeIterator.hasNext()) {

            Node pageContentNode = resultsNodeIterator.nextNode();

            migrateTags(pageContentNode, tagManager);

        }

    } else {

        logger.info("No pages found matching the query {}", queryStr);

    }

} catch (RepositoryException e) {

    logger.error("Error while running query {}", queryStr);

}

I have tried getting resource from request (logged in as admin) and also used system user with both read and write access. But none of them worked.

One observation is the script only works when there are less than 100 pages in the results (QueryResult results = query.execute();) and when there are more results, ex: 400 pages, it directly falls into the else block.

I was seeing "consider creating an index or changing the query" in the error.log, so I updated the oak:indexes by adding my property to "/oak:index/cqPageLucene/indexRules/cq:Page/properties" and also tried my custom oak:index here "/oak:index/custom/indexRules/cq:PageContent/properties". I was seeing indexing was complete

"

*INFO* [async-index-update-async] org.apache.jackrabbit.oak.plugins.index.IndexUpdate Reindexing completed

*INFO* [async-index-update-async] org.apache.jackrabbit.oak.plugins.index.AsyncIndexUpdate [async] Reindexing completed for indexes: [/oak:index/custom*(0)] in 2.207 s"

And when I try to hit the servlet, I dont see "consider creating an index or changing the query" exception anymore, but I get the same exception from the script. I ran out of options, I am not sure whats missing and confused why the query will work on crx/de > tools and not in my script.

Thanks in advance for any suggestions.