[SERVER-54958] Empty result when counting from empty collection in aggregation Created: 04/Mar/21  Updated: 31/Aug/23

Status: Backlog
Project: Core Server
Component/s: Aggregation Framework
Affects Version/s: 4.4.4
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Maël Jaquier Assignee: Backlog - Query Optimization
Resolution: Unresolved Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
is related to SERVER-80458 $count aggregation stage does return ... Backlog
is related to SERVER-3241 Why does group return an empty result... Closed
Assigned Teams:
Query Optimization
Operating System: ALL
Steps To Reproduce:

Example with "live" collection not empty:

> db.live.find()
{ "_id" : "9674988e-ac03-4aa7-b948-13f7a2df48ea", "severity" : 200, "timestamp" : NumberLong("1614873465861257000"), "summary" : "test alarm", "serial" : "serial1", "tags" : "[]" }
{ "_id" : "ee520f41-62a9-4016-8db1-b538489e8d00", "severity" : 0, "timestamp" : NumberLong("1614873465861759000"), "summary" : "test alarm", "serial" : "serial1", "tags" : "[]" }
{ "_id" : "848ccdcc-10de-4a3b-9cc7-ab863c9be003", "severity" : 300, "timestamp" : NumberLong("1614873465861771000"), "summary" : "test alarm", "serial" : "serial1", "tags" : "[]" }
{ "_id" : "bcf9d77b-3b15-4f14-b4ff-3adc869fdb56", "severity" : 400, "timestamp" : NumberLong("1614873465861797000"), "summary" : "test alarm", "serial" : "serial2", "tags" : "[]" }
{ "_id" : "96a0b9c4-2948-4cf3-b350-d0df55b9c2ed", "severity" : 0, "timestamp" : NumberLong("1614873465861818000"), "summary" : "test alarm", "serial" : "serial2", "tags" : "[]" }
{ "_id" : "4cce4bdd-a393-43b2-8835-ddb58394af80", "severity" : 600, "timestamp" : NumberLong("1614873465861839000"), "summary" : "test alarm", "serial" : "serial2", "tags" : "[]" }
> db.live.aggregate([{$match:{}},{$count: 'count'},{$out: 'test'}])
> db.test.find()
{ "_id" : ObjectId("6041050aaf18759a3f23a440"), "count" : 6 }

Example with empty "live" collection:

> db.live.find()
> db.live.aggregate([{$match:{}},{$count: 'count'},{$out: 'test'}])
> db.test.find()
>

 

Participants:

 Description   

Hi,

During an aggregation, we need to count the number of elements matching a specific rule. It works properly most of the time, however when there is no matching document (or the collection itself is empty), we get an empty result instead of a document with count equal to 0.

We understand this is probably a optimization to avoid processing stages on empty result, however operation such as count should probably be computed anyway in order to produce a consistent result.

The reason why we want to apply count inside aggregation instead of the aggregation result is because we would like to apply MongoDB expression evaluation on it (i.e. {count: { $eq: 20 }} for example).



 Comments   
Comment by Charlie Swanson [ 16/Mar/21 ]

Hi mael.jaquier@swissdotnet.ch,

As you can see here, this is happening because $count is simply an alias for $group - shown in the explain output. $group's behavior was considered in SERVER-3241 so this is somewhat of a duplicate ticket. However, I can certainly see an argument that a count of an empty result set should be 0, even if a grouping of that result set might not be.

Comment by Edwin Zhou [ 08/Mar/21 ]

Hi mael.jaquier@swissdotnet.ch,

Thanks for your detailed improvement request. When running aggregate on an empty collection, you're correct in that the planner attempts to optimize the execution, and the executionStats show that the winning plan uses an EOF stage.

{
  stages: [
    {
      $cursor: {
        queryPlanner: {
          plannerVersion: 1,
          namespace: "test.live",
          indexFilterSet: false,
          parsedQuery: {},
          winningPlan: {
            stage: "EOF",
          },
          rejectedPlans: [],
        },
        executionStats: {
          executionSuccess: true,
          nReturned: 0,
          executionTimeMillis: 0,
          totalKeysExamined: 0,
          totalDocsExamined: 0,
          executionStages: {
            stage: "EOF",
            nReturned: 0,
            executionTimeMillisEstimate: 0,
            works: 1,
            advanced: 0,
            needTime: 0,
            needYield: 0,
            saveState: 1,
            restoreState: 1,
            isEOF: 1,
          },
        },
      },
      nReturned: NumberLong(0),
      executionTimeMillisEstimate: NumberLong(0),
    },
    {
      $group: {
        _id: {
          $const: null,
        },
        count: {
          $sum: {
            $const: 1,
          },
        },
      },
      nReturned: NumberLong(0),
      executionTimeMillisEstimate: NumberLong(0),
    },
  ],
  ok: 1,
};

I can understand your use case in returning a count of 0 when using $count in a pipeline on an empty collection. We'll assign this to the appropriate team to be evaluated against our currently planned work. Updates will be posted on this ticket as they happen.

Best,
Edwin

Generated at Thu Feb 08 05:35:02 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.