[SERVER-63368] $lookup matches empty arrays to null/missing/undefined Created: 07/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: Yoon Soo Kim
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-64538 Complete TODO listed in SERVER-63368 Closed
is related to SERVER-64006 $lookup matches on paths through arra... Closed
is related to SERVER-64060 $lookup doesn't match an array with o... Closed
is related to SERVER-63690 Implement path handling of localField... Closed
is related to SERVER-64497 Implement proper semantics for matchi... Closed
Operating System: ALL
Sprint: QE 2022-02-21, QE 2022-03-21
Participants:

 Description   

Local collection L: [{lkey:[]}]
Foreign collection F: [{fkey: null},{fkey:undefined},{no_fkey:true}]
Query: db.L.aggregate({$lookup: {from: "F", localField: "lkey", foreignField: "fkey", as: "matched"}}

The query matches all three foreign records to the local record.
According to the comment in
https://github.com/10gen/mongo/blob/f65c3ec8d02162327c80f11fbb1758096a75cbe8/src/mongo/db/pipeline/document_source_lookup.cpp#L839-L842 the behavior doesn't seem to be intentional.

Proposed semantics for matching empty arrays ("matches X" below means "matches value X from the foreign field").

If the local key is "a":
{a: []} -> no matches
{a: [[], 1]} -> matches [] and 1
{a: [[[]]]} -> matches [[]] (does not match [])
{b: []} -> matches null
{b: [[]]} -> matches null
 
If the local key is "a.x":
{a: {x: []}} -> no matches
{a: {x: 1}} -> matches 1
{a: {x: [1, 2]}} -> matches 1 and 2
{a: [{x: 1}, {x: 2}]}} -> matches 1 and 2
{a: {x: [[]]}} -> matches []
{a: {y: 42}} -> matches null
{a: {y: []}} -> matches null
{a: [{y:[]}]} -> matches null
{a: [{y:42}, {x:[]}]} -> matches null
{a: [{y:42}, {x:[[]]}]} -> matches null and []
 
If the local key is "a.x.w":
{a: [{x: {w: []}}]} -> no matches
{a: [{x:[]}, {x: {w: []}}]} -> matches null
{a: [{x:[]}, {x: {w: 1}}]} -> matches null and 1
{a: [{x:[]}, {x: {w: [[]]}}]} -> matches null and []



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

Quote Irina:

In SBE top-level empty local array matches to nothing, and nested local empty array matches to nested empty foreign array (it should match to top-level empty array in foreign as well – that is a bug in SBE)

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

It has been decided that empty arrays in local should not match null/missing in foreign. We should fix this in the classic engine and implement the same semantics in SBE.
Note: "undefined" can do whatever (but it's probably easier not to match to it )

Comment by Asya Kamsky [ 07/Mar/22 ]

To me the comment in the code specifically indicates that the current behavior is intentional - i.e. when array is empty, treat it as if the value is "missing".   However, I'm not convinced that's correct behavior since the array local/foreign semantics were supposed to be more like $in ... and $in:[] matches nothing.

 

 

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

christopher.harris Has there been any updates around this ticket?

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