Issue in Order by in N1QL query

SQL++
1

Hi,
We are facing an issue during execution of a N1QL query in our UAT environment.

Name of the bucket is CUSTOMER.

Our document structure is something like this…

{
  "cust_id": "XXXXXXX",
  "region": "XXXX",
  "details": {
    "update_time": "2017-01-11T10:42:52Z",
    "first_name": "XXXXX",
    "last_name": "XXXXX"
  }
}

The query getting triggered is as follows…

SELECT META(CUSTOMER).id, CUSTOMER.details.update_time FROM CUSTOMER WHERE 1=1 AND CUSTOMER.region = 'XXXX' AND CUSTOMER.cust_id = 'XXXXXXXXXXXXXXX' 
ORDER BY CUSTOMER.details.update_time desc LIMIT 10 OFFSET 0

We have 3 entries in the UAT database with the above matching criterias

Output shoule be in the following order

id update_time
72d14417-1c94-4740-90ba-49665379f305 2017-01-11T10:42:52Z
b48306d3-fe22-403b-8bba-a0fa4ef69be0 2017-01-11T10:42:01Z
42ebd5c2-ed45-423d-9507-06efa798d478 2017-01-11T10:40:38Z

But the order by is not working as per expectation. It is returning data in arbitrary order. Not based on the update_time value which is given in order by statement.
But this same code is working as per expectation in SIT environment properly.

For your information when I am executing the same query in couchbase console it is working as per expectation in UAT also but from application when the same query is getting executed the order of the fetched data is becoming random. I am using spring boot in our application and using spring data in executing couchbase query and fetch data against it.

Can anyone please give any input on this? what is the problem? If anything is missing?
Any help will be highly appreciated.

1 Like
2

Post the EXPLAIN, Index definition and Complete query when It is not working and when it is working.

Please note above output is showing correctly. Sorted by update_time DESC

If it is not working in Application you can post your application code and may be others can help

3

Hope your code is taking care of synchronization (not using async calls) to fetch the data. cc @ingenthr

4

I would recommend turning the log level up so you can observe the exact query being sent over the wire. Perhaps with that and a code snippet at the spring level, we can identify if there’s something amiss.

5

The query posted above is only getting executed from application also.