[BUG] Spring data cosmos db CosmosTemplate pagination query returns wrong document count

[zhoujia1974]

Describe the bug When using CosmosTemplate.runPaginationQuery method to get a page of documents, if the query string provided using collection join and distinct, the count returned is wrong. public <T> Page<T> runPaginationQuery(SqlQuerySpec querySpec, Pageable pageable, Class<?> domainType, Class<T> returnType) Consider the following example document. { "id": "111", "partitionKey": "AA", "child": [ { "name": "John" }, { "name": "Jason" } ] } If the query is "SELECT DISTINCT c.id FROM c JOIN i IN c.child", the count should return 1. But runPaginationQuery returns count as 2. The reason it returns 2 is that when it builds the count query, it removes the string before FROM and then prefix the remaining string with "select value count(1) ". This ends up with a query "select value count(1) FROM c JOIN i IN c.child" which counts all child collections instead of distinct parent collection. The right way to build the count query should be wrapping the provided query as a subquery such as "select value count(1) from (SELECT DISTINCT c.id FROM c JOIN i IN c.child)"

See NativeQueryGenerator.generateCountQuery(SqlQuerySpec querySpec) method.

Exception or Stack Trace Add the exception log and stack trace if available

To Reproduce Steps to reproduce the behavior:

Code Snippet Add the code snippet that causes the issue.

public SqlQuerySpec generateCountQuery(SqlQuerySpec querySpec) { String queryText = querySpec.getQueryText(); int fromIndex = queryText.toLowerCase(Locale.US).indexOf(" from "); Assert.isTrue(fromIndex >= 0, "query missing from keyword, query=" + queryText); String countQueryText = "select value count(1) " + queryText.substring(fromIndex); return cloneWithQueryText(querySpec, countQueryText); }

Expected behavior A clear and concise description of what you expected to happen.

Screenshots If applicable, add screenshots to help explain your problem.

Setup (please complete the following information):

• OS: windows/linux
• IDE: eclipse
• Library/Libraries: com.azure.spring:azure-spring-boot-starter-cosmos:3.6.0
• Java version: 11
• App Server/Environment: Tomcat
• Frameworks: Spring Boot

If you suspect a dependency version mismatch (e.g. you see NoClassDefFoundError, NoSuchMethodError or similar), please provide

• verbose dependency tree (mvn dependency:tree -Dverbose)

Additional context Add any other context about the problem here.

Information Checklist Kindly make sure that you have added all the following information above and checkoff the required fields otherwise we will treat the issuer as an incomplete report

• [X ] Bug Description Added
• [ ] Repro Steps Added
• [ ] Setup information Added

[kushagraThapar]

Thank you @zhoujia1974 for creating this issue, we will investigate this soon. Meanwhile, if you have a solution, I would encourage you to submit a PR and I will collaborate with you. That way, we can get it out sooner. Let me know if that works for you.

[kushagraThapar]

Closing this because of inactivity, pls re-open if this is still an issue @zhoujia1974 , thanks!