[SERVER-33506] COLLSCAN when using lookup with partial index match Created: 27/Feb/18  Updated: 02/Apr/18  Resolved: 02/Mar/18

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

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

Issue Links:
Related
related to SERVER-33642 Alert client when parsing a query exp... Closed
Operating System: ALL
Steps To Reproduce:

The following aggregation uses a matching index for column "matchKey" on the "other" collection:

db.collection.aggregate([
    {
        "$lookup": {
            "from": "other",
            "let": {
                "id": "$_id"
            },
            "pipeline": [
                {
                    "$match": {
                        "$expr": {
                            "$eq": ["$matchKey","$$id"]
                        }
                    }
                }
            ]
        }
    }
])

If you add another property to the lookup expression like this:

db.collection.aggregate([
    {
        "$lookup": {
            "from": "other",
            "let": {
                "id": "$_id"
            },
            "pipeline": [
                {
                    "$match": {
                        "$expr": {
                            $and:[{"$eq": ["$matchKey","$$id"]},{"$eq":["otherproperty",1]}]
                        }
                    }
                }
            ]
        }
    }
])

The partially matching index is completely disregarded and a COLLSCAN is executed

Participants:

 Description   

Version 3.6.3 fixed full index match for $lookup and $match $expr aggregations.
Whenever there's a partial index match, execution falls back to COLLSCAN, instead of using the partial matched index



 Comments   
Comment by Kelsey Schubert [ 02/Mar/18 ]

Hi danielp@bompracredito.com.br,

Thanks for the report, looking over the aggregation pipeline. I suspect that the issue you are observing is the result of missing a $ preceeding otherproperty

$and:[{"$eq": ["$matchKey","$$id"]},{"$eq":["otherproperty",1]}]

As it stands, the string "otherproperty" will never be equal to 1. If you rewrite this line as

$and:[{"$eq": ["$matchKey","$$id"]},{"$eq":["$otherproperty",1]}]

I would expect the appropriate index to be used.

To help identify these issues faster, I've opened SERVER-33642 as an improvement to alert the user when there is malformed query of this nature rather than proceeding with a COLLSCAN which will return no results. Please feel free to vote for it and watch it for updates.

Kind regards,
Kelsey

Comment by Daniel Polistchuck [ 27/Feb/18 ]

Thanks for fixing my poorly formatted queries

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