ORDER BY produces inconsistent results

Couchbase Server
,
1

Given a document structure:

{
	"status": "closed",
	"type": "report",
	"answers": [
		{
			"q": "recordid",
			"v": "A1234"
		},
		{
			"cids": [
				0
			],
			"q": "otherdata",
			"v": "12345"
		}
	]
},
{
	"status": "open",
	"type": "report",
	"answers": [
		{
			"q": "recordid",
			"v": "B5432"
		},
		{
			"cids": [
				0
			],
			"q": "otherdata",
			"v": "whatever"
		}
	]
}

The following query (which finds all reports sorted by answers.v where answer.q = ‘recordid’) produces good and consistently sorted results:

SELECT DISTINCT ARRAY(COALESCE(TO_STRING(a.cids[ARRAY_LENGTH(a.cids) - 1]) || '~', '') || TO_STRING(a.v)) FOR a IN dev.answers WHEN a.q = 'recordid' END AS `recordid`,
       dev.id
FROM dev
WHERE dev.type = 'report'
    AND ANY a IN dev.answers SATISFIES a.q = 'recordid' END
ORDER BY dev.`recordid` DESC

However, if I add a just a single additional condition:

SELECT DISTINCT ARRAY(COALESCE(TO_STRING(a.cids[ARRAY_LENGTH(a.cids) - 1]) || '~', '') || TO_STRING(a.v)) FOR a IN dev.answers WHEN a.q = 'recordid' END AS `recordid`,
       dev.id
FROM dev
WHERE dev.type = 'report'
	AND dev.status = 'closed' --new condition
    AND ANY a IN dev.answers SATISFIES a.q = 'recordid' END
ORDER BY dev.`recordid` DESC

then each time I press Execute button in the console, I get incorrectly/inconsistently sorted results. If I comment out AND dev.status='closed', suddenly, results are again consistent.

Is this a bug? As it stands, I need to filter on status but cannot, due to it destroying the ORDER of results.

2

dev is source document and ORDER BY dev. recordid . There is no field recordid is the source document. So it evaluates MISSING, so all results are sort by MISSING, when duplicates in sort keys results can be any order in that duplicates. You need to give additional ORDER terms to resolve duplicates.

If you need to sort by projection recordid, remove qualification dev.

ORDER BY recordid DESC

NOTE: recordid is ARRAY, It sorts based on ARRAY. ORDER BY clause | Couchbase Docs

3

@vsr1 Thanks, that did the trick. I’ve been confused as to why I sometimes need to place the bucket name before a property name or the results will come back wrapped in an unnecessary additional object or array. So I’ve been getting into the (bad) habit of automatically prefixing my bucket name to property names. I didn’t realize that the bucket name prefix is only valid for root-level properties.

4

I also do fully qualified name. If you want to use projection alias in ORDER BY, don’t give fully qualified name.