[SERVER-36465] Non-multikey sparse indexes can be used to answer query with {$ne:null} predicate Created: 06/Aug/18  Updated: 29/Oct/23  Resolved: 10/Oct/18

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

Type: Improvement Priority: Major - P3
Reporter: Asya Kamsky Assignee: Ian Boros
Resolution: Fixed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
related to SERVER-36681 Change {$ne: null} semantics to be mo... Backlog
related to SERVER-19742 Equality to null predicates can somet... Backlog
Backwards Compatibility: Fully Compatible
Sprint: Query 2018-10-08, Query 2018-10-22
Participants:

 Description   

Sparse indexes normally can't answer inequality queries because they don't know all the unindexed documents, but the exception to that is inequality to null since it's just a subset of $exists:true predicate which sparse indexes can answer.

This ticket is to allow sparse indexes to be used for {$ne:null} queries.

Additionally, we will add $** testing to null_query_semantics.js



 Comments   
Comment by Githook User [ 10/Oct/18 ]

Author:

{'name': 'Ian Boros', 'email': 'ian.boros@10gen.com'}

Message: SERVER-36465 Support {$ne: null} queries with non-multikey sparse and wildcard indexes
Branch: master
https://github.com/mongodb/mongo/commit/3af0f2a6053a7385b89149adce16a23b88cf9be7

Comment by Ian Boros [ 01/Oct/18 ]

Note to self to look for all cases where we call isMinToMax() for allPaths indexes. Many of those checks will need to be updated to account for this change, given that we'll be able to generate bounds like [[MinKey, undefined), (null, MaxKey]].

Comment by Asya Kamsky [ 14/Aug/18 ]

Yes, I probably should have added "for non-multikey index" (similar to SERVER-27646)

Comment by Ian Boros [ 06/Aug/18 ]

it's just a subset of $exists:true

After talking with james.wahlin and david.storch there's a weird case where this isn't necessarily true:

db.c.insert({a: [1, {c: 1}]})
db.c.insert({a: [1]})
 
db.c.find({"a.b": {$exists: true}}) // Returns nothing
db.c.find({"a.b": {$ne: null}}) // Returns {a: [1]}

I'd be surprised if this behavior was "on purpose", though.

Comment by Asya Kamsky [ 06/Aug/18 ]

And related to that, we don't use the index to find the null value when we do use it for null equality:

db.sparse.find({a:{$eq:null,$exists:true}}).explain()
{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "test.sparse",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"$and" : [
				{
					"a" : {
						"$eq" : null
					}
				},
				{
					"a" : {
						"$exists" : true
					}
				}
			]
		},
		"winningPlan" : {
			"stage" : "FETCH",
			"filter" : {
				"a" : {
					"$eq" : null
				}
			},
			"inputStage" : {
				"stage" : "IXSCAN",
				"keyPattern" : {
					"a" : 1
				},
				"indexName" : "a_1",
				"isMultiKey" : false,
				"multiKeyPaths" : {
					"a" : [ ]
				},
				"isUnique" : true,
				"isSparse" : true,
				"isPartial" : false,
				"indexVersion" : 2,
				"direction" : "forward",
				"indexBounds" : {
					"a" : [
						"[MinKey, MaxKey]"
					]
				}
			}
		},
		"rejectedPlans" : [ ]
	},
	"serverInfo" : {
		"host" : "Asyas-MacBook-Pro.local",
		"port" : 27017,
		"version" : "4.0.0",
		"gitVersion" : "3b07af3d4f471ae89e8186d33bbb1d5259597d51"
	},
	"ok" : 1
}

Comment by Asya Kamsky [ 06/Aug/18 ]

Index on "a" is sparse:

db.sparse.find({a:{$ne:null}}).explain()
{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "test.sparse",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"$nor" : [
				{
					"a" : {
						"$eq" : null
					}
				}
			]
		},
		"winningPlan" : {
			"stage" : "COLLSCAN",
			"filter" : {
				"$nor" : [
					{
						"a" : {
							"$eq" : null
						}
					}
				]
			},
			"direction" : "forward"
		},
		"rejectedPlans" : [ ]
	},
	"serverInfo" : {
		"host" : "Asyas-MacBook-Pro.local",
		"port" : 27017,
		"version" : "4.0.0",
		"gitVersion" : "3b07af3d4f471ae89e8186d33bbb1d5259597d51"
	},
	"ok" : 1
}
db.sparse.find({a:{$ne:null,$exists:true}}).explain()
{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "test.sparse",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"$and" : [
				{
					"a" : {
						"$exists" : true
					}
				},
				{
					"$nor" : [
						{
							"a" : {
								"$eq" : null
							}
						}
					]
				}
			]
		},
		"winningPlan" : {
			"stage" : "FETCH",
			"filter" : {
				"$nor" : [
					{
						"a" : {
							"$eq" : null
						}
					}
				]
			},
			"inputStage" : {
				"stage" : "IXSCAN",
				"keyPattern" : {
					"a" : 1
				},
				"indexName" : "a_1",
				"isMultiKey" : false,
				"multiKeyPaths" : {
					"a" : [ ]
				},
				"isUnique" : true,
				"isSparse" : true,
				"isPartial" : false,
				"indexVersion" : 2,
				"direction" : "forward",
				"indexBounds" : {
					"a" : [
						"[MinKey, MaxKey]"
					]
				}
			}
		},
		"rejectedPlans" : [ ]
	},
	"serverInfo" : {
		"host" : "Asyas-MacBook-Pro.local",
		"port" : 27017,
		"version" : "4.0.0",
		"gitVersion" : "3b07af3d4f471ae89e8186d33bbb1d5259597d51"
	},
	"ok" : 1
}
 

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