[SERVER-39943] Create match expressions for aggregation $gt/$gte/$lt/$lte and add to $expr rewrite Created: 04/Mar/19  Updated: 02/Jan/24  Resolved: 01/Feb/21

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

Type: Improvement Priority: Major - P3
Reporter: James Wahlin Assignee: Katherine Wu (Inactive)
Resolution: Fixed Votes: 0
Labels: PM-234-M3, PM-234-T-data-clone, query-work-resharding
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Backports
Depends
is depended on by SERVER-52745 Implement any MatchExpressions produc... Closed
is depended on by SERVER-49293 Test collection bulk loader for resha... Closed
Documented
is documented by DOCS-14252 Investigate changes in SERVER-39943: ... Closed
Duplicate
is duplicated by SERVER-34926 allow $expr with comparison bounded o... Closed
Related
related to SERVER-39938 aggregation $match before $lookup opt... Closed
related to SERVER-50121 Add 'min' and 'max' parameters to agg... Closed
is related to SERVER-34926 allow $expr with comparison bounded o... Closed
Backwards Compatibility: Fully Compatible
Backport Requested:
v4.4
Sprint: Query 2020-11-30, Query 2020-12-14, Query 2020-12-28, Query 2021-01-11, Query 2021-01-25, Query 2021-02-08
Participants:
Case:

 Description   

When aggregation operators such as $gt/$gte/$lt/$lte are used within $expr, we will not rewrite to match expression. This is because they would not be eligible to use indexes due to type bracketing differences between match and aggregation. We should consider adding regardless, as the rewrite would make these expressions eligible for aggregation match optimization, such as repositioning a comparison expression in the pipeline.

The following is an example of where this would be useful. Ideally we would rewrite the {$expr": {"$gt" : [ "$x",

{"$const": 5}

]}} and move before the $lookup stage as an optimization.

db.foo.explain().aggregate({$lookup:{as:"a",from:"b",localField:"x",foreignField:"y"}},{$unwind:"$a"},{$match:{"a.z":10,$expr:{$gt:["$x",5]}}})
        "stages" : [
                {
                        "$cursor" : {
                                "query" : {
                                },
                                "queryPlanner" : {
                                        "plannerVersion" : 1,
                                        "namespace" : "tpcds10.foo",
                                        "indexFilterSet" : false,
                                        "parsedQuery" : {
                                        },
                                        "winningPlan" : {
                                                "stage" : "EOF"
                                        },
                                        "rejectedPlans" : [ ]
                                }
                        }
                },
                {
                        "$lookup" : {
                                "from" : "b",
                                "as" : "a",
                                "localField" : "x",
                                "foreignField" : "y"
                        }
                },
                {
                        "$match" : {
                                "$and" : [
                                        {
                                                "a.z" : {
                                                        "$eq" : 10
                                                }
                                        },
                                        {
                                                "$expr" : {
                                                        "$gt" : [
                                                                "$x",
                                                                {
                                                                        "$const" : 5
                                                                }
                                                        ]
                                                }
                                        }
                                ]
                        }
                }
        ],
        "ok" : 1
}



 Comments   
Comment by Githook User [ 01/Feb/21 ]

Author:

{'name': 'Katherine Wu', 'email': 'katherine.wu@mongodb.com', 'username': 'kaywux'}

Message: SERVER-39943 Create match expressions for aggregation $gt/$gte/$lt/$lte and add to $expr rewrite
Branch: master
https://github.com/mongodb/mongo/commit/120901099ba270b557054bc8ef06ac87418c0834

Comment by Githook User [ 01/Feb/21 ]

Author:

{'name': 'Katherine Wu', 'email': 'katherine.wu@mongodb.com', 'username': 'kaywux'}

Message: SERVER-39943 Create match expressions for aggregation $gt/$gte/$lt/$lte and add to $expr rewrite
Branch: master
https://github.com/10gen/mongo-enterprise-modules/commit/5cdc2343c567d65029c50236c146bc6c4eba3288

Comment by Craig Homa [ 20/Nov/20 ]

Thanks charlie.swanson, that was accidental.

Comment by Charlie Swanson [ 20/Nov/20 ]

craig.homa did you mean to unassign this from Katherine? I'm assuming that was accidental and putting it back.

Comment by James Wahlin [ 04/Mar/19 ]

This issue was originally reported by SERVER-39938.

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