[SERVER-74930] $avg is returning the sum instead of the average in aggregate $group Created: 15/Mar/23  Updated: 29/Oct/23  Resolved: 28/Mar/23

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

Type: Bug Priority: Critical - P2
Reporter: Jesse English Assignee: Adi Agrawal
Resolution: Fixed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Assigned Teams:
Query Execution
Backwards Compatibility: Fully Compatible
Operating System: ALL
Steps To Reproduce:

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"}}}
]) 

Sprint: QE 2023-04-03
Participants:

 Description   

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.



 Comments   
Comment by Githook User [ 28/Mar/23 ]

Author:

{'name': 'Adityavardhan Agrawal', 'email': 'adi.agrawal@mongodb.com', 'username': 'Adityav369'}

Message: SERVER-74930: Fix $avg is returning the sum instead of the average in aggregate $group
Branch: v6.0
https://github.com/mongodb/mongo/commit/a6ee62d04f087dc8189706032e51d50800323a86

Comment by Adi Agrawal [ 22/Mar/23 ]

Hi jesse.english@eyesover.com and yuan.fang@mongodb.com ,

Thanks for sending this issue over. The failure happens in v6.0 on the slot based execution engine. This is not an issue in v6.2 and subsequent releases. It is fixed by SERVER-60234.

We are working for a fix for v6.0 and will update this soon. Please reach out if you have any questions.

Thanks again!

Comment by Yuan Fang [ 16/Mar/23 ]

Hi jesse.english@eyesover.com,

Thank you for your report. I was able to reproduce the issue you observed in v6.0.5, and I'm passing this along to the team for further investigation. Please continue to watch for updates.

Regards,
Yuan

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