Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-83434

Empty aggregation result after $lookup and $match

XMLWordPrintableJSON

    • Type: Icon: Bug Bug
    • Resolution: Done
    • Priority: Icon: Minor - P4 Minor - P4
    • None
    • Affects Version/s: 7.0.3
    • Component/s: None
    • None
    • Environment:
      Ubuntu 22.04
      Intel Xeon E3-12xx v2 (Ivy Bridge, IBRS)
    • ALL
    • Hide

      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>

       

       

      Show
      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>    

      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.

        1. repro.js
          1 kB
        2. 2023-11-19_21-13-24.png
          2023-11-19_21-13-24.png
          90 kB

            Assignee:
            rhea.thorne@mongodb.com Alison Rhea Thorne
            Reporter:
            danil.zelyutin@gmail.com Danil Zelyutin
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Created:
              Updated:
              Resolved: