[SERVER-64060] $lookup doesn't match an array with object that misses the local key to null Created: 28/Feb/22  Updated: 16/Mar/22  Resolved: 15/Mar/22

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

Type: Bug Priority: Major - P3
Reporter: Irina Yatsenko (Inactive) Assignee: Chris Harris
Resolution: Won't Fix Votes: 0
Labels: mql-semantics
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
related to SERVER-63368 $lookup matches empty arrays to null/... Closed
is related to SERVER-63690 Implement path handling of localField... Closed
Operating System: ALL
Participants:

 Description   

db.l.find()
{ "_id" : 1, "a" : { "no_x" : 1 } }
{ "_id" : 2, "a" : [ { "no_x" : 1 }, { "no_x" : 2 } ] }
{ "_id" : 3, "a" : [ { "x" : 1 }, { "no_x" : 2 } ] }
{ "_id" : 4, "a" : [ { "x" : 1 }, { "x" : null } ] }
 
db.r.find()
{ "_id" : 0, "b" : null }
 
db.l.find({"a.x":null})
{ "_id" : 1, "a" : { "no_x" : 1 } }
{ "_id" : 2, "a" : [ { "no_x" : 1 }, { "no_x" : 2 } ] }
{ "_id" : 3, "a" : [ { "x" : 1 }, { "no_x" : 2 } ] }
{ "_id" : 4, "a" : [ { "x" : 1 }, { "x" : null } ] }
// All four records match to 'null'.
 
db.l.aggregate({$lookup: {from:"r", localField:"a.x", foreignField:"b", as:"matched"}})
{ "_id" : 1, "a" : { "no_x" : 1 }, "matched" : [ { "_id" : 0, "b" : null } ] }
{ "_id" : 2, "a" : [ { "no_x" : 1 }, { "no_x" : 2 } ], "matched" : [ { "_id" : 0, "b" : null } ] }
{ "_id" : 3, "a" : [ { "x" : 1 }, { "no_x" : 2 } ], "matched" : [ ] } // didn't match to 'null'
{ "_id" : 4, "a" : [ { "x" : 1 }, { "x" : null } ], "matched" : [ { "_id" : 0, "b" : null } ] }



 Comments   
Comment by Yoon Soo Kim [ 15/Mar/22 ]

We decided to not change the classic engine behavior after having another meeting with the product team. It's mainly because missing matches null and we collect terminal values of the local path into an array. Resolved this as "Won't Fix".

Comment by Irina Yatsenko (Inactive) [ 15/Mar/22 ]

Please take a look at https://jira.mongodb.org/browse/SERVER-64497. If we are open to changing how $lookup matches missing in the classic engine, it will be easier to implement in SBE, if we don't match missing to null at all, that is, _id:1 produces no matches.

Comment by Yoon Soo Kim [ 14/Mar/22 ]

asya, A question on _Id: 1.

Looking at your reply:

If we change empty array as SERVER-63368 suggests then _id:2 wouldn't match anything either.

As of now, we always try to collect terminal values of local field path into an array and for _id: 1, we will get an empty array for the path "a.x" internally because "a.x" is missing. And IIUC, if we follow the same logic as SERVER-63368 "It has been decided that empty arrays in local should not match null/missing in foreign", we should not match to null/missing for _id: 1.

What is your expected result for _id : 1?

Comment by Irina Yatsenko (Inactive) [ 08/Mar/22 ]

The detail that confuses me is the following:

{ "_id" : 1, "a" : { "no_x" : 1 } } -> "apply" path a.x -> result: missing => should match to whatever missing matches (currently, missing in foreign or null)
{ "_id" : 2, "a" : [ { "x" : 1 }, { "x" : 2 } ] } -> "apply" path a.x -> result: [1, 2] => should match to values 1 and 2
{ "_id" : 3, "a" : [ { "x" : 1 }, { "no_x" : 2 } ] } -> "apply" path a.x -> result: ??? 
{ "_id" : 4, "a" : [ { "x" : [] }, { "no_x" : 2 } ] } -> "apply" path a.x -> result: ??? 

I filed this ticket because I was expecting the result to be [1, missing] for _id:3

Comment by Asya Kamsky [ 07/Mar/22 ]

It's correct that _id:3 does not match null since the value we are using as local field is {{ [ 1 ] }} and 1 does not match null.

If we change empty array as SERVER-63368 suggests then _id:2 wouldn't match anything either.

Generated at Thu Feb 08 05:59:24 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.