[SERVER-73009] Decreasing order sort on clustered collections on replica sets returns increasing order Created: 18/Jan/23  Updated: 29/Oct/23  Resolved: 02/Feb/23

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: 6.3.0-rc0, 6.0.5

Type: Bug Priority: Major - P3
Reporter: Rohan Sharan Assignee: Alyssa Clark
Resolution: Fixed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Backports
Depends
is depended on by SERVER-73423 CLUSTERED_IXSCAN with sort generates ... Closed
Related
is related to SERVER-73482 $natural hint overrides sort on clust... Closed
Assigned Teams:
Query Execution
Backwards Compatibility: Fully Compatible
Backport Requested:
v6.2, v6.0
Sprint: QE 2023-02-06
Participants:

 Description   

There was replica set passthrough tests that were failing on mongosync for clustered collections so they had previously been disabled. In trying to re-enable them (REP-1479), I found that there seems to be an issue with sorting and clustered collections on replica sets on v6.0.

The example I am about to add is with a clustered collection with collation, but I think the issue is present on other types of clustered collections as well.

// Create the collection
const defaultCollation = {     locale: "en",     strength: 2 };
db.createCollection("clustered_collection_with_collation", {clusteredIndex: {key: {_id: 1}, unique: true}, collation: defaultCollation})
 
// Insert some documents
db.clustered_collection_with_collation.insertOne({"_id": -5})
db.clustered_collection_with_collation.insertOne({"_id": 0})
db.clustered_collection_with_collation.insertOne({"_id": 5})
 
// Should sort documents in increasing order, sorts correctly
db.runCommand({
	"aggregate": "clustered_collection_with_collation",
	"pipeline": [
		{"$sort": {"_id": 1}},
		{"$project": {"_id": 1}}
	],
	"readConcern": {"level": "majority", "afterClusterTime": Timestamp(0, 1)},
	"hint": {"_id": 1},
	"cursor": {},
})
 
// Should sort documents in decreasing order, actually sorts in increasing order
db.runCommand({
	"aggregate": "clustered_collection_with_collation",
	"pipeline": [
		{"$sort": {"_id": -1}},
		{"$project": {"_id": 1}}
	],
	"readConcern": {"level": "majority", "afterClusterTime": Timestamp(0, 1)},
	"hint": {"_id": 1},
	"cursor": {},
})

As we can see, when it tries to sort the documents in a clustered collection in decreasing order, it still sorts them in increasing order.



 Comments   
Comment by Githook User [ 02/Feb/23 ]

Author:

{'name': 'Alyssa Wagenmaker', 'email': 'alyssa.wagenmaker@mongodb.com', 'username': 'awagenmaker'}

Message: SERVER-73009 Compute clustered scan direction when plan is hinted or cached
Branch: v6.0
https://github.com/mongodb/mongo/commit/55e4a05daabba24a6ec976a69335823384ed981b

Comment by Githook User [ 02/Feb/23 ]

Author:

{'name': 'Alyssa Wagenmaker', 'email': 'alyssa.wagenmaker@mongodb.com', 'username': 'awagenmaker'}

Message: SERVER-73009 Compute clustered scan direction when plan is hinted or cached
Branch: master
https://github.com/mongodb/mongo/commit/a23cc3ed74c25df12b1c39e2f5569436edf54cf2

Comment by Rohan Sharan [ 26/Jan/23 ]

We have a mongosync release coming up in the next few weeks, so it would be great if this could be fixed by then. We currently are supposed to be supporting clustered collections in mongosync (without expireAfterSeconds), but we will get data consistency errors with replica sets unless this is fixed. If it can't be fixed, we may have to add it as a limitation. cc tim.fogarty@mongodb.com

Comment by Rohan Sharan [ 20/Jan/23 ]

louis.williams@mongodb.com I'm not too familiar with the server's process on when they fix issues like this. Is this going to be patched any time soon?

Comment by Louis Williams [ 19/Jan/23 ]

The problem appears to be that the hint overrides the sort order:

rs [direct: primary] test> db.clustered_collection_with_collation.aggregate([{$sort: {_id: 1}}])
[ { _id: -5 }, { _id: 0 }, { _id: 5 } ]
rs [direct: primary] test> db.clustered_collection_with_collation.aggregate([{$sort: {_id: -1}}])
[ { _id: 5 }, { _id: 0 }, { _id: -5 } ]
rs [direct: primary] test> db.clustered_collection_with_collation.aggregate([{$sort: {_id: -1}}], {hint: {_id: 1}})
[ { _id: -5 }, { _id: 0 }, { _id: 5 } ]

When explained, the descending sort correctly uses a backward collection scan and does not use a blocking sort:

rs [direct: primary] test> db.clustered_collection_with_collation.aggregate([{$sort: {_id: -1}}], {explain: true})
{
  explainVersion: '1',
  queryPlanner: {
    namespace: 'test.clustered_collection_with_collation',
    indexFilterSet: false,
    parsedQuery: {},
    collation: {
      locale: 'en',
      caseLevel: false,
      caseFirst: 'off',
      strength: 2,
      numericOrdering: false,
      alternate: 'non-ignorable',
      maxVariable: 'punct',
      normalization: false,
      backwards: false,
      version: '57.1'
    },
    queryHash: '9DD1D634',
    planCacheKey: '9DD1D634',
    optimizedPipeline: true,
    maxIndexedOrSolutionsReached: false,
    maxIndexedAndSolutionsReached: false,
    maxScansToExplodeReached: false,
    winningPlan: { stage: 'COLLSCAN', direction: 'backward' },
    rejectedPlans: []
  },

Generated at Thu Feb 08 06:23:24 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.