Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-81516

Incorrect query result when lowering to SBE $project that uses a field computed by $setWindowFields

    • Type: Icon: Bug Bug
    • Resolution: Duplicate
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: None
    • Component/s: None
    • None
    • Query Execution
    • ALL
    • Hide
      > 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 }
      
      Show
      > 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 }
    • QE 2023-10-16, QE 2023-10-30

      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
      

            Assignee:
            projjal.chanda@mongodb.com Projjal Chanda
            Reporter:
            irina.yatsenko@mongodb.com Irina Yatsenko (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

              Created:
              Updated:
              Resolved: