AEM 6.3 | Predicate search | issue with orderby

[MKBansal]

Hello Experts,

We are in process to upgrade our site from 6.2 to 6.3. While testing search feature on 6.3 we have noticed that resultset sorting stopped working. Though the same code/query is working on 6.2.

We are using Predicate query builder to create our query.

private static List<Predicate> getSortOrderPredicateGroup(final List<SortCriteria> sortCriterias) {
        final List<Predicate> sortPredicates = new ArrayList<Predicate>();
        int count = 0;
        for (final SortCriteria sortCriteria : sortCriterias) {
            count++;
            final Predicate sortPredicate = new Predicate(SearchConstants.PREDICATE_PREFIX_SORT_ORDER + count,
                    SearchConstants.PREDICATE_TYPE_ORDERBY);
            sortPredicate.set(SearchConstants.PREDICATE_TYPE_ORDERBY, sortCriteria.getProperty());
            sortPredicate.set(SearchConstants.PREDICATE_PARAM_SORT, sortCriteria.getOrder().getValue());
            sortPredicates.add(sortPredicate);
        }
        return sortPredicates;
    }

and then we add this sortOrder predicate in query

query.getPredicates().addAll(getSortOrderPredicateGroup(searchParameters.getSortOrder()));

The predicate query generated is

FilterFlagGroup.Property_1=jcr%3acontent%2fhideInSitemap
FilterFlagGroup.Property_1.operation=contains
FilterFlagGroup.Property_1.value=true
FilterFlagGroup.Property_2=..%2f..%2fjcr%3acontent%2fjcr%3aprimaryType
FilterFlagGroup.Property_2.operation=contains
FilterFlagGroup.Property_2.value=dam%3aAssetContent
FilterFlagGroup.p.not=true
FilterFlagGroup.p.or=true
KeywordGroup.FullText_Keyword_1=%22insurance%22
KeywordGroup.FullText_Keyword_1.relPath=.
KeywordGroup.p.or=true
PathGroup.Path_1=%2fcontent%2fweretail%2fen
PathGroup.Path_2=%2fcontent%2fdam%2fweretail%2fdocuments
PathGroup.p.or=true
SortOrder_1=%40jcr%3acontent%2fjcr%3alastModified
SortOrder_1.sort=desc
TypeGroup.Type_1=cq%3aPage
TypeGroup.Type_2=dam%3aAsset
TypeGroup.p.or=true
p.limit=10
p.offset=0

The SQL query created using above predicate query (in 6.3) is:

select [jcr:path], [jcr:score], * from [cq:Page] as a where contains(*, '"insurance"') and not([jcr:content/hideInSitemap] = true or [../../jcr:content/jcr:primaryType] = 'dam:AssetContent') and isdescendantnode(a, '/content/weretail/en') union select [jcr:path], [jcr:score], * from [cq:Page] as a where contains(*, '"insurance"') and not([jcr:content/hideInSitemap] = true or [../../jcr:content/jcr:primaryType] = 'dam:AssetContent') and isdescendantnode(a, '/content/dam/weretail/documents') 
order by [jcr:content/jcr:lastModified] desc 
union(*)select [jcr:path], [jcr:score], * from [dam:Asset] as a where contains(*, '"insurance"') and not([jcr:content/hideInSitemap] = true or [../../jcr:content/jcr:primaryType] = 'dam:AssetContent') and isdescendantnode(a, '/content/weretail/en') 
order by [jcr:content/jcr:lastModified] desc 
union select [jcr:path], [jcr:score], * from [dam:Asset] as a where contains(*, '"insurance"') and not([jcr:content/hideInSitemap] = true or [../../jcr:content/jcr:primaryType] = 'dam:AssetContent') and isdescendantnode(a, '/content/dam/weretail/documents') 
order by [jcr:content/jcr:lastModified] desc

As you notice, the generated SQL query has two issues:

1. Orderby is added 3 times instead of one time for entire query (line 2, 4, 6)
2. Line 3, union is followed by unwanted (*)

I am not able to figure out if we need to change our predicate to correct this behavior or its a defect. Any help is really appreciated.

Regards

Mohit Bansal