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

$avg is returning the sum instead of the average in aggregate $group

XMLWordPrintableJSON

    • Type: Icon: Bug Bug
    • Resolution: Fixed
    • Priority: Icon: Critical - P2 Critical - P2
    • 6.0.6
    • Affects Version/s: 6.0.5
    • Component/s: Aggregation Framework
    • None
    • Query Execution
    • Fully Compatible
    • ALL
    • Hide

      Step 1: sample data points to use for reproducing this bug:

      db.sample_for_bug.insertMany([    
    {"group_a": "abc", "group_b": "cde", "group_c": "ghi", "amt":    96},
    {"group_a": "abc", "group_b": "cde", "group_c": "hij", "amt":    79},
    {"group_a": "abc", "group_b": "def", "group_c": "ijk", "amt":    33},
    {"group_a": "abc", "group_b": "def", "group_c": "jkl", "amt":    20},
    {"group_a": "bcd", "group_b": "efg", "group_c": "klm", "amt":    67},
    {"group_a": "bcd", "group_b": "efg", "group_c": "lmn", "amt":    9},
    {"group_a": "bcd", "group_b": "fgh", "group_c": "mno", "amt":    23},
    {"group_a": "bcd", "group_b": "fgh", "group_c": "nop", "amt":    11}
])

      Step 2: the aggregate that fails to use the (last) $avg accurately:

      db.sample_for_bug.aggregate([
    {$group: {
        _id: {a: "$group_a", b: "$group_b", c: "$group_c"},
        amt: {$avg: "$amt"}
    }},
    {$group: {
        _id: {a: "$_id.a", b: "$_id.b"},
        amt: {$sum: "$amt"}
    }},
    {$group: {_id: '', amt: {$avg: "$amt"}}}
])

      Step 3: add in some non-$group aggregate command ahead of the last $group and the results are returned accurately:

      db.sample_for_bug.aggregate([
    {$group: {
        _id: {a: "$group_a", b: "$group_b", c: "$group_c"},
        amt: {$avg: "$amt"}
    }},
    {$group: {
        _id: {a: "$_id.a", b: "$_id.b"},
        amt: {$sum: "$amt"}
    }},
    {$match: {amt: {$exists: true}}},
    {$group: {_id: '', amt: {$avg: "$amt"}}}
])
      Show
      Step 1: sample data points to use for reproducing this bug: db.sample_for_bug.insertMany([         { "group_a" : "abc" , "group_b" : "cde" , "group_c" : "ghi" , "amt" :    96},     { "group_a" : "abc" , "group_b" : "cde" , "group_c" : "hij" , "amt" :    79},     { "group_a" : "abc" , "group_b" : "def" , "group_c" : "ijk" , "amt" :    33},     { "group_a" : "abc" , "group_b" : "def" , "group_c" : "jkl" , "amt" :    20},     { "group_a" : "bcd" , "group_b" : "efg" , "group_c" : "klm" , "amt" :    67},     { "group_a" : "bcd" , "group_b" : "efg" , "group_c" : "lmn" , "amt" :    9},     { "group_a" : "bcd" , "group_b" : "fgh" , "group_c" : "mno" , "amt" :    23},     { "group_a" : "bcd" , "group_b" : "fgh" , "group_c" : "nop" , "amt" :    11} ]) Step 2: the aggregate that fails to use the (last) $avg accurately: db.sample_for_bug.aggregate([ {$group: { _id: {a: "$group_a" , b: "$group_b" , c: "$group_c" }, amt: {$avg: "$amt" } }}, {$group: { _id: {a: "$_id.a" , b: "$_id.b" }, amt: {$sum: "$amt" } }}, {$group: {_id: '', amt: {$avg: "$amt" }}} ]) Step 3: add in some non- $group aggregate command ahead of the last $group and the results are returned accurately: db.sample_for_bug.aggregate([ {$group: { _id: {a: "$group_a" , b: "$group_b" , c: "$group_c" }, amt: {$avg: "$amt" } }}, {$group: { _id: {a: "$_id.a" , b: "$_id.b" }, amt: {$sum: "$amt" } }}, {$match: {amt: {$exists: true }}}, {$group: {_id: '', amt: {$avg: "$amt" }}} ])
    • QE 2023-04-03

      When $avg-ing a field within a $group stage after several back-to-back $group aggregates, the aggregate is returning the sum of the field and not the average. Strangely enough, the $avg command will work on a multi-group aggregate, IF one inserts a non-$group command, (e.g. $sort, $match) before that last $group stage in which the $avg is taking place.

            Assignee:
            adi.agrawal@mongodb.com Adi Agrawal
            Reporter:
            jesse.english@eyesover.com Jesse English
            Votes:
            0 Vote for this issue
            Watchers:
            17 Start watching this issue

              Created:
              Updated:
              Resolved: