$group can use a distinct scan on a sparse index, which hides null/missing results

XMLWordPrintableJSON

    • Query Optimization
    • ALL
    • Hide 
      const query = [{
	"$group" : {
		"_id" : "$a.b"
	}
}]

const docs = [
    {
        "_id" : 244,
        "a" : NumberInt(0),
    }
]


const coll = db.foo;
coll.drop()
assert.commandWorked(coll.insert(docs))

jsTestLog(coll.aggregate(query).toArray())
// [ { "_id" : null } ]

assert.commandWorked(coll.createIndex({"a.b": 1}, {sparse: true}))
jsTestLog(coll.aggregate(query).toArray())
// [ ]

jsTestLog(coll.explain().aggregate(query))
// distinct scan on the sparse index, which is why we don't see "null"
      Show
      const query = [{ "$group" : { "_id" : "$a.b" } }] const docs = [ { "_id" : 244, "a" : NumberInt(0), } ] const coll = db.foo; coll.drop() assert .commandWorked(coll.insert(docs)) jsTestLog(coll.aggregate(query).toArray()) // [ { "_id" : null } ] assert .commandWorked(coll.createIndex({ "a.b" : 1}, {sparse: true })) jsTestLog(coll.aggregate(query).toArray()) // [ ] jsTestLog(coll.explain().aggregate(query)) // distinct scan on the sparse index, which is why we don't see " null "
    • None
    • None
    • None
    • None
    • None
    • None
    • None

      The repro shows the $group using a distinct scan on a sparse index. null/missing values affect the query results in this case, so we should not use the sparse index (a regular non-sparse index would be fine though)

      Our documentation says we will not use a sparse index if it affects query results, unless the index is explicitly hinted. In this case it's not hinted and changes results.

            Assignee:
            Militsa Sotirova
            Reporter:
            Matt Boros
            Votes:
            0 Vote for this issue
            Watchers:
            8 Start watching this issue

              Created:
              Updated: