[SERVER-81516] Incorrect query result when lowering to SBE $project that uses a field computed by $setWindowFields Created: 28/Sep/23  Updated: 17/Oct/23  Resolved: 17/Oct/23

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: Projjal Chanda
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-82213 [SBE] buildWindow() should update the... Closed
is duplicated by SERVER-82032 Window stage followed by a stage refe... Closed
Related
related to SERVER-82273 Complete TODO listed in SERVER-81516 Closed
Assigned Teams:
Query Execution
Operating System: ALL
Steps To Reproduce:

> db.test.insert({_id:0, key: 1, val: 42}) // the only record in the collection
> db.test.aggregate([{$setWindowFields: {partitionBy: "$key", output: {total: {$sum: "$val"}}}},{$project: {x: "$total"}}])
 
result in Classic:
{ "_id" : 0, "x" : 42 }
 
result in SBE:
{ "_id" : 0 }

Sprint: QE 2023-10-16, QE 2023-10-30
Participants:

 Description   

The following pipelines return correct results:

1. db.test.aggregate([{$setWindowFields: {partitionBy: "$key", output: {total: {$sum: "$val"}}}}]) // no project
2. db.test.aggregate([{$setWindowFields: {partitionBy: "$key", output: {total: {$sum: "$val"}}}},{$project: {x: "$val"}}]) // computing project of a field not computed by the window function
3. db.test.aggregate([{$setWindowFields: {partitionBy: "$key", output: {total: {$sum: "$val"}}}},{$project: {total: 1}}]) // non-computing project

> db.test.explain().aggregate([{$setWindowFields: {partitionBy: "$key", output: {total: {$sum: "$val"}}}},{$project: {x: "$total"}}]).queryPlanner.winningPlan.slotBasedPlan.stages
[5] project [s16 = makeBsonObj(MakeObjSpec(["_id", "x" = Arg(0)], Closed), s15, s6)]
[4] project [s15 = makeBsonObj(MakeObjSpec(["total" = Arg(0)], Open), s5, s14)]
[4] project [s14 =
    if exists(s13)
    then doubleDoubleSumFinalize(s13)
    else 0
]
[4] window [s8, s5, s6, s2] [frameFirst[], frameLast[], lowBound{}, highBound{}] [s13 = {init{}, add{aggDoubleDoubleSum(s2)}, remove{}}]
[4] project [s8 =
    let [
        l101.0 = (s1 ?: null)
    ]
    in
        if isArray(l101.0)
        then fail(14, "An expression used to partition cannot evaluate to value of type array")
        else move(l101.0)
]
[3] sort [s7] [asc] [s5, s1, s6, s2]
[3] project [s7 =
    if (isArray(s1) ?: false)
    then (_internalLeast(s1) ?: undefined)
    else (s1 ?: null)
]
[2] project [s6 = getField(s5, "total")]
[2] mkbson s5 s3 [_id, key, val] keep [] true false
[1] scan s3 s4 none none none none none none lowPriority [s1 = key, s2 = val] @"a8bd34e9-7358-4940-babf-ad873c5b306b" true false



 Comments   
Comment by Projjal Chanda [ 17/Oct/23 ]

This duplicates SERVER-82213 that Drew is working on. Additionally, the commented out test need to be uncommented as part of that PR.

Comment by Irina Yatsenko (Inactive) [ 28/Sep/23 ]

As I stated in the description, $setWindowFields without $project does produce the correct result. I've tried a similar $project over group and it also produces correct results.

Correct result:
1. db.test.aggregate([{$setWindowFields: {partitionBy: "$key", output: {total: {$sum: "$val"}}}}]) // no project
2. db.test.aggregate([{$setWindowFields: {partitionBy: "$key", output: {total: {$sum: "$val"}}}},{$project: {x: "$val"}}]) // computing project of a field not computed by the window function
3. db.test.aggregate([{$setWindowFields: {partitionBy: "$key", output: {total: {$sum: "$val"}}}},{$project: {total: 1}}]) // non-computing project
4. db.test.aggregate([{$group: {_id: "$key", total: {$sum: "$val"}}}, {$project: {x: "$total"}}]) // computing project of a field computed by group
 
Incorrect result:
db.test.aggregate([{$setWindowFields: {partitionBy: "$key", output: {total: {$sum: "$val"}}}},{$project: {x: "$total"}}])

Thus, the problem seems to be in integration between $project and $setWindowFields. I'll let the QE team decide which project should look at it first.

Comment by Irina Yatsenko (Inactive) [ 28/Sep/23 ]

rui.liu@mongodb.com Is this known?

Note: there is a test case in jstests/aggregation/sources/setWindowFields/window_functions_on_timeseries_coll.js that fails because of this bug when I start lowering TS queries to SBE (the bug isn't specific to TS, it's repro with non-TS collections as well). I'm going to comment out the test case and tag it with this ticket.

Generated at Thu Feb 08 06:46:43 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.