Couchbase query returning random results

.NET SDK
#1

Facing an issue with N1QL queries, I have created an API using .NET core and executing the queries using below snippet -
var result = await _couchbaseCluster.QueryAsync(query);
await foreach (dynamic row in result){
// mapping of ExpandObject to actual model goes here
}
and return response from here

When an API is called from application, it sometimes return empty payload or payload with some results. This behavior is inconsistent.
We have placed all the required indexes on buckets in Couchbase server.

Please help us out in resolving this issue.

#2

What version of Couchbase Server are you using?

Also, it might not be related, but just in case could you also post an example of the N1QL that’s being passed in here?

Finally, it might also be useful to post the log output from running this.

#3

Run the N1QL query in UI, cbq shell or REST and see if you get consistent results. If you get random results provide the execute query in UI provide out put from “Plan Text” tab for both with index definitions.

#4

Sorry for delayed response, i ran a query on Couchbase directly . I hit exeute button 4-5times and every time it gave me different results.

#5

@matthew.groves Sorry for delayed response, we are using Couchbase 6.6 community version. In order to narrow down the issue, i ran the query over Couchbase itself and tried executing 4-5 times but gave me different results. I am not able to find the logs for this on Couchbase server.
here is sample query that i am using
SELECT DISTINCT S.id, T.id, P.amount, P.chkNum, P.cardNum
FROM TestBucket S
INNER JOIN TestBucket T ON S.id = T.id AND UPPER(S.id) = UPPER(‘33f88e46-404a-423d-bbf1-edca107aac3c’) AND S.type = ‘MyType’
INNER JOIN TestBucket TD ON T.transactionId = TD.transactionId AND T.type=‘MyTrn’
INNER JOIN TestBucket P ON P.id = TD.id AND P.type=‘MyPay’
GROUP BY S.id, T.id, P.amount, P.chkNum, P.cardNum