Bug when querying $relatedTo with limit and order by createdAt

[cjlovescoffee]

Issue Description

When querying the objects in a relation property and setting both a limit and ordering by createdAt, not all expected objects are returned.

Removing the limit or changing the order by to another property seems to resolve the issue.

With 14 objects in the relation, the following incorrect number of objects are returned depending on the specified limit

limit	numResults
1	1
2	1
3	1
4	1
5	1
6	2
7	3
8	4
9	5
10	6
11	7
12	8
13	9
14	10
15	11
16	12
17	13
18	14
19	14

I have reproduced this issue with Parse Server 3.1.3 and 3.9.0 running from the appropriate official docker images running on a linux and windows host machine running the docker image inside its linux container

• Server

  • parse-server version (Be specific! Don't say 'latest'.) : 3.9.0
  • Operating System: Docker Container - Alpine Linux v3.9
  • Hardware: Windows PC Host (Intel i7 8700k) - Docker For Windows
  • Localhost or remote server? (AWS, Heroku, Azure, Digital Ocean, etc): Docker container running on Localhost

• Database

  • MongoDB version: 3.4.4
  • Storage engine: Docker volume
  • Hardware: Same as Server above in Mongo docker container
  • Localhost or remote server? Same as server above

[davimacedo]

Can you please write a test case?

[cjlovescoffee]

I've attempted to recreate the issue with a fresh instance of Parse Server 3.9.0 and mongo 3.4.4 (using their respective docker images) with some new data and, so far, have been unable to do so. I'll take a closer look at the data I have that exhibits this issue and get back to you with more information.

[cjlovescoffee]

After failing to recreate the issue with a fresh instance, I took the broken data from my app and removed sections of it, bit by bit, to see if I could narrow down the culprit. I eventually got down to the two offending classes with the minimal amount of properties, and the issue was still present.

At this point, I looked through the mongo data and discovered there were more rows in the join table than there were live objects. I cross-referenced the join table with the main tables and deleted the records that referenced objects which no longer existed - this solved the issue with my query.

The problem is caused when deleting an object (via an SDK, the dashboard, or the REST api) without removing the object from the relation. The stale objects left in the relation causes problems when querying $relatedTo, setting a limit and ordering by createdAt.

When I first started to work with relations, I confirmed that deleting the objects was sufficient to remove the object from its relation query results without throwing any errors. What I didn't notice was that some valid objects were being lost when I ordered the relatedTo queries by createdAt.

What is the correct way to remove objects that are contained in relations of other objects? The documentation does not mention explicitly needing to remove relations before deleting objects.

As this issue only seems to manifest when ordering by createdAt, is this a bug in parse server? Do I need to update my app to handle object deletions and relations explicitly? If the latter, then maybe all the Parse API guide documentation should be updated to stress this?

If I need to update my app, I will have to create a script to sanitise my live database's join tables (to remove the stale objects). Is there anything in parse server that could maybe help with this?

[cjlovescoffee]

To reproduce, follow these steps:

1. Create two new Parse classes: A and B
2. Create a single instance of A
3. Create 15 instances of B and attach them to the single instance of A via a "relation" field on class A (I named mine "B")
4. Delete 1 instance of class B (preferably not the first or last, but any in between)
5. Query (via any SDK or the REST API) class B ("/parse/classes/B") with the following constraints (where yourObjectId is the id of your instance of class A):

• where = {"$relatedTo": {"object":{"__type":"Pointer","className":"A","objectId":"yourObjectId"},"key":"B"}}
• order = createdAt
• limit = 14

You should now expect to see 14 instances of object B returned, but you will get 13.

I did some digging and the following code seems to be the cause: DatabaseController.js - Line 1047

The limit and skip are being applied to the "relatedTo" query but only when sort is set and is createdAt. Removing this entire condition block seems to fix this issue (sorting and limiting works as expected). Not sure why you'd want limit and skip to be used on this query under this particular circumstance?

[davimacedo]

Hi @cjlovescoffee . Thanks for going further with this. Do you want to open a PR with the proposed the change so the others can evaluate and merge?

[dplewis]

@cjlovescoffee I was able to reproduce this issue. You can check this PR for the reason why this exists.

If you want you can remove sort by createdAt and this will fix your issue.

We maybe able to fix your issue and keep the optimization but more investigation is needed.

Edit: Here is a failing test. If you want you can use query.each((object) => {}, { batchSize: 100 }). Batch Size is similar to limit (undocumented) and it sorts by createdAt for optimization.

  fit('query relation with limit and order by createdAt', async () => {
    const childObjects = [];
    for (let i = 0; i < 15; i++) {
      childObjects.push(new ChildObject({ x: i }));
    }
    await Parse.Object.saveAll(childObjects);
    const parent = new ParentObject();
    parent.set('x', 4);
    const relation = parent.relation('child');
    for (const child of childObjects) {
      relation.add(child);
    }
    await parent.save();
    await childObjects[7].destroy();
    const query = relation.query();
    query.limit(14);
    query.ascending('createdAt');
    const list = await query.find();
    console.log(list.length); // 13
  });