[SERVER-25523] Equality to null predicate misses undefined values when answered via the prefix of a text index Created: 10/Aug/16  Updated: 27/Dec/23

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

Type: Bug Priority: Major - P3
Reporter: David Hows Assignee: Backlog - Query Integration
Resolution: Unresolved Votes: 0
Labels: qi-text-search, query-44-grooming
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
Assigned Teams:
Query Integration
Operating System: ALL
Steps To Reproduce:

for(x=0;x<1000;x++){db.foo.insert({"x":x, y:undefined} )}
db.foo.ensureIndex({y:1})
a = db.foo.find({y:null}).hint({y:1}).count()
b = db.foo.find({y:null}).hint({_id:1}).count()
assert.eq(a, b)

Participants:

 Description   

When querying for null on a field fully covered by an index, documents that have undefined as a value will be skipped. This is not true when doing a collection scan.

Looks to affect all of the 3.X family at time of report (pre 3.4).



 Comments   
Comment by David Storch [ 26/Jul/19 ]

This problem has been fixed as of version 4.2 for regular indexes:

MongoDB Enterprise > db.c.drop()
true
MongoDB Enterprise > db.c.insert({a: 1})
WriteResult({ "nInserted" : 1 })
MongoDB Enterprise > db.c.drop()
true
MongoDB Enterprise > db.c.insert({})
WriteResult({ "nInserted" : 1 })
MongoDB Enterprise > db.c.insert({a: null})
WriteResult({ "nInserted" : 1 })
MongoDB Enterprise > db.c.insert({a: undefined})
WriteResult({ "nInserted" : 1 })
MongoDB Enterprise > db.c.find({a: {$eq: null}})
{ "_id" : ObjectId("5d3b57d42994c7ff8f5dffa5") }
{ "_id" : ObjectId("5d3b57d72994c7ff8f5dffa6"), "a" : null }
{ "_id" : ObjectId("5d3b57da2994c7ff8f5dffa7"), "a" : undefined }
MongoDB Enterprise > db.c.createIndex({a: 1})
{
	"createdCollectionAutomatically" : false,
	"numIndexesBefore" : 1,
	"numIndexesAfter" : 2,
	"commitQuorum" : 1,
	"ok" : 1
}
MongoDB Enterprise > db.c.find({a: {$eq: null}})
{ "_id" : ObjectId("5d3b57da2994c7ff8f5dffa7"), "a" : undefined }
{ "_id" : ObjectId("5d3b57d42994c7ff8f5dffa5") }
{ "_id" : ObjectId("5d3b57d72994c7ff8f5dffa6"), "a" : null }
MongoDB Enterprise > db.c.find({a: {$eq: null}}).explain()
{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "test.c",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"a" : {
				"$eq" : null
			}
		},
		"queryHash" : "4B53BE76",
		"planCacheKey" : "100FCEBA",
		"winningPlan" : {
			"stage" : "FETCH",
			"filter" : {
				"a" : {
					"$eq" : null
				}
			},
			"inputStage" : {
				"stage" : "IXSCAN",
				"keyPattern" : {
					"a" : 1
				},
				"indexName" : "a_1",
				"isMultiKey" : false,
				"multiKeyPaths" : {
					"a" : [ ]
				},
				"isUnique" : false,
				"isSparse" : false,
				"isPartial" : false,
				"indexVersion" : 2,
				"direction" : "forward",
				"indexBounds" : {
					"a" : [
						"[undefined, undefined]",
						"[null, null]"
					]
				}
			}
		},
		"rejectedPlans" : [ ]
	},
	"serverInfo" : {
		"host" : "storchbox",
		"port" : 27017,
		"version" : "0.0.0",
		"gitVersion" : "unknown"
	},
	"ok" : 1
}

However, the related problem specifically for "text" indexes still exists. I'm repurposing this ticket to track specifically the "text" index problem.

Comment by Asya Kamsky [ 23/Nov/17 ]

Note: undefined BSON type is deprecated.

Comment by David Storch [ 13/Oct/17 ]

This same problem exists for equality to null predicates over the prefix of a text index:

> db.c.drop();
> db.c.ensureIndex({a: 1, b: "text"});
> db.c.insert({a: undefined, b: "lorem ipsum"});
> db.c.find({a: null, $text: {$search: "lorem"}});
// Should return one result, but returns nothing.

This is significant because the planning code is separate for text indexes and may require a separate fix.

Comment by Linda Qin [ 10/Aug/16 ]

According to the following document, null equality queries (i.e. field: null ) should match fields with values undefined.

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