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

Fix incorrect result of unoptimized pipeline with $match and $count

    • Type: Icon: Bug Bug
    • Resolution: Duplicate
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: None
    • Component/s: None
    • None
    • Query Optimization
    • ALL
    • Hide
      db.xxx.insertMany([ {_id: 1, a: 1, b: null, c: 3},
      		    {_id: 2, a: 2, b: null, c: 4},
      		    {_id: 3, a: 2, b: 2, c: 3},
      		    {_id: 4, a: 2, c: 5},
      		    {_id: 5, a: 3, b: null, c: 6},
      		    {_id: 6, a: 4,  c: 7}]);
      
      
      db.xxx.createIndex({a:1, b:1});
      
      db.adminCommand({configureFailPoint: 'disablePipelineOptimization', mode: 'alwaysOn'});
      
      db.xxx.aggregate([
          {$match: {a: {$ne: 2}}},
          {$match: {b: {$exists: false}}},
          {"$count" : "a"}
      ]); 
      Show
      db.xxx.insertMany([ {_id: 1, a: 1, b: null , c: 3},     {_id: 2, a: 2, b: null , c: 4},     {_id: 3, a: 2, b: 2, c: 3},     {_id: 4, a: 2, c: 5},     {_id: 5, a: 3, b: null , c: 6},     {_id: 6, a: 4,  c: 7}]); db.xxx.createIndex({a:1, b:1}); db.adminCommand({configureFailPoint: 'disablePipelineOptimization' , mode: 'alwaysOn' }); db.xxx.aggregate([     {$match: {a: {$ne: 2}}},     {$match: {b: {$exists: false }}},     { "$count" : "a" } ]);
    • 8

      The following aggregation pipeline produces incorrect result when run with disabled optimization:

      db.xxx.aggregate([
          {$match: {a: {$ne: 2}}},
          {$match: {b: {$exists: false}}},
          {"$count" : "a"}
      ]);

      The incorrect result is observed both with classic and sbe engines.

      If we remove the $count stage, the pipeline produces correct result.

      The pipeline works correctly with optimization and with a collection scan if the index does not exist.

      The execution stats on the second $match expression `{$match: {b: {$exists: false}}}` shows 1 returned result in the pipeline without $count, and 0 nReturned in the other case. See the full explain in the attachments.

      // no $count:
      {"$match" : {
      		"b" : {
      			"$not" : {
      				"$exists" : true
      				}
      			}
      	     },
                   "nReturned" : NumberLong(1),
      	     "executionTimeMillisEstimate" : NumberLong(1)
      	}, ...
      
      // with $count
      {"$match" : {
      		"b" : {
      			"$not" : {
      				"$exists" : true
      				}
      			}
      		},
      		"nReturned" : NumberLong(0),
      		"executionTimeMillisEstimate" : NumberLong(1)
      	}, ...

       

        1. BF-31869-explain-unoptimized-classic-pipe1.txt
          4 kB
          Milena Ivanova
        2. BF-31869-explain-unoptimized-classic-pipe2.txt
          5 kB
          Milena Ivanova
        3. BF-31869-explain-unoptimized-sbe-pipe1.txt
          9 kB
          Milena Ivanova
        4. BF-31869-explain-unoptimized-sbe-pipe2.txt
          9 kB
          Milena Ivanova

            Assignee:
            ruoxin.xu@mongodb.com Ruoxin Xu
            Reporter:
            milena.ivanova@mongodb.com Milena Ivanova
            Votes:
            0 Vote for this issue
            Watchers:
            9 Start watching this issue

              Created:
              Updated:
              Resolved: