[SERVER-64006] $lookup matches on paths through arrays differently for local/foreign collections Created: 25/Feb/22  Updated: 29/Mar/22  Resolved: 29/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: Irina Yatsenko (Inactive)
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-64497 Implement proper semantics for matchi... Closed
Operating System: ALL
Sprint: QE 2022-04-04
Participants:

 Description   

db.coll1.find()
{ key: null }
db.coll2.find()
{ nested: { no_key: 42 } } 
{ nested: [ ] }
{ nested: [ [ ] ] }
{ nested: [ {no_key: 42} ] }
 
db.coll1.aggregate([{$lookup: {from:"coll2", localField:"key", foreignField:"nested.key", as:"matched"}}, {$project: {_id:0, "matched._id":0}}])
produces
{ "key" : null, "matched" : [ { "nested" : { "no_key" : 42 } }, { "nested" : [ { "no_key" : 42 } ] } ] }
That is, docs where the "nested.key" path in foreign traverses through an empty array aren't matched to null.
 
db.coll2.aggregate([{$lookup: {from:"coll1", localField:"nested.key", foreignField:"key", as:"matched"}}, {$project: {_id:0, "matched._id":0}}])
produces
{ "nested" : { "no_key" : 42 }, "matched" : [ { "key" : null } ] }
{ "nested" : [ ], "matched" : [ { "key" : null } ] }
{ "nested" : [ [ ] ], "matched" : [ { "key" : null } ] }
{ "nested" : [ { "no_key" : 42 } ], "matched" : [ { "key" : null } ] }

Expected: the relationship of "matching on specified keys" should be symmetric and not depend on which of the collections is local and which is foreign.
Also see SERVER-63368 that deals with matching empty arrays at the terminal of a path.

NB: the example above uses empty arrays, but the same behavior applies when the arrays contain non-object values.



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

It has been decided to keep this behaviour for the classic engine. Commit 73935c33833 (SERVER-64777 Fix semantics for missing on foreign path and enable lowering to SBE) implements the same semantics when lowering $lookup into SBE

Comment by Ethan Zhang (Inactive) [ 29/Mar/22 ]

irina.yatsenko Can you confirm this is correctly implemented in SBE and close this ticket?

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

As far as symmetry is concerned, that's not even an expectation in MQL for $lookup when arrays are involved (e.g. top-level arrays in local are always traversed and in in foreign they are used for matching as values and then are traversed). And there are also historic reasons why arrays of non-objects do not "produce" the missing value for paths through them.

All in all, I still find the behavior non-intuitive but after the conversation with the product team it's "by design". We should revisit if we fail to re-implement this semantics in SBE.

Comment by Eric Cox (Inactive) [ 02/Mar/22 ]

christopher.harris Is there any update on this one as well?

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