$expr predicates can be incorrectly pushed down before a $group, causing incorrect query results

XMLWordPrintableJSON

    • Type: Bug
    • Resolution: Unresolved
    • Priority: Major - P3
    • None
    • Affects Version/s: None
    • Component/s: Query Planning
    • Query Optimization
    • ALL
    • Hide

      Repro using $expr with a predicate that distinguishes between null and missing:

      > db.c.drop()
true
> db.c.insert({_id: 1, a: null})
WriteResult({ "nInserted" : 1 })
> db.c.insert({_id: 2})
WriteResult({ "nInserted" : 1 })

// Grouping on _id, we see that null and missing are grouped into the same bucket.
// Furthermore, the resulting _id field is of type null.
> db.c.aggregate([{$group: {_id: "$a", n: {$count: {}}}}, {$addFields: {idType: {$type: "$_id"}}}])
{ "_id" : null, "n" : 2, "idType" : "null" }

// Since the _id field is of type null, adding a $match to this effect should not change
// the result of the query. But the count in field "n" incorrectly changes from 2 to 1!
// Running explain shows that this is due to incorrect pushdown of the $expr predicate.
> db.c.aggregate([{$group: {_id: "$a", n: {$count: {}}}}, {$addFields: {idType: {$type: "$_id"}}}, {$match: {$expr: {$eq: ["$_id", null]}}}])
{ "_id" : null, "n" : 1, "idType" : "null" }

      Repro using $expr with a $type predicate:

      > db.c.drop()
true
> db.c.insert({_id: 1, a: NumberInt(5)})
WriteResult({ "nInserted" : 1 })
> db.c.insert({_id: 2, a: NumberLong(5)})
WriteResult({ "nInserted" : 1 })

// Grouping on _id, we see that numerically equal int and long types are grouped into
// the same bucket. In this case, the resulting type of the _id field is "int".
> db.c.aggregate([{$group: {_id: "$a", n: {$count: {}}}}, {$addFields: {idType: {$type: "$_id"}}}])
{ "_id" : 5, "n" : 2, "idType" : "int" }

// Since the _id field of type "int" after the $group, adding a $match that _id is of
// type "int" should not change the results of the query. But the count in field "n"
// incorrectly changes from 2 to 1! Running explain shows that this is due to incorrect
// pushdown of the $expr predicate.
> db.c.aggregate([{$group: {_id: "$a", n: {$count: {}}}}, {$addFields: {idType: {$type: "$_id"}}}, {$match: {$expr: {$eq: ["int", {$type: "$_id"}]}}}])
{ "_id" : 5, "n" : 1, "idType" : "int" }
      Show
      Repro using $expr with a predicate that distinguishes between null and missing: > db.c.drop() true > db.c.insert({_id: 1, a: null }) WriteResult({ "nInserted" : 1 }) > db.c.insert({_id: 2}) WriteResult({ "nInserted" : 1 }) // Grouping on _id, we see that null and missing are grouped into the same bucket. // Furthermore, the resulting _id field is of type null . > db.c.aggregate([{$group: {_id: "$a" , n: {$count: {}}}}, {$addFields: {idType: {$type: "$_id" }}}]) { "_id" : null , "n" : 2, "idType" : " null " } // Since the _id field is of type null , adding a $match to this effect should not change // the result of the query. But the count in field "n" incorrectly changes from 2 to 1! // Running explain shows that this is due to incorrect pushdown of the $expr predicate. > db.c.aggregate([{$group: {_id: "$a" , n: {$count: {}}}}, {$addFields: {idType: {$type: "$_id" }}}, {$match: {$expr: {$eq: [ "$_id" , null ]}}}]) { "_id" : null , "n" : 1, "idType" : " null " } Repro using $expr with a $type predicate: > db.c.drop() true > db.c.insert({_id: 1, a: NumberInt(5)}) WriteResult({ "nInserted" : 1 }) > db.c.insert({_id: 2, a: NumberLong(5)}) WriteResult({ "nInserted" : 1 }) // Grouping on _id, we see that numerically equal int and long types are grouped into // the same bucket. In this case , the resulting type of the _id field is " int " . > db.c.aggregate([{$group: {_id: "$a" , n: {$count: {}}}}, {$addFields: {idType: {$type: "$_id" }}}]) { "_id" : 5, "n" : 2, "idType" : " int " } // Since the _id field of type " int " after the $group, adding a $match that _id is of // type " int " should not change the results of the query. But the count in field "n" // incorrectly changes from 2 to 1! Running explain shows that this is due to incorrect // pushdown of the $expr predicate. > db.c.aggregate([{$group: {_id: "$a" , n: {$count: {}}}}, {$addFields: {idType: {$type: "$_id" }}}, {$match: {$expr: {$eq: [ " int " , {$type: "$_id" }]}}}]) { "_id" : 5, "n" : 1, "idType" : " int " }
    • None
    • None
    • None
    • None
    • None
    • None
    • None

      This is a new manifestation of a category of problems introduced by SERVER-34741 and subsequently patched in SERVER-91102 and then again in SERVER-102698. I reproduced the problem only on 8.2.1, but I'm fairly confident that it affects 8.0.x and 7.0.x versions as well. Note that we did a CA evaluation for SERVER-91102 and SERVER-102698 but declined to issue a CA. We also declined to backport the fixes for those two previous bug reports to older branches. We should discuss in the proper venue, but I imagine the same treatment is warranted here: fix the bug, but neither issue a CA nor do a backport to older branches.

      The problem is conceptually the same as the one originally reported in SERVER-91102: the semantics used for equality in $group and the semantics used in matching may not align. In SERVER-34741, we introduced an optimization to swap $match before $group when the $match is on the _id field of the $group. But this is an incorrect optimization if the $match distinguishes between values that $group considers equivalent. The known cases of this were existence predicates (null/missing are grouped into one bucket) and $type predicates (e.g. numbers of different types that are numerically equal are grouped into one bucket). We fixed these known cases for regular match expressions but failed to do so for $expr. See "Steps for Reproduce" for detailed examples of how to exercise the bug using $expr.

      I discovered this bug by code inspection while working on the fix for SERVER-106505, since my proposed fix involves changes to how $expr predicates are pushed down.

            Assignee:
            [DO NOT USE] Backlog - Query Optimization
            Reporter:
            David Storch
            Votes:
            0 Vote for this issue
            Watchers:
            11 Start watching this issue

              Created:
              Updated: