[SERVER-26581] hinting index with different collation than query specifies should give an error or warning Created: 11/Oct/16  Updated: 06/Dec/22

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

Type: Improvement Priority: Major - P3
Reporter: Asya Kamsky Assignee: Backlog - Query Optimization
Resolution: Unresolved Votes: 0
Labels: query-44-grooming
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Assigned Teams:
Query Optimization
Participants:

 Description   

Looks like it's possible to hint an index that has one collation defined but specify a different collation on the query itself.

It should probably be an error, similar to requests in SERVER-8564 but I can also see this being a warning, since it's possible to conceive a query that is using default collation but wants to use an existing different collation index for filtering in a scenario where this does not impact the results.



 Comments   
Comment by Asya Kamsky [ 29/Jul/19 ]

yeah it appears that we just go minKey/maxKey and use FETCH to do the actual query test.

Comment by David Storch [ 26/Jul/19 ]

asya can the invalid hint result in incorrect query results? I ran a simple test which suggested that this behavior does not trivially result in incorrect results:

MongoDB Enterprise > db.c.createIndex({a: 1}, {collation: {locale: "en_US", strength: 2}})
{
	"createdCollectionAutomatically" : false,
	"numIndexesBefore" : 1,
	"numIndexesAfter" : 2,
	"commitQuorum" : 1,
	"ok" : 1
}
MongoDB Enterprise > db.c.insert({a: "FOO"})
WriteResult({ "nInserted" : 1 })
MongoDB Enterprise > db.c.find({a: "foo"}).hint({a: 1})
// No results.
MongoDB Enterprise > db.c.find({a: "foo"}).hint({a: 1}).explain()
{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "test.c",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"a" : {
				"$eq" : "foo"
			}
		},
		"queryHash" : "4B53BE76",
		"planCacheKey" : "E4BB1AED",
		"winningPlan" : {
			"stage" : "FETCH",
			"filter" : {
				"a" : {
					"$eq" : "foo"
				}
			},
			"inputStage" : {
				"stage" : "IXSCAN",
				"keyPattern" : {
					"a" : 1
				},
				"indexName" : "a_1",
				"collation" : {
					"locale" : "en_US",
					"caseLevel" : false,
					"caseFirst" : "off",
					"strength" : 2,
					"numericOrdering" : false,
					"alternate" : "non-ignorable",
					"maxVariable" : "punct",
					"normalization" : false,
					"backwards" : false,
					"version" : "57.1"
				},
				"isMultiKey" : true,
				"multiKeyPaths" : {
					"a" : [
						"a"
					]
				},
				"isUnique" : false,
				"isSparse" : false,
				"isPartial" : false,
				"indexVersion" : 2,
				"direction" : "forward",
				"indexBounds" : {
					"a" : [
						"[MinKey, MaxKey]"
					]
				}
			}
		},
		"rejectedPlans" : [ ]
	},
	"serverInfo" : {
		"host" : "storchbox",
		"port" : 27017,
		"version" : "0.0.0",
		"gitVersion" : "unknown"
	},
	"ok" : 1
}

Therefore, I'm changing the issue type from "Bug" to "Improvement".

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