AEM 6.4.8.1 - SQL2 request with no result

[#1]

Hello everybody,

We currently got an issue in our instance of AEM. We are currently on AEM 6.4.8.1.

Today to get some data we make a request in our code :

SELECT * FROM [nt:base] WHERE ISDESCENDANTNODE('/etc/commerce/nicephore/catalog/MSegment') AND [cnp:type] = 'Parfums'

When we execute the query through the crx we got some results, it's okay. However today when we execute the query through the code the result is 0, we don't have any result.

We have currently an index for the property cnp:type and we already reindex it but it didn't change anything. We already checked the acl and right and this is not the issue. We don't understand why the same executed query (in crx and in our code) send us different result. We don't have any logs when the query is called.

Here is the code we use to make the query :

Session session = resourceResolver.adaptTo(Session.class);
queryManager = session.getWorkspace().getQueryManager();
StringBuilder queryStrLigne = new StringBuilder("SELECT * FROM [nt:base] WHERE ISDESCENDANTNODE('" + COMMERCE_SEGMENT + "') AND [cnp:type] = 'Parfums'");
Query queryAsset = queryManager.createQuery(queryStrLigne.toString(), Query.JCR_SQL2);
QueryResult result = queryAsset.execute();

What could cause this issue ? If you have any idea it would help a lot.
Thank you.

[#2 · Accepted solution]

Hi @JimmyC148 , 

This seems like user permission issue. 

When you execute the query directly , you must be logged in as admin user or user with permission to query the specific path which you have mentioned. 

When you are trying the same query via program ,  access/permission for the user seems like limited which encountered 0 result. 

If you are using the service user please have a look if the user has required permission for the mentioned path. 

[#3]

You need to get a session using SlingRespository, it will not work with just adapting from resource Resolver.

Example

https://github.com/arunpatidar02/aem63app-repo/blob/master/java/SimpleSQL2SearchServlet.java

[#4]

A couple things:

1. I highly recommend not using SQL2 directly like this and instead using QueryBuilder.  In your case, the path and property predicates can be used as follows:
   1. path=/etc/commerce/nicephore/catalog/MSegment
   2. property=cnp:type
   3. property.value=Parfums
2. As a sanity check, install ACS Tools on your instance and force a reindex of the index you mentioned to rule out the index being the issue:  https://adobe-consulting-services.github.io/acs-aem-commons/features/ensure-oak-index/index.html.  Note that you'll need to install a version compatible 6.4.8 that has the Ensure Oak Index feature as they removed this feature from versions supported AEM as a Cloud Service.

Note that QueryBuilder uses the faster of XPath or SQL under the hood anyway but QueryBuilder use is the best practice for querying in AEM.

[#5]

Hi @JimmyC148,

When you execute the query from CRX, it should have been in admin login or any user who has access to the path which we are querying. (In this case, /etc/commerce/...)

In the code snippet you have shared, can you let know details on how you are retrieving the resourceResolver object. (first line in the snippet)

If you are using Service User then check if the service user is available in the instance with desired permissions.