[SERVER-33364] Combined $match, $expr in aggregation not using indexes Created: 16/Feb/18  Updated: 02/Jun/20  Resolved: 16/Feb/18

Status: Closed
Project: Core Server
Component/s: Aggregation Framework
Affects Version/s: 3.6.0, 3.6.2
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Daniel Polistchuck Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-31760 Lookup sub-pipeline is not using inde... Closed
Operating System: ALL
Steps To Reproduce:

Make sure the collection has an indexed property (ExternalId in this example). The query

{{db.collection.aggregate([
{
"$match": {"$expr": {"$eq": ["FullProposalId","123456"]}}
}
],

{explain:true}

)}}

shows a COLLSCAN fetch stage
while the simple aggregation below (that cannot be used in lookups with "lets"):
{{db.collection.aggregate([
{
"$match":

{"ExternalId":"123456"}

}
],

{explain:true}

)

}}
shows an IXSCAN stage (as epxected)

Participants:

 Description   

While using $lookup stages in aggregation pipelines using the new "Multiple Join Conditions", which requires $expr commands, the lookup uses COLLSCAN instead of indexes to fetch the destination values.

In fact this seems to be a problem with the "$expr" command and not the $lookup phase itself. (see steps to reproduce)



 Comments   
Comment by Bruno Fernandes Bastos [ 28/Dec/18 ]

Hello,

seems the $expr still does not use the correct index if if it a partial compound index (even when all filter criteria match).

Comment by Kelsey Schubert [ 16/Feb/18 ]

Hi danielp@bompracredito.com.br,

We've just released MongoDB 3.6.3-rc0, which is our release candidate for MongoDB 3.6.3, and I'd expect 3.6.3 GA to follow in about a week or so. Please feel free to upgrade to 3.6.3-rc0 to test the fix in the interim.

Kind regards,
Kelsey

Comment by David Storch [ 16/Feb/18 ]

kelsey.schubert, could you answer Daniel's question about about the anticipated timeframe for the 3.6.3 release?

Comment by Daniel Polistchuck [ 16/Feb/18 ]

Sorry, I didn't look up other candidate bug reports before reporting mine...
Now, the dreaded question that PMs hate: Any ETA for 3.6.3?

Cheers,

Daniel

Comment by David Storch [ 16/Feb/18 ]

Hi danielp@bompracredito.com.br,

Thanks for this issue report! It looks like this is a duplicate of SERVER-31760, which is a known issue affecting 3.6.0 through 3.6.2. The fix has been backported to the 3.6 branch and will be available in the 3.6.3 release. The fix is also available for testing in the 3.7.1 development release. Finally, note that SERVER-31760 is documented as part of the known issues in 3.6.0.

Please let me know if you have any questions or concerns.

Best,
Dave

Comment by Daniel Polistchuck [ 16/Feb/18 ]

I tried to use the monospace style in the issue text to format the queries, but it didn't seem to work.

Generated at Thu Feb 08 04:33:09 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.