I have a tree structure of /content/yyyy/mm/dd where article pages reside under the dd directory.
If I execute this query in crx/de:

you can see it returns 196 nodes/pages. If I execute the query using this block of code:
QueryManager queryManager;
QueryResult articleCollection;
queryManager = session.getWorkspace().getQueryManager();
javax.jcr.query.Query query;
query = queryManager.createQuery(sqlStatement, javax.jcr.query.Query.JCR_SQL2);
articleCollection = query.execute();
return articleCollection;
then -1 articles are returned. However, if I move all the articles to a single day, say 2022/9/1 and specify '/content/2022/9/1' as the root then the Java query will return 196 results.
It appears the Java version of the query isn't actually descending and picking up child nodes. Am I missing something?