Orion-LD Pagination issue when using offset and limit

[hibke]

When using Orion-LD versions 1.5.1 and 1.6.1, we encountered some issues with entity retrieval and pagination.
If we set a limit that is inferior to the total number of entities and use pagination with offsets, some entities are messing while others are duplicated.
For instance, in a GET request with a limit of 10, we observe the duplication of urn: Site:C00002:

GET ("FIWARE_HOST")+"/ngsi-ld/v1/entities?type=Site&count=true&limit=10&count=true&offset="+msg.offset;
Page 1:

1-urn:Site:testSite2
2-urn:Site:testSite3
3-urn:Site:testSite1
4-urn:Site:TestElec2ElasticC
5-urn:Site:C00007
6-urn:Site:C00006
7-urn:Site:C00005
8-urn:Site:C00008
9-urn:Site:C00004
10-urn:Site:C00002

Page 2:

11-urn:Site:C00002
12-urn:Site:C00003
13-urn:Site:C00001
14-urn:Site:335902
15-urn:Site:339216
16-urn:Site:149631
17-urn:Site:333183
18-urn:Site:343679
19-urn:Site:335808
20-urn:Site:219451

We noticed that using the same limit consistently produced identical outcomes, including the same missing and duplicated elements. However, modifying the limit resulted in different set of results with different set of missing and duplicated elements. Those are the results for a limit of 11.

Page 1:

1-urn:Site:testSite2
2-urn:Site:testSite3
3-urn:Site:testSite1
4-urn:Site:TestElec2ElasticC
5-urn:Site:C00008
6-urn:Site:C00007
7-urn:Site:C00006
8-urn:Site:C00009
9-urn:Site:C00005
10-urn:Site:C00004
11-urn:Site:C00002

Page 2:

12-urn:Site:C00003
13-urn:Site:C00001
14-urn:Site:343679
15-urn:Site:350006
16-urn:Site:149631
17-urn:Site:335902
18-urn:Site:350511
19-urn:Site:339216
20-urn:Site:219451
21-urn:Site:330903
22-urn:Site:333183

To identify the root cause, we directly queried the entities from mongodb database and did not encounter the same issue, indicating a potential issue with the way Orion retrieves data from mongodb.

db.entities.find({"_id.type": "Site"}, {attrs:0,attrNames:0,creDate:0,modDate:0, lastCorrelator:0, _id:1}).limit(10);

1-{ "_id" : { "id" : "urn:Site:testSite1", "type" : "Site", "servicePath" : "/" } }
2-{ "_id" : { "id" : "urn:Site:testSite2", "type" : "Site", "servicePath" : "/" } }
3-{ "_id" : { "id" : "urn:Site:testSite3", "type" : "Site", "servicePath" : "/" } }
4-{ "_id" : { "id" : "urn:Site:TestElec2ElasticC", "type" : "Site", "servicePath" : "/" } }
5-{ "_id" : { "id" : "urn:Site:C00001", "type" : "Site", "servicePath" : "/" } }
6-{ "_id" : { "id" : "urn:Site:C00002", "type" : "Site", "servicePath" : "/" } }
7-{ "_id" : { "id" : "urn:Site:C00003", "type" : "Site", "servicePath" : "/" } }
8-{ "_id" : { "id" : "urn:Site:C00004", "type" : "Site", "servicePath" : "/" } }
9-{ "_id" : { "id" : "urn:Site:C00005", "type" : "Site", "servicePath" : "/" } }
10-{ "_id" : { "id" : "urn:Site:C00006", "type" : "Site", "servicePath" : "/" } }
11-{ "_id" : { "id" : "urn:Site:C00007", "type" : "Site", "servicePath" : "/" } }
12-{ "_id" : { "id" : "urn:Site:C00008", "type" : "Site", "servicePath" : "/" } }
13-{ "_id" : { "id" : "urn:Site:C00009", "type" : "Site", "servicePath" : "/" } }
...

[kzangeli]

ok, noted.
That's not good!!! :)
I'd need some more input.
All parameters used when starting Orion-LD

I imagine this bug you've found is a side effect from when I implemented the entity maps, a few months back.
[ That can only be true if you start the broker with either -experimental or -mongocOnly. ]

I'll start with this first thing tomorrow. Had a fire to put out today, after a few days away on a trip.

[kzangeli]

As for your test directly against mongo, to make it a better comparison, order the entities by their createdAt (it's called creDate in the DB - old stuff from OMA NGSI).
That's what the broker does. Or, what it should do anyway.

[kzangeli]

I implemented a functional test with 22 entities: https://github.com/FIWARE/context.Orion-LD/pull/1575/files
4 test cases:

1. GET the first 10 entities
2. GET the next 10 entities (offset=10 - the count starts at zero - I imagine that's why you see a duplicated entity)
3. GET the first 11 entities
4. GET the next 11 entities

Works like a charm.

I tried with -experimental, -mongocOnly and without (legacy implementation). All OK.
I use all the same URI params you use. I even put count=true twice :)
So, I'm unable to reproduce the issue.
See if we can figure out what's going wrong.

[ I added a short sleep in my entity creation loop, to make sure the createdAt for the entities are "in order". ]

[hibke]

Hello, Thant for your response. We didn't use -experimental or -mongocOnly , Here's the orion part in the Docker compose file we utilized:

orion:
    image: fiware/orion-ld:1.6.0-PRE-1588
    environment:
      - ORIONLD_TROE=TRUE
      - ORIONLD_TROE_USER=orion
      - ORIONLD_TROE_PWD=orion
      - ORIONLD_TROE_HOST=timescale
      - ORIONLD_MONGO_HOST=mongo
    command: -dbhost mongo -logLevel DEBUG -forwarding

Actually, we don't just have 22 entities, we have thousands. However, setting a small limit as 10 or 11, allows us to easily identify replicated and missing elements.
I've ordered the entities by creDate in mongoDB, and indeed, we have duplicated elements as they all share the same creDate.

db.entities.find({"_id.type": "Site"}, {attrs:0,attrNames:0,modDate:0, lastCorrelator:0, _id:1}).sort({creDate:1}).limit(10).skip(0);
1- { "_id" : { "id" : "urn:Site:318111", "type" : "Site", "servicePath" : "/" }, "creDate" : 1709803861.6121764 }
2- { "_id" : { "id" : "urn:Site:319354", "type" : "Site", "servicePath" : "/" }, "creDate" : 1709803861.6121764 }
3- { "_id" : { "id" : "urn:Site:149631", "type" : "Site", "servicePath" : "/" }, "creDate" : 1709803861.6121764 }
4- { "_id" : { "id" : "urn:Site:314336", "type" : "Site", "servicePath" : "/" }, "creDate" : 1709803861.6121764 }
5- { "_id" : { "id" : "urn:Site:219451", "type" : "Site", "servicePath" : "/" }, "creDate" : 1709803861.6121764 }
6- { "_id" : { "id" : "urn:Site:139496", "type" : "Site", "servicePath" : "/" }, "creDate" : 1709803861.6121764 }
7- { "_id" : { "id" : "urn:Site:060851", "type" : "Site", "servicePath" : "/" }, "creDate" : 1709803861.6121764 }
8- { "_id" : { "id" : "urn:Site:060002", "type" : "Site", "servicePath" : "/" }, "creDate" : 1709803861.6121764 }
9- { "_id" : { "id" : "urn:Site:133626", "type" : "Site", "servicePath" : "/" }, "creDate" : 1709803861.6121764 }
10-{ "_id" : { "id" : "urn:Site:310445", "type" : "Site", "servicePath" : "/" }, "creDate" : 1709803861.6121764 }

db.entities.find({"_id.type": "Site"}, {attrs:0,attrNames:0,modDate:0, lastCorrelator:0, _id:1}).sort({creDate:1}).limit(10).skip(10);
11- { "_id" : { "id" : "urn:Site:339216", "type" : "Site", "servicePath" : "/" }, "creDate" : 1709803861.6121764 }
12-{ "_id" : { "id" : "urn:Site:335808", "type" : "Site", "servicePath" : "/" }, "creDate" : 1709803861.6121764 }
13- { "_id" : { "id" : "urn:Site:314336", "type" : "Site", "servicePath" : "/" }, "creDate" : 1709803861.6121764 }
14-{ "_id" : { "id" : "urn:Site:350006", "type" : "Site", "servicePath" : "/" }, "creDate" : 1709803861.6121764 }
15- { "_id" : { "id" : "urn:Site:139496", "type" : "Site", "servicePath" : "/" }, "creDate" : 1709803861.6121764 }
16-{ "_id" : { "id" : "urn:Site:319354", "type" : "Site", "servicePath" : "/" }, "creDate" : 1709803861.6121764 }
17- { "_id" : { "id" : "urn:Site:060002", "type" : "Site", "servicePath" : "/" }, "creDate" : 1709803861.6121764 }
18- { "_id" : { "id" : "urn:Site:133626", "type" : "Site", "servicePath" : "/" }, "creDate" : 1709803861.6121764 }
19- { "_id" : { "id" : "urn:Site:310445", "type" : "Site", "servicePath" : "/" }, "creDate" : 1709803861.6121764 }
20- { "_id" : { "id" : "urn:Site:335902", "type" : "Site", "servicePath" : "/" }, "creDate" : 1709803861.6121764 }

I think that instead of capturing the time of entity creation, it records the times when the request was made. This explains why we have the same creDate for a lot of entities, and why we are encountring replicated and missing entities.

[kzangeli]

Ah, you have the EXACT SAME creDate ...
That's a problem!
And it explains everything.
Did NOT see this coming!!! :))

[ I added that sleep in my functest to make sure I didn't have a problem like this ]

One solution would be to order by creDate AND then on _id.id.
I think (and certainly hope!) it's possible in mongodb.
If you want to suggest some other solution, feel free. I'm listening :)

[kzangeli]

Think I found it in SOF: https://stackoverflow.com/questions/31209628/sorting-by-subfields-in-mongo-c-driver

I'll implement that and create a new functest, one that creates 22 (for example) entities using a BATCH Create operation.
All entities will have the very same creDate (createdAt) that way.

This should be easy.

[hibke]

Yes, I think that ordering by creDate and _Id.id is the solution. I have tested it, and it worked.
Thank you for taking the time to respond and solve this issue.

[kzangeli]

As expected, easy fix - PR sent.
If all goes well, there will be a new docker image in a few hours.

[kzangeli]

PR merged.
The tag on dockerhub is: 1.6.0-PRE-1591

[kzangeli]

So, please try with the new image and if all is good, go ahead and close this issue

[hibke]

Hello,
I have tested the new image, and it has indeed resolved the issue.
Thank you for your efforts.

[kzangeli]

At your service :)