[SERVER-23229] Projection incorrectly returns null value instead of missing field to user if projection is covered Created: 18/Mar/16  Updated: 26/Jan/24

Status: Backlog
Project: Core Server
Component/s: Querying
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Benjamin Murphy Assignee: Backlog - Query Optimization
Resolution: Unresolved Votes: 3
Labels: open_todo_in_code, query-44-grooming, storch
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Backports
Depends
Duplicate
is duplicated by SERVER-6293 Index only query fills in missing val... Closed
is duplicated by SERVER-24666 Covered Query returns null for unset ... Closed
is duplicated by SERVER-41768 Inclusion projection scenario that fa... Closed
is duplicated by SERVER-42568 Covered query for $replaceRoot involv... Closed
Related
related to SERVER-64645 Add a query knob to disable covered s... Backlog
related to SERVER-6293 Index only query fills in missing val... Closed
related to SERVER-37101 Add optimization mode aggregation (pi... Closed
is related to SERVER-23318 Streaming $group does not handle null... Closed
is related to SERVER-12869 Index null values and missing values ... Backlog
Assigned Teams:
Query Optimization
Operating System: ALL
Sprint: QO 2022-07-11
Participants:
Case:
Linked BF Score: 135

 Description   

If a document is missing a certain field, then covered projections on that field will incorrectly report the field as having value null.

Reproduce as follows:

> db.foo.drop()
true
> db.foo.insert({a:1})
WriteResult({ "nInserted" : 1 })
> db.foo.find({a:1},{b:1,_id:0})
{  } // Nothing returned: expected.
> db.foo.ensureIndex({a:1,b:1})
{
	"createdCollectionAutomatically" : false,
	"numIndexesBefore" : 1,
	"numIndexesAfter" : 2,
	"ok" : 1
}
> db.foo.find({a:1},{b:1,_id:0})
{ "b" : null } // Null returned: unexpected.

Fixing this issue in such a way that is not backwards-breaking for existing indexes will be difficult. This issue is caused by the fact that the index key generation process generates identical keys for missing values and null values.

This also causes unintuitive behavior for features that use covered projections, such as $group (see below for details).

Original description:

On 3.2.0-rc2 and master, a $group stage with an index does not distinguish between a null and missing value, likely because the index uses a null when a value is missing. Once SERVER-4507 and SERVER-23099 are implemented, this behavior may change.

> db.foo.insert({a: 1, b: null})
WriteResult({ "nInserted" : 1 })
> db.foo.insert({a: null, b: 1})
WriteResult({ "nInserted" : 1 })
> db.foo.insert({b: 1})
WriteResult({ "nInserted" : 1 })
> db.foo.insert({a: 1})
WriteResult({ "nInserted" : 1 })
> db.foo.aggregate({$group: {_id: {a: "$a", b: "$b"}}})
{ "_id" : { "b" : 1 } }
{ "_id" : { "a" : null, "b" : 1 } }
{ "_id" : { "a" : 1 } }
{ "_id" : { "a" : 1, "b" : null } }
> db.foo.createIndex({a: 1, b: 1})
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 1,
        "numIndexesAfter" : 2,
        "ok" : 1
}
> db.foo.aggregate({$sort: {a: 1, b: 1}}, {$group: {_id: {a: "$a", b: "$b"}}})
{ "_id" : { "a" : 1, "b" : null } }
{ "_id" : { "a" : null, "b" : 1 } }



 Comments   
Comment by Steve Tarzia [ 11/Jul/22 ]

I confirmed that the commit on Jul 6, 2022 for SERVER-67416 does not fix this issue.  I was confused because SERVER-67416 does fix a similar bug that was incorrectly attributed to this ticket in a comment in `groupMissing.js`.  I opened SERVER-67963 to track work to clean up these tests and to document the $group bug that was fixed in SERVER-67416.  I will remove the link from this ticket to SERVER-67416.

Comment by Steve Tarzia [ 06/Jul/22 ]

Although the test case provided in the ticket description no longer fails, I am going to re-open this because we don't really understand why https://jira.mongodb.org/browse/SERVER-67416 would have fixed the root cause.  I'd like to try to reproduce this with a different JS test.

Comment by Githook User [ 06/Jul/22 ]

Author:

{'name': 'Steve Tarzia', 'email': 'steve.tarzia@mongodb.com', 'username': 'starzia'}

Message: SERVER-67416 SERVER-23229 Always sort fieldpath string sets using custom comparator
Branch: master
https://github.com/mongodb/mongo/commit/19bd7993d149b68918f0d589551c1bf6781c5b63

Comment by Githook User [ 06/Jul/22 ]

Author:

{'name': 'Steve Tarzia', 'email': 'steve.tarzia@mongodb.com', 'username': 'starzia'}

Message: SERVER-67416 SERVER-23229 Always sort fieldpath string sets using custom comparator
Branch: master
https://github.com/10gen/mongo-enterprise-modules/commit/2b29a176797d02ce13be38fa8ad097dba7ec1286

Comment by Steve Tarzia [ 01/Jul/22 ]

This seems to be fixed by my patch for SERVER-67416 (which was motivated by SERVER-66418) so I'm assigning this to myself.

Comment by Kajetan Abt [ 11/Mar/20 ]

Yes we suffer from the same issue.

Adding an index while using a projection will result in an extra "nullish" value in the result set. Queries should not change their results depending on index availability. This broke correct code when we added an index for performance improvements.

Comment by David Storch [ 13/Aug/19 ]

Flagging for re-triage, since this is currently complicating our query fuzz testing efforts.

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