[SERVER-83434] Empty aggregation result after $lookup and $match Created: 19/Nov/23  Updated: 01/Feb/24  Resolved: 01/Feb/24

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: 7.0.3
Fix Version/s: None

Type: Bug Priority: Minor - P4
Reporter: Danil Zelyutin Assignee: Alison Rhea Thorne
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Ubuntu 22.04
Intel Xeon E3-12xx v2 (Ivy Bridge, IBRS)


Attachments: PNG File 2023-11-19_21-13-24.png     File repro.js    
Operating System: ALL
Steps To Reproduce:

1. Install two clean MongoDb servers on Ubuntu 22.04 by official instruction, or only 7.0.3 one.

2. Launch mongo with default configs and everything: sudo systemctl start mongod

3. Create empty database and two collections, 'x' and 'y'.

4. In 'y' import following data:

[{
  "_id": {
    "$oid": "65531826b334f55a111f38bb"
  },
  "xId": {
    "$oid": "65546b236b591ccff56e9c21"
  },
  "name": "Test1"
},
{
  "_id": {
    "$oid": "65531e50b334f55a111f38bc"
  },
  "xId": {
    "$oid": "65546b236b591ccff56e9c21"
  },
  "name": "Test2"
},
{
  "_id": {
    "$oid": "65531e50b334f55a111f38bd"
  },
  "xId": {
    "$oid": "65546b236b591ccff56e9c21"
  },
  "name": "Test3"
},
{
  "_id": {
    "$oid": "655a0117475ec3024eee9c93"
  },
  "name": "Test4",
  "xId": {
    "$oid": "65546b236b591ccff56e9c21"
  }
}] 

5. In 'x' import following data:

[{
  "_id": {
    "$oid": "65546b236b591ccff56e9c21"
  },
  "deleted": false
}]

6. Then create aggregation in 'y' with following content:

 

[
  {
    $lookup: {
      from: "x",
      localField: "xId",
      foreignField: "_id",
      as: "x",
    },
  },
  {
    $match: {
      $expr: {
        $and: [
          {
            $eq: [
              "$_id",
              {
                $literal: ObjectId(
                  "65531e50b334f55a111f38bc"
                ),
              },
            ],
          },
          {
            $eq: [
              {
                $size: "$x",
              },
              1,
            ],
          },
        ],
      },
    },
  },
] 

 

 

Expected:

 

{
  "_id": {
    "$oid": "65531e50b334f55a111f38bc"
  },
  "xId": {
    "$oid": "65546b236b591ccff56e9c21"
  },
  "name": "Test2",
  "x": [
    {
      "_id": {
        "$oid": "65546b236b591ccff56e9c21"
      },
      "deleted": false
    }
  ]
}

 

 

Received (on 7.0.3):

 


<empty documents list>

 

 

Participants:

 Description   

Currently I'm using Prisma with Node.JS, when one of my request with conditions for document and its aggregated relation returned empty result instead of single entity.

The strangest part is for some documents the same aggregation works correctly but for other - it doesn't. Simply changing id to one of imported documents in reproducing example returns different result.

I've profiled its query and found one big aggregation for it.

After decomposing I've found one short part that creates the problem and reproduced it on server via Compass.

On the prod server (7.0.3) it was empty while on my local Mongo (6.0.5) it worked well.

So, I've created two new identical VDS on Ubuntu 22.04 where installed 6.0.11 version and 7.0.3 to be sure that's no replica mechanics involved or etc but only version.

Result is in attachment.

Maybe it's not a bug, but it looks kinda strange anyway.



 Comments   
Comment by Alison Rhea Thorne [ 01/Feb/24 ]

We haven’t heard back from you for some time, so I’m going to close this ticket. If this is still an issue for you, please provide additional information and we will reopen the ticket.

Comment by Alison Rhea Thorne [ 16/Jan/24 ]

We still need additional information to diagnose the problem. If this is still an issue for you, would you please confirm or deny that it exhibits the behavior that you are describing that would also be a great help for triaging this issue. If it does not, can you provide an alternative that does?

Comment by Alison Rhea Thorne [ 29/Dec/23 ]

Hi danil.zelyutin@gmail.com,

Thank you for your report. Unfortunately, I haven't been able to replicate this issue on v7.0.3. Is there any further information that you can provide for replication? Information on any indexes that you have created would be invaluable. Alternatively, I've attached a reproduction script (executable via mongosh). If you can confirm or deny that it exhibits the behavior that you are describing that would also be a great help for triaging this issue. If it does not, can you provide an alternative that does?

Thanks,
Rhea

Output on v6

[
  {
    _id: ObjectId("65531e50b334f55a111f38bc"),
    xId: ObjectId("65546b236b591ccff56e9c21"),
    name: 'Test2',
    x: [ { _id: ObjectId("65546b236b591ccff56e9c21"), deleted: false } ]
  }
]

Output on v7.0.3

[
  {
    _id: ObjectId("65531e50b334f55a111f38bc"),
    xId: ObjectId("65546b236b591ccff56e9c21"),
    name: 'Test2',
    x: [ { _id: ObjectId("65546b236b591ccff56e9c21"), deleted: false } ]
  }
]

Generated at Thu Feb 08 06:52:11 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.