[SERVER-73482] $natural hint overrides sort on clustered collections Created: 31/Jan/23  Updated: 29/Oct/23  Resolved: 13/Apr/23

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: 7.0.0-rc0, 7.1.0-rc0, 6.3.1

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

Issue Links:
Backports
Depends
Related
related to SERVER-73009 Decreasing order sort on clustered co... Closed
Assigned Teams:
Query Optimization
Backwards Compatibility: Fully Compatible
Operating System: ALL
Backport Requested:
v7.0, v6.3
Sprint: QO 2023-02-20, QO 2023-03-06, QO 2023-03-20, QO 2023-04-03, QO 2023-04-17
Participants:
Linked BF Score: 8

 Description   

From david.percy@mongodb.com :

On a clustered collection, if you hint {$natural: -1} and sort {_id: 1} in opposite directions, then we're wrongly assuming the clustered scan satisfies the sort:

> db.clustered_collection.find().sort({_id:1}).hint({$natural: -1})
{ "_id" : 3, "a" : 3 }
{ "_id" : 2, "a" : 2 }
{ "_id" : 1, "a" : 1 } 

A similar query that sorts on {a: 1} gets the right answer, with a blocking sort:

> db.clustered_collection.find().sort({a:1}).hint({$natural: -1})
{ "_id" : 1, "a" : 1 }
{ "_id" : 2, "a" : 2 }
{ "_id" : 3, "a" : 3 } 

And a normal collection gets the right answer, with a blocking sort:

> db.normal_collection.find().sort({_id: 1}).hint({$natural: -1})
{ "_id" : 1, "a" : 1 }
{ "_id" : 2, "a" : 2 }
{ "_id" : 3, "a" : 3 } 

 

It appears that there are multiple issues contributing to this:

  • CollectionScanNode::computeProperties doesn't take scan direction into account when computing which sort it provides on a clustered collection, so it will claim to provide an ascending sort even when it is a backwards scan.
  • Even when the scan's sort is properly computed, the planner will still think that the sort can be provided if we reverse the scan here. However reverseScans won't actually reverse the scan at this point because reverseCollscans defaults to false.


 Comments   
Comment by Githook User [ 13/Apr/23 ]

Author:

{'name': 'Alya Berciu', 'email': 'alya.berciu@mongodb.com', 'username': 'alyacb'}

Message: SERVER-73482 Fix $natural hint with clustered collection sorts
Branch: v7.0
https://github.com/mongodb/mongo/commit/f52315215e7aadfc3eb0bc66d19d19e39bccaeb9

Comment by Githook User [ 13/Apr/23 ]

Author:

{'name': 'Alya Berciu', 'email': 'alya.berciu@mongodb.com', 'username': 'alyacb'}

Message: SERVER-73482 Fix $natural hint with clustered collection sorts
Branch: v6.3
https://github.com/mongodb/mongo/commit/34cde74dae0c59ff7ae215fbbbf0050ecb91f201

Comment by Githook User [ 13/Apr/23 ]

Author:

{'name': 'Alya Berciu', 'email': 'alya.berciu@mongodb.com', 'username': 'alyacb'}

Message: SERVER-73482 Add clustered hint test to multiversion for backports
Branch: master
https://github.com/mongodb/mongo/commit/a1f4a8c5b788903285c3afbcc761913cf9bafc49

Comment by Githook User [ 12/Apr/23 ]

Author:

{'name': 'Alya Berciu', 'email': 'alya.berciu@mongodb.com', 'username': 'alyacb'}

Message: SERVER-73482 Fix $natural hint with clustered collection sorts
Branch: master
https://github.com/mongodb/mongo/commit/abdcf26c5388c75296b561edcb30cda98a86d20f

Comment by Davis Haupt (Inactive) [ 03/Apr/23 ]

Moving this back to open since I haven't had time to investigate this recently.

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