[SERVER-32525] Sort order returned by a multikey index scan is inconsistent with the SortKeyGenerator logic Created: 03/Jan/18  Updated: 27/Oct/23  Resolved: 04/Jan/18

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

Type: Bug Priority: Major - P3
Reporter: Byron Grogan Assignee: Backlog - Query Team (Inactive)
Resolution: Gone away Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
is related to SERVER-19402 Change semantics of sorting by array ... Closed
Assigned Teams:
Query
Operating System: ALL
Participants:

 Description   

For queries with a predicate over a multikey field and a subsequent sort, the result set is ordered differently depending on whether an index is present. A multikey index scan will only consider keys which fall within the bounds implied by the predicate, but the SortKeyGenerator ignores the bounds on fields other than on the sort pattern.

Consider the following example:

// Insert the following documents.
db.foo.insert([ { "_id" : 0, "a" : [ { "b" : 0, "c" : 1 }, { "b" : 1, "c" : 2 } ] }, { "_id" : 1, "a" : [ { "b" : 0, "c" : 2 }, { "b" : 1, "c" : 0 } ] }, { "_id" : 2, "a" : [ { "b" : 0, "c" : 0 }, { "b" : 1, "c" : 1 } ] } ])
 
// Query predicate is 'a.b' == 0.
db.foo.find({ 'a.b': 0 }).sort({ 'a.c': 1 })
{ "_id" : 1, "a" : [ { "b" : 0, "c" : 2 }, { "b" : 1, "c" : 0 } ] }
{ "_id" : 2, "a" : [ { "b" : 0, "c" : 0 }, { "b" : 1, "c" : 1 } ] }
{ "_id" : 0, "a" : [ { "b" : 0, "c" : 1 }, { "b" : 1, "c" : 2 } ] }
 
// Create compound index.
db.foo.createIndex({ 'a.b': 1, 'a.c': 1 })
 
// Query predicate is 'a.b' == 0.
db.foo.find({ 'a.b': 0 }).sort({ 'a.c': 1 })
{ "_id" : 2, "a" : [ { "b" : 0, "c" : 0 }, { "b" : 1, "c" : 1 } ] }
{ "_id" : 0, "a" : [ { "b" : 0, "c" : 1 }, { "b" : 1, "c" : 2 } ] }
{ "_id" : 1, "a" : [ { "b" : 0, "c" : 2 }, { "b" : 1, "c" : 0 } ] }

The expected result is that the sort order would be the same regardless of whether an index is present.



 Comments   
Comment by David Storch [ 04/Jan/18 ]

This problem has gone away in 3.6 as a result of the work done under SERVER-19402. The fix cannot be backported to 3.4 or earlier---it is a breaking change to the semantics of sorting array fields, and it involves substantial changes to how the query execution engine actual performs the sort. Closing as "Gone Away".

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