Query using LIMIT and OFFSET returning the same record on multiple pages

I’ve got a query:

SELECT META(`Dev_YS_Apps_01`).id AS _ID,
       META(`Dev_YS_Apps_01`).cas AS _CAS,
       `Dev_YS_Apps_01`.*
FROM `Dev_YS_Apps_01`
WHERE (TRUE
        AND `fileEntityRef` = "ST:FEN:duplicate-file-record-test"
        AND `state` != "CANCELLED"
        AND `platform` = "ST")
    AND `_class` = "com.xxx.rest.sett.dal.entity.FileRecordDO"
ORDER BY `recordType`,
         `recordNumber`,
         `meta()`.`id`
OFFSET 0
LIMIT 100

For this query, I’ve created the following index, which is used by the query:

CREATE INDEX idx_st_fr_feRef_st_pf_cl_rTp_rNum_id ON Dev_YS_Apps_01(`fileEntityRef`, `state`, `platform`, `_class`, `recordType`, `recordNumber`, `meta().id`) 
WHERE ((`_class` = "com.xxx.rest.sett.dal.entity.FileRecordDO") and `platform` = "ST");

When I query for subsequent pages:

SELECT META(`Dev_YS_Apps_01`).id AS _ID,
       META(`Dev_YS_Apps_01`).cas AS _CAS,
       `Dev_YS_Apps_01`.*
FROM `Dev_YS_Apps_01`
WHERE (TRUE
        AND `fileEntityRef` = "ST:FEN:duplicate-file-record-test"
        AND `state` != "CANCELLED"
        AND `platform` = "ST")
    AND `_class` = "com.xxx.rest.sett.dal.entity.FileRecordDO"
ORDER BY `recordType`,
         `recordNumber`,
         `meta()`.`id`
OFFSET 100
LIMIT 100

I am finding that the same record shows up on multiple pages. How can that be?

The ORDER BY META().id in query and index are wrong. You have back ticks around meta()

`meta()`.`id`
`meta().id`

META() is function and case insensitive, no back-ticks (back-ticks for escaped identifiers only https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/identifiers.html ). Above two must be

meta().`id` 
or 
meta().id

CREATE INDEX ix1  ON Dev_YS_Apps_01( `fileEntityRef` ,  `recordType` ,  `recordNumber` ,  meta().id ) 
WHERE `_class`  = "com.xxx.rest.sett.dal.entity.FileRecordDO" AND  `platform`  = "ST" AND state != "CANCELLED";
OR
CREATE INDEX ix1  ON Dev_YS_Apps_01( `fileEntityRef` ,  `state`, `recordType` ,  `recordNumber` ,  meta().id ) 
WHERE `_class`  = "com.xxx.rest.sett.dal.entity.FileRecordDO" AND  `platform`  = "ST";


SELECT META(d).id AS _ID,
       META(d).cas AS _CAS,
       d.*
FROM `Dev_YS_Apps_01` AS d
WHERE (TRUE
        AND `fileEntityRef` = "ST:FEN:duplicate-file-record-test"
        AND `state` != "CANCELLED"
        AND `platform` = "ST")
    AND `_class` = "com.xxx.rest.sett.dal.entity.FileRecordDO"
ORDER BY `recordType`,
         `recordNumber`,
         META(d).id
OFFSET 0
LIMIT 100;

If your offset becomes high Checkout https://blog.couchbase.com/offset-keyset-pagination-n1ql-query-couchbase/

Thanks. This works. The issue comes with some generated code in a core spring data couchbase library where the sort criteria gets split and the ticks applied.

It seems to be working but I have not found documentation. If I ORDER BY recordType, recordNumber, _ID, does the _ID alias actually reference the META(d).id AS _ID in the SELECT?

I appreciate your time and feedback.

YES. Projection alias can be used in ORDER BY.
WITH, FROM ,LET, WHERE, GROUP, LETTING, SELECT PROJECTION, ORDER BY.
Any defined identifiers, aliases in previous phase can be used in current phase.

Excellent. Thank you for the confirmation!