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

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

XMLWordPrintableJSON

    • 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: